Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have fired one sql script as sysdba which I have to fire using other oracle user.

I have already created some user and each application running on each oracle user. By mistake I had fired a script using one particular user but I have fired it using sysdba.

  • How to find .dbf file of sysdba user.
  • Can I revert my changes.
  • Does it affect other application.
  • I have list of tables and sequences. Can I drop individual table and sequence?
  • Do you have any better solution.

That script created lots of tables and sequences and inserted data into some tables.

Thanks!!!

share|improve this question

migrated from stackoverflow.com Jan 2 at 19:21

This question came from our site for professional and enthusiast programmers.

1 Answer 1

  1. The default tablespace of the SYS (sysdba) user is typically SYSTEM. From that you can figure out the .dbf file being used, although I'm not sure how this will help.
  2. Maybe. Does your database run using flashback? Also, manually backing out changes (or write a script to undo what you did) might be possible.
  3. Maybe. What changed?
  4. Yes.
  5. Be more careful. Ask this question on dba.stackexchange.com.

EDIT:

I would add that running scripts as the SYSDBA user should be VERY carefully evaluated as to necessity, and probably never used for application purposes. As you can see, the SYSDBA user has essentially unlimited power over your database.

share|improve this answer
    
I know that table and sequences are created and that can't be revert back but I have the list of all tables and sequences. So, I'm going to delete each of them. That is the right approach I guess? –  user3154598 Jan 2 at 18:26
2  
Be very sure that the objects that you are planning on deleting were actually created by the script. If you tried to created a table called SEQ$ then it would have failed, because one already exists under the SYS schema. You would definitely not want to drop it. Check using the DBA_OBJECTS view, and look at the CREATED column to check. On a cheery note, I've worked at some places where a mistake like this would have got you fired. –  David Aldridge Jan 2 at 18:31
    
It is Live Server and now I want to rollback that all at any cost. But, Is there any effect if I am not rollback them as each application is working fine. So please suggest the way to get out of this. –  user3154598 Jan 2 at 18:38
1  
@user3154598 - Second David's comment about being extremely careful about what you drop or change when trying to undo this; you don't want to end up in this situation by dropping something crucial, and pretty much everything owned by SYS is crucial. If you are not the DBA I suggest you get advice and sign-off from them before doing anything else. –  Alex Poole Jan 2 at 19:28
1  
@AlexPoole Rather, if you are not a/the DBA, get one and never run a script as a DBA again. –  Phil Jan 2 at 20:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.