12c 初探--连接，启动/关闭，创建用户，数据字典等

Environment

Windows 7 64, Virtualbox ：

Oracle Linux 6.4 64bit ---Unbreakableenterprise kernel

Oracle 12.1

This article covers

1. Connecting to the root database.

2. Getting containers information in thesystem.

3. Switch between different containers.

4. Opening a PDB

5. Data dictionaries in the root and PDB.

6. Creating and granting privilege to acommon 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 Oracleinstance;

lsnrctl start.

sqlplus "/as sysdba"

startup

SQL<  show con_name

CON_NAME

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

CDB$ROOT

Con_name is the current container name. In12c multitenant，a container could be the prebuilt root database or apluggable database which is created by user.

The root(cdb$root) is the parent of allpdbs, no pdb is the child of any other pdb. Every PDB is owned by SYS, whichis 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 thesystem ? 

Answer: Login the root and queryv$containers.

select con_id, name, open_mode fromv$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 tocreate its own pdb, pdb$seed is ALWAYS mounted in read only mode.

Pdborcl is created during theinstallation. By default, pdb(s) are mounted but not open for users when oracleinstance 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 immediatelyafter the instance startup.

create or replace triggerSys.After_Startup after startup on database

begin

   execute immediate 'alterpluggable database all open';

end;

Data dictionaries in the root and PDB

select con_id, name, open_mode fromv$containers;

    CON_ID NAME                        OPEN_MODE

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

          3PDBORCL                         MOUNTED

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

This makes sense, because pdb users sholdnot have access to other pdb data unless he's authorized to. Let's examine thecdb_xxx and dba_xxx.

When you're connected to CDB$ROOT, there'sa big different in what you can see in the dba_xxx and cdb_xxx. dba_xxx givesyou 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 fromcdb_tablespaces group by con_id;

  COUNT(*)     CON_ID

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

          3          2

          5          3

          5          1

SQL< select count(*) fromdba_tablespaces;

  COUNT(*)

----------

          5

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

Question : how to create a common user andgrant privilege to it?

Answer:

SQL< create user c##1 identified byc##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 creationand granting only applies to the current container -- the root. You're not ableto 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 closeimmediate;

alter pluggable database pdborcl openrestricted;

alter pluggable database pdborcl renameglobal_name to pdb3_bis;

alter pluggable database pdborcl closeimmediate;

alter pluggable database pdborcl open;

Question: how to drop a PDB:

Answer:

drop pluggable database pdborcl includingdatafiles;