PRACTICE COMMANDS

SQL>select name,network_name,pdb from cdb_services;

SQL>select sys_context ('USRENV','CON_NAME') from dual;

Connecting with a Net Service Name

SQL>connect c##dba@cdb1

SQL>show con_name

SQL>show con_id

SQL>select name from v$services

CONNECTING PDB

SQL conn system/syed@pdb1

SQL>connect sys@pdb1 as sysdba

SQL>alter session set container=pdb4;

SQL>alter session set container=CDB$ROOT;

SQL>alter session set container=PDB$SEED;

CHECK THE NAME AND STATUS OF DATABASES 

SQL>select name, open_mode from v$pdbs;

SQL>alter pluggable database pdb3 open read only restricted;

SQL>alter pluggable database pdb1,pdb4  open read write;

SQL>alter pluggable database all open read write;

SQL>alter pluggable database all except pdb3 close immediate;

LOGIN TO DIFFERENT PDB

SQL>conn sys/syed@pdb3 as sysdba

=========================================================

TABLE SPACE MANAGEMENT

SQL> alter pluggable database datafile '/u02/app/oradata/pdb1/pdb1_users01.dbf' online;

SQL> alter pluggable database datafile '/u02/app/oradata/pdb1/pdb1_users01.dbf' offline

SQL> alter pluggable database default pdb1_test1;

We can learn datafiles in the container and all pdbs.Note that file# is unique value. And con_id means that which pluggable database have this datafile.

SQL> select con_id,file#,name from v$datafile;

SQL>select con_id,name,open_mode from v$pdbs;

SQL>select con_id, file#,name, from v$datafile where con_id=5;

SQL> select name from v$tempfile;

How to Tell If the Database Is a CDB

Query the V$DATABASE view to find out if a database is a CDB. If the database is a CDB, you’ll see YES as the value for the CDB column and NO otherwise. Here’s an example:

SQL>conn / as sysdba

SQL>select name, con_id, cdb from v$database;

Viewing Information About Containers

A common user whose current container is the root can view information about all containers in a CDB by querying the V$CONTAINERS view, as shown here:

image

If the common user’s current container is a PDB, only that PDB’s information is shown.

SQL>select name, con_id , db_id from v$containers;

Viewing Information About the PDBs

Query the CDA_PDB and DBA_PDB views to get information about the PDBs associated with a CDB. A common user with the current container as root must run this query. Here’s an exampl

SQL> select pdb_id,pdb_name, status from cdb_pdbs;

Finding the Open Mode of a PDB

Use the V$PDBS view to find information about the PDBs associated with the database instances, such as the open mode of each PDB. Here’s an example:

image

SQL>select name, open_mode, restricted,open_time from v$pdbs;

How to Find the Current Container ID and Name

You can find the current container ID by using the SHOW CON_ID command:

SQL>show con_id;

SQL>show con_name

Viewing the History of PDBs

The CDB_PDB_HISTORY view shows information about a PDB’s history, such as the operations performed in a PDB in the past (the column OPERATION) and the source PDB for a cloned PDB (column CLONED_FROM_PDB_NAME).

SQL>select db_name, con_id pdb_name, operation , cloned_from_pdb_name from cdb_pdb_history;

SQL>select tablespaces_name, con_id from cdb_tablespaces;

SQL>select file_name from dba_data_files;

SQL>

Modifying a PDB’s Settings

Use the ALTER PLUGGABLE DATABASE statement to modify a PDB’s settings. You must first connect to the PDB before issuing this command. Here are a few examples:

SQL>connect sys@pdb1 as sysdba

SQL>alter pluggable database datafile '/u02/oradata/pdb1_01.dbf/' offline;

SQL>alter pluggable database default tablespace pdb_tbs;

SQL>alter pluggable database storage (maxsize 10G);