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 multitenanta 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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/638844/viewspace-768316/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/638844/viewspace-768316/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值