Posted by: baskardba | July 9, 2010

What i learned new today..

Hi,

Little flashback on the situation. A query was running for couple of hours and sqltuning advisory showed creating a index will improve the performance of the query.

Tried creating the index, since it has taken lot of time the session was killed. Trying to drop the index it said..

 SQL> drop index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1;
drop index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1
                      *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

But..still that sid was active..

SQL> @sid
Enter value for sid: 1243
old   3: where sid = ‘&sid’
new   3: where sid = ‘1243’

       SID    SNO OSUSER     USERNAME        STATUS   PROCESS      PADDR            SPID         MODULE
———- —— ———- ————— ——– ———— —————- ———— —————————————-
      1243  44678 astlprd    APPS            ACTIVE   14131        000000057B93DE68 2240         JSWUS_MATL_RECEIPT_ACCOUNT_RPT

1 row selected.

so i did

SQL> @kill
Enter value for sid: 1243
Enter value for sno: 44678
old   1: alter system kill session ‘&sid,&sno’
new   1: alter system kill session ‘1243,44678’

System altered.

and tried to drop but it said

SQL> drop index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1;
drop index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1
                      *
ERROR at line 1:
ORA-08104: this index object 1892315 is being online built or rebuilt

After this i cleaned up the failed session

SQL> conn /as sysdba
Connected.
SQL> DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 1892315;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);
COMMIT;
END;
/  2    3    4    5    6    7    8    9   10   11

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

then that object doesnt exist

SQL> @obj
Enter value for 1: JSWUS_MATL_RECEIVING_TEMP_N1
old   3: where object_name like upper(‘%&1%’)
new   3: where object_name like upper(‘%JSWUS_MATL_RECEIVING_TEMP_N1%’)

no rows selected

and then created the indexes

SQL> create index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1 on JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP(“RECEIPT_NUM”,”ORGANIZATION_ID”,”TRANSACTION_TYPE”);

Index created.

SQL> exec fnd_stats.gather_index_stats(‘JSW_CUSTOM’,’JSWUS_MATL_RECEIVING_TEMP_N1′);

PL/SQL procedure successfully completed.

happy learning,

baskar.l

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: