Quick Question : Finding Transactions on a Table

Folks! this subject line may sound silly, but most of us really think how for a moment.

• Business Requirement is some want to add a column to high volatile table, So they want to understand,

• Does it cause issue as its high volatile and access by Website?
• What is the best time to add a column if so it can issue?
• What is alternate method to minimise the impact if any?

Ans 1: You can add a column, that's not an issue.

Ans2: Adding a column to highly volatile table will not have any impact unless if there are transactions running and uncommitted your DDL may wait and complete thereafter. There's a caveat here, adding a column with null will complete quickly than adding a column with some default value. The logic behind is the new column value must be updated in all the old rows and if the table is huge it will take time and eventually your transactions may piled up as such DDL lock will protect the transactions to be start.

Ans3: Best time to add a column? First find how many transactions happening on that table and if audit enabled find when was least transactions happens

SQL> select * from dba_tab_modifications where table_name in ('XXXXXXXXXXXXXXX');

SCHEMA             TABLE                          INSERTS UPDATES DELETES

PORTAL             XXXXXXXXXXXXXXXXXXXX           16415   16415    9964 

Then, Find from audit trail at what time most of the transactions fired on those tables. ( I am using xml auditing hence v$xml_audit_trail , you can use DBA_AUDIT_TRAIL instead), see at 04AM have very less transactions on the table. Establish a pattern of each day then find out when is the less transactional time for that table.

SQL> select object_name,to_char(EXTENDED_TIMESTAMP,'DD-MON-YY HH24'),count(*) from v$xml_audit_trail  where object_name in ('XXXXXXXXXXXXXXX') group by object_name,to_char(EXTENDED_TIMESTAMP,'DD-MON-YY HH24');

TABLENAME                            DATE HR            Count

--------------------------------  -----------     ---------

XXXXXXXXXXXXXXXXXXXXXXXX10-AUG-16 22483

XXXXXXXXXXXXXXXXXXXXXXXX10-AUG-16 23626

XXXXXXXXXXXXXXXXXXXXXXXX11-AUG-16 00736

XXXXXXXXXXXXXXXXXXXXXXXX11-AUG-16 01962

XXXXXXXXXXXXXXXXXXXXXXXX11-AUG-16 02183

XXXXXXXXXXXXXXXXXXXXXXXX11-AUG-16 03332

XXXXXXXXXXXXXXXXXXXXXXXX11-AUG-16 0466

XXXXXXXXXXXXXXXXXXXXXXXX11-AUG-16 05333

XXXXXXXXXXXXXXXXXXXXXXXX11-AUG-16 07162

XXXXXXXXXXXXXXXXXXXXXXXX11-AUG-16 0862

XXXXXXXXXXXXXXXXXXXXXXXX11-AUG-16 09894

XXXXXXXXXXXXXXXXXXXXXXXX11-AUG-16 10717

ANS 4: Alternative method? for Adding a column without impact? Scratching my head really 🙂 , Do you know any? Apart from keeping a replicated table and rename (still it will lock the table)

Geek DBA