I had a problem earlier when I made some changes to the test/dev DB and didn't know how to revert back to correct state.
Issue:
I couldn't delete a row in a table containing incorrect data as the row's primary key was acting as a foreign key in some other table. If I deleted this row it would violate the integrity of the other table(s).
Process:
So I took a backup of the entire DB (just in case I needed it and I did need it!!).
Dropped the constraints, deleted all the related rows (or something like that)
Recreated the constraints and the DB was restored to correct state.
In the process I learned quite a bit about DB's.
Tried out individual commands in the GUI.
Then copied multiple commands into a script.
Ran the scripts, corrected any errors till I got a working script.
Liberally used transaction and rollback to help in the trial-and-error process.
----
I don't remember the specific details but basically
I used a GUI DB browser (SQL Developer) to connect to a Remote DB by port forwarding from my Unix machine.
Working with command-line client is ok if you're really good at it and know all the command syntax etc.
But using a GUI browser really speeds things up as it can manage things like transactions, rollback etc all at the click of a button.
Database GUI SQL Developer allows you to
+ Try out individual commands (with rollback) to see if you're getting the right results.
+ Copy paste small commands into a bigger sql script (with start transaction at start and commit or rollback at script end)
+ Browse a remote DB in GUI mode using a DB connection (using ssh tunnel),
+ backup the entire database (for safety)
+ maybe try out things on a local database first, before attempting the same on the actual DB.
You can export the original DB. Import into a local DB. Try out the operations to check it works.
It may also help to create try out your modifications on a duplicate DB on remote machine instead of touching the problematic DB directly.
1) SQL Developer Setup and Tunneling (port forwarding) instructions are available here.
http://www.cs.tau.ac.il/~boim/courses/databases2009/slides/moreinfo/connection-guide.htm#_Toc150420090
http://www.madirish.net/?article=152
2) You may need to tweak a few things on your Unix machine to get the tunnel working though.
3) Finding somebody in your/other teams who's good at Databases will be a big help.
Local person who can see what the problem is and give quick suggestion is always better.
4) Searching on error messages would help find howto's for fixing the problem.
5) Search error messages in forums like stackoverflow.com will help to quickly find the info or relevant links.
HTH.
----
Oracle SQL Developer
PDF - Oracle SQL Developer User Guide
Oracle SQL Developer at wikipedia
Oracle SQL Developer Tutorial
Connecting Oracle SQL Developer to MS SQL Server
How-to Export Tables/Entire DB with Oracle SQL Developer
Oracle SQL*Plus command line interface (CLI)
SQL*Plus CLI FAQ
SQL*Plus CLI command reference
SQL*Plus Tutorial
Issue:
I couldn't delete a row in a table containing incorrect data as the row's primary key was acting as a foreign key in some other table. If I deleted this row it would violate the integrity of the other table(s).
Process:
So I took a backup of the entire DB (just in case I needed it and I did need it!!).
Dropped the constraints, deleted all the related rows (or something like that)
Recreated the constraints and the DB was restored to correct state.
In the process I learned quite a bit about DB's.
Tried out individual commands in the GUI.
Then copied multiple commands into a script.
Ran the scripts, corrected any errors till I got a working script.
Liberally used transaction and rollback to help in the trial-and-error process.
----
I don't remember the specific details but basically
I used a GUI DB browser (SQL Developer) to connect to a Remote DB by port forwarding from my Unix machine.
Working with command-line client is ok if you're really good at it and know all the command syntax etc.
But using a GUI browser really speeds things up as it can manage things like transactions, rollback etc all at the click of a button.
Database GUI SQL Developer allows you to
+ Try out individual commands (with rollback) to see if you're getting the right results.
+ Copy paste small commands into a bigger sql script (with start transaction at start and commit or rollback at script end)
+ Browse a remote DB in GUI mode using a DB connection (using ssh tunnel),
+ backup the entire database (for safety)
+ maybe try out things on a local database first, before attempting the same on the actual DB.
You can export the original DB. Import into a local DB. Try out the operations to check it works.
It may also help to create try out your modifications on a duplicate DB on remote machine instead of touching the problematic DB directly.
1) SQL Developer Setup and Tunneling (port forwarding) instructions are available here.
http://www.cs.tau.ac.il/~boim/courses/databases2009/slides/moreinfo/connection-guide.htm#_Toc150420090
http://www.madirish.net/?article=152
2) You may need to tweak a few things on your Unix machine to get the tunnel working though.
3) Finding somebody in your/other teams who's good at Databases will be a big help.
Local person who can see what the problem is and give quick suggestion is always better.
4) Searching on error messages would help find howto's for fixing the problem.
5) Search error messages in forums like stackoverflow.com will help to quickly find the info or relevant links.
HTH.
----
Oracle SQL Developer
PDF - Oracle SQL Developer User Guide
Oracle SQL Developer at wikipedia
Oracle SQL Developer Tutorial
Connecting Oracle SQL Developer to MS SQL Server
How-to Export Tables/Entire DB with Oracle SQL Developer
Oracle SQL*Plus command line interface (CLI)
SQL*Plus CLI FAQ
SQL*Plus CLI command reference
SQL*Plus Tutorial
