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 SQL. Show all posts
Showing posts with label oracle SQL. 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: SqlPlus Useful Set Command
Useful SET commands for output formatting and SPOOL:
SET TERM OFF
-- TERM = ON will display on terminal screen (OFF = show in LOG only)
SET ECHO ON
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.
SET TRIMOUT ON
-- TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON
-- TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING OFF
-- HEADING = OFF will hide column headings
SET FEEDBACK OFF
-- FEEDBACK = ON will count rows returned
SET PAUSE OFF
-- PAUSE = ON .. press return at end of each page
SET PAGESIZE 0
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)
SET LINESIZE 80
-- LINESIZE = width of page (80 is typical)
SET VERIFY OFF
-- VERIFY = ON will show before and after substitution variables
-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;
SPOOL OFF
SET TERM OFF
-- TERM = ON will display on terminal screen (OFF = show in LOG only)
SET ECHO ON
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.
SET TRIMOUT ON
-- TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON
-- TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING OFF
-- HEADING = OFF will hide column headings
SET FEEDBACK OFF
-- FEEDBACK = ON will count rows returned
SET PAUSE OFF
-- PAUSE = ON .. press return at end of each page
SET PAGESIZE 0
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)
SET LINESIZE 80
-- LINESIZE = width of page (80 is typical)
SET VERIFY OFF
-- VERIFY = ON will show before and after substitution variables
-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;
SPOOL OFF
Oracle: List freespace by Tablespaces
Use the following script to get sizing (free,used etc) for tablespaces:
------------------------------------------------------------------------------
-- This SQL Plus script lists freespace by tablespace
------------------------------------------------------------------------------
set linesize 200
set pages 100
set verify off
set feedback off
col stat for a4 trunc head Stat
col content for a4 trunc head Type
col tablespace_name for a25 head Tablespace
col MB for 9,999,999 head "Tot|(MB)"
col free for 9,999,999 head "Free|(MB)"
col largest for 99,999,999 head "Largest |Extent (K)"
col percent for a7 head "% Free"
col extent_management for a4 trunc head "Ext.|Mng"
col allocation_type for a4 trunc head "Allc|type"
col segment_space_management for a6 trunc head "Space|Mng"
col pct_increase for 99 head "Pct |Inc."
break on report
compute sum of "free" on report
compute sum of "MB" on report
-- 100 - (NVL (t.bytes / a.bytes * 100, 0)) percent,
SELECT substr(d.status,0,3) stat , substr(d.contents,0,1) content,
d.tablespace_name,
NVL (a.bytes / 1024 / 1024, 0) MB,
NVL (f.bytes / 1024 / 1024, 0) free,
NVL (f.large / 1024, 0) largest,
' '||round(f.bytes/a.bytes*100,0)||'%' percent,
d.extent_management, d.allocation_type,
d.pct_increase, d.segment_space_management
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) large
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT ( d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY'
)
UNION ALL
SELECT substr(d.status,0,3) stat, substr(d.contents,0,1) content,
d.tablespace_name,
NVL (a.bytes / 1024 / 1024, 0) MB,
NVL (a.bytes - NVL (t.bytes, 0), 0) / 1024 / 1024 free,
NVL (t.large / 1024, 0) largest,
' '||round((1-NVL(t.bytes,0)/a.bytes)*100,0)||'%' percent,
d.extent_management, d.allocation_type,
d.pct_increase, d.segment_space_management
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes_cached) bytes,MAX(bytes_cached) large
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY'
/
------------------------------------------------------------------------------
-- This SQL Plus script lists freespace by tablespace
------------------------------------------------------------------------------
set linesize 200
set pages 100
set verify off
set feedback off
col stat for a4 trunc head Stat
col content for a4 trunc head Type
col tablespace_name for a25 head Tablespace
col MB for 9,999,999 head "Tot|(MB)"
col free for 9,999,999 head "Free|(MB)"
col largest for 99,999,999 head "Largest |Extent (K)"
col percent for a7 head "% Free"
col extent_management for a4 trunc head "Ext.|Mng"
col allocation_type for a4 trunc head "Allc|type"
col segment_space_management for a6 trunc head "Space|Mng"
col pct_increase for 99 head "Pct |Inc."
break on report
compute sum of "free" on report
compute sum of "MB" on report
-- 100 - (NVL (t.bytes / a.bytes * 100, 0)) percent,
SELECT substr(d.status,0,3) stat , substr(d.contents,0,1) content,
d.tablespace_name,
NVL (a.bytes / 1024 / 1024, 0) MB,
NVL (f.bytes / 1024 / 1024, 0) free,
NVL (f.large / 1024, 0) largest,
' '||round(f.bytes/a.bytes*100,0)||'%' percent,
d.extent_management, d.allocation_type,
d.pct_increase, d.segment_space_management
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) large
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT ( d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY'
)
UNION ALL
SELECT substr(d.status,0,3) stat, substr(d.contents,0,1) content,
d.tablespace_name,
NVL (a.bytes / 1024 / 1024, 0) MB,
NVL (a.bytes - NVL (t.bytes, 0), 0) / 1024 / 1024 free,
NVL (t.large / 1024, 0) largest,
' '||round((1-NVL(t.bytes,0)/a.bytes)*100,0)||'%' percent,
d.extent_management, d.allocation_type,
d.pct_increase, d.segment_space_management
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes_cached) bytes,MAX(bytes_cached) large
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY'
/
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: Rename Table
Oracle rename table syntax
Oracle provides a rename table syntax as follows:
alter table
table_name
rename to
new_table_name;
table_name
rename to
new_table_name;
For example, we could rename the customer table to old_customer with this syntax:
alter table
customer
rename to
old_customer;
customer
rename to
old_customer;
When you rename an Oracle table you must be aware that Oracle does not update applications (HTML-DB, PL/SQL that referenced the old table name) and PL/SQL procedures may become invalid.
Oracle: Column Statistics SQL
Get column statistics via the following sql.
change xxx to table name of interest.
SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY
FROM DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME ='XXXX'
ORDER BY COLUMN_NAME;
Oracle: Check which user is holding temp tablespace
Check which user holding temp tablespace
SELECT s.username, s.sid, u.TABLESPACE, u.CONTENTS, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr;
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr;
Oracle: Get database name using sqlplus
Get database name using this sql:
select ora_database_name from dual;
select ora_database_name from dual;
SQL Stuff
w3schools SQL Tutorial:
http://www.w3schools.com/sql/default.asp
SQL (orafaq):
http://www.orafaq.com/wiki/SQL
Oracle SQL FAQ:
http://www.orafaq.com/wiki/SQL_FAQ
http://www.w3schools.com/sql/default.asp
SQL (orafaq):
http://www.orafaq.com/wiki/SQL
Oracle SQL FAQ:
http://www.orafaq.com/wiki/SQL_FAQ
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)