Sunday, December 30, 2012

ORA-02055: Distributed update operation failed; rollback required


Every DBA at least one time faced ORA-02055 exception. It may be occur on several reasons. Here is symptom and reason.

ORA-02055:
distributed update operation failed; rollback required
Cause:
a failure during distributed update operation may not have rolled back all effects of the operation. Since some sites may be inconsistent, the transaction must roll back to a savepoint or entirely
Action:
rollback to a savepoint or rollback transaction and resubmit


It will be helpful checking DBA_2PC_PENDING data dictionary view 

select * from DBA_2PC_PENDING order by fail_time

--take local_tran_id or global_tran_id

example 8.44.2012311

Then enable distributed recovery and execute below statements using:
      
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY
      
execute dbms_transaction.rollback_force('8.44.2012311');
      
execute dbms_transaction.purge_lost_db_entry('8.44.2012311')


Source:
http://ora-02055.ora-code.com/
http://docs.oracle.com/cd/B19306_01/server.102/b14231/ds_txnman.htm
http://www.dbmotive.com/ora-02055-distributed-update-operation-failed-rollback-required/

Saturday, December 29, 2012

Oracle Dictionary Scripts - $ORACLE_HOME/rdbms/admin/...sql

Rarely every DBA uses Catalog scripts which locates under $ORACLE_HOME/rdbms/admin/

I will not some of them and when and why we use it. Please note, it depends of the Oracle version and release.

We can say some of them famous, such as utlrp.sql it compiles invalid objects, it often used database upgrade, catalog.sql creates data dictionary, it used when you create database manually : create database ... etc. There are also some wrapped files (extension *.plb)

Here is list (not all) of scripts:

catalog.sql - create data dictionary views.
cataudit.sql - data dictionary views for auditing. (catnoaud.sql will remove)
catblock.sql - create views of oracle locks
catclust.sql - create cluster database specific views definitions
catdbsyn.sql - catalog dba synonyms (dba_synonyms.sql)
catio.sql - collect i/o per table/object stats by statistical sampling
catjava.sql - catalog scripts for java
catjobq.sql - catalog views for the job queue
catjvm.sql - create user|dba|all_java_* views
catldap.sql - catalog for ldap pl/sql api
catldr.sql - views for the direct path of the loader
catlsby.sql - logical standby tables and views
catpart.sql - creates data dictionary views for the partitioning table.
catoctk.sql - catalog - oracle cryptographic toolkit
catol.sql - outline views and synonyms
catparr.sql - parallel-server specific views, performance queries, etc
catpatch.sql - script to apply catalog patch set
catpitr.sql - tablespace point in time specific views
catplug.sql - pluggable tablespace check views
catprc.sql - creates data dictionary views for types, stored procedures, and triggers.
catproc.sql - run all sql scripts for the procedural option
catqm.sql - creates the tables and views needed to run the xdb system
catqueue.sql - contains the queue dictionary information
catrep.sql - run all sql scripts for replication option
catrepc.sql - repcat tables, views, and sequences
catrepm.sql - catalog script for master replication packages
catrepr.sql - catalog replication remove
catreps.sql - installs pl/sql packages for snapshot functionality.
catrls.sql - catalog views for row level security
catrm.sql - packages for the dbms resource manager
catrpc.sql - rpc procedural option
catrule.sql - rules catalog creation
catsnap.sql - data dictionary views for snapshots
catsnmp.sql - creates an snmpagent role
catspace.sql - catalog space management (remove with catspaced.sql)
catsum.sql - data dictionary views for summary management
catsvrmg.sql - create the views and tables required for server manager
catblock.sql - creates the view blocking_locks, which shows which locks are blocking the system
catnoaud.sql - removes the database audit trail created by cataudit.sql, including its data and views
catnoprc.sql - removes data dictionary structures that were created by catprc.sql 

Also keep in mind some script may call inside another script(s) and so on.

Source:
http://www.morganslibrary.org/reference/files.html
http://www.oracle-base.com/dba/scripts.php

Friday, December 28, 2012

How to stop/start RAC database in Oracle 11g

Using srvctl utility on ORACLE 11g we can stop, start and check status of RAC.

Stop/Start RAC instance on Oracle 11g

srvctl stop instance -d dbname -i instancename
srvctl stop instance -d myrac -i rac1

srvctl start instance -d dbname -i instancename
srvctl start instance -d myrac -i rac2


Stop/Start RAC database

srvctl stop database -d dbname -o immediate
srvctl stop database -d myrac -o immediate

P.S: immediate is optional and has alternative : normal, transactional, abort 

srvctl start database -d dbname
srvctl start database -d myrac

P.S: Can be used : nomount, mount, open


Status RAC {NODE, ASM, INSTANCE, DATABASE} 

srvctl status nodeapps -n nodename
srvctl status nodeapps -n rac2

srvctl status asm -n nodename
srvctl status asm -n rac1

srvctl status instance -d dbname -i inst_name
srvctl status instance -d myrac -i rac2

srvctl status database -d racdb
srvctl status database -d racdb

srvctl config database -d racdb --provide hostname, instance_name, path
srvctl config database -d racdb

If you requested to stop RAC database follow steps:.

Before take fresh backup RAC clusters database

1. Shutdown any process in Oracle home that might be accessing a database ex: EM

2.  Shutdown RAC cluster database

Shut down all instance one-by-one
srvctl stop instance -d dbname -i instancename

Or you can shutdown RAC database (all instance will be shutting down)
srvctl stop database -d dbname

3. Shutdown ASM instances
srvcstl stop asm -n node

4.Stop all node applications
srvctl stop nodeapps -n node

5. Shutdown CRS (Cluster Ready Services)


Above guide for Oracle 11g R1. Till Oracle 11g R2 OCR and VOTE resides out of ASM, starting R2 it locates under ASM, so stopping cluster -all will be shutdown clear.

In Oracle 11g R2 there are a few commands to be consistency shutting down RAC

srvctl stop database -d dbname
crsctl stop cluster -all

Especially thanks to my friend, to Mahir for correcting and advice.

Source: http://docs.oracle.com/cd/B28359_01/install.111/b28251/procstop.htm

Tuesday, December 25, 2012

Oracle 11g Enterprise Manager is not able to connect to the database instance.


Today I try to open EM and monitor DB. Unfortunately it was failed and show me "Enterprise Manager is not able to connect to the database instance. The state of the components are listed below."



Authentication passed but it does not work.

If problem on authentication check sysman user`s account status.

SQL> select account_status from dba_users where username='SYSMAN';

Also it will helpful checking emctl`s log which locates under:

/u01/app/oracle/product/11.2.0/dbhome_1/localhost.localdomain_ulfetdb/sysman/log


After checking logs I decided to recreate dbcontrol using below command. (Unfortunately I could not provide content of log)

It will ask some parameters (some of them optional, just enter and skip)
Also to be patient, it may last several minutes...

[oracle@localhost ~]$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Dec 26, 2012 10:19:51 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: ulfetdb
Database Control is already configured for the database ulfetdb
You have chosen to configure Database Control for managing the database ulfetdb
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: yes
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/dbhome_1 ]: listener
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/dbhome_1

Local hostname ................ localhost.localdomain
Listener ORACLE_HOME ................ listener
Listener port number ................ 1521
Database SID ................ ulfetdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
Dec 26, 2012 10:21:01 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/ulfetdb/emca_2012_12_26_10_19_50.log.
Dec 26, 2012 10:21:02 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Dec 26, 2012 10:21:25 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Dec 26, 2012 10:28:38 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Dec 26, 2012 10:28:39 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Dec 26, 2012 10:43:01 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Dec 26, 2012 10:43:30 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Dec 26, 2012 10:45:19 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Dec 26, 2012 10:45:20 AM oracle.sysman.emcp.ParamsManager getLocalListener
WARNING: Error retrieving listener for localhost.localdomain
Dec 26, 2012 10:45:30 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Dec 26, 2012 10:45:30 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Dec 26, 2012 10:46:14 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Dec 26, 2012 10:46:14 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Dec 26, 2012 10:46:52 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Dec 26, 2012 10:46:52 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Dec 26, 2012 10:48:42 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Dec 26, 2012 10:48:42 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://localhost.localdomain:1158/em <<<<<<<<<<<
Dec 26, 2012 10:48:49 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /u01/app/oracle/product/11.2.0/dbhome_1/localhost.localdomain_ulfetdb/sysman/config/emkey.ora.   Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Dec 26, 2012 10:48:49 AM

Let`s check



That`s all.

Alternatively you can directly drop repository and then create it again using below commands.

emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create

Generate script to compile invalid objects

Sometimes some database object(s) stays invalid (there is a lot of reason: modification object, compiling new procedure, function, package (depending exists) etc).

Easy method is compile @$ORACLE_HOME/rdbms/admin/utlrp.sql or detect invalid objects and recompile it. But it is dangerous every time to use utlrp, it is recommended to use utlrp when upgrade db version ex : 9 > 10 > 11 (also release upgrade). Because while upgrade process DB is not available to use for end user.

But if invalid object count is not big and not a few you can use below single SQL and compile result of it.

SQL> select 'ALTER TRIGGER '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='TRIGGER'
union
select 'ALTER FUNCTION '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='FUNCTION'
union
select 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='PACKAGE BODY'
union
select 'ALTER PROCEDURE '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='PROCEDURE'

EX:

ALTER FUNCTION ULFET.TEST_FUNC COMPILE;
ALTER PACKAGE ULFET.LOG_TOOL COMPILE BODY;
ALTER PROCEDURE ULFET.UPDATE_EMP COMPILE;
ALTER PROCEDURE ULFET.ADD_EMP COMPILE;
ALTER TRIGGER ULFET.TAKE_OLD_VALUE COMPILE;

Also you can use DBMS_DDL and DBMS_UTILITY packages:

EXEC DBMS_DDL.ALTER_COMPILE('PACKAGE', 'ULFET', 'LOG_TOOL');
or
EXEC DBMS_UTILITY.COMPILE_SCHEMA('ULFET');

Also you can create PLSQL anonymous block and compile it.

SQL> set serverouput on
SQL> DECLARE
   comp_pack        VARCHAR2 (100);
   comp_pack_body   VARCHAR2 (200);
   comp_view        VARCHAR2 (200);
   comp_proc        VARCHAR2 (200);
   comp_trig        VARCHAR2 (200);
   comp_func        VARCHAR2 (200);
