Oracle Archive Gaps in Standby Database

Oracle Standby Gap info

Oracle Standby log gap sql

Standby Gap

On the Both 

set pagesize 200
set linesize 200
select
  substr(local.name,1,70)  "Archive Name",
  case when remote.sequence# is null then 'NOT TRANSMITTED' 
                                     else 'transmitted'
  end,
  local.sequence#,
  local.thread#
from
  (select * from v$archived_log where dest_id = 1) local 
                                    left join 
  (select * from v$archived_log where dest_id = 2) remote
  on local.sequence# = remote.sequence# and
     local.thread#   = remote.thread#
  order by local.sequence#;



Archive Name                                                           CASEWHENREMOTE.  SEQUENCE#    THREAD#
---------------------------------------------------------------------- --------------- ---------- ----------
/u01/oracle/Archive/arch_1_0000000025_780268156.arc                    transmitted             25          1
/u01/oracle/Archive/arch_1_0000000026_780268156.arc                    transmitted             26          1
/u01/oracle/Archive/arch_1_0000000027_780268156.arc                    transmitted             27          1
/u01/oracle/Archive/arch_1_0000000028_780268156.arc                    transmitted             28          1
/u01/oracle/Archive/arch_1_0000000029_780268156.arc                    transmitted             29          1
/u01/oracle/Archive/arch_1_0000000030_780268156.arc                    transmitted             30          1
/u01/oracle/Archive/arch_1_0000000031_780268156.arc                    transmitted             31          1
/u01/oracle/Archive/arch_1_0000000032_780268156.arc                    transmitted             32          1





On Standby

SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);   

   THREAD#  SEQUENCE#
---------- ----------
         1         49
         1         50
         1         51
         1         52
         1         53
         1         54
         1         59
         1         60

8 rows selected.


On Standby

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     60                    60          0





SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM  V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1         59          1       1381
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      APPLYING_LOG          1         60        791     102400
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1         60        791          1

9 rows selected.




on Primary

column DEST_NAME format A19
column DB_UNIQUE_NAME format A15
column VALID_NOW format A9
column VALID_TYPE format A15
column VALID_ROLE format A15
select DEST_NAME,DB_UNIQUE_NAME,VALID_NOW ,VALID_TYPE,VALID_ROLE from V$ARCHIVE_DEST WHERE DB_UNIQUE_NAME <> 'NONE';

DEST_NAME           DB_UNIQUE_NAME  VALID_NOW VALID_TYPE      VALID_ROLE
------------------- --------------- --------- --------------- ---------------
LOG_ARCHIVE_DEST_2  STANDBY         YES       ONLINE_LOGFILE  PRIMARY_ROLE





