We will create new pluggable database using database link.
Create the user and grant privilege to create pluggable database in the source database:
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 READ WRITE NO
SYS@ORCL19 SQL> alter session set container=plug19;
Session altered.
SYS@ORCL19 SQL> create user adm identified by oracle1;
User created.
SYS@ORCL19 SQL> grant create session,create pluggable database to adm;
Grant succeeded.
Create test user/data:
SYS@ORCL19 SQL> create user test_user identified by oracle1;
User created.
SYS@ORCL19 SQL> create table test_user.test_table(a varchar2(20));
Table created.
SYS@ORCL19 SQL> alter user test_user quota unlimited on users;
User altered.
SYS@ORCL19 SQL> insert into test_user.test_table values('test');
1 row created.
SYS@ORCL19 SQL> commit;
Commit complete.
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 READ WRITE NO
SYS@ORCL19 SQL> alter session set container=plug19;
Session altered.
SYS@ORCL19 SQL> create user adm identified by oracle1;
User created.
SYS@ORCL19 SQL> grant create session,create pluggable database to adm;
Grant succeeded.
Create test user/data:
SYS@ORCL19 SQL> create user test_user identified by oracle1;
User created.
SYS@ORCL19 SQL> create table test_user.test_table(a varchar2(20));
Table created.
SYS@ORCL19 SQL> alter user test_user quota unlimited on users;
User altered.
SYS@ORCL19 SQL> insert into test_user.test_table values('test');
1 row created.
SYS@ORCL19 SQL> commit;
Commit complete.
Edit the tnsnames.ora file:
PLUG19 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PLUG19)
)
)
PLUG19 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PLUG19)
)
)
Create database link in the target database:
SYS@ORCL19 SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@ORCL19 SQL> create database link pdb_create connect to adm identified by oracle1 using 'PLUG19';
Database link created.
SYS@ORCL19 SQL> select * from dual@pdb_create;
D
-
X
SYS@ORCL19 SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@ORCL19 SQL> create database link pdb_create connect to adm identified by oracle1 using 'PLUG19';
Database link created.
SYS@ORCL19 SQL> select * from dual@pdb_create;
D
-
X
Create pluggable database:
In the target datase:
SYS@ORCL19 SQL> create pluggable database plug19_2 from plug19@pdb_create;
Pluggable database created.
SYS@ORCL19 SQL> alter pluggable database plug19_2 open read write;
Pluggable database altered.
Chek the test data:
SYS@ORCL19 SQL> alter session set container=plug19_2;
Session altered.
SYS@ORCL19 SQL> show con_name
CON_NAME
------------------------------
PLUG19_2
SYS@ORCL19 SQL> select * from test_user.test_table;
A
--------------------
test
In the target datase:
SYS@ORCL19 SQL> create pluggable database plug19_2 from plug19@pdb_create;
Pluggable database created.
SYS@ORCL19 SQL> alter pluggable database plug19_2 open read write;
Pluggable database altered.
Chek the test data:
SYS@ORCL19 SQL> alter session set container=plug19_2;
Session altered.
SYS@ORCL19 SQL> show con_name
CON_NAME
------------------------------
PLUG19_2
SYS@ORCL19 SQL> select * from test_user.test_table;
A
--------------------
test
Drop the new database:
SYS@ORCL19 SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 READ WRITE NO
4 PLUG19_2 READ WRITE NO
SYS@ORCL19 SQL> alter pluggable database plug19_2 close immediate;
Pluggable database altered.
SYS@ORCL19 SQL> drop pluggable database plug19_2 including datafiles;
Pluggable database dropped.
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 READ WRITE NO
SYS@ORCL19 SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 READ WRITE NO
4 PLUG19_2 READ WRITE NO
SYS@ORCL19 SQL> alter pluggable database plug19_2 close immediate;
Pluggable database altered.
SYS@ORCL19 SQL> drop pluggable database plug19_2 including datafiles;
Pluggable database dropped.
SYS@ORCL19 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PLUG19 READ WRITE NO