Thinking Out Loud

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)

Advertisements

3 Comments »

  1. And as always stupid bug:
    Bug 12765293 – ORA-600 [kkzuord_copycolcomcb.2.prepare] or [kkzuord_copycolcomcb.2.exec] During DBMS_REDEFINITION of Table with Comments (Doc ID 1339277.1)

    Comment by goryszewskig — June 9, 2015 @ 4:46 pm | Reply

    • Greg! How are you? Thanks for stopping by and sharing.

      Comment by mdinh — June 10, 2015 @ 1:25 am | Reply

  2. All good Michael all good . Love Your blog .
    Regards
    GG

    Comment by goryszewskig — June 13, 2015 @ 5:34 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: