Oracle 11g R1: DDL_LOCK_TIMEOUT

Whenever you are trying to alter a table to add a new column, exclusive lock will be acquired on the table, if the table is used by any other transaction at the same time oracle will throw the following error.

SQL> DESC T

Name            Null?    Type
--------------- -------- -------------
OBJECT_ID       NOT NULL NUMBER
OBJECT_NAME     NOT NULL VARCHAR2(30)

SQL> ALTER TABLE T ADD STATUS CHAR(1);

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

Note: I have opened one more Session #2 and inserted one record (DML) to table t and switched to Session #1 without issuing COMMIT in Session #2

The table 'T' is being used right now by another transaction by someother session, so getting an exclusive lock on the table 'T' may be not impossible at this moment.

Once the user transaction completes, then the user saves tranactions by commit statement, then the acquired lock will be released. Then we can issue the alter command to add a new column.

During busy system time the window for locking the table exclusively does open periodically, but the DBA may NOT be able to perform the alter command exactly at that time.

In Oracle Database 11g, we have a solution for the above problem: DDL Wait option

SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 30;

Session altered.

Now, If a DDL statement in the session does not get the exclusive lock, it will NOT throw an error. Instead, it will wait for 30 seconds. During this period, it continously re-tries the DDL operation until it's successful or the time expires, whichever comes first.

SQL> ALTER TABLE T ADD STATUS CHAR(1);

The above statement hangs and does not give any error, instead of trying repeatedly for an exclusive lock to exeucte alter command.
Globally (for all sessions) also you can enable this feature by the below command;

SQL> ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 30;

System altered.

Don't have Oracle 11g R1?, dont worry, still we can achieve this; Here is my PLSQL code for the same functionality, it will work in any verion of oracle

SQL> set serveroutput on
SQL> declare
  2  v_start   date;
  3  v_end     date:= sysdate+(30+1)/(24*60*60); -- adding one second additionally as an overhead
  4  is_locked boolean:=true;
  5  begin
  6    while (true)
  7     loop
  8       begin
  9         execute immediate 'alter table t add column status char(1)';
 10         dbms_output.put_line('Exclusive lock on table T acquired and column has been added successfully.');
 11         exit;
 12       exception
 13         when others then
 14           null;
 15       end;
 16       v_start := sysdate;  -- Assigning current time
 17       if (v_start = v_end) then  -- Checking for exit criteria
 18         is_locked:=false;
 19         exit;  -- breaking the loop
 20       end if;
 21     end loop;
 22     if (is_locked = false) then
 23       dbms_output.put_line('Sorry, Exclusive Lock request on table T has been failed. Please try again...');
 24     end if;
 25  end;
 26  /
Sorry, Exclusive Lock request on table T has been failed. Please try again...

PL/SQL procedure successfully completed.

Cheers,
Hope you have enjoyed the experiment.
Post your comments (if any).

All about crontab

crontab is a unix utility that allows tasks (shell programs/commands) to be automatically run in the background at according to the specified intervals.
It is one of essential utility which has been used by DBAs on day to day basis. I have used Solaris 5.9 for examples posted in this article.

Here is my standpoint on crontab (based on my practical and theoretical knowledge),

Syntax:
~~~~~~
o crontab [filename] -- This will overwrite an existing crontab entry for the user that invokes it.

o crontab -elr [username]
    e - Edits a copy of the current user's crontab file, or creates an empty file to edit if crontab does not exist
    l - Lists the crontab file for the invoking user
    r - Removes a user's crontab from the crontab directory


Supporting Files:
~~~~~~~~~~~~~~~~
o /etc/cron.d             : main cron directory
o /etc/cron.d/cron.allow  : list of allowed users
o /etc/default/cron       : contains cron default settings
o /etc/cron.d/cron.deny   : list of denied users
o /var/cron/log           : accounting information
o /var/spool/cron/crontabs: spool area for crontab


Access permission (Scenarios):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Users: Access to crontab is allowed:
  o if the user's name appears in /etc/cron.d/cron.allow.
  o if cron.allow  does  not  exist  and  the user's name is not in cron.deny

