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?
[click to continue…]
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.
[click to continue…]
Are you data modeling for a custom OLTP application starting from scratch, with business requirements as the only initial input?
No matter what the size, domain or location of your project, the following lessons will help to ensure that your physical data modeling process is smooth and that you end up with a database design that is accurate, usable and scalable.
[click to continue…]
As an Oracle developer, you have probably wondered at some point: do user and schema in Oracle really mean the same thing? And if they do, why does Oracle have two names for it?
This post helps to clarify the sameness – or otherwise – of Oracle user and schema.
[click to continue…]
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.
[click to continue…]
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.
[click to continue…]
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.
[click to continue…]
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.
[click to continue…]
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!
[click to continue…]
