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.

Thumbnail image for How to Lock a Row: SELECT FOR UPDATE

How to Lock a Row: SELECT FOR UPDATE

How to lock a row in Oracle and manage concurrency conflict during database transactions, with the use of the FOR UPDATE clause in SELECT statements.

Read the Full Article →
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 →
Thumbnail image for The Smart Way to Check if an Element Exists in a Collection

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

Oracle 10G onwards, the set operator MEMBER OF can be applied to test if an element exists in a collection or not. You do not need to loop through all elements in the collection to find a match.

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

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

If Oracle Total Recall is enabled on a table and want to truncate or drop the table, you are in for a problem: error “ORA-55610: Invalid DDL statement on history-tracked table”. How to work around this error.

Read the Full Article →

How to Perform MULTISET Operations on Nested Table of Objects

PL/SQL with MULTISET operations on nested table of objects throws up the error ‘PLS-00306: wrong number or types of arguments…’. This workaround lets you use MULTISET operations successfully with complex types.

Read the Full Article →
Thumbnail image for ORA-02449: Solving the DROP TABLE Dilemma

ORA-02449: Solving the DROP TABLE Dilemma

Quick workaround to error ORA-02449. When you want to drop and recreate an Oracle table, but Oracle does not let you do so due to foreign key references.

Read the Full Article →
Thumbnail image for Which objects depend on a given table?

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, views created on it. This is how you can find out which objects refer to this table.

Read the Full Article →