Solved

exp/imp data to Oracle 12c using transportable tablespace.

Posted on 2015-01-20
25
440 Views
Last Modified: 2015-02-04
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 ?
0
Comment
Question by:marrowyung
  • 16
  • 9
25 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 40559276
I see these method on the upgrade guide:

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 ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40559587
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).
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40561430
"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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40561849
>>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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40563796
"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?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40563917
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 ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40564138
>>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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40565922
"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 ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40566255
>>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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40570116
">>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 !
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40570118
">>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 ?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40570385
>>so original exp/imp has a lot of file other than the .sql file ?

Creating a SQL file is an option.  It is done by specifying a specific parameter.

It typically creates a DMP file.  The DMP file is binary.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:marrowyung
ID: 40576817
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40577272
Not a Toad person but a quick Google of that error leads to:
http://documents.software.dell.com/DOC127938
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40583278
ok, tks anyway, as the Toad 12 freeware can't do export and all and I am not using SQL developer for that.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40583358
seems the path must be in the local workstation but not on server side.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40583360
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40583803
You will need the full client installed (not the instant client).  When installing it, make sure you select the Utilities.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40585724
tks.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40587841
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?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40587858
should I just edit the tnsnames.oRA ?

I tried that but it seems doesn't work.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40588526
>>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"...
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40588656
"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 ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40588670
>>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.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40588681
ok/
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now