Checking the tablespace for self-contained..obstacles during transport

In my blog TRANSPORT TABLESPACE MADE SAFE AND EASY... I have explained how to transport a tablespace among databases. But before transport any tablespace you must ensure that it should be self-contained.

 

What the hell is self-contained?

It means it should not contain any objects that depend on other objects outside the table space or exists on another table space.

For example: A table may be existing in one table space but its associated index exists on another tablespace.

A self contained tablespace can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure. The TS_LIST parameter accepts a comma separated list of tablespace names

Let’s see how to tackle with this?

Creating tablespace ttbs1:

Create tablespace ttbs1 datafile ‘D:\oracle\oradata\noida\DATAFILES\ttbs1.dbf’ size 5m;

Creating tablespace ttbs2:

Create tablespace ttbs2 datafile ‘D:\oracle\oradata\noida\DATAFILES\ttbs2.dbf’ size 5m;

Now create table invent in tablespace ttbs1:

SQL> create table scott.invent(id number) tablespace ttbs1;

Table created.

Here ,adding constraint primary pkey_id will create index on tablespace ttbs2:

SQL> alter table scott.invent add constraint pkey_id primary key(id) using index

tablespace ttbs2;

Table altered.

Checking tablespace for self-contained:

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('ttbs1',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

Here  you see the violation because index scott.pkey_id exist on tablespace ttbs2 but its corresponding table exist on tablespace ttbs1.

 

VIOLATIONS

--------------------------------------------------------------------------------

 

ORA-39908: Index SCOTT.PKEY_ID in tablespace TTBS2 enforces primary constraints

 of table SCOTT.INVENT in tablespace TTBS1.

Now ,a big question how to solve this issue..the solution is to move the index from tablespace ttbs2 to ttbs1.lets see how it is going to be done..

SQL> alter index scott.pkey_id rebuild tablespace ttbs1;

Index altered.

SQL> execute DBMS_TTS.TRANSPORT_SET_CHECK ('ttbs1',TRUE);

PL/SQL procedure successfully completed.

You can clearly see that the tablespace ttbs1 is now self-contained as there is no violations appearing.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

 

ENJOYJ