crsctl

% crsctl
Usage: crsctl check crs - checks the viability of the CRS stack
crsctl check cssd - checks the viability of CSS
crsctl check crsd - checks the viability of CRS
crsctl check evmd - checks the viability of EVM
crsctl set css - sets a parameter override
crsctl get css - gets the value of a CSS parameter
crsctl unset css - sets CSS parameter to its default
crsctl query css votedisk - lists the voting disks used by CSS
crsctl add css votedisk - adds a new voting disk
crsctl delete css votedisk - removes a voting disk
crsctl enable crs - enables startup for all CRS daemons
crsctl disable crs - disables startup for all CRS daemons
crsctl start crs - starts all CRS daemons.
crsctl stop crs - stops all CRS daemons. Stops CRS resources in case of cluster.
crsctl start resources - starts CRS resources.
crsctl stop resources - stops CRS resources.
crsctl debug statedump evm - dumps state info for evm objects
crsctl debug statedump crs - dumps state info for crs objects
crsctl debug statedump css - dumps state info for css objects
crsctl debug log css [module:level]{,module:level} ...
- Turns on debugging for CSS
crsctl debug trace css - dumps CSS in-memory tracing cache
crsctl debug log crs [module:level]{,module:level} ...
- Turns on debugging for CRS
crsctl debug trace crs - dumps CRS in-memory tracing cache
crsctl debug log evm [module:level]{,module:level} ...
- Turns on debugging for EVM
crsctl debug trace evm - dumps EVM in-memory tracing cache
crsctl debug log res turns on debugging for resources
crsctl query crs softwareversion [] - lists the version of CRS software installed
crsctl query crs activeversion - lists the CRS software operating version
crsctl lsmodules css - lists the CSS modules that can be used for debugging
crsctl lsmodules crs - lists the CRS modules that can be used for debugging
crsctl lsmodules evm - lists the EVM modules that can be used for debugging


## Oracle 10g R1
/etc/init.d/init.crs [disable|enable]

$ORA_CRS_HOME/bin/crsctl [disable|enable] crs

$ORA_CRS_HOME/bin/crsctl check crs
$ORA_CRS_HOME/bin/crsctl check evmd
$ORA_CRS_HOME/bin/crsctl check cssd
$ORA_CRS_HOME/bin/crsctl check crsd
$ORA_CRS_HOME/bin/crsctl check install -wait 600


Resource Applications (CRS Utilities)

status $ORA_CRS_HOME/bin/crs_stat
$ORA_CRS_HOME/bin/crs_stat -t
$ORA_CRS_HOME/bin/crs_stat -ls
$ORA_CRS_HOME/bin/crs_stat -p

Note:
-t more readable display
-ls permission listing
-p parameters

Oracle Database rename golbal name

col "value" format a33
col "comment" format a70
select NAME,substr(VALUE$,1,70) "value" ,substr(COMMENT$,1,70) "comment" from sys.props$


1* select NAME,substr(VALUE$,1,70) "value" ,substr(COMMENT$,1,70) "comment" from sys.props$


NAME value comment
------------------------------ --------------------------------- ----------------------------------------------------------------------
DICT.BASE 2 dictionary base tables version #
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
DEFAULT_EDITION ORA$BASE Name of the database default edition
Flashback Timestamp TimeZone GMT Flashback timestamp created in GMT
TDE_MASTER_KEY_ID
DST_UPGRADE_STATE NONE State of Day Light Saving Time Upgrade
DST_PRIMARY_TT_VERSION 11 Version of primary timezone data file
DST_SECONDARY_TT_VERSION 0 Version of secondary timezone data file
DEFAULT_TBS_TYPE SMALLFILE Default tablespace type
NLS_LANGUAGE AMERICAN Language
NLS_TERRITORY AMERICA Territory
NLS_CURRENCY $ Local currency
NLS_ISO_CURRENCY AMERICA ISO currency
NLS_NUMERIC_CHARACTERS ., Numeric characters
NLS_CHARACTERSET AL32UTF8 Character set
NLS_CALENDAR GREGORIAN Calendar system
NLS_DATE_FORMAT DD-MON-RR Date format
NLS_DATE_LANGUAGE AMERICAN Date language
NLS_SORT BINARY Linguistic definition
NLS_TIME_FORMAT HH.MI.SSXFF AM Time format
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM Time stamp format
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR Timestamp with timezone format
NLS_DUAL_CURRENCY $ Dual currency symbol
NLS_COMP BINARY NLS comparison
NLS_LENGTH_SEMANTICS BYTE NLS length semantics
NLS_NCHAR_CONV_EXCP FALSE NLS conversion exception
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
NLS_RDBMS_VERSION 11.2.0.1.0 RDBMS version for NLS parameters
GLOBAL_DB_NAME ORCL.APTUS.CO.UK Global database name
EXPORT_VIEWS_VERSION 8 Export views revision #
WORKLOAD_CAPTURE_MODE CAPTURE implies workload capture is in progress
WORKLOAD_REPLAY_MODE PREPARE implies external replay clients can connect; REPLAY implies wo
NO_USERID_VERIFIER_SALT 8BF56230FC49D6ECFE53EB96A5922DD5
DBTIMEZONE 00:00 DB time zone

36 rows selected.




SQL> select NAME,substr(VALUE$,1,70) "value" ,substr(COMMENT$,1,70) "comment" from sys.props$ where name='GLOBAL_DB_NAME';


NAME value comment
------------------------------ --------------------------------- ----------------------------------------------------------------------
GLOBAL_DB_NAME ORCL.APTUS.CO.UK Global database name

select NAME,substr(VALUE$,1,70) "value" ,substr(COMMENT$,1,70) "comment" from sys.props$ where name like '%GLOBAL%'

NAME value comment
------------------------------ --------------------------------- ----------------------------------------------------------------------
GLOBAL_DB_NAME ORCL.APTUS.CO.UK Global database name



SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE


SQL> col GLOBAL_NAME format a30

SQL> r
1* SELECT * FROM global_name

GLOBAL_NAME
------------------------------
ORCL.APTUS.CO.UK




SELECT name,value FROM v$parameter WHERE name = 'global_names';



select NAME,substr(VALUE$,1,70) "value" ,substr(COMMENT$,1,70) "comment" from sys.props$ where name like '%GLOBAL%' ; ='global_names';


select NAME,substr(VALUE$,1,70) "value" ,substr(COMMENT$,1,70) "comment" from sys.props$ where name like '%GLOBAL%' ;

SELECT name,value FROM v$parameter WHERE name = 'global_names';



SELECT * FROM props$ WHERE name='GLOBAL_DB_NAME';


# Example select * from props$ where name = 'GLOBAL_DB_NAME'; -Returns with old database name
# cause: The database name was not the same in all data dictionary views




ALTER DATABASE RENAME GLOBAL_NAME TO remote1.oracle.com;
SQL> ALTER SESSION SET global_names = TRUE;
SQL> ALTER SYSTEM SET global_names = FALSE;

Oracle active sql , explain plan ,wait , top sql

what sql is currently running
Running sql
Oracle active sql


s.sql

set linesize 200
set pagesize 200
column sid format a12
column username format a10
column terminal format a10
column sql_text format a40 wrap
Select ''''||s.sid||','||s.serial#||'''' SID
,s.status
,s.username
,s.terminal
,q.SQL_TEXT
,q.command_type
,s.sql_id
from
v$session s
,(select distinct
address
,sql_text
,command_type
from v$sql) q
where
q.address=s.sql_address
order by s.sid
/
prompt >>>>>>>>>>>>>>>>>>>>>>>>>>>-- run e.sql for explain plan

alter system kill session 'sid,serial#' IMMEDIATE;

=======
again active session

s1.sql


prompt
prompt Report of Current Active Sessions.

set linesize 150
set pagesize 100
set verify off

