Datafiles and thier sizes

set pagesize 90 linesize 132
col tablespace_name format a30
col file_name format a55
col SizeInGB format 999,999
--col MaxGB format 999,999
-- col UserGB format 999,999

break on tablespace_name skip 1

compute sum of SizeInGB on tablespace_name
-- compute sum of MaxGB on tablespace_name
--compute sum of UserGB on tablespace_name

SELECT  tablespace_name,
        sum(bytes)/(1024*1024) SizeInGB ,
 --       sum(maxbytes)/(1024*1024) MaxGB,
--      sum(user_bytes)/(1024*1024) UserGB,
        file_name
  FROM  dba_data_files
 GROUP BY
        tablespace_name,
        file_name
 ORDER BY 1
/

check_propagate.sql

To check Streams Propagation process.

set linesize 132

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A25
COLUMN QUEUE_SCHEMA HEADING 'Queue|Owner' FORMAT A10
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A20
COLUMN DBLINK HEADING 'Database|Link' FORMAT A20
COLUMN SCHEDULE_STATUS HEADING 'Schedule Status' FORMAT A20

SELECT p.PROPAGATION_NAME,
       s.QUEUE_SCHEMA,
       s.QUEUE_NAME,
       s.DBLINK,
       s.SCHEDULE_STATUS
  FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
  WHERE p.DESTINATION_DBLINK = s.DBLINK AND
        p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
        p.SOURCE_QUEUE_NAME  = s.QUEUE_NAME
/

check_capture.sql

COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A16
COLUMN PROCESS_NAME HEADING 'Capture|Process|Number' FORMAT A7
COLUMN SID HEADING 'Session|ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999
COLUMN STATE HEADING 'State' FORMAT A27
COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo|Entries|Scanned' FORMAT 9999999
COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' FORMAT 999999
COLUMN CAPTURE_MESSAGE_NUMBER HEADING Captured
COLUMN ENQUEUE_MESSAGE_NUMBER HEADING enqueue
COLUMN AVAILABLE_MESSAGE_NUMBER HEADING avialable

SELECT c.CAPTURE_NAME,
       SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, 
       c.SID,
       c.SERIAL#, 
       c.STATE,
       c.TOTAL_MESSAGES_CAPTURED,
       c.TOTAL_MESSAGES_ENQUEUED ,
       c.CAPTURE_MESSAGE_NUMBER,
       c.ENQUEUE_MESSAGE_NUMBER,
       c.AVAILABLE_MESSAGE_NUMBER
  FROM V$STREAMS_CAPTURE c, V$SESSION s
  WHERE c.SID = s.SID AND
        c.SERIAL# = s.SERIAL#
/

list_propagate.sql

COLUMN DESTINATION_DBLINK HEADING 'Destination|DB Link' FORMAT A18
COLUMN SCHEDULE_DISABLED HEADING 'Schedule' FORMAT A8
COLUMN PROCESS_NAME HEADING 'Process' FORMAT A7
COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 9999
COLUMN LAST_ERROR_TIME HEADING 'Last Error Time' FORMAT A15
COLUMN LAST_ERROR_MSG HEADING 'Last Error Message' FORMAT A18

SELECT p.PROPAGATION_NAME ,
p.DESTINATION_DBLINK,
       DECODE(s.SCHEDULE_DISABLED,
                'Y', 'Disabled',
                'N', 'Enabled') SCHEDULE_DISABLED,
       s.PROCESS_NAME,
       s.FAILURES,
       s.LAST_ERROR_TIME,
       s.LAST_ERROR_MSG
  FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
  WHERE --p.PROPAGATION_NAME like 'LDB%_SUPPR_PROPAGATE'   AND 
  p.DESTINATION_DBLINK = s.DESTINATION
  AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
  AND s.QNAME = p.SOURCE_QUEUE_NAME
order by 1;

db_space.sql

This SQL is not the perfect one, but good enough.

set pagesize 80
col used_prcnt format 999.99
col allocated_mb format 999,999
col used_mb format 999,999
col free_mb format 999,999


select ddf.tablespace_name,
       sum(ddf.allocated_mb) as allocated_mb,
       sum(ddf.allocated_mb-dfs.bytes) as used_mb,
       sum(dfs.bytes) as free_mb,
       round(100*(sum(ddf.allocated_mb-dfs.bytes)/sum(ddf.allocated_mb)),2) as Used_prcnt
  from
       (select substr(file_name,1,instr(file_name,'/',1,3)-1) as LUN,
               TABLESPACE_NAME,
               round(sum(bytes)/1024/1024,2) Allocated_MB
          from dba_data_files
         group by substr(file_name,1,instr(file_name,'/',1,3)-1),
                  TABLESPACE_NAME)  ddf,
       (select TABLESPACE_NAME,  round(sum(bytes)/1024/1024,2) as bytes
          from dba_free_space
         group by TABLESPACE_NAME
       ) dfs
 where
    ddf.tablespace_name=dfs.tablespace_name(+)
  group by
  ddf.tablespace_name
order by Used_prcnt desc
/