Moving tables from one server to another

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
jyemAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Cheers,
NicksonKoh
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

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
0
ramumorlaCommented:
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
0
jyemAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.