CALL NOW: 0703 180 5732

 
 
Oracle Data Pump

  Click on left button to know more about data pump through video                                                                                                              

Click here to see Oracle Data Pump Video Course

 

•  Oracle Datapump access driver to load and unload data in external table

ORA ARCHITECTURE                                                                  

Orac

Transport_Tablespace

System tablespace or any sys objects cannot be imported using transportable tablespace

First step make the required tablesapce readonly

First check wether the tablespace is self contained by using

Dbms_tts.transport_set_check(‘tablespacenmae',TRUE);

YOU CAN CHECK ITF THERE IS VIOLATIONS BY Querying

Select * from TRANSPORT_SET_VIOLATIONS;

3 rd step export the tablespace metadata .. using the following command

Expdp system/oracle@oracle transport_tablespace=test directory=testdir dumpfile=bjtest01.dmp logfile=bijt.log

Now move the datafile to other required location . It could be the other computer too..Since it is a datafile the copy is faster

C:\data\test.dbf c:\newlocation\test.dbf

Next make the tablespace readwrite ;

Now in the other database fire the following command.

Impdp system/oracle directory=testdir dumpfile=bjtest01.dmp logfile=bijt.log transport_datafiles=c:\newlocation\test.dbf

This results in fastest movement of data from tablespace from one database to the other.

                                       

Monitoring Data Pump Jobs

Dba_datapump_jobs

Job_name Name of Job

State State of the job

 

Dba_datapump_sessions

Shows the session id

You can join it to v$session table to know more about the session

 

V$session_longups

Important columns are

Totalwork show total estimate of mbs in jobs

Sofar show total mb thus far I the job

Units stands for mbs

Opname shows the data pump jobs

 

EXTERNAL TABLES

Prior to 10g …

External tables are created using oracle driver or datapump driver

. The external tables created via datapump driver can not only be loaded from text file but also be unloaded to a text file.

Suppose you got text data like

1,paul,johny,2000

2,jack,jseph,1200

3,jimmy,joseph,2002

4,nick,nelson,3030

And file is named as cpidata.txt

 

Now if we want to load it in oracle we can use the default oracle_loader driver and load and create the external table.

Via following commands

Create table extest

(

Empid number(10),

Empname varchar2(20),

Sname varchar2(20),

Salary number(10))

Organizational external

(

Type oracle_Loader

Default directory testdir

Access parameters

(

Records delimited by newline

Fields terminated by ‘,'

Missing fileds values are null)

Location (‘cpidata.txt')

)

Reject limit unlimited

;

Organizational external means it is a external table

New Facility in 10g

Unloading of data from tables to external table using

Oracle_datapump driver

 

The following commands creates a external table from a existing table and also makes a dump file in directory specified

Create table etest organization external

(type oracle_datapump

Default directory testdir

Location (‘bit.dmp')

)

As

Select * from pt;

After firing this command a external table etest is created

And note this tables data is stored in bit.dmp since it is a

External table.

 



 















o email info@cpisolution.com Contact NumCallr:0703 180 5732

 
Copyright CpiSolution.com | 2007 © all rights reserved