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

 oracle whoami.sql?

whoami.sql

oracle 12c 
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 ;

-----


set linesize 200 pagesize 0 serveroutput on
column "db details" format a200
select
'user:\'||sys_context('userenv','session_user')||' session_id:\ '||'current_schema:\ '||sys_context('userenv','current_schema')||' instance_name:\ '||sys_context('userenv','instance_name')||
' database role: '||sys_context('userenv','database_role')||' os user:\ '||sys_context('userenv','os_user')||' client ip address:\ '||sys_context('userenv','ip_address')||' server hostname:\ '||sys_context('userenv','server_host')||' client hostname:\ '||sys_context('userenv','host') "db details"
from dual ;



-- From Web ...

set serveroutput on
begin
dbms_output.put_line('USER: ' ||sys_context('userenv','session_user'));
dbms_output.put_line('SESSION ID: ' ||sys_context('userenv','sid'));
dbms_output.put_line('CURRENT_SCHEMA: ' ||sys_context('userenv','current_schema'));
dbms_output.put_line('INSTANCE NAME: ' ||sys_context('userenv','instance_name'));
dbms_output.put_line('DATABASE ROLE: ' ||sys_context('userenv','database_role'));
dbms_output.put_line('OS USER: ' ||sys_context('userenv','os_user'));
dbms_output.put_line('CLIENT IP ADDRESS: '||sys_context('userenv','ip_address'));
dbms_output.put_line('SERVER HOSTNAME: ' ||sys_context('userenv','server_host'));
dbms_output.put_line('CLIENT HOSTNAME: ' ||sys_context('userenv','host'));
end;
/