OraFAQ Forum: Backup & Recovery » Importing some tables that were exported by the DBA account.

Home » RDBMS Server » Backup & Recovery » Importing some tables that were exported by the DBA account.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Importing some tables that were exported by the DBA account. [message #184742]

Thu, 27 July 2006 13:09

JenniferBabes
Messages: 18
Registered: March 2006
Location: Student

Junior Member

Hi. I exported some tables from a database using the DBA account.

Now I'm trying to import those tables and here's what I do:

cd c:\oracle\ora92\bin
exp user/user@user
export file: c:\file.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V09.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

What does this error message mean? Did I do something wrong with the import? Here's a log of the import:

C:\oracle\ora92\bin>exp kiosksystem/kioskdev@kioskdb

Export: Release 9.2.0.1.0 - Production on Thu Jul 27 13:38:33 2006

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

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Enter array fetch buffer size: 4096 >

Export file: EXPDAT.DMP > c:\kioskdb.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > ProgramList

. . exporting table PROGRAMLIST 8 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > SystemList

. . exporting table SYSTEMLIST 7 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully without warnings.

How could i import into a user account from a dump file created from a DBA account. I created the user: user with the following SQL code:

create user user identified by user;
grant all privileges to user with admin option;

Report message to a moderator

Re: Importing some tables that were exported by the DBA account. [message #184745 is a reply to message #184742]

Thu, 27 July 2006 13:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>What does this error message mean?
only a DBA can import a file exported by another DBA
The user your created is not a DBA

Report message to a moderator

Re: Importing some tables that were exported by the DBA account. [message #184747 is a reply to message #184742]

Thu, 27 July 2006 13:52

JenniferBabes
Messages: 18
Registered: March 2006
Location: Student

Junior Member

well i have 2 tables in that .dmp file. how can I get those 2 tables into my account. I"m guessing that I created something wrong with the account. Is there any way I could fix it?

create user user identified by user;
grant all privileges to user with admin option;

Is it something I have to alter here? I need to get those 2 tables into this user account.

Jennifer

Report message to a moderator

Re: Importing some tables that were exported by the DBA account. [message #184753 is a reply to message #184747]

Thu, 27 July 2006 14:49

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As you can create a user, I believe you can connect as a privileged user. Therefore, you might try with something like this:

IMP sys/sys_password@database FILE=export_file.dmp FROMUSER=owner_of_those_tables TOUSER=user_which_should_have_tables

Report message to a moderator

Re: Importing some tables that were exported by the DBA account. [message #184908 is a reply to message #184742]

Fri, 28 July 2006 10:13

JenniferBabes
Messages: 18
Registered: March 2006
Location: Student

Junior Member

This is my code to export:

C:\oracle\ora92\bin>exp system/useruser@oracle

Export: Release 9.2.0.1.0 - Production on Fri Jul 28 11:15:44 2006

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


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Enter array fetch buffer size: 4096 >

Export file: EXPDAT.DMP > c:\t.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 3

Export table data (yes/no): yes > no

Compress extents (yes/no): yes >

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > systemlist

. . exporting table                     SYSTEMLIST
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > programlist

. . exporting table                    PROGRAMLIST
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully without warnings.

This is my code to import:

Route 1:

C:\oracle\ora92\bin>imp tstt/tstt@oracle file=c:\t.dmp fromuser = system touser = tstt

Import: Release 9.2.0.1.0 - Production on Fri Jul 28 11:16:13 2006

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


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

Route 2(to import):

C:\oracle\ora92\bin>imp tstt/tstt@oracle

Import: Release 9.2.0.1.0 - Production on Fri Jul 28 11:18:38 2006

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


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Import file: EXPDAT.DMP > c:\t.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V09.02.00 via conventional path
IMP-00013: only a DBA can import a file exported by another DBA
IMP-00000: Import terminated unsuccessfully

I am still getting the same error. Two accounts. One system and the other tstt. I'm sure there is some way to copy a table from system to the tstt account, but at present I can't seem to find it. Any ideas anybody?

The way I see it I have two options:

1. Find a way to import a file exported by a DBA account.
2. Find a way that when the DBA acount generates the dump file, it is generated such that any user account could import it.

I do not know how to do either of these options so if you do know, please send me a reply.

Thanks in advance,
Jennifer

[Updated on: Fri, 28 July 2006 10:16]

Report message to a moderator

Re: Importing some tables that were exported by the DBA account. [message #184909 is a reply to message #184742]

Fri, 28 July 2006 10:35

JenniferBabes
Messages: 18
Registered: March 2006
Location: Student

Junior Member

I solved it. first I logged in as the DBA, and executed this:

Grant DBA to tstt;

Then I imported as I usually did and it worked!

Report message to a moderator

Previous Topic:	9i Backup on Linux RH3 2 gig limit issue
Next Topic:	Recover db from RMAN cold backup

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 05:47:56 CDT 2024