BEGIN
   FOR c IN (  SELECT * FROM dba_objects WHERE status = 'INVALID' ORDER BY object_type)
   LOOP
      BEGIN
         --generate compile statement
         comp_pack :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_pack_body :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile body;';
         comp_view :='alter view ' || c.owner || '.' || c.object_name || ' compile;';
         comp_proc :='alter procedure '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_func :='alter function '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_trig :='alter trigger '|| c.owner|| '.'|| c.object_name|| ' compile;';
         DBMS_OUTPUT.put_line ('Compile -> ' || c.object_name || ' type : ' || c.object_type);
         --compile
         IF c.object_type = 'PACKAGE' THEN
            EXECUTE IMMEDIATE comp_pack;
         ELSIF c.object_type = 'PACKAGE BODY' THEN
            EXECUTE IMMEDIATE comp_pack_body;
         ELSIF c.object_type = 'VIEW' THEN
            EXECUTE IMMEDIATE comp_view;
         ELSIF c.object_type = 'PROCEDURE' THEN
            EXECUTE IMMEDIATE comp_proc;
         ELSIF c.object_type = 'FUNCTION' THEN
            EXECUTE IMMEDIATE comp_func;
         ELSIF c.object_type = 'TRIGGER' THEN
            EXECUTE IMMEDIATE comp_trig;
         END IF;
         --catch exception and show
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line ('Compile ERROR : '|| c.owner|| '.'|| c.object_name|| ' type => '|| c.object_type);
      END;
   END LOOP;
END;


Read it in azerbaijani : http://www.slideshare.net/ulfettanriverdiyev/invalid-obyektlerin-kampilasiyasi

Monday, December 24, 2012

ORA-02085: Database link created successfully but does not work

I want to create database link on base a database. It means link from database to itself.

My database name is: MYDB

SQL> select name from v$database;

NAME
---------
MYDB

SQL>

--Now try to create database link via hr user.

SQL> create database link mydb_mydb connect to hr identified by hr using 'mydb';

Database link created.

SQL>

--Succeed, now let`s check.

SQL> select * from dual@mydb_mydb;
select * from dual@mydb_mydb
                   *
ERROR at line 1:
ORA-02085: database link MYDB_MYDB.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to
MYDB.REGRESS.RDBMS.DEV.US.ORACLE.COM


What is that ?

After researching I found:

ORA-02085:
database link string connects to string
Cause:
A database link connected to a database with a different name. The connection is rejected.
Action:
create a database link with the same name as the database the database it connects to, or set global_names=false. 
Global_names parameter indeed true on my database, lets change it.

SQL> show parameter global_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE


SQL> alter system set global_names=FALSE scope=both;

System altered.


SQL> show parameter global_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     FALSE


Drop database link which created before.

SQL> drop database link mydb_mydb;

Database link dropped.

SQL> create database link mydb_mydb connect to hr identified by hr using 'mydb';

Database link created.

--No check database link

SQL> select * from dual@mydb_mydb;

D
-
X

SQL>

It is work.

What else if global_names`s TRUE and I try to create db link with same same of SID ?


SQL> create database link mydb connect to hr identified by hr using 'mydb';
create database link mydb connect to hr identified by hr using 'mydb'
                          *
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier


To prevent this kind of error rename global name.


SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
MYDB.REGRESS.RDBMS.DEV.US.ORACLE.COM





SQL> alter database rename global_name to TESTDB.TESTDOMAIND;

Database altered.

SQL> create database link mydb connect to hr identified by hr using 'mydb';

Database link created.

DB link created, bu still does not work.


SQL> select * from dual@mydb;
select * from dual@mydb
                   *
ERROR at line 1:
ORA-02085: database link MYDB.TESTDOMAIND connects to TESTDB.TESTDOMAIND


Now update global_name`s again and check db link.


SQL> alter database rename global_name to MYDB;

Database altered.

SQL> select * from dual@mydb;

D
-
X








Wednesday, December 19, 2012

Which session(s) generate a lot of redo log(s)

How to find which SQL statement generates lots of redo logs.

Firstly use below query to find out which sessions generate more redo logs:

SELECT  s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid AND i.block_changes > 0
ORDER BY 5 DESC, 1;

block_changes column provide us how much blocks have been changed the session

P.S: You can omit block_changes > 0 condition if you want to see all result

But, what about if you want to see amount of undo blocks and undo records accessed by the transaction?
To do that use:

SELECT s.sid, s.serial#, s.username, s.program, s.machine, t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 6, 7 desc;

At the end, to get object lists which block changed historical information:

SELECT dhso.object_name, object_type, SUM (db_block_changes_delta)
FROM dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhso, dba_hist_snapshot dhs
WHERE     dhs.snap_id = dhss.snap_id 
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND begin_interval_time BETWEEN TO_DATE ('20-12-2012 00:00:00', 'DD-MM-YYYY HH24:mi:ss') AND TO_DATE ('20-12-201223:59:59', 'DD-MM-YYYY HH24:mi:ss')
GROUP BY dhso.object_name, object_type
HAVING SUM (db_block_changes_delta) > 0
ORDER BY 2, SUM (db_block_changes_delta) DESC

And to get which SQL statement used :

SELECT distinct dbms_lob.substr(sql_text,4000,1), optimizer_mode, module
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%AZ_PAY%' AND 
dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id --and rownum<2;

Not: You can add additional column to get more detail info for investigation, such as :
use : dba_hist_sqlstat dyanmic view`s columns : cpu_time_total, cpu_time_delta, elapsed_time_total, elapsed_time_delta, iowait_total, disk_reads_total etc 

Top 10 sessions which generate more redo logs:

select b.inst_id, b.SID, b.serial# sid_serial, b.username, machine, b.osuser, b.status, a.redo_mb MB
from (select n.inst_id, sid, round(value/1024/1024) redo_mb from gv$statname n, gv$sesstat s
where n.inst_id=s.inst_id and n.statistic#=134 and s.statistic# = n.statistic# order by value desc) a, gv$session b
where b.inst_id=a.inst_id
  and a.sid = b.sid
and   rownum <= 10;

Total count of redo generation:

select sum(round(gb)) total_redo_count from (select b.inst_id, b.SID, b.serial# sid_serial, b.username, machine, b.osuser, b.status, a.redo_gb GB 
from (select n.inst_id, sid, round(value/1024/1024/1024) redo_gb from gv$statname n, gv$sesstat s 
where n.inst_id=s.inst_id and n.statistic#=134 and s.statistic# = n.statistic# order by value desc ) a, gv$session b
where b.inst_id=a.inst_id
and a.sid = b.sid)

To read in azerbaijani: http://www.slideshare.net/ulfettanriverdiyev/hansi-sessiya-daha-cox-redo-generasiya-edir

Friday, December 14, 2012

Oracle 11g new features: Result cache

One of the best feature for Oracle 11g is setting result cache. Result cache reside in Shared Pool area in SGA. Now database can serves result for queries from the cache instead of re-execute query again and again. But when depending database object is modified cache result stays invalid. Using result cache you can improve performance of your query. It is reduce I/O, sorts, computations.

There are some restrictions for result cache which could not store data:
  • Data dictionary objects;
  • Temporary tables;
  • Queries with bind variables (if different) ;
  • Flashback query`s result;
  • Some SQL functions : current_date, current/local_timestamp, sysdate etc;
  • Function which has any OUT or IN OUT parameter;
  • Function has IN parameter that are CLOB, BLOB, REF CURSOR etc;
  • etc
How to configure result cache
New initialize parameters RESULT_CACHE_MAX_SIZE and RESULT_CACHE_MODE exists on Oracle 11g. You can enable/disable result cache using  RESULT_CACHE_MAX_SIZE parameter.
If value of RESULT_CACHE_MAX_SIZE = 0  it means this feature is disabled.
Memory allocate for the result cache is taken from the shared pool.

There are three different modes you can use and they are managed with RESULT_CACHE_MODE init parameter. This parameter is dynamic and can be changed with ALTER SYSTEM and ALTER SESSION. Default is MANUAL.

Using result_cache hint you can use to manually cache a result of query to override the default setting of there parameter in a session level. Also there is has no_result_cache hint.

There are has several views:

V$RESULT_CACHE_STATISTICS
Lists various server result cache settings and memory usage statistics.
V$RESULT_CACHE_MEMORY
Lists all the memory blocks in the server result cache and their corresponding statistics.
V$RESULT_CACHE_OBJECTS
Lists all the objects whose results are in the
server result cache along with their attributes.
V$RESULT_CACHE_DEPENDENCY
Lists the dependency details between the results in the server cache and dependencies among these results.
CLIENT_RESULT_CACHE_STATS$
Stores cache settings and memory usage statistics for the client result caches obtained from the OCI client processes.

Check resulting our database`s parameters for result cache

SQL> col name format a30
SQL> col value format a20
SQL> select name,value from v$parameter where name like 'result_cache%';

NAME                                        VALUE
------------------------------      --------------------
result_cache_mode                      MANUAL
result_cache_max_size                1605632
result_cache_max_result              5
result_cache_remote_expiration   0

Now check memory utilization in SHARED POOL:

SQL> select * from v$sgastat where pool='shared pool' and name like 'Result%';

POOL         NAME                                    BYTES
------------ --------------------------        ----------
shared pool  Result Cache: State Objs         2852
shared pool  Result Cache: Memory Mgr     124
shared pool  Result Cache: Bloom Fltr         2048
shared pool  Result Cache: Cache Mgr        4416

SQL> select * from V$RESULT_CACHE_DEPENDENCY;

no rows selected

SQL> desc dbms_result_cache;
PROCEDURE BYPASS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BYPASS_MODE                    BOOLEAN                 IN
 SESSION                        BOOLEAN                 IN     DEFAULT
FUNCTION DELETE_DEPENDENCY RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNER                          VARCHAR2                IN
 NAME                           VARCHAR2                IN
PROCEDURE DELETE_DEPENDENCY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNER                          VARCHAR2                IN
 NAME                           VARCHAR2                IN
FUNCTION DELETE_DEPENDENCY RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_ID                      BINARY_INTEGER          IN
PROCEDURE DELETE_DEPENDENCY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_ID                      BINARY_INTEGER          IN
FUNCTION FLUSH RETURNS BOOLEAN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RETAINMEM                      BOOLEAN                 IN     DEFAULT
 RETAINSTA                      BOOLEAN                 IN     DEFAULT
 GLOBAL                         BOOLEAN                 IN     DEFAULT
PROCEDURE FLUSH
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RETAINMEM                      BOOLEAN                 IN     DEFAULT
 RETAINSTA                      BOOLEAN                 IN     DEFAULT
 GLOBAL                         BOOLEAN                 IN     DEFAULT
FUNCTION INVALIDATE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNER                          VARCHAR2                IN
 NAME                           VARCHAR2                IN
PROCEDURE INVALIDATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNER                          VARCHAR2                IN
 NAME                           VARCHAR2                IN
FUNCTION INVALIDATE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_ID                      BINARY_INTEGER          IN
PROCEDURE INVALIDATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OBJECT_ID                      BINARY_INTEGER          IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             BINARY_INTEGER          IN
PROCEDURE INVALIDATE_OBJECT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ID                             BINARY_INTEGER          IN
FUNCTION INVALIDATE_OBJECT RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CACHE_ID                       VARCHAR2                IN
PROCEDURE INVALIDATE_OBJECT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CACHE_ID                       VARCHAR2                IN
PROCEDURE MEMORY_REPORT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DETAILED                       BOOLEAN                 IN     DEFAULT
FUNCTION STATUS RETURNS VARCHAR2

SQL> 

Use memory utilization report for result cache:

SQL> begin dbms_result_cache.memory_report; end;
  2  /
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 1568K bytes (1568 blocks)
Maximum Result Size = 78K bytes (78 blocks)
[Memory]
Total Memory = 9440 bytes [0.005% of the Shared Pool]
... Fixed Memory = 9440 bytes [0.005% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]

PL/SQL procedure successfully completed.

SQL> select * from v$result_cache_objects;

no rows selected

SQL>

For now result cache is clear, let`s cache query result.

I will use result_cache hint override the default setting of parameter in the session.

No I will create 2 same tables with same structure and data. Then using hint I will execute query and will take explain plan (also without hint I will execute query on base another table also will take explain plan) 
and at the end I will compare explain plans and execution times.


SQL> create table ulfet.big_table as select * from dba_objects;

Table created.

SQL> insert into ulfet.big_table select * from ulfet.big_table;

72526 rows created.

SQL> /

145052 rows created.

SQL> /

290104 rows created.

SQL> /

580208 rows created.

SQL> commit;

Commit complete.

SQL>


SQL> select count(1) from big_table;

  COUNT(1)
----------
   1160416

SQL>

Now create second table on base big_table

SQL> create table big_table2 as select * from big_table;

Table created.

SQL> 

Now using below query I will cache query result

SQL> SELECT /*+ RESULT_CACHE */ 
count(*) , object_type 
FROM ulfet.big_table GROUP BY object_type
order by 1;  2    3    4  

  COUNT(*) OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 RULE
        16 EDITION
        32 JAVA SOURCE
        32 DESTINATION
        48 MATERIALIZED VIEW
        48 SCHEDULE
        64 SCHEDULER GROUP
        80 DIMENSION
       112 CONTEXT
       144 INDEXTYPE

  COUNT(*) OBJECT_TYPE
---------- -------------------
       144 WINDOW
       144 UNDEFINED
       160 CLUSTER
       160 RESOURCE PLAN
       160 DIRECTORY
       208 JOB CLASS
       224 JOB
       240 EVALUATION CONTEXT
       304 PROGRAM
       368 RULE SET
       400 CONSUMER GROUP

  COUNT(*) OBJECT_TYPE
---------- -------------------
       640 QUEUE
       832 XML SCHEMA
       880 OPERATOR
      2304 TABLE PARTITION
      2512 PROCEDURE
      2928 LIBRARY
      3664 SEQUENCE
      3840 TYPE BODY
      4800 INDEX PARTITION
      4832 FUNCTION
      4944 JAVA DATA

  COUNT(*) OBJECT_TYPE
---------- -------------------
      9872 TRIGGER
     13344 JAVA RESOURCE
     14720 LOB
     20000 PACKAGE BODY
     20960 PACKAGE
     45120 TYPE
     45968 TABLE
     62592 INDEX
     80944 VIEW
    366720 JAVA CLASS
    444880 SYNONYM

44 rows selected.

SQL> 

#Now looking at the explain plan

SQL> set autotrace trace explain statistics
SELECT /*+ RESULT_CACHE */ 
count(*) , object_type 
FROM ulfet.big_table GROUP BY object_type
order by 1;
SQL>   2    3    4  
44 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 446130829

--------------------------------------------------------------------------------
-------------------

| Id  | Operation            | Name                       | Rows  | Bytes | Cost
 (%CPU)| Time     |

--------------------------------------------------------------------------------
-------------------

|   0 | SELECT STATEMENT     |                            |  1393K|    14M|  473
8   (3)| 00:00:57 |

|   1 |  RESULT CACHE        | 2dp92r6vy7zz94wfhzvrp1jz3w |       |       |
       |          |

|   2 |   SORT ORDER BY      |                            |  1393K|    14M|  473
8   (3)| 00:00:57 |

|   3 |    HASH GROUP BY     |                            |  1393K|    14M|  473
8   (3)| 00:00:57 |

|   4 |     TABLE ACCESS FULL| BIG_TABLE                  |  1393K|    14M|  465
7   (1)| 00:00:56 |

--------------------------------------------------------------------------------
-------------------


Result Cache Information (identified by operation id):
------------------------------------------------------

   1 - column-count=2; dependencies=(ULFET.BIG_TABLE); parameters=(nls); name="S
ELECT /*+ RESULT_CACHE */
count(*) , object_type
FROM ulfet.big_table GROUP BY object_type
order by 1"


Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1569  bytes sent via SQL*Net to client
        441  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         44  rows processed

SQL> 

Now check result cache object view:

SQL> SELECT id, type, name, scn, row_count FROM  v$result_cache_objects;


Execution Plan
----------------------------------------------------------
Plan hash value: 2938610334

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |   131 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$QESRCOBJ |     1 |   131 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("INST_ID"=USERENV('INSTANCE'))


Statistics
----------------------------------------------------------
        310  recursive calls
          0  db block gets
         67  consistent gets
         10  physical reads
          0  redo size
        841  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>


SQL> SELECT * FROM v$result_cache_statistics;

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3700223219

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |   118 |     0   (0)| 00:00:01 |
|*  1 |  FIXED TABLE FULL| X$QESRCSTA |     1 |   118 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("INST_ID"=USERENV('INSTANCE'))


Statistics
----------------------------------------------------------
        285  recursive calls
          0  db block gets
         69  consistent gets
          1  physical reads
          0  redo size
        887  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> SELECT * FROM v$result_cache_statistics;

        ID NAME                           VALUE
---------- ------------------------------ --------------------
         1 Block Size (Bytes)             1024
         2 Block Count Maximum            1568
         3 Block Count Current            32
         4 Result Size Maximum (Blocks)   78
         5 Create Count Success           1
         6 Create Count Failure           0
         7 Find Count                     4
         8 Invalidation Count             0
         9 Delete Count Invalid           0
        10 Delete Count Valid             0
        11 Hash Chain Length              1

11 rows selected.

Elapsed: 00:00:00.03
SQL> 

Now enable timing to check elapsed time of query

SQL> set autotrace off
SQL> set timing on
SQL> SELECT /*+ RESULT_CACHE */ 
count(*) , object_type 
FROM ulfet.big_table GROUP BY object_type
order by 1;SQL>   2    3    4  

  COUNT(*) OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 RULE
        16 EDITION
        32 JAVA SOURCE
        32 DESTINATION
        48 MATERIALIZED VIEW
        48 SCHEDULE
        64 SCHEDULER GROUP
        80 DIMENSION
       112 CONTEXT
       144 INDEXTYPE

  COUNT(*) OBJECT_TYPE
---------- -------------------
       144 WINDOW
       144 UNDEFINED
       160 CLUSTER
       160 RESOURCE PLAN
       160 DIRECTORY
       208 JOB CLASS
       224 JOB
       240 EVALUATION CONTEXT
       304 PROGRAM
       368 RULE SET
       400 CONSUMER GROUP

  COUNT(*) OBJECT_TYPE
---------- -------------------
       640 QUEUE
       832 XML SCHEMA
       880 OPERATOR
      2304 TABLE PARTITION
      2512 PROCEDURE
      2928 LIBRARY
      3664 SEQUENCE
      3840 TYPE BODY
      4800 INDEX PARTITION
      4832 FUNCTION
      4944 JAVA DATA

  COUNT(*) OBJECT_TYPE
---------- -------------------
      9872 TRIGGER
     13344 JAVA RESOURCE
     14720 LOB
     20000 PACKAGE BODY
     20960 PACKAGE
     45120 TYPE
     45968 TABLE
     62592 INDEX
     80944 VIEW
    366720 JAVA CLASS
    444880 SYNONYM

44 rows selected.

Elapsed: 00:00:00.02
SQL> 

repeat again

SQL> /

  COUNT(*) OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 RULE
        16 EDITION
        32 JAVA SOURCE
        32 DESTINATION
        48 MATERIALIZED VIEW
        48 SCHEDULE
        64 SCHEDULER GROUP
        80 DIMENSION
       112 CONTEXT
       144 INDEXTYPE

  COUNT(*) OBJECT_TYPE
---------- -------------------
       144 WINDOW
       144 UNDEFINED
       160 CLUSTER
       160 RESOURCE PLAN
       160 DIRECTORY
       208 JOB CLASS
       224 JOB
       240 EVALUATION CONTEXT
       304 PROGRAM
       368 RULE SET
       400 CONSUMER GROUP

  COUNT(*) OBJECT_TYPE
---------- -------------------
       640 QUEUE
       832 XML SCHEMA
       880 OPERATOR
      2304 TABLE PARTITION
      2512 PROCEDURE
      2928 LIBRARY
      3664 SEQUENCE
      3840 TYPE BODY
      4800 INDEX PARTITION
      4832 FUNCTION
      4944 JAVA DATA

  COUNT(*) OBJECT_TYPE
---------- -------------------
      9872 TRIGGER
     13344 JAVA RESOURCE
     14720 LOB
     20000 PACKAGE BODY
     20960 PACKAGE
     45120 TYPE
     45968 TABLE
     62592 INDEX
     80944 VIEW
    366720 JAVA CLASS
    444880 SYNONYM

44 rows selected.

Elapsed: 00:00:00.00
SQL> 

Waw, 00:00:00, EXCELLENT, is it ? :)

Now check another table`s statistic.

SQL> set autotrace trace explain statistics
SQL> SELECT count(*), object_type
FROM ulfet.big_table2 GROUP BY object_type
order by 1;  2    3

44 rows selected.

Elapsed: 00:00:06.44

Execution Plan
----------------------------------------------------------
Plan hash value: 244325093

--------------------------------------------------------------------------------
--

| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time
 |

--------------------------------------------------------------------------------
--

|   0 | SELECT STATEMENT    |            |   960K|    10M|  4586   (2)| 00:00:56
 |

|   1 |  SORT ORDER BY      |            |   960K|    10M|  4586   (2)| 00:00:56
 |

|   2 |   HASH GROUP BY     |            |   960K|    10M|  4586   (2)| 00:00:56
 |

|   3 |    TABLE ACCESS FULL| BIG_TABLE2 |   960K|    10M|  4531   (1)| 00:00:55
 |

--------------------------------------------------------------------------------
--


Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
      16585  consistent gets
      16711  physical reads
          0  redo size
       1569  bytes sent via SQL*Net to client
        441  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         44  rows processed

SQL> set autotrace off
SQL> SELECT count(*), object_type
FROM ulfet.big_table2 GROUP BY object_type
order by 1;  2    3

  COUNT(*) OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 EDITION
        16 RULE
        32 JAVA SOURCE
        32 DESTINATION
        48 MATERIALIZED VIEW
        48 SCHEDULE
        64 SCHEDULER GROUP
        80 DIMENSION
       112 CONTEXT
       144 UNDEFINED

  COUNT(*) OBJECT_TYPE
---------- -------------------
       144 WINDOW
       144 INDEXTYPE
       160 DIRECTORY
       160 CLUSTER
       160 RESOURCE PLAN
       208 JOB CLASS
       224 JOB
       240 EVALUATION CONTEXT
       304 PROGRAM
       368 RULE SET
       400 CONSUMER GROUP

  COUNT(*) OBJECT_TYPE
---------- -------------------
       640 QUEUE
       832 XML SCHEMA
       880 OPERATOR
      2304 TABLE PARTITION
      2512 PROCEDURE
      2928 LIBRARY
      3664 SEQUENCE
      3840 TYPE BODY
      4800 INDEX PARTITION
      4832 FUNCTION
      4944 JAVA DATA

  COUNT(*) OBJECT_TYPE
---------- -------------------
      9872 TRIGGER
     13344 JAVA RESOURCE
     14720 LOB
     20000 PACKAGE BODY
     20960 PACKAGE
     45120 TYPE
     45968 TABLE
     62592 INDEX
     80944 VIEW
    366720 JAVA CLASS
    444880 SYNONYM

44 rows selected.

Elapsed: 00:00:00.80

Re-execute query again

SQL> /

  COUNT(*) OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 EDITION
        16 RULE
        32 JAVA SOURCE
        32 DESTINATION
        48 MATERIALIZED VIEW
        48 SCHEDULE
        64 SCHEDULER GROUP
        80 DIMENSION
       112 CONTEXT
       144 UNDEFINED

  COUNT(*) OBJECT_TYPE
---------- -------------------
       144 WINDOW
       144 INDEXTYPE
       160 DIRECTORY
       160 CLUSTER
       160 RESOURCE PLAN
       208 JOB CLASS
       224 JOB
       240 EVALUATION CONTEXT
       304 PROGRAM
       368 RULE SET
       400 CONSUMER GROUP

  COUNT(*) OBJECT_TYPE
---------- -------------------
       640 QUEUE
       832 XML SCHEMA
       880 OPERATOR
      2304 TABLE PARTITION
      2512 PROCEDURE
      2928 LIBRARY
      3664 SEQUENCE
      3840 TYPE BODY
      4800 INDEX PARTITION
      4832 FUNCTION
      4944 JAVA DATA

  COUNT(*) OBJECT_TYPE
---------- -------------------
      9872 TRIGGER
     13344 JAVA RESOURCE
     14720 LOB
     20000 PACKAGE BODY
     20960 PACKAGE
     45120 TYPE
     45968 TABLE
     62592 INDEX
     80944 VIEW
    366720 JAVA CLASS
    444880 SYNONYM

44 rows selected.

Elapsed: 00:00:00.37

As you see Oracle use Shared Pool (before used SQL query) but it is not affected as result cache feature.

To monitor result cache use cache_id value provided explain plan on V$RESULT_CACHE_OBJECTS:

SQL> select
NAME, STATUS, ROW_COUNT, BLOCK_COUNT, NAMESPACE, CREATION_TIMESTAMP
from   V$RESULT_CACHE_OBJECTS
where  CACHE_ID='2dp92r6vy7zz94wfhzvrp1jz3w';  2    3

NAME                           STATUS     ROW_COUNT BLOCK_COUNT NAMES CREATION_
------------------------------ --------- ---------- ----------- ----- ---------
SELECT /*+ RESULT_CACHE */     Published         44           1 SQL   14-DEC-12
count(*) , object_type
FROM ulfet.big_table GROUP BY
object_type
order by 1

Elapsed: 00:00:00.01
SQL>


You can also use below statement for monitoring:

SQL> select DBMS_RESULT_CACHE.STATUS from dual;


Result cache can be flushed using :

SQL> exec DBMS_RESULT_CACHE.FLUSH;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.24

Check statistic again

SQL> SELECT * FROM v$result_cache_statistics;

        ID NAME                           VALUE
---------- ------------------------------ --------------------
         1 Block Size (Bytes)             1024
         2 Block Count Maximum            1568
         3 Block Count Current            0
         4 Result Size Maximum (Blocks)   78
         5 Create Count Success           0
         6 Create Count Failure           0
         7 Find Count                     0
         8 Invalidation Count             0
         9 Delete Count Invalid           0
        10 Delete Count Valid             0
        11 Hash Chain Length              0

11 rows selected.

SQL>

Conclusion :
Result cache provide us data (result of query) from memory and not read it from file. But it`s expensive, you can compare above query`s explain plan and can easy see the different.

Source:
http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#PFGRF987
http://docs.oracle.com/cd/E11882_01/server.112/e16638/memory.htm#PFGRF978
http://www.dba-oracle.com/oracle11g/oracle_11g_result_cache_sql_hint.htm


Monday, December 10, 2012

Export from Oracle 11g and Import to Oracle 10g

Using EXPDP take dump from higher version and import into lower version. As you know EXPDP utility firstly was introduce in Oracle 10g. This is enhanced EXP/IMP utility (with additional features, example transport tablespace).
I will use Oracle 11g R2 (11.2.0.1.0) and Oracle 10g R2 (10.2.0.4)

Let`s create directory on Oracle 11g database and give read, write permission.

SQL> create directory my_dir as '/u01/app/oracle/admin/ulfetdb/dpdump';

Directory created.

SQL> grant read, write on directory my_dir to ulfet;

Grant succeeded.

SQL>

Now I will connect as ulfet user and will create new table and insert into one record.

SQL> conn ulfet/ulfet
Connected.

SQL> create table test_tbl (id number, name varchar2(10));

Table created.

SQL> insert into test_tbl values(1, 'Omar');

1 row created.

SQL> commit;

Commit complete.

Time to take export of table using DATAPUMP.

[oracle@localhost dpdump]$ expdp ulfet/ulfet@ulfetdb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl version=10.2

Export: Release 11.2.0.1.0 - Production on Mon Dec 10 14:45:54 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ULFET"."SYS_EXPORT_TABLE_01":  ulfet/********@ulfetdb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl version=10.2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ULFET"."TEST_TBL"                          5.289 KB       1 rows
Master table "ULFET"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ULFET.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/ulfetdb/dpdump/test_tbl.dmp
Job "ULFET"."SYS_EXPORT_TABLE_01" successfully completed at 14:46:29

[oracle@localhost dpdump]$

--Take dmp file to Oracle 10g`s db server and use same utility DATAPUMP`s IMPDP.

--To be sure directory works properly. I will create same directory on 10g.

SQL> create directory my_dir as '/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/dpdump'; 

Directory created.

SQL> grant read, write on directory my_dir to ulfet;

Grant succeeded.

SQL> 

Now try to imp

[oracle@localhost ~]$ impdp ulfet/ulfet@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl

Import: Release 10.2.0.4.0 - Production on Monday, 10 December, 2012 13:43:54

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ULFET"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ULFET"."SYS_IMPORT_TABLE_01":  ulfet/********@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00959: tablespace 'REC_TBS' does not exist
Failing sql is:
CREATE TABLE "ULFET"."TEST_TBL" ("ID" NUMBER, "NAME" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "REC_TBS" 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "ULFET"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 13:44:07

[oracle@localhost ~]$ 

--There is error. Tablespace is different. To prevent such error use remap_tablespace option.

[oracle@localhost ~]$ impdp ulfet/ulfet@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl remap_tablespace=REC_TBS:USERS

Import: Release 10.2.0.4.0 - Production on Monday, 10 December, 2012 13:46:22

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ULFET"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ULFET"."SYS_IMPORT_TABLE_01":  ulfet/********@mydb directory=my_dir dumpfile=test_tbl.dmp tables=test_tbl remap_tablespace=REC_TBS:USERS 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ULFET"."TEST_TBL"                          5.289 KB       1 rows
Job "ULFET"."SYS_IMPORT_TABLE_01" successfully completed at 13:46:24

[oracle@localhost ~]$

--Now, connect as ulfet and check table

SQL> conn ulfet/ulfet
Connected.

SQL> select * from test_tbl;

        ID NAME
---------- ----------
         1 Omar

SQL> 

--Yes, this task completed.

--But what will happen if user omit version=10.2 during export/import. 
--Export will work normal as usual but while import data you will face exception such as ORA-39142.

--Let`s see.

--Oracle 11g

[oracle@localhost dpdump]$ expdp ulfet/ulfet@ulfetdb directory=my_dir dumpfile=test_tbl2.dmp tables=test_tbl

Export: Release 11.2.0.1.0 - Production on Mon Dec 10 15:13:11 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ULFET"."SYS_EXPORT_TABLE_01":  ulfet/********@ulfetdb directory=my_dir dumpfile=test_tbl2.dmp tables=test_tbl 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ULFET"."TEST_TBL"                          5.414 KB       1 rows
Master table "ULFET"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ULFET.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/ulfetdb/dpdump/test_tbl2.dmp
Job "ULFET"."SYS_EXPORT_TABLE_01" successfully completed at 15:13:30

--Oracle 10g

[oracle@localhost ~]$ impdp ulfet/ulfet@mydb directory=my_dir dumpfile=test_tbl2.dmp tables=test_tbl remap_tablespace=REC_TBS:USERS

Import: Release 10.2.0.4.0 - Production on Monday, 10 December, 2012 13:53:30

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/dpdump/test_tbl2.dmp"

EXPDP has version options and supply the following Oracle versions:
  • Oracle 10g R1
  • Oracle 10g R2
  • Oracle 11g R1
  • Oracle 11g R2

To read in Azerbaijani : http://www.slideshare.net/ulfettanriverdiyev/oracle-11g-expdp-alib-oracle-10g-import-etmek-15608810

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...