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

Data Pump Backup Using Parameter File (.par)

This article represent how can use data pump with PARAFILE.

1. Create file with extension .par (Parameter File)  in any directory like.

I have created in (D:\app\) name DATA_PUMP.par

2. Give parameter value. Like

My file contain this value 

DIRECTORY=DATA_DIR

DUMPFILE=RND_TABLE.dmp

LOGFILE=RND_TABLE.log

TABLES=RND.DEPT,RND.EMP

3. Now execute with data pump. Like 

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

C:\Users\rajib.pradhan>

========================================================

With Where Condition 

DIRECTORY=DATA_DIR

DUMPFILE=RND_TABLE_QUERY.dmp

LOGFILE=RND_TABLE_QUERY.log

TABLES=RND.DEPT,RND.EMP

QUERY=RND.EMP:"WHERE DEPARTMENT_ID !=50 ORDER BY EMPLOYEE_ID"

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:37:18 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"                                 9.265 KB       0 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_QUERY.DMP

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 00:37:35

Oracle Table Locking Details.

When an transaction (INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE) occurred in table. Oracle database lock table. It's called DML lock. The purpose of a DML lock, also called a data lock, is to guarantee the integrity of data being accessed concurrently by multiple users.

Row Locks (TX): A row lock, also called a TX lock, is a lock on a single row of a table. A transaction acquires a row lock for each row modified by using DML( INSERT, UPDATE, DELETE, MERGE) and SELECT ... FOR UPDATE. The row lock exists until the transaction commits or rolls back.

Table Locks (TM): A transaction automatically acquires a table lock (TM lock) when a table is modified with the following statements: INSERT, UPDATE,DELETE, MERGE, and SELECT ... FOR UPDATE. These DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.

Manual Lock : User can lock table manually for interrupting access.

Example:-

SQL> CONN RND/rnd@ORCL
Connected.
SQL>
SQL> CREATE TABLE CURR
  2  (
  3    CURRENCY_CD  VARCHAR2(9 BYTE),
  4    EXCH_RT_EUR  NUMBER,
  5    EXCH_RT_USD  NUMBER
  6  );

Table created.

SQL> INSERT INTO CURR(CURRENCY_CD, EXCH_RT_EUR, EXCH_RT_USD) VALUES ('BDT',1,80);

1 row created.
------------------------------------------------------------------------------------------------------------
Now connect sys schema using another terminal. Because when you exit from current terminal session will be close and transaction will be ROLLBACK.
-----------------------------------------------------------------------------------------------------------------
SQL> CONN /AS SYSDBA
Connected.
SQL> set line 200
SQL> SELECT A.SESSION_ID,
  2         A.ORACLE_USERNAME,
  3         A.OS_USER_NAME,
  4         B.OWNER "OBJECT OWNER",
  5         B.OBJECT_NAME,
  6         B.OBJECT_TYPE,
  7         A.LOCKED_MODE
  8    FROM (SELECT OBJECT_ID,
  9                 SESSION_ID,
 10                 ORACLE_USERNAME,
 11                 OS_USER_NAME,
 12                 LOCKED_MODE
 13            FROM V$LOCKED_OBJECT) A,
 14         (SELECT OBJECT_ID,
 15                 OWNER,
 16                 OBJECT_NAME,
 17                 OBJECT_TYPE
 18            FROM DBA_OBJECTS) B
 19   WHERE A.OBJECT_ID = B.OBJECT_ID;

SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   OBJECT OWNER
---------- ------------------------------ ------------------------------ ------------------------------
OBJECT_NAME                                                                                                              OBJECT_TYPE         LOCKED_MODE
-------------------------------------------------------------------------------------------------------------------------------- ------------------- -----------
         8 RND                            SPFTL\rajib.pradhan            RND
CURR                                                                                                                     TABLE                         3


SQL>
------------------------------------------------------------------------------------------------------
Now apply commit from your previous terminal.
-------------------------------------------------------------------------------------------------------

SQL> commit;

Commit complete.

------------------------------------------------------------------------------------------------------
Now check lock object using sys terminal.
------------------------------------------------------------------------------------------------------

SQL>  SELECT A.SESSION_ID,
  2          A.ORACLE_USERNAME,
  3          A.OS_USER_NAME,
  4          B.OWNER "OBJECT OWNER",
  5          B.OBJECT_NAME,
  6          B.OBJECT_TYPE,
  7          A.LOCKED_MODE
  8     FROM (SELECT OBJECT_ID,
  9                  SESSION_ID,
 10                  ORACLE_USERNAME,
 11                  OS_USER_NAME,
 12                  LOCKED_MODE
 13             FROM V$LOCKED_OBJECT) A,
 14          (SELECT OBJECT_ID,
 15                  OWNER,
 16                  OBJECT_NAME,
 17                  OBJECT_TYPE
 18             FROM DBA_OBJECTS) B
 19    WHERE A.OBJECT_ID = B.OBJECT_ID;

no rows selected


SQL>

Copy file to another host using Linux/Unix System.

$ pwd
/u01/SBS_REP
$ scp -r /u01/SBS_REP/* root@10.11.201.200:/u02/SBS/
The authenticity of host '10.11.201.200 (10.11.201.200)' can't be established.
RSA key fingerprint is 59:3e:a0:5f:17:3d:61:fc:fb:30:6a:91:71:57:0f:d3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.11.201.200' (RSA) to the list of known hosts.
root@10.40.40.41's password:
1040_SBS-2_03082014105501.txt                                                                                                         100% 1455KB   1.4MB/s   00:00
1057_SBS-2_03082014105507.txt                                                                                                         100% 1313KB   1.3MB/s   00:00
1107_SBS-2_03082014105513.txt                                                                                                         100% 1623KB   1.6MB/s   00:00
1123_SBS-2_03082014105519.txt                                                                                                         100% 1688KB   1.7MB/s   00:00
1164_SBS-2_03082014105526.txt                                                                                                         100% 1633KB   1.6MB/s   00:00
1172_SBS-2_03082014105533.txt                                                                                                         100% 1477KB   1.4MB/s   00:00
1206_SBS-2_03082014105539.txt                                                                                                         100% 3731KB   3.6MB/s   00:00
1214_SBS-2_03082014105550.txt                                                                                                         100% 2330KB   2.3MB/s   00:00
1230_SBS-2_03082014105558.txt                                                                                                         100% 1082KB   1.1MB/s   00:00
1297_SBS-2_03082014105604.txt                                                                                                         100% 3902KB   3.8MB/s   00:00
1305_SBS-2_03082014105616.txt                                                                                                         100% 1282KB   1.3MB/s   00:00
16014_SBS-2_03082014105622.txt                                                                                                        100% 1321KB   1.3MB/s   00:00
16048_SBS-2_03082014105628.txt                                                                                                        100% 2414KB   2.4MB/s   00:00
16071_SBS-2_03082014105636.txt                                                                                                        100% 1612KB   1.6MB/s   00:00
16121_SBS-2_03082014105642.txt                                                                                                        100% 1732KB   1.7MB/s   00:00
16162_SBS-2_03082014105650.txt                                                                                                        100% 3786KB   3.7MB/s   00:01
16204_SBS-2_03082014105701.txt                                                                                                        100% 5304KB   5.2MB/s   00:00
16212_SBS-2_03082014105716.txt                                                                                                        100% 1225KB   1.2MB/s   00:00
16220_SBS-2_03082014105722.txt                                                                                                        100% 1895KB   1.9MB/s   00:00
16238_SBS-2_03082014105729.txt                                                                                                        100% 2078KB   2.0MB/s   00:00
16295_SBS-2_03082014105737.txt                                                                                                        100% 2149KB   2.1MB/s   00:00
16345_SBS-2_03082014105744.txt                                                                                                        100% 1562KB   1.5MB/s   00:00
16352_SBS-2_03082014105750.txt                                                                                                        100% 2504KB   2.5MB/s   00:00
44107_SBS-2_03082014105759.txt                                                                                                        100% 3611KB   3.5MB/s   00:00
44123_SBS-2_03082014105810.txt                                                                                                        100% 3597KB   3.5MB/s   00:00
44164_SBS-2_03082014105821.txt                                                                                                        100% 4605KB   4.5MB/s   00:01
44206_SBS-2_03082014105835.txt                                                                                                        100% 7741KB   7.6MB/s   00:00
44297_SBS-2_03082014105857.txt                                                                                                        100% 3663KB   3.6MB/s   00:00
44321_SBS-2_03082014105908.txt                                                                                                        100% 2144KB   2.1MB/s   00:00
44347_SBS-2_03082014105916.txt                                                                                                        100%  819KB 819.3KB/s   00:00
44396_SBS-2_03082014105920.txt                                                                                                        100% 3769KB   3.7MB/s   00:00
44412_SBS-2_03082014105932.txt                                                                                                        100% 1417KB   1.4MB/s   00:00
55020_SBS-2_03082014105937.txt                                                                                                        100%  500KB 499.9KB/s   00:00
55046_SBS-2_03082014105941.txt                                                                                                        100%  385KB 385.0KB/s   00:00
55079_SBS-2_03082014105945.txt                                                                                                        100% 2402KB   2.4MB/s   00:00
55087_SBS-2_03082014105953.txt                                                                                                        100% 3373KB   3.3MB/s   00:00
55194_SBS-2_03082014110003.txt                                                                                                        100%  493KB 492.8KB/s   00:00
55202_SBS-2_03082014110007.txt                                                                                                        100% 1721KB   1.7MB/s   00:00
55228_SBS-2_03082014110013.txt                                                                                                        100%  988KB 987.6KB/s   00:00
55236_SBS-2_03082014110018.txt                                                                                                        100% 1306KB   1.3MB/s   00:00
55244_SBS-2_03082014110024.txt                                                                                                        100%  727KB 727.3KB/s   00:00
55319_SBS-2_03082014110028.txt                                                                                                        100%  939KB 938.8KB/s   00:00
55343_SBS-2_03082014110033.txt                                                                                                        100% 1468KB   1.4MB/s   00:00
55376_SBS-2_03082014110039.txt                                                                                                        100%   19KB  19.4KB/s   00:00
$