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

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
  • 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

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.

Question has a verified solution.

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

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

839 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