ba6.us - Dave's Database Related Stuff

Now that I am working in an environment where I am responsible for more database instances it seems like data is always moving around. Machines are being retired, platforms are changing, all kinds of fun stuff.

I often have to move using regular old Oracle import/export utilities. These utilities aren't the best at some of the more esoteric Oracle features so some 'backfilling' of objects or permissions may be required.

Here are some queries that are helpful when moving a schema that has Oracle Java Virtual Machine (JVM) Dependencies:

Overview of all Java Permissions in a Database

SELECT * FROM DBA_JAVA_POLICY
WHERE ENABLED='ENABLED'
 ORDER BY GRANTEE, KIND, TYPE_SCHEMA, TYPE_NAME, ACTION;

Grab permissions for specified user

Here is a query that will give you grants associated with the Oracle JVM so that you can run them on another database.

SELECT 'exec dbms_java.grant_permission('''||
       GRANTEE||''','''||
       TYPE_SCHEMA||':'||TYPE_NAME||''','''||
       NAME||''','''||ACTION||''');' as "Grant Stmt" 
  FROM DBA_JAVA_POLICY
 WHERE GRANTEE = UPPER('&granteename')
 AND ENABLED='ENABLED'
 ORDER BY GRANTEE, KIND, TYPE_SCHEMA, TYPE_NAME, ACTION;