0

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!!!

Improve this question

1 Answer 1

Reset to default
2
  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.

Improve this answer
7
  • 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
    Commented Jan 2, 2014 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
    Commented Jan 2, 2014 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
    Commented Jan 2, 2014 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
    Commented Jan 2, 2014 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ᵀᴹ
    Commented Jan 2, 2014 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.