column sid_serial format A10 heading 'Sid,Serial'
column osuser format A10 heading 'OS USer'
column user_schema format A12 heading 'User/Schema'
column machine format A35 heading 'Machine/What'
column program format A37 heading 'Program'
column action format a15 heading 'Action'
column s_status format a1 heading 'S'
column wait_event format a30 heading 'Wait Event'
column lockwait format a1 heading 'L'
column sql_text format a50 heading 'sql text'
column unix_pid format a8
select /*+ RULE */ decode (jr.job,NULL,s.osuser,'JOB '||jr.job) osuser
,decode (s.username,s.schemaname,s.username,s.username||'/'||s.schemaname) user_schema,substr(sq.sql_text,1,50) sql_text
,s.sid||','||s.serial# as sid_serial
,p.spid as unix_pid
,substr (s.status,1,1) as s_status
,substr (s.lockwait,1,1) as lockwait
,aa.name as action
-- ,nvl (j.what,s.machine) as machine
,w.event as wait_event
-- ,nvl(s.program,p.program) as program
from v$session s
,sys.dba_jobs_running jr
,sys.dba_jobs j
,v$process p
,v$session_wait w
,sys.audit_actions aa
,sys.v$sql sq
where s.type <> 'BACKGROUND'
and s.status in ('ACTIVE','KILLED')
and nvl(s.program,p.program) not like '% (SNP%)'
and s.paddr = p.addr
and s.sid = jr.sid(+)
and jr.job = j.job(+)
and s.sid = w.sid (+)
and s.command = aa.action (+)
and sq.SQL_ID=s.sql_id
order by machine, s.osuser,s.username,s.program,s.sid
/

output ----

