Monday, July 18, 2011

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;

Friday, July 8, 2011

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

Thursday, July 7, 2011

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.