Moving tables from one server to another

Posted on 2007-04-10
Last Modified: 2013-12-18
I have a working database (Oracle 10g) that has about 70 tables that I want to move to a diffrent server.   Is there a way to generate .SQL script that contains all the CREATE TABLE syntax for each table from an existing database.  Or is there a better method to move all the tables from the old server to the new server?

Thank you
Question by:jyem
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 17

Accepted Solution

NicksonKoh earned 300 total points
ID: 18886481
Hi jyem,

There are a few methods.
1) SQL : Generate the SQL creation syntax by referencing the to the user_tables, user_tab_columns and a few more others for a exact creation of the tables including the indexes and constraints. Sorry to say, I dunno the exact SQL to do this.

2) Use TOAD : Download the trial if you do not have. It's so simple. Under the schema window, you can select the tables and right click to obtain the option to generate the exact table creation script. (I always do this; that's why I never bothered to figure out using step 1)

3) Use export and import : Export just the schema that you need and then import them into the working database choosing only to import structures if you do not wish to import the tables.


Assisted Solution

ramumorla earned 200 total points
ID: 18887165
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

Export the 9i DB

Shutdown the 9i DB to stop users from establishing a session

Copy the Oracle 9i EXPORT.DMP and IMPORT.PAR files to the 10g DB (destination server)

Ensure tablespaces have been created/configured in the 10g DB to coincide with the 9i DB

Take the 10g DB out of ARCHIVELOG mode

Import the 9i DB into the 10G DB

Check import logs for errors

Reset passwords for system (and other) 10g accounts back to the original 9i 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 10g DB

Ensure MS Access apps are able to connect and work

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

Give the MS Access Devs the OK to change the ODBC 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 10g DB back into ARCHIVELOG mode

Verify the nightly backup client is configured properly

Monitor the alert log and dump directories for possible issues

Expert Comment

ID: 18888196
Hi By mistake I mentioned, 9i to 10g upgrade, please read 10g as Dest database and 9i as source database, the entire answer solves your problem

Author Comment

ID: 18892511
Thank you

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

688 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