Rebuilding Indexes

when to rebuild index

the index may be rebuild in following situations

1)If the existing index is to moved to different tablespace.
2) If index contains many deleted entries.
3) If the table of index has been moved to another tablespace.

in case of rebuilding an index

1) A new index is built using an existing index as data source.
2)the older index is deleted after the new index is built.
3)queries can continue to use the existing index while the new index is being built.

example:

ALTER INDEX mytable_idx REBILD
TABLESPACE newtb;

monitoring index usage

From Oracle 9i ,statistic about the usage of an index can be gathered and displayed in V$OBJECT_USAGE . If the information shows that the index is never used , then that index can be dropped.

To start the index monitoring

ALTER INDEX mytable_idx
MONITORING USAGE

To stop the index monitoring

ALTER INDEX mytable_idx
NOMONITORING USAGE


SQL> select * from v$object_usage ;

INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING END_MONITORING
------------------- -------------------
MYTABLE_IDX MYTABLE NO YES
02/08/2010 11:40:35 02/08/2010 11:41:27

High Water Mark In Oracle

High water mark

The high water mark is divides a segment into used blocks free blocks
Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted.
Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan.
Oracle keeps track of the high water mark for a segment in the segment header.

Moving the high water mark

In normal DB operations, the high water mark only moves upwards, not downwards. The exceptions being the truncate.
If there is a lot of free space below the high water mark, one might consider to use alter table move statements.

Initial position

The initial position of the high water mark is extent 0 block 0 for tables and extent 0 block 1 for indexes.

Shutting down the Oracle Database

To shutdown an instance you must connect as SYSOPER or SYSDBA and by using the following command you can shutdown the database :

SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

Shutdown options

a) SHUTDOWN NORMAL

NORMAL shutdown of the database proceeds with followings:

i) New connections are not allowed.
ii) server waits for all users who are currently connected to disconnect before shutdown.
iii)Background processes are terminated
iv) Oracle Server closes and dismounts the database before shutting down the instance.

b) SHUTDOWN TRANSACTIONAL

i) No new connections can be made
ii)waits till the user transactions are completed
iii) when all transactions have finished a shutdown occurs immediately

c) SHUTDOWN IMMEDIATE

i) SQL statements currently processing are not completed
ii)does not wait for users who are connected to database to logout
iii) oracle rolls back active transactions and disconnects all users.
iv) closes and dismounts the database before shutting down the instance.

d) SHUTDOWN ABORT

i) currently SQL statements being processed by the Oracle server are immediately terminated.
ii) Oracle does not waits for users who are currently connected to the database to disconnect.
iii) Uncommitted transactions are not rolled back
iv) instance terminated without closing the files.
v) The database is not closed or dismounted.
vi) The next startup requires instance recovery , which occurs automatically.

Opening database in read only or read write mode or restricted mode

To prevent the user to modify the data you can open the database in read only mode by following command

ALTER DATABASE OPEN READ ONLY

TO make database read write

ALTER DATABASE OPEN READ WRITE

In READ WRITE mode the users can generate online redo log files.

Opening database in restricted mode:

You can open database in restricted mode to perform database structure maintenance or import export of database.

STARTUP RESTRICTED

To place the instance in the restricted mode

ALTER SYSTEM ENABLE RESTRICTED SESSION;

Enabling restricted session mode ensures that in future only those users with restricted session privilege can login.

You can also disable the restricted session mode by DISABLE RESTRICTED SESSION command