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

opening database in mount or open mode

If you have started your database in nomount mode then you can simply fire this command to
open the database in mount mode or in open mode

ALTER DATABASE {MOUNT | OPEN}

Starting Oracle Database

Starting Oracle Database has following stages :
a)nomount
b)mount
c)open

a)nomount:

when the Oracle Database starts it starts in nomount mode first. In this stage it reads the paramter file to read the configuration.By default it looks for spfileSID.ora if this file dosen't exists then it looks for default spfile on the server side.
If default spfile is also not found then it looks for initSID.ora the pfile and starts the instance.
You can also mention the pfile at the startup command to override the use of default spfile to start the instance. for example

STARTUP PFILE=$ORACLE_HOME/DBS/INITDEVEN.ora

After this it allocates the SGA and starts the background processes.After this opening the alert file and trace file is performed.

b)mount :

Generally we start the database in mount mode to perform some maintenance operations.In this stage the database is started but it is not open that is users are not allows to connect to the database.

you can start the database in mount mode to perform operations like renaming data files,enabling and disabling archivelog mode and also for cold backups

In the mount mode following tasks are performed:

a) associating a database with previously started instance.
b)locating and opening control file
c) reading the control file to obtain the information regarding the names and status of data file and online redo log files.

Here in this stage the existence of the datafile and online redo log file is not checked.

c) Open:

Now the database is open means valid user can connect to database and perform data access operations.

In this stage the opening of datafiles and online redo log files are performed.If any of the datafile or online redo log files are not present then the oracle server will return an error.

Oracle server verifies that all the data files and online redo log files are consistence.If necessary the background process SMON perform the instance recovery