OraFAQ Forum: Backup & Recovery » How to restore a specific table using imp command

Home » RDBMS Server » Backup & Recovery » How to restore a specific table using imp command

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

How to restore a specific table using imp command [message #200439]

Mon, 30 October 2006 23:00

vishubms
Messages: 10
Registered: October 2006
Location: Bangalore

Junior Member

hi techies..

here is my problem...

Some user deleted a table, now he wants to restore the table back. I have yesterday's dump file which was taken by using exp command for the entire database. Now i want to restore just the deleted table of the perticular user....
plz send me the imp command corresponding to the above...

Report message to a moderator

Re: How to restore a specific table using imp command [message #200442 is a reply to message #200439]

Mon, 30 October 2006 23:02

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Oracle Version if 10g. then just used below cmd.
sql>flashback table "table_name" before to drop;

or  through Imp 

cmd>imp scott/tiger@db_name tables=table_name

Report message to a moderator

Re: How to restore a specific table using imp command [message #200472 is a reply to message #200442]

Tue, 31 October 2006 01:15

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

Senior Member
Account Moderator

If it is about the full database export (as I've understood from OP's post), I doubt that "Scott" was the one who did it (i.e. he is not a DBA) - using such a command, it is quite likely to end up with the

IMP-00013: only a DBA can import a file exported by another DBA

Therefore, something like this might work better:

IMP sys/pwd@database FILE=export.dmp FROMUSER=scott TOUSER=scott TABLES=employees

Report message to a moderator

Re: How to restore a specific table using imp command [message #200476 is a reply to message #200472]

Tue, 31 October 2006 01:28

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

SQL> conn scott/tiger@orcl
Connected.
SQL> host imp scott/tiger@orcl tables=employees

Import: Release 10.1.0.2.0 - Production on Tue Oct 31 11:20:55 2006

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

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

SQL> conn / as sysdba
Connected.
SQL> grant imp_full_database,exp_full_database to scott;

Grant succeeded.

SQL> conn scott/tiger@orcl
Connected.

But scott user have "imp_full_database or exp_full_database" privs then he able to imp tables.

Note : forget to mention about privs.

Report message to a moderator

Re: How to restore a specific table using imp command [message #200479 is a reply to message #200476]

Tue, 31 October 2006 01:36

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

Senior Member
Account Moderator

I know you forgot it; that's why guardian angels ... er, devils are here for /forum/fa/1626/0/

Report message to a moderator

Previous Topic:	Cannot recover CURRENT redo log
Next Topic:	Recovery with Parrallelism

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 25 09:17:31 CST 2026