Solved

exp & imp or database tables

Posted on 2007-04-10
15
896 Views
Last Modified: 2013-12-19
Hi,
      I need to replicate some tables on database A to database B. I can use EXP and IMP utility to take a backup of all the tables on A and import them onto B but i have some questions to ask, suppose i want to backup 5 tables T1,T2,T3,T4 & T5

1. Can i have a backup of just the STRUCTURE and no data of the table T1 on A using EXP?  If yes, is there any different options to be given to IMP to import it on the database B.

2. If the tables, T1,T2,T3,T4 & T5 have any indexes, constraints or dependency tables, what is the approach to follow with EXP to export the tables with all the indexes, constraints and dependency tables in consistent with each other and how to import them?

Any information will be of a great help.

Thanks,
Harris.
0
Comment
Question by:harris2107
  • 4
  • 4
  • 3
  • +2
15 Comments
 
LVL 3

Expert Comment

by:kimarti3
ID: 18884419
What version of Oracle are you using?  If 10g, I would advise you to use datapump instead of exp/imp.  Using datapump, you can do an export of metadata only, which is the structure without the data.  In metadata only, it will get the indexes, etc with it.  When you do an import of metadata only, same thing.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 100 total points
ID: 18884544
1. Yes, with option ROWS=N, and no, no different IMP syntax needed (ROWS=N can be used with imp or exp)

2. exp will do that itself, AFAIK. Maybe you have to use option CONSISTENT=Y, so every necessary table for valid constraints will be exported, too.
0
 
LVL 17

Assisted Solution

by:NicksonKoh
NicksonKoh earned 200 total points
ID: 18886549
Hi harris2107,

The export and import utility actually will automatically prompt you what you want to do. It's really quite simple. All you need to do is go test out the 2 commands exp and them imp and follow the instructions.

Like the other expert mentioned, all constraints and indexes will be imported. During import, you will also be asked whether u want to import the data.

One option to take note is the "Ignore Errors" prompt which I usually set to yes. This is because sometimes a constraint or table is already there and the imp would run into error in such a case. And because I want to ignore such error and let the import continue all the way, I would usually set the Ignore Error prompt to yes.


Cheers,
NicksonKoh
0
 
LVL 4

Assisted Solution

by:ramumorla
ramumorla earned 100 total points
ID: 18887170
To obtain the cleanest possible export, run INVALID.SQL script to check for invalid objects on 9i DB (source server) and run VALIDATE.SQL (UTLRP.SQL?)to recompile any invalid objects

Ensure tablespaces have been created/configured in the dest DB to coincide with the Source DB

Take the Dest DB out of ARCHIVELOG mode

Import the src DB into the 10G DB

Check import logs for errors

Reset passwords for system (and other) dest accounts back to the original src DB accounts

Make sure users are pointing to proper tablespaces

Run INVALID.SQL script on 10g DB again to check for invalid objects and run VALIDATE.SQL (UTLRP.SQL?) to recompile any invalid objects

Recompile all PL/SQL modules that may be in an INVALID state, including packages, and types on 10G DB using UTLRP.SQL (is this same as above?)

Check the local applications and DB links on the dest DB

Give the Oracle Devs the OK to change the hardcoded connect string from the old to the new


Deploy the new Oracle registry key LOCAL default parameter key connect string to all WS

Put the dest DB back into ARCHIVELOG mode

Verify the nightly backup client is configured properly

Monitor the alert log and dump directories for possible issues
0
 
LVL 3

Expert Comment

by:kimarti3
ID: 18888950
export/import will not be supported after 10g.  Datapump is a lot more efficient.
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18889016
yes!
0
 

Author Comment

by:harris2107
ID: 18889863
Hi,
     Thank you all for the replies. Just have few more questions

1. Suppose if we put CONSISTENT=Y in the EXP command, all the base and dependent tables will be export too right? Does the constraints too come along here or do we have to issue CONSTRAINTS=Y?

