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

3 thoughts on “How to transport a tablespace from 9i to 11g

  1. Hi Moreno ,

    i found a little typo …

    the first “SQL> alter tablespace lmc read write;”
    should be “SQL> alter tablespace lmc read only;”

    best regards ,

    Marc

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