This is a short post to document a bug i recently hit when doing tablespace point in time recovery in our VMWARE VCENTER database and also to show the importance of the trace event 10046 as a troubleshooting tool.
When executing tablespace point in time recovery a few days ago, although the target tablespace passed the full containment check (DBMS_TTS.TRANSPORT_SET_CHECK(‘VPX’,TRUE,TRUE) ) i get the error ORA-02449 at the end of the procedure.(Tested database 184.108.40.206.6)
After multiple manual check i decided to trace (10046 event ) the DDL “drop tablespace VPX including contents” to take look on what is going on behalf.
And here is one of the query that is used for doing some check before dropping the tablespace .
This query seem to check for dependencies between objects across tablespaces and return one row ! So i decided to execute it and check it’s output.The object referenced on the output (by obj#) was an IOT .After analyzing it structure and dependency it was easy to reproduce a simple test case.The cause of the problem lie on the fact that TABLESPACE_NAME for an IOT table is NULL (TS# = 0 ) so when checking for dependencies between objects (R constraint in this case) the table will be considered as located in another tablespace.
As a workaround i dropped the tablespace manually using “drop tablespace VPX including contents cascade constraints” and then finished the import of the transportable tablespace using IMPDP.
Hope that help ! That’s it 😀
Here is the test case :
CREATE TABLESPACE tablespace_test_drop; CREATE USER test_user IDENTIFIED BY "1254%$$gg" DEFAULT TABLESPACE tablespace_test_drop; ALTER USER test_user QUOTA UNLIMITED ON tablespace_test_drop; CREATE TABLE test_user.tab1 (CONTEXT_ID NUMBER (38) NOT NULL); CREATE UNIQUE INDEX test_user.pk_index ON test_user.tab1 (CONTEXT_ID) NOPARALLEL; ALTER TABLE test_user.tab1 ADD ( CONSTRAINT PK_CONS1 PRIMARY KEY (CONTEXT_ID) USING INDEX test_user.pk_index ENABLE VALIDATE); CREATE TABLE test_user.tab2 ( SAMPLE_ID NUMBER (38) NOT NULL, CONTEXT_ID NUMBER (38) NOT NULL, CONSTRAINT PK_CONS2 PRIMARY KEY (SAMPLE_ID) ENABLE VALIDATE ) ORGANIZATION INDEX PCTTHRESHOLD 50; ALTER TABLE test_user.tab2 ADD ( CONSTRAINT FK_CONS FOREIGN KEY (CONTEXT_ID) REFERENCES test_user.tab1 (CONTEXT_ID) ENABLE VALIDATE); DROP TABLESPACE tablespace_test_drop INCLUDING CONTENTS;