Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, August 11, 2015

Oracle external tables using datapump

External table using datapump:

Below we will create an external table "sales_ch" and its data will be stored in a datapump file.


SQL> select username,default_tablespace from dba_users where username='SH';

USERNAME              DEFAULT_TABLESPACE
------------------------------ ------------------------------
SH                  USERS

SQL> create directory data_dir as '/home/oracle/sshaik';

Directory created.


SQL> create directory log_dir as '/home/oracle/sshaik/log';

Directory created.

SQL> grant all on directory data_dir to sh;



SQL> !pwd
/home/oracle/sshaik

SQL> !ls -lrt sales_ch.exp
ls: cannot access sales_ch.exp: No such file or directory


SQL> create table sales_ch organization external (
 2  type oracle_datapump
 3  default directory data_dir
 4  location ('sales_ch.exp')
 5  )
 6  as select * from sales
 7  where channel_id='4';

Table created.


Verify that the datapump file is created at the OS level:

SQL> !ls -lrt sales_ch.exp
-rw-r----- 1 oracle dba 4018176 Aug  5 15:25 sales_ch.exp

SQL> select count(*) from sales_ch;

 COUNT(*)
----------
    118416

SQL> select count(*) from sales where channel_id='4';

 COUNT(*)
----------
    118416


SQL> select object_name,object_type from dba_objects where object_name='SALES_CH';

OBJECT_NAME          
OBJECT_TYPE                   
--------------------------------------------------------------------------------
SALES_CH                        TABLE




SQL>  select owner,table_name,type_owner,type_name from dba_external_tables where table_name='SALES_CH'

OWNER                   TABLE_NAME              TYP TYPE_NAME
------------------------------ ------------------------------ --- ------------------------------
SH                   SALES_CH               SYS ORACLE_DATAPUMP


 
 

No comments: