[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 907
  • Last Modified:

exp & imp or database tables

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
harris2107
Asked:
harris2107
  • 4
  • 4
  • 3
  • +2
5 Solutions
 
kimarti3Commented:
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
 
QlemoC++ DeveloperCommented:
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
 
NicksonKohCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
ramumorlaCommented:
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
 
kimarti3Commented:
export/import will not be supported after 10g.  Datapump is a lot more efficient.
0
 
ramumorlaCommented:
yes!
0
 
harris2107Author Commented:
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
 
kimarti3Commented:
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
 
ramumorlaCommented:
kimarti3-- Data pumpu is not supported on 9i...
0
 
kimarti3Commented:
oh, i was unaware he was using 9i
0
 
harris2107Author Commented:
Kimarti,
                If we give FULL=Y, EXP does a full backup of the database.
0
 
NicksonKohCommented:
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
 
ramumorlaCommented:
Are we dumped??
0
 
harris2107Author Commented:
:)

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
 
NicksonKohCommented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now