Solved

import does not work

Posted on 2009-04-13
6
846 Views
Last Modified: 2013-12-18
Hi,

I created a scehma/user 'xyz_user' using command

create user xyz_user identified by password;

and granted following privileges to it like

grant resource, exp_full_database, imp_full_database,create session to xyz_user;

I imported a schema dump file 'Database_Dumpfile_1'supplied by client with data, structure into xyz_user  using command

imp xyz_user/password full=y file=Database_Dumpfile_1.dmp

Later I made some changes to table structure, data.

Now when I tried to export latest updated schema back to dump file 'Database_Dumpfile_2' to deliver back to the client using usual old command

exp xyz_user/password indexes=n full=y file=Database_Dumpfile_2.dmp

It is exporting entire database( including all schemas) instead of just required single 'xyz_user' schema .

so i introduced

 owner='(xyz_user)'

to export only the specific schema.

Now at client end when we try to import this dump file
using command

imp client_user/password full=y file=Database_Dumpfile_2.dmp

giving error message like 'cannot open the dump file'.

Do i need to introduce
 owner='(xyz_user)' in the import statement as well? when I introduced it like

imp client_user/password full=y owner='(xyz_user)' file=Database_Dumpfile_2.dmp it did not like it, says check help section for correct options etc.

(Note:
Earlier i used use following privilege command
GRANT all privilege TO xyz_user  WITH ADMIN OPTION
 which never gave issues like this and used to export only single schema instead of entire database with all schema
)

How can I import this dump file. What is the command i should use for this. Any sample code, ideas, resources, links highly appreciated. Thanks in advance.
0
Comment
Question by:gudii9
  • 2
  • 2
  • 2
6 Comments
 
LVL 14

Accepted Solution

by:
ajexpert earned 300 total points
ID: 24134364
HTH
Here are few points you need to take care of:
 

1.  Verify if the export is done sucessfully by examining the log file.  If the file says something like 'Export terminated sucessfully' means there was no problem during export

2.  During import see if you are specifying the correct path (It is the export dump as in step 1).  Here too, log it and examine the log file.
 

You can view help of imp/exp by entering exp help=y or imp help=y
 

3.  The command for export with owner is
 

exp uid/pwd@instance file=<exppath> owner=<ownername> log=explog.log
 

4.  The command for import with owner is

imp uid/pwd@instance fromuser=<owner> touser=<ownername_to_loadtables> file=<exppath_from_step1> implog.log statistics=none buffer=10000000 commit=y 

Open in new window

0
 
LVL 7

Author Comment

by:gudii9
ID: 24134498
when i follow above steps

giving message like


Import: Release 10.2.0.1.0 - Production on Mon Apr 13 21:04:01 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

 How can i pass through this message and import successfully. I am executing these commands from command prompt as usual by going to the folder location wherever the dump file Database_Dumpfile_2  is, in my windows xp machine. Please advise.


0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24134511
0
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.

 
LVL 18

Expert Comment

by:sventhan
ID: 24139028
do you have "exp_full_database" and "imp_full_database" roles to do the export and import?
Have you done the exp/imp before or is this the first time?
0
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 200 total points
ID: 24139051
do you have the DBA roles assigned to you? or, the user that you're using for export and import has the DBA roles/privileage?
 
0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31569731
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.

Question has a verified solution.

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

Suggested Solutions

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

911 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

22 Experts available now in Live!

Get 1:1 Help Now