Dependencies

If schema object A references schema object B, then A has a dependency on B. Oracle records dependencies automatically to help users and DBAs with dependency management.

DBMS_REDEFINITION for online table redefinition

DBMS_REDEFINITION: Convert Non-Partitioned Table to Partitioned [pre 12.2]

Partitioning a non-partitioned table in an Oracle pre-12.2 database, using DBMS_REDEFINITION built-in methods. Scripts and demo.

Read the Full Article →
Check if an Element Exists in a Collection

The Smart Way to Check if an Element Exists in a Collection

When you need to find out if a specific element exists in a collection or not, how do you go about it? Do you loop through the list till you find the value? If that’s what you do, this post is for you.

Read the Full Article →
ORA-55610: Invalid DDL statement on history-tracked table

ORA-55610: Invalid DDL statement on history-tracked table

If you have got Oracle Total Recall enabled on a table, and now want to truncate or drop the table, you are in for a problem. Clearly enough, Oracle does not allow you to do so because it has flashback archive enabled. How do you get around this problem?

Read the Full Article →
MULTISET Operations on Nested Table of Objects

How to Perform MULTISET Operations on Nested Table of Objects

We’ve seen the easy working of MULTISET operations on nested tables of simple types (i.e. collections of text, numbers). And we’ve seen the problem with extending MULTISET operations to nested tables of complex types (i.e. those based on OBJECTs with multiple attributes). PL/SQL code when run with MULTISET operations on complex collections throws up the […]

Read the Full Article →
ORA-02449: unique/primary keys in table referenced by foreign keys

ORA-02449: Solving the DROP TABLE Dilemma

Sometimes you want to drop and recreate an Oracle table, and are in a fix because when you issue the DROP command Oracle responds with ORA-02449: Disabling constraints has no effect on ORA-02449, the only resolution is to drop constraints. Which presents another dilemma: the error does not specify which foreign keys are causing this […]

Read the Full Article →
Database Object Dependencies

Which objects depend on a given table?

A database table usually has other objects referring to it – tables linked through foreign keys, stored procedures referring to it. You might want to find out – which packages refer to this table? Are there views created on it? If I change the table design, how many and which objects will be affected? The […]

Read the Full Article →