|
|
|
Re: how to back up and restore the tables [message #187383 is a reply to message #187373] |
Sun, 13 August 2006 03:25 ![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: 21809 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
As you've mentioned "fast", perhaps it is all about testing purposes? For example, you have a table which is full of useful data. Now you'd like to apply some UPDATEs which could be harmful to this table and mess up the data.
To backup this table, you could create the backup table - if it is not too large - in the current schema as
SQL> CREATE TABLE my_backup_table AS SELECT * FROM original_table;
If anything goes wrong with the original one, delete (truncate/drop - choose one which suits the most your needs) it and restore it using
SQL> INSERT INTO original_table SELECT * FROM my_backup_table;
Furthermore, save it into the export file:
OS> EXP username/password@database TABLES=original_table FILE=backup.dmp
To restore it, use IMP command:
OS> IMP username/password@database FILE=backup.dmp
This statement might require use of IGNORE=Y if the table still exists in a schema (because CREATE TABLE statement would fail; with IGNORE=Y, Import utility will just import data into the table). Also, be careful because if there's still data in the table, you'll append backup data into existing ones which will also fail if there's unique key constraint (or index) in the table. Therefore, perhaps the best scenario would be
SQL> DELETE original_table;
OS> IMP username/password@database FILE=backup.dmp IGNORE=Y
Does any of this help? Or did I miss the point?
|
|
|
Re: how to back up and restore the tables [message #187389 is a reply to message #187116] |
Sun, 13 August 2006 04:18 ![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) |
youthsen
Messages: 45 Registered: August 2006
|
Member |
|
|
hi Littlefoot
thank you very much.
i have worked the first two command.
but i don't know how to enter in to "os>"
then only i can enter your commands.
so pls give me any on to enter into "os>" for export the table as file.
i am expecting your reply.
|
|
|
|
Re: how to back up and restore the tables [message #187529 is a reply to message #187116] |
Mon, 14 August 2006 06:29 ![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) |
youthsen
Messages: 45 Registered: August 2006
|
Member |
|
|
hi Littlefoot
once again i am telling that i am new to oracle.
i dont know how to enter os> prompt.
is it with in oracle? or winows?
my table name is - sen
database name - oracle.world
username/password = scott/tiger
pls give the info to enter into "os>" prompt.
is it like "start-->run-->command then c:\windows>" ?
i am expecting your reply.
|
|
|
|
Re: how to back up and restore the tables [message #187540 is a reply to message #187116] |
Mon, 14 August 2006 06:57 ![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) |
youthsen
Messages: 45 Registered: August 2006
|
Member |
|
|
hi littelfoot
my table name = sen
database name = oracle.world
username/password = scott/tiger
i entered such as
start-->run--->command
now it opens like
c:\windows>exp scott/tiger@oracle.world tables=sen file=sen.dmp
bad command or file name.
so it shows like this. so what to do to solve this problem.
pls reply.
|
|
|
|
Re: how to back up and restore the tables [message #187552 is a reply to message #187116] |
Mon, 14 August 2006 07:33 ![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) |
youthsen
Messages: 45 Registered: August 2006
|
Member |
|
|
hi Littlefoot
i am using windows98 os and personal oracle for windows.
is this "personal oracle for windows" is a client version?
and i find the files "exp80.exe" and "imp80.exe" at follwoing location.
c:\orawin98\bin>
can i use this files to export and import?
i used exp80.exe instead of exp. but it shows the error msg.
how can i find the files "exp.exe" and "imp.exe" from source cd of personal oracle for windows.
pls help me.
|
|
|
|
Re: how to back up and restore the tables [message #187595 is a reply to message #187116] |
Mon, 14 August 2006 10:39 ![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) |
youthsen
Messages: 45 Registered: August 2006
|
Member |
|
|
hi
i entered as follwos
c:\orawin95\bin>exp80 scott/tiger@oracle.world tables=sen file=senthil.dmp
Export release 8.0.3.0.0 production on Mon Aug 14 12:2:47 2006
(c) Copyright 1997 oracle corporation. All rights reserved.
EXP-00056: ORACEL error 12154 encountered
ORA-12154: TNS:could not resolve service name
EXP-00000: Export terminated unsuccessfully
how to solve this error
|
|
|
|
Re: how to back up and restore the tables [message #187847 is a reply to message #187116] |
Wed, 16 August 2006 00:23 ![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) |
youthsen
Messages: 45 Registered: August 2006
|
Member |
|
|
hi littlefoot
my table name:sen
username/password : scott/tiger
i used the follwoing command to find my database name after logged in as scott/tiger.
SQL> select * from global_name;
GLOBAL_NAME
----------------------------------------
ORACLE.WORLD
i used the following command to find the database name by logged in as system/manage.
SQL> select name from v$database;
NAME
---------
ORACLE
so which database name i have to use that "oracle" or "oracle.world".
is there any other method to find the database name.
the two files are "Sqlnet.ora" and "Tnsnames.ora" are located in c:\orawin95\net80\admin.
i have attached a file "Sqlnet.ora". but i dont know how to attach more than one file. so i am attaching another file "Tnsnames.ora" in next post.
pls reply very fast.
-
Attachment: Sqlnet.ora
(Size: 0.18KB, Downloaded 1244 times)
[Updated on: Wed, 16 August 2006 00:46] Report message to a moderator
|
|
|
|
|
Re: how to back up and restore the tables [message #188445 is a reply to message #187116] |
Fri, 18 August 2006 10:36 ![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) |
youthsen
Messages: 45 Registered: August 2006
|
Member |
|
|
hi little foot
i exported the table using the commands
c:\windows>exp80 scott/tiger tables=sen file=sen.dmp
i imported the table using the command
c:\windows>imp80 scott/tiger file=sen.dmp tables=sen
i did not mention the database name. but it is working fine.
why it doesn't need database name
|
|
|
|
|