Oracle Internals

Though have been working on oracle for many years now and have been using oracle datapump for quiet some time as well. Recently we found an application had issues post schema import, on identifying the root cause found two things: 1. Objects grant were missing on the schema we imported where schema owner was grantee. 2. Object grants were missing on the schema based on sys objects. To my surprise both are actually normal things. With schema and table level import object permissions where importing schema granted permission to other schemas will be played but when imported schema is grantee(permission given by other schema owner) those permissions are not played. If you still want to see those grant use: impdp full=y dumpfile=expdp.dmp sqlfile=fullimpdp.sql INCLUDE=GRANT This will create a sqlfile with name fullimpdp.sql and will consists of object level permissions which are normally not being imported with impdp. Now solution for this problem can be 1. Backup these per...

Recently while compiling a trigger in PDB database using sys user owned by another schema gave me error "ORA-01031: insufficient privileges" SQL> Alter trigger owner.trigger_name compile; ORA-01031: insufficient privileges Sys user is super admin user has all possible privilege, but with 12.2 owner of trigger need direct permission  of "administer database trigger" to allow other users to compile the trigger. This behavior is intruded with 12.2 on- wards. In order to solve this problem grant  "administer database trigger" direct permission to owner of the trigger and compile the trigger again. SQL>grant administer database trigger to  owner ; SQL> Alter trigger owner.trigger_name compile; This details are also backed by Oracle Metalink Document -  Doc ID 2275535.1