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

oracle dblink create

oracle dblink create
=================

Create this Database public database link in local database
with remote specified user and password .
and TNS service name (which is created for that remote database).

* The single quotes around the service name are mandatory
* The service name must be in the TNSNAMES.ORA file on the server


============================
Needed Privilege
============================

Privilege Database Required For
--------------------------------------------------------------------------------
CREATE DATABASE LINK Local Creation of a private database link.
CREATE PUBLIC DATABASE LINK Local Creation of a public database link.
CREATE SESSION Remote Creation of any type of database link.


select *
FROM ROLE_SYS_PRIVS
WHERE PRIVILEGE IN ( 'CREATE SESSION','CREATE DATABASE LINK',
'CREATE PUBLIC DATABASE LINK')


CREATE DATABASE LINK with_REPORT
CONNECT TO basel2 IDENTIFIED BY basel2
USING 'REPORT';


DROP DATABASE LINK with_REPORT


SELECT * FROM STPASTDU@WITH_REPORT

truncate table stpastdu;

insert into stpastdu
select * from stpastdu@with_report ;

commit;

truncate table BASELGLTRA;

insert into BASELGLTRA
select * from BASELGLTRA@with_report ;

commit ;

truncate table BASELTRANS


SELECT * FROM all_db_links;

desc gv$session_connect_info

--------------

1.

CREATE DATABASE LINK supply.us.acme.com;

(A private link using the global database name to the remote supply database.
The link uses the userid/password of the connected user.
So if scott (identified by tiger) uses the link in a query,
the link establishes a connection to the remote database as scott/tiger.)

2.

CREATE DATABASE LINK link_2 CONNECT TO jane IDENTIFIED BY doe USING 'us_supply';

(A private fixed user link called link_2 to the database with service name us_supply.
The link connects to the remote database with the userid/password of jane/doe
regardless of the connected user.)

3.

CREATE DATABASE LINK link_1 CONNECT TO CURRENT_USER USING 'us_supply';


(A private link called link_1 to the database with service name us_supply.
The link uses the userid/password of the current user to log onto the remote database.)


1.Connected User Link

CREATE [SHARED] [PUBLIC] DATABASE LINK
CONNECT TO CURRENT_USER
USING '';

2.Current User Link

CREATE [PUBLIC] DATABASE LINK
CONNECT TO CURRENT_USER
USING '';

3.Fixed User Link

CREATE [PUBLIC] DATABASE LINK
CONNECT TO
IDENTIFIED BY
USING '';


4.Shared Link

CREATE SHARED DATABASE LINK
AUTHENTICATED BY IDENTIFIED BY
USING '';

conn test/test

CREATE SHARED DATABASE LINK shared
CONNECT TO scott IDENTIFIED BY tiger
AUTHENTICATED BY test IDENTIFIED BY test
USING 'link';

SELECT * FROM user_db_links;

SELECT table_name, tablespace_name FROM
user_tables@shared;

==============================
Executing Remote Procedures
==============================
@();

exec testproc@remote_db(1);

or

CREATE OR REPLACE SYNONYM testproc FOR remote_db.testproc;

exec testproc(1);

=======================
Test Link
=======================
declare
v_t varchar2(200);
BEGIN
ALTER SESSION CLOSE DATABASE LINK db_link;
SELECT table_name
INTO v_t
FROM all_tables@db_link
WHERE rownum = 1;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20999, 'No Connection here');
END;

=======================
Close Link
=======================
ALTER SESSION CLOSE DATABASE LINK ;

ALTER SESSION CLOSE DATABASE LINK curr_user;

============================
Drop Database Link
=============================

DROP DATABASE LINK ;
DROP DATABASE LINK test_link;

DROP PUBLIC DATABASE LINK ;
DROP PUBLIC DATABASE LINK test_link;