Home » Article » Script to check index on unusable state

Script to check index on unusable state

Pages

October 2011
M T W T F S S
« Sep   Nov »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

RSS Link

  • An error has occurred; the feed is probably down. Try again later.

Twitter Updates

Blog Stats

  • 345,582 hits

Archives

Pengunjung


Seperti posting gw terdahulu disini (https://ardhian.wordpress.com/2011/10/19/oracle-ora-01502-on-sap-r3-4-7-ent-oracle-11g/), ternyata problem ini muncul pada saat upgrade Oracle dan beberapa index yang belum pernah gw rebuild ataupun rebuild compress pun mengalami hal yang sama. Berdasarkan data diatas, gw coba search di internet dan mendapatkan script sql kecil untuk mendapatkan informasi index mana saja yang mengalami state seperti ini.

Berikut script kecil itu :

select index_name, status, owner from
dba_indexes where STATUS = 'UNUSABLE';

Contoh yang gw dapat untuk eksekusi ini adalah.

SQL> select index_name, status, owner from
dba_indexes where STATUS = ‘UNUSABLE’;  2

INDEX_NAME                     STATUS   OWNER
—————————— ——– ——————————
ACCTIT~Z01                     UNUSABLE SAPWPR
S514~VAB                       UNUSABLE SAPWPR
TCJ_POSITIONS~0                UNUSABLE SAPWPR
S055~VAB                       UNUSABLE SAPWPR
S055~0                         UNUSABLE SAPWPR
GLPCA~7                        UNUSABLE SAPWPR
GLPCA~3                        UNUSABLE SAPWPR
GLPCA~2                        UNUSABLE SAPWPR
FMEP~W                         UNUSABLE SAPWPR
FMEP~V                         UNUSABLE SAPWPR
FMEP~R                         UNUSABLE SAPWPR

INDEX_NAME                     STATUS   OWNER
—————————— ——– ——————————
FMEP~M                         UNUSABLE SAPWPR
FMEP~K                         UNUSABLE SAPWPR
ACCTIT~1                       UNUSABLE SAPWPR
ACCTIT~0                       UNUSABLE SAPWPR

15 rows selected.

Solusi nya adalah melakukan rebuild index. Bisa rebuild biasa ataupun rebuild compress. Script solusi akan gw share ke posting berikutnya setelah gw uji coba.


1 Comment

  1. SAP OS DB MIGRATION ONLINE TRAINING complete Support for a Migration of your

    Operating System and Database Description ,If at any stage of the SAP software

    life cycle you should need to change your operating system and/or database

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

%d bloggers like this: