问题描述
I'm testing some script and suffering from following error
script ::
set trimspool on;
set heading off;
set linesize 300;
set echo off;
set pages 10000;
set long 90000;
COL DDL FORMAT A10000;
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', false);
SPOOL role.sql;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME )
FROM dba_users
WHERE USERNAME NOT IN ('SYS', 'SYSTEM', 'SCOTT', 'DBSNMP', 'OUTLN', 'WKPROXY', 'WMSYS', 'ORDSYS', 'ORDPLUGINS', 'MDSYS', 'CTXSYS', 'XDB', 'ANONYMOUS', 'OWNER', 'WKSYS', 'ODM_MTR', 'ODM', 'OLAPSYS', 'HR', 'OE', 'PM', 'SH', 'QS_ADM', 'QS', 'QS_WS', 'QS_ES', 'QS_OS', 'QS_CBADM', 'QS_CB', 'QS_CS', 'PERFSTAT', 'ORACLE_OCM', 'APPQOSSYS', 'DIP');
SPOOL off;
Result ::
ORA-31608: ROLE_GRANT 유형의 지정된 객체를 찾을 수 없음
ORA-06512: "SYS.DBMS_METADATA", 4018행
ORA-06512: "SYS.DBMS_METADATA", 5991행
ORA-06512: 1행
31608. 00000 - "specified object of type %s not found"
*Cause: The specified object was not found in the database.
*Action: Correct the object specification and try the call again.
please, help me !!! 专家解答
如果用户没有授予任何角色,则会收到此错误,例如
您可以使用pragma或wrapper函数来处理此问题,例如
SQL> create user NO_ROLES identified by NO_ROLES;
User created.
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME )
2 FROM dba_users
3 WHERE USERNAME = 'NO_ROLES';
ERROR:
ORA-31608: specified object of type ROLE_GRANT not found
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 6469
ORA-06512: at "SYS.DBMS_METADATA", line 9353
ORA-06512: at line 1
您可以使用pragma或wrapper函数来处理此问题,例如
SQL>
SQL> set long 100000
SQL> set autoprint on
SQL> variable x clob
SQL>
SQL> select dbms_metadata.get_ddl( 'USER', 'A' ) from dual;
DBMS_METADATA.GET_DDL('USER','A')
-------------------------------------------------------------------------------
CREATE USER "A" IDENTIFIED BY VALUES 'AFCC9478DFBF9029'
TEMPORARY TABLESPACE "TEMP"
SQL>
SQL> declare
2 no_grant exception;
3 pragma exception_init( no_grant, -31608 );
4 begin
5 :x := dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'A' );
6 exception
7 when no_grant then :x := '-- no system grants';
8 end;
9 /
PL/SQL procedure successfully completed.
X
-------------------------------------------------------------------------------
-- no system grants
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。




