DBA

Tasks typically handled by a DBA, such as installation, patching, configuration, migration, security, troubleshooting, monitoring, backup, recovery.

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.

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?

Read more

Oracle Total Recall for Tracking History

Oracle Total Recall

Oracle 11g Flashback Data Archive (Oracle Total Recall), meant as a DBA tool to recover from logical corruptions and human errors, can be put to good use for another standard requirement in applications. Most applications need an audit trail or history of changes for important entities — say, the trail of changes to customer’s data needs to be tracked. Such requirements becomes trivial with Total Recall, eliminating a huge chunk of coding and testing effort.

Read more

Drop and Recreate All Foreign Keys on Oracle Table

UNION vs UNION ALL

The last post showed a workaround for the error “ORA-02449: unique/primary keys in table referenced by foreign keys“, which blocks any attempt to drop an Oracle table if there are foreign keys from other tables referring to it.

The caveat: if the dropped table has to be recreated, the dropped foreign keys must be recreated as well.

How will you determine which foreign keys are to be recreated, and how will you quickly create them? Read on for an easy solution.

Read more

ORA-02449: Solving the DROP TABLE Dilemma

ORA-02449: unique/primary keys in table referenced by foreign keys

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.

Read more

How to Add Table Column at Specific Position

Alter Table Add Table Column at Specific Position

In a perfect world, the database design would be firm and final before development starts. But we do not live in a perfect world – changes can and do happen. You forgot a column in a table, or your client has a change request – for various reasons, you may find that you need to add a column to an existing table.

Oracle allows you to alter a table easily for adding a column, but what if you want the new column added at a specific position in the table? Now you’re in a spot. There is no command to “alter table add column at position 2”; Oracle simply places the column after all the existing columns.

Read more

Unlock Database User Account in Oracle Express Edition

Unlock Database User

Quick tip to unlock database user account in Oracle Express Edition (XE) – this is among the first things you’d need to do when you start using Oracle XE.

The default user HR in Oracle Express Edition is locked when you first try to login after database installation.

SQL> conn hr/hr
ERROR:
ORA-28000: the account is locked

This is apparently due to security reasons, and needs to be unlocked by the administrator.

To unlock HR — or any other database user account in Oracle Database XE – follow the steps below.

Read more

What is CTAS?

Create Table as SELECT (CTAS)

You have probably heard other DBAs/developers and websites talk about creating a database table through CTAS. Does that term leave you befuddled? There’s a very simple explanation to that acronym – read on!

Read more