DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, January 27, 2012

Non ASM to ASM Conversion

Before Proceeding to process, please know what is ASM and how to use it.

1. Set the ASM environment and create diskgroup
oracle@AIX01]. oraenv
ORACLE_SID] +ASM1

sqlplus /as sysasm

sql> Create diskgroup DATA EXTERNAL REDUNDANCY DISK '/dev/rhdiskpower47','/dev/rhdiskpower48';

2. set the db environment and shutdown the database

oracle@AIX01]. oraenv
ORACLE_SID] ORCL

oracle@AIX01] sqlplus '/as sysdba'
sql> shu immediate

3. Make the database to nomount state through RMAN
$ Rman target /
RMAN>STARTUP NOMOUNT

4. move controlfile to asm through RMAN
RMAN> restore controlfile to ‘+DATA’ from ‘/oracledb/control1/ctrl_1.ctl’;

5. set the asm environment and find the controlfile from asm diskgroup
$ . oraenv
+ASM1
$ asmcmd
ASMCMD> find --type CONTROLFILE +DATA *
+DATA/ORCL/CONTROLFILE/current.256.734177857
ASMCMD>exit
--Copy the name and full path of controlfile

6. change the control_files parameter from non-asm to asm and restart the database post the db parameter changes.

SQL> alter system set control_files='+DATA/ORCL/CONTROLFILE/current.256.734727317' scope=spfile;
SQL>shutdown immediate;
SQL>startup mount;

7. Now check the control file
SQL> select name from v$controlfile;
+DATA/ORCL/CONTROLFILE/current.256.734727317

8. Now moving the Datafiles to ASM

. oraenv
ORCL

$ rman target /
RMAN>configure device type disk parallelism 4;
RMAN> backup as copy database format '+DATA';
RMAN>switch database to copy ;
RMAN> ALTER DATABASE OPEN;

9. Connect to sqlplus as sysdba and check for all data files

SQL> select file_name from dba_data_files;

10. Now move the Redo Log files to ASM

Please check for which logfile group is active and proceed with caution
NOTE :In this case Logfile group 6 is in active/current state so switching logfile so that it becomes inactive.
SQL> alter database drop logfile group 1;
SQL> alter database add logfile group 1 ('+DATA) size 300M;
SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2 ('+DATA') size 300M;
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 ('+DATA') size 300M;

11. Move SPfile to ASM
Connect to sqlplus as sysdba
Sqlplus “ / as sysdba”
SQL> create pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpfile.ora' from spfile;
SQL> shutdown immediate;
Edit the pfile to change the data file creation place
*.db_create_file_dest=+DATA
SQL>startup pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpfile.ora';
SQL> CREATE SPFILE='+DATA' FROM PFILE='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initpfile.ora';

12. find the spfile and Modify the pfile point to spfile
. oraenv
+ASM1

$asmcmd
ASMCMD> find --type parameterfile +DATA *
+DATA/ORCL/PARAMETERFILE/spfile.354.734189649

.oraenv
ORCL

##remove all entries and add the spfile parameter
$cd $ORACLE_HOME/dbs
vi initpfile.ora
spfile='+DATA/ORCL/PARAMETERFILE/spfile.354.734189649'

13. startup with pfile
sqlplus '/as sysdba'
shu immediate
startup pfile=?/dbs/initpfile.ora

14. moving/creating temporary tablespace in ASM

Creating TEMPFILEs In ASM

SQL> select name, bytes from v$tempfile;
SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;
SQL> alter database default temporary tablespace temp1;
SQL> drop tablespace temp including contents;
SQL> create temporary tablespace temp tempfile SIZE 10120M extent management local uniform size 1M;
SQL> alter database default temporary tablespace temp;
SQL> drop tablespace temp1 including contents;
SQL> select name from v$tempfile;

ACL Creation and Access in 11g

1. To Drop ACL



exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL('utlmailpkg.xml');




2. Create and Assign ACL TO DBUSER and Assign it all host.
-- BELOW CODE WILL CREATE NETWORK_SERVICES.xml ACL file and REQUESTS DBUSER AS INPUT, TO WHICH ACL ACCESS NEEDS TO BE PROVIDED



BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL


( acl =>'NETWORK_SERVICES.xml',


description => 'ACL Access',


principal => '&&DBUSER',


is_grant => true,


privilege => 'connect');


DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE


( acl => 'NETWORK_SERVICES.xml',


principal => '&DBUSER',


is_grant => true,


privilege => 'resolve');


DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL


( acl => 'NETWORK_SERVICES.xml',


host => '*');


COMMIT;


END;


/


3. Adding ACL privilege to ANOTHER DBUSER
-- BELOW CODE WILL REQUEST FOR ANOTHER DBUSER NAME TO WHICH ACL ACCESS NEEDS TO BE PROVIDED


exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'NETWORK_SERVICES.xml', principal => '&&ANOTHERDBUSER', is_grant => true, privilege => 'connect');


exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'NETWORK_SERVICES.xml', principal => '&ANOTHERDBUSER', is_grant => true, privilege => 'resolve');


4. smtp_out_server parameter settings
alter system set smtp_out_server='localhost:25' scope=BOTH;


5. To Test the ACL Access
conn DBUSER/
SQL> select utl_http.request('http://www.oracle.com') from dual;


UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM')
--------------------------------------------------------------------------------
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


--------------------------------------------------------------------------------
var ssUrlPrefix = "/us/"; var ssUrlType = "2"; var g_navNode_Path = new Array(); g_navNode_Path[0] = '8'; var g_ssSourceNodeId = "8"; var g_ssSourceSiteId = "ocomen";



UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM')
--------------------------------------------------------------------------------


6. it should provide the above result,


if it is failure, then it throws
ORA-24247: network access denied by access control list (ACL)



7. In case you get ORA-29278: SMTP transient error: 421 Service not available.
The error shows that the smtp port (25) is not accessible.


( as per parameter smtp_out_server=localhost:25 )
When you do telnet 25 port , it will throw connection refused error..


[prod@linux dbhome_1]$ telnet localhost 25


Trying 127.0.0.1...telnet: connect to address 127.0.0.1: Connection refused


telnet: Unable to connect to remote host: Connection refused


IF that is the case Then, For linux ,please start sendmail service, from root
[root@linux ~]# service sendmail start
Once it is started you should be able get response from telnet and ORA-29278 error will be recovered
[prod@linux dbhome_1]$ telnet localhost 25


Trying 127.0.0.1...Connected to linux.mydb.com (127.0.0.1).


Escape character is '^]'.


220 linux.mydb.com ESMTP Sendmail 8.13.1/8.13.1; Thu, 26 Jan 2012 07:10:21 -0800



Enabling HTTP Header Trace from Firefox

Live HTTP headers is a public add-in for Mozilla browsers that captures HTTP information during navigation, including URLs, cookies, and other headers. Information gathered using this tool can be saved to a file, ready for upload to Oracle Support.

LiveHTTP headers is available here as an xpi, under the installation tab: http://livehttpheaders.mozdev.org/

a:- Install the downloaded executable
b:- Open a new Mozilla Firefox session c:- Enable LiveHTTPheaders by choosing "Tools / LiveHTTPHeaders".

This will bring up a Window of HTTP header data that will accumulate as you navigate from place to place.

d:- Replicate the problem.

e:- Click the "Save All" button , Analyse the issue / upload the file created to service request.

Enabling HTTP Header Trace from Internet Explorer

HTTPHeaders is an Explorer Bar for Microsoft Internet Explorer that will display the HTTP Headers sent and received by Internet Explorer as you surf the web. It can be useful in debugging various web-development problems related to cookies, caching, redirection, proxy servers, etc. It is nicely integrated with Microsoft Internet Explorer so you'll be able to see the HTTP headers as they are sent and received from the web server, without any need to fire up an extra tool set. Information gathered using this tool can be saved to a file, ready for upload to Oracle Support.

Please download ieHTTPHeaders from here: http://www.blunck.se/iehttpheaders.html

a:- Install the downloaded executable in the Client PC

b:- Open a new Internet Explorer session

c:- Activate the IEHTTPHeader window by selecting "View/Explorer Bar/ieHTTPHeaders"
From your Internet Explorer menu bar. This will bring up a Window of HTTP header data that will accumulate as you navigate from place to place.

d:- Replicate the problem.

e:- Click the "HEADERS VIEW" tab in the ieHTTPHeaders window

f:- Right click the "HEADERS VIEW" page and choose save

e:- Analyse the Issue/Upload the file to SR.

Enabling IAS debug in R12 Applications

1. cd $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/config
1. vi oc4j.properties
2. Add: values
AFLOG_ENABLED=true
AFLOG_LEVEL=statement
AFLOG_MODULE=%
AFLOG_FILENAME=/tmp/1.log
2. Copy the $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf to a backup file.
3. Edit the $INST_TOP/ora/10.1.3/Apache/Apache/conf/httpd.conf and change Apache to run in debug.
Example:
=======
# LogLevel: Control the number of messages logged to the error_log.
# Possible values include: debug, info, notice, warn, error, crit, # alert, emerg.
#
LogLevel debug
=======
4. Restart the Apache and OACore service ($ADMIN_SCRIPTS_HOME/adoacorectl.sh).
5. The following logs will be generated
->$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
->$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/log.xml
->$APPLRGF/javacache.log
->$LOG_HOME/ora/10.1.3/Apache/access_log
->$LOG_HOME/ora/10.1.3/Apache/error_log
->$LOG_HOME/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.err
->$LOG_HOME/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.out
->/tmp/1.log

Wednesday, January 18, 2012

Procedure for generating the DDL statements of the database objects.

There are two parameters used in the specified object,
 
1. Object Type        ==> eg: FUNCTION,PROCEDURE,PACKAGE,TABLE,VIEW etc.,
2. Directory Name  ==> Specify the directory name where the output to be stored.

Usage -  DS_GET_DDL_STATEMENT(<Object type>,<Directory Name>)

Objects to be created:
----------------------
 CREATE TABLE "SMF_APEXRPS"."DS_ERROR_TABLE"
   (    "COMNAME" VARCHAR2(200 BYTE),
    "COMDDDL" LONG
   );


Create or Replace
PROCEDURE DS_GET_DDL_STATEMENT(P_OBJECTTYPE VARCHAR2,PDIR VARCHAR2)
AS
V_DDL LONG;
V_ERRMSG VARCHAR2(500);
vInHandle  utl_file.file_type;
CURSOR C1 IS
SELECT * FROM USER_OBJECTS WHERE object_type=P_OBJECTTYPE;
BEGIN
FOR I IN C1 LOOP
select
dbms_metadata.get_ddl(P_OBJECTTYPE,I.OBJECT_NAME)  INTO V_DDL
from dual;
  vInHandle := utl_file.fopen(PDIR, I.OBJECT_NAME||'.txt', 'W');
  IF utl_file.is_open(vInHandle) THEN
   utl_file.put_line(vInHandle, V_DDL, FALSE);
    utl_file.fflush(vInHandle);
    utl_file.fclose_all;
 END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
V_ERRMSG:=SQLERRM;
INSERT INTO DS_ERROR_TABLE VALUES('ERROR',V_ERRMSG);
END;

Friday, January 6, 2012

How to Tell if the Oracle Software is 32-Bit or 64-Bit

How to find if the Oracle Client Software is 32-Bit or 64-Bit ?

here you can have some ways to find it.

You can find whether Oracle Client Software is 32-Bit or 64-Bit by using the following :


1.Check the sqlplus binary

% file $ORACLE_HOME/bin/sqlplus

64-bit will show 64-bit.
32-bit will show 32-bit or not specify wordsize

2.Check for the directories :

$ORACLE_HOME/lib32
$ORACLE_HOME/lib

% ls -l $ORACLE_HOME/lib32
% ls -l $ORACLE_HOME/lib


If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib exist, then it is 64 bit client.
If you have only $ORACLE_HOME/lib you need to use method 1 as there are client versions (11.2)where $ORACLE_HOME/lib32 directory does not exist on 64-bit client installations.

Refernce:
How To Find If The Oracle Client Software Is 32-Bit Or 64-Bit on UNIX platforms [ID 434295.1]

NOTE:119707.1 - How to Tell if the Oracle Software is 32-Bit or 64-Bit

Data Guard Log Shipping Fails With Error Ora-16191 In 11g

Issue:

PING[ARC1]: Heartbeat failed to connect to standby ‘STANDBY’. Error is 16191.
Sat Aug 06 02:06:52 2011
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191

Things Verified :

1. Create password file for sys in both primary and standby
2. Login as a sys from primary to standby and vice versa
Sqlplus sys@STANDBY as sysdba
3. tnsping also works from primary to standby and vice versa

Solution :

In 11g oracle comes with the strong password authentication, so we have to use ignorecase parameter while creating password file in both primary and standby, otherwise we have to set SEC_CASE_SENSITIVE_LOGON=FALSE (Dynamic parameter).

1. orapwd file=orapw$ORACLE_SID password=***** entries=5 ignorecase=Y
2. alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=both;

REP-3000: Internal error starting Oracle Toolkit

Issue:

Recently we had issue with Concurrent requests which having print format of PDF or XML, and the exact
error is "REP-3000: Internal error starting Oracle Toolkit"

Solution:
1. Log on as root on the UNIX box.
2. Start The VNC server:
Example:
$ vncserver :0
3. Set the DISPLAY environment variable for Oracle Reports by performing the
following steps:
a. $ set DISPLAY=hostname:0; export DISPLAY
b. $ xhost +
c. Set the DISPLAY parameter in adcmctl.sh, adrepctl.sh, adfrmctl.sh and gsmstart.sh to
hostname:0
d. Restart concurrent manager and report server

How to Verify:
1. Login with system administrator responsibility
2. Concurrent – program-Define-Query Active Users-Change Print format to PDF- save
3. Run the Active Users Concurrent Request
4. It should be completed without any issue.

Monday, January 2, 2012

Provides a report on the top segments (in bytes) grouped by Segment Type

-- +==================================================================+
-- PURPOSE: Provides a report on the top segments (in bytes) grouped by Segment Type
-- +==================================================================+

SET LINESIZE 155
SET PAGESIZE 9999
SET VERIFY   OFF

BREAK ON segment_type SKIP 1
COMPUTE SUM OF bytes ON segment_type

COLUMN segment_type        FORMAT A20                HEADING 'Segment Type'
COLUMN owner               FORMAT A15                HEADING 'Owner'
COLUMN segment_name        FORMAT A30                HEADING 'Segment Name'
COLUMN partition_name      FORMAT A30                HEADING 'Partition Name'
COLUMN tablespace_name     FORMAT A20                HEADING 'Tablespace Name'
COLUMN bytes               FORMAT 9,999,999,999,999  HEADING 'Size (in bytes)'
COLUMN extents             FORMAT 999,999,999        HEADING 'Extents'

SELECT
    a.segment_type      segment_type
  , a.owner             owner
  , a.segment_name      segment_name
  , a.partition_name    partition_name
  , a.tablespace_name   tablespace_name
  , a.bytes             bytes
  , a.extents           extents
FROM
    (select
         b.segment_type
       , b.owner
       , b.segment_name
       , b.partition_name
       , b.tablespace_name
       , b.bytes
       , b.extents
     from
         dba_segments b
     order by
         b.bytes desc
    ) a
WHERE
    rownum < 101
ORDER BY
    segment_type, bytes desc, owner, segment_name
/

To check for index fragmentation

 -- +------------------------------------------------------------------------------+
-- | PURPOSE  : To check for index fragmentation. As a rule of thumb if 10-15%  |
-- |            of the table data changes, then you should consider rebuilding the index  |
-- +-------------------------------------------------------------------------------+

ANALYZE INDEX &&index_name VALIDATE STRUCTURE;

COL name         HEADING 'Index Name'          FORMAT a30
COL del_lf_rows  HEADING 'Deleted|Leaf Rows'   FORMAT 99999999
COL lf_rows_used HEADING 'Used|Leaf Rows'      FORMAT 99999999
COL ibadness     HEADING '% Deleted|Leaf Rows' FORMAT 999.99999

SELECT
    name
  , del_lf_rows
  , lf_rows - del_lf_rows lf_rows_used
  , TO_CHAR( del_lf_rows /(DECODE(lf_rows,0,0.01,lf_rows))*100,'999.99999') ibadness
FROM   index_stats
/

prompt
prompt Consider rebuilding any index if % of Deleted Leaf Rows is > 20%
prompt

undefine index_name

Backup Schedule Template

Report free space fragmentation

-- +----------------------------------------------------------------------------+
-- | PURPOSE  : Report free space fragmentation.                                             |
-- |            THIS SCRIPT MUST BE RUN AS THE SYS USER!!!                  |
-- +----------------------------------------------------------------------------+

connect / as sysdba

CREATE OR REPLACE VIEW free_space (
    tablespace
  , pieces
  , free_bytes
  , free_blocks
  , largest_bytes
  , largest_blks
  , fsfi
  , data_file
  , file_id
  , total_blocks
)
AS
SELECT
    a.tablespace_name
  , COUNT(*)
  , SUM(a.bytes)
  , SUM(a.blocks)
  , MAX(a.bytes)
  , MAX(a.blocks)
  , SQRT(MAX(a.blocks)/SUM(a.blocks))*(100/SQRT(SQRT(count(a.blocks))))
  , UPPER(b.file_name)
  , MAX(a.file_id)
  , MAX(b.blocks)
FROM
    sys.dba_free_space  a
  , sys.dba_data_files  b
WHERE
    a.file_id = b.file_id
GROUP BY
    a.tablespace_name,  b.file_name
/

CLEAR COLUMNS
SET LINESIZE  120
SET PAGESIZE  9999
SET FEEDBACK  off
SET VERIFY    off

BREAK ON tablespace SKIP 2 ON REPORT

COMPUTE SUM OF  total_blocks  ON tablespace
COMPUTE SUM OF  free_blocks   ON tablespace
COMPUTE SUM OF  free_blocks   ON report
COMPUTE SUM OF  total_blocks  ON report

COLUMN tablespace     HEADING "Tablespace"    FORMAT a15
COLUMN file_id        HEADING File#           FORMAT 99999
COLUMN pieces         HEADING Frag            FORMAT 9999
COLUMN free_bytes     HEADING 'Free Byte'
COLUMN free_blocks    HEADING 'Free Blk'      FORMAT 999,999,999
COLUMN largest_bytes  HEADING 'Biggest Bytes'
COLUMN largest_blks   HEADING 'Biggest Blks'  FORMAT 999,999,999
COLUMN data_file      HEADING 'File Name'     FORMAT a45
COLUMN total_blocks   HEADING 'Total Blocks'  FORMAT 999,999,999


SELECT
    tablespace
  , data_file
  , pieces
  , free_blocks
  , largest_blks
  , file_id
  , total_blocks
FROM
    free_space
/


DROP VIEW free_space
/

List all currently connected user sessions ordered by current PGA size

-- +-------------------------------------------------------------------------------------+
-- | PURPOSE  : List all currently connected user sessions ordered by current PGA size  |
-- +-------------------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid                     FORMAT 99999          HEADING 'SID'
COLUMN serial_id               FORMAT 999999         HEADING 'Serial#'
COLUMN session_status          FORMAT a9             HEADING 'Status'          JUSTIFY right
COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid                  FORMAT 9999999        HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING 'Session Program' TRUNC
COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'
COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'
COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'
COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'

prompt
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Current PGA Size          |
prompt +----------------------------------------------------+

SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , sstat1.value         session_pga_memory
  , sstat2.value         session_pga_memory_max
  , sstat3.value         session_uga_memory
  , sstat4.value         session_uga_memory_max
FROM
    v$process  p
  , v$session  s
  , v$sesstat  sstat1
  , v$sesstat  sstat2
  , v$sesstat  sstat3
  , v$sesstat  sstat4
  , v$statname statname1
  , v$statname statname2
  , v$statname statname3
  , v$statname statname4
WHERE
      p.addr (+)            = s.paddr
  AND s.sid                 = sstat1.sid
  AND s.sid                 = sstat2.sid
  AND s.sid                 = sstat3.sid
  AND s.sid                 = sstat4.sid
  AND statname1.statistic#  = sstat1.statistic#
  AND statname2.statistic#  = sstat2.statistic#
  AND statname3.statistic#  = sstat3.statistic#
  AND statname4.statistic#  = sstat4.statistic#
  AND statname1.name        = 'session pga memory'
  AND statname2.name        = 'session pga memory max'
  AND statname3.name        = 'session uga memory'
  AND statname4.name        = 'session uga memory max'
ORDER BY session_pga_memory DESC
/

List all currently connected user sessions ordered by Logical I/O

-- +--------------------------------------------------------------------------------+
-- | PURPOSE  : List all currently connected user sessions ordered by Logical - I/O |
-- +--------------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid               FORMAT 99999            HEADING 'SID'
COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'
COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a18              HEADING 'Session Program' TRUNC
COLUMN session_machine   FORMAT a8               HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN logical_io        FORMAT 999,999,999,999  HEADING 'Logical I/O'
COLUMN physical_reads    FORMAT 999,999,999,999  HEADING 'Physical Reads'
COLUMN physical_writes   FORMAT 999,999,999,999  HEADING 'Physical Writes'

prompt
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by Logical I/O               |
prompt +----------------------------------------------------+

SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , sstat1.value
    + sstat2.value         logical_io
  , sstat3.value         physical_reads
  , sstat4.value         physical_writes
FROM
    v$process  p
  , v$session  s
  , v$sesstat  sstat1
  , v$sesstat  sstat2
  , v$sesstat  sstat3
  , v$sesstat  sstat4
  , v$statname statname1
  , v$statname statname2
  , v$statname statname3
  , v$statname statname4
WHERE
      p.addr (+)            = s.paddr
  AND s.sid                 = sstat1.sid
  AND s.sid                 = sstat2.sid
  AND s.sid                 = sstat3.sid
  AND s.sid                 = sstat4.sid
  AND statname1.statistic#  = sstat1.statistic#
  AND statname2.statistic#  = sstat2.statistic#
  AND statname3.statistic#  = sstat3.statistic#
  AND statname4.statistic#  = sstat4.statistic#
  AND statname1.name        = 'db block gets'
  AND statname2.name        = 'consistent gets'
  AND statname3.name        = 'physical reads'
  AND statname4.name        = 'physical writes'
ORDER BY logical_io DESC
/

List all currently connected user sessions ordered by CPU time.

-- +----------------------------------------------------------------------------+
-- | DATABASE : Oracle |
-- | PURPOSE : List all currently connected user sessions ordered by CPU time. |
-- +----------------------------------------------------------------------------+

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN serial_id FORMAT 999999 HEADING 'Serial#'
COLUMN session_status FORMAT a9 HEADING 'Status' JUSTIFY right
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN os_pid FORMAT 9999999 HEADING 'O/S PID' JUSTIFY right
COLUMN session_program FORMAT a20 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a14 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN cpu_value FORMAT 999,999,999,999 HEADING 'CPU'

prompt
prompt +----------------------------------------------------+
prompt | User Sessions Ordered by CPU |
prompt +----------------------------------------------------+

SELECT
s.sid sid
, s.serial# serial_id
, lpad(s.status,9) session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, lpad(p.spid,7) os_pid
, s.program session_program
, lpad(s.machine,14) session_machine
, sstat.value cpu_value
FROM
v$process p
, v$session s
, v$sesstat sstat
, v$statname statname
WHERE
p.addr (+) = s.paddr
AND s.sid = sstat.sid
AND statname.statistic# = sstat.statistic#
AND statname.name = 'CPU used by this session'
ORDER BY cpu_value DESC
/