External table in oracle 11g

External table in oracle 11g


A) First need to Create a Directory
=======================================
=======================================

SQL> CREATE DIRECTORY My_dir AS 'D:\dir_external\dir';


B) Then create External Table
=======================================
=======================================

1. External table normal in oracle 10g
=========================================

SQL> CREATE TABLE Noncompressed_all_objects
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_dir
LOCATION ( 'Noncompressed_all_obj.dmp' )
)
AS
SELECT *
FROM all_objects;


To see The size of file
-----------------------
SQL> host dir /od D:\dir_external\dir\*.DMP


2. External Table Compressed in Oracle 11g
=============================================

SQL> CREATE TABLE compressed_all_objects
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_dir
ACCESS PARAMETERS (COMPRESSION ENABLED)----Difference
LOCATION ( 'compressed_all_obj.dmp' )
)
AS
SELECT *
FROM all_objects;

To see compressed size
------------------
SQL> host dir /od D:\dir_external\dir\*.DMP


3. External Table Compressed in Oracle 11g
==============================================

SQL> CREATE TABLE encrypted_all_objects
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_dir
ACCESS PARAMETERS (ENCRYPTION ENABLED) ----Difference
LOCATION ( 'encrypted_all_obj.dmp' )
)
AS
SELECT *
FROM all_objects;


4. External Table Combining compression and encryption in Oracle 11g
===================================================================

SQL> CREATE TABLE combining_all_objects
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_dir
ACCESS PARAMETERS (ENCRYPTION ENABLED COMPRESSION ENABLED)
LOCATION ( 'combining_all_obj.dmp' )
)
AS
SELECT *
FROM all_objects;

[note: To change the location of this external table to use the compressed file]

SQL> ALTER TABLE Noncompressed_all_objects LOCATION ('compressed_all_obj.dmp');