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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : June-2018

**********************************************************************************


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]


I am certified Oracle Cloud Architect:
Oracle Cloud Infrastructure 2018 Certified Architect Associate
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate

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: