Reorganizing a Table using DBMS_REDEFINITION

Posted at Thursday, July 03, 2008

There are many methods to reorganize/defragment a table in Oracle. If you want to avoid downtime, however, your choices are limited to using the 'SHRINK SPACE' feature and/or employing the DBMS_REDEFINITION PL/SQL package.

This post demonstrates the latter method. The DBMS_REDEFINITION package is most commonly used for redefining a table, however can be used to reorganize a table amongst other things. Here is a great article which covers the package in a little more detail: On-line Table Reorganization and Redefinition.

This example rebuilds the OE.ORDER_ITEMS table using DBMS_REDEFINITION.

Collect information about the table's segments prior to the reorg

ANALYZE TABLE oe.order_items COMPUTE STATISTICS;

SELECT avg_row_len, num_rows, chain_cnt

  FROM dba_tables

 WHERE table_name = 'ORDER_ITEMS' AND owner = 'OE';

SELECT ROUND (BYTES / 1024) kb

  FROM dba_segments

 WHERE owner = 'OE' AND segment_name = 'ORDER_ITEMS';

Check whether the table in question violates any redefinition restrictions

EXEC dbms_redefinition.can_redef_table('OE','ORDER_ITEMS', dbms_redefinition.cons_use_pk);

Create the Intermediary Table - in my case, I use the exact same structure

CREATE TABLE OE.ORDER_ITEMS_REDEF

(

  ORDER_ID      NUMBER(12),

  LINE_ITEM_ID  NUMBER(3)                       NOT NULL,

  PRODUCT_ID    NUMBER(6)                       NOT NULL,

  UNIT_PRICE    NUMBER(8,2),

  QUANTITY      NUMBER(8)

) TABLESPACE USERS;

Start the Redefinition Process

EXEC dbms_redefinition.start_redef_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

You will notice that Oracle creates a Materialized View in order to store the table changes, etc.

SELECT COUNT (*)

  FROM oe.mlog$_order_items;

Sync up the Source table with the Intermediary table

EXEC dbms_redefinition.sync_interim_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

Duplicate the table's dependent objects on the Intermediary table

CREATE INDEX oe.item_order_redef_ix ON oe.order_items_redef

(order_id) LOGGING TABLESPACE users;

CREATE INDEX oe.item_product_redef_ix ON oe.order_items_redef

(product_id) LOGGING TABLESPACE users;

CREATE UNIQUE INDEX oe.order_items_redef_pk ON oe.order_items_redef

(order_id, line_item_id) LOGGING TABLESPACE users;

CREATE UNIQUE INDEX oe.order_items_redef_uk ON oe.order_items_redef

(order_id, product_id) LOGGING TABLESPACE users;

ALTER TABLE oe.order_items_redef ADD (

  CONSTRAINT order_items_redef_pk

 PRIMARY KEY (order_id, line_item_id) USING INDEX TABLESPACE USERS);

ALTER TABLE oe.order_items_redef ADD (

  CONSTRAINT order_items_redef_order_id_fk

 FOREIGN KEY (order_id)

 REFERENCES oe.orders (order_id)

 ON DELETE CASCADE DISABLE NOVALIDATE);

ALTER TABLE oe.order_items_redef ADD (

  CONSTRAINT order_items_rdef_product_id_fk

 FOREIGN KEY (product_id)

 REFERENCES oe.product_information (product_id) DISABLE NOVALIDATE);

GRANT SELECT ON oe.order_items_redef TO bi;

GRANT SELECT ON oe.order_items_redef TO pm;

Notice how, I create the foreign key constraints initially as disabled.

To simplify things, I use the following to duplicate any triggers to the Intermediary table

DECLARE

 retval NUMBER(5);

BEGIN

 dbms_redefinition.copy_table_dependents('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF', copy_indexes=>0, copy_triggers=>TRUE, copy_constraints=>FALSE,copy_privileges=>FALSE, num_errors=>retval);

 dbms_output.put_line(retval);

END;

/

Disable any foreign key constraints on the Source table before finishing the table redefinition process

ALTER TABLE oe.order_items DISABLE CONSTRAINT order_items_order_id_fk;



ALTER TABLE oe.order_items DISABLE CONSTRAINT order_items_product_id_fk;

Finish the Redefinition

EXEC dbms_redefinition.finish_redef_table('OE', 'ORDER_ITEMS', 'ORDER_ITEMS_REDEF');

Drop the Intermediary Table

DROP TABLE oe.order_items_redef;

Rename any dependent objects

ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_rdef_product_id_fk TO order_items_product_id_fk;

ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_redef_order_id_fk TO order_items_order_id_fk;

ALTER TABLE oe.order_items RENAME CONSTRAINT order_items_redef_pk TO order_items_pk;

ALTER INDEX oe.item_order_redef_ix RENAME TO item_order_ix;

ALTER INDEX oe.item_product_redef_ix RENAME TO item_product_ix;

ALTER INDEX oe.order_items_redef_pk RENAME TO order_items_pk;

ALTER INDEX oe.order_items_redef_uk RENAME TO order_items_uk;

At this point, the table should be reorganized/defragmented. Optionally, compare the storage footprint post-redefintion

ANALYZE TABLE oe.order_items COMPUTE STATISTICS;

SELECT avg_row_len, num_rows, chain_cnt

  FROM dba_tables

 WHERE table_name = 'ORDER_ITEMS' AND owner = 'OE';

SELECT ROUND (BYTES / 1024) kb

  FROM dba_segments

 WHERE owner = 'OE' AND segment_name = 'ORDER_ITEMS';

Use this pl/sql script to determine which indexes need defragmentation.

Use this pl/sql script to determine which indexes need defragmentation.

create or replace procedure system.my_indfrag (schema_in  in varchar2)

as

        cursor ind_cur(schema varchar2) is

        (select owner, index_name from dba_indexes where owner=

        upper(schema));

        owner varchar2(40);

        ind_name varchar2(30);

        cursor badness_cur is

        (select name, del_lf_rows/(lf_rows+0.00001) badness, height

        from system.ind_temp_table

        where del_lf_rows/(lf_rows+0.00001) > .15 or height > 5 );

        bad_row badness_cur%rowtype;

