Create a pluggable database

dba oracle montreal create pdb oracle12c
 

How to create a pluggable database?

One of my clients in Montreal wants to create an empty pluggable database in a multitenant Oracle database. How can I do that?

In this example, I will not use the plug/unplug/clone features.

tnsnames.ora:

# PDB:
MY-PDB-NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my-hostname.ca)(PORT = 1525))
)
(CONNECT_DATA =
(SERVICE_NAME = my-pdb-name.ca)
(SERVER = DEDICATED)
)
)

create pluggable database MY-PDB-NAME admin user PDBA identified by xxxxx
file_name_convert = ('/u02/oradata/CDB-NAME/seed','/u02/oradata/CDB-NAME/MY-PDB-NAME');

alter pluggable database MY-PDB-NAME open;

alter session set container=MY-PDB-NAME;

alter system set listener_networks = '((NAME=net1)(LOCAL_LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=my-hostname.ca)(PORT=1525)))))';

-- Restart the listener on 1525 port 


-- Create the user and the index tablespace

CREATE BIGFILE TABLESPACE MY-PDB-NAME_DATA DATAFILE
 '/u02/oradata/CDB-NAME/MY-PDB-NAME/my-pdb-name_data.dbf' SIZE 256M
AUTOEXTEND ON NEXT 256M MAXSIZE 10G
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
--BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE BIGFILE TABLESPACE MY-PDB-NAME_INDEX DATAFILE
 '/u02/oradata/CDB-NAME/MY-PDB-NAME/my-pdb-name_index.dbf' SIZE 256M
AUTOEXTEND ON NEXT 256M MAXSIZE 10G
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
--BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;


-- Create the application user
CREATE USER MY-PDB-NAME
 IDENTIFIED BY xxxxx
 DEFAULT TABLESPACE MY-PDB-NAME_DATA
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;
 
 -- 2 Roles for MY-PDB-NAME
 GRANT CONNECT TO MY-PDB-NAME;
 GRANT RESOURCE TO MY-PDB-NAME;
 GRANT SELECT_CATALOG_ROLE TO MY-PDB-NAME;
 ALTER USER MY-PDB-NAME DEFAULT ROLE ALL;
 
 -- 1 System Privilege for MY-PDB-NAME
 GRANT UNLIMITED TABLESPACE TO MY-PDB-NAME;
 
 -- 2 Tablespace Quotas for MY-PDB-NAME
 ALTER USER MY-PDB-NAME QUOTA UNLIMITED ON MY-PDB-NAME_DATA;
 ALTER USER MY-PDB-NAME QUOTA UNLIMITED ON MY-PDB-NAME_INDEX;

GRANT CREATE ANY VIEW TO MY-PDB-NAME;


-- Test the connection to the pluggable database:
sqlplus MY-PDB-NAME/xxxxx@MY-PDB-NAME
Connected.
SQL>REM THE END

Author: Vincent Fenoll
Compatible: Oracle 12c with the multitenant option.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.