OS USer User/Schema sql text Sid,Serial UNIX_PID S L Action Wait Event
---------- ------------ -------------------------------------------------- ---------- -------- - - --------------- ------------------------------
oracle SYS select /*+ RULE */ decode (jr.job,NULL,s.osuser,'J 53,3705 20231 A SELECT SQL*Net message to client





s3.sql

set linesize 80 pagesize 60
clear columns
column txt format a620 heading 'USERNAME OSUSER SPID TERMINAL PROGRAM STATUS'
select rpad(vs.username,11) || ' ' || rpad(vs.osuser,14) || ' ' || rpad(vp.spid,6) || ' ' || rpad(nvl(vs.terminal,' '),11) || ' ' ||
rpad(nvl(nvl(vs.program,vs.module),' '),25) || ' ' || rpad(vs.status,8) || substr(sa.sql_text,1,560) txt
from v$session vs,v$process vp,v$sqlarea sa
where vs.username is not null
and vp.addr = vs.paddr
and vs.sql_address = sa.address(+)
and vs.sql_hash_value = sa.hash_value(+)
order by 1;
clear columns
set pagesize 24
set echo on

USERNAME OSUSER SPID TERMINAL PROGRAM STATUS
--------------------------------------------------------------------------------
SYS oracle 16765 pts/6 sqlplus@apt-amd-02 (TNS V ACTIVE
select rpad(vs.username,11) || ' ' || rpad(vs.osuser,14) || ' ' || rpad(vp.spi
d,6) || ' ' || rpad(nvl(vs.terminal,' '),11) || ' ' || rpad(nvl(nvl(vs.progra
m,vs.module),' '),25) || ' ' || rpad(vs.status,8) || substr(sa.sql_text,1,560)
txt from v$session vs,v$process vp,v$sqlarea sa where vs.username is not nu
ll and vp.addr = vs.paddr and vs.sql_address = sa.address(+) and vs.sql_ha
sh_value = sa.hash_value(+) order by 1

SYS oracle 17448 pts/17 sqlplus@apt-amd-02 (TNS V INACTIVE





p2.sql

set linesize 80 pagesize 60 recsep off timing off
clear columns
column username format a11
column osuser format a13
column "PROGRAM OR BANNER FORM" format a22 trunc
column "RunSec" format a6
column spid format a7
column terminal format a10
column server format a12
select vs.username,vs.osuser,vp.spid,vs.terminal, vs.module "PROGRAM OR BANNER FORM",to_char(vs.logon_time,'HH24:MI') "LOGON",
substr(to_char(value/100,'999.99'),2) "RunSec"
from v$session vs,v$process vp,v$sesstat st
where vs.username is not null
and vp.addr = vs.paddr
and vs.sid = st.sid
and statistic# = 12
order by vs.username;
select v1.terminal "SERVER",sessions,active "ACTIVE FORMS" from
(select terminal,count(*) sessions from v$session
where terminal is not null and osuser = 'SYSTEM'
group by terminal) v1,
(select terminal,count(*) active from v$session
where terminal is not null and osuser = 'SYSTEM'
and module != 'GUAGMNU' and length(module) = 7
group by terminal) v2
where v1.terminal = v2.terminal(+);
clear columns
set pagesize 24 timing on
set echo on showmode both


USERNAME OSUSER SPID TERMINAL PROGRAM OR BANNER FORM LOGON RunSec
----------- ------------- ------- ---------- ---------------------- ----- ------
SYS oracle 17448 pts/17 sqlplus@apt-amd-02 (TN 08:47 .00
SYS oracle 16765 pts/6 sqlplus@apt-amd-02 (TN 16:03 .00

==========


currently connected to the database and waits

set pagesize 50000;
set linesize 320;
set heading on;
set verify off;
set trimspool on;
clear screen;

column date_of_run format a30;
column open_time format a30;
column block_size_bytes format a20;

column ousername format a10 heading 'Oracle|User ID' justify left
column oosuser format a10 heading 'OS|User ID' justify left
column omachine format a10 heading 'Machine ID' justify left
column uspid format 999999 heading 'UNIX|Process|ID' justify right
column oserial# format 999999 heading 'Oracle|Serial|No' justify right
column osid format 999999 heading 'Oracle|Session|ID' justify right
column oprogram format a50 heading 'Program' justify left
column qsqltxt format a100 heading 'Current|Statement' justify left word
column ostatus format a8 heading 'Session|Status' justify left
column qrows format 999999 heading 'Rows|Processed' justify right
column nname format a30 heading 'Statistic|Name' justify left
column svalue format 9999999999 heading 'Statistic|Value' justify right
column avg_lastd format 9999999999 heading 'Average|Last Day' justify right
column lst_exect format 9999999999 heading 'Last|Eexec Time' justify right
col qsqltxt format a50
break on ousername on oosuser on omachine on uspid on oserial# on osid on oprogram on qsqltxt on ostatus on qrows noduplicates


prompt
prompt *******************************************************
prompt IF THIS SCRIPT DOES NOT RETURN ANYTHING, RUN IT AGAIN.
prompt THE TARGET QUERY MAY NOT BE RUNNING THE MOMENT YOU TRY.
prompt *******************************************************

PROMPT
ACCEPT KNOWN_PARAMETER_NAME CHAR PROMPT 'ENTER PARAMETER NAME [1]: ' DEFAULT 1
ACCEPT KNOWN_PARAMETER_VALUE CHAR PROMPT 'ENTER PARAMETER VALUE [1]: ' DEFAULT 1
PROMPT

select d.name DATABASE,
to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') DATE_OF_RUN,
to_char(open_time, 'DD-MON-YYYY HH24:MI:SS') OPEN_TIME,
value BLOCK_SIZE_BYTES
from v$database d,
v$parameter p,
v$thread t
where p.name = 'db_block_size';

select
o.username ousername
,o.osuser oosuser
,o.machine omachine
,lpad(u.spid,7) uspid
,o.serial# oserial#
,o.sid osid
-- ,o.program oprogram
-- ,substr(q.sql_text,1,50) qsqltxt
,q.sql_id
,o.status ostatus
,q.rows_processed qrows
,n.name nname
,s.value svalue
from
v$process u
,v$session o
,v$sqlarea q
,v$sesstat s
,v$statname n
where
u.addr=o.paddr
and o.sql_address=q.address(+)
and o.sql_hash_value=q.hash_value(+)
and o.sid=s.sid
and n.statistic#=s.statistic#
and n.name in (
'CPU used by this session'
,'physical reads'
,'physical writes'
,'session pga memory max'
,'session uga memory max'
)
and &KNOWN_PARAMETER_NAME=&KNOWN_PARAMETER_VALUE
order by 1,2,3;



select sql_text from v$sqlarea where sql_id='&sql_id';
Enter value for sql_id: ggzz1vwf6rg79



============
for explain plan
e.sql

set pagesize 32000
set linesize 200
column sid format a10
column username format a10
column terminal format a10
column sql_text format a90 wrap
column OPERATION format a30
column OBJECT format a30
break on SID
Select ''''||s.sid||','||s.serial#||'''' SID,
CASE
WHEN (E.OPERATION like '%STATEMENT') then Q.SQL_TEXT
else LPAD(' ',E.DEPTH*2,' ')||RTRIM(REPLACE(E.OPERATION||'/'||E.OPTIONS||'/'||E.OBJECT_OWNER||'.'||E.OBJECT_NAME,'//','/')||'/'||to_char(E.PARTITION_START)||'-'||to_char(E.PARTITION_STOP),'/-.')
END SQL_TEXT
--,E.OBJECT_NODE
--,E.OPTIMIZER
--,E.ID
--,E.PARENT_ID
--,E.DEPTH
--,E.COST
--,E.CARDINALITY
--,E.BYTES
--,E.OTHER_TAG
--,E.PARTITION_START
--,E.PARTITION_STOP
--,E.PARTITION_ID
--,E.OTHER
--,E.DISTRIBUTION
,E.CPU_COST
,E.IO_COST
From v$sql_plan E, v$sql Q, V$SESSION S
Where E.address=Q.address(+)
and E.hash_value=Q.hash_value(+)
and E.child_number=Q.child_number(+)
and E.address=S.sql_address
order by s.sid,e.ID
/

prompt >>>>>>>>>>>>>>>>>>> w.sql for long running processes


e1.sql

set head off
set lines 200
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

el.sql

-- execution plan of the last executed statement for that session
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

or

set head off
set lines 200
select * from TABLE(dbms_xplan.display_cursor(null, null, 'ALL ALLSTATS LAST'));




ew.sql

set linesize 120
set pagesize 500
select * from TABLE(dbms_xplan.display_awr('&sql_id'));



====
for Wait info

-bash-3.00$ cat w.sql

col p1 format a10
col p2 format a10
col p3 format a10
col USERNAME format a15
col SERIAL format a10
col sid format a10
col STATUS format a10
col MESSAGE format a70

set linesize 200
set pagesize 200
column sid format 9999
column DONE_PCT format a8
column MINUTES_REM format 99999
select SID,to_char(ROUND((SOFAR*100)/TOTALWORK,2))||' %' DONE_PCT
,trunc(TIME_REMAINING/60) MINUTES_REM
,trunc(ELAPSED_SECONDS/60) MINUTES_ELAPSED
,MESSAGE
from V$SESSION_LONGOPS
order by start_time
/


FOR Process
p.sql


set feedback off
set pause off
set pagesize 22
set linesize 100
ttitle center 'TRANSACTION STATUS' skip 5
btitle center 'DBA SCRIPTS'
col START_TIME format a20 heading 'START TIME'
col USERNAME format a14 heading USER
col NAME format a15 heading ACTION
col USED_UBLK format 9,999,999,999 heading 'ROLLBACK BLOCKS'
col USED_UREC format 9,999,999,999 heading 'ROLLBACK RECORDS'
col SPID format a12 heading 'UNIX PROCESS'
select
to_char(to_date(a.START_TIME,'MM/DD/YY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') START_TIME,
b.USERNAME,
c.NAME,
a.USED_UBLK,
a.USED_UREC,
d.SPID
from
V$TRANSACTION a,
V$SESSION b,
SYS.AUDIT_ACTIONS c,
V$PROCESS d
where
a.SES_ADDR=b.SADDR and
b.COMMAND=c.ACTION and
d.ADDR=b.PADDR
order
by 1,2
/
ttitle off
btitle off
set feedback on
set pause on
============
top sql

t.sql

set trimspool on
--set linesize 300
set linesize 200
set verify off
set trimspool on
set feedback off

spool dba_topsql.log

prompt
prompt Report of Possible SQL to Tune.

column gets_per_exe format 999,999,990
column disk_reads format 999,999,990
column buffer_gets format 999,999,990
column executions format 999,999,990
column sorts format 999,990
column hash_value format 99999999990
column sql_id format a14
column sql_text format a50 word_wrapped
column username format a13
column cpu_time format 999,990.00 heading 'CPU(s)'
column elapsed_time format 999,990.00 heading 'Elapsed(s)'

select u.username
, v.disk_reads
, v.buffer_gets
, v.executions
-- , v.sorts
, v.buffer_gets/v.executions as gets_per_exe
, (v.cpu_time/1000000) as cpu_time
, (v.elapsed_time/1000000) as elapsed_time
-- , v.address
-- , v.hash_value
, v.sql_id
, substr (v.sql_text,1,70) as sql_text
-- , a.name as action_type
from v$sqlarea v
, dba_users u
, audit_actions a
where v.parsing_schema_id = u.user_id
and v.command_type = a.action
-- and u.username not in ('SYS','SYSTEM')
and v.buffer_gets >=
( select min (v2.buffer_gets)
from
( select v3.buffer_gets
from v$sqlarea v3
order by buffer_gets desc
) v2
where rownum < 20
)
order by v.buffer_gets desc
/
spool off
set feedback 6

=============
for wait info
-bash-3.00$ cat w1.sql

SET LINESIZE 250
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20

SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
d.spid AS process_id,
a.wait_class,
a.seconds_in_wait,
a.state,
a.blocking_session,
a.blocking_session_status,
a.module,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$process d
WHERE a.paddr = d.addr
AND a.status = 'ACTIVE'
ORDER BY 1,2;

SET PAGESIZE 14

==

hplan.sql

undefine PLAN_HASH_VALUE
-- accept PLAN_HASH_VALUE prompt 'Please enter PLAN_HASH_VALUE to show Statistics for: '
accept sql_id prompt 'Please enter sql_id to show Statistics for: '
col iowait_delta format 9999999.99 heading iowaitdelta(ms)
col iowait_total format 9999999.99 heading iowaittotal(ms)
col ELAPSED_TIME_TOTAL format 9999999.99 heading elapsdtimetotal(ms)
col ELAPSED_TIME_DELTA format 9999999.99 heading elapsdtimedelta(ms)
col PLAN_HASH_VALUE heading plan_hashvalue
col CONCURRENCY_WAIT_TOTAL format 9999999.99 heading concwaittotal(ms)
col CONCURRENCY_WAIT_delta format 9999999.99 heading concwaitdelta(ms)
col CLUSTER_WAIT_DELTA format 9999999.99 heading clustwaitdelta(ms)
col CLUSTER_WAIT_TOTAL format 9999999.99 heading clustwaittotal(ms)
col APWAIT_TOTAL format 9999 heading applwaittimetotal(micro)
col APWAIT_DELTA format 9999 heading applwaittimedelta(micro)
col PLSEXEC_TIME_TOTAL format 9999 heading plsqlexectimetotal(micro)
col PLSEXEC_TIME_DELTA format 9999 heading plsqlexectimedelta(micro)
col JAVAEXEC_TIME_DELTA format 9999 heading javaexectimedelta(micro)
col JAVAEXEC_TIME_TOTAL format 9999 heading javaexectimetotal(micro)
col optimizer_cost format 9999 heading optcostcol optimizer_mode format a10 heading optimmode
col kept_versions format 999 heading keptvers
col invalidations_total format 999 heading invalidtot
col invalidations_delta format 999 heading invaliddlt
col parse_calls_total format 99999 heading parsecallstotal
col parse_calls_delta format 99999 heading parsecallsdelta
col executions_total format 999999 heading exectotal
col executions_delta format 999999 heading execdelta
col fetches_total format 9999999 heading fetchestotal
col fetches_delta format 9999999 heading fetchesdelta
col end_of_fetch_count_total format 9999 heading endoffetchcalltotal
col end_of_fetch_count_delta format 9999 heading endoffetchcalldelta
col buffer_gets_total format 99999999 heading buffergetstotal
col buffer_gets_delta format 99999999 heading buffergetsdelta
col disk_reads_total format 999999 heading diskreadstotal
col disk_reads_delta format 9999999 heading diskreadsdelta
col rows_processed_total format 9999999 heading rowsprocessedtotal
col rows_processed_delta format 9999999 heading rowsprocesseddelta
col rows_ex format 999999 heading rowsexeccol snap_id format 99999 heading snapid
col ela_ex format 9999999.99 heading elapsedperexecution
col cwt_ex format 9999999.99 heading cwtperexecution
col instance_number format 99 heading inID

select sql_id, plan_hash_value,dba_hist_sqlstat.snap_id,
to_char(dba_hist_snapshot.BEGIN_INTERVAL_TIME,'dd-mm_hh24:mi') snap_beg,dba_hist_sqlstat.instance_number,invalidations_delta,
parse_calls_delta,executions_delta,fetches_delta,buffer_gets_delta,
disk_reads_delta,rows_processed_delta,elapsed_time_delta/1000 elapsed_time_delta,iowait_delta/1000 iowait_delta,clwait_delta/1000 cluster_wait_delta,ccwait_delta/1000 concurrency_wait_delta,optimizer_mode, optimizer_cost,
substr(optimizer_mode,1,3) opt,
case when executions_delta = 0 then NULL
when rows_processed_delta = 0 then NULL
else(rows_processed_delta/executions_delta) end rows_ex,
case when executions_delta = 0 then NULL
when clwait_delta = 0 then NULL
else(clwait_delta/executions_delta)/1000 end cwt_ex,
case when executions_delta = 0 then NULL
when elapsed_time_delta = 0 then NULL
else(elapsed_time_delta/executions_delta)/1000 end ela_ex
from dba_hist_sqlstat, dba_hist_snapshot
where sql_id='&sql_id'
-- and plan_hash_value='and PLAN_HASH_VALUE'
and dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id
and dba_hist_sqlstat.instance_number=dba_hist_snapshot.instance_number
order by dba_hist_sqlstat.instance_number,dba_hist_sqlstat.snap_id
/

Oracle Used space in Meg by segment type

set pagesize 50000
set line 80

col "Total Used Meg" format 999,999,990
col "Data part" format 999,999,990
col "Index part" format 999,999,990
col "LOB part" format 999,999,990
col "RBS part" format 999,999,990
tti 'Used space in Meg by segment type'

select sum(bytes)/1024/1024 "Total Used",sum( decode( substr(segment_type,1,5), 'TABLE', bytes/1024/1024, 0)) "Data part",
sum( decode( substr(segment_type,1,5), 'INDEX', bytes/1024/1024, 0)) "Index part",
sum( decode( substr(segment_type,1,3), 'LOB', bytes/1024/1024, 0)) "LOB part",
sum( decode(segment_type,'ROLLBACK', bytes/1024/1024, 0)) "RBS part",
sum( decode(segment_type,'TEMPORARY', bytes/1024/1024, 0)) "TEMP part"
from sys.dba_segments
/

tti off

tti "Total database size"

select sum(bytes)/1024/1024 "Total DB size in Meg" from sys.v_$datafile
/
tti off



SQL> @used_space.sql

Tue Sep 13 page 1
Used space in Meg by segment type

Total Used Data part Index part LOB part RBS part TEMP part
---------- ------------ ------------ ------------ ------------ ----------
2060.75 1,218 490 274 0 0


Tue Sep 13 page 1
Total database size

Total DB size in Meg
--------------------
2728.75

Oracle object are accessed

col object for A34
col owner for A16 trunc
col type for A12 head Obj|type
col accessed for 9999 head "Accessed|by # users"

select A.owner,A.object,A.TYPE,count(*) accessed
from v$ACCESS A
where A.owner not in ('SYS','SYSTEM')
and A.TYPE = 'TABLE'
group by A.owner,A.object,A.TYPE
order by accessed
/



1 select A.owner,A.object,A.TYPE,count(*) accessed
2 from v$ACCESS A
3 where 1=1
4 -- and A.owner not in ('SYS','SYSTEM')
5 and A.TYPE = 'TABLE'
6 group by A.owner,A.object,A.TYPE
7* order by accessed
SQL> /

Obj Accessed
OWNER OBJECT type by # users
---------------- ---------------------------------- ------------ ----------
SYSTEM AQ$_QUEUE_TABLES TABLE 1
SYS EXPACT$ TABLE 1
SCHEDULER$_LIGHTWEIGHT_JOB TABLE 1
WRI$_ADV_DEF_PARAMETERS TABLE 1
WRI$_SQLSET_STATEMENTS TABLE 1
IDL_UB2$ TABLE 1
COL_USAGE$ TABLE 1
WRI$_ADV_REC_ACTIONS TABLE 1
KOTAD$ TABLE 1
DAM_CONFIG_PARAM$ TABLE 1
WRI$_ADV_TASKS TABLE 1
IDL_UB1$ TABLE 1
WRI$_ADV_DEFINITIONS TABLE 1
WRI$_ADV_ACTIONS TABLE 1

Oracle users' table and index on tablespace

set pagesize 60
set echo off
spool users_obj_tab.txt
column tablespace_name format a20
column owner format a20
column objects format a20
break on owner on tablespace_name
select substr(owner,1,20) owner,substr(tablespace_name,1,32) tablespace_name,count(*)||decode(count(*),1,' table',' tables') objects
from sys.dba_tables
group by substr(owner,1,20),substr(tablespace_name,1,32)
union
select substr(owner,1,20) owner,substr(tablespace_name,1,32) tablespace_name, count(*)||decode(count(*),1,' index',' indexes') objects
from sys.dba_indexes
group by substr(owner,1,20),substr(tablespace_name,1,32)
/
spool off




OWNER TABLESPACE_NAME OBJECTS
-------------------- -------------------- --------------------
ABC USERS 1 table
ANUJ USERS 2 indexes
4 tables
ANUJREP USERS 1 index
1 table
ANUJTEST ANUJTEST 1 table
APEX_030200 SYSAUX 1101 indexes
356 tables
4 tables
APEX_040000 TSAPEXU 1362 indexes
422 tables
4 tables
APPQOSSYS SYSAUX 2 tables
CTXSYS SYSAUX 34 tables
58 indexes
13 tables
DBSNMP SYSAUX 11 indexes
21 tables
2 indexes
4 tables
EXFSYS SYSAUX 20 tables
40 indexes
1 index
27 tables

Oracle table PRIVILEGE info

tab_priv.sql


TABLE PRIVILEGE
TABLE PRIVILEGES
tab priv

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

-- TABLE_PRIVILEGES

-- Grants on objects for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee:





Accept owner prompt 'Enter owner Name or all..'
col grantee format a15
col owner format a15
col table_name format a25
col grantor format a15
col select_priv format a1 Heading 's|e|l'
col insert_priv format a1 Heading 'i|n|s'
col delete_priv format a1 Heading 'D|E|L'
col update_priv format a1 Heading 'U|P|D'
col references_priv format a1 Heading 'R|E|F'
col alter_priv format a1 Heading 'A|l|t'
col index_priv format a1 heading 'I|N|D'
col created format a11 heading 'Grnted on:'
break on owner skip 4 on table_name skip 1 on report
set linesize 130
btitle skip 1 center-
'Y=granted,N=not Granted,G=granted with grant option,'-
's=Granted on specific column,A=granted on A11 olumns'-

select owner,TABLE_NAME,GRANTEE,GRANTOR,CREATED,SELECT_PRIV,INSERT_PRIV,DELETE_PRIV,UPDATE_PRIV,REFERENCES_PRIV,ALTER_PRIV,INDEX_PRIV
from TABLE_PRIVILEGES
where owner not in ('SYS')
and owner=upper('&owner')
order by owner,table_name,grantor,grantee
/



SQL> @tab_priv
Enter owner Name or all..SYSTEM
old 4: and owner=upper('&owner')
new 4: and owner=upper('SYSTEM')

s i D U R A I
e n E P E l N
OWNER TABLE_NAME GRANTEE GRANTOR Grnted on: l s L D F t D
--------------- ------------------------- --------------- --------------- ----------- - - - - - - -
SYSTEM DEF$_AQCALL SYS SYSTEM G N N N N N N

DEF$_CALLDEST SYS SYSTEM G N N N N N N

DEF$_DESTINATION SYS SYSTEM G N N N N N N

DEF$_ERROR SYS SYSTEM G N N N N N N

HELP PUBLIC SYSTEM Y N N N N N N

MVIEW$_ADV_INDEX PUBLIC SYSTEM Y A Y A N N N

MVIEW$_ADV_OWB PUBLIC SYSTEM Y A Y A N N N

MVIEW$_ADV_PARTITION PUBLIC SYSTEM Y A Y A N N N

OL$ PUBLIC SYSTEM Y A Y A N N N

OL$HINTS PUBLIC SYSTEM Y A Y A N N N

OL$NODES PUBLIC SYSTEM Y A Y A N N N

PRODUCT_PRIVS PUBLIC SYSTEM Y N N N N N N

REPCAT$_REPPROP SYS SYSTEM G N N N N N N

REPCAT$_REPSCHEMA SYS SYSTEM G N N N N N N



Y=granted,N=not Granted,G=granted with grant option,s=Granted on specific column,A=granted on A11 olumns

14 rows selected.



SELECT grantor, table_name, privilege FROM user_tab_privs_recd;
/

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;