Tablespace point in time recovery and IOT : ORA-02449

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 12.1.0.2.6)

Capture

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 .

capture 2

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.

capture 2

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;

3 thoughts on “Tablespace point in time recovery and IOT : ORA-02449

  1. Thank you Suntrupth 🙂

    I just re-executed the query “select NULL from (select obj# objnum# from tab$ where ts#=:1 and bitand( …. ” as showed in the screenshot after replacing “NULL” with “tin.objnum#” to get the “obj#”.

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