12c中PROFILE在PDB和CDB中是公用的,不过创建的profile名称在CDB和PDB有所不同。
如:
1.CDB中创建Profile
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>CREATE PROFILE est_a LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
OMPOSITE_LIMIT 5000000;
CREATE PROFILE est_a LIMIT
*
ERROR at line 1:
ORA-65140: invalid common profile name
|
提示无效profile name,CDB中创建profile,名称需要c##开头和创建用户一样。当然这个规则也可以修改。
1
2
3
4
5
6
7
8
9
10
11
|
SQL>CREATE PROFILE c##test_b LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
9 COMPOSITE_LIMIT 5000000;
Profile created.
SQL>
|
1.1 CDB中查询profile
1
2
3
4
5
6
7
|
SQL> select distinct profile from dba_profiles;
PROFILE
--------------------------------------------------------------------------------
ORA_STIG_PROFILE
C##TEST_A
C##TEST_B
DEFAULT
|
1.2CDB中创建用户并指定新建的PROFILE
1
2
3
4
5
6
7
8
9
10
|
SQL> create user c##test identified by lei;
User created.
SQL> alter user c##test profile c##test_b;
User altered.
SQL> col username
for
a15
SQL> col profile
for
a25
SQL> select t.username,T.profile from dba_users t where t.username=
'C##TEST'
;
USERNAMEPROFILE
--------------- -------------------------
C##TEST C##TEST_B
|
2.PDB中创建用户并指定CDB中创建的profile
1
2
3
4
5
6
7
8
9
10
|
SQL> alter session set container=zhixin;
Session altered.
SQL> create user test_user identified by lei;
User created.
SQL> alter user test_user profile c##test_b;
User altered.
SQL> select t.username,T.profile from dba_users t where t.username=
'TEST_USER'
;
USERNAME PROFILE
--------------- -------------------------
TEST_USER C##TEST_B
|
使用都没有问题.
转:http://www.cndba.cn/Expect-le/article/271