Starting and Stopping Oracle Enterprise Manager 11g Grid Control

OMS_HOME and AGENT_HOME you can find /etc/oratab

[oracle@smrgccadmdl1 ~]$ grep -E 'oms|agent' /etc/oratab
*:/d01/oracle/product/Middleware/oms11g:N
*:/d01/oracle/product/Middleware/agent11g:N
so OMS_HOME would be /d01/oracle/product/Middleware/oms11g   &
   AGENT_HOME would be /d01/oracle/product/Middleware/agent11g

NOTE: The Oracle Enterprise Manger 11g Grid Control install process puts a script called gcstartup in /etc/init.d that will stop and start the Oracle Management Service and Agent on OS startup/shutdown. It does not start or stop the repository database. If you would like to stop the automated startup/shutdown of Grid Control services place comments in front of the OMS and AGENT home directories in the /etc/oratab file or remove the script /etc/init.d/gcstartup.

Stopping Oracle Enterprise Manager 11g Grid Control

Stop the Oracle Management Service
From the OMS_HOME directory run the following to stop the OMS and WebTier
export ORACLE_HOME=/d01/oracle/product/Middleware/oms11g
export ORACLE_SID=svrpgcc
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin:$PATH
services[oracle@smrgccadmdl1 ~]$ cd $OMS_HOME/bin

[oracle@smrgccadmdl1 bin]$ emctl stop oms -all (emctl stop oms -force)

Oracle Enterprise Manager 11g Release 1 Grid Control  

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Stopping WebTier...

WebTier Successfully Stopped

Stopping Oracle Management Server...

Oracle Management Server Successfully Stopped

Oracle Management Server is Down

Note:

if you do not include the –all flag the HTTP services for the WebLogic Server will not be shutdown. Stop the Oracle Management Agent
From the AGENT_HOME directory run the following to stop the Agent
export ORACLE_HOME=/d01/oracle/product/Middleware/agent11g
export ORACLE_SID=svrpgcc
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/opmn/bin:$PATH

[oracle@smrgccadmdl1 ~ ]$ cd $AGENT_HOME/bin
[oracle@smrgccadmdl1 bin]$pwd
/d01/oracle/product/Middleware/agent11g/bin

[oracle@smrgccadmdl1 bin]$ emctl stop agent

 

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Stopping agent ... stopped.

Stop the repository DataBase:

[oracle@smrgccadmdl1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 28 05:44:14 2012
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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Stop the Listener :

[oracle@smrgccadmdl1 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-AUG-2012 05:55:48
 
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.108.101.111)(PORT=1521)))
The command completed successfully
Starting Oracle Enterprise Manager 11g Grid Control :

1). Start the Listener

2). Start the Reposioty DataBase

3). Start teh OMS & WebTier Services(Oracle Management Agent--> emctl start oms)

4). start the AGENT_HOME (emctl start agent)

5). https://smrgccadmdl1.domain:7799/em

Note: check url for installation

http://www.oracledistilled.com/grid-control/installing-oracle-enterprise-manager-grid-control-11gr1-on-linux/

Querys for DBLOCKS & Size of the Table :

------------------------------:To find BDLOCKS :------------------------------------------

SELECT s.inst_id, NVL (s.username, 'Internal') "Database User", m.SID,
s.serial#, p.spid "DB OS Process", m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server", s.process "Apps OS process", m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name", SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;
To know Particilar Table Size :
SQL> SELECT tbl.owner TABLE_OWNER,
tbl.TABLE_NAME ,
tbl.TABLESPACE_NAME,
ceil((BLOCKS*8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)) "Data lower than HWM in MB"
FROM  DBA_TABLES tbl
where table_name like 'XX_EMF_ERROR_DETAILS'; 

-----------------------------------Temp Usage :-----------------------------------

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

---------------------To know the Product  Version & Patchset level---------------------

select decode(nvl(a.APPLICATION_short_name,'Not Found'),
        'SQLAP','AP','SQLGL','GL','OFA','FA',
        'Not Found','id '||to_char(fpi.application_id),
        a.APPLICATION_short_name) apps,
        decode(fpi.status,'I','Installed','S','Shared',
               'N','Inactive',fpi.status) status,
        fpi.product_version,
        nvl(fpi.patch_level,'-- Not Available --') Patchset,
        to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
  and fpi.oracle_id = o.oracle_id(+)
