marrowyung
asked on
exp/imp data to Oracle 12c using transportable tablespace.
Dear Sir,
when I tried to export data using Toad and I know oralce has some other export feature called transportable! in the export mode I see a choice called "Generate a transportable tablespace" , what is the use of transportable ?
what is the diff between normal export and this ?
when I tried to export data using Toad and I know oralce has some other export feature called transportable! in the export mode I see a choice called "Generate a transportable tablespace" , what is the use of transportable ?
what is the diff between normal export and this ?
Transportable tablespace allows you to move the datafiles from one database and use them in another database.
There are restrictions on when and how you can do this. The online docs will tell you what you need to know.
As far as a difference between datapump and original exp/imp there are many differences. The main one that I like to point out is the original ones will allow you to use files on a remote client. Datapump has to place the files on the database server (unless you use the network link to move the data directly).
There are restrictions on when and how you can do this. The online docs will tell you what you need to know.
As far as a difference between datapump and original exp/imp there are many differences. The main one that I like to point out is the original ones will allow you to use files on a remote client. Datapump has to place the files on the database server (unless you use the network link to move the data directly).
ASKER
"There are restrictions on when and how you can do this. The online docs will tell you what you need to know."
Give me a link exactly tell this please. the pdf I see this do not explain thing further.
"Transportable tablespace allows you to move the datafiles from one database and use them in another database."
exp/imp can also do this too, right?
you mean mount the datafile to other database/instance?
"The main one that I like to point out is the original ones will allow you to use files on a remote client. "
remote client means? example please.
Give me a link exactly tell this please. the pdf I see this do not explain thing further.
"Transportable tablespace allows you to move the datafiles from one database and use them in another database."
exp/imp can also do this too, right?
you mean mount the datafile to other database/instance?
"The main one that I like to point out is the original ones will allow you to use files on a remote client. "
remote client means? example please.
>>Give me a link exactly tell this please. the pdf I see this do not explain thing further.
The online docs have a pretty good 'search'.
Limitations on Transportable Tablespace Use
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN11396
>>exp/imp can also do this too, right?
transportable tablespaces rely on exp/imp but they are different things.
exp/imp moves 'data'. It physically extracts the object and data, places it into a file then reads it from the file and 'creates/inserts' it into the destination.
Transportable tablespaces moves the datafile and all the contents at one time.
>>remote client means? example please.
A machine that is NOT the database server. For an example: Your PC where you have the Oracle Client installed but no database running.
You can use the classic exp/imp to extract data from a database located somewhere in the world to your local PC.
The online docs have a pretty good 'search'.
Limitations on Transportable Tablespace Use
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN11396
>>exp/imp can also do this too, right?
transportable tablespaces rely on exp/imp but they are different things.
exp/imp moves 'data'. It physically extracts the object and data, places it into a file then reads it from the file and 'creates/inserts' it into the destination.
Transportable tablespaces moves the datafile and all the contents at one time.
>>remote client means? example please.
A machine that is NOT the database server. For an example: Your PC where you have the Oracle Client installed but no database running.
You can use the classic exp/imp to extract data from a database located somewhere in the world to your local PC.
ASKER
"exp/imp moves 'data'. It physically extracts the object and data, places it into a file then reads it from the file and 'creates/inserts' it into the destination.
yeah, in a flat .sql file.
"Transportable tablespaces moves the datafile and all the contents at one time.
"
so this also means transportable tablespace can do the job faster? just like detach a data file and attach it to the target instance?
"A machine that is NOT the database server. For an example: Your PC where you have the Oracle Client installed but no database running."
ok, then why we should use the transported tablespace file on a client PC without oracle database ?
what is the benefit of it?
yeah, in a flat .sql file.
"Transportable tablespaces moves the datafile and all the contents at one time.
"
so this also means transportable tablespace can do the job faster? just like detach a data file and attach it to the target instance?
"A machine that is NOT the database server. For an example: Your PC where you have the Oracle Client installed but no database running."
ok, then why we should use the transported tablespace file on a client PC without oracle database ?
what is the benefit of it?
ASKER
I read the link:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN11395
"About Transporting Tablespaces Across Platforms
Starting with Oracle Database Release 10g, you can transport tablespaces across platforms. This functionality can be used to:
•Allow a database to be migrated from one platform to another
•Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle Database on different platforms
•Simplify the distribution of data from a data warehouse environment to data marts, which are often running on smaller platforms
the first 3 points can be done by emp/imp too, am I right ?
or migration by Transportable Tablespaces only help by migrating data batch by batch when compare with PUmp exp/imp ?
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN11395
"About Transporting Tablespaces Across Platforms
Starting with Oracle Database Release 10g, you can transport tablespaces across platforms. This functionality can be used to:
•Allow a database to be migrated from one platform to another
•Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle Database on different platforms
•Simplify the distribution of data from a data warehouse environment to data marts, which are often running on smaller platforms
the first 3 points can be done by emp/imp too, am I right ?
or migration by Transportable Tablespaces only help by migrating data batch by batch when compare with PUmp exp/imp ?
>>yeah, in a flat .sql file.
Not really. It creates a proprietary binary file that has the DDL and data contained in it. It is not human readable or editable.
>>just like detach a data file and attach it to the target instance?
Exactly.
>>then why we should use the transported tablespace file on a client PC without oracle database ?
You cannot. Datafiles attach to databases which sort of imply a running database.
>>the first 3 points can be done by emp/imp too, am I right ?
There are many ways to get data from pointA to pointB. exp/imp and transportable tablespaces are just two ways.
>>Transportable Tablespaces only help by migrating data batch by batch when compare with PUmp exp/imp ?
Sort of. They differ on the 'how' they get the data and objects from pointA to pointB. You can export and import specific objects in a 'batch' as well.
A difference is that using transportable tablespaces you take everything in the tablespace no questions asked.
Think about it this way:
Have you been to a hardware store before?
How many different hammers do they have?
Why?
They ALL perform the same basic function so why do you need different ones?
The reason is not ALL tasks they are used for are exactly the same. You figure out the task/problem you are trying to solve and select the hammer that will perform that task most efficiently.
Same with exp/imp versus transportable tablespaces. Each has benefits based on the specific requirements you have. In some situations they may be equal so choosing one over the other doesn't matter.
Only you can decide which is 'best' for you.
Not really. It creates a proprietary binary file that has the DDL and data contained in it. It is not human readable or editable.
>>just like detach a data file and attach it to the target instance?
Exactly.
>>then why we should use the transported tablespace file on a client PC without oracle database ?
You cannot. Datafiles attach to databases which sort of imply a running database.
>>the first 3 points can be done by emp/imp too, am I right ?
There are many ways to get data from pointA to pointB. exp/imp and transportable tablespaces are just two ways.
>>Transportable Tablespaces only help by migrating data batch by batch when compare with PUmp exp/imp ?
Sort of. They differ on the 'how' they get the data and objects from pointA to pointB. You can export and import specific objects in a 'batch' as well.
A difference is that using transportable tablespaces you take everything in the tablespace no questions asked.
Think about it this way:
Have you been to a hardware store before?
How many different hammers do they have?
Why?
They ALL perform the same basic function so why do you need different ones?
The reason is not ALL tasks they are used for are exactly the same. You figure out the task/problem you are trying to solve and select the hammer that will perform that task most efficiently.
Same with exp/imp versus transportable tablespaces. Each has benefits based on the specific requirements you have. In some situations they may be equal so choosing one over the other doesn't matter.
Only you can decide which is 'best' for you.
ASKER
"Not really. It creates a proprietary binary file that has the DDL and data contained in it. It is not human readable or editable."
with Pump export, I can read the file ... can't see why .
"then why we should use the transported tablespace file on a client PC without oracle database ?
You cannot. Datafiles attach to databases which sort of imply a running database.
"
I am wondering why you said then.
"As far as a difference between datapump and original exp/imp there are many differences. The main one that I like to point out is the original ones will allow you to use files on a remote client."
"The reason is not ALL tasks they are used for are exactly the same. You figure out the task/problem you are trying to solve and select the hammer that will perform that task most efficiently."
yeah! good. I am now wondering why someoine can use RMAN to do database upgrade from 10gR2/11gR2 to 12c, even only backup data, how can 12c get use to it ?
with Pump export, I can read the file ... can't see why .
"then why we should use the transported tablespace file on a client PC without oracle database ?
You cannot. Datafiles attach to databases which sort of imply a running database.
"
I am wondering why you said then.
"As far as a difference between datapump and original exp/imp there are many differences. The main one that I like to point out is the original ones will allow you to use files on a remote client."
"The reason is not ALL tasks they are used for are exactly the same. You figure out the task/problem you are trying to solve and select the hammer that will perform that task most efficiently."
yeah! good. I am now wondering why someoine can use RMAN to do database upgrade from 10gR2/11gR2 to 12c, even only backup data, how can 12c get use to it ?
>>with Pump export, I can read the file ... can't see why .
Some of it appears in clear text but it is NOT a flat text file.
>>I am wondering why you said then.
I was commenting on export/import not transportable tablespaces. Datapump export will only create a file on the database server. Classic export will create a file on the machine where it was executed. Same for Import.
>> I am now wondering why someoine can use RMAN to do database upgrade from 10gR2/11gR2 to 12c, even only backup data, how can 12c get use to it ?
I don't know how to do an upgrade with RMAN.
Some of it appears in clear text but it is NOT a flat text file.
>>I am wondering why you said then.
I was commenting on export/import not transportable tablespaces. Datapump export will only create a file on the database server. Classic export will create a file on the machine where it was executed. Same for Import.
>> I am now wondering why someoine can use RMAN to do database upgrade from 10gR2/11gR2 to 12c, even only backup data, how can 12c get use to it ?
I don't know how to do an upgrade with RMAN.
ASKER
">>with Pump export, I can read the file ... can't see why .
Some of it appears in clear text but it is NOT a flat text file."
ok, tks anyway.
"I don't know how to do an upgrade with RMAN. "
excellent and powerful statment ! I like that !
Some of it appears in clear text but it is NOT a flat text file."
ok, tks anyway.
"I don't know how to do an upgrade with RMAN. "
excellent and powerful statment ! I like that !
ASKER
">>yeah, in a flat .sql file.
Not really. It creates a proprietary binary file that has the DDL and data contained in it. It is not human readable or editable.
"
so original exp/imp has a lot of file other than the .sql file ?
Not really. It creates a proprietary binary file that has the DDL and data contained in it. It is not human readable or editable.
"
so original exp/imp has a lot of file other than the .sql file ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have a toad to try to do the orignal export and see following screenshot please:
anyone know I can't go further ?
export.jpg
setting.jpg
Toad.jpg
anyone know I can't go further ?
export.jpg
setting.jpg
Toad.jpg
Not a Toad person but a quick Google of that error leads to:
http://documents.software.dell.com/DOC127938
http://documents.software.dell.com/DOC127938
ASKER
ok, tks anyway, as the Toad 12 freeware can't do export and all and I am not using SQL developer for that.
ASKER
seems the path must be in the local workstation but not on server side.
ASKER
I can only find Exp.exe from the oracle server but it seems that toad is looking for that file on my desktop, what should I install on my desktop so that Toad can see it?
You will need the full client installed (not the instant client). When installing it, make sure you select the Utilities.
ASKER
tks.
ASKER
but I just done it by only install oracle software on it as I don't have a chocie to see what component I can install, so I install everytihng and expdp.exe and exp.exe are there.
but when I run it, it returns:
UDE-12514: operation generated ORACLE error 12514
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
can't see why as I am ready connected to the oracle, TNS should be ok, right?
but when I run it, it returns:
UDE-12514: operation generated ORACLE error 12514
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
can't see why as I am ready connected to the oracle, TNS should be ok, right?
ASKER
should I just edit the tnsnames.oRA ?
I tried that but it seems doesn't work.
I tried that but it seems doesn't work.
>>ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Check out what this error means:
http://ora-12514.ora-code.com/
>>should I just edit the tnsnames.oRA ?
You need the tnsnames.ora file to be properly set up to be able to locate the database you want to work with.
>>I tried that but it seems doesn't work.
Oracle is pretty strict with the format of this file. If you aren't familiar with manually editing it, then you should use the GUI (Network Configuration Assistant).
Define "Doesn't work"...
Check out what this error means:
http://ora-12514.ora-code.com/
>>should I just edit the tnsnames.oRA ?
You need the tnsnames.ora file to be properly set up to be able to locate the database you want to work with.
>>I tried that but it seems doesn't work.
Oracle is pretty strict with the format of this file. If you aren't familiar with manually editing it, then you should use the GUI (Network Configuration Assistant).
Define "Doesn't work"...
ASKER
"Oracle is pretty strict with the format of this file. If you aren't familiar with manually editing it, then you should use the GUI (Network Configuration Assistant)."
ok, this seems I have to do it from toad.
"Define "Doesn't work"... "
I tried to edit that file and do the same thing again, I still get the same answer.
what is the diff between exp.exe and expdp.exe ?
ok, this seems I have to do it from toad.
"Define "Doesn't work"... "
I tried to edit that file and do the same thing again, I still get the same answer.
what is the diff between exp.exe and expdp.exe ?
>>what is the diff between exp.exe and expdp.exe ?
Classic and Datapump (the one with dp is datapump).
>> I tried to edit that file and do the same thing again, I still get the same answer.
The link about the error message has several actions to take to help fix the error.
Either the tnsnames.ora file isn't configured properly or the instance isn't registered with the listener on the database server.
Classic and Datapump (the one with dp is datapump).
>> I tried to edit that file and do the same thing again, I still get the same answer.
The link about the error message has several actions to take to help fix the error.
Either the tnsnames.ora file isn't configured properly or the instance isn't registered with the listener on the database server.
ASKER
ok/
ASKER
1. DBUA
2. Transportable tablespaces (TTS) export and import, using the Oracle Database 12c feature full transportable export/import, or the traditional TTS mode
3. Oracle Data Pump Export/Import, using either dump files or network mode
4. The Original Export/Import utilities
we know what 1 for,
what is 2 is about and what is the diff between 2 and 3,
what is the diff between 3 and 4? what is original exp/imp is about ?