How to find the unused index in database

Query to find the unused  index in database

SELECT owner, table_name ,index_name,index_type ,LAST_ANALYZED FROM DBA_INDEXES WHERE index_name IN (SELECT index_name
FROM
(SELECT
owner,
index_name
FROM
DBA_INDEXES di
WHERE
di.index_type != 'LOB'
AND
owner   =&owner
MINUS
SELECT
index_owner owner,
index_name
FROM
DBA_CONSTRAINTS dc
WHERE
index_owner   =&owner
MINUS
SELECT
p.object_owner owner,
p.object_name index_name
FROM
DBA_HIST_SNAPSHOT sn,
DBA_HIST_SQL_PLAN p
WHERE
sn.snap_id BETWEEN &start_snap_id AND &stop_snap_id
AND
p.object_type = 'INDEX'
))
and owner =&owner
ORDER BY 1


Query to find the frequency  of  index usage

SELECT
p.object_name search_columns,
ROUND (COUNT(*)/15 ,2 ) COUNT
FROM
DBA_HIST_SNAPSHOT sn,
DBA_HIST_SQL_PLAN p,
DBA_HIST_SQLSTAT st
WHERE
st.sql_id = p.sql_id
AND
sn.snap_id = st.snap_id
AND
p.object_type = 'INDEX'
AND sn.snap_id BETWEEN &start_snap_id AND &stop_snap_id
AND p.object_owner =&owner
GROUP BY
p.object_name ORDER BY 2 DESC, 1

ORA-00257:archiver error

ORA-00257:	archiver error. Connect internal only, until freed.
Cause:	The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.
Action:	Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving. In My case the device associated with ARCHIVE_LOG_DEST  was 100 % full

Life Cycle of a Cursor

Having a good understanding of life cycle of cursors is required knowledge for optimizing application
that execute SQL statements. The following are the steps carried out during the processing of a cursor:
Open cursor: A memory structure for the cursor is allocated in the server-side private memory
of the server process associates with the session,a user global area (UGA).Note that no SQL statement is associated with the cursor yet
Parse cursor: A SQL statement is associate with the cursor It's parse representation that include
the execution plan (which describe how the SQl engine will execute the SQl statement)is loaded in the shared pool ,specifically,in the literary cache. The structure in the uga is updated to store a pointer to the location of the sharable cursor in the library cache
Define output variables:  If the SQL statements returns a data , the variable receiving must be declare .This is not necessary not only for quires but also for DELETE,INSERT,UPDATE statement that use the returning clause
Bind input variables : If the SQL statement use bind variables, their value must be provided .
No check is performed during the binding.If invalid data is passed , a runtime error will be raised during the execution.
Execute cursor: The SQL statements is executed.But be careful , becuase the database engine doesn't always do anything significant during this phase. In fact, for many types of queries,

the real processing is usually delayed to the fetch phase.
Fetch cursor: If the SQL statement returns data, this step retrieves it. Especially for queries,
this step is where most of the processing is performed. In the case of queries, rows might
be partially fetched. In other words, the cursor might be closed before fetching all the rows.
Close cursor: The resources associated with the cursor in the UGA are freed and consequently
made available for other cursors.The shareable cursor in the library cache is not
removed. It remains there in the hope of being reused in the future.