Rebuild one partition of an index

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.


Let me note, my table`s size approximate 27 GB. Not big, not little :)

Partitioned in_date column, and has 2 partitioned indexes, in_date and subscriber_id.

Checked when table`s statistics was taken and are those indexes valid. Yesterday gathered statistic.


Now, let`s rebuild partition index.

SQL> alter index MY_ARCH_TEST_DB_IDX2 rebuild partition P2008_201010 online;

Remove old trace files or unnecessary files

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  {} \;

DDL trigger

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          VARCHAR2(30 BYTE),
  OSUSER             VARCHAR2(30 BYTE),
  DDL_TEXT           CLOB,
  DDL_DATE           TIMESTAMP(6) WITH LOCAL TIME ZONE
)

Now, create trigger:

CREATE OR REPLACE TRIGGER DDL_WATCH_AFTER
AFTER DDL ON DATABASE
BEGIN
IF ora_dict_obj_type IN ('TABLE','INDEX'/*an other object types*/) THEN /*You may exclude specific db users*/

INSERT INTO DDL_WATCH_LOG (ACTION, ORA_EVENT, ORACLE_LOGIN_USER, ORA_DATABASE_NAME, ORA_OBJECT_TYPE, ORA_OBJECT_NAME, ORADICT_OBJ_OWNER, ORA_SESSIONUSER, ORA_HOSTNAME, ORA_IPADDRESS, OSUSER, OSUSER, DDL_TEXT, DDL_DATE)
VALUES
('AFTER', ora_sysevent, ora_login_user, ora_database_name, ora_dict_obj_type, ora_dict_obj_name, sys_context('userenv','SESSION_USER'), sys_context('userenv','HOST'),
sys_context('userenv','IP_ADDRESS'), sys_context('userenv','OS_USER'),Dbms_Metadata.GET_DDL(ora_dict_obj_type,ora_dict_obj_name,ora_dict_obj_owner), SYSDATE);
END IF;
END;
/



Has another method, example using audit, but this trigger will behavior on development DBs where several database users changes existing db objects, create/remove etc.