2. Do we need to have the exact TABLESPACES at the destination for the import to take place? Suppose table T1 is in tablespace USERS1 on database A and we import the table onto database B, which does not have USERS1 tablespace, does the IMP function work here? or do we have to create the tablespace USERS1 on B and then issue the IMP function?

3. The user has to be same on both database A and database B right to peform EXP and IMP right? Suppose we issue EXP on database A as user TOM, we need to have a user by name TOM on database B to perform a IMP of the dump file from A right? or can we use  TOUSER and FROMUSER to overcome this dependency?

4. A SYSDBA can perform an EXP but how to perform this? i mean do we have to login as                  "exp sys/passwd file=expdump.dmp tables=DEPT" or is there any other way for the sysdba to perform this.

Any information will be of a great help.

THanks,
Harris.
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 3

Assisted Solution

by:kimarti3
kimarti3 earned 100 total points
ID: 18889987
1. I believe if you do FULL=Y then it will get all of the constraints, indexes, etc. so you don't have to do CONSTRAINTS=Y.  You will still need to do CONSISTENT=Y though.
2.  the same tablespaces have to exist on the other machine before you can import, otherwise it will error out.
3.  you can use TOUSER and FROMUSER if different users
4.  If you are only exporting tables, why would you do that from sys user?  best to do it from the user that owns those tables.  if you want to do a full export of the database, use system, not sys.

I still recommend DATAPUMP over exp/imp.
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18890104
kimarti3-- Data pumpu is not supported on 9i...
0
 
LVL 3

Expert Comment

by:kimarti3
ID: 18890197
oh, i was unaware he was using 9i
0
 

Author Comment

by:harris2107
ID: 18892121
Kimarti,
                If we give FULL=Y, EXP does a full backup of the database.
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18895013
A full database export requires account with DBA privileges. Use just the required account needed. If just a specific schema, then just use that user. If recreating a development database from scratch that aims to be exactly the same as production, do a full database using the account with DBA privileges.

Tablespaces need to be created fist if you want the tables to imported into the same tablespace. Otherwise the tables will be imported into the system tablespace and that is bad. For development, some people ignore this but I would advise that you create the tablespace so that the development and the production databases are as similar as possible.

It seems you need a tutorial. Both the inline method and the step by step method are very well describe in
http://www.softlookup.com/tutorial/oracle/ch09.asp

cheers
Nickson
0
 
LVL 4

Expert Comment

by:ramumorla
ID: 18916334
Are we dumped??
0
 

Author Comment

by:harris2107
ID: 18918652
:)

Thank you all for the quick replies. Yep, did an export of the the whole schema with no data and imported it with no issues. But few things i would like to outline and could you please confirm these for me.

1. It is not necessary to have the same username at the target database where we are about to import. We can even imp with different username and with no TOUSER and FROMUSER clause. ( I have tried it and it worked)

2.  It is not necessary to have the exact tablespaces at the target database for the import to work. If we do not have the matching tablespaces, the IMPORT utility will create the necessary objects (tables, views, indexes, stored procedures, triggers, etc..) in the default tablespace or any tablespace in which the user has grant to.

3. We can ignore the WARNING  " EXP-0020: EXPORTING QUOSTIONABLE STATISTICS"  while doing an export right. If we get "EXPORT TERMINATED SUCCESSFULLY  (with out or with warnings)" , then we are good to go in anyways.

Thank you all,
Harris.
0
 
LVL 17

Assisted Solution

by:NicksonKoh
NicksonKoh earned 200 total points
ID: 18921069
All points correct. A bit to add

1. It boil down to the privileges which the account has.

2. Yes . It is often that the default tablespace for users are not specified and hence why I mention it will go into system tablespace.

3. That's what I do too. Set Ignore Create Error to true.

cheers
Nickson
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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

746 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

13 Experts available now in Live!

Get 1:1 Help Now