Oracle 11g : Where to find X$ tables

See at the following url to have access to some X$ tables:
Wikipedia X$table : http://www.orafaq.com/wiki/X$_Table
Oracle Internal X$Tables : http://oracle-abc.wikidot.com/x-tables
Oracle X$tables : http://yong321.freeshell.org/computer/x$table.html

Oracle 11g: What is the id of the current session

To know the id of the current session :

SQL>select SYS_CONTEXT('USERENV', 'SESSIONID') from dual;
SYS_CONTEXT('USERENV','SESSIONID')
----------------------------------
4294967295

See this page to retrieve all parameters defined at the user session level :
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions172.htm

Oracle 11g : SQLLoader

See the following reference for more information :

• for th command line reference :
  http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/ldr_params.htm#i1004682
• For the SQL loader concepts :
  http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/ldr_concepts.htm

Oracle 11g : How to retrieve the source code of a procedure

Look the content of the user_source table:

desc user_source;
NAME   VARCHAR2(30)
TYPE   VARCHAR2(12)
LINE   NUMBER
TEXT   VARCHAR2(4000)

You can issue the following SQL request to find the source code of a specific procedure :

SELECT text
FROM   user_source
WHERE  name = 
AND    type = 'PROCEDURE'
ORDER BY line;

Oracle 11g: How to display explain plan

Intallation
To retrieve the execution plan for a specific sql request or session, you need to install the PLAN_TABLE table in your database.
Oracle provide the utlxplan.sql script located under ${ORACLE_HOME}/rdbms/admin/ to perform its installation.

SQL>@?/rdbms/admin/utlxplan;

Calculate plan for a specific request
To populate the PLAN_TABLE table for a specific request :

SQL>truncate table PLAN_TABLE;
SQL>explain plan for select * from user_tables;

Display plan for the previous request

SQL>set linesize 400
SQL>@?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4164660786

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |  1831 |  4173K|   515   (1)| 00:00:07 |
|*  1 |  HASH JOIN RIGHT OUTER      |               |  1831 |  4173K|   515   (1)| 00:00:07 |
|   2 |   INDEX FAST FULL SCAN      | I_OBJ2        | 12815 |   300K|    33   (0)| 00:00:01 |
|*  3 |   HASH JOIN RIGHT OUTER     |               |  1831 |  4130K|   482   (2)| 00:00:06 |
|   4 |    TABLE ACCESS FULL        | USER$         |    93 |  1581 |     3   (0)| 00:00:01 |
|*  5 |    HASH JOIN RIGHT OUTER    |               |  1831 |  4100K|   478   (1)| 00:00:06 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   6 |     INDEX FAST FULL SCAN    | I_OBJ1        | 12815 |   100K|    11   (0)| 00:00:01 |
|*  7 |     HASH JOIN               |               |  1831 |  4085K|   466   (1)| 00:00:06 |
|   8 |      TABLE ACCESS FULL      | TS$           |     5 |    95 |     3   (0)| 00:00:01 |
|*  9 |      HASH JOIN RIGHT OUTER  |               |  1831 |  4051K|   463   (1)| 00:00:06 |
|  10 |       TABLE ACCESS FULL     | SEG$          |  5235 |   230K|    43   (0)| 00:00:01 |
|* 11 |       HASH JOIN RIGHT OUTER |               |  1831 |  3971K|   419   (1)| 00:00:06 |
|  12 |        TABLE ACCESS FULL    | DEFERRED_STG$ |  2783 | 64009 |     7   (0)| 00:00:01 |
|* 13 |        HASH JOIN            |               |  1831 |  3930K|   412   (1)| 00:00:05 |
|* 14 |         TABLE ACCESS FULL   | TAB$          |  2729 |   261K|   367   (0)| 00:00:05 |
|  15 |         MERGE JOIN CARTESIAN|               |  1831 |  3754K|    44   (3)| 00:00:01 |
|* 16 |          HASH JOIN          |               |     1 |  2070 |     1 (100)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 17 |           FIXED TABLE FULL  | X$KSPPI       |     1 |    55 |     0   (0)| 00:00:01 |
|  18 |           FIXED TABLE FULL  | X$KSPPCV      |   100 |   196K|     0   (0)| 00:00:01 |
|  19 |          BUFFER SORT        |               |  1831 | 54930 |    44   (3)| 00:00:01 |
|* 20 |           TABLE ACCESS FULL | OBJ$          |  1831 | 54930 |    43   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."BOBJ#"="CO"."OBJ#"(+))
   3 - access("CX"."OWNER#"="CU"."USER#"(+))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   5 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
   7 - access("T"."TS#"="TS"."TS#")
   9 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND
              "T"."TS#"="S"."TS#"(+))
  11 - access("T"."OBJ#"="DS"."OBJ#"(+))
  13 - access("O"."OBJ#"="T"."OBJ#")
  14 - filter(BITAND("T"."PROPERTY",1)=0)
  16 - access("KSPPI"."INDX"="KSPPCV"."INDX")
  17 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
  20 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0)

43 rows selected.

Remark : An other way to display a plan under sqlplus is :

SQL> set autotrace on;

To trace only the plan and associated statistics:

SQL> set autotrace traceonly explain statistics;

Note that you may need to enable the PLUSTRACE role for the user that activates the autotrace feature. ($ORACLE_HOME/sqlplus/admin/plustrce.sql)
If you have an error then give the 'select any dictionary' role to the user.

Remark : Using the dbms_xplan package:

•  To display the plan from the plan table
  

  SQL> select * from table( dbms_xplan.display);

•  To display the plan from the shared pool
  

  SQL> select * from table( dbms_xplan.display_cursor);

 

Displaying current plan with dbms_xplan

• Retrieve the sql_id associated to the current request in the v$sql view
• Display the plan like this:
  

  SQL> select * from table( dbms_xplan.display_cursor('<sql_id>', NULL));

Note that under 11g, the adaptive cursor sharing can generate several suboptimal plan for a sql request depending on statistics and histograms. The second parameter of the display_cursor procedure correspond to the child number so a specific plan in relation to the selectivity of parameters used in the request.
To known if a sql request is sensitive to bind values and adaptive cursor sharing:

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM   v$sql WHERE  sql_text = '...';

SQL_ID        CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf            0 Y N
9bmm6cmwa8saf            1 Y Y

2 rows selected.

 

Oracle 11g: Export import DATA PUMP

Data pump export/import is a utility for unloading and loading data and metadata from a database into a set of system files. Because the dump files are written by the server, rather than by the client, the database administrator must create directory objects where the utility can managed the files set.

SQL>create or replace directory export_dir as '/opt/oracle/admin/export';
SQL>grant read, write on directory export_dir to fmosse;

DATA_PUMP_DIR directory
By default Oracle create the DATA_PUMP_DIR that references the file system directory '/opt/oracle/admin/<sid>/dpdump/'. User must have the EXP_FULL_DATABASE or IMP_FULL_DATABASE privileges to use the DATA_PUMP_DIR database directory object.
If for some reason the system directory defined in DATA_PUMP_DIr is not found then Oracle use the $ORACLE_HOME/rdbms/log directory instead.

Data Pump Export Modes

Export provides different modes for unloading different portions of the database. The mode is specified on the command line, using the appropriate parameter. The available modes are as follows:

• Full Export Mode
• Schema Mode
• Table Mode
• Tablespace Mode
• Transportable Tablespace Mode

Remark : You need the dba privilege to export or import the entire database or some tablespaces.

Exporting the full database

expdb system content=ALL full=y directory=DATA_PUMP_DIR dumpfile=export_full.dmp logfile=export_full.log

The 'content' parameter specifies which data to export. this parameter can have the following values :

• ALL : To export all data and metadata
• DATA_ONLY : Only data will be exported
• METADATA_ONLY: only the structure of the database will be exported

The 'full' parameter specifies that we will export the entire database.

Exporting some schemas

expdb system content=ALL schemas=fmosse directory=DATA_PUMP_DIR dumpfile=export_full.dmp logfile=export_full.log

/br>Exporting some tables of a specific schema

expdb system content=ALL schemas=fmosse tables="TABLE1,TABLE2,TABLE3" directory=DATA_PUMP_DIR dumpfile=export_full.dmp logfile=export_full.log

Exporting a database with a set of dumpfile that does not exceed a specific size

expdp system content=all full=y directory=DATA_PUMP_DIR dumpfile=export_full_%u.dmp logfile=export_full.log filesize=250000

If the 'parallel' parameter is defined with the %U, then oracle create initially a set of dump files defined using the value of the 'parallel' attribute.Oracle can create more files than defined in the parallel attribute if the filesize is specified with a value that requires more files than defined in the parallel attribute.

Exporting a database using several processes in parallel

expdp system content=all full=y directory=DATA_PUMP_DIR dumpfile=export_full.dmp logfile=export_full.log parallel=4

Overwrite export files if already exists

expdp system content=all full=y directory=DATA_PUMP_DIR dumpfile=export_full_%u.dmp  logfile=export_full.log parallel=4 filesize=250000 reuse_dumpfiles=Y

Estimating the size of the export

expdp system content=all full=y directory=DATA_PUMP_DIR estimate_only=y estimate=statistics

If estimate_only=y is specified then the dumpfile parameter must not be defined.
The 'estimate' parameter defines the algorithm to use for estimation. The available values are:

• BLOCKS : The default value
• STATISTICS

Importing data

impdp hr directory=DATA_PUMP_DIR dumpfile=hr_export.dmp content=all tables="EMPLOYEES" parallel=1

DataPump import transformation
It is possible with the impdp tool to remap parts of the object when importing a dump file:

• REMAP_DATAFILE : to remap datafiles
• REMAP_TABLESPACES : to remap tablespaces
• REMAP_SCHEMA : to remap a schema to an other one
• REMAP_TABLE : to remap the name of a table
• REMAP_DATA : to alter data before the import

To change the name of a table yuo can write REMAP_TABLE="EMPOYEE:EMP".

Consistent backup
Use the parameter flashback_time=to_timestamp( '24/10/2011 18:19:00', 'DD/MM/YYYY HH24:MI:SS')