Thinking Out Loud

June 12, 2015

Linux Locking using flock

Filed under: linux,RMAN — mdinh @ 4:31 am

I am faced with a situation on how to create locking mechanism for RMAN backup.

Script dbf.sh backups database and can be run simutaneously if it’s not for the same database.

Hence dbf.sh sh running for PROD1 & PROD2 at the same time is VALID and running for PROD1 & PROD1 at the same time is NOT VALID.

While dbf.sh is running, arc.sh (backup archivelog) should not be running.

This was instigated by Laurent Schneider from his post on Can you restore from a full online backup?
http://laurentschneider.com/wordpress/2015/05/can-you-restore-from-a-full-online-backup.html

First test, dbf.sh is running and arc.sh should not.

The key is to have locking based on the database sid PROD1 and not the script.

$ crontab -l

#22 20 * * * /media/sf_working/sh/ogg_lag_sec.sh hawklas 0 > /tmp/ogg_lag_sec.sh.log 2>&1
#04 11 * * 2 [ $(date +\%d) -ge 07 ] && /home/oracle/t.sh > /tmp/t.log
* * * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 3600 /tmp/PROD1 /home/oracle/dbf.sh PROD1 >> /tmp/dbfPROD1.log 2>&1
* * * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 3600 /tmp/PROD2 /home/oracle/dbf.sh PROD2 >> /tmp/dbfPROD2.log 2>&1
* * * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 0 /tmp/PROD1 /home/oracle/arc.sh PROD1 >> /tmp/arcPROD1.log 2>&1
* * * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 0 /tmp/PROD2 /home/oracle/arc.sh PROD2 >> /tmp/arcPROD2.log 2>&1

Looks like dbf.sh was run and arc.sh was not.

