Script to check for Oracle table locks:
Display details on which table is being locked by which process/user
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 ;
Showing posts with label oracle database. Show all posts
Showing posts with label oracle database. Show all posts
Oracle: Script to display current slow job details
Oracle Script to display slow jobs details
column e_dttm format a15
column usr format a10
column hp format 9999999999
select
TO_CHAR(m. end_time, ' DD-MON-YYYY HH24:MI: SS' ) e_dttm, -- Interval End Time
m.intsize_csec/100 ints, -- Interval size in sec
s.username usr,
m.session_id sid,
m.session_serial_num ssn,
ROUND( m. cpu) cpu100, -- CPU usage 100th sec
m.physical_reads prds, -- Number of physical reads
m.logical_reads lrds, -- Number of logical reads
m.pga_memory pga, -- PGA size at end of interval
m.hard_parses hp,
m.soft_parses sp,
m.physical_read_pct prp,
m.logical_read_pct lrp,
s.sql_id
from v$sessmetric m, v$session s
where (m. physical_reads > 100
or m. cpu > 100
or m. logical_reads > 100)
and m. session_id = s. sid
and m. session_serial_num = s. serial#
order by m. physical_reads DESC, m. cpu DESC, m. logical_reads DESC;
column e_dttm format a15
column usr format a10
column hp format 9999999999
select
TO_CHAR(m. end_time, ' DD-MON-YYYY HH24:MI: SS' ) e_dttm, -- Interval End Time
m.intsize_csec/100 ints, -- Interval size in sec
s.username usr,
m.session_id sid,
m.session_serial_num ssn,
ROUND( m. cpu) cpu100, -- CPU usage 100th sec
m.physical_reads prds, -- Number of physical reads
m.logical_reads lrds, -- Number of logical reads
m.pga_memory pga, -- PGA size at end of interval
m.hard_parses hp,
m.soft_parses sp,
m.physical_read_pct prp,
m.logical_read_pct lrp,
s.sql_id
from v$sessmetric m, v$session s
where (m. physical_reads > 100
or m. cpu > 100
or m. logical_reads > 100)
and m. session_id = s. sid
and m. session_serial_num = s. serial#
order by m. physical_reads DESC, m. cpu DESC, m. logical_reads DESC;
Oracle: Script to display session details
Script to display Oracle session details:
column value format a40
column description format a100
SELECT 'AUDITED_CURSORID' AS Parameter, SYS_CONTEXT('USERENV','AUDITED_CURSORID') AS Value, 'Returns the cursor ID of the SQL that triggered the audit' AS Description FROM Dual
UNION ALL
SELECT 'AUTHENTICATION_DATA' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') AS Value, 'Authentication data' AS Description FROM Dual
UNION ALL
SELECT 'AUTHENTICATION_TYPE' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') AS Value, 'Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy' AS Description FROM Dual
UNION ALL
SELECT 'BG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','BG_JOB_ID') AS Value, 'If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL
SELECT 'CLIENT_IDENTIFIER' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') AS Value, 'Returns the client identifier (global context)' AS Description FROM Dual
UNION ALL
SELECT 'CLIENT_INFO' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_INFO') AS Value, 'User session information' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SCHEMA' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS Value, 'Returns the default schema used in the current schema' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SCHEMAID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') AS Value, 'Returns the identifier of the default schema used in the current schema' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SQL' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SQL') AS Value, 'Returns the SQL that triggered the audit event' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_USER' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USER') AS Value, 'Name of the current user' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_USERID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USERID') AS Value, 'Userid of the current user' AS Description FROM Dual
UNION ALL
SELECT 'DB_DOMAIN' AS Parameter, SYS_CONTEXT('USERENV','DB_DOMAIN') AS Value, 'Domain of the database from the DB_DOMAIN initialization parameter' AS Description FROM Dual
UNION ALL
SELECT 'DB_NAME' AS Parameter, SYS_CONTEXT('USERENV','DB_NAME') AS Value, 'Name of the database from the DB_NAME initialization parameter' AS Description FROM Dual
UNION ALL
SELECT 'ENTRYID' AS Parameter, SYS_CONTEXT('USERENV','ENTRYID') AS Value, 'Available auditing entry identifier' AS Description FROM Dual
UNION ALL
SELECT 'EXTERNAL_NAME' AS Parameter, SYS_CONTEXT('USERENV','EXTERNAL_NAME') AS Value, 'External of the database user' AS Description FROM Dual
UNION ALL
SELECT 'FG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','FG_JOB_ID') AS Value, 'If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL
SELECT 'GLOBAL_CONTEXT_MEMORY' AS Parameter, SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') AS Value, 'The number used in the System Global Area by the globally accessed context' AS Description FROM Dual
UNION ALL
SELECT 'HOST' AS Parameter, SYS_CONTEXT('USERENV','HOST') AS Value, 'Name of the host machine from which the client has connected' AS Description FROM Dual
UNION ALL
SELECT 'INSTANCE' AS Parameter, SYS_CONTEXT('USERENV','INSTANCE') AS Value, 'The identifier number of the current instance' AS Description FROM Dual
UNION ALL
SELECT 'IP_ADDRESS' AS Parameter, SYS_CONTEXT('USERENV','IP_ADDRESS') AS Value, 'IP address of the machine from which the client has connected' AS Description FROM Dual
UNION ALL
SELECT 'ISDBA' AS Parameter, SYS_CONTEXT('USERENV','ISDBA') AS Value, 'Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.' AS Description FROM Dual
UNION ALL
SELECT 'LANG' AS Parameter, SYS_CONTEXT('USERENV','LANG') AS Value, 'The ISO abbreviate for the language' AS Description FROM Dual
UNION ALL
SELECT 'LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','LANGUAGE') AS Value, 'The language, territory, and character of the session. In the following format:language_territory.characterset' AS Description FROM Dual
UNION ALL
SELECT 'NETWORK_PROTOCOL' AS Parameter, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') AS Value, 'Network protocol used' AS Description FROM Dual
UNION ALL
SELECT 'NLS_CALENDAR' AS Parameter, SYS_CONTEXT('USERENV','NLS_CALENDAR') AS Value, 'The calendar of the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_CURRENCY' AS Parameter, SYS_CONTEXT('USERENV','NLS_CURRENCY') AS Value, 'The currency of the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_DATE_FORMAT' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') AS Value, 'The date format for the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_DATE_LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') AS Value, 'The language used for dates' AS Description FROM Dual
UNION ALL
SELECT 'NLS_SORT' AS Parameter, SYS_CONTEXT('USERENV','NLS_SORT') AS Value, 'BINARY or the linguistic sort basis' AS Description FROM Dual
UNION ALL
SELECT 'NLS_TERRITORY' AS Parameter, SYS_CONTEXT('USERENV','NLS_TERRITORY') AS Value, 'The territory of the current session' AS Description FROM Dual
UNION ALL
SELECT 'OS_USER' AS Parameter, SYS_CONTEXT('USERENV','OS_USER') AS Value, 'The OS username for the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'PROXY_USER' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USER') AS Value, 'The name of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL
SELECT 'PROXY_USERID' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USERID') AS Value, 'The identifier of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL
SELECT 'SESSION_USER' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USER') AS Value, 'The database user name of the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'SESSION_USERID' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USERID') AS Value, 'The database identifier of the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'SESSIONID' AS Parameter, SYS_CONTEXT('USERENV','SESSIONID') AS Value, 'The identifier of the auditing session' AS Description FROM Dual
UNION ALL
SELECT 'TERMINAL' AS Parameter, SYS_CONTEXT('USERENV','TERMINAL') AS Value, 'The terminal where the user is logged' AS Description FROM Dual;
column value format a40
column description format a100
SELECT 'AUDITED_CURSORID' AS Parameter, SYS_CONTEXT('USERENV','AUDITED_CURSORID') AS Value, 'Returns the cursor ID of the SQL that triggered the audit' AS Description FROM Dual
UNION ALL
SELECT 'AUTHENTICATION_DATA' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') AS Value, 'Authentication data' AS Description FROM Dual
UNION ALL
SELECT 'AUTHENTICATION_TYPE' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') AS Value, 'Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy' AS Description FROM Dual
UNION ALL
SELECT 'BG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','BG_JOB_ID') AS Value, 'If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL
SELECT 'CLIENT_IDENTIFIER' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') AS Value, 'Returns the client identifier (global context)' AS Description FROM Dual
UNION ALL
SELECT 'CLIENT_INFO' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_INFO') AS Value, 'User session information' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SCHEMA' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS Value, 'Returns the default schema used in the current schema' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SCHEMAID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') AS Value, 'Returns the identifier of the default schema used in the current schema' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SQL' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SQL') AS Value, 'Returns the SQL that triggered the audit event' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_USER' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USER') AS Value, 'Name of the current user' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_USERID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USERID') AS Value, 'Userid of the current user' AS Description FROM Dual
UNION ALL
SELECT 'DB_DOMAIN' AS Parameter, SYS_CONTEXT('USERENV','DB_DOMAIN') AS Value, 'Domain of the database from the DB_DOMAIN initialization parameter' AS Description FROM Dual
UNION ALL
SELECT 'DB_NAME' AS Parameter, SYS_CONTEXT('USERENV','DB_NAME') AS Value, 'Name of the database from the DB_NAME initialization parameter' AS Description FROM Dual
UNION ALL
SELECT 'ENTRYID' AS Parameter, SYS_CONTEXT('USERENV','ENTRYID') AS Value, 'Available auditing entry identifier' AS Description FROM Dual
UNION ALL
SELECT 'EXTERNAL_NAME' AS Parameter, SYS_CONTEXT('USERENV','EXTERNAL_NAME') AS Value, 'External of the database user' AS Description FROM Dual
UNION ALL
SELECT 'FG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','FG_JOB_ID') AS Value, 'If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL
SELECT 'GLOBAL_CONTEXT_MEMORY' AS Parameter, SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') AS Value, 'The number used in the System Global Area by the globally accessed context' AS Description FROM Dual
UNION ALL
SELECT 'HOST' AS Parameter, SYS_CONTEXT('USERENV','HOST') AS Value, 'Name of the host machine from which the client has connected' AS Description FROM Dual
UNION ALL
SELECT 'INSTANCE' AS Parameter, SYS_CONTEXT('USERENV','INSTANCE') AS Value, 'The identifier number of the current instance' AS Description FROM Dual
UNION ALL
SELECT 'IP_ADDRESS' AS Parameter, SYS_CONTEXT('USERENV','IP_ADDRESS') AS Value, 'IP address of the machine from which the client has connected' AS Description FROM Dual
UNION ALL
SELECT 'ISDBA' AS Parameter, SYS_CONTEXT('USERENV','ISDBA') AS Value, 'Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.' AS Description FROM Dual
UNION ALL
SELECT 'LANG' AS Parameter, SYS_CONTEXT('USERENV','LANG') AS Value, 'The ISO abbreviate for the language' AS Description FROM Dual
UNION ALL
SELECT 'LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','LANGUAGE') AS Value, 'The language, territory, and character of the session. In the following format:language_territory.characterset' AS Description FROM Dual
UNION ALL
SELECT 'NETWORK_PROTOCOL' AS Parameter, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') AS Value, 'Network protocol used' AS Description FROM Dual
UNION ALL
SELECT 'NLS_CALENDAR' AS Parameter, SYS_CONTEXT('USERENV','NLS_CALENDAR') AS Value, 'The calendar of the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_CURRENCY' AS Parameter, SYS_CONTEXT('USERENV','NLS_CURRENCY') AS Value, 'The currency of the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_DATE_FORMAT' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') AS Value, 'The date format for the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_DATE_LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') AS Value, 'The language used for dates' AS Description FROM Dual
UNION ALL
SELECT 'NLS_SORT' AS Parameter, SYS_CONTEXT('USERENV','NLS_SORT') AS Value, 'BINARY or the linguistic sort basis' AS Description FROM Dual
UNION ALL
SELECT 'NLS_TERRITORY' AS Parameter, SYS_CONTEXT('USERENV','NLS_TERRITORY') AS Value, 'The territory of the current session' AS Description FROM Dual
UNION ALL
SELECT 'OS_USER' AS Parameter, SYS_CONTEXT('USERENV','OS_USER') AS Value, 'The OS username for the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'PROXY_USER' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USER') AS Value, 'The name of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL
SELECT 'PROXY_USERID' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USERID') AS Value, 'The identifier of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL
SELECT 'SESSION_USER' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USER') AS Value, 'The database user name of the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'SESSION_USERID' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USERID') AS Value, 'The database identifier of the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'SESSIONID' AS Parameter, SYS_CONTEXT('USERENV','SESSIONID') AS Value, 'The identifier of the auditing session' AS Description FROM Dual
UNION ALL
SELECT 'TERMINAL' AS Parameter, SYS_CONTEXT('USERENV','TERMINAL') AS Value, 'The terminal where the user is logged' AS Description FROM Dual;
Oracle: Commonly Used System Tables
Below is an alphabetical listing of commonly used Oracle system tables.
| System Table | Description |
|---|---|
| ALL_ARGUMENTS | Arguments in object accessible to the user |
| ALL_CATALOG | All tables, views, synonyms, sequences accessible to the user |
| ALL_COL_COMMENTS | Comments on columns of accessible tables and views |
| ALL_CONSTRAINTS | Constraint definitions on accessible tables |
| ALL_CONS_COLUMNS | Information about accessible columns in constraint definitions |
| ALL_DB_LINKS | Database links accessible to the user |
| ALL_ERRORS | Current errors on stored objects that user is allowed to create |
| ALL_INDEXES | Descriptions of indexes on tables accessible to the user |
| ALL_IND_COLUMNS | COLUMNs comprising INDEXes on accessible TABLES |
| ALL_LOBS | Description of LOBs contained in tables accessible to the user |
| ALL_OBJECTS | Objects accessible to the user |
| ALL_OBJECT_TABLES | Description of all object tables accessible to the user |
| ALL_SEQUENCES | Description of SEQUENCEs accessible to the user |
| ALL_SNAPSHOTS | Snapshots the user can access |
| ALL_SOURCE | Current source on stored objects that user is allowed to create |
| ALL_SYNONYMS | All synonyms accessible to the user |
| ALL_TABLES | Description of relational tables accessible to the user |
| ALL_TAB_COLUMNS | Columns of user's tables, views and clusters |
| ALL_TAB_COL_STATISTICS | Columns of user's tables, views and clusters |
| ALL_TAB_COMMENTS | Comments on tables and views accessible to the user |
| ALL_TRIGGERS | Triggers accessible to the current user |
| ALL_TRIGGER_COLS | Column usage in user's triggers or in triggers on user's tables |
| ALL_TYPES | Description of types accessible to the user |
| ALL_UPDATABLE_COLUMNS | Description of all updatable columns |
| ALL_USERS | Information about all users of the database |
| ALL_VIEWS | Description of views accessible to the user |
| DATABASE_COMPATIBLE_LEVEL | Database compatible parameter set via init.ora |
| DBA_DB_LINKS | All database links in the database |
| DBA_ERRORS | Current errors on all stored objects in the database |
| DBA_OBJECTS | All objects in the database |
| DBA_ROLES | All Roles which exist in the database |
| DBA_ROLE_PRIVS | Roles granted to users and roles |
| DBA_SOURCE | Source of all stored objects in the database |
| DBA_TABLESPACES | Description of all tablespaces |
| DBA_TAB_PRIVS | All grants on objects in the database |
| DBA_TRIGGERS | All triggers in the database |
| DBA_TS_QUOTAS | Tablespace quotas for all users |
| DBA_USERS | Information about all users of the database |
| DBA_VIEWS | Description of all views in the database |
| DICTIONARY | Description of data dictionary tables and views |
| DICT_COLUMNS | Description of columns in data dictionary tables and views |
| GLOBAL_NAME | global database name |
| NLS_DATABASE_PARAMETERS | Permanent NLS parameters of the database |
| NLS_INSTANCE_PARAMETERS | NLS parameters of the instance |
| NLS_SESSION_PARAMETERS | NLS parameters of the user session |
| PRODUCT_COMPONENT_VERSION | version and status information for component products |
| ROLE_TAB_PRIVS | Table privileges granted to roles |
| SESSION_PRIVS | Privileges which the user currently has set |
| SESSION_ROLES | Roles which the user currently has enabled. |
| SYSTEM_PRIVILEGE_MAP | Description table for privilege type codes. Maps privilege type numbers to type names |
| TABLE_PRIVILEGES | Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| TABLE_PRIVILEGE_MAP | Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names |
Oracle: SQL for displaying current processes, userids
use the followings sql for displaying current running processes, userids:
SET TERMOUT ON
SET HEADING ON
SET PAGESIZE 40
SET LINESIZE 180
SET NEWPAGE 0
SET VERIFY OFF
SET ECHO OFF
SET UNDERLINE =
SET FEEDBACK ON
SET LONG 1000
SET EMBED ON
COLUMN sid_ser FORMAT a10 HEADING ' SID/Ser'
COLUMN sid FORMAT 99999 HEADING ' SID'
COLUMN username FORMAT a6 HEADING 'Oracle|User'
COLUMN osuser FORMAT a10 TRUNC HEADING 'O/S User'
COLUMN machine FORMAT a10 HEADING 'Machine'
COLUMN program FORMAT a20 HEADING 'Program'
COLUMN F_Ground FORMAT 99999 HEADING 'F''Ground|Process'
COLUMN B_Ground FORMAT 99999 HEADING 'B''Ground|Process'
COLUMN sql_text FORMAT a45 word_wrap HEADING 'SQL Text'
COLUMN disk_reads FORMAT 99,999 HEADING 'Disk|Reads|(*1000)'
COLUMN buffer_gets FORMAT 9,999,999 HEADING 'Buffer|Gets|(*1000)'
COLUMN rows_processed FORMAT 9,999,999 HEADING 'Rows|Processed|(*1000)'
COLUMN sorts FORMAT 99,999 HEADING 'Sorts'
COLUMN executions FORMAT 9,999,999,999 HEADING 'Exctn'
TTITLE Center 'SQL Currently Executing'
SELECT /*+ ORDERED */
s.sid || ',' || s.serial# as sid_ser,
s.username,
s.osuser,
x.sql_text,
x.disk_reads / 1000 AS disk_reads,
x.buffer_gets / 1000 AS buffer_gets,
x.rows_processed / 1000 AS rows_processed,
x.sorts
,x.executions
,x.address
FROM v$session S,
v$process P,
v$sql X
WHERE LOWER(s.osuser) LIKE LOWER(NVL('&os_user%', '%'))
AND s.username LIKE UPPER(NVL('&oracle_user%', '%'))
AND s.sid LIKE NVL('&sid', '%')
AND s.type != 'BACKGROUND'
AND s.sql_address = x.address
AND s.sql_hash_value = x.hash_value
-- AND s.username NOT IN ('SYS','SYSTEM')
AND s.paddr = p.addr
ORDER
BY S.sid
;
SET TERMOUT ON
SET HEADING ON
SET PAGESIZE 40
SET LINESIZE 180
SET NEWPAGE 0
SET VERIFY OFF
SET ECHO OFF
SET UNDERLINE =
SET FEEDBACK ON
SET LONG 1000
SET EMBED ON
COLUMN sid_ser FORMAT a10 HEADING ' SID/Ser'
COLUMN sid FORMAT 99999 HEADING ' SID'
COLUMN username FORMAT a6 HEADING 'Oracle|User'
COLUMN osuser FORMAT a10 TRUNC HEADING 'O/S User'
COLUMN machine FORMAT a10 HEADING 'Machine'
COLUMN program FORMAT a20 HEADING 'Program'
COLUMN F_Ground FORMAT 99999 HEADING 'F''Ground|Process'
COLUMN B_Ground FORMAT 99999 HEADING 'B''Ground|Process'
COLUMN sql_text FORMAT a45 word_wrap HEADING 'SQL Text'
COLUMN disk_reads FORMAT 99,999 HEADING 'Disk|Reads|(*1000)'
COLUMN buffer_gets FORMAT 9,999,999 HEADING 'Buffer|Gets|(*1000)'
COLUMN rows_processed FORMAT 9,999,999 HEADING 'Rows|Processed|(*1000)'
COLUMN sorts FORMAT 99,999 HEADING 'Sorts'
COLUMN executions FORMAT 9,999,999,999 HEADING 'Exctn'
TTITLE Center 'SQL Currently Executing'
SELECT /*+ ORDERED */
s.sid || ',' || s.serial# as sid_ser,
s.username,
s.osuser,
x.sql_text,
x.disk_reads / 1000 AS disk_reads,
x.buffer_gets / 1000 AS buffer_gets,
x.rows_processed / 1000 AS rows_processed,
x.sorts
,x.executions
,x.address
FROM v$session S,
v$process P,
v$sql X
WHERE LOWER(s.osuser) LIKE LOWER(NVL('&os_user%', '%'))
AND s.username LIKE UPPER(NVL('&oracle_user%', '%'))
AND s.sid LIKE NVL('&sid', '%')
AND s.type != 'BACKGROUND'
AND s.sql_address = x.address
AND s.sql_hash_value = x.hash_value
-- AND s.username NOT IN ('SYS','SYSTEM')
AND s.paddr = p.addr
ORDER
BY S.sid
;
Oracle: Optimizer settings
Oracle OPTIMIZER settings
alter session set "_optimizer_cost_model" =choose;
alter session set "_optimizer_cost_model"=io;
alter session set "_optimizer_cost_model"=cpu;
Oracle: SQLplus Prompt to show user, connection (10g)
SQLplus Prompt to show user, connection (Oracle 10g)
run the following in sqlplus:
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "
Display:
user@DB (privilege if exist)>
Display:
user@DB (privilege if exist)>
Oracle Database Stuff
Oracle FAQ - Oracle Wiki: http://www.orafaq.com/wiki/Main_Page
Oracle DBA FAQ - Oracle Basic Concepts: http://dev.fyicenter.com/faq/oracle/oracle_basic_concept.php
Oracle SQL *Plus: An Introduction and Tutorial: http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/
Oracle DBA FAQ - Oracle Basic Concepts: http://dev.fyicenter.com/faq/oracle/oracle_basic_concept.php
Oracle SQL *Plus: An Introduction and Tutorial: http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/
Subscribe to:
Posts (Atom)