Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 2 April 2014

How to prevent table from Altering

If you need to secure your table from any kind of DDL actions then following example will help you to understand actually what happens when locks are disabled on the TABLE.
SQL> alter table temp_payroll disable table lock;
Table altered.
Now no one can drop as well as truncate the table as table locks are disabling.
SQL> drop table temp_payroll;
drop table temp_payroll
ORA-00069: cannot acquire lock — table locks disabled for temp_payroll

SQL> truncate table temp_payroll;
truncate table temp_payroll
ORA-00069: cannot acquire lock — table locks disabled for temp_payroll

Also you are not able to modify and drop the column but you are able to add the column.
SQL> alter table temp_payroll modify amount Number (10,2);
ORA-00069: cannot acquire lock — table locks disabled for temp_payroll

SQL> alter table temp_payroll drop column Amount Number (10,2)
alter table temp_payroll drop column Amount Number (10,2)
ORA-00069: cannot acquire lock — table locks disabled for temp_payroll

But you are able to run the DML operation such as insert/update/delete.
SQL> delete from temp_payroll;
1 row deleted.

SQL> alter table temp_payroll add amount Number (10,2);
Table altered.

You can easily enable the table lock to perform any kind of DDL operation again.
SQL> alter table temp_payroll enable table lock;
Table altered.
SQL> drop table temp_payroll;
Table dropped.
Enable table lock allowing DDL operations on the table. All currently executing statements must commit or rollbacks before oracle database enable the table lock. To check the pending transactions:
Select * from dba_2pc_pending;

Select * from dba_2pc_neighbors;

6 comments:

  1. The real question is "why should someone be able to alter/drop a table?"

    ReplyDelete
  2. sir , how i close to his database in oracle 10g...

    ReplyDelete
    Replies
    1. Dear,

      I am not getting you comment.
      Please write in clear way.

      Delete
  3. You state "secure your table from any kind of DDL actions" but your example contains
    SQL> alter table temp_payroll add amount Number (10,2);
    Table altered.
    ?

    ReplyDelete
    Replies
    1. Dear,
      How you checked? I don't know
      If you use: alter table temp_payroll disable table lock; then after
      you cannot perform: alter table temp_payroll add amount Number (10,2);

      Delete
    2. How to check? It is written black on white in your own example! Excerpt:

      SQL> alter table temp_payroll drop column Amount Number (10,2)
      alter table temp_payroll drop column Amount Number (10,2)
      ORA-00069: cannot acquire lock — table locks disabled for temp_payroll

      But you are able to run the DML operation such as insert/update/delete.
      SQL> delete from temp_payroll;
      1 row deleted.

      SQL> alter table temp_payroll add amount Number (10,2);
      Table altered.

      !!

      Delete