Users: Access to crontab is denied:
  o if cron.allow exists and the user's name is not in it.
  o if cron.allow does not exist and user's name is in cron.deny
  o if neither file exists, only a user with the solaris.jobs.user authorization is allowed to submit a job.
  o If BSM audit is enabled, the user's shell is not audited and the user is not the crontab owner. This can occur if the user logs in via a program, such as some versions of SSH, which does not set audit parameters.



Crontab Pattern:
~~~~~~~~~~~~~~~

*  *  *  *  * [nohup] script (full path) [> logfile location]
_  _  _  _  _
|  |  |  |  |
|  |  |  |  |
|  |  |  |  |
|  |  |  |  |
|  |  |  |  +----------> Day of the week (0-6 with 0=Sun) 
|  |  |  |
|  |  |  +-------------> Month of the year (1-12)
|  |  |
|  |  +----------------> Day of the month (1-31)
|  |
|  +-------------------> Hour (0-23)
|  
+----------------------> Minute (0-59)


Symbols:
~~~~~~~
- Used to specify the range between two integers
, Used to specifiy the multiple integers


Examples:
~~~~~~~~
00 13 * * *
--> Runs at 1 PM on every day

00 13 7 * *
--> Runs at 1 PM on every 7th day of all months

00 13 1-10 1,2 *
--> Runs at 1 PM during 1st to 10th days of Jan & Feb months

00 14 * * 1-5
--> Runs at 2 PM on all business days (Mon to Fri)

00 11 * * 6,0
--> Runs at 11 AM on weekends (Sat & Sun)

0,10,20,30,40,50 * * * *
--> Runs every 10 mins

Hope you have a gained some knowledge about Unix Job Scheduler. Have a nice day.

Please post your comments (if any).

Beware of ALTER TABLE MOVE Command

Yesterday, i read about Rebuilding of Table in "Practical Oracle 8i" By Jonathan Lewis, it was well explained. Very good book, yes its my all time favorite book.
I would like to post an article about the possible ways of rebuilding table (mainly with the help of MOVE command) and its impacts.

Oracle provides many ways to reorganize/rebuild a table,

alter table move

dbms_redefinition

ctas (ie. create table as select...)

exp/imp

expdp/impdp (datapump)

Here some more explanation about alter table move command, The alter table move command allows us to change the tablespace, storage clauses, lowers highest watermark etc.

If you have to rebuild a table, always consider the move command first. Availability can be reduced (During Move command execution, no transaction should refer on the same object else we may get ORA-08103: Object no longer exists), but the benefit to administrative safety and convenience is huge. There are no windows of opportunity for data to go missing.

Now we will see an impact of MOVE command,
Afer the execution of move command, the dependent indexes will become INVALID/UNUSABLE since it changes the ROWIDs of the table rows. So we have to rebuild a dependent indexes immediately by the below command.

ALTER INDEX index_name REBUILD;

Here is my experiment,

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table t
  2  as
  3  select * from all_objects;

Table created.

SQL> insert into t select * from t;

32659 rows created.

SQL> insert into t select * from t;

65318 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_t on t(id);

Index created.

SQL> select index_name,index_type,status
  2  from user_indexes
  3  where index_name='IDX_T';

INDEX_NAME       INDEX_TYPE        STATUS
---------------- ----------------- --------
IDX_T            NORMAL            VALID

SQL> set linesize 200;
SQL> set autotrace traceonly explain;

SQL> select * from t where object_id=9;


Execution Plan
----------------------------------------------------------
Plan hash value: 3868287381

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     4 |   512 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     4 |   512 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=9)


SQL> set autotrace off;

SQL> alter table t move;

Table altered.


SQL> select index_name,index_type,status
  2  from user_indexes
  3  where index_name='IDX_T';

INDEX_NAME      INDEX_TYPE    STATUS
--------------- ------------- --------
IDX_T           NORMAL        UNUSABLE

SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    24 |  3072 |   478   (2)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |    24 |  3072 |   478   (2)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=9)

Note
-----
   - dynamic sampling used for this statement

SQL> alter index idx_t rebuild;

Index altered.

SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 3868287381

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     4 |   512 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     4 |   512 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=9)

Note
-----
   - dynamic sampling used for this statement

The conclusion is, after execution of move command, the dependent indexes should be rebuilt immediately.

Hope you have enjoyed the experiment.

Please post your comments (if any).