Oracle 10g, 11g: Export and Import schema statistics

Easy way to export and import schema statistics – in case the optimizer is behaving incorrectly and you want to re-import old statistics when the optimizer behaved properly.

SCHEMA NAME – POWERCARD

Create the table to store the statistics-
EXEC DBMS_STATS.CREATE_STAT_TABLE('POWERCARD', 'STATS_TABLE');

Export schema stats – will be stored in the 'STATS_TABLE'
EXEC DBMS_STATS.export_schema_stats('POWERCARD','STATS_TABLE',NULL,'POWERCARD');

If required import these statistics back to POWERCARD schema.
EXEC DBMS_STATS.import_schema_stats('POWERCARD','STATS_TABLE',NULL,'POWERCARD');

Finally drop the table created to backup the schema stats
EXEC DBMS_STATS.drop_stat_table('POWERCARD','STATS_TABLE');

ORA-39242: Unable to export/import TABLE_DATA: … due to table attributes.

ORA-39242: Unable to export/import TABLE_DATA: … due to table attributes.

The root cause to the problem was there was some index in the unusable state.

To find the unusable index ran the following query:

select owner, index_name from dba_indexes where status=’UNUSABLE';

To resolve the issue rebuild the index by the following query:

ALTER INDEX OWNER.INDEX_NAME REBUILD ONLINE nologging parallel 8;

And after that while trying to run the import the import run without any error.

Oracle: ORA-39001: invalid argument value ORA-39071: Value for EXCLUDE is badly formed. ORA-00936: missing expression

While import/export in Unix shell:

impdp system/manager dumpfile=powercard_schema_structure_25052015.dmp directory=DUMPS logfile=imp_powercard_schema_structure_25052015_2.log table_exists_action=truncate  EXCLUDE=TABLE:"IN ('AUTHORIZATION_ALERTS', 'XML_MSG_XCHANGE_TRACE_HIST' , 'PCARD_TRACES' , 'AUTHORIZATION_ALERTS_TEMP','ATM_COMMANDS_DF','CARD_ACTIVITY','TRANSACTION_HIST_ADD_CHIP','VSS_MEMBER_SET_46_V4','TRANSACTION_HIST_ADD_FRAUD')"

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression

Solution: Use escape character  \ before special character like: '  "  (  )

impdp system/xxxx dumpfile=schema_structure_25052015.dmp directory=DUMPS logfile=imp_powercard_schema_structure_25052015_2.log table_exists_action=truncate  EXCLUDE=TABLE:\"IN \(\'AUTHORIZATION_ALERTS\', \'XML_MSG_XCHANGE_TRACE_HIST\' , \'PCARD_TRACES\' , \'AUTHORIZATION_ALERTS_TEMP\',\'ATM_COMMANDS_DF\',\'CARD_ACTIVITY\',\'TRANSACTION_HIST_ADD_CHIP\',\'VSS_MEMBER_SET_46_V4\',\'TRANSACTION_HIST_ADD_FRAUD\'\)\"

11g: Kill RAC session from SQLPLUs

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' immediate;' from gv$session where username = 'SCOTT';

Finding all files in directory containing a text string on Linux

grep -rnw 'directory' -e "pattern"

error while loading shared libraries: libodm11.so: cannot open shared object file: No such file or directory ERROR: ORA-12547: TNS:lost contact

Enter user-name:
oracleSIT: error while loading shared libraries: libodm11.so: cannot open shared object file: No such file or directory
ERROR:
ORA-12547: TNS:lost contact

Solution:

$ cd $ORACLE_HOME/bin/ 

$ relink all

TNS-01189: The listener could not authenticate the user

Error looks like its related to listener, but it happened with us as a result of number of sessions exceeded.

Enter user-name: / as sysdba
ERROR:
ORA-00020: maximum number of processes (300) exceeded