Oracle Peoplesoft : DATA PUMP

Oracle Data Pump is the replacement for the original Export and Import utilities.
Oracle Data Pump enables very high-speed movement of data and metadata from one database to another.
The Data Pump Export and Import utilities have a similar look and feel to the original
utilities, but they are much more efficient and give you greater control and management
of your import and export jobs.
Export and Import utilities a primer on how to move up to the faster, more powerful, and more flexible Data Pump Export and Import utilities.
Here we have implement data pump steps are as follows:-

C:\Users\Administrator>SET ORACLE_SID=PAA92

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 14 10:19:52 2014

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2182592 bytes

Variable Size 771752512 bytes

Database Buffers 289406976 bytes

Redo Buffers 5595136 bytes

Database mounted.

Database opened.

SQL> SHOW USER

USER is "SYS"

SQL> DESC ALL_DIRECTORIES;

Name Null? Type

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

OWNER NOT NULL VARCHAR2(30)

DIRECTORY_NAME NOT NULL VARCHAR2(30)

DIRECTORY_PATH VARCHAR2(4000)

SQL> SELECT DIRECTORY_NAME FROM ALL_DIRECTORIES;

DIRECTORY_NAME

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

DATA_PUMP_DIR

ORACLE_OCM_CONFIG_DIR

SQL> CREATE DIRECTORY TESTDIR AS 'E:\DUMP';

Directory created.

SQL> SELECT DIRECTORY_NAME FROM ALL_DIRECTORIES;

DIRECTORY_NAME

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

TESTDIR

DATA_PUMP_DIR

ORACLE_OCM_CONFIG_DIR

SQL> SELECT DIRECTORY_PATH FROM ALL_DIRECTORIES;

DIRECTORY_PATH

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

E:\DUMP

E:\oracle11g\product\11.2.0\dbhome_1/rdbms/log/

E:\oracle11g\product\11.2.0\dbhome_1/ccr/state

SQL> CREATE USER ADAM IDENTIFIED BY CM;

User created.

SQL> GRANT CREATE SESSION TO ADAM;

Grant succeeded.

SQL> GRANT CREATE TABLE TO ADAM;

Grant succeeded.

SQL> GRANT READ, WRITE ON DIRECTORY TESTDIR TO ADAM;

Grant succeeded.

SQL> ALTER USER ADAM QUOTA 10M ON SYSTEM;

User altered.

SQL> SHOW USER

USER is "SYS"

SQL> CONNECT ADAM/CM

Connected.

SQL> SHOW USER

USER is "ADAM"

SQL> CREATE TABLE EMP(NAME VARCHAR2(10));

Table created.

SQL> INSERT INTO EMP VALUES('SAM');

1 row created.

SQL> INSERT INTO EMP VALUES('LINA');

1 row created.

SQL> INSERT INTO EMP VALUES('ANDREW');

1 row created.

SQL> SELECT * FROM EMP;

NAME

----------

SAM

LINA

ANDREW

SQL> COMMIT;

Commit complete.

SQL> HOST

Microsoft Windows [Version 6.1.7601]

C:\Users\Administrator>E:

E:\>EXPDP ADAM/CM DIRECTORY=TESTDIR TABLES=ONE DUMPFILE=EXP.DMP LOGFILE=EXP.

LOG

Export: Release 11.2.0.1.0 - Production on Mon Jul 14 10:38:43 2014

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "ADAM"."SYS_EXPORT_TABLE_01": ADAM/******** DIRECTORY=TESTDIR TABLES

=ONE DUMPFILE=EXP.DMP LOGFILE=EXP.LOG

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "ADAM"."ONE" 5.031 KB 3 rows

Master table "ADAM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for ADAM.SYS_EXPORT_TABLE_01 is:

E:\DUMP\EXP.DMP

Job "ADAM"."SYS_EXPORT_TABLE_01" successfully completed at 10:38:58

E:\>CD DUMP

E:\DUMP>DIR

Volume in drive E is Local Disk

Volume Serial Number is 489D-01E5

Directory of E:\DUMP

07/14/2014 10:38 AM <DIR> .

07/14/2014 10:38 AM <DIR> ..

07/14/2014 10:38 AM 86,016 EXP.DMP

07/14/2014 10:38 AM 1,017 EXP.LOG

2 File(s) 87,033 bytes

2 Dir(s) 61,106,884,608 bytes free

E:\DUMP>SQLPLUS / AS SYSDBA

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 14 10:40:06 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM DUMP;

SELECT * FROM DUMP

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> SELECT * FROM ONE;

SELECT * FROM ONE

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> CONN ADAM/CM

Connected.

SQL> SELECT * FROM EMP;

NAME

----------

SAM

LINA

ANDREW

SQL> DROP TABLE EMP;

Table dropped.

SQL> SELECT * FROM EMP;

SELECT * FROM EMP

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> HOST

Microsoft Windows [Version 6.1.7601]

E:\DUMP>CD..

E:\>IMPDP ADAM/CM DIRECTORY=TESTDIR TABLES=ONE DUMPFILE=EXP.DMP LOGFILE=EXP

.LOG

Import: Release 11.2.0.1.0 - Production on Mon Jul 14 10:44:42 2014

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "ANGEL"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "ADAM"."SYS_IMPORT_TABLE_01": ADAM/******** DIRECTORY=TESTDIR TABLES

=ONE DUMPFILE=EXP.DMP LOGFILE=EXP.LOG

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "ANGEL"."ONE" 5.031 KB 3 rows

Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at 10:44:45

E:\>SHOW USER ;

'SHOW' is not recognized as an internal or external command,

operable program or batch file.

E:\>EXIT

SQL> SHOW USER

USER is "ADAM"

SQL> SELECT * FROM EMP;

NAME

----------

SAM

LINA

ANDREW

SQL>

Oracle Peoplesoft

Tuesday, 22 July 2014

DATA PUMP

No comments:

Post a Comment