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

All of us has some point in time used Oracle SQL monitoring reports which are pretty useful to identify critical statistics for an currently running query which can be find through various database queries as well. With SQL monitoring reports it makes pretty easy for DBA's to identify real time statistics about qery execution state. There are multiple ways where sql monitoring reports can be extracts Real Time - Fetch the reports when query is running. Historical Reports - Fetch the reports when queries are already completed in some point time in history. Real Time Reports can be extracted through OEM (Oracle Enterprise Manager) SQLPLUS commands - We are going to discuss them within this blog. Very often everyone of us might of noticed sql monitoring reports are not generated for all queries, which is due to some important factor: _sqlmon_threshold with default value of 5 means any query which has combined CPU and I/O execution more than 5 seconds is entit...