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 !!!
If a user does not have any roles granted, then you'll get this error, for example
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
You can use a pragma or wrapper function to handle this, for example
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