begin

        /* 

         * Assumes that you have created a table system.ind_temp_table of 

the same

         * structure as index_stats. 

         */

        dbms_output.enable(1000000);

        delete from system.ind_temp_table;

        open ind_cur(schema_in);

        fetch ind_cur into owner, ind_name; 

        while ind_cur%found

        loop

               begin <>

                       execute immediate('analyze index ' || owner  || 

'.' || 

                                              '"' || ind_name || '"' || 

' validate structure');

                       insert into system.ind_temp_table

                       (select * from index_stats);

                       commit;

               exception

                       when others then

                               dbms_output.put_line(ind_name || ':' || 

chr(09) || sqlerrm);

                               rollback;

               end analyze_block;

               fetch ind_cur into owner, ind_name;

        end loop;

        close ind_cur;

        open badness_cur;

        fetch badness_cur into bad_row;

        if badness_cur%found then

               dbms_output.put_line(rpad('Index Name',30) || chr(09) || 

'% Del. Entries' || chr(09) || 'Height');

        else

               dbms_output.put_line('No indexes in this schema need 

rebuilding.');

        end if;

        while badness_cur%found

        loop

               dbms_output.put_line(rpad(bad_row.name,30) || chr(09) 

                       || rpad(to_char(round(bad_row.badness*100,0)) || 

'%',14) || chr(09) ||

                       bad_row.height);

               fetch badness_cur into bad_row;

        end loop;

        close badness_cur;

end;

Sample output

ASP>  exec my_indfrag('HR_DATA');

chairs_index1:  ORA-01418: specified index does not exist

ASP>  exec system.my_indfrag('HR_DATA');

Index Name                      % Del. Entries  Height

IDX_DISC_PERSON_APPT_VERIFIED   50%             2

IDX_DISC_PERSON_APPT_VERIFIED   58%           1

IDX_LEAVE_END_RPT               63%           2

IDX_LEAVE_START_RPT             60%           3

IDX_LEAVES_NAME_KEY             65%           1

TC_WORKGRPS_PK                  48%           1

TC_SYSTEM_ACCRUALS_PK           44%           2

TC_WORKGROUP_SETTINGS_PK        10%           6

PL/SQL procedure successfully completed.

How to check blocking locks

What's blocking my lock?

If you've ever gotten a phone call from an annoyed user whose transaction just won't go through, or from a developer who can't understand why her application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked. Even better, you can identify the exact row that a session is waiting to lock.

Create a blocking lock

To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table:

SQL> create table tstlock (foo varchar2(1), bar varchar2(1));

Table created.

SQL> insert into tstlock values (1,'a'); 

1 row created.

SQL> insert into tstlock values (2, 'b');

1 row created.

SQL> select * from tstlock ;

FOO BAR

--- ---

1   a

2   b

2 rows selected.

SQL> commit ;

Commit complete.

Now grab a lock on the whole table, still in Session 1:
SQL> select * from tstlock for update ;
And in Session 2, try to update a row:

SQL> update tstlock set bar=

  2  'a' where bar='a' ;

This statement will hang, blocked by the lock that Session 1 is holding on the entire table.

Identify the blocking session

Oracle provides a view, DBA_BLOCKERS, which lists the SIDs of all blocking sessions. But this view is often, in my experience, a good bit slower than simply querying V$LOCK, and it doesn't offer any information beyond the SIDs of any sessions that are blocking other sessions. The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information.

SQL> select * from v$lock ;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0

ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0

ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0

ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

....     ....            ... ...      ....       ....       ....       ....        ....      ....

Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.
In the query above, we can see that SID 422 is blocking SID 479. SID 422 corresponds to Session 1 in our example, and SID 479 is our blocked Session 2.
To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:

SQL> select l1.sid, ' IS BLOCKING ', l2.sid

  2  from v$lock l1, v$lock l2

  3  where l1.block =1 and l2.request > 0

  4  and l1.id1=l2.id1

  5  and l1.id2=l2.id2

SQL> /

       SID 'ISBLOCKING'         SID

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

       422  IS BLOCKING         479

1 row selected.

Even better, if we throw a little v$session into the mix, the results are highly readable:

SQL> select s1.username || '@' || s1.machine

  2  || ' ( SID=' || s1.sid || ' )  is blocking '

  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

  4  from v$lock l1, v$session s1, v$lock l2, v$session s2

  5  where s1.sid=l1.sid and s2.sid=l2.sid

  6  and l1.BLOCK=1 and l2.request > 0

  7  and l1.id1 = l2.id1

  8  and l2.id2 = l2.id2 ;

BLOCKING_STATUS

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

BULKLOAD@yttrium ( SID=422 )  is blocking BULKLOAD@yttrium ( SID=479 )

1 row selected.

There's still more information in the v$lock table, but in order to read that information, we need to understand a bit more about lock types and the cryptically-named ID1 and ID2 columns.

Lock type and the ID1 / ID2 columns

In this case, we already know that the blocking lock is an exclusive DML lock, since we're the ones who issued the locking statement. But most of the time, you won't be so lucky. Fortunately, you can read this information from the v$lock table with little effort.
The first place to look is the TYPE column. There are dozens of lock types, but the vast majority are system types. System locks are normally only held for a very brief amount of time, and it's not generally helpful to try to tune your library cache, undo logs, etc. by looking in v$lock! (See the V$LOCK chapter in the Oracle Database Reference for a list of system lock types.)
There are only three types of user locks, TX, TM and UL. UL is a user-defined lock -- a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.
The TM lock is a DML lock. It's acquired once for each object that's being changed. The ID1 column identifies the object being modified.

Lock Modes

You can see more information on TM and TX locks just by looking at the lock modes. The LMODE and REQUEST columns both use the same numbering for lock modes, in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock in order to change data; LMODE will be 6. If it can't obtain an exclusive lock because some of the rows it wants to change are locked by another session, then it will request a TX in exclusive mode; LMODE will be 0 since it does not have the lock, and REQUEST will be 6. You can see this interaction in the rows we selected earlier from v$lock:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0

ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.
You may also see TX locks in mode 4, Shared mode. If a block containing rows to be changed doesn't have any interested transaction list (ITL) entries left, then the session acquires a TX lock in mode 4 while waiting for an ITL entry. If you see contention for TX-4 locks on an object, you probably need to increase INITRANS for the object.
TM locks are generally requested and acquired in modes 3, aka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock. (Note that CREATE TABLE doesn't require a TM lock -- it doesn't need to lock any objects, because the object in question doesn't exist yet!) DML requires a Shared-Row Exclusive lock. So, in the rows we selected earlier from v$lock, you can see from the TM locking levels that these are DML locks:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

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

ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0

ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0

Identifying the locked object

Now that we know that each TM row points to a locked object, we can use ID1 to identify the object.

SQL> select object_name from dba_objects where object_id=88519 ;

OBJECT_NAME

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

TSTLOCK

Sometimes just knowing the object is enough information; but we can dig even deeper. We can identify not just the object, but the block and even the row in the block that Session 2 is waiting on.

Identifying the locked row

We can get this information from v$session by looking at the v$session entry for the blocked session:

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#

  2* from v$session where sid=479 ;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#

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

        88519             16          171309             0

This gives us the object ID, the relative file number, the block in the datafile, and the row in the block that the session is waiting on. If that list of data sounds familiar, it's because those are the four components of an extended ROWID. We can build the row's actual extended ROWID from these components using the DBMS_ROWID package. The ROWID_CREATE function takes these arguments and returns the ROWID:

SQL> select do.object_name,

  2  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,

  3  dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )

  4  from v$session s, dba_objects do

  5  where sid=543

  6  and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C

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

TSTLOCK                 88519             16          171309             0 AAAVnHAAQAAAp0tAAA

And, of course, this lets us inspect the row directly.

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR

--- ---

1   a

Conclusion

We've seen how to identify a blocking session, and how to inspect the very row that the waiting session is waiting for. And, I hope, learned a bit about v$lock in the process.

How Oracle Locking Works

When a transaction updates a row, it puts a lock so that no one can update the same row until it commits. When another transaction issues an update to the same row, it waits until the first one either commits or rolls back. After the first transaction performs a commit or rollback, the update by the second transaction is executed immediately, since the lock placed by the first transaction is now gone. How exactly does this locking mechanism work? Several questions come to mind in this context:

1. Is there some kind of logical or physical structure called lock?
2. How does the second transaction know when the first transaction has lifted the lock?
3. Is there some kind of “pool” of such locks where transactions line up to get one?
4. If so, do they line up to return it when they are done with the locking?
5. Is there a maximum number of possible locks?
6. Is there something called a block level lock? Since Oracle stores the rows in blocks, when all or the majority of rows in the blocks are locked by a single transaction, doesn’t it make sense for to lock the entire block to conserve the number of locks?
7. The previous question brings up another question – does the number of active locks in the database at any point really matter?

If you are interested to learn about all this, please read on.

Lock Manager

Since locks convey information on who has what rows modified but not committed, anyone interested in making the update much check with some sort of system that is available across the entire database. So, it makes perfect sense to have a central locking system in the database, doesn’t it? But, when you think about it, a central lock manager can quickly become a single point of contention in a busy system where a lot of updates occur. Also, when a large number of rows are updated in a single transaction, an equally large number of locks will be required as well. The question is: how many? One can guess; but it will be at best a wild one. What if you guessed on the low side and the supply of available locks is depleted? In that case some transactions can’t get locks and therefore will have to wait (or, worse, abort). Not a pleasant thought in a system that needs to be scalable. To counter such a travesty you may want to make the available supply of locks really high. What is the downside of that action? Since each lock would potentially consume some memory, and memory is finite, it would not be advisable to create an infinite supply of locks.

Some databases actually have a lock manager with a finite supply of such locks. Each transaction must ask to get a lock from it before beginning and relinquish locks to it at the completion. In those technologies, the scalability of application suffers immensely as a result of the lock manager being the point of contention. In addition, since the supply of locks is limited, the developers need to commit frequently to release the locks for other transactions. When a large number of rows have locks on them, the database replaces the row locks with a block level lock to cover all the rows in the block – a concept known as lock escalation. Oracle does not follow that approach. In Oracle, there no central lock manager, no finite limit on locks and there is no such concept called lock escalation. The developers commit only when there is a logical need to do so; not otherwise.

Lock Management in Oracle

So, how is that approach different in case of Oracle? For starters, there is no central lock manager. But the information on locking has to be recorded somewhere. Where then? Well, consider this: when a row is locked, it must be available to the session, which means the session’s server process must have already accessed and placed the block in the buffer cache prior to the transaction occurring. Therefore, what is a better place for putting this information than right there in the block (actually the buffer in the buffer cache) itself?

Oracle does precisely that – it records the information in the block. When a row is locked by a transaction, that nugget of information is placed in the header of the block where the row is located. When another transaction wishes to acquire the lock on the same row, it has to access the block containing the row anyway (as you learned in Part 1 of this series) and upon reaching the block, it can easily confirm that the row is locked from the block header. A transaction looking to update a row in a different block puts that information on the header of that block. There is no need to queue behind some single central resource like a lock manager. Since lock information is spread over multiple blocks instead of a single place, this mechanism makes transactions immensely scalable.

Being the smart reader you are, you are now hopefully excited to learn more or perhaps you are skeptical. You want to know the nuts and bolts of this whole mechanism and, more, you want proof. We will see all that in a moment.

Transaction Address

Before understanding the locks, you should understand clearly what a transaction is and how it is addressed. A transaction starts when an update to data such as insert, update or delete occurs (or the intention to do so, e.g. SELECT FOR UPDATE) and ends when the session issues a commit or rollback. Like everything else, a specific transaction should have a name or an identifier to differentiate it from another one of the same type. Each transaction is given a transaction ID. When a transaction updates a row (it could also insert a new row or delete an existing one; but we will cover that little later in this article), it records two things:

• The new value
• The old value

The old value is recorded in the undo segments while the new value is immediately updated in the buffer where the row is stored. The data buffer containing the row is updated regardless of whether the transaction is committed or not. Yes, let me repeat – the data buffer is updated as soon as the transaction modifies the row (before commit). If you didn’t know that, please see the Part 1 of this series.

Undo information is recorded in a circular fashion. When new undo is created, it is stored in the next available undo “slot”. Each transaction occupies a record in the slot. After all the slots are exhausted and a new transaction arrives, the next processing depends on the state of the transactions. If the oldest transaction occupying any of the other slots is no longer active (that is either committed or rolled back), Oracle will reuse that slot. If none of the transactions is inactive, Oracle will have to expand the undo tablespace to make room. In the former case (where a transaction is no longer active and its information in undo has been erased by a new transaction), if a long running query that started before the transaction occurred selects the value, it will get an ORA-1555 error. But that will be covered in a different article in the future. If the tablespace containing the undo segment can’t extend due to some reason (such as in case of the filesystem being completely full), the transaction will fail.

Speaking of transaction identifiers, it is in the form of three numbers separated by periods. These three numbers are:

• Undo Segment Number where the transaction records its undo entry
• Slot# in the undo segment
• Sequence# (or wrap) in the undo slot

This is sort of like the social security number of the transaction. This information is recorded in the block header. Let’s see the proof now through a demo.

Demo

First, create a table:

SQL> create table itltest (col1 number, col2 char(8));

Insert some rows into the table.

SQL> begin
  2     for i in 1..10000 loop 
  3             insert into itltest values (i,'x');
  4     end loop;
  5     commit;
  6  end;
  7  /

Remember, this is a single transaction. It started at the “BEGIN” line and ended at “COMMIT”. The 10,000 rows were all inserted as a part of the same transaction. To know the transaction ID of this transaction, Oracle provides a special package - dbms_transaction. Here is how you use it. Remember, you must use it in the same transaction. Let’s see:

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
------------------------------------------------------------------------


1 row selected.

Wait? There is nothing. The transaction ID returned is null. How come?

If you followed the previous section closely, you will realize that the transaction ends when a commit or rollback is issued. The commit was issued inside the PL/SQL block. So, the transaction had ended before you called the dbms_transaction is package. Since there was no transaction, the package returned null.

Let’s see another demo. Update one row:

SQL> update itltest set col2 = 'y' where col1 = 1;

1 row updated.

In the same session, check the transaction ID:

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
-------------------------------------------------------------------------
3.23.40484

1 row selected.

There you see – the transaction ID. The three numbers separated by period signify undo segment number, slot# and record# respectively. Now perform a commit:

SQL> commit;

Commit complete.

Check the transaction ID again:

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
-------------------------------------------------------------------------


1 row selected.

The transaction is gone so the ID is null, as expected.

Since the call to the package must be in the same transaction (and therefore in the same session), how can you check the transaction in a different session? In real life you will be asked to check transaction in other sessions, typically application sessions. Let’s do a slightly different test. Update the row one more time and check the transaction:

SQL> update itltest set col2 = 'y' where col1 = 1;

1 row updated.

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
-----------------------------------------------------------------------
10.25.31749

1 row selected.

From a different session, check for active transactions. This information is available in the view V$TRANSACTION. There are several columns; but we will look at four of the most important ones:

• ADDR – the address of the transaction, which is a raw value
• XIDUSN – the undo segment number
• XIDSLOT – the slot#
• XIDSQN – the sequence# or record# inside the slot

SQL> select addr, xidusn, xidslot, xidsqn
  2  from v$transaction;

ADDR         XIDUSN    XIDSLOT     XIDSQN
-------- ---------- ---------- ----------
3F063C48         10         25      31749

Voila! You see the transaction id of the active transaction from a different session. Compare the above output to the one you got from the call to dbms_transaction package. You can see that the transaction identifier shows the same set of numbers.

Interested Transaction List

You must be eager to know about the section of the block header that contains information on locking and how it records it. It is a simple data structure called "Interested Transaction List" (ITL), a list that maintains information on transaction. The ITL contains several placeholders (or slots) for transactions. When a row in the block is locked for the first time, the transaction places a lock in one of the slots. In other words, the transaction makes it known that it is interested in some rows (hence the term "Interested Transaction List"). When a different transaction locks another set of rows in the same block, that information is stored in another slot and so on. When a transaction ends after a commit or a rollback, the locks are released and the slot which was used to mark the row locks in the block is now considered free (although it is not updated immediately - fact about which you will learn later in a different installment).

[Updated Jan 22, 2011] [Thank you, Randolph Geist (info@www.sqltools-plusplus.org) for pointing it out. I follow his blog http://oracle-randolf.blogspot.com/, which is a treasure trove of information.
The row also stores a bit that represents the whether it is locked or not.
[end of Update Jan 22, 2011]


ITLs in Action

Let's see how ITLs really work. Here is an empty block. The block header is the only occupant of the block.

This is how the block looks like after a single row has been inserted:

Note, the row was inserted from the bottom of the block. Now, a second row has been inserted:

A session comes in and updates the row Record1, i.e. it places a lock on the row, shown by the star symbol. The lock information is recorded in the ITL slot in the block header:

The session does not commit yet; so the lock is active. Now a second session - Session 2 - comes in and updates row Record2. It puts a lock on the record - as stored in the ITL slot.

I have used two different colors to show the locks (as shown by the star symbol) and the color of the ITL entry.

As you can clearly see, when a transaction wants to update a specific row, it doesn’t have to go anywhere but the block header itself to know if the row is locked or not. All it has to do is to check the ITL slots. However ITL alone does not show with 100% accuracy that row is locked (again, something I will explain in a different installment). The transaction must go to the undo segment to check if the transaction has been committed. How does it know which specifci part of the undo segment to go to? Well, it has the information in the ITL entry. If the row is indeed locked, the transaction must wait and retry. As soon as the previous transaction ends, the undo information is updated and the waiting transaction completes its operation.

So, there is in fact a queue for the locks, but it's at the block level, not at the level of the entire database or even the segment.

Demo

The proof is in the pudding. Let’s see all this through a demo. Now that you know the transaction entry, let’s see how it is stored in the block header. To do that, first, we need to know which blocks to look for. So, we should get the blocks numbers where the table is stored:

SQL> select file_id, relative_fno, extent_id, block_id, blocks
  2  from dba_extents
  3  where segment_name = 'ITLTEST';

   FILE_ID RELATIVE_FNO  EXTENT_ID   BLOCK_ID     BLOCKS
---------- ------------ ---------- ---------- ----------
         7            7          0       3576          8
         7            7          1       3968          8
         7            7          2       3976          8
         7            7          3       3984          8

To check inside the block, we need to “dump” the contents of the block to a tracefile so that we can read it. From a different session issue a checkpoint so that the buffer data is now written to the dis:

SQL> alter system checkpoint;

Now dump the data blocks 3576 through 3583.

SQL> alter system dump datafile 7 block min 3576 block max 3583;

System altered.

This will create a tracefile in the user dump destination directory. In case of Oracle 11g, the tracefile will be in the diag structure under /diag/rdbms///trace directory. It will be most likely the last tracefile generated. You can also get the precise name by getting the OS process ID of the session:

SQL> select p.spid
  2  from v$session s, v$process p
  3  where s.sid = (select sid from v$mystat where rownum < 2)
  4  and p.addr = s.paddr
  5  /

SPID
------------------------
9202

1 row selected.

Now look for a file named _ora_9202.trc. Open the file in vi and search for the phrase “Itl”. Here is an excerpt from the file:

Itl           Xid                  Uba          Flag  Lck        Scn/Fsc
0x01   0x000a.019.00007c05  0x00c00288.1607.0e  ----    1  fsc 0x0000.00000000
0x02   0x0003.017.00009e24  0x00c00862.190a.0f  C---    0  scn 0x0000.02234e2b

This is where the information on row locking is stored. Remember, the row locking information is known as Interested Transaction List (ITL) and each ITL is stored in a “slot”. Here it shows two slots, which is the default number. Look for the one where the “Lck” column shows a value. It shows “1”, meaning one of the rows in the blocks is locked by a transaction. But, which transaction? To get that answer, note the value under the “Xid” column. It shows the transaction ID - 0x000a.019.00007c05. These numbers are in hexadecimal (as indicated by the 0x at the beginning of the number). Using the scientific calculator in Windows, I converted the values to decimal as 10, 25 and 31749 respectively. Do they sound familiar? Of course they do; they are exactly as reported by both the record in v$transaction and the dbms_transaction.local_transaction_id function call.

This is how Oracle determines that there is a transaction has locked the row and correlates it to the various components in the other areas – mostly the undo segments to determne if it is active. Now that you know undo segments holds the transaction details, you may want to know more about the segment. Remember, the undo segment is just a segment, like any other table, indexes, etc. It resides in a tablespace, which is on some datafile. To find out the specifics of the segment, we will look into some more columns of the view V$TRANSACTION:

SQL> select addr, xidusn, xidslot, xidsqn, ubafil, ubablk, ubasqn, ubarec,
  2  status, start_time, start_scnb, start_scnw, ses_addr
  3  from v$transaction;

ADDR         XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN
-------- ---------- ---------- ---------- ---------- ---------- ----------
    UBAREC STATUS           START_TIME           START_SCNB START_SCNW SES_ADDR
---------- ---------------- -------------------- ---------- ---------- --------
3F063C48         10         25      31749          3        648       5639
        14 ACTIVE           12/30/10 20:00:25      35868240          0 40A73784


1 row selected.

The columns with names starting with UBA show the undo block address information. Look at the above output. The UBAFIL shows the file#, which is “3” in this case. Checking for the file_id: 

SQL> select * from dba_data_files
  2> where file_id = 3;

FILE_NAME
-------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
+DATA/d112d2/datafile/undotbs1.260.722742813
         3 UNDOTBS1                       4037017600     492800 AVAILABLE
           3 YES 3.4360E+10    4194302          640 4035969024      492672
ONLINE


1 row selected.

Note the UBASQN (which is the undo block sequence#) value in the earlier output, which was 5639. Let’s revisit the ITL entries in the dump of block:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.019.00007c05  0x00c00288.1607.0e  ----    1  fsc 0x0000.00000000
0x02   0x0003.017.00009e24  0x00c00862.190a.0f  C---    0  scn 0x0000.02234e2b

Look at the entry under the Uba column: 0x00c00288.1607.0e. As indicated by the “0x” at the beginning, these are in hexadecimal. Using a scientific calculator, let’s convert them. 1607 in hex means 5639 in decimal – the UBA Sequence# (UBASQN). The value “e” is 14 in decimal, which corresponds to the UBAREC. Finally the value 288 is 648 in decimal, which is the UBABLK. Now you see how the information is recorded in the block header and is also available to the DBA through the view V$TRANSACTION. 

Let’s see some more important columns of the view. A typical database will have many sessions; not just one. Each session may have an active transaction, which means you have to link sessions to transactions to generate meaningful information. The transaction information also contains the session link. Note the column SES_ADDR, which is the address of the session that issued the transaction. From that, you can get the session information

SQL> select sid, username
  2  from v$session
  3  where saddr = '40A73784';

SID USERNAME
--- --------
123 ARUP

There you go – you now have the SID of the session. And now that you know the SID, you can look up any other relevant data on the session from the view V$SESSION.

Takeaways

Here is a summary of what you learned so far:

1. Transaction in Oracle starts with a data update (or intention to update) statement. Actually there are some exceptions which we will cover in a later article.
2. It ends when a commit or rollback is issued
3. A transaction is identified by a transaction ID (XID) which is a set of three numbers – undo segment#, undo slot# and undo record# - separated by periods.
4. You can view the transaction ID in the session itself by calling dbms_transaction.local_transaction_id function.
5. You can also check all the active transactions in the view v$transaction, where the columns XIDUSN, XIDSLOT and XIDSQN denote the undo segment#, undo slot# and undo rec# - the values that make up the transaction ID.
6. The transaction information is also stored in the block header. You can check it by dumping the block and looking for the term “Itl”.
7. The v$transaction view also contains the session address under SES_ADDR column, which can be used to join with the SADDR column of v$session view to get the session details.
8. From the session details, you can find out other actions by the session such as the username, the SQL issues, the machine issued from, etc.

How to set-up a crontab file

Howto set-up a crontab file

In Linux, Cron is a daemon/service that executes shell commands periodically on a given schedule. Cron is driven by a crontab, a configuration file that holds details of what commands are to be run along with a timetable of when to run them.

Creating a crontab file

You can create a crontab file by entering the following terminal command:

crontab -e

Entering the above command will open a terminal editor with a new blank crontab file, or it will open an existing crontab if you already have one. You can now enter the commands to be executed, see syntax below, before saving the file and exiting the editor. As long as your entries were entered correctly your commands should now be executed at the times/dates you specified. You can see a list of active crontab entries by entering the following terminal command:

crontab -l

Crontab syntax

A crontab file has six fields for specifying minute, hour, day of month, month, day of week and the command to be run at that interval. See below:

*     *     *     *     *  command to be executed

-     -     -     -     -

|     |     |     |     |

|     |     |     |     +----- day of week (0 - 6) (Sunday=0)

|     |     |     +------- month (1 - 12)

|     |     +--------- day of month (1 - 31)

|     +----------- hour (0 - 23)

+------------- min (0 - 59)

Crontab examples

Writing a crontab file can be a somewhat confusing for first time users, therefore I have listed below some crontab examples:

* * * * * <command> #Runs every minute

30 * * * * <command> #Runs at 30 minutes past the hour

45 6 * * * <command> #Runs at 6:45 am every day

45 18 * * * <command> #Runs at 6:45 pm every day

00 1 * * 0 <command> #Runs at 1:00 am every Sunday

00 1 * * 7 <command> #Runs at 1:00 am every Sunday

00 1 * * Sun <command> #Runs at 1:00 am every Sunday

30 8 1 * * <command> #Runs at 8:30 am on the first day of every month

00 0-23/2 02 07 * <command> #Runs every other hour on the 2nd of July

As well as the above there are also special strings that can be used:

@reboot <command> #Runs at boot

@yearly <command> #Runs once a year [0 0 1 1 *]

@annually <command> #Runs once a year [0 0 1 1 *]

@monthly <command> #Runs once a month [0 0 1 * *]

@weekly <command> #Runs once a week [0 0 * * 0]

@daily <command> #Runs once a day [0 0 * * *]

@midnight <command> #Runs once a day [0 0 * * *]

@hourly <command> #Runs once an hour [0 * * * *]

Multiple commands

A double-ampersand “&&” can be used to run multiple commands consecutively. The following example would run command_01 and then command_02 once a day:

@daily <command_01> && <command_02>

Disabling email notifications

By default a cron job will send an email to the user account executing the cronjob. If this is not needed put the following command at the end of the cron job line:

>/dev/null 2>&1

Specifying a crontab file to use

As mentioned at the top of this post, you can create a new crontab file with the “crontab -e” command. However, you may already have a crontab file, if you do you can set it to be used with the following command:

crontab -u <username> <crontab file>

Therefore the following command…

crontab -u tux ~/crontab

…would set Tux’s crontab file to that of the file named “crontab” residing in Tux’s home directory.

Removing a crontab file

To remove your crontab file simply enter the following terminal command:

crontab -r

Further information

Refer to the man page for further information about crontab. Enter the terminal command:

man crontab

External links

Some external links for your browsing pleasure:

• Cron article on Wikipedia
• Ubuntu Documentation - CronHowto
• Debian Help - Scheduling Backup Jobs using at and crontab in debian
• Matt Cutts - How to fetch a url with curl or wget silently
• Sitepoint - Getting PHP and Cron to Work Together

I think that pretty much covers the subject of cron jobs and crontab. Please feel free to comment if I have missed anything or made any obvious mistakes.

Oracle R12 Application File System

Hi,

Few days back I have posted about the 3-tiers 1)Destop 2)Application 3)Database,
I'm continuing with R12 application filesystem in this post.


II)Applications File System– Overview

The Top-Level R12-Applications Directory Structure is divided into 3 parts:
1)Database Server files
2)Instant specific files
3)Application tier Server


1)Database Server files(db):
• The db/apps_st/data directory is located on the database node machine, and contains the system tablespaces, redo log files, data tablespaces, index tablespaces,and database files
• The db/tech_st/10.2.0 directory is located on the database node machine, and contains the ORACLE_HOME for the Oracle10g database
• The apps/apps_st/appl (APPL_TOP) directory contains the product directories and
files for Oracle Applications.
• The apps/apps_st/comn (COMMON_TOP) directory contains Java classes, HTML
pages, and other files and directories used by multiple products
• The apps/tech_st/10.1.2 directory contains the ORACLE_HOME used for the
Applications technology stack tools components.
• The apps/tech_st/10.1.3 directory contains the ORACLE_HOME used for the
Applications technology stack Java components.

2)Instant specific files(inst):
Instance Home (INST_TOP):

*Oracle Applications Release 12 introduces the concept of a top-level directory for an Applications instance. This directory is referred to as the Instance Home, and denoted by the environment variable $INST_TOP.
*Using an Instance Home provides the ability to share Applications and technology.

Notable features of this architecture include:

• The latest version of Oracle Containers for Java (OC4J), the successor to JServ, is
included in Oracle Application Server 10.1.3.
• All major services are started out of the OracleAS 10.1.3 ORACLE_HOME.
• The Applications modules (packaged in the file formsapp.ear) are deployed into the
OC4J-Forms instance running out of the OracleAS 10.1.3 ORACLE_HOME, while the frmweb executable is invoked out of the OracleAS 10.1.2 ORACLE_HOME.
*stack code among multiple instances, for example a development instance and a test instance.
*Support for read-only file systems and centralization of log files.

