Skip to main content

Oracle Data Dump Import and Export

Login to server :
sqlplus / as sysdba


exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y
or
exp SYSTEM/password PARFILE=param.dat

where param.dat contains the following information 

FILE=dba.dmp 
GRANTS=y 
FULL=y 
ROWS=y 
LOG=dba.log

To dump a single schema to disk.

exp / FILE=dump.dmp OWNER=production

Export Specific tables to disk

exp SYSTEM/password FILE=export.dmp TABLES=(user1.table,user2.table)

Export Data in one user 

exp / FILE=export.dmp TABLES=(table1,table2)


Using imp: 

To import the full database exported in the example above.
imp SYSTEM/password FULL=y FIlE=dba.dmp

To import just the dept and emp tables from the scott schema
imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)

To import tables and change the owner 

imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)


To import just the scott schema exported in the example above
imp / FIlE=scott.dmp


Comments

Post a Comment

Popular posts from this blog

Insert script with multiple cursors and condition check

DECLARE CURSOR C1 IS   SELECT ID FROM TABLE_NAME_1 WHERE COLUMN IN ('');   CURSOR C2     IS       SELECT ID FROM TABLE_NAME_2 WHERE COLUMN IN ('');              CURSOR C3 (CP_TABLE_01_ID NUMBER,CP_TABLE_02_ID NUMBER)         IS           SELECT COUNT(*) AS COUNT_UP           FROM TABLE_NAME_3           WHERE COLUMN_CONDITION_01=CP_TABLE_02_ID           AND COLUMN_CONDITION_02=CP_TABLE_01_ID; COUNT_UP NUMBER; BEGIN FOR R1 IN C1 LOOP     FOR R2 IN C2     LOOP          OPEN C3(R1.ID,R2.ID);        FETCH C3 INTO COUNT_UP;        CLOSE C3;               IF (COUNT_UP=0) THEN           INSERT           INTO TABL...
Post a Comment
Read more

SELECT DELETE STORED PROCEDURE

This stored procedure will help to select and delete records from table with passing variables. create or replace PROCEDURE SP_DELETE_DATA (ID IN INTEGER ,NAME IN VARCAHR2) AS CUR_01_F_COUNT NUMBER ; CURSOR CUR_01 IS SELECT COUNT ( * ) FROM TABLE_NAME CF WHERE ID = ID AND NAME = NAME; BEGIN OPEN CUR_01; FETCH CUR_01 INTO CUR_01_F_COUNT; CLOSE CUR_01; dbms_output.put_line( ' COUNT FOR IS :' || CUR_01_F_COUNT); IF CUR_01_F_COUNT > 0 THEN DELETE FROM TABLE_NAME WHERE ID = ID AND NAME = NAME; dbms_output.put_line( 'REMOVED COUNT IS :' || sql % ROWCOUNT ); IF SQL % ROWCOUNT > 0 THEN COMMIT ; END IF ; END IF ;
Post a Comment
Read more