Wednesday, May 17, 2006

How to check if a tablespace is transportable

In order to find out if a tablespace or a tablespace set is transportable you can use the package DBMS_TTS.

This is the interface of the package:
sys@10gr2 SQL> desc dbms_tts
PROCEDURE DOWNGRADE
FUNCTION ISSELFCONTAINED RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN
FULL_CHECK BOOLEAN IN
PROCEDURE KCP_CKCMP
FUNCTION TRANSPORT_CHAR_SET_CHECK RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
TARGET_DB_CHAR_SET_NAME VARCHAR2 IN
TARGET_DB_NCHAR_SET_NAME VARCHAR2 IN
ERR_MSG VARCHAR2 OUT
PROCEDURE TRANSPORT_CHAR_SET_CHECK_MSG
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
TARGET_DB_CHAR_SET_NAME VARCHAR2 IN
TARGET_DB_NCHAR_SET_NAME VARCHAR2 IN
PROCEDURE TRANSPORT_SET_CHECK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TS_LIST CLOB IN
INCL_CONSTRAINTS BOOLEAN IN DEFAULT
FULL_CHECK BOOLEAN IN DEFAULT


This is how you use the package:
sys@10gr2 SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('LUTZ_TS', TRUE)
PL/SQL procedure successfully completed.

sys@10gr2 SQL> exec dbms_tts.TRANSPORT_SET_CHECK('SYSTEM', TRUE)
BEGIN dbms_tts.TRANSPORT_SET_CHECK('SYSTEM', TRUE); END;

*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_SYS_SQL", line 899
ORA-06512: at "SYS.DBMS_SQL", line 19
ORA-06512: at "SYS.DBMS_TTS", line 838
ORA-29351: can not transport system, sysaux, or temporary tablespace 'SYSTEM'
ORA-06512: at "SYS.DBMS_TTS", line 867
ORA-06512: at line 1

After you have checked the tablespace with the procedure TRANSPORT_SET_CHECK you can use a table call transport_set_violations which is owned by SYS to look at error messages from the procedure.
This table will contain the messages from the last run of the procedure and it will retained only for the duration of the session.
In other words, it will be refreshed with every new session.

sys@10gr2 SQL> desc transport_set_violations
Name Null? Type
----------------------------------------- -------- ---------------------------- VIOLATIONS VARCHAR2(2000)



Now SYS creates a table in the tablespace I want to check ...
sys@10gr2 SQL> create table test_sys tablespace lutz_ts as select * from hr.test;
Table created.

And I check if the tablspace is transportable...

sys@10gr2 SQL> exec dbms_tts.TRANSPORT_SET_CHECK('LUTZ_TS', TRUE)
PL/SQL procedure successfully completed.

sys@10gr2 SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------Sys owned object TEST_SYS in tablespace LUTZ_TS not allowed in pluggable set


This would be the error message if the tablespace is not self containing...

sys@10gr2 SQL> sys@10gr2 SQL> select * from transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------
Constraint TESXT_FK between table SYS.TEST_SYS in tablespace USERS and table HR.
TEST in tablespace LUTZ_TS

Starting with Oracle 10gR2 we can create transportable tablespaces from backup.

2 Comments:

At Wed May 17, 11:19:00 PM, Blogger Alex Gorbachev said...

Hi Lutz,
Very good overview! Just a small addition: if one needs to specify more than one tablespace, they should be separated by space. More details on the last two parameters for TRANSPORT_SET_CHECK can be found in "Administrator's Guide, Chapter 8" (there is a reasonable explanation for difference between full and self contained set) and, of course, in "PL/SQL Packages and Types Reference".
Regards,
Alex

 
At Fri May 19, 11:40:00 AM, Blogger lutz_hartmann said...

Hi Alex,
thank you very much for your feedback and for pointing this out!
I am very happy if this blog becomes something like a discussion forum.
Comments are welcome any time of course!

 

Post a Comment

<< Home