3)Application tier Server files(apps):
ORACLE_HOMEs:
There are 3 ORACLE_HOMEs in the Architecture of R12:
One ORACLE Database 10g rel2( Oracle 10.2.0 Home) &
Two ORACLE Application Server(OracleAS Homes)

*Use of Two Oracle Application Server ORACLE_HOMEs in Release 12
Two different Oracle Application Server (OracleAS) 10g releases, in separate ORACLE_HOMEs, are used in Oracle Applications Release 12.
*This enables Oracle Applications to take advantage of the latest Oracle technologies.
• The Oracle Application Server 10.1.3 ORACLE_HOME (sometimes referred to as
the Web or Java ORACLE_HOME) replaces the 8.1.7-based ORACLE_HOME
provided by Oracle9i Application Server 1.0.2.2.2 in Release 11i).

IMP QUERIES ORACLE APPS DBA

2)How to find the release of Apps installed or version installed in our machine?
Ans:conn apps
Enter password:
Connected.

SQL> select release_name from fnd_product_groups;

RELEASE_NAME
--------------------------------------------------
12.1.1

3)What is Yellow Bar Warning in Apps?

Ans: Oracle Applications Release 11.5.1 (11i) requires that its code run in a trusted mode and uses J-Initiator to run Java applets on a desktop client. If an applet is “trusted,” however, Java will extend the privileges of the applet.The Yellow Warning Bar is a warning that your applet is not running in a trusted mode.To indicate that an applet is trusted, it must be digitally signed using a digital Certificate,so Oracle Applications requires that all Java archive files must be digitally signed.

