Oracle System Privileges

General Information
Note: System privileges are privileges that do not relate to a specific schema or object.

Data Dictionary Objects Related To System Privileges
all_sys_privs session_privs user_sys_privs
dba_sys_privs system_privilege_map

Administer
Administer Any SQL Tuning Set
Administer Database Trigger (database level trigger)
Administer Resource Manager
Administer SQL Management Object
Administer SQL Tuning Set
Flashback Archive Administrator
Grant Any Object Privilege
Grant Any Privilege
Grant Any Role
Manage Scheduler
Manage Tablespace
Advanced Queuing
Dequeue Any Queue
Enqueue Any Queue
Manage Any Queue

Advisor Framework
Advisor
Administer SQL Tuning Set
Administer Any SQL Tuning Set
Administer SQL Management Object
Alter Any SQL Profile
Create Any SQL Profile
Drop Any SQL Profile

Alter Any Privileges
Alter Any Cluster
Alter Any Cube
Alter Any Cube Dimension
Alter Any Dimension
Alter Any Evaluation Context
Alter Any Index
Alter Any Indextype
Alter Any Library
Alter Any Materialized View
Alter Any Mining Model
Alter Any Operator
Alter Any Outline
Alter Any Procedure
Alter Any Role
Alter Any Rule
Alter Any Rule Set
Alter Any Sequence
Alter Any SQL Profile
Alter Any Table
Alter Any Trigger
Alter Any Type

Alter Privileges
Alter Database
Alter Profile
Alter Resource Cost
Alter Rollback Segment
Alter Session
Alter System
Alter Tablespace
Alter User
Analyze Privileges
Analyze Any
Analyze Any Dictionary
Audit Privileges
Audit Any
Audit System
Backup Privileges
Backup Any Table
Change Privilege
Change Notification
Clusters
Alter Any Cluster
Create Cluster
Create Any Cluster
Drop Any Cluster
Comment Privileges
Comment Any Mining Model
Comment Any Table
Contexts
Create Any Context
Drop Any Context

Create Any Privileges
Create Any Cluster
Create Any Context
Create Any Cube
Create Any Cube Build Process
Create Any Cube Dimension
Create Any Dimension
Create Any Directory
Create Any Evaluation Context
Create Any Index
Create Any Indextype
Create Any Job
Create Any Library
Create Any Materialized View
Create Any Measure Folder
Create Any Mining Model
Create Any Operator
Create Any Outline
Create Any Procedure
Create Any Rule
Create Any Rule Set
Create Any Sequence
Create Any SQL Profile
Create Any Synonym
Create Any Table
Create Any Trigger
Create Any Type
Create Any View

Create Privileges
Create Cluster
Create Cube
Create Cube Build Process
Create Cube Dimension
Create Database Link
Create Dimension
Create Evaluation Context
Create External Job
Create Indextype
Create Job
Create Library
Create Materialized View
Create Measure Folder
Create Mining Model
Create Operator
Create Procedure
Create Profile
Create Public Database Link
Create Public Synonym
Create Role
Create Rollback Segment
Create Rule
Create Rule Set
Create Sequence
Create Session
Create Synonym
Create Table
Create Tablespace
Create Trigger
Create Type
Create User
Create View
Database
Alter Database
Alter System
Audit System
Database Links
Create Database Link
Create Public Database Link
Drop Public Database Link
Debug
Debug Any Procedure
Debug Connect Session
Delete
Delete Any Cube Dimension
Delete Any Measure Folder
Delete Any Table
Dimensions
Alter Any Dimension
Create Any Dimension
Create Dimension
Drop Any Dimension
Directories
Create Any Directory
Drop Any Directory

Drop Any Privileges
Drop Any Cluster
Drop Any Context
Drop Any Cube
Drop Any Cube Build Process
Drop Any Cube Dimension
Drop Any Dimension
Drop Any Directory
Drop Any Evaluation Context
Drop Any Index
Drop Any Indextype
Drop Any Library
Drop Any Materialized View
Drop Any Measure Folder
Drop Any Mining Model
Drop Any Operator
Drop Any Outline
Drop Any Procedure
Drop Any Role
Drop Any Rule
Drop Any Rule Set
Drop Any Sequence
Drop Any SQL Profile
Drop Any Synonym
Drop Any Table
Drop Any Trigger
Drop Any Type
Drop Any View
Drop Privileges
Drop Profile
Drop Public Database Link
Drop Public Synonym
Drop Rollback Segment
Drop Tablespace
Drop User
Evaluation Context
Alter Any Evaluation Context
Create Any Evaluation Context
Create Evaluation Context
Drop Any Evaluation Context
Execute Any Evaluation Context

Execute Any Privileges
Execute Any Class
Execute Any Evaluation Context
Execute Any Indextype
Execute Any Library
Execute Any Operator
Execute Any Procedure
Execute Any Program
Execute Any Rule
Execute Any Rule Set
Execute Any Type
Export & Import
Export Full Database
Import Full Database
Fine Grained Access Control
Exempt Access Policy
File Group
Manage Any File Group
Manage File Group
Read Any File Group
Flashback
Flashback Any Table
Flashback Archive Administrator
Force
Force Any Transaction
Force Transaction
Indexes
Alter Any Index
Create Any Index
Drop Any Index
Indextype
Alter Any Indextype
Create Any Indextype
Create Indextype
Drop Any Indextype
Execute Any Indextype
Insert
Insert Any Cube Dimension
Insert Any Measure Folder
Insert Any Table
Job Scheduler
Create Any Job
Create External Job
Create Job
Execute Any Class
Execute Any Program
Manage Scheduler
Libraries
Alter Any Library
Create Any Library
Create Library
Drop Any Library
Execute Any Library
Locks
Lock Any Table
Materialized Views
Alter Any Materialized View
Create Any Materialized View
Create Materialized View
Drop Any Materialized View
Flashback Any Table
Global Query Rewrite
On Commit Refresh
Query Rewrite
Mining Models
Alter Any Mining Model
Comment Any Mining Model
Create Any Mining Model
Create Mining Model
Drop Any Mining Model
Select Any Mining Model
OLAP Cubes
Alter Any Cube
Create Any Cube
Create Cube
Drop Any Cube
Select Any Cube
Update Any Cube
OLAP Cube Build
Create Any Cube Build Process
Create Cube Build Process
Drop Any Cube Build Process
Update Any Cube Build Process
OLAP Cube Dimensions
Alter Any Cube Dimension
Create Any Cube Dimension
Create Cube Dimension
Delete Any Cube Dimension
Drop Any Cube Dimension
Insert Any Cube Dimension
Select Any Cube Dimension
Update Any Cube Dimension
OLAP Cube Measure Folders
Create Any Measure Folder
Create Measure Folder
Delete Any Measure Folder
Drop Any Measure Folder
Insert Any Measure Folder
Operator
Alter Any Operator
Create Any Operator
Create Operator
Drop Any Operator
Execute Any Operator
Outlines
Alter Any Outline
Create Any Outline
Drop Any Outline
Procedures
Alter Any Procedure
Create Any Procedure
Create Procedure
Drop Any Procedure
Execute Any Procedure
Profiles
Alter Profile
Create Profile
Drop Profile
Query Rewrite
Global Query Rewrite
Query Rewrite
Refresh
On Commit Refresh
Resumable
Resumable
Roles
Alter Any Role
Create Role
Drop Any Role
Grant Any Role
Rollback Segment
Alter Rollback Segment
Create Rollback Segment
Drop Rollback Segment
Scheduler
Manage Scheduler
Select
Select Any Cube
Select Any Cube Dimension
Select Any Dictionary
Select Any Mining Model
Select Any Sequence
Select Any Table
Select Any Transaction
Sequence
Alter Any Sequence
Create Any Sequence
Create Sequence
Drop Any Sequence
Select Any Sequence
Session
Alter Resource Cost
Alter Session
Create Session
Restricted Session
Synonym
Create Any Synonym
Create Public Synonym
Create Synonym
Drop Any Synonym
Drop Public Synonym
Sys Privileges
SYSDBA
SYSOPER
Tablespace
Alter Tablespace
Create Tablespace
Drop Tablespace
Manage Tablespace
Unlimited Tablespace

