Ulfat`s DataBase Blog

Today one of the our developer asked me that, one query works very slowly. After investigation I have found out plan of the query is incorrect and query use substr function which incorrect place. While using select I`ve received ORA-01502. What is the this error? ORA-01502: index 'string.string' or partition of such index is in unusable state Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition     I am going to check my partition indexes which is unusable state. SQL> select * from DBA_IND_PARTITIONS where index_name = 'MY_ARCH_TEST_DB_IDX2' and status <> 'USABLE' Ups! Found. P2008_201010 partition is unusable state. Checked how many rows has in this partition: SQL> select count(1) from my_arch_test partition (P2008_201010) 126339601 rows...

As OS administrator, DBA should be check filesystem, clean up unnecessary and old trace files. Below command will delete files older than 7 days. find *.trc -mtime +7 -type f -local -exec rm  {} \;

Sometimes someone creates/drop/modify db object(s). However it depends requester. To prevent this dislike events I suggest create DDL trigger. This trigger will fire every DDL action. First of all we should determine which kind of DDL we have to store. So, create table: CREATE TABLE SYS.DDL_WATCH_LOG (   ACTION             CHAR(6 BYTE),   ORA_EVENT       VARCHAR2(20 BYTE),   ORACLE_LOGIN_USER      VARCHAR2(30 BYTE),   ORA_DATABASE_NAME   VARCHAR2(50 BYTE),   ORA_OBJECT_TYPE   VARCHAR2(20 BYTE),   ORA_OBJECT_NAME   VARCHAR2(30 BYTE),   ORA_OBJECT_OWNER  VARCHAR2(30 BYTE),   ORA_SESSIONUSER        VARCHAR2(30 BYTE),   ORA_HOSTNAME           VARCHAR2(60 BYTE),   ORA_IPADDRESS   ...