How to transport a tablespace from 9i to 11g

It has been a while since I’ve written here. Main reason: Oracle Corp aquired me! Anyway, today on the menu we have a very interesting thing:

“How to transport a tablespace from 9i to 11g”.

It’s easy. No science here. You just have to follow the basics steps of the regular from 9i to 9i TTS procedure. I’ll reproduce the drill here of my testings with 9.2.0.1 to 11.1.0.6.

SQL> create tablespace lmc datafile ‘c:\oracle\oradata\orc9\lmc_01.dbf’ size 10m;

Tablespace criado.

SQL> create table mytable tablespace lmc as

2  select * from dba_objects where rownum < 101;

Tabela criada.

SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => ‘LMC’, incl_constraints => TRUE);

Procedimento de PL/SQL concluφdo com Ωxito.

SQL> SELECT * FROM transport_set_violations;

VIOLATIONS

——————————————————————————–

Sys owned object  MYTABLE in tablespace LMC not allowed in pluggable set

– ooooopsie dasy

SQL> drop table mytable;

Tabela suprimida.

SQL> conn system/oracle

Ligado.

SQL> create table mytable tablespace lmc as

2  select * from dba_objects where rownum < 101;

Tabela criada.

SQL> conn sys as sysdba

Introduzir senha:

Ligado.

SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => ‘LMC’, incl_constraints => TRUE);

Procedimento de PL/SQL concluφdo com Ωxito.

SQL> SELECT * FROM transport_set_violations;

nπo foram seleccionadas linhas

– Cool, we can go on

exp USERID=’sys/oracle AS SYSDBA’         TRANSPORT_TABLESPACE=y         TABLESPACES=LMC    FILE=C:\oracle\oradata\orc9\lmc_tt.dmp

SQL> alter tablespace lmc read only;

Tablespace alterado.

——- On Database 11G ——

Copy the file “lmc_01.dbf” to: “c:\oracle\oradata\orco”

imp USERID=’sys/oracle AS SYSDBA’   TRANSPORT_TABLESPACE=y     DATAFILES=’C:\oracle\oradata\orco\LMC_01.DBF’   TABLESPACES=lmc FILE=C:\oracle\oradata\orc9\lmc_tt.dmp

SQL> alter tablespace lmc read write;

Tablespace alterado.

SQL> conn system/oracle

Ligado.

SQL> desc mytable

Nome                                      Nulo?    Tipo

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

OWNER                                              VARCHAR2(30)

OBJECT_NAME                                        VARCHAR2(128)

SUBOBJECT_NAME                                     VARCHAR2(30)

OBJECT_ID                                          NUMBER

DATA_OBJECT_ID                                     NUMBER

OBJECT_TYPE                                        VARCHAR2(18)

CREATED                                            DATE

LAST_DDL_TIME                                      DATE

TIMESTAMP                                          VARCHAR2(19)

STATUS                                             VARCHAR2(7)

TEMPORARY                                          VARCHAR2(1)

GENERATED                                          VARCHAR2(1)

SECONDARY                                          VARCHAR2(1)

SQL> select count(*) from mytable;

COUNT(*)

———-

100

Bingo. Has if by magic the table arrives at the 11g database untouched!

Hope you liked it.

LMC.

About these ads