oracle
Oracle Script – Disable/Enable constraints
While working on databases, many a time one needs to enable/disable constraints for various reasons. Query to disable a constraint: ALTER TABLE table_name DISABLE CONSTRAINT constraint_name; Query to enable a constraint: ALTER TABLE table_name ENABLE CONSTRAINT constraint_name; While writing DMLs, when I know that some query operation would change the existing [...]
Oracle – Removing Partitioning from Tables Without Deleting Data
For one of my projects, we wanted to perform some tests and had to remove list partitiong (without deleting data) from the existing tables. From what I could gather, although there are commands to Drop partitions, they drop all the data in the partition as well. There is no direct command or approach to remove [...]
How to generate Explain Plan? – Oracle
What is an Explain Plan? I’m a PL/SQL developer, who like many others, never bothered about explain plans and how Oracle executes what I’ve written. After migrating from 9i to 10g, a set of Oracle stored procedures started taking too long. The first step to start the investigation was to look at the explain plan. [...]
Oracle Performance Degradation – 9i to 10g migration issue
Problem: After migrating from Oracle 9i to 10g, performance of a set of stored procedures started degrading erratically. A set of Stored Procedure (SP) that took about 30 mins on Oracle 9i, started taking 12+ hours in Oracle 10g. In subsequent executions, the performance degraded further to 46+ hrs. Some of methods tried to get [...]