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';

------------------------------ ------------------------------
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

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;


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


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

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: