Query to find Big Table or High consumption Table in oracle database

 SELECT * FROM

(select

SEGMENT_NAME,

SEGMENT_TYPE,

BYTES/1024/1024/1024 GB,

TABLESPACE_NAME

from

dba_segments

order by 3 desc ) WHERE

ROWNUM <= 10;

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process

Issue: DR site is down permanently and disabled the log_archive_dest_state_2 on both nodes, but still archive logs are not deleting from the primary.

cause: 

SELECT * from v$archive_dest where (valid_now = 'UNKNOWN' AND status = 'DEFERRED') ;

If the above query returns a row (rows), then you have some destinations that are marked DEFERRED

If  you have one of V$ARCHIVE_DEST with valid_now=’UNKNOWN’ and status = ‘DEFERRED’.

Then because of Bug 16082541, we are considering DEFERRED destination applied archived logs too and hence RMAN not purging those. 

Solution:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = '' SCOPE = BOTH sid='*';

After changing above parameter archive log files deleted from primary.

How to Kill RMAN Process/session in oracle Database

 1. Os Level:

ps -ef|grep rman

kill -9 <process_id>

--Once killed the os session, rman sessions still running and taking backup in database level.

--Use the below query to find the % completion

2. Database Level:

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

--Kill all the rman sessions in database.

alter system kill session '2515,27025' immediate;

Note: After killing database sessions using alter query, it will take sometime to clear the existing sessions. 

But % wont increase after killing database sessions.