order by 1,2
/
APPS                                               STATUS    PRODUCT_VERSION                PATCHSET                       Update Date
-------------------------------------------------- --------- ------------------------------ ------------------------------ -----------------
AD                                                 Shared    12.0.0                         R12.AD.B.2                     05-Sep-2010

-------------------------To find Conc-req  Completion ------------------------

SELECT fcr.REQUEST_ID,fc.USER_CONCURRENT_PROGRAM_NAME,TO_CHAR(fcr.actual_start_date,'DD-MON HH24:MI:SS') actual_start_date,TO_CHAR(fcr.actual_completion_date,'DD-MON HH24:MI:SS') actual_completion_date,fcr.STATUS_CODE
FROM FND_CONCURRENT_REQUESTS fcr,
                FND_CONCURRENT_PROGRAMS_TL fc
WHERE fcr.phase_code='C'
                AND fc.concurrent_program_id=fcr.concurrent_program_id
                AND fc.USER_CONCURRENT_PROGRAM_NAME='&Conc-req_name' order by fcr.actual_start_date ;

-------------
TOP sqls :
-------------
set linesize 1000
set pagesize 1000
col sql for a50
SELECT sql,buffer_gets, executions
FROM (SELECT hash_value,address,substr(sql_text,1,40) sql,
            buffer_gets, executions, buffer_gets/executions "Gets/Exec"
      FROM V$SQLAREA
      WHERE buffer_gets > 100000 AND executions > 10
      ORDER BY buffer_gets DESC)
      WHERE rownum <= 5
/

HOW TO DO TABLE AND INDEX REORGANIZATION [ID 736563.1]

1). Alter table move (to another tablespace, or same tablespace).
alter table table_name move; This would do the table reorg.

