Before updating a row in a database table, you might want to check for concurrency conflict – another transaction should not be updating the same row simultaneously.
This can be achieved by locking the row in Oracle before update. Here’s how.
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: Convert Non-Partitioned Table to Partitioned [pre 12.2]
Oracle 12.2 has introduced a number of new features that ease partitioning, the most-awaited perhaps is the ALTER TABLE MODIFY syntax to convert a non-partitioned table to partitioned.
What do you do if your database version is pre-12.2? Partitioning a non-partitioned table in pre-12.2 databases is trickier, not impossible. Try DBMS_REDEFINITION: here’s how.
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.
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.
SQL> truncate table customer;
truncate table customer
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL> drop table customer;
drop table customer
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
Clearly enough, Oracle does not allow you to do so because it has flashback archive enabled.
SQL> -- Check history tracking SQL> select flashback_archive_name 2 from dba_flashback_archive_tables 3 where table_name = 'CUSTOMER'; FLASHBACK_ARCHIVE_NA -------------------- TOTALRECALL_FA
How do you get around this problem?
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 error:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_<operation type>'
There is a workaround though, which lets us use MULTISET operations successfully with complex types. Here’s how.
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:
SQL> drop table agreement;
drop table agreement
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
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 problem, and from which tables.
Luckily, there is a quick workaround to ORA-02449, provided you are ready to heed the risks of the approach along with it.
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 table ALL_DEPENDENCIES has the answer to the question: Which objects refer to this table? Walk through the following examples for more.