Oracle ACE Associate Award

I have been awarded the Oracle ACE Associate Award, this award is given to elite technical professionals in Oracle technology, for their expertise in addition to their contribution within Oracle community.

I am very happy to say that i am the First Saudi National IT Professional to receive such distinction 🙂  , and among the best of Oracle ACE’s from around the world.

My Oracle ACE Profile:

Oracle ACE News Letter for the Month of September 2015:

Oracle ACE Associate

Emad Al-Mousa Oracle ACE Profile

Oracle ACE News Letter

الحمد لله رب العالمين

Performing Import Data Pump with EXCLUDE=STATISTICS

some DBA’s use EXCLUDE=STATISTICS parameter in their import datapump command in order to speed up the import process.

*** example of impdp command:
impdp power_user/power43b Directory=DUMP_DIR Dumpfile=Exp_schema.dmp Logfile=Exp.log EXCLUDE=STATISTICS
However, in reality indexes created while importing are “analyzed” automatically, and this is performed by design. Only tables that are not going to be “analyzed” while importing.

the reason behind that is there is a hidden parameter called “_optimizer_compute_index_stats” is set to “TRUE“.
its not advisable that you manipulate Oracle hidden parameters, you need to consult Oracle and open SR with them in case you need their consultation.

to check that indexes were analyzed after the import, use the sql query:

SQL> select OWNER, INDEX_NAME, TABLE_NAME, LAST_ANALYZED from dba_indexes where OWNER=’power_user’;

Have A Nice Summmmmmmer…


ORA-00604: error occurred at recursive SQL level 1 ORA-06598: insufficient INHERIT PRIVILEGES privilege ORA-06512: at “XDB.DBMS_CSX_INT”, line 1 while upgrading to 12c

while upgrading your database to 12c , its very important that you monitor your alert.log file to check for errors and potential problems.

to find out the location of you alert log file, query the following:

SQL> select * form v$diag_info;

one of the databases that i have upgraded, i found the following error being thrown in the alert.log file:

XDB SGA reset to NULL.

ORA-00604: error occurred at recursive SQL level 1

ORA-06598: insufficient INHERIT PRIVILEGES privilege

ORA-06512: at “XDB.DBMS_CSX_INT”, line 1

this error can be ignored, to ensure that your XDB component is ok run the utilrip after the upgrade is completed:


make sure that the XDB is fine by querying:

SQL> select COMP_NAME,VERSION,STATUS from dba_registry;

so basically the error is not dangerous but you need to verify that everything is fine.

*************************************** …….*****************************************   🙂


a new parameter was introduced in Oracle 12c  for the datapump operations. this parameter is TRANSFORM=DISABLE_ARCHIVE_LOGGING

To explore this parameter let me first start importing a schema in my database without specifying the parameter:

my current database is in “archive log mode”

select LOG_MODE , force_logging from v$database;

query for archive log mode

The time taken for this import was 2 hours !!!

And, 55 GB of archive storage was generated during the import.

Now, i will re-import again using the parameter TRANSFORM=DISABLE_ARCHIVE_LOGGING in my below command:

nohup $ORACLE_HOME/bin/impdp “‘/as sysdba ‘”  DIRECTORY=EXP_DIR DUMPFILE=SCHEMA_JUNE28.dmp transform=disable_archive_logging:y logfile=IMP_disable_archive.log  &

the time taken to finish the import was 1 hour 28 minutes !!! half an hour less the the normal standard import operation.

And, the storage of the archive log was around 30 GB.

The conclusion of this exercise is that the new parameter introduced in 12c will benefit in 2 things:

  • faster import process completion
  • less storage for archive generation

Remark: make sure that when you use this parameter ,that if you have a logical standby setup for the database, that the database is in “archive log mode” and “force logging” is enabled.