Duplicate a schema on the same DB with TTS

The procedure describes how to duplicate a schema using the datapump utility with TTS (Transportable Tablespaces).

Advantages: easy to copy all objects, without entering the application logic

Disadvantages: tablespaces to put readonly / offline during copying

C:\>set ORACLE_SID=TEST
C:\>set ORACLE_HOME=c:\oracle\11g2
C:\>set PATH=%ORACLE_HOME%\bin;%PATH%

C:\>sqlplus / as sysdba

SQL> CREATE TABLESPACE TBUSER1 DATAFILE 'c:\oracle\oradata\test\tbusers1.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> create user user1 identified by user1 default tablespace TBUSER1 temporary tablespace TEMP;

User created.

SQL> grant connect,resource to user1;

Grant succeeded.

SQL> connect user1/user1
Connected.
SQL> create table tabletest (id number, testo varchar(50));

Table created.

SQL> insert into tabletest select rownum, 'Name'||rownum from dual  connect by rownum<=100; 100 rows created. SQL> commit;

Commit complete.

C:\>sqlplus / as sysdba

SQL> exec dbms_tts.transport_set_check ('TBUSER1',TRUE,TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

SQL> alter tablespace TBUSER1 read only;

Tablespace altered.

expdp directory=DATA_PUMP_DIR transport_tablespaces=TBUSER1  dumpfile=TBUSER1.dmp logfile=TBUSER1.log

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Avvio di "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  / AS SYSDBA directory=DATA_PUMP_DIR transport_tablespaces=TBUSER1 dumpfile=TBUSER1.dmp logfile=TBUSER1.log 
Elaborazione dell object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Elaborazione dell object type TRANSPORTABLE_EXPORT/TABLE
Elaborazione dell object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Caricamento/Scaricamento della tabella principale "SYS"."SYS_EXPORT_TRANSPORTABLE_01" completato
******************************************************************************
File di dump impostato per SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\ORACLE\ADMIN\TEST\DPDUMP\TBUSER1.DMP
******************************************************************************
File di dati richiesti per la tablespace trasportabile TBUSER1:
  C:\ORACLE\ORADATA\TEST\TBUSERS1.DBF
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" completato in 23:28:16


C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Ven Feb 7 23:18:27 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  alter database datafile 'C:\ORACLE\ORADATA\TEST\TBUSERS1.DBF' offline;

Database altered.

SQL> host move C:\ORACLE\ORADATA\TEST\TBUSERS1.DBF C:\ORACLE\ORADATA\TEST\TBUSERS1A.DBF
        1 file spostato/i.

SQL> alter database rename file 'C:\ORACLE\ORADATA\TEST\TBUSERS1.DBF' to 'C:\ORACLE\ORADATA\TEST\TBUSERS1A.DBF';

Database altered.

SQL> host copy C:\ORACLE\ORADATA\TEST\TBUSERS1A.DBF C:\ORACLE\ORADATA\TEST\TBUSERS1.DBF
          1 file copiati.

SQL> alter database datafile 'C:\ORACLE\ORADATA\TEST\TBUSERS1A.DBF' online;

Database altered.

SQL> alter tablespace tbuser1 read write;

Tablespace altered.

SQL> alter tablespace tbuser1 rename to tbuser1a;

Tablespace altered.

SQL> select count(*) from user1.tabletest;

  COUNT(*)
----------
       100

SQL> create user user2 identified by user2 default tablespace users temporary tablespace TEMP;

impdp directory=DATA_PUMP_DIR dumpfile=TBUSER1.dmp logfile=TBUSER1.imp.log transport_datafiles='C:\ORACLE\ORADATA\TEST\TBUSERS1.DBF' remap_schema=user1:user2

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Caricamento/Scaricamento della tabella principale "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completato
Avvio di "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  / AS SYSDBA directory=DATA_PUMP_DIR dumpfile=TBUSER1.dmp logfile=TBUSER1.imp.log transport_datafiles='C:\ORACLE\ORADATA\TEST\TBUSERS1.DBF' remap_schema=user1:user2 
Elaborazione dell object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Elaborazione dell object type TRANSPORTABLE_EXPORT/TABLE
Elaborazione dell object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completato in 23:33:21

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Ven Feb 7 23:36:36 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  alter tablespace tbuser1 rename to tbuser2a;

Tablespace altered.

SQL> alter user user2 default tablespace tbuser2a;

User altered.

SQL> alter database datafile 'C:\ORACLE\ORADATA\TEST\TBUSERS1.DBF' offline;

Database altered.

SQL> host move C:\ORACLE\ORADATA\TEST\TBUSERS1.DBF C:\ORACLE\ORADATA\TEST\TBUSERS2A.DBF
        1 file spostato/i.

SQL>  alter database rename file 'C:\ORACLE\ORADATA\TEST\TBUSERS1.DBF' to 'C:\ORACLE\ORADATA\TEST\TBUSERS2A.DBF';

Database altered.

SQL>
SQL> alter database datafile 'C:\ORACLE\ORADATA\TEST\TBUSERS2A.DBF' online;

Database altered.

SQL> alter tablespace tbuser2a read write;

Tablespace altered.

SQL> select count(*) from user1.tabletest;

  COUNT(*)
----------
       100

SQL> select count(*) from user2.tabletest;

  COUNT(*)
----------
       100

SQL> set lines 180
SQL> select segment_name, tablespace_name, owner from dba_segments where owner in ('USER1','USER2');

SEGMENT_NAME                                                                      TABLESPACE_NAME        OWNER
--------------------------------------------------------------------------------- ------------------------------ 
TABLETEST                                                                         TBUSER1A               USER1
TABLETEST                                                                         TBUSER2A               USER2


SQL> select 'VITTORIA!!!!!' from dual;

VITTORIA!!!!!





1 thought on “Duplicate a schema on the same DB with TTS”

Leave a Comment