今天一客户说在做impdp导入时,报错Ora-28031。我们看看ora-28031的报错是什么意思:
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-28031: maximum of 148 enabled roles exceeded
从报错信息看,是SYS用户的role超过148个,一开始,我们用
select count(*) from dba_role_privs where grantee = 'SYS';
进行,查询,发现远远不到148个,后来,查询到文档 DataPump Import Errors With Repeated ORA-39083 ORA-31625 And ORA-28031 During OBJECT_GRANTS (Doc ID 1101035.1) 发现,原来不仅仅是直接的role,级联的role也会被计算在内,即需要用下面的sql进行查询:
select lpad (' ', 2*level, ' ')||granted_role
from dba_role_privs
where default_role = 'YES'
start with grantee = 'SYS'
connect by prior granted_role = grantee;
from dba_role_privs
where default_role = 'YES'
start with grantee = 'SYS'
connect by prior granted_role = grantee;
我们看到,如果算级联的role的话,SYS就有超过148个的role,因此再用impdp导入的时候,会报错ora-28031。
根据文档建议,可以在导出前,在源库删除部分role,或者导入时 EXCLUDE=ROLE。
另外,我们还可以这样做,
(1)备份原来default role的权限
select 'alter user sys default role '||wm_concat(granted_role)||';' from dba_role_privs
where grantee = 'SYS' and default_role='YES';
where grantee = 'SYS' and default_role='YES';
(2)直接设置:
alter user SYS default roles DBA;
(3)导出
(4)impdp导入后恢复,用第一步记录的SQL恢复
一般情况下,引起Ora-28031的role的往往是SYS,但是在极端情况下,也有可能是别的role,写了如下一段,可以检查,并备份超过148个role的授权语句。
set serveroutput on
set line 5000
DECLARE
v_count varchar2( 20);
v_sql varchar2(2000 );
BEGIN
DBMS_OUTPUT.put_line( '###################### REPORT START HERE #######################');
DBMS_OUTPUT.put_line( chr(9 ));
DBMS_OUTPUT.put_line( chr(9 ));
DBMS_OUTPUT.put_line( '============= The number of recursive role ================');
FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
select count (lpad( ' ', 2 * level, ' ') || granted_role)
into v_count
from dba_role_privs
where default_role = 'YES'
start with grantee = employee_rec.GRANTEE
connect by prior granted_role = grantee;
if v_count >= 148 then
DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
lpad(v_count, 5 , '.') ||
' <=== Cause ORA-28031');
else
DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
lpad(v_count, 5 , '.'));
end if ;
END LOOP;
DBMS_OUTPUT.put_line( chr(9 ));
DBMS_OUTPUT.put_line( '================== Use SQL to backup =====================');
FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
select count (lpad( ' ', 2 * level, ' ') || granted_role)
into v_count
from dba_role_privs
where default_role = 'YES'
start with grantee = employee_rec.GRANTEE
connect by prior granted_role = grantee;
if v_count >= 148 then
select 'alter user ' || employee_rec.GRANTEE ||' default role ' || wm_concat(granted_role) || ';' ||chr( 9)
into v_sql
from dba_role_privs
where grantee = employee_rec.GRANTEE
and default_role = 'YES' ;
DBMS_OUTPUT.put_line( substr(v_sql,1 ,2000));
end if ;
END LOOP;
DBMS_OUTPUT.put_line( chr(9 ));
DBMS_OUTPUT.put_line( chr(9 ));
DBMS_OUTPUT.put_line( '######################## REPORT END HERE #######################');
END;
/
set line 5000
DECLARE
v_count varchar2( 20);
v_sql varchar2(2000 );
BEGIN
DBMS_OUTPUT.put_line( '###################### REPORT START HERE #######################');
DBMS_OUTPUT.put_line( chr(9 ));
DBMS_OUTPUT.put_line( chr(9 ));
DBMS_OUTPUT.put_line( '============= The number of recursive role ================');
FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
select count (lpad( ' ', 2 * level, ' ') || granted_role)
into v_count
from dba_role_privs
where default_role = 'YES'
start with grantee = employee_rec.GRANTEE
connect by prior granted_role = grantee;
if v_count >= 148 then
DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
lpad(v_count, 5 , '.') ||
' <=== Cause ORA-28031');
else
DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
lpad(v_count, 5 , '.'));
end if ;
END LOOP;
DBMS_OUTPUT.put_line( chr(9 ));
DBMS_OUTPUT.put_line( '================== Use SQL to backup =====================');
FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
select count (lpad( ' ', 2 * level, ' ') || granted_role)
into v_count
from dba_role_privs
where default_role = 'YES'
start with grantee = employee_rec.GRANTEE
connect by prior granted_role = grantee;
if v_count >= 148 then
select 'alter user ' || employee_rec.GRANTEE ||' default role ' || wm_concat(granted_role) || ';' ||chr( 9)
into v_sql
from dba_role_privs
where grantee = employee_rec.GRANTEE
and default_role = 'YES' ;
DBMS_OUTPUT.put_line( substr(v_sql,1 ,2000));
end if ;
END LOOP;
DBMS_OUTPUT.put_line( chr(9 ));
DBMS_OUTPUT.put_line( chr(9 ));
DBMS_OUTPUT.put_line( '######################## REPORT END HERE #######################');
END;
/
我们来测试一下:
sys@ORA11G(192.168.1.37)> set serveroutput on
sys@ORA11G(192.168.1.37)> set line 5000
sys@ORA11G(192.168.1.37)> DECLARE
2 v_count varchar2( 20);
3 v_sql varchar2(2000 );
4 BEGIN
5 DBMS_OUTPUT.put_line( '###################### REPORT START HERE #######################');
6 DBMS_OUTPUT.put_line( chr(9 ));
7 DBMS_OUTPUT.put_line( chr(9 ));
8 DBMS_OUTPUT.put_line( '============= The number of recursive role ================');
9 FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
10 select count (lpad( ' ', 2 * level, ' ') || granted_role)
11 into v_count
12 from dba_role_privs
13 where default_role = 'YES'
14 start with grantee = employee_rec.GRANTEE
15 connect by prior granted_role = grantee;
16 if v_count >= 148 then
17 DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
18 lpad(v_count, 5 , '.') ||
19 ' <=== Cause ORA-28031');
20 else
21 DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
22 lpad(v_count, 5 , '.'));
23 end if ;
24 END LOOP;
25 DBMS_OUTPUT.put_line( chr(9 ));
26 DBMS_OUTPUT.put_line( '================== Use SQL to backup =====================');
27 FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
28 select count (lpad( ' ', 2 * level, ' ') || granted_role)
29 into v_count
30 from dba_role_privs
31 where default_role = 'YES'
32 start with grantee = employee_rec.GRANTEE
33 connect by prior granted_role = grantee;
34 if v_count >= 148 then
35 select 'alter user ' || employee_rec.GRANTEE ||' default role ' || wm_concat(granted_role) || ';' ||chr( 9)
36 into v_sql
37 from dba_role_privs
38 where grantee = employee_rec.GRANTEE
39 and default_role = 'YES' ;
40 DBMS_OUTPUT.put_line( substr(v_sql,1 ,2000));
41 end if ;
42 END LOOP;
43 DBMS_OUTPUT.put_line( chr(9 ));
44 DBMS_OUTPUT.put_line( chr(9 ));
45 DBMS_OUTPUT.put_line( '######################## REPORT END HERE #######################');
46 END;
47 /
###################### REPORT START HERE #######################
============= The number of recursive role ================
APEX_030200...........................................2
CTXSYS................................................2
DATAPUMP_EXP_FULL_DATABASE............................5
DATAPUMP_IMP_FULL_DATABASE...........................10
DBA..................................................41
DBSNMP................................................3
EXECUTE_CATALOG_ROLE..................................1
EXFSYS................................................1
EXP_FULL_DATABASE.....................................4
HS_ADMIN_ROLE.........................................2
IMP_FULL_DATABASE.....................................4
JAVASYSPRIV...........................................1
LOGSTDBY_ADMINISTRATOR................................1
MDDATA................................................2
MDSYS.................................................2
MGMT_VIEW.............................................1
OEM_MONITOR...........................................2
OLAPSYS...............................................2
OLS_TEST..............................................4
OPS$JIMMY.............................................1
ORDSYS................................................1
OUTLN.................................................1
OWBSYS...............................................10
ROLE_A................................................2
ROLE_B................................................2
ROLE_C...............................................42
ROLE_D...............................................49
SCOTT.................................................2
SELECT_CATALOG_ROLE...................................1
SPATIAL_CSW_ADMIN_USR.................................3
SPATIAL_WFS_ADMIN_USR.................................3
SYS.................................................224 <=== Cause ORA-28031
SYSMAN................................................4
SYSTEM...............................................44
TEST.................................................44
TT1...................................................1
WMSYS.................................................3
XDB...................................................3
================== Use SQL to backup =====================
alter user SYS default role ROLE_D,XDB_SET_INVOKER,XDBADMIN,JMXSERVER,IMP_FULL_DATABASE,APEX_ADMINISTRATOR_ROLE,JAVADEBUGPRIV,JAVAIDPRIV,RECOVERY_CATALOG_OWNER,DELETE_CATALOG_ROLE,CSW_USR_ROLE,OLAP_XS_ADMIN,DBA,WFS_USR_ROLE,XDB_WEBSERVICES_OVER_HTTP,AUTHENTICATEDUSER,JAVAUSERPRIV,ADM_PARALLEL_EXECUTE_TASK,ROLE_C,ROLE_A,OWB_DESIGNCENTER_VIEW,CWM_USER,OLAP_DBA,ORDADMIN,HS_ADMIN_ROLE,HS_ADMIN_SELECT_ROLE,DATAPUMP_IMP_FULL_DATABASE,EXP_FULL_DATABASE,ROLE_B,OWB$CLIENT,MGMT_USER,SPATIAL_CSW_ADMIN,CTXAPP,JAVA_ADMIN,OEM_MONITOR,HS_ADMIN_EXECUTE_ROLE,DATAPUMP_EXP_FULL_DATABASE,AQ_ADMINISTRATOR_ROLE,DBFS_ROLE,SPATIAL_WFS_ADMIN,OLAP_USER,XDB_WEBSERVICES,JAVA_DEPLOY,EJBCLIENT,JAVASYSPRIV,SCHEDULER_ADMIN,AQ_USER_ROLE,LOGSTDBY_ADMINISTRATOR,EXECUTE_CATALOG_ROLE,RESOURCE,OWB_USER,OLAPI_TRACE_USER,XDB_WEBSERVICES_WITH_PUBLIC,OEM_ADVISOR,GATHER_SYSTEM_STATISTICS,SELECT_CATALOG_ROLE,CONNECT;
######################## REPORT END HERE #######################
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
sys@ORA11G(192.168.1.37)>
sys@ORA11G(192.168.1.37)> set line 5000
sys@ORA11G(192.168.1.37)> DECLARE
2 v_count varchar2( 20);
3 v_sql varchar2(2000 );
4 BEGIN
5 DBMS_OUTPUT.put_line( '###################### REPORT START HERE #######################');
6 DBMS_OUTPUT.put_line( chr(9 ));
7 DBMS_OUTPUT.put_line( chr(9 ));
8 DBMS_OUTPUT.put_line( '============= The number of recursive role ================');
9 FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
10 select count (lpad( ' ', 2 * level, ' ') || granted_role)
11 into v_count
12 from dba_role_privs
13 where default_role = 'YES'
14 start with grantee = employee_rec.GRANTEE
15 connect by prior granted_role = grantee;
16 if v_count >= 148 then
17 DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
18 lpad(v_count, 5 , '.') ||
19 ' <=== Cause ORA-28031');
20 else
21 DBMS_OUTPUT.put_line( rpad(employee_rec.GRANTEE, 50 , '.') ||
22 lpad(v_count, 5 , '.'));
23 end if ;
24 END LOOP;
25 DBMS_OUTPUT.put_line( chr(9 ));
26 DBMS_OUTPUT.put_line( '================== Use SQL to backup =====================');
27 FOR employee_rec IN (SELECT distinct GRANTEE FROM dba_role_privs order by 1 ) LOOP
28 select count (lpad( ' ', 2 * level, ' ') || granted_role)
29 into v_count
30 from dba_role_privs
31 where default_role = 'YES'
32 start with grantee = employee_rec.GRANTEE
33 connect by prior granted_role = grantee;
34 if v_count >= 148 then
35 select 'alter user ' || employee_rec.GRANTEE ||' default role ' || wm_concat(granted_role) || ';' ||chr( 9)
36 into v_sql
37 from dba_role_privs
38 where grantee = employee_rec.GRANTEE
39 and default_role = 'YES' ;
40 DBMS_OUTPUT.put_line( substr(v_sql,1 ,2000));
41 end if ;
42 END LOOP;
43 DBMS_OUTPUT.put_line( chr(9 ));
44 DBMS_OUTPUT.put_line( chr(9 ));
45 DBMS_OUTPUT.put_line( '######################## REPORT END HERE #######################');
46 END;
47 /
###################### REPORT START HERE #######################
============= The number of recursive role ================
APEX_030200...........................................2
CTXSYS................................................2
DATAPUMP_EXP_FULL_DATABASE............................5
DATAPUMP_IMP_FULL_DATABASE...........................10
DBA..................................................41
DBSNMP................................................3
EXECUTE_CATALOG_ROLE..................................1
EXFSYS................................................1
EXP_FULL_DATABASE.....................................4
HS_ADMIN_ROLE.........................................2
IMP_FULL_DATABASE.....................................4
JAVASYSPRIV...........................................1
LOGSTDBY_ADMINISTRATOR................................1
MDDATA................................................2
MDSYS.................................................2
MGMT_VIEW.............................................1
OEM_MONITOR...........................................2
OLAPSYS...............................................2
OLS_TEST..............................................4
OPS$JIMMY.............................................1
ORDSYS................................................1
OUTLN.................................................1
OWBSYS...............................................10
ROLE_A................................................2
ROLE_B................................................2
ROLE_C...............................................42
ROLE_D...............................................49
SCOTT.................................................2
SELECT_CATALOG_ROLE...................................1
SPATIAL_CSW_ADMIN_USR.................................3
SPATIAL_WFS_ADMIN_USR.................................3
SYS.................................................224 <=== Cause ORA-28031
SYSMAN................................................4
SYSTEM...............................................44
TEST.................................................44
TT1...................................................1
WMSYS.................................................3
XDB...................................................3
================== Use SQL to backup =====================
alter user SYS default role ROLE_D,XDB_SET_INVOKER,XDBADMIN,JMXSERVER,IMP_FULL_DATABASE,APEX_ADMINISTRATOR_ROLE,JAVADEBUGPRIV,JAVAIDPRIV,RECOVERY_CATALOG_OWNER,DELETE_CATALOG_ROLE,CSW_USR_ROLE,OLAP_XS_ADMIN,DBA,WFS_USR_ROLE,XDB_WEBSERVICES_OVER_HTTP,AUTHENTICATEDUSER,JAVAUSERPRIV,ADM_PARALLEL_EXECUTE_TASK,ROLE_C,ROLE_A,OWB_DESIGNCENTER_VIEW,CWM_USER,OLAP_DBA,ORDADMIN,HS_ADMIN_ROLE,HS_ADMIN_SELECT_ROLE,DATAPUMP_IMP_FULL_DATABASE,EXP_FULL_DATABASE,ROLE_B,OWB$CLIENT,MGMT_USER,SPATIAL_CSW_ADMIN,CTXAPP,JAVA_ADMIN,OEM_MONITOR,HS_ADMIN_EXECUTE_ROLE,DATAPUMP_EXP_FULL_DATABASE,AQ_ADMINISTRATOR_ROLE,DBFS_ROLE,SPATIAL_WFS_ADMIN,OLAP_USER,XDB_WEBSERVICES,JAVA_DEPLOY,EJBCLIENT,JAVASYSPRIV,SCHEDULER_ADMIN,AQ_USER_ROLE,LOGSTDBY_ADMINISTRATOR,EXECUTE_CATALOG_ROLE,RESOURCE,OWB_USER,OLAPI_TRACE_USER,XDB_WEBSERVICES_WITH_PUBLIC,OEM_ADVISOR,GATHER_SYSTEM_STATISTICS,SELECT_CATALOG_ROLE,CONNECT;
######################## REPORT END HERE #######################
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
sys@ORA11G(192.168.1.37)>
然后我们执行第(2)步,alter user SYS default roles DBA;然后再检查:
sys@ORA11G(192.168.1.37)> alter user SYS default roles DBA;
User altered.
Elapsed: 00:00:00.06
--运行上面的程序:
###################### REPORT START HERE #######################
============= The number of recursive role ================
APEX_030200...........................................2
CTXSYS................................................2
DATAPUMP_EXP_FULL_DATABASE............................5
DATAPUMP_IMP_FULL_DATABASE...........................10
DBA..................................................41
DBSNMP................................................3
EXECUTE_CATALOG_ROLE..................................1
EXFSYS................................................1
EXP_FULL_DATABASE.....................................4
HS_ADMIN_ROLE.........................................2
IMP_FULL_DATABASE.....................................4
JAVASYSPRIV...........................................1
LOGSTDBY_ADMINISTRATOR................................1
MDDATA................................................2
MDSYS.................................................2
MGMT_VIEW.............................................1
OEM_MONITOR...........................................2
OLAPSYS...............................................2
OLS_TEST..............................................4
OPS$JIMMY.............................................1
ORDSYS................................................1
OUTLN.................................................1
OWBSYS...............................................10
ROLE_A................................................2
ROLE_B................................................2
ROLE_C...............................................42
ROLE_D...............................................49
SCOTT.................................................2
SELECT_CATALOG_ROLE...................................1
SPATIAL_CSW_ADMIN_USR.................................3
SPATIAL_WFS_ADMIN_USR.................................3
SYS.................................................126
SYSMAN................................................4
SYSTEM...............................................44
TEST.................................................44
TT1...................................................1
WMSYS.................................................3
XDB...................................................3
================== Use SQL to backup =====================
######################## REPORT END HERE #######################
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
sys@ORA11G(192.168.1.37)>
User altered.
Elapsed: 00:00:00.06
--运行上面的程序:
###################### REPORT START HERE #######################
============= The number of recursive role ================
APEX_030200...........................................2
CTXSYS................................................2
DATAPUMP_EXP_FULL_DATABASE............................5
DATAPUMP_IMP_FULL_DATABASE...........................10
DBA..................................................41
DBSNMP................................................3
EXECUTE_CATALOG_ROLE..................................1
EXFSYS................................................1
EXP_FULL_DATABASE.....................................4
HS_ADMIN_ROLE.........................................2
IMP_FULL_DATABASE.....................................4
JAVASYSPRIV...........................................1
LOGSTDBY_ADMINISTRATOR................................1
MDDATA................................................2
MDSYS.................................................2
MGMT_VIEW.............................................1
OEM_MONITOR...........................................2
OLAPSYS...............................................2
OLS_TEST..............................................4
OPS$JIMMY.............................................1
ORDSYS................................................1
OUTLN.................................................1
OWBSYS...............................................10
ROLE_A................................................2
ROLE_B................................................2
ROLE_C...............................................42
ROLE_D...............................................49
SCOTT.................................................2
SELECT_CATALOG_ROLE...................................1
SPATIAL_CSW_ADMIN_USR.................................3
SPATIAL_WFS_ADMIN_USR.................................3
SYS.................................................126
SYSMAN................................................4
SYSTEM...............................................44
TEST.................................................44
TT1...................................................1
WMSYS.................................................3
XDB...................................................3
================== Use SQL to backup =====================
######################## REPORT END HERE #######################
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
sys@ORA11G(192.168.1.37)>
好,此时可以安全做导出导入。待导入完后,恢复default role并检查:
sys@ORA11G(192.168.1.37)> alter user SYS default role ROLE_D,XDB_SET_INVOKER,XDBADMIN,JMXSERVER,IMP_FULL_DATABASE,APEX_ADMINISTRATOR_ROLE,JAVADEBUGPRIV,JAVAIDPRIV,RECOVERY_CATALOG_OWNER,DELETE_CATALOG_ROLE,CSW_USR_ROLE,OLAP_XS_ADMIN,DBA,WFS_USR_ROLE,XDB_WEBSERVICES_OVER_HTTP,AUTHENTICATEDUSER,JAVAUSERPRIV,ADM_PARALLEL_EXECUTE_TASK,ROLE_C,ROLE_A,OWB_DESIGNCENTER_VIEW,CWM_USER,OLAP_DBA,ORDADMIN,HS_ADMIN_ROLE,HS_ADMIN_SELECT_ROLE,DATAPUMP_IMP_FULL_DATABASE,EXP_FULL_DATABASE,ROLE_B,OWB$CLIENT,MGMT_USER,SPATIAL_CSW_ADMIN,CTXAPP,JAVA_ADMIN,OEM_MONITOR,HS_ADMIN_EXECUTE_ROLE,DATAPUMP_EXP_FULL_DATABASE,AQ_ADMINISTRATOR_ROLE,DBFS_ROLE,SPATIAL_WFS_ADMIN,OLAP_USER,XDB_WEBSERVICES,JAVA_DEPLOY,EJBCLIENT,JAVASYSPRIV,SCHEDULER_ADMIN,AQ_USER_ROLE,LOGSTDBY_ADMINISTRATOR,EXECUTE_CATALOG_ROLE,RESOURCE,OWB_USER,OLAPI_TRACE_USER,XDB_WEBSERVICES_WITH_PUBLIC,OEM_ADVISOR,GATHER_SYSTEM_STATISTICS,SELECT_CATALOG_ROLE,CONNECT;
User altered.
Elapsed: 00:00:00.07
--运行上面的程序:
###################### REPORT START HERE #######################
============= The number of recursive role ================
APEX_030200...........................................2
CTXSYS................................................2
DATAPUMP_EXP_FULL_DATABASE............................5
DATAPUMP_IMP_FULL_DATABASE...........................10
DBA..................................................41
DBSNMP................................................3
EXECUTE_CATALOG_ROLE..................................1
EXFSYS................................................1
EXP_FULL_DATABASE.....................................4
HS_ADMIN_ROLE.........................................2
IMP_FULL_DATABASE.....................................4
JAVASYSPRIV...........................................1
LOGSTDBY_ADMINISTRATOR................................1
MDDATA................................................2
MDSYS.................................................2
MGMT_VIEW.............................................1
OEM_MONITOR...........................................2
OLAPSYS...............................................2
OLS_TEST..............................................4
OPS$JIMMY.............................................1
ORDSYS................................................1
OUTLN.................................................1
OWBSYS...............................................10
ROLE_A................................................2
ROLE_B................................................2
ROLE_C...............................................42
ROLE_D...............................................49
SCOTT.................................................2
SELECT_CATALOG_ROLE...................................1
SPATIAL_CSW_ADMIN_USR.................................3
SPATIAL_WFS_ADMIN_USR.................................3
SYS.................................................224 <=== Cause ORA-28031
SYSMAN................................................4
SYSTEM...............................................44
TEST.................................................44
TT1...................................................1
WMSYS.................................................3
XDB...................................................3
================== Use SQL to backup =====================
alter user SYS default role ROLE_D,XDB_SET_INVOKER,XDBADMIN,JMXSERVER,IMP_FULL_DATABASE,APEX_ADMINISTRATOR_ROLE,JAVADEBUGPRIV,JAVAIDPRIV,RECOVERY_CATALOG_OWNER,DELETE_CATALOG_ROLE,CSW_USR_ROLE,OLAP_XS_ADMIN,DBA,WFS_USR_ROLE,XDB_WEBSERVICES_OVER_HTTP,AUTHENTICATEDUSER,JAVAUSERPRIV,ADM_PARALLEL_EXECUTE_TASK,ROLE_C,ROLE_A,OWB_DESIGNCENTER_VIEW,CWM_USER,OLAP_DBA,ORDADMIN,HS_ADMIN_ROLE,HS_ADMIN_SELECT_ROLE,DATAPUMP_IMP_FULL_DATABASE,EXP_FULL_DATABASE,ROLE_B,OWB$CLIENT,MGMT_USER,SPATIAL_CSW_ADMIN,CTXAPP,JAVA_ADMIN,OEM_MONITOR,HS_ADMIN_EXECUTE_ROLE,DATAPUMP_EXP_FULL_DATABASE,AQ_ADMINISTRATOR_ROLE,DBFS_ROLE,SPATIAL_WFS_ADMIN,OLAP_USER,XDB_WEBSERVICES,JAVA_DEPLOY,EJBCLIENT,JAVASYSPRIV,SCHEDULER_ADMIN,AQ_USER_ROLE,LOGSTDBY_ADMINISTRATOR,EXECUTE_CATALOG_ROLE,RESOURCE,OWB_USER,OLAPI_TRACE_USER,XDB_WEBSERVICES_WITH_PUBLIC,OEM_ADVISOR,GATHER_SYSTEM_STATISTICS,SELECT_CATALOG_ROLE,CONNECT;
######################## REPORT END HERE #######################
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
sys@ORA11G(192.168.1.37)>
User altered.
Elapsed: 00:00:00.07
--运行上面的程序:
###################### REPORT START HERE #######################
============= The number of recursive role ================
APEX_030200...........................................2
CTXSYS................................................2
DATAPUMP_EXP_FULL_DATABASE............................5
DATAPUMP_IMP_FULL_DATABASE...........................10
DBA..................................................41
DBSNMP................................................3
EXECUTE_CATALOG_ROLE..................................1
EXFSYS................................................1
EXP_FULL_DATABASE.....................................4
HS_ADMIN_ROLE.........................................2
IMP_FULL_DATABASE.....................................4
JAVASYSPRIV...........................................1
LOGSTDBY_ADMINISTRATOR................................1
MDDATA................................................2
MDSYS.................................................2
MGMT_VIEW.............................................1
OEM_MONITOR...........................................2
OLAPSYS...............................................2
OLS_TEST..............................................4
OPS$JIMMY.............................................1
ORDSYS................................................1
OUTLN.................................................1
OWBSYS...............................................10
ROLE_A................................................2
ROLE_B................................................2
ROLE_C...............................................42
ROLE_D...............................................49
SCOTT.................................................2
SELECT_CATALOG_ROLE...................................1
SPATIAL_CSW_ADMIN_USR.................................3
SPATIAL_WFS_ADMIN_USR.................................3
SYS.................................................224 <=== Cause ORA-28031
SYSMAN................................................4
SYSTEM...............................................44
TEST.................................................44
TT1...................................................1
WMSYS.................................................3
XDB...................................................3
================== Use SQL to backup =====================
alter user SYS default role ROLE_D,XDB_SET_INVOKER,XDBADMIN,JMXSERVER,IMP_FULL_DATABASE,APEX_ADMINISTRATOR_ROLE,JAVADEBUGPRIV,JAVAIDPRIV,RECOVERY_CATALOG_OWNER,DELETE_CATALOG_ROLE,CSW_USR_ROLE,OLAP_XS_ADMIN,DBA,WFS_USR_ROLE,XDB_WEBSERVICES_OVER_HTTP,AUTHENTICATEDUSER,JAVAUSERPRIV,ADM_PARALLEL_EXECUTE_TASK,ROLE_C,ROLE_A,OWB_DESIGNCENTER_VIEW,CWM_USER,OLAP_DBA,ORDADMIN,HS_ADMIN_ROLE,HS_ADMIN_SELECT_ROLE,DATAPUMP_IMP_FULL_DATABASE,EXP_FULL_DATABASE,ROLE_B,OWB$CLIENT,MGMT_USER,SPATIAL_CSW_ADMIN,CTXAPP,JAVA_ADMIN,OEM_MONITOR,HS_ADMIN_EXECUTE_ROLE,DATAPUMP_EXP_FULL_DATABASE,AQ_ADMINISTRATOR_ROLE,DBFS_ROLE,SPATIAL_WFS_ADMIN,OLAP_USER,XDB_WEBSERVICES,JAVA_DEPLOY,EJBCLIENT,JAVASYSPRIV,SCHEDULER_ADMIN,AQ_USER_ROLE,LOGSTDBY_ADMINISTRATOR,EXECUTE_CATALOG_ROLE,RESOURCE,OWB_USER,OLAPI_TRACE_USER,XDB_WEBSERVICES_WITH_PUBLIC,OEM_ADVISOR,GATHER_SYSTEM_STATISTICS,SELECT_CATALOG_ROLE,CONNECT;
######################## REPORT END HERE #######################
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
sys@ORA11G(192.168.1.37)>
看到已经恢复。
网友评论已有0条评论, 我也要评论