Primary key creation using dbms_metadata ddl and after impdp
>> Wednesday, 5 June 2013
I had a problem where code stopped working the same way after I had recreated a primary key for the developers. Initially they asked me to recreate an index, which I did. Then they asked me for a primary key so I created it using the index. Their code started to error where it had not done before..
What they should have asked for was a primary key not a primary key using an existing index. There is a big difference in the way they behave when you delete the key.
With a primary key that created it's own index, if you drop the key, the index goes with it.
If you create a primary key on an existing index, and then drop the key, the index stays in place, just the constraint gets dropped.
I'll show you how it works, but for a really great explanation, I also recommend you go have a look at http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/ as it explains it very well.
Here we go:
Create primary key allowing Oracle to create index
drop table tbl_test;
CREATE TABLE tbl_test ( col_1 NUMBER,col_2 NUMBER);
ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1);
select index_name, table_name from dba_indexes where index_name='TBL_TEST_PK';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
TBL_TEST_PK TBL_TEST
SELECT constraint_name, constraint_type, index_name FROM user_constraints
WHERE table_name = 'TBL_TEST';
CONSTRAINT_NAME C INDEX_NAME
------------------------------ - ------------------------------
TBL_TEST_PK P TBL_TEST_PK
This statement is the key bit of information about whether it will delete the index with the key drop or not, it;s explanation is at Jonathon Lewis' Scratchpad http://jonathanlewis.wordpress.com/2012/04/19/drop-constraint/
select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from sys.ind$ ind$, dba_objects do
where do.owner='SYSTEM'
and do.object_name = 'TBL_TEST_PK'
and do.object_id=ind$.obj#;
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID BITAND(IND$.PROPERTY,4096) PROPERTY
---------- -------------------------- ----------
TBL_TEST_PK
50284 4096 4097
Dropping the primary key means the automatically created index also gets dropped.
ALTER TABLE tbl_test DROP PRIMARY KEY;
Table altered.
select index_name, table_name from dba_indexes where index_name='TBL_TEST_PK';
no rows selected
SELECT constraint_name, constraint_type, index_name FROM user_constraints
WHERE table_name = 'TBL_TEST';
no rows selected
select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from sys.ind$ ind$, dba_objects do
where do.owner='SYSTEM'
and do.object_name = 'TBL_TEST_PK'
and do.object_id=ind$.obj#;
no rows selected
Create a primary key using an existing index
drop table tbl_test2;
CREATE TABLE tbl_test2 ( col_1 NUMBER, col_2 NUMBER);
CREATE UNIQUE INDEX idx_col_1 ON tbl_test2(col_1);
ALTER TABLE tbl_test2 ADD CONSTRAINT tbl_test2_pk PRIMARY KEY(col_1) USING
INDEX idx_col_1;
select index_name, table_name from dba_indexes where index_name='IDX_COL_1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
IDX_COL_1 TBL_TEST2
SELECT constraint_name, constraint_type, index_name FROM user_constraints
WHERE table_name = 'TBL_TEST2';
CONSTRAINT_NAME C INDEX_NAME
------------------------------ - ----------------
TBL_TEST2_PK P IDX_COL_1
select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from sys.ind$ ind$, dba_objects do
where do.owner='SYSTEM'
and do.object_name='IDX_COL_1'
and do.object_id=ind$.obj#;
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID BITAND(IND$.PROPERTY,4096) PROPERTY
---------- -------------------------- ----------
IDX_COL_1
50286 0 1
dropping the primary key will not delete the index
ALTER TABLE tbl_test2 DROP PRIMARY KEY;
select index_name, table_name from dba_indexes where index_name='IDX_COL_1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
IDX_COL_1 TBL_TEST2
SELECT constraint_name, constraint_type, index_name FROM user_constraints
WHERE table_name = 'TBL_TEST2';
no rows selected
select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from sys.ind$ ind$, dba_objects do
where do.owner='SYSTEM'
and do.object_name='IDX_COL_1'
and do.object_id=ind$.obj#;
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID BITAND(IND$.PROPERTY,4096) PROPERTY
---------- -------------------------- ----------
IDX_COL_1
50286 0 1
So now we understand how dropping the primary key affects the index it is based on, we know it makes a big difference how it is actually created.
Now look at what happens when the constraints are imported:
drop table tbl_test;
CREATE TABLE tbl_test ( col_1 NUMBER,
col_2 NUMBER);
ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1);
drop table tbl_test2;
CREATE TABLE tbl_test2 ( col_1 NUMBER, col_2 NUMBER);
CREATE UNIQUE INDEX idx_col_1 ON tbl_test2(col_1);
ALTER TABLE tbl_test2 ADD CONSTRAINT tbl_test2_pk PRIMARY KEY(col_1) USING INDEX idx_col_1;
select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from sys.ind$ ind$, dba_objects do
where do.owner='SYSTEM'
and do.object_name in ('TBL_TEST_PK','IDX_COL_1')
and do.object_id=ind$.obj#;
OBJECT_NAME
--------------------------------------------------------------
OBJECT_ID BITAND(IND$.PROPERTY,4096) PROPERTY
---------- -------------------------- ----------
IDX_COL_1
50290 0 1
TBL_TEST_PK
50288 4096 4097
CREATE DIRECTORY dpump_dir2 AS '/home/oracle';
vi myparfile
CONTENT=ALL
DIRECTORY=DPUMP_DIR2
REUSE_DUMPFILES=Y
TABLES=
system.tbl_test
system.tbl_test2
expdp system dumpfile=mytest.dmp logfile=mytest.log parfile=myparfile
drop table tbl_test;
drop table tbl_test2;
impdp system full=Y content=ALL directory=DPUMP_DIR2 dumpfile=mytest.dmp logfile=mytestimp.log
The tables, indexes and constraints are back:
select index_name, table_name from dba_indexes where index_name='TBL_TEST_PK';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
TBL_TEST_PK TBL_TEST
SELECT constraint_name, constraint_type, index_name FROM user_constraints WHERE table_name = 'TBL_TEST';
CONSTRAINT_NAME C INDEX_NAME
------------------------------ - ------------------------------
TBL_TEST_PK P TBL_TEST_PK
select index_name, table_name from dba_indexes where index_name='IDX_COL_1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
IDX_COL_1 TBL_TEST2
SELECT constraint_name, constraint_type, index_name FROM user_constraints WHERE table_name = 'TBL_TEST2';
CONSTRAINT_NAME C INDEX_NAME
------------------------------ - ------------------------------
TBL_TEST2_PK P IDX_COL_1
So far so good, bit now look:
select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from sys.ind$ ind$, dba_objects do
where do.owner='SYSTEM'
and do.object_name in ('TBL_TEST_PK','IDX_COL_1')
and do.object_id=ind$.obj#;
OBJECT_NAME
-------------------------------------------------------------------------------
OBJECT_ID BITAND(IND$.PROPERTY,4096) PROPERTY
---------- -------------------------- ----------
IDX_COL_1
50335 0 1
TBL_TEST_PK
50334 0 1
So this time the index is NOT dropped in either type of primary key when the primary key is dropped:
ALTER TABLE tbl_test DROP PRIMARY KEY;
select index_name, table_name from dba_indexes where index_name='TBL_TEST_PK';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
TBL_TEST_PK TBL_TEST
SELECT constraint_name, constraint_type, index_name FROM user_constraints WHERE table_name = 'TBL_TEST';
no rows selected
ALTER TABLE tbl_test2 DROP PRIMARY KEY;
Table altered.
select index_name, table_name from dba_indexes where index_name='IDX_COL_1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
IDX_COL_1 TBL_TEST2
SELECT constraint_name, constraint_type, index_name FROM user_constraints WHERE table_name = 'TBL_TEST2';
no rows selected
Impdp simply creates the keys as if they were created on existing indexes, it doesn't recreate them as they were built originlally. I think this sucks.
dbms_metadata doesn't get it right either:
dbms_metadata also doesn't understand that primary keys have been created differently, but it treats them the opposite way to datapump:
Create an inline primary key
create table test (
snorm number,
snurm varchar2(40),
snerm date,
snirm varchar2(4),
constraint test_pk primary key(snorm));
select dbms_metadata.get_ddl ('TABLE','TEST','SYSTEM') from dual;
CREATE TABLE "SYSTEM"."TEST"
( "SNORM" NUMBER,
"SNURM" VARCHAR2(40),
"SNERM" DATE,
"SNIRM" VARCHAR2(4),
CONSTRAINT "YINK_PK" PRIMARY KEY ("SNORM")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
select dbms_metadata.get_dependent_ddl('INDEX','TEST','SYSTEM') from dual;
CREATE UNIQUE INDEX "SYSTEM"."TEST_PK" ON "SYSTEM"."TEST" ("SNORM")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS"
Create a primary key on an existing index:
create table test2(
snorm number,
snurm varchar2(40),
snerm date,
snirm varchar2(4));
create index test2_idx on test2(snorm);
alter table test2 add constraint test2_pk primary key(snorm) using index test2_idx;
select dbms_metadata.get_ddl('TABLE','TEST2','SYSTEM') from
CREATE TABLE "SYSTEM"."TEST2"
( "SNORM" NUMBER,
"SNURM" VARCHAR2(40),
"SNERM" DATE,
"SNIRM" VARCHAR2(4),
CONSTRAINT "TEST2_PK" PRIMARY KEY ("SNORM")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
SQL> select dbms_metadata.get_dependent_ddl('INDEX','TEST2','SYSTEM') from dual;
CREATE INDEX "SYSTEM"."TEST2_IDX" ON "SYSTEM"."TEST2" ("SNORM")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS"
dbms_metadata produces the same ddl for both despite them being created very differently.
So using dbms_metadata to produce create database scripts could cause issues. And incase you are wondering Toad does the same thing.
So if developers code relies on the index being retained or not when they drop constraints (as they often do before large data loads) then recreating uat databases using these methods could store up a heap of trouble later.
*Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Further reading: https://nzdba.wordpress.com/tag/ind/
