Home » RDBMS Server » Backup & Recovery » Need Help in Oracle Export/Import? (Oracle 10g R2 , Windows Server 2003)
Need Help in Oracle Export/Import? [message #329162] Tue, 24 June 2008 06:19 Go to next message
wasimsda
Messages: 2
Registered: March 2008
Location: Bangalore
Junior Member

I have dropped a column from a table, and i want to get the data of the column back. I have the export file of that username before the column of the table was dropped. By which command i will get back my dropped column and also the data.
Re: Need Help in Oracle Export/Import? [message #329163 is a reply to message #329162] Tue, 24 June 2008 06:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You need to import the whole table. Not just the column.
I would import it into another dummy database and bring only the required table.
Before you do any of these,
did you try your luck in oracle recylebin?
Re: Need Help in Oracle Export/Import? [message #329636 is a reply to message #329163] Thu, 26 June 2008 02:22 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Mahesh
... into another dummy database
If you don't have a spare database, perhaps another "dummy" schema in YOUR database might also be a way to do that.

Here's a walkthrough (in a case you need it): first, we'll create a table and export its contents:
M:\>sqlplus scott/tiger@ora10

SQL> create table some_table
  2  (id number,
  3   name varchar2(20));

Table created.

SQL> insert into some_table (id, name) values (1, 'Littlefoot');

1 row created.

SQL> commit;

Commit complete.

SQL> $exp scott/tiger@ora10 tables=some_table file=some_table.dmp

About to export specified tables via Conventional Path ...
. . exporting table                     SOME_TABLE          1 rows exported
Export terminated successfully without warnings.

Now, drop a column:
SQL> alter table some_table drop column name;

Table altered.

SQL> select * from some_table;

        ID
----------
         1

Import a table into another user's schema in the same database (make sure that this user doesn't have this table in his schema!):
SQL> $imp mike/lion@ora10 file=some_table.dmp tables=some_table

Warning: the objects were exported by SCOTT, not by you

import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into MIKE
. importing SCOTT's objects into MIKE
. . importing table                   "SOME_TABLE"          1 rows imported
Import terminated successfully without warnings.

Now, grant required privileges to the owner of the "original" table so that we could perform data transfer:
SQL> connect mike/lion@ora10
Connected.
SQL> grant select on some_table to scott;

Grant succeeded.

Finally, add a missing column to the "original" table and insert missing data:
SQL> connect scott/tiger@ora10
Connected.
SQL> desc some_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER

SQL> alter table some_table add name varchar2(20);

Table altered.

SQL> update some_table s set
  2    s.name = (select m.name
  3              from mike.some_table m
  4              where m.id = s.id
  5             );

1 row updated.

SQL> select * from some_table;

        ID NAME
---------- --------------------
         1 Littlefoot

SQL>

I guess that should be the end.
Re: Need Help in Oracle Export/Import? [message #330987 is a reply to message #329162] Wed, 02 July 2008 00:51 Go to previous message
pranabhjain
Messages: 3
Registered: July 2008
Junior Member
Just import the table some other schema.Add column back to original table.then update the original table by joining new duplicate table with primary keys.

Regards
Pranabh
Previous Topic: how to recover the database from this errorsORA-00313 ORA-00312 ORA-27046 OSD-04012
Next Topic: Rman error
Goto Forum:
  


Current Time: Sun Dec 04 10:39:28 CST 2016

Total time taken to generate the page: 0.12361 seconds