Thursday, June 18, 2009


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.


Name            Null?    Type
--------------- -------- -------------


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


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.


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;


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.

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

Tuesday, June 9, 2009

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),

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 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).

Monday, June 1, 2009

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;
    Oracle Database 10g Enterprise Edition Release - 64bi
    PL/SQL Release - Production
    CORE      Production
    TNS for Solaris: Version - Production
    NLSRTL Version - 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';
    ---------------- ----------------- --------
    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';
    --------------- ------------- --------
    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)
       - 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)
       - 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).

    Thursday, May 14, 2009

    Oracle 11g - Alert Log (X$DBGALERTEXT)

    Today, i would like to share one new feature of Oracle 11g which i have come across recently.

    In Oracle 11g, gives us a x$dbgalertext fixed table that maps to the alert log file, and allowing us to write SQL queries against it. It is very good news for Oracle Developers; becuase they dont have to depend on DBA's to view the alert log content.

    SQL> desc x$dbgalertext
    Name                       Null? Type
    -------------------------- ----- ---------------------------
    DDR                              RAW(4)
    INDX                             NUMBER
    INST_ID                          NUMBER
    ORGANIZATION_ID                  VARCHAR2(64)
    COMPONENT_ID                     VARCHAR2(64)
    HOST_ID                          VARCHAR2(64)
    HOST_ADDRESS                     VARCHAR2(16)
    MESSAGE_TYPE                     NUMBER
    MESSAGE_LEVEL                    NUMBER
    MESSAGE_ID                       VARCHAR2(64)
    MESSAGE_GROUP                    VARCHAR2(64)
    CLIENT_ID                        VARCHAR2(64)
    MODULE_ID                        VARCHAR2(64)
    PROCESS_ID                       VARCHAR2(32)
    THREAD_ID                        VARCHAR2(64)
    USER_ID                          VARCHAR2(64)
    INSTANCE_ID                      VARCHAR2(64)
    DETAILED_LOCATION                VARCHAR2(160)
    PROBLEM_KEY                      VARCHAR2(64)
    UPSTREAM_COMP_ID                 VARCHAR2(100)
    DOWNSTREAM_COMP_ID               VARCHAR2(100)
    EXECUTION_CONTEXT_ID             VARCHAR2(100)
    ERROR_INSTANCE_ID                NUMBER
    VERSION                          NUMBER
    MESSAGE_TEXT                     VARCHAR2(2048)
    MESSAGE_ARGUMENTS                VARCHAR2(128)
    PARTITION                        NUMBER
    RECORD_ID                        NUMBER
    Out of all attributes from x$dbgalertext, MESSAGE_TEXT attribute is the key here. It contains the alert log conent. Have nice time.

    Please post your comments (if any).

    Monday, May 4, 2009

    Experiment on EXECUTE IMMEDIATE

    Today, We will do an interesting experiment on EXECUTE IMMEDIATE statement. This statement allows us to execute DYNAMIC SQL Statements inside the PLSQL Procedure. It is one of the good feature from Oracle. For SYNTAX and more information about this you can find out in Oracle Documentation.

    Now we are going to Create one Database User and then we will write one PLSQL procedure with EXECUTE IMMEDIATE statement.
    Lets see,

    SQL> -– Login as SYSDBA and Create the user called ‘DEMO’
    SQL> show user
    USER is "SYS"
    SQL> create user demo identified by demo;
    User created.
    SQL> grant connect,resource to demo;
    Grant succeeded.
    Database user ‘Demo’ has been created successfully and granted CONNECT, RESOURCE roles to demo user. Okay, It is time to understand some basics of Oracle User Management.

    Privilege: A privilege is a permission to execute a SQL Statement or to access another Database User’s Objects. From Oracle Perspective, there are two types or privileges, SYSTEM Privileges like (CREATE TABLE, CREATE VIEW, CREATE PUBLIC SYNONYM etc) and OBJECT Privileges like (SELECT, UPDATE, DELETE, INSERT on Objects eg. TABLE).

    Role: A role is a set or group of privileges that can be granted to Users or another Role. “Role is a set of privileges“. Then, What are the privileges that are grouped in CONNECT and RESOURCE Roles?. Let me query the Data Dictionary Tables
    SQL> desc role_sys_privs;
    Name                       Null?        Type
    -------------              --------     ------------   
    ROLE                       NOT NULL     VARCHAR2(30)
    PRIVILEGE                  NOT NULL     VARCHAR2(40)
    ADMIN_OPTION                            VARCHAR2(3)
    SQL> select privilege 
      2  from role_sys_privs 
      3  where role = ‘CONNECT’;
    SQL> select privilege 
      2  from role_sys_privs 
      3  where role = ‘RESOURCE’;
    8 rows selected.
    Alright. Lets continue the experiment, As, i said earlier the user called ‘DEMO’ has been created and granted some roles. Now connect as DEMO user and Create a PLSQL procedure with EXECUTE IMMEDIATE Statement.
    SQL> conn demo/demo
    SQL> show user
    USER is "DEMO"
    SQL> /*Create a procedure to create table called t and
    SQL>  attributes id,name with EXECUTE IMMEDIATE Statement */
    SQL> create or replace procedure p
      2  is
      3  begin
      5                     (ID NUMBER, 
      6                      NAME VARCHAR2(20))’;
      7  end;
      8  /
    Procedure created.
    SQL> -- Created the procedure called “P”. 
    SQL> -- Let me execute this procedure
    SQL> execute p;
    BEGIN p; END;
    ERROR at line 1:
    ORA-01031: insufficient privileges
    ORA-06512: at “DEMO.P”, line 4
    ORA-06512: at line 1
    Oops!, got an error “insufficient privileges” - Do we have this privilege?, YES. we have. It has been grouped under RESOURCE role and the RESOURCE role has been granted to this user (FYI: We have queried the Data Dictionary table to see the privileges that are mapped under RESOURCE role. SEE ABOVE). Then, What is stopping us? Let me grant the CREATE TABLE privilege directly to the DEMO user.
    SQL> conn sys@orcl as sysdba
    Enter password: *******
    SQL> show user
    USER is "SYS"
    SQL> grant CREATE TABLE to DEMO;
    Grant succeeded.
    SQL> conn demo/demo
    SQL> show user
    USER is "DEMO"
    SQL> -- Let me execute the procedure now
    SQL> execute p;
    PL/SQL procedure successfully completed.
    SQL> — Wow! Table ‘T’ got created.
    SQL> desc t;
    Name                Null?    Type
    ----                -----    ------------
    ID                           NUMBER
    NAME                         VARCHAR2(20)
    The Conculsion is, In STORED PROCEDURE, roles are DISABLED. Hence any PRIVILEGES granted by a ROLE to USER, will NOT be in effect. The PRIVILEGES MUST be explicitly granted to the user.

    Hope you have enjoyed the experiment :-)

    Post your suggestions (if any).

    Reverse Engineer - Database Schema

    There are lots of tools available in the market to reverse engineer your existing database schema objects and creates the Data Model from it
    (Eg. Microsoft Visio, JDeveloper etc).

    To do this job, I have prepared a SQL Script; the resultset of this query will tell you the Parent/Child relationships (FK_TABLE, FK_COLUMN referring PK_TABLE, PK_COLUMN). I feel this will be very handy script for DBAs, Application Developers and Database Architect to analyze the Parent-Child relationship for their existing database objects in the schema.

    I have executed the below script in SCOTT Schema, Please verify the output.

    SQL>column fk_table format a30
    SQL>column fk_column format a30
    SQL>column pk_table format a30
    SQL>column pk_column format a30
    SQL>set linesize 140
    SQL> SELECT p.table_name pk_table, 
      2         p.column_name pk_column,
      3         c.table_name fk_table, 
      4      c.column_name fk_column
      5    FROM (SELECT uc.constraint_name, 
      6                 ucc.table_name, 
      7        ucc.column_name
      8            FROM user_constraints uc, 
      9           user_cons_columns ucc
     10           WHERE uc.constraint_name IN (
     11         SELECT r_constraint_name
     12                 FROM user_constraints
     13                 WHERE constraint_type = 'R')
     14           AND uc.table_name = ucc.table_name
     15           AND uc.constraint_name = ucc.constraint_name) p,
     16         (SELECT uc.r_constraint_name, 
     17           ucc.table_name, 
     18        ucc.column_name
     19            FROM user_constraints uc, 
     20          user_cons_columns ucc
     21           WHERE uc.constraint_name IN (
     22         SELECT constraint_name
     23                 FROM user_constraints
     24                 WHERE constraint_type = 'R')
     25             AND uc.table_name = ucc.table_name
     26             AND uc.constraint_name = ucc.constraint_name) c
     27   WHERE p.constraint_name = c.r_constraint_name;
    --------  ---------  --------  ---------
    DEPT      DEPTNO     EMP       DEPTNO
    Hope this will be useful for you.

    Wednesday, February 11, 2009

    Tom Kyte's DBA Resolutions for 2009

    Tom Kyte, Oracle DBA and founder of the Ask Tom blog, gave the Oracle Database Insider his resolutions for 2009 in hopes that other DBAs will follow suit. "In the coming year," says Kyte, "I resolve to...

    o Practice a restore at least once a month under different scenarios. DBAs are allowed to make mistakes—with one exception: They cannot make mistakes in recovery. We can fix any other mistake easily, but not recovery mistakes.

    o Learn something new. When was the last time you read the "what's new in" chapters in the documentation? Take a couple of minutes to see what's new.

    o Participate in user group discussion forums. If I just do the same thing day in, day out, never experiencing what others are doing, I'll stagnate. The easiest way to stay current, to network, and to learn something new is to participate.

    o Become a mentor for someone more junior. I'll get two things out of this: First, I will learn a ton of new stuff because they will ask me things I don't fully know. Second, I'll have put in place the next generation of DBAs so that I can move up or move over in the future. Training your replacement is one sure way to be able to progress yourself.

    o Be more thoughtful, don’t jump to conclusions, take my time. As the old saying goes, haste makes waste. And that is never more true than when doing something that will take hours or longer to accomplish, as many DBA tasks may take. I will study the options and pick the one that is best suited for the problem at hand—realizing that the approach I take in 2009 might be very different from the one I took in 2008, 2000, 1995, or any other time.