oracle cdb 创建用户,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: ExampleIf 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: ExamplesThe 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: ExampleThe 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 CDBThe 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