2).
SQL> alter table table_name enable row movement;
Table altered.
There are 2 ways of using this command.
Method 1. Break in two parts: In first part rearrange rows and in second part reset the HWM.
Part 1: Rearrange (All DML's can happen during this time)
SQL> alter table table_name shrink space compact;
Table altered.
Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
SQL> alter table table_name shrink space;
Table altered.
Method 2. All in one go:
SQL> alter table table_name shrink space; (Both rearrange and restting HWM happens in one statement)
The above mentioned methods are online reorg and would not cause any impact.
Oracle 10g describes the reorganization operations that are possible using the ONLINE clause in the
SQL> CREATE/ALTER INDEX and TABLE statements.
ALTER TABLE emp MOVE ONLINE;
CREATE INDEX emp.ename.idx ON emp(ename) ONLINE;
ALTER INDEX emp.ename_idx REBUILD ONLINE;
ALTER INDEX emp.ename_idx COALESCE;
Reclaiming Unused Space

==============    Truncate Table   ===================

TRUNCATE TABLE XXASL.XX_EMF_ERROR_DETAILS;
ALTER TABLE XXASL.XX_EMF_ERROR_DETAILS ENABLE ROW MOVEMENT;
ALTER TABLE XXASL.XX_EMF_ERROR_DETAILS SHRINK SPACE;
ALTER INDEX XXASL.IDX$$_2A2D0001 REBUILD;

=============  Indix is Valid / Not    ===================
SQL> select INDEX_NAME,STATUS from dba_indexes where INDEX_NAME='&indx_name' and owner='&Owner';
If  the status is UNUSABLE then fire the below comman then check the status will bechanged to  VALID

SQL> alter index owner.<indx_name> rebuild online;

............................................&&&..................................................

How To activate the Output Post Processor :

The Profile Option "Concurrent: GSM Enabled" must be set to Y
Profile--> System -->Profile : Concurent: GSM Enabled (find)
Then:
1. Login  to Apps with sysadmin responsibility
2. Navigate to:  Concurrent -> Managers -> Define
3. Query for
          Manager = 'Output Post Processor'
    or   Short Name = FNDCPOPP
4. Check the checkbox "Enable" .
5. Click on 'Work Shifts button
6. see Work Shift of the OPP and
    Set
            Processes = 1
    and
            Parameters = oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
    and
            Sleep Second = 30
7. Save
8. Navigate to:_ Concurrent -> Managers -> Administer
9. Make sure the Output Post Processor Status is not 'Deactivate'
    Set Status to 'Activate' if necessary.
10. Bounce The Concurrent Managers using
    adcmctl.sh
-----------------------------------

Querys for TableSpace info & Temp Usage

+++++++++++++++To know Tablespace Strecture on backend ++++++++++++++

SELECT dbms_metadata.get_ddl('TABLESPACE',tablespace_name )
FROM dba_tablespaces
WHERE tablespace_name IN ( '&your_tablespace' );

@@@@@@@@@---To Find Temp Usage on particular User---@@@@@@@@@
set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr
/

+++++++++++
 SQL> select tablespace_name, file_id, bytes_used, bytes_free  from v$temp_space_header ;
SQL> select tablespace_name,extent_size,total_extents,total_blocks,max_size,max_blocks from v$sort_segment;
SQL>  select tablespace_name, file_id, bytes_used, bytes_free  from v$temp_space_header;
*******************To Know Application Version:**************************

SQL> select product_group_id, product_group_name, release_name,
        product_group_type, argument1
  from fnd_product_groups;

+++++++++++++& Product Version &++++++++++++++++++

select decode(nvl(a.APPLICATION_short_name,'Not Found'),

        'SQLAP','AP','SQLGL','GL','OFA','FA',

        'Not Found','id '||to_char(fpi.application_id),

        a.APPLICATION_short_name) apps,

        decode(fpi.status,'I','Installed','S','Shared',

               'N','Inactive',fpi.status) status,

        fpi.product_version,

        nvl(fpi.patch_level,'-- Not Available --') Patchset,

        to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"

from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi

where fpi.application_id = a.application_id(+)

  and fpi.oracle_id = o.oracle_id(+)

order by 1,2

/

(OR)

SQL> SELECT TABLESPACE_NAME TBSP_NAME, USED_SPACE, TABLESPACE_SIZE TBSP_SIZE, USED_PERCENT FROM SYS.DBA_TABLESPACE_USAGE_METRICS;

--------------------To Find TableSpace Usage------------------------

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

*********Undo TableSpace :

SQL> select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b where a.saddr=b.ses_addr;

==================To know Workflow Info===================
wfver.sql script
• Required Init Parameters
• Files and Packages versions
• Queues status
• Workflow Services and Agent Listeners status
SQL>spool wfver.lst
SQL>@$FND_TOP/sql/wfver.sql
…
SQL>spool off

==================================================
++++++++Workflow

column corrid format A50
column state format A15
select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid,
decode(wfe.state,0,' 0 = Ready',1,'1 = Delayed',2,'2 = Retained',
3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,
count(*) COUNT from applsys.wf_deferred wfe group by wfe.corrid, wfe.state;

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

To Analyze the DISK I/o's :

----------------------------

prompt SESSIONS PERFORMING HIGH I/O > 50000
select p.spid, s.sid,s.process cli_process, s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,lpad(t.sql_text,30) "Last SQL"
from v$session s, v$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
t.disk_reads > 10000
order by t.disk_reads desc;

----------->>>>>>>>>>>><<<<<<<<<<<<----------------

Gather Schema Statistics - Oracle Applications

Gather  Schema  Statistics :

Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

The cost-based optimization (CBO)  uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan.

As a general rule, run Gather Schema Statistics under the following circumstances:

1.  After there has been a significant change in data in either content or volume.

2.  After importing data.

3.  Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.

4.  Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.

Estimate Percentage / Modification threshold defines the percentage which should be used to initiate gather stats for those objects which have actually changed beyond the threshold.

 The default is 10% (i.e. meaning any table which has changed via DML more than 10%, stats will be collected, otherwise it will be skipped).

How to run Gather Schema Statistics concurrent program:

1. Log on to Oracle Applications with

    Responsibility = System Administrator

2. Submit Request Window

    Navigate to: Concurrent > Requests

3. Query for the Gather Schema Statistics

4. Enter the appropriate parameters. This can be run for specific schemas by specifying the schema name or entering  ‘ALL’  to gather statistics for every schema in the database

5. Submit the Gather Schema Statistics program

Parameters :

------------------

Schema Name:  Schema for which statistics are to be gathered. Specify ALL for all Oracle Applications schemas

Percent:  The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100

Degree:  The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum ofparallel_max_servers and cpu_count.

Backup Flag:  NOBACKUP is used, then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.

Restart Request ID:  In the case where the Gather Schema Statistics run fails due to whatever reasons, the concurrent request can be re-submitted and it will pick up where the failed run left off, if you provide the concurrent request_id of the failed run.

History Mode:  Last Run – History records for each object are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the object. This is the default behavior

Gather Options:  GATHER: All tables and indexes of the schema schema name are selected for stats gathering. This is the default

Modifications Threshold:  Applicable only to GATHER AUTO and LIST AUTO Options

Invalidate Dependent Cursors:  This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. By default, dependent cursors are invalidated.

How to Gather the Statistics of Custom Schema when we submit the concurrent request called Gather Schema Statistics :

When we submit Gather Schema Stats with Parameter  ALL, concurrent request will complete successfully, and DBAs will not realize that custom schemas are not analyzed.

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SYS',estimate_percent => 50,cascade => TRUE ,options => 'GATHER AUTO');

Sql > select count(table_name)  from  dba_tables  where  last_analyzed  is  not null  and  owner= <custom_schema_name>;

Here you realize none of the tables in custom schema are analyzed.

Gather Schema Statistics program gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.

Reason:  

Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables

ie  FND_ORACLE_USERID  and  FND_APPLICATIONS_TL
However , when Gather schema statistics is submitted it uses the below query to get schema information
Sql > select distinct upper(oracle_username) sname
          from fnd_oracle_userid a,
         fnd_product_installations b
         where a.oracle_id = b.oracle_id
         order by sname;
Note : When custom schemas are created the entry is not made in  FND_PRODUCT_INSTALLATIONS  and hence it is not picked up in the above query.
Solution :
How can we make an entry in fnd_product_installations so that it is picked up by Gather Schema Stats. Follow below steps
Go to the Responsibility called Alert Manager and Navigate to the form -> Installations under Systems Menu.
Define custom application in this form. Go to the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.
Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schemas as well.
******************
Check out MetaLink Docs for more information:
- 141532.1 “How to Gather Schema Statistics for Oracle Applications 11i”
- 163208.1 “bde_last_analyzed.sql - Verifies Statistics for all installed Apps modules 11.5”
- 156968.1 “coe_stats.sql - Automates CBO Stats Gathering using FND_STATS and Table sizes”

DataBase Info & Helth Check....!!

******Total Size of The Database******

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual ;

==============================================================
++++++++++++++++++++++++++++Total Size of The Database
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
=========================================================================
+++++++++++++++++++++++++++++++largest objects in the database
SELECT * FROM ( select  SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME  from   dba_segments order by 3 desc  )  WHERE ROWNUM <= 10 ;

col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
=====================================
Show all connected users
set lines 100 pages 999
col ID format a15
select username
,      sid || ',' || serial# "ID"
,      status
,      last_call_et "Last Activity"
from   v$session
where  username is not null
order by status desc
,        last_call_et desc
/
============================
Time since last user activity
set lines 100 pages 999
select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et
/
=======================
Sessions sorted by logon time
set lines 100 pages 999
col ID  format a15
col osuser format a15
col login_time format a14
select  username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
===========================
Show user info including os pid
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order  by to_number(p.spid)
/
============================
Show a users current sql
Select sql_text
from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value
        from v$session
        where username like '&username')
/
==============================
Session status associated with the specified os process id
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from  V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
=====================================
All active sql
set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
  for x in
   (select username||'('||sid||','||serial#||') ospid = '|| process ||
    ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    sql_address,
    sql_hash_value
   from v$session
   where status = 'ACTIVE'
   and rawtohex(sql_address) <> '00'
   and username is not null ) loop
   for y in (select sql_text
   from v$sqlarea
   where address = x.sql_address ) loop
   if ( y.sql_text not like '%listener.get_cmd%' and
    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   end if;
  end loop;
 end loop;
end;
/
==============================
Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
/
==============================
List open cursors per user
set pages 999
select  sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value
/
(or)
set lines 100 pages 999
select count(hash_value) cursors
,      sid
,      user_name
from   v$open_cursor
group by
       sid
,      user_name
order by
       cursors
/
======================================