If you need to delete all rows in a table that has parent keys for other tables’ foreign keys, and the foreign keys constraints have not been defined with “on delete cascade”, you can do a recursive delete with the following simple procedure. This is typically something you will do only in a test or development database, and not in production. As always, it is a good thing to understand this procedure before you execute it: ...
This post is a mixture of experiences from a recent project and an overview on how to use RAT in a project. Real Application Testing (RAT) is a licensed feature for the Enterprise Edition (EE) of the Oracle database. It was released on 11g, but can be used to test migration of databases from 9i and 10g to 11g (assuming that the source databases have a required version/patch). The term Real is, I reckon, used to hint at a more authentic testing process. The tests are executed based on a capture from a real production system. ...
A couple of months ago I had an incident on a live production system. The obvious error was to execute a DDL statement at 9 am that should have been performed during off-business hours. On the other hand I guess you cannot avoid any ad hoc change on a live system forever. That will require you to be aware of any problem and issue that may strike your system. Say you have an issue right now, users are complaining, or to put it bluntly, someone is losing money. You think you have the solution, but before you actually apply it you want to know if it will actually work or make matters worse. Most people will run to their test system, at least to check that the syntax is right. I had verified it long time ago and knew it would work. However, there is one problem with test systems. Though we strive to make them as similar to the original production system as possible, they never are. Even if you can afford the seemingly waste of hardware and space, something is likely to be missing in the test system. In my case it was the users… With Oracle 11g and Real Application Testing (which I have not tried yet) the load can be better mirrored in the test system, but I can’t imagine it will behave as real users. ...