Home » RDBMS Server » Backup & Recovery » Importing some tables that were exported by the DBA account.
Importing some tables that were exported by the DBA account. [message #184742] Thu, 27 July 2006 13:09 Go to next message
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;
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 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 20900
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
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 Go to previous messageGo to next message
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 Go to previous message
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!
Previous Topic: 9i Backup on Linux RH3 2 gig limit issue
Next Topic: Recover db from RMAN cold backup
Goto Forum:
  


Current Time: Thu Dec 08 12:46:32 CST 2016

Total time taken to generate the page: 0.09274 seconds