Home » RDBMS Server » Backup & Recovery » How to restore a specific table using imp command
How to restore a specific table using imp command [message #200439] Mon, 30 October 2006 23:00 Go to next message
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...
Re: How to restore a specific table using imp command [message #200442 is a reply to message #200439] Mon, 30 October 2006 23:02 Go to previous messageGo to next message
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
Re: How to restore a specific table using imp command [message #200472 is a reply to message #200442] Tue, 31 October 2006 01:15 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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
Re: How to restore a specific table using imp command [message #200476 is a reply to message #200472] Tue, 31 October 2006 01:28 Go to previous messageGo to next message
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.
Re: How to restore a specific table using imp command [message #200479 is a reply to message #200476] Tue, 31 October 2006 01:36 Go to previous message
Littlefoot
Messages: 20901
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 ./fa/1626/0/
Previous Topic: Cannot recover CURRENT redo log
Next Topic: Recovery with Parrallelism
Goto Forum:
  


Current Time: Sat Dec 10 01:09:37 CST 2016

Total time taken to generate the page: 0.21699 seconds