4)How to check the custom top installled?

Ans:

SQL> Select BASEPATH,PRODUCT_CODE,APPLICATION_SHORT_NAME
From fnd_application
Where application_Short_name like '%CUST_TOP_name%';

5)How to check multi-org is enabled in Oracle applications?

Ans:

SQL> select multi_org_flag from fnd_product_groups;

M
-
Y
Note:For enabling multi-org check the MY ORACLE SUPPORT notes 396351.1 and 220601.1

6)How to compile invalid objects in Oracle Applications?

Ans: Check the below link for all possible ways to compile the invalid objects in Oracle Application.Usually 'adadmin' utility provides us the option to do this task.

http://onlineappsdba.blogspot.com/2008/05/how-to-compile-invalid-objects-in-apps.html


7)Can we install Apps Tier and Database Tier on different Operating system while installing Oracle EBS 11i/R12?
Ans: Yes it is possible.We can do this by following below MY ORACLE SUPPORT notes:

Oracle Apps 11i --> Using Oracle EBS with a Split Configuration Database Tier on 11gR2 [ID 946413.1]

Oracle Apps R12 --> Oracle EBS R12 with Database Tier Only Platform on Oracle Database 11.2.0 [ID 456347.1]


8)How to find the node details in Oracle Applications?
Ans: FND_NODES tables in 'apps' schema helps in finding node details after installation,clonning and migration of applications.
SQL> SELECT NODE_NAME||' '||STATUS ||' '||NODE_ID||' '||HOST
FROM FND_NODES;

9)How to see the products installed and their versions in Oracle Applications?
Ans:

SQL> SELECT APPLICATION_ID||''||ORACLE_ID||''||PRODUCT_VERSION||''||STATUS||''||PATCH_LEVEL
FROM FND_PRODUCT_INSTALLATIONS;

O/P looks like below:

172 172 12.0.0 I R12.CCT.B.1
191 191 12.0.0 I R12.BIS.B.1
602 602 12.0.0 I R12.XLA.B.1
805 805 12.0.0 I R12.BEN.B.1
8302 800 12.0.0 I R12.PQH.B.1
8303 800 12.0.0 I R12.PQP.B.1
809 809 12.0.0 I 11i.HXC.C
662 662 12.0.0 I R12.RLM.B.1
663 663 12.0.0 I R12.VEA.B.1
298 298 12.0.0 N R12.POM.B.1
185 185 12.0.0 I R12.XTR.B.1

10)How to see the concurrent Requests and jobs in Oracle Applications?
Ans: FND_CONCURRENT_REQUESTS can be used to see the concurrent requests and job details.These details are useful
in troubleshooting concurrent manager related issues.

SQL>SELECT REQUEST_ID||' '||REQUEST_DATE||' '||REQUESTED_BY||' '||PHASE_CODE||' '||STATUS_CODE
FROM FND_CONCURRENT_REQUESTS;

O/P will be as given below:
REQUEST_ID||''||REQUEST_DATE||''||REQUESTED_BY||''||PHASE_CODE||''||STATUS_CODE
--------------------------------------------------------------------------------------------------------
6088454 24-NOV-11 1318 P I
6088455 24-NOV-11 1318 P Q
6088403 24-NOV-11 0 C C
6088410 24-NOV-11 0 C C


Where:

PHASE_CODE column can have values:
C Completed
I Inactive
P Pending
R Running

STATUS_CODE Column can have values:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting


11)What is the significance of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables?
Ans: FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables are created and Dropped during the 'adadmin' and 'adpatch' sessions.
Both AD utilities (adpatch/adadmin) access the same tables to store the workers details, so both FND_INSTALL_PROCESSES and
AD_DEFERRED_JOBS tables need to be dropped from the failed adpatch session ,so that adadmin/adpatch session can run successfully next time.

 

12) To find sessions list?

ANS:

clear col

set lines 180

set pages 90

REM 2007-10-31 S.E. Added machine column.

REM 2003-08-19 S.E. Added column statements for 9i sqlplus

column osuser     format a14

column username   format a15

column sid        format 999999

column serial#    format 999999

column pid        format 999999

column spid       format a6

column logon_time format a14

column status     format a8

column machine    format a30

column program    format a54

spool sessionlist.txt

select s.osuser osuser,

       s.username username,

       s.sid sid,

       s.serial# serial#,

       p.pid pid,

       p.spid spid,

       substr(to_char(s.logon_time,'mm-dd hh24:mi:ss'),1,14) logon_time,

       s.status status,

       s.machine machine,

       s.program program

  from v$session s,

       v$process p

  where s.paddr = p.addr(+)

  order by status, osuser, username, logon_time asc

/

spool off;

13) How to check SID is active or not?

Ans :

CLEAR COLUMNS

column sid                                format 9999

column serial                             format 99999

column osuser   HEADING 'O/S|Username'    FORMAT a9

column username HEADING 'Oracle|Username' FORMAT a9

column program                            FORMAT a20

undefine SID

select sid, serial#, osuser, username, status, logon_time, program

  from v$session

 where sid like '&SID'

CLEAR COLUMNS

/

14) How to check session information with the help of SID?

clear col

set lines 180

set pages 90

REM 2007-10-31 S.E. Added machine column.

REM 2003-08-19 S.E. Added column statements for 9i sqlplus

column osuser     format a14

column username   format a15

column sid        format 999999

column serial#    format 999999

column pid        format 999999

column spid       format a6

column logon_time format a14

column status     format a8

column machine    format a30

column program    format a54

spool sessionlist.txt

select s.osuser osuser,

       s.username username,

       s.sid sid,

       s.serial# serial#,

       p.pid pid,

       p.spid spid,

       substr(to_char(s.logon_time,'mm-dd hh24:mi:ss'),1,14) logon_time,

       s.status status,

       s.machine machine,

       s.program program

  from v$session s,

       v$process p

  where s.paddr = p.addr(+)

  order by status, osuser, username, logon_time asc

/

spool off;

prdora@gs2f09dbebz1lv:/PRD02/prdora1/habeeb> clear

You have new mail in /var/mail/prdora

prdora@gs2f09dbebz1lv:/PRD02/prdora1/habeeb> cat sid.sql

CLEAR COLUMNS

column sid                                format 9999

column serial                             format 99999

column osuser   HEADING 'O/S|Username'    FORMAT a9

column username HEADING 'Oracle|Username' FORMAT a9

column program                            FORMAT a20

undefine SID

select sid, serial#, osuser, username, status, logon_time, program

  from v$session

 where sid like '&SID'

CLEAR COLUMNS

/

prdora@gs2f09dbebz1lv:/PRD02/prdora1/habeeb> cat sessiondet.sql

Display all 150 possibilities? (y or n)

prdora@gs2f09dbebz1lv:/PRD02/prdora1/habeeb> cat sessiondet.sql

set lines  95

set pages  900

set head   off

set feedb  off

set echo   off

set verify off

PROMPT

ACCEPT p_sid CHAR PROMPT 'Please enter a sid number: '

spool sessdettemp.sql

select 'spool /tmp/session_detail.&&p_sid..'||to_char(sysdate,'DD-MON-YYYY-HH24MI')||'.log' from dual;

spool off

@sessdettemp.sql

PROMPT

PROMPT V$SESSION INFORMATION

PROMPT

