Monitoring Index to Overhead Unnecessary Statement

We can monitor our indexes to determine whether they are being used or not. If we found the indexes are not being  uses we can disable this for reduce database Overhead.


SQL> CREATE TABLE INDEX_MONITOR (ID NUMBER, NAME VARCHAR2(300));

Table created.

SQL> CREATE INDEX IND_INDEX_MONITOR_ID ON INDEX_MONITOR(ID);

Index created.

SQL> ALTER INDEX IND_INDEX_MONITOR_ID MONITORING USAGE;

Index altered.

SQL> INSERT INTO INDEX_MONITOR VALUES(10,'RAJIB');

1 row created.

SQL> INSERT INTO INDEX_MONITOR VALUES(20,'RAJIB');

1 row created.

SQL> INSERT INTO INDEX_MONITOR VALUES(30,'RAJIB');

1 row created.

SQL> INSERT INTO INDEX_MONITOR VALUES(40,'RAJIB');

1 row created.

SQL> SELECT * FROM INDEX_MONITOR;

        ID
----------
NAME
--------------------------------------------------------------------------------
        10
RAJIB

        20
RAJIB

        30
RAJIB


        ID
----------
NAME
--------------------------------------------------------------------------------
        40
RAJIB


SQL> SELECT * FROM V$OBJECT_USAGE;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING    END_MONITORING
------------------- -------------------
IND_INDEX_MONITOR_ID           INDEX_MONITOR                  YES NO
08/25/2014 18:04:45


SQL> SET LINE 2000
SQL> SET LINE 2000
SQL> SELECT * FROM V$OBJECT_USAGE;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IND_INDEX_MONITOR_ID           INDEX_MONITOR                  YES NO  08/25/2014 18:04:45

SQL> SELECT * FROM INDEX_MONITOR WHERE ID=10;

        ID NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
        10 RAJIB

SQL> SELECT * FROM V$OBJECT_USAGE;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- -------------------
IND_INDEX_MONITOR_ID           INDEX_MONITOR                  YES YES 08/25/2014 18:04:45

SQL>

How to create new oracle instance in windows.

C:\Users\rajib.pradhan>oradim -NEW -SID CLONEDB -STARTMODE MANUAL -PFILE D:\app\oradata\DBRMAN_BACKUP\DBRMAN_PFILE.ORA
Instance created.

C:\Users\rajib.pradhan>

CLONEDB = Database Name.
D:\app\oradata\DBRMAN_BACKUP\DBRMAN_PFILE.ORA - PFILE Lication


Deleting Instance
----------------------------------------------------------------------------

C:\Users\rajib.pradhan>ORADIM -DELETE -SID CLONEDB
Instance deleted.


C:\Users\rajib.pradhan>

UDE-00010: multiple job modes requested, schema and tables.

Cause : This error show when you use multiple job in parameter file.

Solution & Example: Use only one parameter like SCHEMAS or TABLES.

When Parameter file are like ..

SCHEMAS=RND
DIRECTORY=DATA_DIR
DUMPFILE=RND_TABLE_QUERY.dmp
LOGFILE=RND_TABLE_QUERY.log
TABLES=RND.DEPT,RND.EMP

C:\Users\rajib.pradhan>expdp "'/ as sysdba'" parfile=D:\app\DATA_PUMP.par

Export: Release 11.2.0.1.0 - Production on Fri Aug 15 00:09:14 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDE-00010: multiple job modes requested, schema and tables.

When I have remove SCHEMA parameter like 

DIRECTORY=DATA_DIR

DUMPFILE=RND_TABLE_QUERY.dmp

LOGFILE=RND_TABLE_QUERY.log

TABLES=RND.DEPT,RND.EMP

I have successfully export ...

C:\Users\rajib.pradhan>expdp "'/ as sysdba'" parfile=D:\app\DATA_PUMP.par

Export: Release 11.2.0.1.0 - Production on Fri Aug 15 00:10:45 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" parfile=D:\app\DATA_PUMP.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 41.06 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "RND"."EMP"                                 35.00 MB  524288 rows

. . exported "RND"."DEPT"                                7.023 KB      28 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  D:\DUMP\RND_TABLE.DMP

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:11:43