Need Help in Oracle Export/Import? [message #329162] |
Tue, 24 June 2008 06:19 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
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 #329636 is a reply to message #329163] |
Thu, 26 June 2008 02:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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.
|
|
|
|