Oracle12C 数据库创建用户与数据表，CDB与PDB启动测试

对于12c之前的数据库创建用户方式，大家都会写创建语句。但是12c开始因为有了CDB和PDB的说法，实现扩展数据库，则创建用户方式则有所不同。

当12c数据库创建完成后，使用sqlplus  / as sysdba  方式登录数据库连接的是CDB，如果要创建用户则需要使用固定模式：

示例如下：

SQL> show parameter name


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      ora12c
db_unique_name                       string      ora12c
global_names                         boolean     FALSE
instance_name                        string      ora12c
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string
service_names                        string      ora12c
SQL> CREATE USER c##comm_ora12c IDENTIFIED BY comm_oracle DEFAULT TABLESPACE users;   


User created.


SQL> grant connect,resource to c##comm_ora12c;  用户成功创建并授权成功！

SQL>


Grant succeeded.


SQL> 

上述命令是创建了通用的CDB用户，注意必须使用c##开头。

查询授权：

SQL> col GRANTED_ROLE for a30
SQL> run
  1* select * from cdb_ROLE_PRIVS where GRANTED_ROLE='RESOURCE' AND GRANTEE='C##COMM_ORA12C'


GRANTEE              GRANTED_ROLE                   ADM DEF COM     CON_ID
-------------------- ------------------------------ --- --- --- ----------
C##COMM_ORA12C       RESOURCE                       NO  YES NO           1


SQL> select * from cdb_ROLE_PRIVS where GRANTED_ROLE='CONNECT' AND GRANTEE='C##COMM_ORA12C';


GRANTEE              GRANTED_ROLE                   ADM DEF COM     CON_ID
-------------------- ------------------------------ --- --- --- ----------
C##COMM_ORA12C       CONNECT                        NO  YES NO           1


SQL> 

SQL> conn c##comm_ora12c
Enter password: 
Connected.
SQL> show user
USER is "C##COMM_ORA12C"

SQL> create table test12c (id number(10,0) primary key,name varchar2(30));


Table created.


SQL>表成功创建

SQL> insert into TEST12C values(1,'测试12c');
insert into TEST12C values(1,'测试12c')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'




SQL> 此时无法插入数据！

SQL> alter user C##COMM_ORA12C QUOTA unlimited ON users TEMPORARY TABLESPACE temp;


User altered.


SQL> insert into TEST12C values(1,'测试12c');


1 row created.


SQL> commit;   数据插入成功！

通过上述实验可以看出12c 版本与之前数据库版本的不同之处。注意CDB与PDB之间关系！

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;


    CON_ID       DBID NAME                 OPEN_MODE
---------- ---------- -------------------- ----------
         2 4062021294 PDB$SEED             READ ONLY
         3 2255993317 PDB12C1              MOUNTED     
         4 1951738610 PDB12C2              MOUNTED


SQL>此时PDB还处于mount状态。

如果要启动PDB；

SQL> alter PLUGGABLE database PDB12C1 open;


Pluggable database altered.


SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;


    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4062021294 PDB$SEED                       READ ONLY
         3 2255993317 PDB12C1                        READ WRITE
         4 1951738610 PDB12C2                        MOUNTED


SQL> PDB12C1 成功启动，PDB12C2同理。

上述问题可以参考最新官方文档，找到解决方案！

官方文档叙述：

Examples

All of the following examples use the example tablespace, which exists in the seed database and is accessible to the sample schemas.

Creating a Database User: Example If you create a new user with PASSWORD EXPIRE, then the user's password must be changed before the user attempts to log in to the database. You can create the user sidney by issuing the following statement:

CREATE USER sidney 
    IDENTIFIED BY out_standing1 
    DEFAULT TABLESPACE example 
    QUOTA 10M ON example 
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON system 
    PROFILE app_user 
    PASSWORD EXPIRE;

The user sidney has the following characteristics:

• The password out_standing1

• Default tablespace example, with a quota of 10 megabytes

• Temporary tablespace temp

• Access to the tablespace SYSTEM, with a quota of 5 megabytes

• Limits on database resources defined by the profile app_user (which was created in "Creating a Profile: Example")

• An expired password, which must be changed before sidney can log in to the database

Creating External Database Users: Examples The following example creates an external user, who must be identified by an external source before accessing the database:

CREATE USER app_user1
   IDENTIFIED EXTERNALLY
   DEFAULT TABLESPACE example
   QUOTA 5M ON example
   PROFILE app_user;

The user app_user1 has the following additional characteristics:

• Default tablespace example

• Default temporary tablespace example

• 5M of space on the tablespace example and unlimited quota on the temporary tablespace of the database

• Limits on database resources defined by the app_user profile

To create another user accessible only by an operating system account, prefix the user name with the value of the initialization parameter OS_AUTHENT_PREFIX. For example, if this value is "ops$", then you can create the externally identified user external_user with the following statement:

CREATE USER ops$external_user
   IDENTIFIED EXTERNALLY
   DEFAULT TABLESPACE example
   QUOTA 5M ON example
   PROFILE app_user;     

Creating a Global Database User: Example The following example creates a global user. When you create a global user, you can specify the X.509 name that identifies this user at the enterprise directory server:

CREATE USER global_user
   IDENTIFIED GLOBALLY AS 'CN=analyst, OU=division1, O=oracle, C=US'
   DEFAULT TABLESPACE example
   QUOTA 5M ON example;

Creating a Common User in a CDB The following example creates a common user called c##comm_user in a CDB. Before you run this CREATE USER statement, ensure that the tablespaces example and temp_tbs exist in all of the containers in the CDB.

CREATE USER c##comm_user
   IDENTIFIED BY comm_pwd
   DEFAULT TABLESPACE example
   QUOTA 20M ON example
   TEMPORARY TABLESPACE temp_tbs;

The user comm_user has the following additional characteristics:

• The password comm_pwd

• Default tablespace example, with a quota of 20 megabytes

• Temporary tablespace temp_tbs