Solved

Moving tables from one server to another

Posted on 2007-04-10
4
1,356 Views
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
0
Comment
Question by:jyem
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
NicksonKoh earned 300 total points
Comment Utility
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
 
LVL 4

Assisted Solution

by:ramumorla
ramumorla earned 200 total points
Comment Utility
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
 
LVL 4

Expert Comment

by:ramumorla
Comment Utility
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
 

Author Comment

by:jyem
Comment Utility
Thank you
0

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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now