Table
Alter Any Table
Backup Any Table
Comment Any Table
Create Any Table
Create Table
Delete Any Table
Drop Any Table
Flashback Any Table
Insert Any Table
Lock Any Table
Select Any Table
Update Any Table
Transaction
Force Any Transaction
Force Transaction
Trigger
Administer Database Trigger
Alter Any Trigger
Create Any Trigger
Create Trigger
Drop Any Trigger
Types
Alter Any Type
Create Any Type
Create Type
Drop Any Type
Execute Any Type
Under Any Type
Update
Update Any Cube
Update Any Cube Build Process
Update Any Cube Dimension
Update Any Table
Under
Under Any Table
Under Any Type
Under Any View
User
Alter User
Become User
Create User
Drop User
View
Create Any View
Create View
Drop Any View
Flashback Any Table
Merge Any View
Under Any View

Granting System Privileges
Grant A Privilege GRANT TO ;
GRANT create table TO uwclass;

Revoking System Privileges
Revoke A Single Privilege REVOKE FROM ;
REVOKE create table FROM uwclass;

Determine User Privs

This query will list the system privileges assigned to a user SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('%&uname%')
UNION
SELECT grantee, granted_role
FROM dba_role_privs
UNION
SELECT grantee, privilege
FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

or

SELECT path
FROM (
SELECT grantee,
sys_connect_by_path(privilege, ':')||':'||grantee path
FROM (
SELECT grantee, privilege, 0 role
FROM dba_sys_privs
UNION ALL
SELECT grantee, granted_role, 1 role
FROM dba_role_privs)
CONNECT BY privilege=prior grantee
START WITH role = 0)
WHERE grantee IN (
SELECT username
FROM dba_users
WHERE lock_date IS NULL
AND password != 'EXTERNAL'
AND username != 'SYS')
OR grantee='PUBLIC'
/

Dangerous Demo

Execute Any Procedure SELECT *
FROM dba_sys_privs
WHERE privilege LIKE '%CREATE ANY PROC%';

conn owb/owb

CREATE OR REPLACE PROCEDURE .do_sql(sqlin VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE sqlin;
END;
/

BEGIN
.do_sql('drop table emp cascade constraints');
END;
/

Oracle Object Privileges

General Information
Note: While the data dictionary objects are named tab, as in table, the privileges that can be granted and revoked extend to other object types including functions, packages, and procedures.
Data Dictionary Objects Related To Object Privileges
objauth$ objpriv$
dba_col_privs all_col_privs user_col_privs
- all_col_privs_made user_col_privs_made
- all_col_privs_recd user_col_privs_recd
dba_tab_privs all_tab_privs user_tab_privs
- all_tab_privs_made user_tab_privs_made
- all_tab_privs_recd user_tab_privs_recd
column_privileges table_privileges table_privilege_map
Object Privileges
0 ALTER 9 SELECT 22 UNDER
1 AUDIT 10 UPDATE 23 ON COMMIT REFRESH
2 COMMENT 11 REFERENCES 24 QUERY REWRITE
3 DELETE 12 EXECUTE 26 DEBUG
4 GRANT 16 CREATE 27 FLASHBACK
5 INDEX 17 READ 28 MERGE VIEW
6 INSERT 18 WRITE 29 USE (for 11gR2 - not 11gR1)
7 LOCK 20 ENQUEUE 30 FLASHBACK ARCHIVE
8 RENAME 21 DEQUEUE
Note: Privilege 29, USE, was introduced in preparation for a new feature that will first appear in 11gR2.

Granting Object Privileges

Grant A Single Privilege GRANT ON TO
conn uwclass/uwclass

CREATE TABLE test (
testcol VARCHAR2(20));

GRANT SELECT ON test TO abc;

set linesize 100
col grantee format a30
col table_name format a30
col privilege format a20

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Grant Multiple Privileges GRANT ON TO
conn uwclass/uwclass

GRANT INSERT, DELETE ON test TO abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Grant All Privileges GRANT ALL ON TO
conn abc/abc

GRANT ALL ON test TO uwclass;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn uwclass/uwclass

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Grant Execute GRANT EXECUTE ON TO
conn uwclass/uwclass

GRANT EXECUTE ON getosuser TO abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoking Object Privileges

Revoke A Single Privilege REVOKE ON FROM
conn uwclass/uwclass

REVOKE SELECT ON test FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoke Multiple Privileges REVOKE ON FROM
conn uwclass/uwclass

REVOKE INSERT, DELETE ON test FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoke All Privileges REVOKE ALL ON FROM
conn uwclass/uwclass

REVOKE ALL ON test FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Revoke Execute REVOKE EXECUTE ON FROM
conn uwclass/uwclass

REVOKE EXECUTE ON getosuser FROM abc;

SELECT grantee, table_name, privilege
FROM user_tab_privs_made;

conn abc/abc

SELECT grantor, table_name, privilege
FROM user_tab_privs_recd;

Granting Column Privileges
Grant Column Privileges GRANT () ON TO ;
GRANT UPDATE (first_name, last_name) ON person TO uwclass;

Revoking Column Privileges
Revoke Column Privilege REVOKE () ON FROM ;
REVOKE UPDATE (first_name, last_name) ON person FROM uwclass;

Object Privilege Related Query
Show privileges by object

set linesize 121
col select_priv format a10
col insert_priv format a10
col update_priv format a10
col delete_priv format a10

SELECT table_name, grantee,
MAX(DECODE(privilege, 'SELECT', 'SELECT')) AS select_priv,
MAX(DECODE(privilege, 'DELETE', 'DELETE')) AS delete_priv,
MAX(DECODE(privilege, 'UPDATE', 'UPDATE')) AS update_priv,
MAX(DECODE(privilege, 'INSERT', 'INSERT')) AS insert_priv
FROM dba_tab_privs
WHERE grantee IN (
SELECT role
FROM dba_roles)
GROUP BY table_name, grantee;

use full scripts

-- /www/doc/sql/useful_misc.sql
-- $Id: useful-misc.sql,v 1.1.3.1 2001/08/29 05:31:19 andy Exp $
--
-- Useful stuff culled largely from Joe Trezzo's Oracle PL/SQL book.
-- See also the other useful_*.sql files, for the longer code.
--
-- by atp@arsdigita.com, 2000-07-17

-- Note that a lot (but not all) of this stuff is already covered by
-- the current version of Cassandracle, but I haven't made any attempt
-- to compare the two or pare down this list.


-- References:
--
-- Trezzo, Joseph C.; _Oracle PL/SQL Tips and Techniques_;
-- Osborne/McGraw-Hill, 1999, www.osborne.com; ISBN: 0-07-882439-9.
-- [Very useful book, though fat and poorly organized.]
--
-- http://www.tusc.com/tusc/nonhtml/plsql_bookcode.zip
-- Code from Trezzo's book.
--
-- Greenspun, Philip; "Tips for Using Oracle";
-- http://www.arsdigita.com/asj/oracle-tips


-- Useful Oracle shells:
-- $ORACLE_HOME/bin/sqlplus
-- $ORACLE_HOME/bin/svrmgrl
-- $ORACLE_HOME/bin/lsnrctl
-- At aD, do NOT use lsnrctl to restart the listener!  Instead, use:
--   sudo /etc/init.d/ora8-net8
-- If you do not, Oracle will not be able to find any external
-- libraries you may be using.  (Something to do with Environment
-- variables, but I haven't looked into it.)

-- Oracle Error Messgage Index:
--   http://oradoc.photo.net/ora816/server.816/a76999/index.htm

-- You probably want to put these in your login.sql file.
-- (When you start up sqlplus, it will execute any login.sql file it
-- find in your current directory.)
set linesize 200
set pagesize 1000
set serveroutput on size 1000000
begin dbms_output.enable(1000000); end;
/


-- To turn off interpretation of & in Sqlplus:
set def off

-- To retrieve a view definition from the database:
-- Sqlplus truncates long type columns, so do:
--
select VIEW_NAME, TEXT_LENGTH from user_views where view_name = upper('your_view_name_here');
--
-- Then, do "set long n" in Sqlplus.  Make sure n is >= TEXT_LENGTH.
set long 1000;
select TEXT from user_views where view_name = upper('your_view_name_here');


-- ** Regular Expressions **
-- The OWA_PATTERN pl/sql regexp package is part of the Oracle PL/SQL Web
-- Toolkit.  Install the whole toolkit with:
--   $ORACLE_HOME/webdb30/admin/plsql/owa/owains.sql
-- or read about OWA_PATTERN here:
--   http://technet.oracle.com/files/search/search.htm?OWA_PATTERN
--   $ORACLE_HOME/webdb30/admin/plsql/owa/pubpat.sql
--   $ORACLE_HOME/webdb30/admin/plsql/owa/privpat.sql


-- Find the table, column, etc. for a constraint.
select
 uc.constraint_name
,uc.table_name
,ucc.column_name
,uc.SEARCH_CONDITION
from user_constraints uc, user_cons_columns ucc
where uc.constraint_name = ucc.constraint_name
 and ucc.column_name = 'LIMIT_PRICE'
 --and uc.table_name in ('ORDERS','ORDERS_AUDIT')
;


-- Get list of invalid objects.
--   Trezzo c. pg. 207, 451
select
 STATUS ,OBJECT_TYPE ,CREATED ,LAST_DDL_TIME ,TIMESTAMP ,OBJECT_NAME
from user_objects
--from dba_objects
where status != 'VALID'
order by
 timestamp
 --object_name
;
 

-- Get list of disabled triggers.
--   Trezzo pg. 452
-- File: 9_5.sql
SELECT owner, trigger_name, trigger_type, triggering_event, table_owner||'.'||table_name
FROM dba_triggers
WHERE status <> 'ENABLED'
--  AND owner  = 'PLSQL_USER'
ORDER BY owner, trigger_name;



-- According to Xuequn (Robert) Xu xux@arsdigita.com 2000/12/01 in:
--   http://www.arsdigita.com/ticket/issue-view.tcl?msg_id=74326
-- The below queries do NOT show locks on packages.  However you can
-- check the v$access view like this:
select
username, osuser, process, type, terminal,
to_char(logon_time, 'YYYY-MM-DD HH24:MI') as logon_time,
(sysdate-logon_time)*24 as hours_ago
from v$session
order by hours_ago desc, process ;


-- Find Oracle locks:
--   Originally from Philip's Oracle tips document.
-- Usually very fast, but I've seen it hang on a really hosed Oracle
-- with hundreds of resource locks.
set linesize 180
 column username  format a18
 column program   format a32
 column machine   format a12
 column n_seconds format a6
select
 s.username ,s.sid ,s.serial#
 ,ltrim(w.seconds_in_wait) as n_seconds
 ,process ,machine ,terminal ,program
from  v$session s, v$session_wait w
where s.sid = w.sid
 and s.sid in (select sid from v$lock where lmode=6)
order by s.username ,s.sid ,s.serial#
;

-- alter system kill session 'SID,SERIAL#';

-- Find Oracle locks and what they're doing.
set linesize 180
 column username  format a18
 column program   format a32
 column machine   format a12
 column n_seconds format a6
select
 s.username ,s.sid ,s.serial#
 ,ltrim(w.seconds_in_wait) as n_seconds
 ,process ,machine ,terminal ,program
 ,sql.sql_text
from  v$session s, v$session_wait w
 ,v$sqltext sql
where s.sid = w.sid
 and s.sid in (select sid from v$lock where lmode=6)
 and sql.address    = s.sql_address
 and sql.hash_value = s.sql_hash_value
 and upper(username) like 'MUNIVERSAL%'
order by s.username ,s.sid ,s.serial# ,sql.piece
;

-- See which users are waiting for a lock.
-- This is fast.
SELECT s.username, s.serial#, s.lockwait
FROM   v$session s
where s.lockwait is not null
order by s.lockwait, s.username ;

-- Who's waiting for a lock, and what SQL is he executing?
-- This is fast.
SELECT s.username, s.serial#, s.lockwait
 ,sql.sql_text
FROM   v$session s
 ,v$sqltext sql
where s.lockwait is not null
 and sql.address    = s.sql_address
 and sql.hash_value = s.sql_hash_value
 and upper(username) like 'MUNIVERSAL%'
order by s.lockwait, s.username ,sql.piece ;




-- What are users currently doing?
set linesize 180
SELECT s.username, s.serial#, sql.sql_text
FROM   v$session s, v$sqltext sql
WHERE  sql.address    = s.sql_address
 AND  sql.hash_value = s.sql_hash_value
 --and upper(s.username) like 'USERNAME%'
order by s.username ,s.sid ,s.serial# ,sql.piece ;


-- What users are currently locked, and what are they doing?
-- Really slow, I never let it finish.
--   Trezzo pg. 475
-- File: 9_21.sql
SELECT s.username, s.serial#, l.id1, st.sql_text
FROM   v$session s, v$lock l, v$sqltext st
 -- It's the joining v$session to v$lock that seems to really slow things down.
WHERE  s.lockwait   = l.kaddr
AND    st.address    = s.sql_address
AND    st.hash_value = s.sql_hash_value;

-- What users are currently locked?
-- Really slow.
SELECT s.username, s.serial#, l.id1
FROM   v$session s, v$lock l
WHERE  s.lockwait   = l.kaddr;

-- Who is CAUSING a user to be locked?
-- Really slow - took almost 8 minutes to return "no rows selected".
--   Trezzo pg. 476
-- File: 9_22.sql
SELECT a.serial#, a.sid, a.username, b.id1, c.sql_text
FROM   v$session a, v$lock b, v$sqltext c
WHERE  b.id1 IN (
   SELECT DISTINCT e.id1
   FROM v$session d, v$lock e
   WHERE d.lockwait   = e.kaddr )
 AND     a.sid        = b.sid
 AND     c.hash_value = a.sql_hash_value
 AND     b.request    = 0;


-- Check init.ora parameters.
--   Trezzo pg. 453
-- File: 9_6.sql
SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM v$parameter
ORDER BY name;

-- Check some particular DBMS_JOB related init.ora parameters.
--   Trezzo pg. 576
-- File: 12_9.sql
SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM   v$parameter
WHERE UPPER(name) IN ('JOB_QUEUE_PROCESSES','JOB_QUEUE_INTERVAL')
ORDER BY NAME;


-- What database objects are being accessed right now?
-- Returns a potentially massive list of stuff.
--   Trezzo pg. 474
-- File: 9_20.sql
SELECT s.sid, s.username, a.owner, a.object, a.type
FROM   v$session s, v$access a
WHERE  s.sid = a.sid;


-- What statements are executing in each rollback segment?
--   Trezzo pg. 477
-- File: 9_23.sql
SELECT a.name, b.xacts tr, c.sid, c.serial#, c.username, d.sql_text
FROM   v$rollname a, v$rollstat b, v$session c,
      v$sqltext d,v$transaction e
WHERE  a.usn            = b.usn   
AND    b.usn            = e.xidusn
AND    c.taddr          = e.addr
AND    c.sql_address    = d.address
AND    c.sql_hash_value = d.hash_value
ORDER BY a.name, c.sid, d.piece;


-- Show all open cursors  :
column user_name format a15
column osuser    format a15
column machine   format a15
select user_name ,status ,osuser --,machine
 ,a.sql_text
from v$session b, v$open_cursor a
where a.sid = b.sid
order by status ,user_name ,osuser ,a.sql_text ;

-- Show all open cursors, with full text of SQL - slower:
select user_name ,status ,osuser --,machine
 ,c.sql_text
from v$session b, v$open_cursor a,  v$sqlarea c
where a.sid = b.sid
 and c.address = a.address ;
order by status ,user_name ,osuser ,c.sql_text ;

-- Show how many open cursors are currently in use:
select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
 and a.statistic#= 3 ;


-- A list of some useful Oracle V$ views
--   Trezzo pg. 486


-- Jobs running - useful views:
--   Trezzo pg. 577
-- DBA_JOBS, USER_JOBS, DBA_JOBS_RUNNING

-- Submit and control jobs with DBMS_JOB.
--   Trezzo pg. 579 - 583

-- What jobs exist?
--   Trezzo pg. 578
-- File: 12_10.sql
SELECT what, job, priv_user,
      TO_CHAR(last_date, 'MM/DD/YYYY HH24:MI:SS') last,
      DECODE(this_date, NULL, 'NO', 'YES') running,
      TO_CHAR(next_date, 'MM/DD/YYYY HH24:MI:SS') next,
      interval, total_time, broken
FROM   dba_jobs
ORDER BY what;

-- What jobs are running right now?
--   Trezzo pg. 579
-- File: 12_11.sql
SELECT a.job, what,
      TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') now,
      TO_CHAR(a.this_date, 'mm/dd/yyyy hh24:mi:ss') this
FROM   dba_jobs_running a, dba_jobs b
WHERE  a.job = b.job;


-- Stuff about the Data Dictionary
--   Trezzo pg. 675 - 697


-- Go into svrmgrl, connect internal, and do these for your username,
-- so that you can run all the various queries above.  FYI, our Oracle
-- DBA says this is ok to do, to wit:
--
--     "Granting select privileges on dynamic performance views to
--     users is harmless. The "ad_cassandracle" role has almost all of
--     the read privileges on system tables and data dictionary
--     views. You can certainly do the same for [our ACS Oracle user
--     name]."

grant select on dba_objects      to your_oracle_username;
grant select on dba_triggers     to your_oracle_username;
grant select on dba_jobs_running to your_oracle_username;
grant select on dba_jobs         to your_oracle_username;
grant select on dba_dependencies to your_oracle_username;
grant select on v_$parameter     to your_oracle_username;
grant select on v_$session       to your_oracle_username;
grant select on v_$session_wait  to your_oracle_username;
grant select on v_$access        to your_oracle_username;
grant select on v_$lock          to your_oracle_username;
grant select on v_$sqltext       to your_oracle_username;
grant select on v_$rollname      to your_oracle_username;
grant select on v_$rollstat      to your_oracle_username;
grant select on v_$sqltext       to your_oracle_username;
grant select on v_$transaction   to your_oracle_username;
grant select on SYS.FILEXT$      to muniversaldev;
grant select on DBA_FREE_SPACE   to muniversaldev;
grant select on DBA_DATA_FILES   to muniversaldev;
grant select on SYS.DBA_SOURCE   to muniversaldev;


-- Data Dictionary Tables about Columns:
--user_coll_types
--user_tab_columns
--user_clu_columns
--user_col_comments
--user_col_privs
--user_cons_columns
--user_ind_columns
--user_tab_col_statistics
--user_trigger_cols
--user_updatable_columns

connect internal;
-- Grant a user read privileges on all tables in the database:
grant exp_full_database to username;
-- Grant read access to all v$ views, etc.:
grant select_catalog_role to username;


-- Note that you can use bind variables in pl/sql.  This makes using
-- queries out of an ACS 4.x AOLserver error log much easier.  E.g.:
variable user_id         number
variable db_code         varchar2(20)
variable db_access_level varchar2(20)
variable proteome_code   varchar2(20)
variable ip_address      varchar2(20)
variable hostname        varchar2(50)
begin
 :user_id         := 95321;
 :db_code         := 'human';
 :db_access_level := 'lite';
 :proteome_code   := 'proteome';
 :ip_address      := '207.46.131.91';
 :hostname        := 'microsoft.com';
end;
/
show errors


-- Here's how to escape the wildcard meanings of _ with like in:
select object_type  from acs_objects  where object_type like 'p\_%'  escape '\';


-- Heres something out of the BBoard, to find all contraints
-- relationships:
--
-- make a table containing on row per relationship
create table tmp_relations as
select
 c1.table_name parent,
 c1.constraint_name pk,
 c2.table_name child,
 c2.constraint_name fk,
 c2.r_constraint_name parent_pk
from
 user_constraints c1,
 user_constraints c2
where c1.constraint_type = 'P'
 and c2.constraint_type = 'R'
 and c2.r_constraint_name = c1.constraint_name
;
-- do a tree query to get complete depth
select child, level  from tmp_relations
start with parent = 'ACS_OBJECTS'
connect by parent = PRIOR child ;

explain plan for an sid

REM ------------------------------------------------------------------------------------------------
REM Author : edmurray
REM #DESC : Full sql text and execution plan given an Oracle sid
REM Usage : Input parameter: sql_hash_value
REM Description:
REM ------------------------------------------------------------------------------------------------

@plusenv
undef v_sid

col
col hash_value new_value v_hash_value
-- format 99999999999
col sql_text format a64 word_wrapped
break on hash_value

select sql_hash_value hash_value, module
from v$session
where sid = &&v_sid;


SELECT
t.sql_text
FROM v$sqltext t
WHERE t.hash_value = &&v_hash_value
ORDER BY t.piece
;

SET ECHO OFF
SELECT LPAD( ' ', 2 * ( LEVEL - 1 ) ) ||
DECODE( id, 0, operation || ' (Cost = ' || position || ')',
LEVEL - 1 || '.' || NVL( position, 0 ) ||
' ' || operation ||
' ' || options ||
' ' || object_name ||
' ' || object_node ) "Query Plan"
FROM (select distinct id,parent_id, operation,cost, position,options,object_name,object_node
FROM v$sql_plan
where hash_value = &&v_hash_value)
START WITH id = 0
CONNECT BY PRIOR id = parent_id
/

undef sql_hash_value

sessions script

rem ********************************************************************
rem Filename : showSessions.sql
rem Version : 1.01
rem Usage : SQL> @showSessions
rem Description : List all current user sessions and their SQL
rem
rem Author : Andrew Morris
rem Date : 17 May 98
rem Notes:
rem O/S Usr - Operating System Username for backend server process.
rem Srvr PID - O/S backend server process id.
rem Clint PID - O/S client process id (for local connections) or
rem - some sort of SQL*Net address (for remote connections).
rem SID_SERIAL - Session ID, Session Serial Number (reqd for kill session command).
rem TERMINAL - Terminal Name (if interactive session otherwise null).
rem Ora User - Oracle username.
rem STATUS - Session status ie Active, Inactive or killed.
rem Logon Time - Date & Time session first connected.
rem Idle - Hours & minutes since the session sent any SQL.
rem for active sessions this is when the current sql was submitted
rem for inactive session this is the amount of time since the last sql
rem LkSID - SID for the blocking session. ie the session that has obtained a
rem lock that the current session must wait for before continueing.
rem Wait - Hours & Mins that the session has been waiting for the lock to be
rem released.
rem
rem Modified
rem Date Author Reason
rem ********************************************************************
set pause ...more
set pagesize 24
set linesize 132
set numwidth 5
column SID_Serial format a10
column Idle format a5
column LkMins format 999
column Wait format a5

select substr(vp.username, 1, 8) "O/S Usr", vp.spid "Srvr PID", vs.process "Clint PID",
vs.sid||','||vs.serial# SID_Serial,
vs.terminal, substr(vs.username, 1, 10) "Ora User",
vs.status, substr(to_char(vs.LOGON_TIME, 'dd/mon hh:mi'), 1, 12) "Logon Time",
to_char(trunc(sysdate) + (vs.LAST_CALL_ET/(24*3600)), 'hh24:mi') "Idle",
vl2.sid "LkSID",
to_char(trunc(sysdate) + (vl1.ctime/(24*3600)), 'hh24:mi') "Wait"
from V$PROCESS vp, V$SESSION vs, V$LOCK vl1, V$LOCK vl2
where vp.addr = vs.paddr
and vs.lockwait = vl1.kaddr(+)
and vl1.id1=vl2.id1(+)
and vl2.lmode(+)!=0
and vs.paddr not in (select paddr from V$BGPROCESS) -- exclude background processes
order by 7, 9 -- Status, Idle Time
/

set pause on
col module format a20
break on SID_Serial SKIP 1 on status

select
vs.sid||','||vs.serial# SID_Serial,
vs.status, vs.module, st.sql_text
from V$SESSION vs, V$SQLTEXT st
where st.address=decode(vs.sql_address, hextoraw('00'), vs.prev_sql_addr, vs.sql_address)
and st.hash_value=decode(vs.sql_hash_value, 0, vs.prev_hash_value, vs.sql_hash_value)
and vs.paddr not in (select paddr from V$BGPROCESS) -- exclude background processes
order by vs.status, vs.last_call_et, 1, st.piece
/

set pause off
set numwidth 10
clear columns
clear breaks

user details

select rpad(username,10,' ') as "DB User",
to_char(logon_time,'hh24:mi Mon/dd') as "Logon Time",
initcap(status) as "Status",sid||','||serial# as "Session",process as "Process",
rpad(upper(substr(program,instr(program,'\',-1)+1)),25,' ') as "Program",
rpad(lower(osuser),10,' ') as "OS User",
rpad(initcap(machine),15,' ') as "Machine Name" from v$session
where username like upper('%&USERNAME%')
order by machine,program;

list all user lock mode and objects

col owner format a12
col object_name format a25
col ORACLE_USERNAME format a15
col OS_USER_NAME format a15
col OBJECT_TYPE format a15
set lines 140

select l.OBJECT_ID,o.owner,o.OBJECT_NAME,o.OBJECT_TYPE,SESSION_ID,ORACLE_USERNAME,OS_USER_NAME,LOCKED_MODE
from v$locked_object l,dba_objects o
where l.OBJECT_ID=o.OBJECT_ID;

Lookup database details for a given Unix process id

rem -----------------------------------------------------------------------
rem Filename: unixusr.sql
rem Purpose: Lookup database details for a given Unix process id
rem Date: 06-Oct-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set serveroutput on size 50000
set echo off feed off veri off

accept SID prompt 'Enter Oracle SID: '

DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select sid into v_sid
from sys.v_$session s
where sid = &&SID;
exception
when no_data_found then
dbms_output.put_line('Unable to find SID &&SID!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9999999999.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));

dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;

dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;

dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;

dbms_output.put_line('Connect Info:');
for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
end loop;

dbms_output.put_line('Locks:');
for c1 in ( select
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.object_name,
'TD', o.object_name,
'TM', o.object_name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_$lock l, dba_objects o
where sid = s.sid
and l.id1 = o.object_id(+) ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;

dbms_output.put_line('=====================================================================');

END;
/

undef SID

performace scripts

--------GEN KILL SESSION FOR INACTIVE/IDLE SESSIONS SINCE YESTERDAY------
SELECT 'ALTER SYSTEM KILL SESSION '||'''||SID||','||SERIAL#||''
from V$SESSION
WHERE TO_CHAR(LOGON_TIME,'YYYYDDMM HH24:MI') < TO_CHAR(SYSDATE-1,'YYYYDDMM HH24:MI')
AND USERNAME IS NOT NULL
============finding top CPU user SQL =============
SELECT /*+ RULE */ * FROM (
SELECT cpu_time, disk_reads, rows_processed, sql_text FROM v$sqlarea
ORDER BY cpu_time DESC )
WHERE rownum <6

-------by rows_processed, disk_reads (max io)
SELECT * FROM (
SELECT cpu_time, disk_reads, rows_processed, sql_text FROM v$sqlarea
ORDER BY disk_reads DESC )
WHERE rownum <6


--------by max time elapsed user---------------
SELECT * FROM (
SELECT cpu_time, disk_reads, rows_processed,elapsed_time sql_text FROM v$sqlarea
ORDER BY elapsed_time DESC )
WHERE rownum <6


------- By run time memory
SELECT * FROM (
SELECT cpu_time, runtime_mem, rows_processed,elapsed_time sql_text FROM v$sqlarea
ORDER BY elapsed_time DESC )
WHERE rownum <6

---------check morethan1sess by user/osuser
set lines 100
col username form a25
col osuser form a25
col machine form a25
SELECT username, osuser,machine, count(*) from v$session where username is not
null group by username, osuser,machine having count(*) >1

-------------------------------
----- check buffer cache hit ratio:

SELECT SID ,OPNAME,TARGET,SOFAR,TOTALWORK FROM V$SESSION_LONGOPS WHERE
TIME_REMAINING!='0' AND sofar--------
COL id FORMAT 999
COL parent_id FORMAT 999 HEADING "PARENT"
COL operation FORMAT a35 TRUNCATE
COL object_name FORMAT a30
ed
Wrote file afiedt.buf

SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
options operation, object_name
FROM (
SELECT id, parent_id, operation, options, object_name
FROM v$sql_plan
WHERE address = '&address'
AND hash_value = &hash_value
AND child_number = &child_number)
START WITH id = 0
CONNECT BY PRIOR id = parent_id
/

select distinct vs.sid, vs.serial#, vp.spid, vsql.child_number CN, vs.process pai, vs.username, vs.osuser, vs.machine, to_char(floor(vs.last_call_et/3600),'fm0000')||':'||
to_char(floor(mod(vs.last_call_et,3600)/60),'fm00')||':'||
to_char(mod(mod(vs.last_call_et,3600),60),'fm00')||' Hs' "ACTIVE SINCE", to_char(vs.logon_time,'DD-MON-YY HH24:MI:SS') Logon
from v$session vs, v$process vp, dba_kgllock dk, v$sql vsql
where vs.paddr=vp.addr
AND SQL_HASH_VALUE='&1'
AND DK.KGLLKUSE = VS.SADDR
AND DK.KGLLKHDL = VSQL.CHILD_ADDRESS
AND VS.SQL_ADDRESS = VSQL.ADDRESS
order by machine;

====================================================================================================---- TO FIND USERS LOCKING RESOURCES IN SAME ORDER (NOT DEADLOCKS),
--- SOLN: KILL THE LOCKING SESSION (SID)

select 'alter system kill session'||''''||sid||','||serial# ||''''||';'from v$session where sid=&sid1 AND USERNAME IS NOT NULL; --status='INACTIVE'

===================
---check the session wait on DB
select event, count(*) from v$session_wait group by event having count(*)>1;
=======================================================================================================================
============finding top CPU user SQL =============
SELECT * FROM (
SELECT cpu_time, disk_reads, rows_processed, sql_text FROM v$sqlarea
ORDER BY cpu_time DESC ) WHERE rownum <6;
=======================================================================================================================
-------by rows_processed, disk_reads (max io)
SELECT * FROM (SELECT cpu_time, disk_reads, rows_processed, sql_text FROM v$sqlarea ORDER BY disk_reads DESC )
WHERE rownum <6;
=======================================================================================================================
SELECT GROUP#,STATUS,TO_CHAR(FIRST_TIME,'DD-MON-YYYY hh:mi:ss') Time FROM V$LOG ORDER BY FIRST_TIME;
=======================================================================================================================
select count(*) from v$log_history where trunc(first_time)=trunc(sysdate);
=======================================================================================================================
===lONG RUNNING QUERY
SET LINESIZE 2000
col username form a10
col target form a24
col opname form a11
col program form a16
col terminal form a15
col sid form 999
select s.username,s.sid,s.program,s.terminal,OPNAME,TARGET,SOFAR ,TOTALWORK from v$session s,v$session_longops l where s.sid=l.sid and time_remaining!='0';
======================================================================================
==CURRENT SESSION ACTIVE
SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL AND STATUS='ACTIVE';
=======================================================================================================================
---Temp seg usages
col username form a15
col tablespace form a10
select s.username,s.sid,blocks,tablespace,segtype from v$tempseg_usage t ,v$session s where t.session_num=s.serial# order by extents;
=======================================================================================================================
------kill session
select 'alter system kill session'||''''||sid||','||serial# ||''''||';'from v$session where status='INACTIVE' AND USERNAME IS NOT NULL
=======================================================================================================================
=======================================================================================================================
--to check free space in SGA
SELECT pool,name,bytes/1024/1024 "mb" FROM V$SGASTAT WHERE NAME LIKE '%free memory%';
=======================================================================================================================

$$$$$$$$$$Log OPS $$$$$$$$$$$$$

SET LINESIZE 2000
col username form a10
col target form a24
col opname form a11
col program form a16
col terminal form a15
col sid form 999
col sql_text form a200
select s.username,s.sid,s.program,s.terminal,OPNAME,TARGET,SOFAR ,TOTALWORK,s.osuser,q.sql_text from v$session s,v$session_longops l,v$sqlarea q where s.sid=l.sid and s.SQL_ADDRESS=q.ADDRESS and time_remaining!='0'

=======================================================================================================================
-- to check blocker/waiter using v$lock in oracle 9i : works FAST:

SELECT /*+ RULE */ l1.sid, ' IS BLOCKING ', l2.sid
FROM v$lock l1, v$lock l2
WHERE l1.block =1
AND l2.request > 0
AND l1.id1=l2.id1
AND l1.id2=l2.id2;

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;


create view bsess as SELECT l1.sid SID, '||IS BLOCKING||' blk ,l2.sid SID2 FROM v$lock l1, v$lock l2 WHERE l1.block =1 AND l2.request > 0 AND l1.id1=l2.id1 AND l1.id2=l2.id2

create or replace view bsessv2 as SELECT s1.status,s1.serial# ,l1.sid, ' || IS BLOCKING || ' blk , l2.sid sid2 ,s2.serial# serial2,s2.status status2
FROM v$lock l1, v$lock l2 ,v$session s1,v$session s2 WHERE l1.block =1 AND l2.request > 0
AND l1.id1=l2.id1 AND l1.id2=l2.id2;


^^^^^^^^^^^^^^^^^ sql statement ^^^^^^^^^^^^^^^^^


Full sql text

select x.sql_text from v$session s,v$sqltext x where s.sql_address=x.address and s.sid = &which_sid
order by sid, piece asc;

select ses.sid SID,sqa.SQL_TEXT SQL from v$session ses, v$sqlarea sqa, v$process proc where ses.paddr=proc.addr and ses.sql_hash_value=sqa.hash_value and proc.spid='&ospid';

select sid,USERNAME,sql_text,to_char(LOGON_TIME,'dd-mon-yyyy HH24:MI'),status,machine,program from v$session,v$sqlarea
where sid =&sid1 and SQL_ADDRESS=ADDRESS;--hash_value=sql_hash_value


select sid,USERNAME,sql_text,LOGON_TIME from v$session,v$sqlarea where sid =&sid1 and SQL_ADDRESS=ADDRESS;--hash_value=sql_hash_value


select sid,serial# from v$session where process='';

select sql_text from v$sqlarea a, v$session b where a.hash_value = b.sql_hash_value
and b.sid = 2178

select ses.sid SID,ses.serial#,sqa.SQL_TEXT SQL from v$session ses, v$sqlarea sqa, v$process proc where ses.paddr=proc.addr and ses.sql_hash_value=sqa.hash_value and proc.spid=17480;

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

----FIND SID USING PROCESS ID(OS)
SELECT SID,SPID,S.USERNAME,S.STATUS FROM V$PROCESS P, V$SESSION S WHERE ADDR=PADDR AND SPID=&osid;

=======================================================================================================================
--------
COL id FORMAT 999
COL parent_id FORMAT 999 HEADING "PARENT"
COL operation FORMAT a35 TRUNCATE
COL object_name FORMAT a30
SELECT id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
options operation, object_name
FROM (
SELECT id, parent_id, operation, options, object_name
FROM v$sql_plan
WHERE address = '&address'
AND hash_value = &hash_value
AND child_number = &child_number)
START WITH id = 0
CONNECT BY PRIOR id = parent_id
/
=======================================================================================================================
select distinct vs.sid, vs.serial#, vp.spid, vsql.child_number CN, vs.process pai, vs.username, vs.osuser, vs.machine, to_char(floor(vs.last_call_et/3600),'fm0000')||':'||
to_char(floor(mod(vs.last_call_et,3600)/60),'fm00')||':'||
to_char(mod(mod(vs.last_call_et,3600),60),'fm00')||' Hs' "ACTIVE SINCE", to_char(vs.logon_time,'DD-MON-YY HH24:MI:SS') Logon
from v$session vs, v$process vp, dba_kgllock dk, v$sql vsql
where vs.paddr=vp.addr
AND SQL_HASH_VALUE='&1'
AND DK.KGLLKUSE = VS.SADDR
AND DK.KGLLKHDL = VSQL.CHILD_ADDRESS
AND VS.SQL_ADDRESS = VSQL.ADDRESS
order by machine;
=======================================================================================================================

select /*+ RULE */ l1.sid,'IS BLOCKING',l2.sid from v$lock l1,v$lock l2
where l1.block=1 and l2.request>0 and l1.id1=l2.id1 and l1.id2=l2.id2
=======================================================================================================================
select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/
*****************killing os level in unix where session marked as kill (in v$session (status)************
select username, sid, serial#, status from v$session where status='KILLED'

select p.spid,s.sid from v$process p, v$session s where p.addr=s.paddr and s.status='KILLED';

SELECT SID,SPID,S.USERNAME,S.STATUS,s.machine FROM V$PROCESS P, V$SESSION S WHERE ADDR=PADDR AND SPID=&osid;

SELECT p.spid,S.USERNAME,S.STATUS,s.machine FROM V$PROCESS P, V$SESSION S WHERE ADDR=PADDR AND s.sid=

select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_pid from v$session vs, v$process vp
where vs.paddr = vp.addr
/


If you get something like:

SID USERNAME OSUSER FG_PID BG_PID
---- --------------- ---------- --------- ---------
10 JULIAN JULIAN 1965:6969 1234

run in unix as the oracle user

kill -9 1234

Find put sid of unix process id which taking long time ang high cpu usage

select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_pid from v$session vs, v$process vp
where vs.paddr = vp.addr and vp.spid=


******************************************************************
JOB WAIT QUEUE's

select * from v$system_event where event like ‘%wait%’;
select s.sid, s.username, s.osuser, s.type, s.machine, s.program, p.program,
s.event from v$session s, v$process p where s.paddr = p.addr and s.event = 'jobq slave wait'
********************************************************************************
Waiting time by user

select sesion.sid, sesion.username,sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history, v$session sesion where active_session_history.sample_time between sysdate - 60/2880 and sysdate and active_session_history.session_id = sesion.sid group by sesion.sid, sesion.username order by 3;
****************************************************************************************************
Sql using more resourcec

select active_session_history.user_id, dba_users.username,sqlarea.sql_text, sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history,
v$sqlarea sqlarea, dba_users where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username order by 4;
***************************************************************************************************
Objects highest resources

select dba_objects.object_name, dba_objects.object_type, active_session_history.event, sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history, dba_objects
where active_session_history.sample_time between sysdate - 60/2880 and sysdate and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event order by 4;
****************************************************************************************************
select * from v$waitstat;
************************************
All Wait events

SELECT event, total_waits, time_waited, percent FROM (SELECT se.event, se.total_waits, se.time_waited,
(se.time_waited / total.total_waittime) * 100 percent FROM (SELECT sum (time_waited) total_waittime
FROM v$system_event WHERE event NOT IN ('pmon timer', 'ges pmon to exit', 'ges lmd and pmon to attach',
'ges cached resource cleanup', 'parallel recovery coordinator waits for cleanup of slaves', 'smon timer', 'index (re)build online cleanup',
'dispatcher timer', 'dispatcher listen timer', 'timer in sksawat', 'SQL*Net message to client', 'SQL*Net message to dblink', 'SQL*Net more data to client',
'SQL*Net more data to dblink', 'SQL*Net message from client','SQL*Net more data from client','SQL*Net message from dblink',
'SQL*Net more data from dblink','SQL*Net break/reset to client','SQL*Net break/reset to dblink','PL/SQL lock timer','rdbms ipc message',
'ges remote message','gcs remote message','jobq slave wait','Null event' )
AND time_waited > 10) total, v$system_event se WHERE se.event NOT IN ('pmon timer', 'ges pmon to exit', 'ges lmd and pmon to attach',
'ges cached resource cleanup', 'parallel recovery coordinator waits for cleanup of slaves', 'smon timer','index (re)build online cleanup',
'dispatcher timer','dispatcher listen timer', 'timer in sksawat', 'SQL*Net message to client', 'SQL*Net message to dblink',
'SQL*Net more data to client','SQL*Net more data to dblink','SQL*Net message from client','SQL*Net more data from client',
'SQL*Net message from dblink', 'SQL*Net more data from dblink','SQL*Net break/reset to client','SQL*Net break/reset to dblink','PL/SQL lock timer',
'rdbms ipc message', 'ges remote message','gcs remote message', 'jobq slave wait', 'Null event' )
AND se.time_waited > 10 ORDER BY percent DESC) WHERE ROWNUM < 10;

*****************************************************************************
Dynamic RMAN Views for Past and Current Jobs
col STATUS format a9
col hrs format 999.99
select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key

col ins format a10
col outs format a10
select SESSION_KEY,OPTIMIZED, COMPRESSION_RATIO, INPUT_BYTES_PER_SEC_DISPLAY ins,
OUTPUT_BYTES_PER_SEC_DISPLAY outs,TIME_TAKEN_DISPLAY from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

select * from V$RMAN_BACKUP_TYPE;
select output from v$rman_output where session_key = 4 order by recid;
select output from v$rman_output where session_key = 4 order by recid;
*************************************************************************************************
Database Growth
select to_char(creation_time, 'RRRR Month') "Month", sum(bytes)/1024/1024
"Growth in Meg" from sys.v_$datafile where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');
***************************************************************************************************
The following query shows the library cache latch information:

select name,gets,misses,sleeps from v$latch where name like 'library%';

To get the OS process id:

select a.name,pid from v$latch a , V$latchholder b
where a.addr=b.laddr and a.name = 'library cache%';

select count(*) number_of_waiters from v$session_wait w, v$latch l
where w.wait_time = 0 and w.event = 'latch free'
and w.p2 = l.latch# and l.name like 'library%';

It is also very useful to just select from v$session_wait to determine what
else is causing a slowdown:

select * from v$session_wait where event != 'client message'
and event not like '%NET%' and wait_time = 0 and sid > 5;

To determine the percentage of sql statement parse calls that find a cursor to
share you can execute the following:

select gethitratio from v$librarycache where namespace = 'SQL AREA';
To identify the SQL statements that are receiving a lot of parse calls execute
the following query:

select sql_text, parse_calls, executions from v$sqlarea
where parse_calls > 100 and executions < 2*parse_calls;

select name, value from v$sysstat where name like 'parse count%';

**********************************************************************************************************
setting Opatch path in UNIX

export PATH=$PATH:$ORACLE_HOME/OPatch
************************************************************************************************************
Jobq slave wait

select s.sid,p.spid,s.username, s.osuser, s.type, s.machine, s.program, p.program, s.event
from v$session s, v$process p where s.paddr = p.addr and s.event = 'jobq slave wait'
************************************************************************************************************

select s.username,b.sid, a.STATISTIC#, name, b.value from v$sesstat b, v$statname a, v$session s
where a.STATISTIC# = b.STATISTIC# and ( name like '%physical%' or name like '%consist%' )
and b.sid = s.sid and s.program not like 'oracle@%'
order by 2,4
************************************************************************************************************
enq: TX - row lock contention
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request
/

for which SQL currently is waiting to,

select sid, sql_text from v$session s, v$sql q where sid in (select sid from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

The blocking session is,

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
************************************************************************************************************
Latch Info

Select n.name, l.sleeps from v$latch l, v$latchname n where n.latch#=l.latch# and l.sleeps > 0 order by l.sleeps
************************************************************************************************************
JOB's

select job,SCHEMA_USER,LOG_USER,PRIV_USER,what from dba_jobs where SCHEMA_USER='FINSTAGE_PROD';
************************************************************************************************************
licence max watermark

select RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION,
INITIAL_ALLOCATION from v$resource_limit
/
****************************************************************************************************
script to recompile PL/SQL packages:

Set heading off;
set feedback off;
set echo off;
Set lines 999;
Spool run_invalid.sql

select 'ALTER ' || OBJECT_TYPE || ' ' ||
OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects where status = 'INVALID'and
object_type in ('PACKAGE','FUNCTION','PROCEDURE');

spool off;
set heading on;
set feedback on;
set echo on;

****************************************************************************************************
index usage scan

col c1 heading ‘Object|Name’ format a30
col c2 heading ‘Operation’ format a15
col c3 heading ‘Option’ format a15
col c4 heading ‘Index|Usage|Count’ format 999,999
break on c1 skip 2
break on c2 skip 2

select p.object_name c1, p.operation c2, p.options c3,
count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner <> 'SYS' and p.operation like '%INDEX%'
and p.sql_id = s.sql_id group by
p.object_name, p.operation, p.options order by 1,2,3;

select trunc((del_lf_rows/lf_rows)*100,2)||'%' "status" from index_stats;

*******************************************************************

showing gather stats job in oracle 10g

select state from dba_scheduler_jobs where job_name ='GATHER_STATS_JOB';


select job_name, job_type, program_name, schedule_name, job_class
from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

*******************************************************************

col sid format 999
col process format 9999
col sql_text format a810
set linesize 1500
select sid,process, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));

*******************************************************************

IMP monitoring

SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM
sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;

*******************************************************************