Datapump notes

Datapump
Exporting table
syntax 

expdp username_taking_export/passwd directory=directory_name dumpfile=some_related_name.dmp logfile=some_related_name.log tables=username.table_name

Example--> 

expdp system/passwd directory=dba_dir dumpfile=exp_scott_emp.dmp logfile=exp_scott_emp.log tables=scott.emp

usename_taking_export---> is the user as whom you are taking export backup.Can be the owner of table or any other user who has required privileges.

directory--> a directory which has been created & from above username_taking_export user should have been granted read, write privileges. It is here the dumpfiles are going to be created.

tables--> if the table is to be exported from another schema, then qualify it with the schema/user name

Hidden parameters - Oracle Tidbits

Upgrade brought an issue with new optimizer features. On analysis an optimizer feature had to be turned off. While I save that experience for another day in here I wanted to share about how the particular feature a hidden parameter setting had to be changed.

To get its current value from "v$parameter"
SQL> select name, value from v$parameter where name like '/_fix%' escape '/';
no rows selected
For finding the hidden parameter the following Query should help.  I was searching for "_fix_control"

set escape '\'
select a.ksppinm name,b.ksppstvl value, b.ksppstdf deflt,
decode (a.ksppity, 1,'boolean',
     2,'string',
     3,'number',
     4,'file', a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '\_fix\_contr%'
order by
name;

NAME                 VALUE      DEFLT     TYPE                                     DESCRIPTION
-------------------- ---------- --------- ---------------------------------------- --------------------------
_fix_control                    TRUE      string                                   bug fix control parameter


We have the option of changing it session or system level. For changing any hidden parameters it is required to check with oracle support. As an example its being changed at system level here.

SQL> alter system set "_fix_control"='8893626:OFF' scope=both;
System altered.
NAME                 VALUE      DEFLT     TYPE                                     DESCRIPTION
-------------------- ---------- --------- ---------------------------------------- ------------------------
_fix_control         8893626:OFF TRUE      string                                   bug fix control parameter