I was helping out a customer today with what they thought was a bug in DBMS_METADATA. They were using it to unload a complete set of DDL scripts for their schema, and their schema is big. Not big in the sense of data volume but in the number of database objects contained in the schema. (Anyone using Oracle Applications will know that the database can happily accommodate tens of thousands of objects in a schema without any problem).
To build a script to run the appropriate grants for objects, they were using the DBMS_METADATA.GET_GRANTED_DDL routine. I don’t have access to their schema, but I can mimic the issue they were encountering with a standard database installation because the PUBLIC schema has thousands of objects and grants, so it can serve as our “large schema”.
Here is how we can unload all of the grants to the PUBLIC schema
SQL> set pages 0
SQL> set longchunksize 3000
SQL> set long 2000000000
SQL> set lines 1000
SQL> spool x:\temp\grants.sql
SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT','PUBLIC') from dual;
GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION
GRANT READ ON "SYS"."SYSTEM_PRIVILEGE_MAP" TO PUBLIC
GRANT READ ON "SYS"."TABLE_PRIVILEGE_MAP" TO PUBLIC
GRANT READ ON "SYS"."USER_PRIVILEGE_MAP" TO PUBLIC
GRANT READ ON "SYS"."STMT_AUDIT_OPTION_MAP" TO PUBLIC
GRANT INSERT ON "SYS"."FINALHIST$" TO PUBLIC
GRANT SELECT ON "SYS"."DM$EXPIMP_ID_SEQ" TO PUBLIC
GRANT SELECT ON "SYS"."MODELGTTRAW$" TO PUBLIC
...
...
...
GRANT EXECUTE ON "SYS"."com/sun/tools/javac/jvm/CRTable$SourceRange" TO PUBLIC
GRANT EXECUTE ON "SYS"."com/sun/tools/javac/util/ByteBuffer" TO PUBLIC
GRANT EXECUTE ON "SYS"."com/sun/tools/javac/jvm/CRTable" TO PUBLIC
GRANT EXECUTE ON "SYS"."com/sun/tools/javac/jvm/Items$1" TO PUBLIC
SQL> spool off
That looks like it has worked successfully, but when the customer subsequently ran their grant script against their target environment, and tried to launch their application, then “all hell broke loose” as their DBA conveyed to me 
.
The answer lies in the size of the file I created with the script above. Here’s a screen grab from that file opened in Textpad.
Does it strike you as an extreme coincidence that there would be exactly 10,000 grants to the PUBLIC schema in my database. It is of course easy to check that and prove that this was not the case.
SQL> select count(*)
2 from dba_tab_privs
3 where grantee = 'PUBLIC';
COUNT(*)
----------
44591
Some of the DBMS_METADATA routines have an upper limit on the number of entries they will return. Running a DESCRIBE on the package reveals:
SQL> desc DBMS_METADATA
...
...
FUNCTION GET_DEPENDENT_DDL RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
BASE_OBJECT_NAME VARCHAR2 IN
BASE_OBJECT_SCHEMA VARCHAR2 IN DEFAULT
VERSION VARCHAR2 IN DEFAULT
MODEL VARCHAR2 IN DEFAULT
TRANSFORM VARCHAR2 IN DEFAULT
OBJECT_COUNT NUMBER IN DEFAULT
...
...
and it turns out that the default for object_count is set to 10,000. We need to adjust it upwards if we want to handle larger counts.
SQL> set pages 0
SQL> set longchunksize 3000
SQL> set long 2000000000
SQL> set lines 1000
SQL> spool x:\temp\grants.sql
SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT','PUBLIC',object_count=>50000) from dual;
GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION
GRANT READ ON "SYS"."SYSTEM_PRIVILEGE_MAP" TO PUBLIC
GRANT READ ON "SYS"."TABLE_PRIVILEGE_MAP" TO PUBLIC
GRANT READ ON "SYS"."USER_PRIVILEGE_MAP" TO PUBLIC
GRANT READ ON "SYS"."STMT_AUDIT_OPTION_MAP" TO PUBLIC
...
...
GRANT READ ON "APEX_200200"."APEX_ISSUE_NOTIFICATIONS" TO PUBLIC
GRANT EXECUTE ON "APEX_200200"."WWV_FLOW_SPATIAL_API" TO PUBLIC
GRANT EXECUTE ON "ORDS_METADATA"."ORDS_CONSTANTS" TO PUBLIC
GRANT EXECUTE ON "ORDS_METADATA"."ORDS_UTIL" TO PUBLIC
SQL> spool off
SQL> host wc -l x:\temp\grants.sql
44648 x:\temp\grants.sql
So for anyone dealing with large schemas that is using DBMS_METADATA, make sure you are not silently hitting any of the default limits imposed, and set object_count accordingly.
Learning is not a spectator sport 
Got some thoughts? Leave a comment