Home » SQL & PL/SQL » SQL & PL/SQL » Backup table using create statement (10.2.0.2.0 ,windows)
Backup table using create statement [message #428470] Wed, 28 October 2009 08:12 Go to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Hi all,

I created a backup table before deleting some records in the table.

Here is the steps which i followed.
create table MAN_BKP as select * from MAN;
delete from man where records =1;

By mistake,i created the backup table MAN_BKP in A user (which should be created in user B)
and deleted the records in different user B.

Now my backup table is in user A.

My question is how to revert those backup table of user A to user B.

Thanks,


Re: Backup table using create statement [message #428472 is a reply to message #428470] Wed, 28 October 2009 08:19 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do another create table as select while logged in as user B?
Re: Backup table using create statement [message #428473 is a reply to message #428470] Wed, 28 October 2009 08:20 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
and remember to grant SELECT privileges on the table you need to select from now.
Re: Backup table using create statement [message #428474 is a reply to message #428472] Wed, 28 October 2009 08:22 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


Cookie

I did not get your point.Could you please be clear.

Regards,
Re: Backup table using create statement [message #428478 is a reply to message #428470] Wed, 28 October 2009 08:29 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Seems fairly obvious?

Log in as user B
do:
create table MAN_BKP as select * from a.MAN_BKP;


Or did you mean something else?
Re: Backup table using create statement [message #428494 is a reply to message #428478] Wed, 28 October 2009 09:09 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


I cannot run this backup table in user B as i had already deleted some records in user B.


Regards,
Re: Backup table using create statement [message #428496 is a reply to message #428470] Wed, 28 October 2009 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then you need to explain in more detail what the problem is, because it's not at all clear.
Re: Backup table using create statement [message #428507 is a reply to message #428496] Wed, 28 October 2009 09:51 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Let me explain one more time.

I have deleted some records in table in some user AA for which i had taken backup
not in the same user, but in different user.

Could you please tell me how to revert that backup table which i have created in
different user to the user (where i deleted records).

Hope it is clear now.


Regards,
Re: Backup table using create statement [message #428517 is a reply to message #428507] Wed, 28 October 2009 10:35 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Hitman11 wrote on Wed, 28 October 2009 14:51

Could you please tell me how to revert that backup table which i have created in
different user to the user (where i deleted records).

To me this means that you want to move the backup table with all it's data to the user where you deleted the records. In which case I've already given the solution.

If that's not what you mean then you're going to have to describe the problem in more detail instead of just repeating yourself.
Re: Backup table using create statement [message #428539 is a reply to message #428507] Wed, 28 October 2009 13:01 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Hitman11 wrote on Wed, 28 October 2009 10:51

I have deleted some records in table in some user AA for which i had taken backup
not in the same user, but in different user.


What schema, what table, when? List a detailed description with schema names and table names along with a timeline of when things were done. You're explanation leaves a lot to be desired. "Same," "different," "some" are such ambiguous words.
Re: Backup table using create statement [message #428541 is a reply to message #428470] Wed, 28 October 2009 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>My question is how to revert those backup table of user A to user B.

INSERT INTO B.MAN SELECT * FROM A.MAN_BKP WHERE RECORDS=1;
Re: Backup table using create statement [message #428592 is a reply to message #428541] Thu, 29 October 2009 01:24 Go to previous message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member

Hi Cookie monster,

I followed your below step.It worked.

"create table MAN_BKP as select * from a.MAN_BKP;"

Thanks for your help.

Thank you all for your inputs.

Regards,





[Updated on: Thu, 29 October 2009 01:24]

Report message to a moderator

Previous Topic: Assistance With Script for Monthly DB & EBS Sessions.
Next Topic: Invalid identifier when update
Goto Forum:
  


Current Time: Tue Feb 11 02:55:33 CST 2025