Tuesday 8 October 2013

How to clear /flush buffer_cache;



SQL> clear buffer;
buffer cleared

SQL> alter system flush buffer_cache;

System altered.


SQL> alter system flush shared_pool;

System altered.



SQL> purge dba_recyclebin;

DBA Recyclebin purged.

How to check block corruption in oracle;

 dbv

[oracle@adcp ~]$ dbv

DBVERIFY: Release 11.2.0.1.0 - Production on Tue Oct 8 11:56:03 2013

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

Keyword     Description                    (Default)
----------------------------------------------------
FILE        File to Verify                 (NONE)
START       Start Block                    (First Block of File)
END         End Block                      (Last Block of File)
BLOCKSIZE   Logical Block Size             (8192)
LOGFILE     Output Log                     (NONE)
FEEDBACK    Display Progress               (0)
PARFILE     Parameter File                 (NONE)
USERID      Username/Password              (NONE)
SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN    Highest Block SCN To Verify    (NONE)
            (scn_wrap.scn_base OR scn) 

 

[oracle@adcp ~]$ dbv file='/oracle_data/u01/app/oracle/product/11.2.0/db_1/adcp/users01.dbf'

DBVERIFY: Release 11.2.0.1.0 - Production on Tue Oct 8 11:56:13 2013

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

DBVERIFY - Verification starting : FILE = /oracle_data/u01/app/oracle/product/11.2.0/db_1/adcp/users01.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 4194302
Total Pages Processed (Data) : 2585414
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 420235
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 46888
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1141765
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 105994890 (0.105994890)
[oracle@adcp ~]$


DBMS_REPAIR: CHECK_OBJECT: This option check block corruption for a particular table or index. It not only detects block corruption but also suggests repair options.
DB_VERIFY: It detects block corruption for a database which is in offline mode.
ANALYZE TABLE: This command is used with VALIDATE STRUCTURE option to analyze the structure of index, table or cluster. It confirm with message if the structure of object is ok and show error message if corruption is detected.
DB_BLOCK_CHECKING:

Step by step clone of an oracle database using RMAN

 Both the target and source database are running in 11g release 2version 


Source DB

Step 1:

[oracle@exilant ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 12:36:33 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     826
Next log sequence to archive   828
Current log sequence           828
SQL> exit

Step 2:

[oracle@exilant dbs]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 18 12:38:10 2013

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

RMAN> connect target /

connected to target database: APPLE (DBID=3254491270)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name APPLE are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Step 3:

RMAN> backup database plus archivelog;


Starting backup at 18-SEP-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=222 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=615 RECID=180 STAMP=821463136
input archived log thre

channel ORA_DISK_1: starting piece 1 at 18-SEP-13
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_annnn_TAG20130918T123905_93lndlbc_.bkp tag=TAG20130918T123905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp tag=TAG20130918T123905 comment=NONE

Starting Control File and SPFILE Autobackup at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/autobackup/2013_09_18/o1_mf_s_826461889_93lnqbsp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-13

Step 4:

RMAN> backup current controlfile;

Starting backup at 18-SEP-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 18-SEP-13
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_ncnnf_TAG20130918T124731_93lnwfpv_.bkp tag=TAG20130918T124731 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 18-SEP-13

Starting Control File and SPFILE Autobackup at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/autobackup/2013_09_18/o1_mf_s_826462057_93lnwlj8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-13

RMAN> exit

Step 5:

oracle@exilant dbs]$ cd /opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/
[oracle@exilant 2013_09_18]$ ls
o1_mf_annnn_TAG20130918T123905_93lndlbc_.bkp
o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp
o1_mf_annnn_TAG20130918T123905_93lnjctl_.bkp
o1_mf_annnn_TAG20130918T123905_93lnlqgs_.bkp
o1_mf_annnn_TAG20130918T124447_93lnq7v1_.bkp
o1_mf_ncnnf_TAG20130918T124731_93lnwfpv_.bkp
o1_mf_nnndf_TAG20130918T124339_93lno5bt_.bkp

Note: Before scp make the directory in the target  mkdir -p opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/

[oracle@exilant 2013_09_18]$ scp * oracle@10.0.13.10:/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/
oracle@10.0.13.10's password:
o1_mf_annnn_TAG20130918T123905_93lndlbc 100% 1140MB  20.0MB/s   00:57   
o1_mf_annnn_TAG20130918T123905_93lngn0k 100% 1129MB  22.1MB/s   00:51   
o1_mf_annnn_TAG20130918T123905_93lnjctl 100% 1137MB  19.9MB/s   00:57   
o1_mf_annnn_TAG20130918T123905_93lnlqgs 100% 1064MB  19.4MB/s   00:55   
o1_mf_annnn_TAG20130918T124447_93lnq7v1 100%   17KB  17.0KB/s   00:00   
o1_mf_ncnnf_TAG20130918T124731_93lnwfpv 100% 9984KB   9.8MB/s   00:01   
o1_mf_nnndf_TAG20130918T124339_93lno5bt 100% 1131MB  18.5MB/s   01:01  

Step 6:

[oracle@exilant 2013_09_18]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 12:54:42 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile='/tmp/initclonedb.ora' from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@exilant 2013_09_18]$ scp /tmp/initclonedb.ora oracle@10.0.13.10:/opt/app/oracle/product/11.2.0/dbhome_1/dbs/ {target oraclehome/dbs}
oracle@10.0.13.10's password:
initclonedb.ora                         100%  978     1.0KB/s   00:00   

[oracle@exilant 2013_09_18]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 13:11:34 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/apple/system01.dbf
/opt/app/oracle/oradata/apple/sysaux01.dbf
/opt/app/oracle/oradata/apple/undotbs01.dbf
/opt/app/oracle/oradata/apple/users01.dbf


In Target

Step 1:

SQL> archive log list;
Database log mode           Archive Mode

Step 2
:

$ mkdir -p /opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/

from the reference of the rman backup
channel ORA_DISK_1: finished piece 1 at 18-SEP-13
piece handle=/opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp tag=TAG20130918T123905 comment=NONE


[oracle@oraclecps dbs]$ cd /opt/app/oracle/flash_recovery_area/APPLE/backupset/2013_09_18/

ls
o1_mf_annnn_TAG20130918T123905_93lndlbc_.bkp
o1_mf_annnn_TAG20130918T123905_93lngn0k_.bkp
o1_mf_annnn_TAG20130918T123905_93lnjctl_.bkp
o1_mf_annnn_TAG20130918T123905_93lnlqgs_.bkp
o1_mf_annnn_TAG20130918T124447_93lnq7v1_.bkp
o1_mf_ncnnf_TAG20130918T124731_93lnwfpv_.bkp

[oracle@oraclecps 2013_09_18]$ cd /opt/app/oracle/product/11.2.0/dbhome_1/dbs/
ls

[oracle@oraclecps dbs]$ ls
hc_DBUA0.dat    initclonedb.ora  peshm_DBUA0_0      spfileclonedb.ora
hc_clonedb.dat    initorcl.ora     peshm_clonedb_0  spfileorcl.ora
hc_orcl.dat    lkORCL         peshm_orcl_0
init.ora    orapworcl     snapcf_orcl.f

Step3:

$  vi initclonedb.ora  {previous init file now make changes}
\[oracle@oraclecps dbs]$ cat initclonedb.ora
apple.__db_cache_size=2080374784
apple.__java_pool_size=134217728
apple.__large_pool_size=67108864
apple.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
apple.__pga_aggregate_target=2684354560
apple.__sga_target=4026531840
apple.__shared_io_pool_size=0
apple.__shared_pool_size=1677721600
apple.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/apple/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/app/oracle/oradata/apple/control01.ctl','/opt/app/oracle/flash_recovery_area/apple/control02.ctl'
*.db_block_size=8192
*.db_domain='ads.exilant.in'
*.db_name='apple'
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=42949672960
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=appleXDB)'
*.memory_target=6655311872
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.star_transformation_enabled='TRUE'
*.undo_tablespace='UNDOTBS1'

Now the word apple has to change to cloned to achieve this type..in vi editor

esc-:1,$ s#apple#clonedb#g

And after that add two covert lines

*.db_file_name_convert='apple','clonedb'
*.log_file_name_convert='apple','clonedb'

Now my init files look like

[oracle@oraclecps dbs]$ cat initclonedb.ora
clonedb.__db_cache_size=2080374784
clonedb.__java_pool_size=134217728
clonedb.__large_pool_size=67108864
clonedb.__oracle_base='/opt/app/oracle'#ORACLE_BASE set from environment
clonedb.__pga_aggregate_target=2684354560
clonedb.__sga_target=4026531840
clonedb.__shared_io_pool_size=0
clonedb.__shared_pool_size=1677721600
clonedb.__streams_pool_size=0
*.audit_file_dest='/opt/app/oracle/admin/clonedb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/app/oracle/oradata/clonedb/control01.ctl','/opt/app/oracle/flash_recovery_area/clonedb/control02.ctl'
*.db_block_size=8192
*.db_domain='ads.exilant.in'
*.db_file_name_convert='apple','clonedb'
*.db_name='clonedb'
*.db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=42949672960
*.diagnostic_dest='/opt/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
*.log_file_name_convert='apple','clonedb'
*.memory_target=6655311872
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.star_transformation_enabled='TRUE'
*.undo_tablespace='UNDOTBS1'

Step 4:

Now make these directories reference from the init file highlited adump and two control files

[oracle@oraclecps dbs]$ mkdir -p /opt/app/oracle/admin/clonedb/adump
[oracle@oraclecps dbs]$ mkdir -p /opt/app/oracle/oradata/clonedb/
[oracle@oraclecps dbs]$ mkdir -p /opt/app/oracle/flash_recovery_area/clonedb/

Step 5:

[oracle@oraclecps dbs]$ vi /etc/oratab

orcl:/opt/app/oracle/product/11.2.0/dbhome_1:N
clonedb:/opt/app/oracle/product/11.2.0/dbhome_1:N  add this line only

Step 6:

[oracle@oraclecps dbs]$ . oraenv
ORACLE_SID = [orcl] ? clonedb
The Oracle base for ORACLE_HOME=/opt/app/oracle/product/11.2.0/dbhome_1 is /opt/app/oracle
[oracle@oraclecps dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 18 15:26:09 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;

SQL> show parameter service;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
service_names                 string     clonedb.ads.exilant.in
SQL>


[oracle@oraclecps dbhome_1]$ cd network/admin/

Make sure you copy the tnsnames entry to the target server from the source
[oracle@oraclecps admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


APPLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.11.23) (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apple.ads.exilant.in)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.13.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = clonedb.ads.exilant.in)
    )
  )

[oracle@oraclecps admin]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 18-SEP-2013 16:06:07

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/opt/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.13.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = clonedb.ads.exilant.in)))
OK (0 msec)

[oracle@oraclecps admin]$ tnsping apple

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 18-SEP-2013 16:06:11

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/opt/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.11.23) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = apple.ads.exilant.in)))
OK (210 msec)

[oracle@oraclecps admin]$

sql>sqlplus sys/oracle@apple---tns entry

sql>connected

 it should connect

Step 7:


[oracle@oraclecps admin]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 18 16:08:06 2013

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

RMAN> connect auxiliary /

connected to auxiliary database: CLONEDB (not mounted)

RMAN> connect target sys/oracle@apple

connected to target database: APPLE (DBID=3254491270)

RMAN> run
2> {
3> allocate auxiliary channel c1 device type disk;
4> allocate channel c2 device type disk;
5> duplicate target database to 'clonedb';
6> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=10 device type=DISK

allocated channel: c2
channel c2: SID=6 device type=DISK

Starting Duplicate Db at 18-SEP-13

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''APPLE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter sys

$ sqlplus / as sysdba

sql>select name  , open_mode from v$database;

cloned read write

sql> select name  , open_mode,instance_name from v$database,v$instance;

dbname-clonedb
open_mode -read write
instance name-clonedb



Hope this will help to clone the oracle db


Monday 7 October 2013

How to determine whether a table is used to store materialized view logs;

:

SQL>conn manjit/manjit;
Connected.
SQL> create table T1(A number primary key);

Table created.

SQL> create materialized view log on T1 with primary key;

Materialized view log created.

SQL> select TABLE_NAME from USER_TABLES;

TABLE_NAME
------------------------------
MANJU
MANJIT
SYS_EXPORT_FULL_32
SYS_EXPORT_FULL_31
SYS_EXPORT_FULL_33
SYS_EXPORT_FULL_35
SYS_EXPORT_FULL_36
SYS_EXPORT_FULL_37
SYS_EXPORT_FULL_38
SYS_EXPORT_FULL_39
SYS_EXPORT_FULL_03

TABLE_NAME
------------------------------
SYS_EXPORT_FULL_40
SYS_EXPORT_FULL_41
SYS_EXPORT_FULL_43
SYS_EXPORT_FULL_42
SYS_EXPORT_FULL_24
SYS_EXPORT_FULL_28
SYS_EXPORT_FULL_30
SYS_EXPORT_FULL_34
MLOG$_T1
T1
SYS_EXPORT_FULL_44

TABLE_NAME
------------------------------
SYS_EXPORT_FULL_46
SYS_EXPORT_FULL_47
SYS_EXPORT_FULL_45
SYS_EXPORT_FULL_17
SYS_EXPORT_FULL_21
SYS_EXPORT_FULL_22
SYS_EXPORT_FULL_29
SYS_EXPORT_FULL_27
SYS_EXPORT_FULL_25
SYS_EXPORT_FULL_23
SYS_EXPORT_FULL_26

TABLE_NAME
------------------------------
SYS_EXPORT_FULL_16
SYS_EXPORT_FULL_02
SYS_EXPORT_FULL_19
SYS_EXPORT_FULL_20
SYS_EXPORT_FULL_11
SYS_EXPORT_FULL_15
RUPD$_T1

40 rows selected.

SQL> select MASTER, LOG_TABLE from USER_MVIEW_LOGS;

MASTER                   LOG_TABLE
------------------------------ ------------------------------
T1                   MLOG$_T1

SQL> select * from USER_MVIEW_LOGS;

LOG_OWNER               MASTER
------------------------------ ------------------------------
LOG_TABLE               LOG_TRIGGER              ROW PRI OBJ FIL
------------------------------ ------------------------------ --- --- --- ---
SEQ INC
--- ---
MANJIT                   T1
MLOG$_T1                              NO  YES NO  NO
NO  NO


SQL> select  master, log, temp_log from sys.mlog$ where mowner = user and master = 'T1';

MASTER                   LOG
------------------------------ ------------------------------
TEMP_LOG
------------------------------
T1                   MLOG$_T1
RUPD$_T1


SQL>


How to get the explain plan in oracle


We can use the DBMS_XPLAN


1.
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

2 .

SQL> conn manjit/manjit;
Connected.
SQL> explain plan for select * from home;

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2313634949

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     3 |    36 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| HOME |     3 |    36 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)

12 rows selected.


SQL> analyze table home compute statistics;

Table analyzed.

SQL> explain plan for select * from home;

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2313634949

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     3 |    12 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| HOME |     3 |    12 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

How to find out which user is running what sql query in oracle database

Below script will gives the information of
  • Which user currently logged on
  • Which sql query they are running
  • Which computer the user is logged on
  • How long the query is running

To run this query you need SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY grant.

SELECT 
SUBSTR(SS.USERNAME,1,8) USERNAME,
SS.OSUSER "USER",
AR.MODULE || ' @ ' || SS.machine CLIENT,
SS.PROCESS PID,
TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,
AR.DISK_READS DISK_READS,
AR.BUFFER_GETS BUFFER_GETS,
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,
W.EVENT EVENT,
SS.status,
AR.SQL_fullTEXT SQL
FROM V$SESSION_WAIT W,
V$SQLAREA AR,
V$SESSION SS, 
v$timer T
WHERE SS.SQL_ADDRESS = AR.ADDRESS
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
AND SS.SID = w.SID (+)
AND ss.STATUS = 'ACTIVE'
AND W.EVENT != 'client message'
ORDER BY  SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC
;

How to use sqlplus SPOOL command:

Using spool command we can generate output files in client machine:

SQL>conn manjit/manjit;
connected

 SQL> Spool on
SQL> set heading off <===
SQL> Spool /oradata/backup/spooltext.txt
SQL> Query(sql query)
SQL> Spool off

We create a file in D:\External_Tables named emp_query.sql with the following saved query. 

SELECT EMPNO ||',' || ENAME || ',' || SAL || ',' || COMM FROM EMP; 

SQL> CONN manjit/manjit@pro 
Connected. 
 
 SQL> SET FEEDBACK OFF HEADING OFF ECHO OFF 
 SQL> SPOOL D:\External_Tables\emp.csv 
 SQL> @D:\External_Tables\emp_query.sql 
 SQL> SPOOL OFF 
 
To view the content of the file from SQLPLUS, type.. 

SQL> host type D:\External_Tables\emp.csv 

How to find out expected time of completion for an oracle query

From oracle 10 g onwards we have got an option to check how long a query will run,to find out expected time of completion for a query.

 Script:This script is using v$session_longops

SELECT
opname,
target,
ROUND((sofar/totalwork),4)*100 Percentage_Complete,
start_time,
CEIL(time_remaining/60) Max_Time_Remaining_In_Min,
FLOOR(elapsed_seconds/60) Time_Spent_In_Min
FROM v$session_longops
WHERE sofar != totalwork;

Script:Should have acess to v$sqlarea table 

SELECT 
opname
target,
ROUND((sofar/totalwork),4)*100 Percentage_Complete,
start_time,
CEIL(TIME_REMAINING  /60) MAX_TIME_REMAINING_IN_MIN,
FLOOR(ELAPSED_SECONDS/60) TIME_SPENT_IN_MIN,
AR.SQL_FULLTEXT,
AR.PARSING_SCHEMA_NAME,
AR.MODULE client_tool
FROM V$SESSION_LONGOPS L, V$SQLAREA AR
WHERE L.SQL_ID = AR.SQL_ID 
AND TOTALWORK > 0
AND ar.users_executing > 0
AND sofar != totalwork;