Export and Import Data 1

[florida1987]

I have a local development Oracle 9i database on my Windows XP workstation and I have the production Oracle 9i on a unix server. I use Oracle Enterprise Manager (OEM) in my local database for creating my tables, sql plus etc. Now I was wondering if there is anyway to export and import my data from my local database to the server database.
If so please advise how?

 

[SantaMufasa]

Yes, Florida, you can use Oracle's exp (export) and imp (import) utilities. Amongst their functionality is the specific purpose to clone tables and their data from one Oracle database instance to another, regardless of operating system.

Following are sample exp and imp commands that export an entire Oracle user schema and then import the schema into the target database. (The import works best if you have done a "CREATE USER..." statement on the receiving user schema.):

exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=TEST.dump log=TEST_Exp.log statistics=none owner=TEST userid=TEST/TEST@tnsalias

imp buffer=15000000 grants=y feedback=1000 fromuser=TEST touser=TEST file=DHUNT.TEST.dump log=TEST_Imp.log  userid=TEST/TEST@tnsalias

You can see a comprehensive list of parameters and their explanations (including the ones I have used, above) if you issue these commands:

exp help=y

imp help=y

You should issue all of the above commands from your operating system prompt. The commands should appear on the same command line...without a [Return] until after the last parameter.

You can issue both an "exp" or "imp" command on a line by itself (without parameters) at which point you are in "interactive" mode and you will receive prompts for parameter values.

If you have additional questions, or need clarifications on what you discover from investigations of the above, we are eager to help...just post your questions here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[florida1987]

Thanks, I tried this in my Windows Command Line:

imp buffer=15000000 grants=y feedback=1000 fromuser=TEST touser=TEST file=DHUNT.TEST.dump log=TEST_Imp.log  userid=TEST/TEST@tnsalias

and it gave me a message imp - 00013 only DBA can import..
I am not a DBA on the Server. I assume I cant import data from the Production server to my local database if I am not the DBA on the server? Both the fromuser and touser have the same name.


For the exp I tried this:

exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=TEST.dump log=TEST_Exp.log statistics=none owner=TEST userid=TEST/TEST@tnsalias

and it gave me back successful output saying all was exported but I didnt see my local Schema tables show up in the Server Schema. Both the local and server usernames are the same.
Please advise.

 

[SantaMufasa]

Florida,

Sorry...I should have pointed out that:[ul][li]TEST was the name of the schema that I was using in my example,[/li][li]TEST is also the password for that schema, and,[/li][li]tnsalias is the entry in the tnsnames.ora file for the database instance to which you are trying to connect.[/li][/ul]If you literally used my sample code, above, then I am amazed that you received a "successful" outcome message.


So, replace TEST, above, with appropriate entries for your schema and password, and replace tnsalias with the alias that appears in your tnsnames.ora file for the database to which you are trying to connect.

If you cannot get things to work successfully, and you are not the DBA, then you have every right to ask your DBA for assistance. (That's why your company is paying her/him the Big Bucks. <grin>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[florida1987]

Thanks, In my previous post example I just posted your exact code but I did use my Schema/password and tnsalias. I can understand the import not working but why did the export say it worked (local database to server database) and I didnt see my local database tables in the server? Maybe I need DBA privileges but I assume DBA cant give that out or is there a special privilege he can give out??

 

[SantaMufasa]

Florida,

To clarify, one does not need to be a DBA to perform exports or imports. But, if a DBA performs the export, a DBA must also perform the import. I'll bet that on the local machine (from which you did the export), that your user has DBA privileges.

If that is the case, then I suggest that you revoke DBA privileges from yourself on the local side, perform the export, then give yourself back DBA privileges on the local side. That way, the export will not be performed by a DBA, and you will be able to perform the import with your same username on the server side.

Let us know if my suspicions are correct about your being a DBA on the local side.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[florida1987]

Thanks!

I will do that on Monday. I googled "Oracle DBA revoke" and found something like this: To revoke DBA privileges from a user: alter user markd revoke DBA. Revoking DBA priviliges SQL> revoke dba from gsdev; I assume that is all I need to do for revoking DBA privilege on my local username (called Jones) is this command:
Revoke dba from Jones;

 

[SantaMufasa]

Correct.

The easiest way to REVOKE or GRANT DBA privileges is to connect (from the o/s prompt) to your local Oracle database instance in this fashion:

<prompt> sqlplus /nolog

SQL*Plus: Release <some version> - Production on Sat Mar 8 17:56:29 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> revoke dba from jones;

Revoke succeeded.

Then, just re-GRANT in the same fashion once you have completed the export.

Let us know the outcome.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]