col archive_log_seq format 9999999
col dest format a10
select max(SEQUENCE#) archive_log_seq ,'1' dest from v$archived_log where DEST_ID=1
union
select max(SEQUENCE#),'2' dest from v$archived_log where DEST_ID=2


or

select * from (select max(SEQUENCE#) archive_log_seq ,'1' dest from v$archived_log where DEST_ID=1
union
select max(SEQUENCE#) archive_log_seq ,'2' dest from v$archived_log where DEST_ID=2 )

ARCHIVE_LOG_SEQ DEST
--------------- ----------
             60 1
             60 2


 select pri , stand , pri - stand "Diff" from ( select max(SEQUENCE#) pri from v$archived_log where DEST_ID=1) , (select max(b.SEQUENCE#) stand from v$archived_log b where b.DEST_ID=2 );

       PRI      STAND       Diff
---------- ---------- ----------
        60         60          0





On standby

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS ;

or

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS where ARCHIVED_THREAD# !=0 ;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
               1            60               0            0
               1            60               1           60




on Stnadby

alter session set nls_date_format='dd-mon-yy hh24:mi:ss'
 
Session altered.


select app_thread, seq_app, tm_applied,
nvl(seq_rcvd,seq_app) seq_rcvd, nvl(tm_rcvd,tm_applied) tm_rcvd
from (select sequence# seq_app, FIRST_TIME tm_applied, thread# app_thread
from v$archived_log where applied = 'YES'
and (first_time, thread#) in ( select max(FIRST_TIME ), thread# from v$archived_log where applied = 'YES'
group by thread# ) ),(select sequence# seq_rcvd, FIRST_TIME tm_rcvd, thread# rcvd_thread
from v$archived_log where applied = 'NO'
and (first_time, thread#) in ( select max(FIRST_TIME ), thread# from v$archived_log where applied = 'NO'
group by thread# )
)
where rcvd_thread(+)= app_thread
/



APP_THREAD    SEQ_APP TM_APPLIED           SEQ_RCVD TM_RCVD
---------- ---------- ------------------ ---------- ------------------
         1         60 19-jan-13 07:04:29         60 19-jan-13 07:04:29





On primary

select (select name from V$DATABASE) database ,(select max(sequence#) from v$archived_log where dest_id=1) Current_primary_seq,
( select max(sequence#) from v$archived_log where next_time > sysdate - 1
and dest_id=2 ) max_stby,(select nvl((select max(sequence#) - min(sequence#) from v$archived_log
where next_time > sysdate - 1 and dest_id=2 and applied='NO'),0) from dual) "To be applied",
((select max(sequence#) from v$archived_log
where dest_id=1) - (select max(sequence#) from v$archived_log where dest_id=2)) "To be Shipped" from dual 
/

DATABASE  CURRENT_PRIMARY_SEQ   MAX_STBY To be applied To be Shipped
--------- ------------------- ---------- ------------- -------------
VIHAAN                     60         60             0             0




On both The site Primary and Standby 

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,(select thread# thrd, max(sequence#) lhmax from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd 

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1                60               60


 
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

    Thread Last Sequence Generated
---------- -----------------------
         1                      60
         1                      60



SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
     (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#))   APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;


    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     60                    60          0




On Primary

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
 
   Thread Last Sequence Generated
---------- -----------------------
         1                      60
         1                      60



On Physical Standby

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;


    Thread Last Sequence Generated
---------- -----------------------
         1                      60

set pagesize 0
select 
'Thread                     : ' || THREAD   		||chr(10)||
'Prod - Archived            : ' ||PR_Archived 		||chr(10)||
'STBY - Archived            : ' ||STBY_ARCHIVED 	||chr(10)||
'STBY - Applied             : ' ||STBY_APPLIED 		||chr(10)||
'Shipping GAP (PR -> STBY)  : '	||SHIPPING_GAP_STBY ||chr(10)||
'Applied GAP (STBY -> STBY) : '	||APPLIED_GAP_STBY_STBY
from (
select
t1 THREAD,
pricre "PR_ARCHIVED",
stdcre "STBY_ARCHIVED",
stdnapp "STBY_APPLIED",
pricre-stdcre "SHIPPING_GAP_STBY",
stdcre-stdnapp "APPLIED_GAP_STBY_STBY"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES'  and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and  thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
;
set pagesize 80



Thread                     : 1
Prod - Archived            : 7105
STBY - Archived            : 7105
STBY - Applied             : 7104
Shipping GAP (PR -> STBY)  : 0
Applied GAP (STBY -> STBY) : 1





set linesize 200 pagesize 200
alter session set nls_date_format='dd-mm-YYYY hh24:mi'; 
col destination for a15
select /*+ parallel(8) */ sysdate, ar.inst_id "inst_id",
       ar.dest_id "dest_id",
       ar.status "dest_status",
       ar.destination "destination",
         (select MAX (sequence#) highiest_seq   from v$archived_log val, v$database vdb
           where val.resetlogs_change# = vdb.resetlogs_change#
                 and thread# = ar.inst_id
                 and dest_id = ar.dest_id)
       - NVL (
            (select MAX (sequence#)   from v$archived_log val, v$database vdb
              where  val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'
                    and applied = 'YES'),  0)   "applied_gap",
         (SELECT MAX (sequence#) highiest_seq    from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                     AND thread# = ar.inst_id)
       - NVL (
            (SELECT MAX (sequence#)  from v$archived_log val, v$database vdb
              where val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'), 0)     "received_gap",
       NVL (
          (SELECT MAX (sequence#)   from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'), 0) "last_received_seq",
       NVL (
          (SELECT MAX (sequence#)  from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'
                  and applied = 'YES'),  0)   "last_applied_seq"
  from (SELECT DISTINCT dest_id,
                        inst_id,
                        status,
                        target,
                        destination,
                        error
          from sys.gv_$archive_dest
         where target = 'STANDBY' and STATUS <> 'DEFERRED') ar;



SYSDATE             inst_id    dest_id dest_stat destination     applied_gap received_gap last_received_seq last_applied_seq
---------------- ---------- ---------- --------- --------------- ----------- ------------ ----------------- ----------------
17-08-2023 14:58          1          2 VALID     *********_****5              1          0       7105             7104






SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;




SELECT 'Last Applied : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence# FROM v$archived_log
WHERE sequence# =(SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES')
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,thread#,sequence# FROM v$archived_log
WHERE sequence# =(SELECT MAX(sequence#) FROM v$archived_log );



LOGS             TIME                           THREAD#  SEQUENCE#
---------------- --------------------------- ---------- ----------
Last Applied :   11-JUN-23:08:35:19                   1      35567
Last Received :  11-JUN-23:08:44:26                   1      35568



-- Check that Archive Logs are being Shipped
-- This query needs to be run on the Primary database

SET PAGESIZE 300
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A20
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,'.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
);








set linesize 200 pagesize 200
col destination for a15
select /*+ parallel(8) */ ar.inst_id "inst_id",
       ar.dest_id "dest_id",
       ar.status "dest_status",
       ar.destination "destination",
         (select MAX (sequence#) highiest_seq   from v$archived_log val, v$database vdb
           where val.resetlogs_change# = vdb.resetlogs_change#
                 and thread# = ar.inst_id
                 and dest_id = ar.dest_id)
       - NVL (
            (select MAX (sequence#)   from v$archived_log val, v$database vdb
              where  val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'
                    and applied = 'YES'),  0)   "applied_gap",
         (SELECT MAX (sequence#) highiest_seq    from v$archived_log val, v$database vdb
           where     val.resetlogs_change# = vdb.resetlogs_change#
                     AND thread# = ar.inst_id)
       - NVL (
            (SELECT MAX (sequence#)  from v$archived_log val, v$database vdb
              where val.resetlogs_change# = vdb.resetlogs_change#
                    and thread# = ar.inst_id
                    and dest_id = ar.dest_id
                    and standby_dest = 'YES'), 0)     "received_gap",
       NVL (
          (SELECT MAX (sequence#)   from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'), 0) "last_received_seq",
       NVL (
          (SELECT MAX (sequence#)  from v$archived_log val, v$database vdb
            where     val.resetlogs_change# = vdb.resetlogs_change#
                  and thread# = ar.inst_id
                  and dest_id = ar.dest_id
                  and standby_dest = 'YES'
                  and applied = 'YES'),  0)   "last_applied_seq"
  from (SELECT DISTINCT dest_id,
                        inst_id,
                        status,
                        target,
                        destination,
                        error
          from sys.gv_$archive_dest
         where target = 'STANDBY' and STATUS <> 'DEFERRED') ar;
	



set echo on feed on term on
set linesize 200
col PRIMARY_TIME format a20
col STANDBY_COMPLETION_TIME format a23
col LAG_MINUTES for 99999.99
col db_name for a15

SELECT
sys_context('USERENV','DB_NAME') db_name,
prim.thread# thread,
prim.seq primary_seq,
to_char(prim.tm, 'DD-MON-YYYY HH24:MI:SS') primary_time,
tgt.thread# standby_thread,
tgt.seq standby_seq,
to_char(tgt.tm, 'DD-MON-YYYY HH24:MI:SS') standby_completion_time,prim.seq - tgt.seq seq_gap,( prim.tm - tgt.tm ) * 24 * 60 lag_minutes
FROM
(
SELECT thread#,MAX(sequence#) seq,MAX(completion_time) tm FROM v$archived_log GROUP BY thread#
) prim,
(
SELECT
thread#,
MAX(sequence#) seq,
MAX(completion_time) tm
FROM v$archived_log
WHERE dest_id IN (SELECT dest_id FROM v$archive_dest
WHERE target = 'STANDBY'
)
AND applied = 'YES'
GROUP BY thread#
) tgt
WHERE prim.thread# = tgt.thread#;





select *
from (select TIMESTAMP,
completion_time "ArchTime",
SEQUENCE#,
round((blocks * block_size) / (1024 * 1024), 1) "Size Meg",
round((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
OVER(order by TIMESTAMP)) * 24 * 60 * 60,1) "Diff(sec)",
round((blocks * block_size) / 1024 /decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP) OVER(order by TIMESTAMP)) * 24 * 60 * 60),0,1,(TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
OVER(order by TIMESTAMP)) * 24 * 60 * 60),1) "KB/sec",round((blocks * block_size) / (1024 * 1024) /decode(((TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP)
OVER(order by TIMESTAMP)) * 24 * 60 * 60),0,1,(TIMESTAMP - lag(TIMESTAMP, 1, TIMESTAMP) OVER(order by TIMESTAMP)) * 24 * 60 * 60),3) "MB/sec",
round(((lead(TIMESTAMP, 1, TIMESTAMP) over(order by TIMESTAMP)) - completion_time) * 24 * 60 * 60,1) "Lag(sec)"
from v$archived_log a, v$dataguard_status dgs
where a.name = replace(dgs.MESSAGE, 'Media Recovery Log ', '')
and dgs.FACILITY = 'Log Apply Services'
order by TIMESTAMP desc)
where rownum < 10;

=====================================================================

Oracle  DataGaurd on Standard Edition !!!!!!!!!!!!!!!!!!!
on standby !!!! SE !!!!!! dbvisit

--Prod !!

select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2,almax Max_seq_arch1,almax2 Max_seq_arch2
 from (select  max(sequence#) almax    from gv$archived_log where  THREAD#=1) al1,
      (select  max(sequence#) almax2   from gv$archived_log  where THREAD#=2) al2,
      (select max(sequence#) maxSCN1   from gv$log_history   where THREAD#=1) lh,
	  (select max(sequence#) maxSCN2   from gv$log_history   where THREAD#=2) ;

MAX_SEQ_HIST1 MAX_SEQ_HIST2 MAX_SEQ_ARCH1 MAX_SEQ_ARCH2
------------- ------------- ------------- -------------
       524064        470606        524064        470606



---on standby !!!! SE !!!!!! dbvisit
select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2
--,almax Max_seq_arch1,almax2 Max_seq_arch2
 from (select  max(sequence#) almax    from  gv$archived_log where THREAD#=1) al1,
      (select  max(sequence#) almax2   from gv$archived_log  where THREAD#=2) al2,
      (select max(sequence#) maxSCN1   from gv$log_history   where THREAD#=1) lh,
	  (select max(sequence#) maxSCN2   from gv$log_history   where THREAD#=2) ;

MAX_SEQ_HIST1 MAX_SEQ_HIST2
------------- -------------
       524064        470605





Physical Standby
set linesize 300
alter session set nls_date_format = 'dd-Mon-yyyy hh24:mi:ss';
select * from ( select sysdate,count(*) To_Be_Restored from x$kcvfh where fhrba_seq=0),
              ( select count(*) restored,max(fhrba_seq) max_sequence,min(fhrba_seq) min_sequence,max(fhscn) max_scn  from x$kcvfh where fhrba_seq!=0),
			  ( select count(*) total,min(fhscn) min_scn,min(fhrba_seq) min_seq from x$kcvfh)

On Primary

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
 

On Physical Standby

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

-- Standard edition 

select maxSCN1 Max_seq_hist1 ,maxSCN2 Max_seq_hist2

--,almax,almax2

 from (select  max(sequence#) almax   from v$archived_log   where resetlogs_change#=(select resetlogs_change# from v$database where THREAD#=1)) al1,

      (select  max(sequence#) almax2   from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database where THREAD#=2)) al2,

      (select max(sequence#) maxSCN1   from v$log_history  where first_time=(select max(first_time) from v$log_history where THREAD#=1)) lh,

  (select max(sequence#) maxSCN2   from v$log_history  where first_time=(select max(first_time) from v$log_history where THREAD#=2)) ;

Oracle DBMS_SCHEDULER example

The DBMS_JOB package is replaced by the DBMS_SCHEDULER package in oracle 10g
( oracle job )


login as sys or system

sqlplus / as sysdba

sqlplus>


this job will run daily 4 a.m


begin
dbms_scheduler.create_job
(
job_name => 'RMAN_FULL_F',
job_type => 'EXECUTABLE',
job_action => '/usr/bin/ksh',
start_date =>sysdate+ 1/288 ,
number_of_arguments => 1,
repeat_interval => 'freq=daily; byhour=4; byminute=0; bysecond=0',
enabled => false,
comments => 'oracle backup RMAN '
);
end;
/

PL/SQL procedure successfully completed.

(start_date =>sysdate+ 1/288 this will start job after 5min )

Notice that the JOB_TYPE can be PLSQL_BLOCK, STORED_PROCEDURE, and EXECUTABLE.
The REPEAT_INTERVAL
can be HOURLY, DAILY, MINUTELY, YEARLY or BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, BYMINUTE, BYSECOUND.
For example, FREQ=BYWEEKNO=4,7,52 or BYDAY=MON, etc.




exec dbms_scheduler.set_job_argument_value(job_name=>'RMAN_FULL_F',argument_pos
ition=>1 ,argument_value=>'/aptus/oracle/admin/aptdb/script/rman_oracle.sh') ;

PL/SQL procedure successfully completed.

(rman_oracle.sh is the novagenesis Rman backup file )

to eable the job

SQL> exec dbms_scheduler.enable(name=>'RMAN_FULL_F');

PL/SQL procedure successfully completed.



to see the detail of job
select status,run_duration,actual_start_date,additional_info from dba_scheduler_job_run_details
where job_name='RMAN_FULL_F'

oracle dictionary info dict.sql

dict comment
dict info
dictionary comments
@dict.sql
dict.sql


break on sort_key skip1 nodup
col sort_key noprint
col comments format a78 word

def table = &&1

WITH dict_view AS
( SELECT CASE
WHEN SUBSTR(table_name,1,5) = 'USER_' THEN 1
WHEN SUBSTR(table_name,1,4) = 'ALL_' THEN 2
WHEN SUBSTR(table_name,1,4) = 'DBA_' THEN 3
ELSE 4
END AS sort_key
, table_name
, comments
FROM dictionary
WHERE table_name LIKE UPPER('%&&1%')
UNION
SELECT CASE
WHEN SUBSTR(v.view_name,1,5) = 'USER_' THEN 1
WHEN SUBSTR(v.view_name,1,4) = 'ALL_' THEN 2
WHEN SUBSTR(v.view_name,1,4) = 'DBA_' THEN 3
ELSE 4
END AS sort_key
, NVL(s.synonym_name,v.view_name)
, NVL(d.comments,c.comments)
FROM dba_views v
LEFT OUTER JOIN dictionary d
ON d.table_name = v.view_name
LEFT OUTER JOIN all_synonyms s
ON s.table_name = v.view_name
AND s.table_owner = v.owner
AND s.owner = 'PUBLIC'
LEFT OUTER JOIN dba_tab_comments c
ON c.table_name = v.view_name
AND c.owner = v.owner
WHERE ( v.view_name LIKE UPPER('V\_$%&&1%') ESCAPE '\'
OR v.view_name LIKE UPPER('GV\_$&&1%') ESCAPE '\'
OR v.view_name LIKE UPPER('DBA\_$&&1%') ESCAPE '\' )
AND d.table_name IS NULL )
SELECT sort_key, table_name, comments
FROM ( SELECT sort_key, table_name, comments , ROW_NUMBER() OVER(PARTITION BY table_name ORDER BY table_name NULLS LAST) AS seq
FROM dict_view )
WHERE seq = 1
ORDER BY sort_key, table_name
/
undefine 1



SQL> @dict
Enter value for 1: file
old 11: WHERE table_name LIKE UPPER('%&&1%')
new 11: WHERE table_name LIKE UPPER('%file%')
old 31: WHERE ( v.view_name LIKE UPPER('V\_$%&&1%') ESCAPE '\'
new 31: WHERE ( v.view_name LIKE UPPER('V\_$%file%') ESCAPE '\'
old 32: OR v.view_name LIKE UPPER('GV\_$&&1%') ESCAPE '\'
new 32: OR v.view_name LIKE UPPER('GV\_$file%') ESCAPE '\'
old 33: OR v.view_name LIKE UPPER('DBA\_$&&1%') ESCAPE '\' )
new 33: OR v.view_name LIKE UPPER('DBA\_$file%') ESCAPE '\' )

TABLE_NAME COMMENTS
------------------------------ ------------------------------------------------------------------------------
USER_DBFS_HS_FILES
USER_FILE_GROUPS Details about file groups
USER_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
USER_FILE_GROUP_FILES Details about file group files
USER_FILE_GROUP_TABLES Details about the tables in the file group repository
USER_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
USER_FILE_GROUP_VERSIONS Details about file group versions
USER_SCHEDULER_FILE_WATCHERS Scheduler file watch requests owned by the current user

ALL_FILE_GROUPS Details about file groups
ALL_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
ALL_FILE_GROUP_FILES Details about file group files
ALL_FILE_GROUP_TABLES Details about the tables in the file group repository
ALL_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
ALL_FILE_GROUP_VERSIONS Details about file group versions
ALL_SCHEDULER_FILE_WATCHERS Scheduler file watch requests visible to the current user

DBA_DATA_FILES Information about database data files
DBA_EXP_FILES Description of export files
DBA_FILE_GROUPS Details about file groups
DBA_FILE_GROUP_EXPORT_INFO Details about export information of file group versions
DBA_FILE_GROUP_FILES Details about file group files
DBA_FILE_GROUP_TABLES Details about the tables in the file group repository
DBA_FILE_GROUP_TABLESPACES Details about the transportable tablespaces in the file group repository
DBA_FILE_GROUP_VERSIONS Details about file group versions
DBA_HIST_DATAFILE Names of Datafiles
DBA_HIST_FILEMETRIC_HISTORY File Metrics History
DBA_HIST_FILESTATXS Datafile Historical Statistics Information
DBA_HIST_IOSTAT_FILETYPE Historical I/O statistics by file type
DBA_HIST_IOSTAT_FILETYPE_NAME File type names for historical I/O statistics
DBA_HIST_TEMPFILE Names of Temporary Datafiles
DBA_PROFILES Display all profiles and their limits
DBA_SCHEDULER_FILE_WATCHERS All scheduler file watch requests in the database
DBA_SQL_PROFILES set of sql profiles
DBA_TEMP_FILES Information about database temp files

GV$ASM_FILE Synonym for GV_$ASM_FILE
GV$ASM_FILESYSTEM Synonym for GV_$ASM_FILESYSTEM
GV$BACKUP_DATAFILE Synonym for GV_$BACKUP_DATAFILE
GV$BACKUP_SPFILE Synonym for GV_$BACKUP_SPFILE
GV$CONTROLFILE Synonym for GV_$CONTROLFILE
GV$CONTROLFILE_RECORD_SECTION Synonym for GV_$CONTROLFILE_RECORD_SECTION

Oracle List of constraints owned by the current user

constraint list
List of constraints owned by the current user
List constraints for a table owned by the current user




SQL> CREATE OR REPLACE TYPE db_constraint_ot AS OBJECT
( owner VARCHAR2(30)
, constraint_name VARCHAR2(30)
, constraint_type VARCHAR2(1)
, table_name VARCHAR2(30)
, search_condition VARCHAR2(32767)
, r_owner VARCHAR2(30)
, r_constraint_name VARCHAR2(30)
, delete_rule VARCHAR2(9)
, status VARCHAR2(8)
, deferrable VARCHAR2(14)
, deferred VARCHAR2(9)
, validated VARCHAR2(13)
, generated VARCHAR2(14)
, bad VARCHAR2(3)
, rely VARCHAR2(4)
, last_change DATE
, index_owner VARCHAR2(30)
, index_name VARCHAR2(30)
, invalid VARCHAR2(7)
, view_related VARCHAR2(14) )
/

Type created.

SQL> CREATE OR REPLACE TYPE db_constraint_tt AS TABLE OF db_constraint_ot;
2 /

Type created.

SQL> CREATE OR REPLACE TYPE varchar2_tt AS TABLE OF VARCHAR2(4000) ;
2 /

Type created.







def table = &1
set term off
store set sqlplus_settings.sql replace
TTITLE OFF
var tabname VARCHAR2(30)
var results REFCURSOR
exec :tabname := UPPER('&table')
col table new_value table
SELECT UPPER(:tabname) AS "TABLE" FROM dual;
set def on term on autoprint off feed off lines 110

col type hea "Type"
col constraint_name hea "Constraint name"
col table_name hea "Table name"
col table_owner format a20 hea "Owner"
col status hea "Status"
col deferrable hea "Deferrable?"
col deferred hea "Deferred?"

col search_condition format a60 word hea "Definition"

DECLARE
v_table_exists VARCHAR2(1) := 'Y';
v_constraints DB_CONSTRAINT_TT := DB_CONSTRAINT_TT();

CURSOR c_constraints (cp_tablename all_constraints.table_name%TYPE)
IS
SELECT c.owner
, CASE
WHEN c.generated = 'GENERATED NAME' AND c.constraint_name LIKE 'SYS\_%' ESCAPE '_'
THEN '[' || c.constraint_name || ']'
ELSE c.constraint_name
END AS constraint_name
, c.constraint_type
, c.table_name
, c.search_condition
, TO_CHAR(NULL) AS column_list
, DECODE(c.constraint_type,
'R', 'Foreign key (%COLS%) to ' || r.table_name
|| ' (' || c.r_constraint_name
|| ')'
|| DECODE(c.delete_rule, 'CASCADE', ', ' || c.delete_rule),
'P', 'Primary key (%COLS%)',
'U', 'Unique key (%COLS%)'
) key_description
, CAST
( MULTISET
( SELECT column_name
FROM all_cons_columns
WHERE owner = c.owner
AND constraint_name = c.constraint_name
ORDER BY position
) AS VARCHAR2_TT ) key_columns
, c.r_owner
, c.r_constraint_name
, c.delete_rule
, c.status
, c.deferrable
, c.deferred
, c.validated
, c.generated
, c.bad
, c.rely
, c.last_change
, c.index_owner
, c.index_name
, c.invalid
, c.view_related
FROM all_constraints c
, all_constraints r
WHERE c.table_name = cp_tablename
AND c.owner = USER
AND r.constraint_name (+)= c.r_constraint_name
AND r.owner (+)= c.r_owner;
BEGIN
FOR r IN c_constraints(:tabname)
LOOP
v_constraints.EXTEND;

IF r.key_columns.COUNT > 0 THEN
FOR i IN r.key_columns.FIRST..r.key_columns.LAST LOOP
r.column_list := r.column_list || r.key_columns(i) || ', ';
END LOOP;
r.column_list := RTRIM(r.column_list,', ');
END IF;

r.key_description := REPLACE(r.key_description,'%COLS%', r.column_list);

v_constraints(c_constraints%ROWCOUNT) :=
DB_CONSTRAINT_OT
( r.owner
, r.constraint_name
, r.constraint_type
, r.table_name
, NVL(r.search_condition,r.key_description)
, r.r_owner
, r.r_constraint_name
, r.delete_rule
, r.status
, r.deferrable
, r.deferred
, r.validated
, r.generated
, r.bad
, r.rely
, r.last_change
, r.index_owner
, r.index_name
, r.invalid
, r.view_related );
END LOOP;

IF v_constraints.COUNT = 0 THEN
-- Nothing in ALL_CONSTRAINTS - check table exists:
SELECT MIN('N') INTO v_table_exists
FROM dual
WHERE NOT EXISTS
( SELECT 1 FROM all_tables
WHERE table_name = :tabname
AND owner = USER );

IF v_table_exists = 'N' THEN
DBMS_OUTPUT.PUT_LINE('No such table "' || :tabname || '"');
END IF;
END IF;

OPEN :results FOR
SELECT CASE
WHEN search_condition LIKE '"%" IS NOT NULL' THEN 'Not Null'
WHEN constraint_type = 'C' THEN 'Check'
WHEN constraint_type = 'U' THEN 'Unique'
WHEN constraint_type = 'P' THEN 'Primary'
WHEN constraint_type = 'R' THEN 'FK'
ELSE constraint_type
END AS type
, constraint_name
, search_condition
, status
FROM TABLE(v_constraints)
ORDER BY
CASE
WHEN constraint_type = 'P' THEN '1'
WHEN constraint_type = 'U' THEN '2'
WHEN constraint_type = 'R' THEN '3'
WHEN constraint_type LIKE '"%" IS NOT NULL' THEN '4'
WHEN constraint_type = 'C' THEN '5'
ELSE constraint_type
END
, constraint_name;
END;
/

set term on

print :results





TTITLE ON LEFT 'Tables referencing &table:' SKIP1

SELECT -- r_owner AS table_owner
table_name
, constraint_name
, status
, deferrable
, deferred
FROM all_constraints
WHERE constraint_type = 'R'
AND r_owner = USER
AND r_constraint_name IN
( SELECT constraint_name
FROM user_constraints
WHERE table_name = :tabname
AND constraint_type IN ('P','U') );

prompt
TTITLE ""
TTITLE OFF
@sqlplus_settings.sql




SQL> create table anuj1( x number primary key ) ;


test this script


SQL> @cons
Enter value for 1: ANUJ1

Type Constraint name Definition Status
-------- ------------------------------ ------------------------------------------------------------ --------
Primary SYS_C0021021 Primary key (X) ENABLED





SQL> create table anuj2( n number, CONSTRAINT anuj2_fk FOREIGN KEY(n) REFERENCES anuj1(x) );

SQL> desc anuj2
Name Null? Type
----------------------------------------------------------- -------- N NUMBER


SQL> @cons
Enter value for 1: anuj1

Type Constraint name Definition Status
-------- ------------------------------ ------------------------------------------------------------ --------
Primary SYS_C0021021 Primary key (X) ENABLED

Tables referencing ANUJ1:
Table name Constraint name Status Deferrable? Deferred?
------------------------------ ------------------------------ -------- -------------- ---------
ANUJ2 ANUJ2_FK ENABLED NOT DEFERRABLE IMMEDIATE





save_sqlplus_settings.sql
set termout off
store set sqlplus_settings replace
clear breaks
clear columns
clear computes
set feedback off
set verify off
set termout on
set define "&"




restore_sqlplus_settings.sql
set termout off
@sqlplus_settings
clear breaks
clear columns
clear computes
set termout on
============================


or


SQL> col table_name format a32
col columns format a40
set lines 140
set pages 200
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
order by table_name
/


TABLE_NAME CONSTRAINT_NAME COLUMNS
-------------------------------- ------------------------------ ----------------------------------------
EMP FK_DEPTNO DEPTNO

How to read oracle Release No ?

read oracle version
Oracle Release Numbers




Release Number Format

Oracle database server labeled "Release 10.2.0.1.0." The significance of each number

10 is the version number
2 is the new features release number
0 is the maintenance release number
1 is the generic patch set number
0 is the platform specific patch set number




Version Number : This is the most general identifier. It represents a major new edition (or version) of the
software and contains significant new functionality.
New Features Release Number : This number represents a new features release level.
Maintenance Release Number : This number represents a maintenance release level. A few new features may also be included.
Generic Patch Set Number : This number identifies a generic patch set. The patch set is applicable across all operating system and
hardware platforms.


SELECT * FROM PRODUCT_COMPONENT_VERSION;

oracle recover option

recover database until cancel
recover database until time '2004-03-21:22:59:04'
recover database until change 123456

recover datafile 'filename' until cancel
recover datafile 'filename' until time '2004-03-21:22:59:04'
recover datafile 'filename' until change 123456

recover tablespace ts_name until cancel
recover tablespace ts_name until time '2004-03-21:22:59:04'
recover tablespace ts_name until change 123456

recover database using backup controlfile

recover database until cancel
recover database until time '2004-03-21:22:59:04'
recover database until change 123456

recover datafile 'filename' until cancel
recover datafile 'filename' until time '2004-03-21:22:59:04'
recover datafile 'filename' until change 123456

recover tablespace ts_name until cancel
recover tablespace ts_name until time '2004-03-21:22:59:04'
recover tablespace ts_name until change 123456

recover database using backup controlfile

Until time
Performs a incomplete recovery (=Point in time recovery).
The format of the time is 'YYYY-MM-DD:HH24:MI:SS'


recover automatic database until time '2005-02-14:15:45:00';



recover database UNTIL TIME '2010-05-08:08:42:00'


recover database UNTIL TIME '2010-05-08:08:42:00';
recover database until time '2004-03-21:22:59:04'

Oracle Disable/Enable constraints for schema

Oracle Disable/Enable constraints for schema
Oracle constraint Disable
Disable all table constraints in Oracle




spool disable_cons.sql
select 'alter table '||owner||'.'||table_name||' disable constraint '
||constraint_name||'CASCADE ;'
from all_constraints
where status= 'ENABLED'
and owner =upper('&&Owner')
and constraint_type ='P'
union
select 'alter table '||owner||'.'||table_name||' disable constraint '
||constraint_name||' CASCADE ;'
from all_constraints
where status = 'ENABLED'
and CONSTRAINT_TYPE ='R'
and r_constraint_name in
(select constraint_name from all_constraints
where owner =upper('&&Owner')
and CONSTRAINT_TYPE ='P'
)

/
spool off
spool disable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||' disable;'
from all_triggers
where owner =upper('&&Owner')
and status ='ENABLED'
/
spool off
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80


@disable_cons.sql ---- run two time
@disable_triggers.sql

check

select OWNER,
TABLE_NAME,
CONSTRAINT_NAME,
decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
STATUS
from dba_constraints
where STATUS = 'ENABLED'
and owner =upper('&&Owner')
order by OWNER, TABLE_NAME, CONSTRAINT_NAME







after diable the constraints then enable them via this script




spool enable_cons.sql
select 'alter table '||owner||'.'||table_name||' ENABLE constraint '
||constraint_name||';'
from all_constraints
where status= 'DISABLED'
and owner =upper('&&Owner')
and constraint_type ='P'
union
select 'alter table '||owner||'.'||table_name||' ENABLE constraint '
||constraint_name||';'
from all_constraints
where status = 'DISABLED'
and CONSTRAINT_TYPE ='R'
and r_constraint_name in
(select constraint_name from all_constraints
where owner =upper('&&Owner')
and CONSTRAINT_TYPE ='P'
)
/
spool off
spool enable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||' enable;'
from all_triggers
where owner =upper('&&Owner')
and status ='DISABLED'
/
spool off
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80

@enable_cons.sql
@enable_triggers.sql



Then check

select OWNER,
TABLE_NAME,
CONSTRAINT_NAME,
decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
STATUS
from dba_constraints
where STATUS = 'DISABLED'
and owner =upper('&&Owner')
order by OWNER, TABLE_NAME, CONSTRAINT_NAME


or






BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'ENABLED'
ORDER BY c.constraint_type DESC)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name||' CASCADE ;' );
END LOOP;
END;
/




BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'DISABLED'
ORDER BY c.constraint_type)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/

============

CREATE OR REPLACE PROCEDURE control_fkeys(

 p_table_name IN dba_constraints.table_name%TYPE ,

 p_owner IN dba_constraints.table_name%TYPE,

 p_enable_flag IN NUMBER

,p_status IN OUT NUMERIC)

AUTHID DEFINER IS

 -- constants

 k_enable dba_constraints.status%TYPE := 'ENABLE';

 k_disable dba_constraints.status%TYPE := 'DISABLE';

 -- identify fkeys on pkey for given table

 CURSOR id_fkeys (

 c_table_name dba_constraints.table_name%TYPE

 ,c_owner dba_constraints.owner%TYPE

 ,c_status dba_constraints.status%TYPE

  )

IS

  SELECT table_name, constraint_name fkey, r_constraint_name pkey,status

 FROM dba_constraints

WHERE ( constraint_type='R' and table_name = c_table_name and owner=c_owner) 

--outbound fk constraints

 or

 ( r_constraint_name IN (

 --inbound fk constraints

 SELECT constraint_name

 FROM dba_constraints

 WHERE table_name = c_table_name

 and owner=p_owner

 AND constraint_type='P')

 )

 AND status!=c_status

 ORDER BY table_name, constraint_name;

 /* Note: this select only gets FK constraints that are inbound, meaning

the specified table contains parent records.

 */

-- SELECT table_name, constraint_name fkey, r_constraint_name pkey,status

-- FROM user_constraints

-- WHERE constraint_type='R'

-- AND status!=c_status

-- AND r_constraint_name IN (

-- SELECT constraint_name

-- FROM user_constraints

-- WHERE table_name=c_table_name

-- AND constraint_type='P')

-- ORDER BY table_name, constraint_name;

 -- record variables

 --

 rec_id_fkeys id_fkeys%ROWTYPE;

 --

 -- variables

  l_status dba_constraints.status%TYPE;

 l_table_name dba_constraints.table_name%TYPE;

 l_owner dba_constraints.table_name%TYPE;

 l_stmt VARCHAR2(255);

 l_pkey_name dba_constraints.constraint_name%TYPE;

 BEGIN

 p_status := 0;

 l_table_name := UPPER(p_table_name);

l_owner  := UPPER(p_owner);

 IF (p_enable_flag = 1) THEN

 l_status := k_enable;

 ELSIF (p_enable_flag = 0) THEN

 l_status := k_disable;

 ELSE

 DBMS_OUTPUT.put_line(

 '-- control_fkeys: enable_flag must be 1 or 0 [' || p_enable_flag

|| ']');

 p_status := 1001;

 END IF;

 IF (p_status = 0) THEN -- validated enable flag

 -- a primary key for the given table must exist

 SELECT constraint_name

 INTO l_pkey_name

 FROM dba_constraints

 WHERE table_name=l_table_name

 AND constraint_type='P'

 and owner=l_owner

 ;

 DBMS_OUTPUT.put_line( '-- control_fkeys: ' || l_status || ' foreign key constraints on table ' || l_table_name || ' whose primary key is ' || l_pkey_name);

 OPEN id_fkeys(l_table_name, l_owner,l_status || 'D');

 LOOP -- process foreign keys

 FETCH id_fkeys INTO rec_id_fkeys;

 EXIT WHEN id_fkeys%NOTFOUND;

 IF l_status = k_enable THEN

l_stmt := 'ALTER TABLE ' || rec_id_fkeys.table_name ||' ENABLE NOVALIDATE CONSTRAINT ' ||rec_id_fkeys.fkey;

 DBMS_OUTPUT.put_line(l_stmt);

 --EXECUTE IMMEDIATE l_stmt;

 l_stmt := 'ALTER TABLE ' || rec_id_fkeys.table_name || ' MODIFY ' ||' CONSTRAINT ' || rec_id_fkeys.fkey || 'VALIDATE' ;

 DBMS_OUTPUT.put_line(l_stmt);

 --EXECUTE IMMEDIATE l_stmt;

 ELSE

 l_stmt := 'ALTER TABLE ' || rec_id_fkeys.table_name ||' DISABLE CONSTRAINT ' || rec_id_fkeys.fkey;

 DBMS_OUTPUT.put_line(l_stmt);

 --EXECUTE IMMEDIATE l_stmt;

 END IF;

 END LOOP; -- process foreign keys

 IF (id_fkeys%ROWCOUNT = 0) THEN -- no fkeys found that weren't enabled/disabled

 DBMS_OUTPUT.put_line( '-- control_fkeys: No foreign keys found against table ' || l_table_name || ' to ' || l_status);

 END IF; -- no rows found

 CLOSE id_fkeys;

 END IF; -- validated enable flag

EXCEPTION

WHEN NO_DATA_FOUND THEN -- primary key lookup failed

 p_status := 1002;

 DBMS_OUTPUT.put_line( '-- control_fkeys: no primary key exists for table ' ||l_table_name);

WHEN OTHERS THEN

 p_status := SQLCODE;

 DBMS_OUTPUT.put_line('-- control_fkeys: ' || SQLERRM(p_status));

 IF (id_fkeys%ISOPEN) THEN

 CLOSE id_fkeys;

 END IF;

END control_fkeys;

/

set serveroutput on ;

variable ret_val number;

execute sys.control_fkeys('EMP','ANUJ',1,:ret_val);

execute sys.control_fkeys('EMP','ANUJ',1,:ret_val);

-- control_fkeys: ENABLE foreign key constraints on table EMP whose primary key is PK_EMP

ALTER TABLE EMP ENABLE NOVALIDATE CONSTRAINT FK_DEPTNO

ALTER TABLE EMP MODIFY  CONSTRAINT FK_DEPTNOVALIDATE

==============

--DISABLE constraints

for i in (select 'ALTER TABLE '||ac.owner||'.'||ac.table_name||' DISABLE CONSTRAINT '||constraint_name  as sql_string,constraint_name,ac.table_name

          from all_constraints ac,all_tables at

          where ac.table_name = at.table_name

          and ac.owner=at.owner

          and ac.owner=p_owner 

order by constraint_type desc

) loop

  dbms_output.put_line(current_timestamp||':'||i.sql_string);

 --execute immediate i.sql_string;

  dbms_output.put_line( 'Constraint '||constraint_name ||' on '||table_name ||' is ' || ' Disabled.');

end loop; 

 

-- Enable 

for i in (select 'ALTER TABLE '||ac.owner||'.'||ac.table_name||' ENABLE novalidate CONSTRAINT  '||

          constraint_name  as sql_string,constraint_name,ac.table_name

          from all_constraints ac ,all_tables at

          where ac.table_name = at.table_name

          and ac.owner=at.owner

          and ac.owner=p_owner 

 order by constraint_type

) loop

        dbms_output.put_line(i.sql_string);

        -- execute immediate i.sql_string;

  end loop;

===================

SET SERVEROUTPUT ON 

DECLARE

    v_sql VARCHAR2(500);

BEGIN

    FOR rec IN (

        SELECT owner, table_name, constraint_name

        FROM all_constraints

        WHERE owner = 'ANUJ'

          AND validated = 'NOT VALIDATED'

          AND table_name NOT LIKE 'BIN$%' -- Ignore Recycle Bin objects

          AND constraint_type IN ('P', 'U', 'R', 'C') -- PK, Unique, FK, Check

    )

    LOOP

        BEGIN

            v_sql := 'ALTER TABLE "' || rec.owner || '"."' || rec.table_name || 

                     '" MODIFY CONSTRAINT "' || rec.constraint_name || '" ENABLE VALIDATE';

            

            DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);

            EXECUTE IMMEDIATE v_sql;

            

        EXCEPTION

            WHEN OTHERS THEN

                DBMS_OUTPUT.PUT_LINE('FAILED on table ' || rec.table_name || ': ' || SQLERRM);

                -- The loop will continue 

        END;

    END LOOP;

END;

/

set serveroutput on

DECLARE

    constraint_text LONG;

    c_name          VARCHAR2(100);

    c_type          VARCHAR2(100);

    c_owner         VARCHAR2(100);

    c_table_name    VARCHAR2(100);

    v_disable_sql   VARCHAR2(1000);

    v_enable_sql    VARCHAR2(1000);

    CURSOR constraints IS

        SELECT owner, table_name, search_condition, constraint_name, constraint_type

        FROM dba_constraints

        WHERE owner IN ('ANUJ')

          AND table_name in ( 'DEPT','EMP');

BEGIN

    OPEN constraints;

    LOOP

        FETCH constraints INTO c_owner, c_table_name, constraint_text, c_name, c_type;

        EXIT WHEN constraints%NOTFOUND;

        -- Filter out 'IS NOT NULL' check constraints

        IF INSTR(UPPER(constraint_text), 'IS NOT NULL') = 0 OR constraint_text IS NULL THEN

            dbms_output.put_line('==============================================');

            dbms_output.put_line('Table Name       = ' || c_owner || '.' || c_table_name);

            dbms_output.put_line('Constraint Name  = ' || c_name);

            dbms_output.put_line('Constraint Type  = ' || c_type);

            dbms_output.put_line('Search Condition = ' || constraint_text);

            -- Construct DISABLE command

            v_disable_sql := 'ALTER TABLE "' || c_owner || '"."' || c_table_name || '" MODIFY CONSTRAINT "' || c_name || '" DISABLE CASCADE;';

            

            -- Construct ENABLE command

            v_enable_sql  := 'ALTER TABLE "' || c_owner || '"."' || c_table_name || '" MODIFY CONSTRAINT "' || c_name || '" ENABLE;';

            -- Print SQL statements without auto-executing

            dbms_output.put_line('Disable SQL : ' || v_disable_sql);

            dbms_output.put_line('Enable SQL  : ' || v_enable_sql);

            -- Uncomment below when ready to execute automatically:

            -- EXECUTE IMMEDIATE REPLACE(v_disable_sql, ';', '');

            -- EXECUTE IMMEDIATE REPLACE(v_enable_sql, ';', '');

        END IF;

    END LOOP;

    CLOSE constraints;

    dbms_output.put_line('==============================================');

END;

/

SQL> SQL> ALTER TABLE "ANUJ"."EMP" MODIFY CONSTRAINT "FK_DEPTNO" DISABLE CASCADE;

Table altered.

SQL>  ALTER TABLE "ANUJ"."EMP" MODIFY CONSTRAINT "FK_DEPTNO" ENABLE;

Table altered.

SQL> ALTER TABLE "ANUJ"."EMP" MODIFY CONSTRAINT "PK_EMP" DISABLE CASCADE;

Table altered.

SQL> ALTER TABLE "ANUJ"."EMP" MODIFY CONSTRAINT "PK_EMP" ENABLE;

Table altered.

SQL>  ALTER TABLE "ANUJ"."DEPT" MODIFY CONSTRAINT "PK_DEPT" DISABLE CASCADE;

Table altered.

SQL> ALTER TABLE "ANUJ"."DEPT" MODIFY CONSTRAINT "PK_DEPT" ENABLE;

Table altered.

SQL>