 
Click here to see Oracle Data Pump Video Course
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 |
