Oracle DB: Routine DB Health Management Tasks

Basic Steps

Gathering Statistics :

a)Table Stats(statistics)

Sql> exec dbms_stats.gather_table_stats(‘Schema’,’Tname’);

Sql> select table_name,to_char(last_analyzed,’hh24:mi:ss dd-mon-yy’) from dba_tables where table_name=’Tname’;

b)Schema Stats

Sql> exec dbms_stats.gather_schema_stats(‘Schema’);

c)Database Stats

Sql> exec dbms_stats.gather_database_stats();

d)Dictionary Stats

Sql>exec dbms_stats.gather_dictionary_stats();

e)Index Stats

Sql>exec dbms_stats.gather_index_stats(‘Schema’,’IND_Name’);

Deleting Stats :

 a)Table Stats

 Sql>exec dbms_stats.delete_table_stats(‘Schema’,’Tname’);

 b) Schema Stats

 Sql>exec dbms_stats.delete_schema_stats(‘Schema’);

 c) Database Stats

 Sql>exec dbms_stats.delete_database_stats();

Defragmentation :

Table :

Sql> alter table table_name shrink space compact;

Index:

Making index invisible/visible

SQL>select index_name,visibility from user_indexes where index_name=’IND_EMP’;

SQL>alter index ind_emp invisible;

SQL>alter index ind_emp visible;

Analyze First

Sql>analyze index index_name validate structure;

sql>select height,name from index_stats where name='IND_NAME';

Rebuild/Coalesce:

Sql> alter index index_name rebuild online;

Sql> alter index index_name coalesce;

Pin /Unpin Database objects :

If dbms_shared_pool package is not  there then run the .sql script

Sql> @$ORACLE_HOME/rdbms/admin/dbmspool.sql

Pinning  object

Sql> exec dbms_shared_pool.keep(‘DBMS_SPACE_ADMIN’);

Sql> select * from v$db_object_cache where kept=’YES’;

Unpining  object

Sql> exec dbms_shared_pool.unkeep(‘DBMS_SPACE_ADMIN’);

Shrinking temporary tablespace

SQL>alter tablespace temp shrink space keep 100m;

Checking and Compiling DB Invalid Objects (Packages,Procedures )

Compiling DB invalid objects

Sql> select count(*) from dba_objects where status=’INVALID’;

Compiling Packages

Sql > alter package pack_name compile;

Sql> alter view view_name compile;

Indexes:

B*Tree index

sql>create index indx_name on table_name (col_name);

Unique index

sql>create unique index ind_name on table_name (col_name);

Composite index

sql>create index ind_name on table_name (col_name,col_name);

Bitmap index

sql>create bitmap index ind_name on table_name (gender);

Functional index

sql>create index ind_name on table_name(upper(col_name));

Reverse Key index

sql>create index ind_name on table_name(col_name) reverse;

IOT (Index Organized Table)

sql>create table table_name (col_name datatype primary key,col_name datatype)

 organization index;