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

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

****************Got the opportunity to pen a Book on Database Cloud Services.......More details to follow.***************

Title : Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2021


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Azure Certified Architect Expert

Azure Certified Architect

Azure Certified Administrator

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified 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';

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