Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Moving tables from one server to another

Posted on 2007-04-10
Medium Priority
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 1200 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 800 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…
Suggested Courses

578 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