select

 rpad('SADDR'                ,22,'.')||' '||rpad(SADDR                ,20,' ')||CHR(10)||

 rpad('SID'                  ,22,'.')||' '||rpad(SID                  ,20,' ')||CHR(10)||

 rpad('SERIAL#'              ,22,'.')||' '||rpad(SERIAL#              ,20,' ')||CHR(10)||

 rpad('AUDSID'               ,22,'.')||' '||rpad(AUDSID               ,20,' ')||CHR(10)||

 rpad('PADDR'                ,22,'.')||' '||rpad(PADDR                ,20,' ')||CHR(10)||

 rpad('USER#'                ,22,'.')||' '||rpad(USER#                ,20,' ')||CHR(10)||

 rpad('USERNAME'             ,22,'.')||' '||rpad(USERNAME             ,30,' ')||CHR(10)||

 rpad('COMMAND'              ,22,'.')||' '||rpad(COMMAND              ,20,' ')||CHR(10)||

 rpad('TADDR'                ,22,'.')||' '||rpad(TADDR                ,10,' ')||CHR(10)||

 rpad('LOCKWAIT'             ,22,'.')||' '||rpad(LOCKWAIT             ,10,' ')||CHR(10)||

 rpad('STATUS'               ,22,'.')||' '||rpad(STATUS               ,10,' ')||CHR(10)||

 rpad('SERVER'               ,22,'.')||' '||rpad(SERVER               ,10,' ')||CHR(10)||

 rpad('SCHEMA#'              ,22,'.')||' '||rpad(SCHEMA#              ,20,' ')||CHR(10)||

 rpad('SCHEMANAME'           ,22,'.')||' '||rpad(SCHEMANAME           ,30,' ')||CHR(10)||

 rpad('OSUSER'               ,22,'.')||' '||rpad(OSUSER               ,20,' ')||CHR(10)||

 rpad('PROCESS'              ,22,'.')||' '||rpad(PROCESS              ,10,' ')||CHR(10)||

 rpad('MACHINE'              ,22,'.')||' '||rpad(MACHINE              ,70,' ')||CHR(10)||

 rpad('TERMINAL'             ,22,'.')||' '||rpad(TERMINAL             ,10,' ')||CHR(10)||

 rpad('PROGRAM'              ,22,'.')||' '||rpad(PROGRAM              ,50,' ')||CHR(10)||

 rpad('TYPE'                 ,22,'.')||' '||rpad(TYPE                 ,10,' ')||CHR(10)||

 rpad('SQL_ADDRESS'          ,22,'.')||' '||rpad(SQL_ADDRESS          ,20,' ')||CHR(10)||

 rpad('SQL_HASH_VALUE'       ,22,'.')||' '||rpad(SQL_HASH_VALUE       ,20,' ')||CHR(10)||

 rpad('PREV_SQL_ADDR'        ,22,'.')||' '||rpad(PREV_SQL_ADDR        ,20,' ')||CHR(10)||

 rpad('PREV_HASH_VALUE'      ,22,'.')||' '||rpad(PREV_HASH_VALUE      ,20,' ')||CHR(10)||

 rpad('MODULE'               ,22,'.')||' '||rpad(MODULE               ,50,' ')||CHR(10)||

 rpad('MODULE_HASH'          ,22,'.')||' '||rpad(MODULE_HASH          ,20,' ')||CHR(10)||

 rpad('ACTION'               ,22,'.')||' '||rpad(ACTION               ,40,' ')||CHR(10)||

 rpad('ACTION_HASH'          ,22,'.')||' '||rpad(ACTION_HASH          ,20,' ')||CHR(10)||

 rpad('CLIENT_INFO'          ,22,'.')||' '||rpad(CLIENT_INFO          ,70,' ')||CHR(10)||

 rpad('FIXED_TABLE_SEQUENCE' ,22,'.')||' '||rpad(FIXED_TABLE_SEQUENCE ,20,' ')||CHR(10)||

 rpad('ROW_WAIT_OBJ#'        ,22,'.')||' '||rpad(ROW_WAIT_OBJ#        ,20,' ')||CHR(10)||

 rpad('ROW_WAIT_FILE#'       ,22,'.')||' '||rpad(ROW_WAIT_FILE#       ,20,' ')||CHR(10)||

 rpad('ROW_WAIT_BLOCK#'      ,22,'.')||' '||rpad(ROW_WAIT_BLOCK#      ,20,' ')||CHR(10)||

 rpad('ROW_WAIT_ROW#'        ,22,'.')||' '||rpad(ROW_WAIT_ROW#        ,20,' ')||CHR(10)||

 rpad('LOGON_TIME'           ,22,'.')||' '||rpad(to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS'),20,' ')||CHR(10)||

 rpad('LAST_CALL_ET'         ,22,'.')||' '||rpad(LAST_CALL_ET         ,20,' ')

 from gv$session

where sid = &&p_sid;

PROMPT

PROMPT V$PROCESS INFORMATION

PROMPT

select

 rpad('ADDR'                 ,22,'.')||' '||rpad(p.ADDR               ,20,' ')||CHR(10)||

 rpad('PID'                  ,22,'.')||' '||rpad(p.PID                ,20,' ')||CHR(10)||

 rpad('SPID'                 ,22,'.')||' '||rpad(p.SPID               ,10,' ')||CHR(10)||

 rpad('USERNAME'             ,22,'.')||' '||rpad(p.USERNAME           ,20,' ')||CHR(10)||

 rpad('SERIAL#'              ,22,'.')||' '||rpad(p.SERIAL#            ,20,' ')||CHR(10)||

 rpad('TERMINAL'             ,22,'.')||' '||rpad(p.TERMINAL           ,10,' ')||CHR(10)||

 rpad('PROGRAM'              ,22,'.')||' '||rpad(p.PROGRAM            ,50,' ')||CHR(10)||

 rpad('BACKGROUND'           ,22,'.')||' '||rpad(p.BACKGROUND         ,10,' ')||CHR(10)||

 rpad('LATCHWAIT'            ,22,'.')||' '||rpad(p.LATCHWAIT          ,10,' ')||CHR(10)||

 rpad('LATCHSPIN'            ,22,'.')||' '||rpad(p.LATCHSPIN          ,10,' ')

 from gv$session s,

      gv$process p

where s.sid = &&p_sid

  and s.paddr = p.addr;

PROMPT

PROMPT V$SESS_IO INFORMATION

PROMPT

select

 rpad('SID'                  ,22,'.')||' '||rpad(SID                  ,30,' ')||CHR(10)||

 rpad('BLOCK_GETS'           ,22,'.')||' '||rpad(BLOCK_GETS           ,30,' ')||CHR(10)||

 rpad('CONSISTENT_GETS'      ,22,'.')||' '||rpad(CONSISTENT_GETS      ,30,' ')||CHR(10)||

 rpad('PHYSICAL_READS'       ,22,'.')||' '||rpad(PHYSICAL_READS       ,30,' ')||CHR(10)||

 rpad('BLOCK_CHANGES'        ,22,'.')||' '||rpad(BLOCK_CHANGES        ,30,' ')||CHR(10)||

 rpad('CONSISTENT_CHANGES'   ,22,'.')||' '||rpad(CONSISTENT_CHANGES   ,30,' ')

 from gv$sess_io

where sid = &&p_sid;

PROMPT

PROMPT V$SESSION_EVENT INFORMATION

PROMPT

select

 rpad('SID'                  ,22,'.')||' '||rpad(SID                  ,30,' ')||CHR(10)||

 rpad('EVENT'                ,22,'.')||' '||rpad(EVENT                ,30,' ')||CHR(10)||

 rpad('TOTAL_WAITS'          ,22,'.')||' '||rpad(TOTAL_WAITS          ,30,' ')||CHR(10)||

 rpad('TOTAL_TIMEOUTS'       ,22,'.')||' '||rpad(TOTAL_TIMEOUTS       ,30,' ')||CHR(10)||

 rpad('TIME_WAITED'          ,22,'.')||' '||rpad(TIME_WAITED          ,30,' ')||CHR(10)||

 rpad('AVERAGE_WAIT'         ,22,'.')||' '||rpad(round(AVERAGE_WAIT,4),30,' ')

 from gv$session_event

where sid = &&p_sid;

PROMPT

PROMPT V$SESSION_WAIT INFORMATION

PROMPT

select

 rpad('SID'                  ,22,'.')||' '||rpad(SID                  ,30,' ')||CHR(10)||

 rpad('SEQ#'                 ,22,'.')||' '||rpad(SEQ#                 ,30,' ')||CHR(10)||

 rpad('EVENT'                ,22,'.')||' '||rpad(EVENT                ,30,' ')||CHR(10)||

 rpad('WAIT_TIME'            ,22,'.')||' '||rpad(WAIT_TIME            ,30,' ')||CHR(10)||

 rpad('SECONDS_IN_WAIT'      ,22,'.')||' '||rpad(SECONDS_IN_WAIT      ,30,' ')||CHR(10)||

 rpad('STATE'                ,22,'.')||' '||rpad(STATE                ,30,' ')

 from gv$session_wait

where sid = &&p_sid

order by seq#;

PROMPT

PROMPT V$SQLAREA INFORMATION

PROMPT [Displays previous sql if no current sql.]

PROMPT

select

 rpad('SQL_TEXT'             ,22,'.')||' '||CHR(10)||CHR(10)||a.SQL_TEXT

 from gv$session s,

      gv$sqlarea a

where s.sid = &&p_sid

  and decode(sql_address,'00', prev_sql_addr, sql_address) = a.address;

--  and decode(sql_address,'00', prev_hash_value, sql_hash_value) = a.hash_value;

select

 rpad('SHARABLE_MEM'         ,22,'.')||' '||rpad(a.SHARABLE_MEM       ,30,' ')||CHR(10)||

 rpad('PERSISTENT_MEM'       ,22,'.')||' '||rpad(a.PERSISTENT_MEM     ,30,' ')||CHR(10)||

 rpad('RUNTIME_MEM'          ,22,'.')||' '||rpad(a.RUNTIME_MEM        ,30,' ')||CHR(10)||

 rpad('SORTS'                ,22,'.')||' '||rpad(a.SORTS              ,30,' ')||CHR(10)||

 rpad('VERSION_COUNT'        ,22,'.')||' '||rpad(a.VERSION_COUNT      ,30,' ')||CHR(10)||

 rpad('LOADED_VERSIONS'      ,22,'.')||' '||rpad(a.LOADED_VERSIONS    ,30,' ')||CHR(10)||

 rpad('OPEN_VERSIONS'        ,22,'.')||' '||rpad(a.OPEN_VERSIONS      ,30,' ')||CHR(10)||

 rpad('USERS_OPENING'        ,22,'.')||' '||rpad(a.USERS_OPENING      ,30,' ')||CHR(10)||

 rpad('EXECUTIONS'           ,22,'.')||' '||rpad(a.EXECUTIONS         ,30,' ')||CHR(10)||

 rpad('USERS_EXECUTING'      ,22,'.')||' '||rpad(a.USERS_EXECUTING    ,30,' ')||CHR(10)||

 rpad('LOADS'                ,22,'.')||' '||rpad(a.LOADS              ,30,' ')||CHR(10)||

 rpad('FIRST_LOAD_TIME'      ,22,'.')||' '||rpad(a.FIRST_LOAD_TIME    ,30,' ')||CHR(10)||

 rpad('INVALIDATIONS'        ,22,'.')||' '||rpad(a.INVALIDATIONS      ,30,' ')||CHR(10)||

 rpad('PARSE_CALLS'          ,22,'.')||' '||rpad(a.PARSE_CALLS        ,30,' ')||CHR(10)||

 rpad('DISK_READS'           ,22,'.')||' '||rpad(a.DISK_READS         ,30,' ')||CHR(10)||

 rpad('BUFFER_GETS'          ,22,'.')||' '||rpad(a.BUFFER_GETS        ,30,' ')||CHR(10)||

 rpad('ROWS_PROCESSED'       ,22,'.')||' '||rpad(a.ROWS_PROCESSED     ,30,' ')||CHR(10)||

 rpad('COMMAND_TYPE'         ,22,'.')||' '||rpad(a.COMMAND_TYPE       ,30,' ')||CHR(10)||

 rpad('OPTIMIZER_MODE'       ,22,'.')||' '||rpad(a.OPTIMIZER_MODE     ,30,' ')||CHR(10)||

 rpad('PARSING_USER_ID'      ,22,'.')||' '||rpad(a.PARSING_USER_ID    ,30,' ')||CHR(10)||

 rpad('PARSING_SCHEMA_ID'    ,22,'.')||' '||rpad(a.PARSING_SCHEMA_ID  ,30,' ')||CHR(10)||

 rpad('KEPT_VERSIONS'        ,22,'.')||' '||rpad(a.KEPT_VERSIONS      ,30,' ')||CHR(10)||

 rpad('ADDRESS'              ,22,'.')||' '||rpad(a.ADDRESS            ,30,' ')||CHR(10)||

 rpad('HASH_VALUE'           ,22,'.')||' '||rpad(a.HASH_VALUE         ,30,' ')||CHR(10)||

 rpad('MODULE'               ,22,'.')||' '||rpad(a.MODULE             ,60,' ')||CHR(10)||

 rpad('MODULE_HASH'          ,22,'.')||' '||rpad(a.MODULE_HASH        ,30,' ')||CHR(10)||

 rpad('ACTION'               ,22,'.')||' '||rpad(a.ACTION             ,30,' ')||CHR(10)||

 rpad('ACTION_HASH'          ,22,'.')||' '||rpad(a.ACTION_HASH        ,30,' ')||CHR(10)||

 rpad('SERIALIZABLE_ABORTS'  ,22,'.')||' '||rpad(a.SERIALIZABLE_ABORTS,30,' ')

 from gv$session s,

      gv$sqlarea a

where s.sid = &&p_sid

  and decode(sql_address,'00', prev_sql_addr, sql_address) = a.address;

--  and decode(sql_address,'00',prev_hash_value, sql_hash_value) = a.hash_value;

spool off

clear buffer

!rm sessdettemp.sql

15) How to check Locks?

ANS:

CLEAR COLUMNS

col ssid    form A7

col lmode   form 999 heading "loc"

col request form 999 heading "req"

col name    form A30

break on id1 on sid

select lpad(' ',decode(a.request,0,0,3))||a.sid ssid,

       a.id1,

       a.lmode,

       a.request,

       c.name

  from sys.obj$  c,

       gv$lock    b,

       gv$lock    a

 where a.id1 in ( select id1 from gv$lock where lmode = 0 )

   and a.sid  = b.sid

   and c.obj# = b.id1

   and b.type = 'TM'

 order by a.id1, a.request, b.sid, c.name

/

CLEAR COLUMNS

16) How to check Blocking Session?

ANS:

select blocking_session, sid from v$session where blocking_session !=0;

17) How to check Tablespace Size?

ANS:

SET PAGESIZE 60

SET FEEDBACK OFF

COLUMN tablespace_name HEADING 'Tablespace' FORMAT a20

COLUMN total HEADING 'Total|MB' FORMAT 99,99,999

COLUMN available HEADING 'Available|MB' FORMAT 99,99,999

COLUMN used HEADING 'Used|MB' FORMAT 99,99,999

COLUMN pctused HEADING 'Percent|Used'  FORMAT 999.99

COLUMN large_extent HEADING 'Largest|Available|Extent' FORMAT 9,999

BREAK ON REPORT

COMPUTE SUM OF USED TOTAL ON REPORT

col today new_value v_date noprint

col db_name new_value db_sid noprint

select to_char(SYSDATE,'Month DD, YYYY') today from dual;

select name db_name from v$database ;

TTITLE LEFT db_sid ' - Daily Tablespace Usage Report' RIGHT v_date SKIP 2

select a.tablespace_name, b.total,

        c.available, d.large_extent,

        (b.total - c.available) used,

        round((((b.total - c.available)*100)/b.total),2) pctused

from

 (select tablespace_name, (sum(bytes)/1024)/1024 total

        from dba_data_files group by tablespace_name) b,

 (select tablespace_name,  nvl(round(((sum(bytes)/1024)/1024),2),0) available

        from dba_free_space group by tablespace_name) c,

 (select tablespace_name, (max(bytes)/1024)/1024 large_extent

        from dba_free_space group by tablespace_name) d,

 dba_tablespaces a

where a.tablespace_name = b.tablespace_name (+)

and   a.tablespace_name = c.tablespace_name (+)

and   a.tablespace_name = d.tablespace_name (+)

order by pctused

/

undefine today

undefine db_name

clear col

TTITLE OFF

18) How to check current concurrent program running from backend?

ANS:

set head on

set feed on

set pages 56

set lines 200

set recsep off

col "rqst_id"        heading "Reqst Id"      format 999999999

col "pgm_appl_id"    heading "Appl Id"       format 999999

col "conc_pgm_id"    heading "Prg Id"        format 999999

col "stat_cd"        heading "St"            format a2

col "os_pid"         heading "CMgr|OSPID"    format a5

col "vspid"          heading "SPID"          format a5

col "vsid"           heading "SID"           format 99999

col "vserial#"       heading "SER#"          format 99999

col "pri"            heading "Pri"           format 99

col "que"            heading "Mgr"           format a3

col "time"           heading "Run|Time"      format 99999

col "avgtime"        heading "Avg|Run|Time"  format 99999

col "walltime"       heading "Wall|Time"     format 99999

col "usr"            heading "User|Name"     format a18

col "program"        heading "Program"       format a90

select a.request_id                                                             "rqst_id"

      ,a.program_application_id                                                 "pgm_appl_id"

      ,a.concurrent_program_id                                                  "conc_pgm_id"

      ,a.status_code||decode(a.resubmit_interval, null, null, '*')              "stat_cd"

      ,b.os_process_id                                                          "os_pid"

      ,v.spid                                                                   "vspid"

      ,v.sid                                                                    "vsid"

      ,v.serial#                                                                "vserial#"

      ,a.priority                                                               "pri"

      ,substr(d.concurrent_queue_name,1,3)                                      "que"

      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440             "time"

      ,decode(sign(a.requested_start_date - a.request_date),1,

              round(((sysdate-requested_start_date) * 1440),0),

              round(((sysdate-request_date) * 1440),0))                         "walltime"

      ,e.user_name                                                              "usr"

      ,c.concurrent_program_name||' - '||rtrim(ct.user_concurrent_program_name) "program"

  from APPLSYS.fnd_concurrent_requests     a

      ,APPLSYS.fnd_concurrent_processes    b

      ,APPLSYS.fnd_concurrent_programs     c

      ,APPLSYS.fnd_concurrent_programs_tl  ct

      ,APPLSYS.fnd_concurrent_queues       d

      ,APPLSYS.fnd_user                    e

      ,(select s.sid, s.serial#, p.spid from v$session s, v$process p where s.paddr = p.addr) v

 where a.controlling_manager = b.concurrent_process_id

   and a.concurrent_program_id = c.concurrent_program_id

   and a.program_application_id = c.application_id

   and a.concurrent_program_id = ct.concurrent_program_id

   and a.program_application_id = ct.application_id

   and b.concurrent_queue_id = d.concurrent_queue_id

   and ct.language = 'US'

   and a.requested_by = e.user_id

   and a.phase_code in ('R','T')

   and a.oracle_process_id=v.spid(+)

 order by 11 asc

/

clear col

20) How to find SID with the help of  OS PID????

ANS:

COLUMN OSUSER HEADING 'OS|Username' FORMAT a9

COLUMN USERNAME HEADING 'Oracle|Username' FORMAT a8

COLUMN SID FORMAT 9999

COLUMN SERIAL# FORMAT 999999

COLUMN PROGRAM FORMAT a32

SET FEEDBACK OFF

SET VERIFY OFF

select a.sid, a.serial#, a.osuser, a.username, a.status, a.logon_time, a.program

from v$session a, v$process b

where a.paddr = b.addr

and b.spid like '&os_process'

/

SET FEEDBACK ON

21) How to Re-Bulid Index??

ANS:

prompt

ACCEPT spoolfile CHAR prompt 'Output-file : ';

ACCEPT schema CHAR prompt 'Schema name (% allowed) : ';

prompt

prompt

prompt Rebuild the index when :

prompt   - deleted entries represent 20% or more of the current entries

prompt   - the index depth is more then 4 levels.

prompt Possible candidate for bitmap index :

prompt   - when distinctiveness is more than 99%

prompt

spool &spoolfile

set serveroutput on

set verify off

declare

 c_name        INTEGER;

 ignore        INTEGER;

 height        index_stats.height%TYPE := 0;

 lf_rows       index_stats.lf_rows%TYPE := 0;

 del_lf_rows   index_stats.del_lf_rows%TYPE := 0;

 distinct_keys index_stats.distinct_keys%TYPE := 0;

 cursor c_indx is

  select owner, table_name, index_name

  from dba_indexes

  where owner like upper('&schema')

    and owner not in ('SYS','SYSTEM');

begin

 dbms_output.enable (1000000);

dbms_output.put_line ('Owner           Index Name                              Table Name                         % Deleted Entries        Blevel Distinctiveness');

 dbms_output.put_line ('--------------- --------------------------------------- ---------------------------------- ----------------------- ----------------------');

 c_name := DBMS_SQL.OPEN_CURSOR;

 for r_indx in c_indx loop

  DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||

                 r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);

  ignore := DBMS_SQL.EXECUTE(c_name);

  select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,

         decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)

         into height, lf_rows, del_lf_rows, distinct_keys

  from index_stats;

--

-- Index is considered as candidate for rebuild when :

--   - when deleted entries represent 20% or more of the current entries

--   - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)

-- Index is (possible) candidate for a bitmap index when :

--   - distinctiveness is more than 99%

--

  if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then

    dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||

                                rpad(r_indx.table_name,35,' ') ||

                          lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||

                          lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));

  end if;

 end loop;

 DBMS_SQL.CLOSE_CURSOR(c_name);

end;

/

spool off

set verify on

22) To check Temp tablespace Free Blocks?

ANS :

SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

23) How to check OBJECT LEVEL LOCK?

ANS:

select a.INST_ID, a.OBJECT_ID, a.SESSION_ID,b.sid, b.serial#, b.status from gv$locked_object a, v$session b where object_id like '41411' and b.status='INVALID' and a.session_id=b.sid;