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');
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Tuesday, October 20, 2009
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment