Steps to create database using DBCA tool

Steps to create database using DBCA tool

1) Open a terminal and invoke DBCA




2) Click next to proceed further




3) Click create database and then click next to proceed further




4) Click general purpose and then click next to proceed further

5) Give database name and then click next to proceed further

6) To configure Enterprise Manager tick the checkbox and then click next to proceed further

7) Give the password for the following users and then click next to proceed further

or

8) Select file system and then click next to proceed further

9) Select the location to create database and then click next to proceed further

10) Tick checkbox of flash recovery area and to put database in archivelog mode Tick the checkbox of enable archiving and then click next to proceed further

11) Tick checkbox of sample schema to create some default users for testing purpose and then click next to proceed further

12) Select custom to alter sga size and pga size and then click sizing tab to proceed further

13) You can alter number of process according to your requierment and then click Character set to proceed further

14) Click connection mode to proceed further

15) Select network connection mode and then click next to proceed further

Select shared server mode

Select edit shared server parameter

Select number of dispatcher

16) Select ok to proceed further

17) Database creation will take place

18) Click exit to proceed further

Sql queries to check ACTIVE / INACTIVE Sessions

Sql queries to check ACTIVE / INACTIVE Sessions:

1.Total Count of sessions:

select count(s.status) TOTAL_SESSIONS
from gv$session s;

2.Total Count of Inactive sessions:

select count(s.status) INACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

3.Sessions Which are in Inactive Status From More Than 1Hour:

select count(s.status) "INACTIVE SESSIONS > 1HOUR "
from gv$session s, v$process p
where
p.addr=s.paddr and
s.last_call_et > 3600 and
s.status='INACTIVE';

4.Count Of Active Session:

select count(s.status) ACTIVE_SESSIONS
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='ACTIVE';

5.Total Sessions Count Ordered By Program:

col program for a30
select s.program,count(s.program) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.program;

6.Total Count Of Session Ordered By Moudle:

col module  for a30
prompt TOTAL SESSIONS
select s.module,count(s.sid) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.module;

7.Total Count Of Sessions Ordered By Action:

col action for a30
prompt TOTAL SESSIONS
select s.action,count(s.sid) Total_Sessions
from gv$session s, v$process p
where  p.addr=s.paddr
group by s.action;

8.Inactive Sessions:

prompt INACTIVE SESSIONS
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program
from gv$session s, v$process p
where
p.addr=s.paddr and
s.status='INACTIVE';

9.Inactive:

prompt INACTIVE SESSIONS
select count(s.status) INACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE';

10.Inactive Programs:

col module for a40            
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, v$process p
where  p.addr=s.paddr and
s.status='INACTIVE';

11.Inactive Programs with disk reads:

prompt INACTIVE SESSIONS
select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
GROUP BY S.PROGRAM;

12.Inactive  Session Count With Program:

col program for a30
prompt TOTAL INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
group by s.program
order by 2 desc;

13.Total  Inactive Sessions More Than 1Hour:

col program for a30
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Inactive_Sessions_from_1Hour
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
and s.last_call_et > (3600)
group by s.program
order by 2 desc;

14.Total Inactive Sessions Group By  Module:

col program for a60
COL MODULE FOR A30
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.module,count(s.module) Total_Inactive_Sessions
from gv$session s,v$process p
where     p.addr=s.paddr  AND
s.status='INACTIVE'
group by s.module;

15.Inactive Session Details More Than 1 Hour:

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et_Hrs for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.last_call_et > (3600)
order by last_call_et;

16.Inactive Program  --Any--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
And s.program='&PROGRAM_NAME'
order by last_call_et;

17.Inactive Modules  --Any-

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
And s.module like '%order_cleanup_hazmat_v3.sql'
order by last_call_et;

18.Inactive Jdbc Session:

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a60
col elapsed_time for 999999999999
select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,
s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

19.Count OF Inactive Sessions More Than One Hour:

SELECT COUNT(P.SPID)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.program='JDBC Thin Client'
and s.last_call_et > 3600
order by last_call_et;

FORMS

20.Total Form Sessions:

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%');

21.Forms Sessions Details:

col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600
order by spid;                    


col machine for a15
col "Last SQL" for a30
select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,
S.ACTION,s.process Client_Process,s.machine
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.action like ('FRM%') and
s.last_call_et > 3600;        
order by 4;                          

22.Inactive Forms Sessions Details:

col program for a15
col last_call_et for 999.99
select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
order by last_call_et desc;

23.Unique SPID:

select unique(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

24.Count Forms:

select COUNT(p.spid)
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600;

25.Zero Hash Value:

select COUNT(p.spid)
from gv$session s,gv$process p
where
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like 'FRM:%'
and s.last_call_et > 3600
AND S.SQL_HASH_VALUE=0;

26.Inactive Form By Name:

select count(s.sid) from v$session S
where s.action like ('%&ACTION%')
AND S.STATUS='INACTIVE';

27.Group By Action:

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S
WHERE S.STATUS='INACTIVE' and
s.action like ('%FRM%')
group by s.action;

28.From A Specfic Username:

SET LINSIZE 152
col spid for a10
col process_spid for a10
col user_name for a20
col form_name for a20
select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,
d.user_form_name "FORM_NAME"
from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,
apps.fnd_form_tl d
where
a.login_id=b.login_id
and c.user_name like 'JROMO'
and a.user_id=c.user_id
and trunc(b.start_time) >trunc(sysdate -11)
and trunc(b.end_time) is null
and b.form_id=d.form_id
and d.language='US';

29.Inactive Form:

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='INACTIVE'
and s.action like ('FRM%')
and s.last_call_et > (3600*3)
order by last_call_et;


30.Inactive Form Sessions: 

col cli_proc for a9
COL AUDSID FOR A6
COL PID FOR A6
COL SID FOR A5
COL FORM_NAME FOR A25
COL USER_NAME FOR A15
col last_call_et for 9999.99
SELECT
-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
( SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id
) user_name,vs.status,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,
vs.last_call_et/3600 last_call_et,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( vs.process, 1, 8 ) cli_proc,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.module || ' - ' ||
( SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id        = rf.form_id
AND ft.language       = USERENV('LANG')
), 1, 40 ) form_name
FROM apps.fnd_logins           fl,
gv$process            vp,
apps.fnd_login_resp_forms rf,
gv$session            vs
WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */
AND fl.login_type   = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid          = vp.pid
AND fl.login_id     = rf.login_id
AND rf.end_time    IS NULL
AND rf.audsid       = vs.audsid
and vs.status='INACTIVE'
ORDER BY
vs.process,
fl.process_spid;

Active

prompt ACTIVE SESSIONS
select count(s.status) ACTIVE
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
s.status='ACTIVE';

Moudule

set pagesize 40
col INST_ID for 99
col spid for a10
set linesize 150
col PROGRAM for a10
col action format a10
col logon_time format a16
col module format a13
col cli_process format a7
col cli_mach for a15
col status format a10
col username format a10
col last_call_et for 9999.99
col sql_hash_value for 9999999999999col username for a10
set linesize 152
set pagesize 80
col "Last SQL" for a30
col elapsed_time for 999999999999
select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr
and s.MODULE like ('&MODULE_NAME_1HR%')
and s.last_call_et > ('&TIME_HRS' * 3600)
order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$sqlarea t,gv$process p
where s.sql_address =t.address and
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;

Toad Sessions:

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,
s.status,s.action,s.module,s.program
from gv$session s, gv$process p
where
p.addr=s.paddr
and s.MODULE like ('%TOAD%')
Order by last_call_et;

Client Machine Sessions Count:

select count(s.process) TOTAL from v$session S
where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S
where s.machine like ('%&CLIENT_MACHINE%')
AND S.SQL_HASH_VALUE=0
AND S.LAST_CALL_ET > 3600;

Unique Actions

col module for a40            
prompt INACTIVE SESSIONS
col INACTIVE_PROGRAMS FOR A40
select distinct (s.program) INACTIVE_PROGRAMS,s.module
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
s.machine like ('%&CLIENT_MACHINE%') AND
p.addr=s.paddr and
s.status='INACTIVE';

GROUP BY  program

col program for a60
prompt TOTAL SESSIONS
col INACTIVE_PROGRAMS FOR A40
select s.program,count(s.program) Total_Inactive_Sessions
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr  AND
s.machine like ('%&CLIENT_MACHINE%') AND
s.status='INACTIVE'
group by s.program;

MySQL Commands

MySQL Commands 

1.To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

2.Create a database on the sql server.

mysql> create database [databasename];

3.List all databases on the sql server.

mysql> show databases;

4.Switch to a database.

mysql> use [db name];

5.To see all the tables in the db.

mysql> show tables;

6.To see database's field formats.

mysql> describe [table name];

7.To delete a db.

mysql> drop database [database name];

8.To delete a table.

mysql> drop table [table name];

9.Show all data in a table.

mysql> SELECT * FROM [table name];

10.Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

11.Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

12. Update database permissions/privilages.

mysql> flush privileges;

13.Delete a column.

mysql> alter table [table name] drop column [column name];

14.Describe the fields (columns) of the "products" table.

mysql> DESCRIBE products;

15.create a new MySQL table:

CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1),
signup_date DATE);

Displaying Control File Information:

Displaying Control File Information:

The following views display information about control files:


1.V$DATABASE:
Displays database information from the control file.

2.V$CONTROLFILE:
Lists the names of control files.

3.V$CONTROLFILE_RECORD_SECTION:
Displays information about control file record sections.

4.V$PARAMETER:
Can be used to display the names of control files as specified in the CONTROL_FILES initialization parameter.

SCN Based Recovery

SCN Based Recovery

1.On the new physical standby, determine the current scn:

SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;

CURRENT_SCN
-----------
    900117
   
2.On the new primary database, create an incremental backup based on the current SCN of the standby and a new standby control file:
Connect to rman :
% rman target /
RMAN> backup incremental from scn 900117 database format '/home/oracle/u01/%U';
RMAN> backup device type disk format '/home/oracle/u01/%U' current control file for standby;   

3.Transfer all backup pieces generated by the above command to the standby host.

% scp /home/oracle/u01/04m2vv18_1_1:`pwd`

4.Connect to the new physical standby as the RMAN target to do the following:
restore the standby controlfile,
catalog the standby datafiles and backup pieces,
and switch the standby controlfile to the standby datafile copies

% rman target /
RMAN> startup nomount force;
RMAN> restore standby controlfile from '/home/oracle/u01/0hm30a85_1_1';
RMAN> alter database mount;
RMAN> catalog start with '+DATA/titans_stby';
RMAN> switch database to copy;
RMAN> recover database noredo;

5.Restart managed recovery.
SQL> recover managed standby database using current logfile disconnect;

Wait events in Oracle:

Wait events in Oracle:
  
When Oracle executes an SQL statement, it is not constantly executing. Sometimes it has to wait for a specific event to happen before it can proceed.

For example, if Oracle (or the SQL statement) wants to modify data, and the corresponding database block is not currently in the SGA, Oracle waits for this block to be available for modification.

All possible wait events can be found in v$event_name. In Oracle 10g R1, there are some 806 different wait events.
What Oracle waits for and how long it has totally waited for these events can be monitored through the following views:

v$session_event
v$session_wait
v$system_event


1.Buffer Busy waits/Cache Buffers Chains Latch waits:

This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy".

The two main cases where this can occur are:
1.Another session is reading the block into the buffer
2.Another session holds the buffer in an incompatible mode to our request.

2."Read By Other Session" wait event.

