My Oracle Diary: February 2013

Monday, February 25, 2013

Export/Import table using database link

Source db - db1 from where you want to pull the data
Destination db - db2 to where you want to store the data

In db2,

SQL> create public database link DBLINK connect to BIREPORTING identified by pass$word using 'PROD_DB';

Database link created.

where

DBLINK is the anonymous name you would be giving to the database link
BIREPORTING is the username in db1
pass$word is the password for the username in db1
PROD_DB is the net service name available in db1

SQL> CREATE TABLE BIREPORTING.CHANNEL AS SELECT * FROM REPOSITORY.CHANNEL@DBLINK;

Table created.

SQL> COMMIT;

Commit complete.

Export/Import rows using database link

Source db - db1 from where you want to pull the data
Destination db - db2 to where you want to store the data

In db2,

SQL> create public database link DBLINK connect to BIREPORTING identified by pass$word using 'PROD_DB';

Database link created.

where

DBLINK is the anonymous name you would be giving to the database link
BIREPORTING is the username in db1
pass$word is the password for the username in db1
PROD_DB is the net service name available in db1

SQL> INSERT INTO BIREPORTING.CHANNEL SELECT * FROM REPOSITORY.CHANNEL@DBLINK;

99 rows created.

SQL> COMMIT;

Commit complete.

where

BIREPORTING.CHANNEL belongs to db2
REPOSITORY.CHANNEL belongs to db1

Friday, February 22, 2013

Does the user has the privilege to export/import?

To export full database using data pump user must have EXP_FULL_DATABASE role or dba role and to import full database using data pump user must have IMP_FULL_DATABASE or dba role.
In order to see whether user has these privilege or not you can query,

SQL>SET lines 100

COL privilege FOR a50 
SELECT grantee, granted_role, default_role 
FROM dba_role_privs 
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE') 
ORDER BY 1,2;