$ ls -alrt /tmp/*PROD*

-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:22 /tmp/PROD1
-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:22 /tmp/PROD2
-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:22 /tmp/arcPROD1.log
-rw-r--r--. 1 oracle oinstall 77 Jun 11 20:22 /tmp/dbfPROD1.log
-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:22 /tmp/arcPROD2.log
-rw-r--r--. 1 oracle oinstall 77 Jun 11 20:22 /tmp/dbfPROD2.log

$ cat /tmp/dbfPROD1.log

Starting /home/oracle/dbf.sh PROD1 Thu Jun 11 20:22:01 PDT 2015
Sleeping 119

$ cat /tmp/dbfPROD2.log

Starting /home/oracle/dbf.sh PROD2 Thu Jun 11 20:22:01 PDT 2015
Sleeping 119

Continuing to monitor the process and arc.sh never ran since dbf.sh was always running.

$ ls -alrt /tmp/*PROD*

-rw-r--r--. 1 oracle oinstall   0 Jun 11 20:22 /tmp/PROD1
-rw-r--r--. 1 oracle oinstall   0 Jun 11 20:22 /tmp/PROD2
-rw-r--r--. 1 oracle oinstall   0 Jun 11 20:22 /tmp/arcPROD1.log
-rw-r--r--. 1 oracle oinstall   0 Jun 11 20:22 /tmp/arcPROD2.log
-rw-r--r--. 1 oracle oinstall 231 Jun 11 20:25 /tmp/dbfPROD2.log
-rw-r--r--. 1 oracle oinstall 231 Jun 11 20:25 /tmp/dbfPROD1.log

$ cat /tmp/dbfPROD1.log

Starting /home/oracle/dbf.sh PROD1 Thu Jun 11 20:22:01 PDT 2015
Sleeping 119
Starting /home/oracle/dbf.sh PROD1 Thu Jun 11 20:24:00 PDT 2015
Sleeping 119
Starting /home/oracle/dbf.sh PROD1 Thu Jun 11 20:25:59 PDT 2015
Sleeping 119

$ cat /tmp/dbfPROD2.log

Starting /home/oracle/dbf.sh PROD2 Thu Jun 11 20:22:01 PDT 2015
Sleeping 119
Starting /home/oracle/dbf.sh PROD2 Thu Jun 11 20:24:00 PDT 2015
Sleeping 119
Starting /home/oracle/dbf.sh PROD2 Thu Jun 11 20:25:59 PDT 2015
Sleeping 119

Looking good so far. But what happens when arc.sh is currently running and then dbf.sh is started?

It would be a shame to have dbf.sh backup died because arc.sh is running.

$ crontab -l

#22 20 * * * /media/sf_working/sh/ogg_lag_sec.sh hawklas 0 > /tmp/ogg_lag_sec.sh.log 2>&1
#04 11 * * 2 [ $(date +\%d) -ge 07 ] && /home/oracle/t.sh > /tmp/t.log
43 20 * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 0 /tmp/PROD1 /home/oracle/arc.sh PROD1 >> /tmp/arcPROD1.log 2>&1
43 20 * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 0 /tmp/PROD2 /home/oracle/arc.sh PROD2 >> /tmp/arcPROD2.log 2>&1
44 20 * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 3600 /tmp/PROD1 /home/oracle/dbf.sh PROD1 >> /tmp/dbfPROD1.log 2>&1
44 20 * * * [ $(date +\%d) -ge 07 ] && /usr/bin/flock -w 3600 /tmp/PROD2 /home/oracle/dbf.sh PROD2 >> /tmp/dbfPROD2.log 2>&1

From /usr/bin/flock -w 3600, this means wait up to 3600s before aborting dbf.sh

Let’s test this.

$ date

Thu Jun 11 20:43:06 PDT 2015

$ ls -alrt /tmp/*PROD*

-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:43 /tmp/PROD2
-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:43 /tmp/PROD1
-rw-r--r--. 1 oracle oinstall 78 Jun 11 20:43 /tmp/arcPROD2.log
-rw-r--r--. 1 oracle oinstall 78 Jun 11 20:43 /tmp/arcPROD1.log

$ cat /tmp/arcPROD1.log

Starting /home/oracle/arc.sh PROD1 Thu Jun 11 20:43:01 PDT 2015
Sleeping 135s

$ cat /tmp/arcPROD2.log

Starting /home/oracle/arc.sh PROD2 Thu Jun 11 20:43:01 PDT 2015
Sleeping 135s

arc.sh started at 20:43 and is sleeping for 135s while dbf.sh is scheduled to run at 20:44

20:43 + 135s would take us to 20:45:15 which is well after the scheduled time for dbf.sh at 20:44

Let’s see if this works.

$ date

Thu Jun 11 20:45:33 PDT 2015

$ ls -alrt /tmp/*PROD*

-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:43 /tmp/PROD2
-rw-r--r--. 1 oracle oinstall  0 Jun 11 20:43 /tmp/PROD1
-rw-r--r--. 1 oracle oinstall 78 Jun 11 20:43 /tmp/arcPROD2.log
-rw-r--r--. 1 oracle oinstall 78 Jun 11 20:43 /tmp/arcPROD1.log
-rw-r--r--. 1 oracle oinstall 75 Jun 11 20:45 /tmp/dbfPROD1.log
-rw-r--r--. 1 oracle oinstall 75 Jun 11 20:45 /tmp/dbfPROD2.log

$ cat /tmp/dbfPROD1.log

Starting /home/oracle/dbf.sh PROD1 Thu Jun 11 20:45:16 PDT 2015
Sleeping 1

$ cat /tmp/dbfPROD2.log

Starting /home/oracle/dbf.sh PROD2 Thu Jun 11 20:45:16 PDT 2015
Sleeping 1

dbf.sh started at 20:45:16 – 1 second after arc.sh completed.

Simple scripts used to test with and you will need to modify sleep time accordingly for each test case.

$ cat dbf.sh

echo "Starting $0 $*" `date`
echo "Sleeping 1"
sleep 1

$ cat arc.sh

echo "Starting $0 $*" `date`
echo "Sleeping 135s"
sleep 135

And there you have it.

Good Night.

Reference: https://ma.ttias.be/prevent-cronjobs-from-overlapping-in-linux/

Advertisements

June 9, 2015

Not Another dbms_redefinition Post

Filed under: 11g,oracle — mdinh @ 3:43 am

If you follow me on twitter at all, then you will realize I often rant about how demos are too simplistic and do not represent real world issues.

Please allow me to demonstrate how to partitioning an existing table using DBMS_REDEFINITION.

In the real world, how tables are there without foreign keys?

What happens if there are transactions underlying the table during redef?

What happened to the NOT NULL constraints?

I really like how Oracle adds TMP$$ to the object name to avoid collision.

Don’t remember seeing this before; however, my previous test case was not as thorough.

Hopefully, all these questions will be addressed below.

$ sqlplus hr/hr @redef.sql

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 8 19:50:56 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(HR@hawklas):PRIMARY> — Drop objects to make test case re-runable

ARROW:(HR@hawklas):PRIMARY> exec execute immediate 'drop table parent cascade constraint purge'; exception when others then null

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> exec execute immediate 'drop table parent_int cascade constraint purge'; exception when others then null

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> exec execute immediate 'drop table child purge'; exception when others then null

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> exec execute immediate 'drop materialized view log ON parent'; exception when others then null

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> exec execute immediate 'drop materialized view parent_int'; exception when others then null

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> /*
ARROW:(HR@hawklas):PRIMARY> drop table parent cascade constraint purge;
ARROW:(HR@hawklas):PRIMARY> drop table parent_int cascade constraint purge;
ARROW:(HR@hawklas):PRIMARY> drop table child purge;
ARROW:(HR@hawklas):PRIMARY> drop materialized view log ON parent;
ARROW:(HR@hawklas):PRIMARY> drop materialized view parent_int;
ARROW:(HR@hawklas):PRIMARY> */
ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.ABORT_REDEF_TABLE(uname=>USER,orig_table=>'PARENT',int_table=>'PARENT_INT');

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> --
ARROW:(HR@hawklas):PRIMARY> alter session enable parallel dml;

Session altered.

ARROW:(HR@hawklas):PRIMARY> alter session force parallel dml parallel 4;

Session altered.

ARROW:(HR@hawklas):PRIMARY> alter session force parallel query parallel 4;

Session altered.

ARROW:(HR@hawklas):PRIMARY> alter session set db_file_multiblock_read_count=128;

Session altered.

ARROW:(HR@hawklas):PRIMARY> — Create test case

ARROW:(HR@hawklas):PRIMARY> create table parent(id int not null, name varchar2(30) not null, dt date not null);

Table created.

ARROW:(HR@hawklas):PRIMARY> alter table parent add constraint pk_parent primary key(id) using index;

Table altered.

ARROW:(HR@hawklas):PRIMARY> create table child(id int not null, name varchar2(30) not null);

Table created.

ARROW:(HR@hawklas):PRIMARY> alter table child add constraint fk_parent foreign key (id) references parent(id);

Table altered.

ARROW:(HR@hawklas):PRIMARY> insert into parent values (1,'one',sysdate-100);

1 row created.

ARROW:(HR@hawklas):PRIMARY> insert into parent values (2,'two',sysdate-200);

1 row created.

ARROW:(HR@hawklas):PRIMARY> insert into parent values (3,'three',sysdate-300);

1 row created.

ARROW:(HR@hawklas):PRIMARY> insert into child values(1,'another one');

1 row created.

ARROW:(HR@hawklas):PRIMARY> commit;

Commit complete.

— Get DDL for table to redef.
$ sysdba @extract_table_ddl.sql

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 9 04:43:52 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',true);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> EXEC dbms_metadata.SET_TRANSFORM_PARAM(dbms_metadata.session_transform,'CONSTRAINTS',false);

PL/SQL procedure successfully completed.

ARROW:(SYS@hawklas):PRIMARY> -- SELECT dbms_metadata.get_ddl('TABLE','PARENT','HR') from dual;
ARROW:(SYS@hawklas):PRIMARY> set long 1000000 longchunksize 32000 linesize 32000 pages 0 newpage none
ARROW:(SYS@hawklas):PRIMARY> set heading off tab off echo off define off sqlprefix off blockterminator off timing off verify off feedb off

  CREATE TABLE "HR"."PARENT"
   (    "ID" NUMBER(*,0),
        "NAME" VARCHAR2(30),
        "DT" DATE
   ) ;

ARROW:(SYS@hawklas):PRIMARY> exit

UPDATE: Add using dbms_metadata to get DDL for table to be redef above.

What’s done in the real word, yes?

ARROW:(HR@hawklas):PRIMARY> — Create interval partition table for redef

ARROW:(HR@hawklas):PRIMARY> create table parent_int(id int, name varchar2(30), dt date)
  2  partition by range (dt)
  3  interval( numtoyminterval(1,'month'))
  4  (
  5  partition p0 values less than (to_date('2010-10-01', 'yyyy-mm-dd'))
  6  );

Table created.

ARROW:(HR@hawklas):PRIMARY> — Check table partitions

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, partition_name, tablespace_name, subpartition_count sub_ct, partition_position par_ct, interval int, composite com
  2  from USER_TAB_PARTITIONS where table_name='PARENT' order by partition_position asc;

no rows selected

ARROW:(HR@hawklas):PRIMARY> — Check table contraints

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, constraint_name, constraint_type, status, validated
  2  from USER_CONSTRAINTS where table_name in ('PARENT','CHILD') order by 1,3;

TABLE_NAME                     CONSTRAINT_NAME      C STATUS   VALIDATED
------------------------------ -------------------- - -------- -------------
CHILD                          SYS_C004145          C ENABLED  VALIDATED
CHILD                          SYS_C004146          C ENABLED  VALIDATED
CHILD                          FK_PARENT            R ENABLED  VALIDATED
PARENT                         SYS_C004141          C ENABLED  VALIDATED
PARENT                         SYS_C004142          C ENABLED  VALIDATED
PARENT                         SYS_C004143          C ENABLED  VALIDATED
PARENT                         PK_PARENT            P ENABLED  VALIDATED

7 rows selected.

ARROW:(HR@hawklas):PRIMARY> --
ARROW:(HR@hawklas):PRIMARY> desc parent;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER(38)
 NAME                                                                    NOT NULL VARCHAR2(30)
 DT                                                                      NOT NULL DATE

ARROW:(HR@hawklas):PRIMARY> desc child;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER(38)
 NAME                                                                    NOT NULL VARCHAR2(30)

ARROW:(HR@hawklas):PRIMARY> desc parent_int;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER(38)
 NAME                                                                             VARCHAR2(30)
 DT                                                                               DATE

ARROW:(HR@hawklas):PRIMARY> — Verify table can be redef and start redef

ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.CAN_REDEF_TABLE(uname=>USER,tname=>'PARENT',options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.START_REDEF_TABLE(uname=>USER,orig_table=>'PARENT',int_table=>'PARENT_INT',options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> — Copy dependencies from original to interim table
ARROW:(HR@hawklas):PRIMARY> — Using subsitution variable from SQL*Plus

ARROW:(HR@hawklas):PRIMARY> set autoprint on
ARROW:(HR@hawklas):PRIMARY> VARIABLE error_count number
ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.COPY_TABLE_DEPENDENTS(uname=>USER,orig_table=>'PARENT',int_table=>'PARENT_INT',num_errors=>:error_count,ignore_errors=>FALSE);

PL/SQL procedure successfully completed.


ERROR_COUNT
-----------
          0

ARROW:(HR@hawklas):PRIMARY> -- Check for errors
ARROW:(HR@hawklas):PRIMARY> SELECT count(*) FROM DBA_REDEFINITION_ERRORS;

  COUNT(*)
----------
         0

ARROW:(HR@hawklas):PRIMARY> SELECT object_name, base_table_name, ddl_txt FROM DBA_REDEFINITION_ERRORS;

no rows selected

ARROW:(HR@hawklas):PRIMARY> — Sync any outstanding transactions and finish redef

ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.SYNC_INTERIM_TABLE(uname=>USER,orig_table=>'PARENT',int_table=>'PARENT_INT');

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> EXEC dbms_redefinition.FINISH_REDEF_TABLE(uname=>USER,orig_table=>'PARENT',int_table=>'PARENT_INT');

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> --
ARROW:(HR@hawklas):PRIMARY> EXEC dbms_stats.GATHER_TABLE_STATS(user,tabname=>'PARENT',cascade=>true,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'for all columns size 1',degree=>8);

PL/SQL procedure successfully completed.

Notice table PARENT contains NULLABLE columns while PARENT_INT contains NOT NULL, just the oposite of what was created.

ARROW:(HR@hawklas):PRIMARY> desc parent;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                               NUMBER(38)
 NAME                                                                             VARCHAR2(30)
 DT                                                                               DATE

ARROW:(HR@hawklas):PRIMARY> desc child;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER(38)
 NAME                                                                    NOT NULL VARCHAR2(30)

ARROW:(HR@hawklas):PRIMARY> desc parent_int;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------------------------------------
 ID                                                                      NOT NULL NUMBER(38)
 NAME                                                                    NOT NULL VARCHAR2(30)
 DT                                                                      NOT NULL DATE

ARROW:(HR@hawklas):PRIMARY> — Check FK contraints

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, constraint_name, constraint_type, status, validated
  2  from USER_CONSTRAINTS where table_name in ('PARENT','CHILD') order by 1,3;

TABLE_NAME                     CONSTRAINT_NAME      C STATUS   VALIDATED
------------------------------ -------------------- - -------- -------------
CHILD                          SYS_C004145          C ENABLED  VALIDATED
CHILD                          SYS_C004146          C ENABLED  VALIDATED
CHILD                          TMP$$_FK_PARENT0     R DISABLED NOT VALIDATED
CHILD                          FK_PARENT            R ENABLED  NOT VALIDATED
PARENT                         SYS_C004142          C ENABLED  NOT VALIDATED
PARENT                         SYS_C004143          C ENABLED  NOT VALIDATED
PARENT                         SYS_C004141          C ENABLED  NOT VALIDATED
PARENT                         PK_PARENT            P ENABLED  VALIDATED

8 rows selected.

ARROW:(HR@hawklas):PRIMARY> SELECT constraint_name,constraint_type,table_name r_table,r_constraint_name r_constraint,
  2  status,bad,rely,validated,index_name,delete_rule
  3  from USER_CONSTRAINTS
  4  where r_constraint_name in (
  5  select constraint_name
  6  from USER_CONSTRAINTS
  7  where table_name like 'PARENT%'
  8  );

CONSTRAINT_NAME      C R_TABLE              R_CONSTRAINT         STATUS   BAD RELY VALIDATED     INDEX_NAME           DELETE_RU
-------------------- - -------------------- -------------------- -------- --- ---- ------------- -------------------- ---------
TMP$$_FK_PARENT0     R CHILD                TMP$$_PK_PARENT0     DISABLED          NOT VALIDATED                      NO ACTION
FK_PARENT            R CHILD                PK_PARENT            ENABLED           NOT VALIDATED                      NO ACTION

ARROW:(HR@hawklas):PRIMARY> — Enable validate non-FK constraints

ARROW:(HR@hawklas):PRIMARY> declare
  2    l_sql varchar2(1000);
  3  begin
  4    for x in (
  5      SELECT table_name, constraint_name from USER_CONSTRAINTS
  6      where table_name in ('PARENT','CHILD') and VALIDATED='NOT VALIDATED' and constraint_type'R'
  7    ) loop
  8      begin
  9        l_sql := 'alter table '||x.table_name||' enable validate constraint '||x.constraint_name;
 10        dbms_output.put_line (l_sql);
 11        execute immediate l_sql;
 12      exception when others then null;
 13      end;
 14  end loop;
 15  end;
 16  /
alter table PARENT enable validate constraint SYS_C004142
alter table PARENT enable validate constraint SYS_C004143
alter table PARENT enable validate constraint SYS_C004141

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> — Verify constraints

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, constraint_name, constraint_type, status, validated
  2  from USER_CONSTRAINTS where table_name in ('PARENT','CHILD') order by 1,3;

TABLE_NAME                     CONSTRAINT_NAME      C STATUS   VALIDATED
------------------------------ -------------------- - -------- -------------
CHILD                          SYS_C004145          C ENABLED  VALIDATED
CHILD                          SYS_C004146          C ENABLED  VALIDATED
CHILD                          TMP$$_FK_PARENT0     R DISABLED NOT VALIDATED
CHILD                          FK_PARENT            R ENABLED  NOT VALIDATED
PARENT                         SYS_C004142          C ENABLED  VALIDATED
PARENT                         SYS_C004143          C ENABLED  VALIDATED
PARENT                         SYS_C004141          C ENABLED  VALIDATED
PARENT                         PK_PARENT            P ENABLED  VALIDATED

8 rows selected.

ARROW:(HR@hawklas):PRIMARY> — Enable validate FK constraints

ARROW:(HR@hawklas):PRIMARY> declare
  2    l_sql varchar2(1000);
  3  begin
  4    for x in (
  5      SELECT table_name,constraint_name from user_constraints
  6      where r_constraint_name in (
  7      select constraint_name
  8      from USER_CONSTRAINTS
  9      where table_name = 'PARENT'
 10      )
 11      and status='ENABLED'
 12      and validated='NOT VALIDATED'
 13    ) loop
 14      begin
 15        l_sql := 'alter table '||x.table_name||' enable validate constraint '||x.constraint_name;
 16        dbms_output.put_line (l_sql);
 17        execute immediate l_sql;
 18      exception when others then null;
 19      end;
 20  end loop;
 21  end;
 22  /
alter table CHILD enable validate constraint FK_PARENT

PL/SQL procedure successfully completed.

ARROW:(HR@hawklas):PRIMARY> — Verify constraints

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, constraint_name, constraint_type, status, validated
  2  from USER_CONSTRAINTS where table_name in ('PARENT','CHILD') order by 1,3;

TABLE_NAME                     CONSTRAINT_NAME      C STATUS   VALIDATED
------------------------------ -------------------- - -------- -------------
CHILD                          SYS_C004145          C ENABLED  VALIDATED
CHILD                          SYS_C004146          C ENABLED  VALIDATED
CHILD                          TMP$$_FK_PARENT0     R DISABLED NOT VALIDATED
CHILD                          FK_PARENT            R ENABLED  VALIDATED
PARENT                         SYS_C004142          C ENABLED  VALIDATED
PARENT                         SYS_C004143          C ENABLED  VALIDATED
PARENT                         SYS_C004141          C ENABLED  VALIDATED
PARENT                         PK_PARENT            P ENABLED  VALIDATED

8 rows selected.

ARROW:(HR@hawklas):PRIMARY> — Verify partitions

ARROW:(HR@hawklas):PRIMARY> SELECT table_name, partition_name, tablespace_name, subpartition_count sub_ct, partition_position par_ct, interval int, composite com
  2  from USER_TAB_PARTITIONS where table_name='PARENT' order by partition_position asc;

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                    SUB_CT     PAR_CT INT COM
------------------------------ ------------------------------ ------------------------------ ---------- ---------- --- ---
PARENT                         P0                             USERS                                   0          1 NO  NO
PARENT                         SYS_P503                       USERS                                   0          2 YES NO
PARENT                         SYS_P502                       USERS                                   0          3 YES NO
PARENT                         SYS_P501                       USERS                                   0          4 YES NO

ARROW:(HR@hawklas):PRIMARY> — Verify index name

ARROW:(HR@hawklas):PRIMARY> SELECT index_name from USER_INDEXES where regexp_like (table_name,’^parent|^child’,’i’);

INDEX_NAME
——————————
TMP$$_PK_PARENT0
PK_PARENT

ARROW:(HR@hawklas):PRIMARY>

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
   uname                    IN  VARCHAR2,
   orig_table               IN  VARCHAR2,
   int_table                IN  VARCHAR2,
   copy_indexes             IN  PLS_INTEGER := 1,
   copy_triggers            IN  BOOLEAN     := TRUE,
   copy_constraints         IN  BOOLEAN     := TRUE,
   copy_privileges          IN  BOOLEAN     := TRUE,
   ignore_errors            IN  BOOLEAN     := FALSE,
   num_errors               OUT PLS_INTEGER,
   copy_statistics          IN  BOOLEAN     := FALSE, 
   copy_mvlog               IN  BOOLEAN     := FALSE); 

Why are Not Null Constraints not Copied by Dbms_redefinition.copy_table_dependents? (Doc ID 1089860.1)

Create a free website or blog at WordPress.com.