12c 初探--連線，啟動/關閉，建立使用者，資料字典等

Environment：

Windows 7 64, Virtualbox ：

Oracle Linux 6.4 64bit ---Unbreakable enterprise kernel

Oracle 12.1

This article covers:

1. Connecting to the root database.

2. Getting containers information in the system.

3. Switch between different containers.

4. Opening a PDB

5. Data dictionaries in the root and PDB.

6. Creating and granting privilege to a common user.

7. Renaming and dropping a PDB.

Connecting using OS authentication

Login linux as oracle, start Oracle listener, login oracle using sqlplus using OS authentication, and start the Oracle instance;

lsnrctl start.

sqlplus "/as sysdba"

startup

SQL>  show con_name

 CON_NAME

----------------------------------------------------

CDB$ROOT

Con_name is the current container name. In 12c multitenant，a container could be the prebuilt root database or a pluggable database which is created by user.

The root(cdb$root) is the parent of all pdbs, no pdb is the child of any other pdb. Every PDB is owned by SYS, which is a common user in the CDB, regardless of which user created the PDB.

Con_id of cdb$root(AKA: the root) is 1.

Question: how many containers in the system ? 

Answer: Login the root and query v$containers.

select con_id, name, open_mode from v$containers;

      CON_ID    NAME            OPEN_MODE

----------------    ----------------  --------------------------------------------

                   1     CDB$ROOT READ WRITE

                   2     PDB$SEED  READ ONLY

                   3     PDBORCL    MOUNTED

The PDB$SEED is a template for user to create its own pdb, pdb$seed is ALWAYS mounted in read only mode.

Pdborcl is created during the installation. By default, pdb(s) are mounted but not open for users when oracle instance starts. You have to manually put pdb(s) in open mode. 

Question: how to change current container：

Answer: 

alter session set container = PDBORCL;

Session altered.

SQL> show con_name;

 CON_NAME

------------------------------

PDBORCL

Question: how to put the pdb in open mode:

alter pluggable database pdborcl open;

alter pluggable database all open;

Or, put all pdbs in open mode immediately after the instance startup.

create or replace trigger Sys.After_Startup after startup on database

begin

   execute immediate 'alter pluggable database all open';

end;

Data dictionaries in the root and PDB

select con_id, name, open_mode from v$containers;

    CON_ID NAME                        OPEN_MODE

---------- ------------------------------ ----------

          3 PDBORCL                         MOUNTED

Remember when logged in as sys to the root, v$container shows us information of all containers. Now, we're constrained to the current container. The same rule applies to all v$xxx, cdb_xxx. 

This makes sense, because pdb users shold not have access to other pdb data unless he's authorized to. Let's examine the cdb_xxx and dba_xxx.

When you're connected to CDB$ROOT, there's a big different in what you can see in the dba_xxx and cdb_xxx. dba_xxx gives you information of cdb$root, while cdb_xxx the whole picture of all containers.

SQL> show con_name;

 CON_NAME

------------------------------

CDB$ROOT

SQL> select count(*), con_id from cdb_tablespaces group by con_id;

  COUNT(*)     CON_ID

---------- ----------

          3          2

          5          3

          5          1

SQL> select count(*) from dba_tablespaces;

  COUNT(*)

----------

          5

When you are connected to a PDB, the CDB_xxx or DBA_xxx views show the same information.

Question : how to create a common user and grant privilege to it?

Answer:

SQL> create user c##1 identified by c##1 container=all;

User created.

SQL> grant dba to c##1 container=all;

Grant succeeded.

SQL> connect c##1/c##1@pdborcl;

Connected.

Without the container clause, the creation and granting only applies to the current container -- the root. You're not able to create a common user in the PDB.

Alter user c##1 quota unlimited on users.

 It's a new privilege in 12c required to write to tablespaces.

Question: how to rename a PDB?

Answer: 

To rename a pdb, login as sysdba to pdb, put it in "open restricted" mode and run alter database command; finally close and open it.

sqlplus system/123456@pdborcl;

alter pluggable database pdborcl close immediate;

alter pluggable database pdborcl open restricted;

alter pluggable database pdborcl rename global_name to pdb3_bis;

alter pluggable database pdborcl close immediate;

alter pluggable database pdborcl open;

Question: how to drop a PDB:

 Answer:

 drop pluggable database pdborcl including datafiles;