When user sessions request for data, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same data, the first session will read the data into the buffer cache while other sessions wait. In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10g and higher, this wait time is now broken out into the "read by other session" wait event.

3.Log File Sync waits:

Log file sync waits occur when sessions wait for redo data to be written to disk.
Typically this is caused by slow writes or committing too frequently in the application.

4. Db file sequential read

Wait for an I/O read request to complete. A sequential read is usually a single-block read. This differs from "db file scattered read" in that a sequential read reads data into contiguous memory. (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA).

5.Db file scattered read


The process has issued an I/O request to read a series of contiguous blocks from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during a full table scan or full index scan.

6.Direct path read
The process has issued asynchronous I/O requests that bypass the buffer cache, and is waiting for them to complete. These wait events typically involve sort segments.

7.Db file parallel write
The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.

8.Log file sync :
The process is waiting for LGWR to finish flushing the log buffer to disk. This occurs when a user commits a transaction.
(A transaction is not considered committed until all of the redo to recover the transaction has been successfully written to disk.)

9.Enqueue:
The process is waiting on an enqueue (a lock you can see in v$lock).
This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user.

10.Log file parallel write :
The process is waiting for blocks to be written to all online redo log members in one group.
LGWR is typically the only process to see this wait event. It will wait until all blocks have been written to all members.

TOP 10 SQL QUERIES

 The top 10 sql queries which consuming high IO, CPU's. in oracle db. 
 I am getting the sql query by applying the hash value in v$sqlarea. The queries are below.
Really it is very helpful for daily activity.
1.Top sql command:

SQL> select c.* from
       (select disk_reads,
          buffer_gets,
                   rows_processed,
               executions,
               first_load_time,
          sql_text
              from v$sqlarea
          where parsing_user_id !=0
          order by
             buffer_gets/decode(executions,null,1,0,1,executions) desc ) c
   where rownum < 11;
  
2..--Top 10 by Buffer Gets:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        buffer_gets, executions, buffer_gets/executions "Gets/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE buffer_gets > 10000
 ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;
3...-Top 10 by Physical Reads:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        disk_reads, executions, disk_reads/executions "Reads/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE disk_reads > 1000
 ORDER BY disk_reads DESC)
WHERE rownum <= 10;

4. Top 10 by Executions:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        executions, rows_processed, rows_processed/executions "Rows/Exec",
        hash_value,address
   FROM V$SQLAREA
  WHERE executions > 100
 ORDER BY executions DESC)
WHERE rownum <= 10;
  
5. Top 10 by Parse Calls:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        sharable_mem, executions, hash_value,address
   FROM V$SQLAREA
  WHERE sharable_mem > 1048576
 ORDER BY sharable_mem DESC)
WHERE rownum <= 10;
6. Top 10 by Version Count:

set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
        version_count, executions, hash_value,address
   FROM V$SQLAREA
  WHERE version_count > 20
 ORDER BY version_count DESC)
WHERE rownum <= 10;

7. Long running sql:

set lines 200
col opname for a35
col progress for a15
col progress for a8
col TARGET for a45
col USERNAME for a8
select a.sid,a.serial#,b.username,b.opname,LAST_UPDATE_TIME,round(b.SOFAR*100 / b.TOTALWORK,2) || '%' as progress,
b.TIME_REMAINING,b.target from gV$SESSION_LONGOPS b,gV$SESSION a where a.sid=b.sid and TIME_REMAINING <> 0 order by 6;

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
set lines 200
set pages 200
col opname for a35
col progress for a15
col progress for a8
col TARGET for a45
select distinct a.inst_id,a.sid,a.serial#,b.username,b.opname,a.logon_time,round(b.SOFAR*100 / b.TOTALWORK,2) || '%' as progress,
b.TIME_REMAINING,b.target from gV$SESSION_LONGOPS b,gV$SESSION a where a.inst_id=b.inst_id and a.sid=b.sid and TIME_REMAINING <> 0 order by a.inst_id,logon_time  ;