Oracle 12c: Transportable Tablespaces
[oracle@vbgeneric ~]$ export TWO_TASK= sqlplus / as sysdba show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCL READ WRITE NO Move from tablespace>>>>>>>> 4 ORDS READ WRITE NO 5 ANUJ READ WRITE NO <<<<< To Move tablespace 7 ORCLDP READ WRITE NO set linesize 300 col FILE_NAME for a90 select FILE_ID,FILE_NAME from cdb_data_files ; FILE_ID FILE_NAME ---------- ------------------------------------------------------------------------------------------ 20 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_usertbs_dxk31j8j_.dbf 19 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_users_dxk31j7b_.dbf 18 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_sysaux_dxk31j9h_.dbf 21 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anuj01.dbf 17 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/anujo1_mf_system_dxk31j8o_.dbf 23 /u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/tts01_.dbf <<< datafile ****************** Demo ... SQL> alter session set container=orcl; Session altered. set linesize 200 pagesize 100 serveroutput on column "db details" format a100 select 'db_name: ' ||sys_context('userenv', 'db_name') || ' \cdb:-' ||(select cdb from v$database) || ' \auth_id: ' ||sys_context('userenv', 'authenticated_identity') || ' \user: ' ||sys_context('userenv', 'current_user') || ' \container:' ||nvl(sys_context('userenv', 'con_name'), 'non-cdb') "db details" from dual ; ---------------------------------------------------------------------------------------------------- db_name: orcl12c \cdb:-YES \auth_id: oracle \user: SYS \container:ORCL Create tablespace .. CREATE TABLESPACE tts datafile '/u01/app/oracle/oradata/vihcdbd/ANUJ/datafile/tts01_.dbf' size 1024M autoextend on maxsize unlimited ; Tablespace created. SQL> create user ttsuser identified by tts ; User created. SQL> grant connect,resource to ttsuser ; Grant succeeded. SQL> GRANT UNLIMITED TABLESPACE TO ttsuser; Grant succeeded. SQL> alter user ttsuser default tablespace tts ; User altered. Create table and row .. create table TTSUSER.table1_test ( EmpNO number(8), EmpDate date ) ; create table TTSUSER.table2_test ( Emp_name varchar2(20), EmpDate date ) ; declare i number; begin for i in 1..1000 loop insert into TTSUSER.table1_test values(i,sysdate); insert into TTSUSER.table2_test values ('Number is LAST'||i, sysdate); end loop; commit; end; SQL> select count(*) from TTSUSER.table1_test; COUNT(*) ---------- 1000 SQL> select count(*) from TTSUSER.table2_test ; COUNT(*) ---------- 1000 SQL> alter tablespace tts read only ; Tablespace altered. Now copy datafile Copy datafile now from one pluggable database(orcl) to another pluggable database (anuj) dir .. [oracle@vbgeneric ~]$ ls -ltr /u01/app/oracle/oradata/orcl12c/ANUJ/ total 2078520 -rw-r----- 1 oracle oinstall 20979712 May 1 2017 pdbseed_temp012016-06-02_07-10-28-AM.dbf -rw-r----- 1 oracle oinstall 5251072 Mar 25 11:45 users01.dbf -rw-r----- 1 oracle oinstall 272637952 Mar 25 14:58 system01.dbf -rw-r----- 1 oracle oinstall 775954432 Mar 25 14:58 sysaux01.dbf -rw-r----- 1 oracle oinstall 1073750016 Mar 25 15:00 tts01_.dbf >>>>>> cp /u01/app/oracle/oradata/orcl12c/orcl/tts01_.dbf /u01/app/oracle/oradata/orcl12c/ANUJ/ ls -ltr /u01/app/oracle/oradata/orcl12c/ANUJ/ total 2078580 -rw-r----- 1 oracle oinstall 20979712 Mar 25 15:06 pdbseed_temp012016-06-02_07-10-28-AM.dbf -rw-r----- 1 oracle oinstall 1073750016 Mar 25 15:07 tts01_.dbf <<<<<< -rw-r--r-- 1 oracle oinstall 1014 Mar 25 15:07 import.log -rw-r----- 1 oracle oinstall 5251072 Mar 26 02:22 users01.dbf -rw-r----- 1 oracle oinstall 775954432 Mar 26 03:05 sysaux01.dbf -rw-r----- 1 oracle oinstall 272637952 Mar 26 03:24 system01.dbf Now On diffrent pluggable database i.e. anuj SQL> alter session set container=anuj; Session altered. create directory TMP as '/u01/app/oracle/oradata/orcl12c/ANUJ'; create database link tts connect to system identified by sys using '//localhost/orcl'; Database link created. SQL> create user ttsuser identified by tts ; User created. SQL> grant connect,resource to ttsuser ; Grant succeeded. SQL> GRANT UNLIMITED TABLESPACE TO ttsuser; Grant succeeded. SQL> alter user ttsuser default tablespace tts ; User altered. impdp '"sys/vihaan@//localhost/anuj as sysdba"' network_link=tts directory=TMP TRANSPORT_TABLESPACES=TTS transport_datafiles=/u01/app/oracle/oradata/orcl12c/ANUJ/tts01_.dbf Import: Release 12.1.0.2.0 - Production on Sun Mar 25 15:04:45 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/********@//localhost/anuj AS SYSDBA" network_link=tts directory=TMP TRANSPORT_TABLESPACES=TTS transport_datafiles=/u01/app/oracle/oradata/orcl12c/ANUJ/tts01_.dbf Source time zone is +00:00 and target time zone is -07:00. Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sun Mar 25 15:07:55 2018 elapsed 0 00:03:01 SQL> alter session set container=anuj; Session altered. SQL> select count(*) from TTSUSER.table2_test; COUNT(*) ---------- 1000
Search This Blog
Total Pageviews
Monday, 26 March 2018
Oracle 12c: Transportable Tablespaces
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ► 2011 (362)
No comments:
Post a Comment