暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle 数据库管理系统 _ 元数据库。获取 _ 授予 _ ddl错误

askTom 2017-05-16
690

问题描述

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 !!!

专家解答

如果用户没有授予任何角色,则会收到此错误,例如

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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论