select * from v$version;
col "SESSAO" for a50
set lines 190
SELECT DECODE(l.request, 0, 'Bloqueando: ', 'Aguardando: ') || 'Inst-> ' ||
s.inst_id || ' Sid:serial-> ' || l.sid || ',' || s.serial# "SESSAO",
substr(s.username, 1, 15) Username,
l.lmode,
l.request,
l.type,
sql_hash_value,
ctime "TEMPO"
FROM GV$LOCK L, GV$SESSION S
WHERE (l.id1, l.id2, l.type) IN
(SELECT l2.id1, l2.id2, l2.type FROM GV$LOCK l2 WHERE l2.request > 0)
AND s.sid = l.sid
ORDER BY l.id1, l.request, l.ctime DESC;
col sessao for a25
col event for a50
col username for a25
col osuser for a25
set lines 200
with a as (
select level lev,
CONNECT_BY_ROOT COL1 BLOCKER, col1 lev1
from ((select inst_id||','||sid col1,event,sql_id,program,blocking_instance
||','||blocking_session col2,seconds_in_wait
from gv$session))
connect by nocycle prior col2=col1
start with col2 in (select blocking_instance ||','||blocking_session from gv$session where
blocking_session is not null)
) ,
b as (
select distinct lev1 from a where (a.lev,a.blocker) in (select max(lev),blocker from a group by
blocker)
)
select
lpad(' ',3*(level-1)) || col1 sessao,username, osuser, event,sql_id,seconds_in_wait
from ((select inst_id||','||sid col1,username, osuser, event,sql_id,program,blocking_instance
||','||blocking_session col2,seconds_in_wait
from gv$session))
connect by nocycle prior col1=col2
start with col1 in (select lev1 from b)
;
SELECT p2raw, p2/power(16,8) blocking_sid, p1 mutex_id, sid blocked_sid FROM v$session WHERE event LIKE 'cursor:%' AND state='WAITING'
select name, detected_usages from dba_feature_usage_statistics where detected_usages > 0;
set linesize 190
set pagesize 100
COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40
SELECT occupant_name "ITEM",
OCCUPANT_DESC "DESCRICAO",
space_usage_kbytes / 1048576 "SPACE USED (GB)",
schema_name "SCHEMA"
FROM v$sysaux_occupants
ORDER BY 1;
select * from (
select 'grant ' || privilege || decode(privilege,'READ',' on directory "','WRITE',' on directory "',' on "' || owner || '"."') ||
table_name || '" to "' || grantee || decode(grantable, 'YES', '" with grant option','"') || ';' from dba_tab_privs where
grantee in ('ANDERSON','GRAF')
union all
select 'grant '||privilege||' to "'||grantee||'"'||decode(admin_option,'YES',' with admin option')||';' from dba_sys_privs where
grantee in ('ANDERSON','GRAF')
union all
select 'grant "'||granted_role||'" to "'||grantee||'"'||decode(admin_option,'YES',' with admin option')||';' from dba_role_privs where
grantee in ('ANDERSON','GRAF'));
set lines 190 col Component for a70 select comp_name "COMPONENT", version, status from dba_registry;
set serveroutput on
declare
dbf number;
tmpdbf number;
lgf number;
ctl number;
soma number;
begin
select trunc(sum(bytes/1024/1024),2) into dbf from v$datafile;
select trunc(sum(bytes/1024/1024),2) into tmpdbf from v$tempfile;
select trunc(sum(bytes/1024/1024),2) into lgf from v$log l, v$logfile lf where l.group# = lf.group#;
select trunc(sum(block_size*file_size_blks/1024/1024),2) into ctl from v$controlfile;
select trunc((dbf+tmpdbf+lgf+ctl)/1024,2) into soma from dual;
DBMS_OUTPUT.PUT_LINE(chr(10));
DBMS_OUTPUT.PUT_LINE('Datafiles: '|| dbf ||' MB');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('Tempfiles: '|| tmpdbf ||' MB');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('Logfiles: '|| lgf ||' MB');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('Controlfiles: '|| ctl ||' MB');
DBMS_OUTPUT.PUT_LINE(chr(0));
DBMS_OUTPUT.PUT_LINE('Total Tamanho: '|| soma ||' GB');
end;
/
select /*+ rule */
table_name,
decode(partitioned, '/', 'NO', partitioned) partitioned,
num_rows,
data_mb,
indx_mb,
lob_mb,
total_mb,
OWNER
from (select data.table_name,
DATA.OWNER,
partitioning_type ||
decode(subpartitioning_type,
'none',
null,
'/' || subpartitioning_type) partitioned,
num_rows,
nvl(data_mb, 0) data_mb,
nvl(indx_mb, 0) indx_mb,
nvl(lob_mb, 0) lob_mb,
nvl(data_mb, 0) + nvl(indx_mb, 0) + nvl(lob_mb, 0) total_mb
from (select table_name,
OWNER,
nvl(min(num_rows), 0) num_rows,
round(sum(data_mb), 2) data_mb
from (select table_name, num_rows, data_mb, OWNER
from (select a.table_name,
A.OWNER,
a.num_rows,
b.bytes / 1024 / 1024 as data_mb
from DBA_tables a, DBA_segments b
where a.table_name = b.segment_name
and a.owner = b.owner))
group by table_name, OWNER) data,
(select a.table_name,
round(sum(b.bytes / 1024 / 1024), 2) as indx_mb
from DBA_indexes a, DBA_segments b
where a.index_name = b.segment_name
and a.owner = b.owner
group by a.table_name) indx,
(select a.table_name,
round(sum(b.bytes / 1024 / 1024), 2) as lob_mb
from DBA_lobs a, DBA_segments b
where a.segment_name = b.segment_name
and a.owner = b.owner
group by a.table_name) lob,
user_part_tables part
where data.table_name = indx.table_name(+)
and data.table_name = lob.table_name(+)
and data.table_name = part.table_name(+))
-- WHERE OWNER = 'TESTE'
order by total_mb DESC, table_name
select
nvl(dbms_lob.getlength(CASE_DATA),0)+1 +
nvl(vsize(CASE_NUMBER ),0)+1 +
nvl(vsize(CASE_DATA_NAME),0)+1 +
nvl(vsize(LASTMOD_TIME_T),0)+1
"Total bytes per row"
from
mytab
where
case = 123;
select TIPO,
round(sum(nvl(dbms_lob.getlength(XML_RETORNO), 0) + 1) / 1024 / 1024,
2) "TAMANHO_LOB"
from MINHA_TABELA
group by TIPO
order by 2 desc
SELECT created, status, sql_attr.attr_val outline_hints FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr WHERE sql_profiles.signature = sql_attr.signature AND sql_profiles.name = 'PROFILE_ANDERSON' ORDER BY sql_attr.attr# ASC;
SELECT extractValue(value(h), '.') AS hint
FROM sys.sqlobj$data od,
sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),
'/outline_data/hint'))) h
WHERE so.name = 'PROFILE_NAME'
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;
Select a.sid,
serial#,
a.osuser,
a.machine,
c.SQL_FULLTEXT,
a.terminal,
b.SEGTYPE,
a.program,
a.USERNAME,
a.status,
round((b.blocks * 8192) / 1024 / 1024, 2) "Tam TEMP",
c.last_load_time
from v$sort_usage b, v$session a, V$SQLAREA c
where a.saddr = b.SESSION_ADDR
and c.hash_value = b.SQLHASH
order by c.last_load_time
set lines 200
define DAYS_AGO=40
with
pivot1 as
(
select min(snap_id) AS begin_snap_id
from dba_hist_snapshot
where trunc( begin_interval_time, 'DD') >= trunc(sysdate - &DAYS_AGO, 'DD')
),
pivot2 as
(
SELECT
trunc(ash.sample_time,'MI') sample_time,
ash.SESSION_ID,
ash.SESSION_SERIAL#,
ash.SQL_ID,
ash.sql_exec_id,
U.temporary_tablespace,
max(temp_space_allocated)/(1024*1024) max_temp_per_sql_mb
from
dba_hist_active_sess_history ash
INNER JOIN dba_users U ON ash.user_id = U.user_id
where
ash.session_type = 'FOREGROUND'
and ash.temp_space_allocated > 0
-- and U.temporary_tablespace = 'TEMP3'
and snap_id >= (select begin_snap_id from pivot1)
group by
trunc(ash.sample_time,'MI') ,
ash.SESSION_ID,
ash.SESSION_SERIAL#,
ash.SQL_ID,
ash.sql_exec_id,
U.temporary_tablespace
),
pivot3 as
(
select temporary_tablespace, sample_time, sum(max_temp_per_sql_mb) total_temp_permin_mb
from pivot2
group by temporary_tablespace, sample_time
order by temporary_tablespace, sample_time
),
tbspc as
(
select (select name from v$tablespace t where t.ts# = tablespace_id) tablespace_name,
trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss')) rtime,
min(tablespace_maxsize * 8192 / 1024 / 1024) tablespace_minsize,
max(tablespace_maxsize * 8192 / 1024 / 1024) tablespace_maxsize
from dba_hist_tbspc_space_usage
group by tablespace_id, trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'))
)
select temporary_tablespace, tbspc.tablespace_minsize tablespace_min_size, tbspc.tablespace_maxsize temp_max_size_mb, trunc(sample_time, 'DD') as day, max
(total_temp_permin_mb) max_temp_per_day_mb
from pivot3
inner join tbspc ON tbspc.tablespace_name = pivot3.temporary_tablespace and tbspc.rtime = trunc(sample_time, 'DD')
group by temporary_tablespace, tbspc.tablespace_minsize, tbspc.tablespace_maxsize , trunc(sample_time, 'DD')
--having trunc(sample_time, 'DD') > to_date('01-11-13', 'DD-MM-YY')
order by temporary_tablespace, day;
column sum_max_mb format 999,999,999; column temporary_tablespace format A20 WITH pivot1 AS ( SELECT trunc(ash.sample_time,'MI') sample_time, ash.SESSION_ID, ash.SESSION_SERIAL#, ash.SQL_ID, ash.sql_exec_id, U.temporary_tablespace, max(temp_space_allocated)/(1024*1024) max_temp_mb FROM dba_hist_active_sess_history ash, dba_users U WHERE ash.user_id = U.user_id and ash.session_type = 'FOREGROUND' and ash.temp_space_allocated > 0 GROUP BY trunc(ash.sample_time,'MI'), ash.SESSION_ID, ash.SESSION_SERIAL#, ash.SQL_ID, ash.sql_exec_id, U.temporary_tablespace ) SELECT temporary_tablespace, to_char(sample_time,'dd/mm/yyyy hh24:mi') sample_time, sum(max_temp_mb) sum_max_mb from pivot1 GROUP BY sample_time, temporary_tablespace ORDER BY temporary_tablespace, sample_time;
set lines 200
set pages 800
COLUMN module format A20
COLUMN sql_opname format A20
COLUMN etime_secs FORMAT 999,999.9
COLUMN etime_mins FORMAT 999,999.9
COLUMN user_id FORMAT 999999
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 99999
COLUMN username FORMAT A25
COLUMN inst_id FORMAT 99
COLUMN sql_opname FORMAT A10
COLUMN sql_id FORMAT A13
COLUMN sql_exec_id FORMAT 9999999999
COLUMN max_temp_mb FORMAT 999,999,999
COLUMN sql_start_time FORMAT A25
COLUMN sql_end_time FORMAT A25
SELECT ASH.instance_number inst_id,
ASH.user_id,
ASH.session_id sid,
ASH.session_serial# serial#,
ASH.sql_id,
--ASH.sql_exec_id,
ASH.sql_opname,
ASH.module,
MIN(sample_time) sql_start_time,
MAX(sample_time) sql_end_time,
((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24) etime_secs ,
((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (60*24) etime_mins ,
MAX(temp_space_allocated)/(1024*1024) max_temp_mb
FROM dba_hist_active_sess_history ASH
WHERE ASH.session_type = 'FOREGROUND'
AND ASH.sql_id IS NOT NULL
AND sample_time BETWEEN to_timestamp('20-12-2017 05:00', 'DD-MM-YYYY HH24:MI') AND to_timestamp('20-12-2017 06:00', 'DD-MM-YYYY HH24:MI')
--and ASH.sql_id = 'd1s02myktu19h'
GROUP BY ASH.instance_number,
ASH.user_id,
ASH.session_id,
ASH.session_serial#,
ASH.sql_id,
ASH.sql_opname,
ASH.sql_exec_id,
ASH.module
HAVING MAX(temp_space_allocated) > 0
order by MAX_TEMP_MB;
set lines 200
col name for a40
col username for a10
col value for a40
select a.sid, c.username, a.name, a.value
from v$ses_optimizer_env a
join v$sys_optimizer_env b on a.id = b.id
join v$session c on a.sid = c.sid
where a.value <> b.value
and c.username is not null
and c.username not in ('SYS', 'SYSTEM', 'DBSNMP')
order by a.sid, a.name;
select tablespace_name, used_space*8192/1024/1024, tablespace_size*8192/1024/1024, used_percent from dba_tablespace_usage_metrics
select * from (select (select name from v$tablespace t where t.ts# = tablespace_id) tablespace_name,
rtime,
tablespace_size*8192/1024/1024 tablespace_size,
tablespace_maxsize*8192/1024/1024 tablespace_maxsize,
tablespace_usedsize*8192/1024/1024 tablespace_usedsize
from dba_hist_tbspc_space_usage) where tablespace_name='TABLESPACE_NAME' order by rtime desc
col quota format a10
select username
, tablespace_name
, decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from dba_ts_quotas
where tablespace_name not in ('TEMP')
/
set lines 200
select a.*,
round(((a.TOTAL_MB - a.FREE_MB) / decode(TOTAL_MB,0,1,TOTAL_MB)) * 100, 2) as "PERC_USED"
from (select GROUP_NUMBER,
NAME,
TYPE,
round((TOTAL_MB - REQUIRED_MIRROR_FREE_MB) /
decode(TYPE, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1),
2) as "TOTAL_MB",
((FREE_MB - REQUIRED_MIRROR_FREE_MB) /
decode(TYPE, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1)) as "FREE_MB"
from sys.v_$asm_diskgroup) a
/
dg=DISKGROUP_NAME; for dir in $(asmcmd ls $dg); do echo $dg/$dir; asmcmd du $dg/$dir; done
set lines 190
col name for a60
col value for a15
col description for a70
SELECT NAME,
VALUE,
DECODE(isdefault, 'TRUE', 'Y', 'N') AS "Default",
DECODE(isem, 'TRUE', 'Y', 'N') AS sesmod,
DECODE(isym, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') AS sysmod,
DECODE(imod, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') AS modified,
DECODE(iadj, 'TRUE', 'Y', 'N') AS adjusted,
description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id AS INSTANCE,
x.indx + 1,
ksppinm AS NAME,
ksppity,
ksppstvl AS VALUE,
ksppstdf AS isdefault,
DECODE(BITAND(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') AS isem,
DECODE(BITAND(ksppiflg / 65536, 3),
1,
'IMMEDIATE',
2,
'DEFERRED',
'FALSE') AS isym,
DECODE(BITAND(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') AS imod,
DECODE(BITAND(ksppstvf, 2), 2, 'TRUE', 'FALSE') AS iadj,
ksppdesc AS description
FROM x$ksppi x, x$ksppsv y
WHERE x.indx = y.indx
AND SUBSTR(ksppinm, 1, 1) = '_'
AND x.inst_id = USERENV('Instance'))
ORDER BY NAME;
col OBJECT_NAME for a60 select owner, object_name, object_type, status from dba_objects where status <> 'VALID' order by 1, 2; --Only 11G (dba_invalid_objects) select owner, object_name, object_type, status from dba_invalid_objects where status <> 'VALID' order by 1, 2
select owner,
decode(object_type,
null,
'===========================>',
object_type) as "OBJECT_TYPE",
count(object_type) as "TOTAL",
decode(grouping(owner), 0, null, 1, 'Total de objectos invalidos.') as " "
from dba_objects
where status <> 'VALID'
group by rollup(owner, object_type)
order by owner, object_type desc;
set linesize 190 col "Buffer Hit Ratio" format a16; col "Dictionary Hit Ratio" format a20; col "Library Hit Ratio" format a17; col "RedoLog Wait" format a12; col "PGA Hit Ratio" format a13; with a as ( select to_char(round(((1-(sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+(sum(decode(name,'consistent gets',value,0))))))*100),4),'99.99') || '%' as "Buffer Hit Ratio" from v$sysstat ), b as ( select to_char(round(((1-(sum(getmisses)/sum(gets)))*100),4),'999.99') || '%' as "Dictionary Hit Ratio" from v$rowcache ), c as ( select to_char(round(100-((((sum(reloads)/sum(pins))))),4),'999.99') || '%' as "Library Hit Ratio" from v$librarycache ), d as ( select to_char(round((100-(100*sum(decode(name,'redo log space requests',value,0))/sum(decode(name,'redo entries',value,0)))),4),'999.999') || '%' as "RedoLog Wait" from sys.v_$sysstat ), e as ( select to_char(round(value,4),'999.99') ||'%' "PGA Hit Ratio" from sys.v_$pgastat where name = 'cache hit percentage' ) select * from a,b,c,d,e;
select
'session_cached_cursors' parameter,
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from
( select
max(s.value) used
from
v$statname n,
v$sesstat s
where
n.name = 'session cursor cache count' and
s.statistic# = n.statistic#
),
( select
value
from
v$parameter
where
name = 'session_cached_cursors'
)
union all
select
'open_cursors',
lpad(value, 5),
to_char(100 * used / value, '990') || '%'
from
( select
max(sum(s.value)) used
from
v$statname n,
v$sesstat s
where
n.name in ('opened cursors current', 'session cursor cache count') and
s.statistic# = n.statistic#
group by
s.sid
),
( select
value
from
v$parameter
where
name = 'open_cursors'
)
/
select to_char(FIRST_TIME, 'yyyy/mm/dd hh24'), count(*) from v$log_history group by to_char(FIRST_TIME, 'yyyy/mm/dd hh24') order by 1;
set lines 200 pages 800
select to_char(first_time, 'DD/MM/YYYY') day,
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '00', 1, 0)), '999') "00",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '01', 1, 0)), '999') "01",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '02', 1, 0)), '999') "02",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '03', 1, 0)), '999') "03",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '04', 1, 0)), '999') "04",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '05', 1, 0)), '999') "05",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '06', 1, 0)), '999') "06",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '07', 1, 0)), '999') "07",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '08', 1, 0)), '999') "08",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '09', 1, 0)), '999') "09",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '10', 1, 0)), '999') "10",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '11', 1, 0)), '999') "11",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '12', 1, 0)), '999') "12",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '13', 1, 0)), '999') "13",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '14', 1, 0)), '999') "14",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '15', 1, 0)), '999') "15",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '16', 1, 0)), '999') "16",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '17', 1, 0)), '999') "17",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '18', 1, 0)), '999') "18",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '19', 1, 0)), '999') "19",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '20', 1, 0)), '999') "20",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '21', 1, 0)), '999') "21",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '22', 1, 0)), '999') "22",
to_char(sum(decode(substr(to_char(first_time, 'HH24'), 1, 2), '23', 1, 0)), '999') "23",
sum(1) "TOTAL_IN_DAY"
from v$log_history where first_time > trunc(sysdate - to_number(to_char(last_day(sysdate),'DD')))
group by to_char(first_time, 'DD/MM/YYYY')
order by to_date(day,'DD/MM/YYYY') asc;
col STARTUP_TIME for a19 col VERSION for a11 col DB_NAME for a8 col INSTANCE_NAME for a15 col HOST_NAME for a12 select INSTANCE_NUMBER, to_char(STARTUP_TIME, 'DD/MM/YYYY HH24:MI:SS') as STARTUP, VERSION, DB_NAME, INSTANCE_NAME, HOST_NAME from DBA_HIST_DATABASE_INSTANCE order by STARTUP_TIME;
col table_name for a40 select owner, index_name, table_owner, table_name, status from dba_indexes where status <> 'VALID' order by 1, 2, 3, 4;
select table_name, index_name, column_name from dba_ind_columns where index_name = 'INDEX_NAME' order by index_name, column_position;
set lines 190
col sid for 9999
col "serial#" for 99999
col message for a80
comp sum of "% COMPLETE" on report
break on report
SELECT SID,
SERIAL#,
MESSAGE,
ROUND(SOFAR / TOTALWORK * 100, 2 / count(*)) "% COMPLETE"
FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK
GROUP BY SID, SERIAL#, ROUND, ROUND(SOFAR / TOTALWORK * 100, 2) /
set lines 190
select substr(o.object_name, 1, 25) objeto,
l.session_id session_id,
l.oracle_username ora_user,
l.os_user_name os_user
from dba_objects o, v$locked_object l
where l.object_id = o.object_id
and o.object_name = 'OBJECT_NAME'
order by 1, 3, 4
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; select to_char(TIMESTAMP,'dd/mm/yyyy hh24:mi') HORARIO, INSERTS, UPDATES, DELETES from dba_tab_modifications where TABLE_OWNER='TESTE' and TABLE_NAME='TESTE'; --obs: a sumarização é zerada a cada coleta de estatística
set lines 200
column c0 heading "Owner" format a12
column c1 heading "Object|Name" format a30
column c2 heading "Object|Type" format a8
column c3 heading "Number of|Blocks in|Buffer|Cache" format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer" format 999
column c5 heading "Buffer|Pool" format a7
column c6 heading "Block|Size" format 99,999
select a.owner c0,
a.object_name c1,
a.object_type c2,
sum(num_blocks) c3,
(sum(a.num_blocks) / greatest(sum(s.blocks), .001)) * 100 c4,
s.buffer_pool c5,
sum(bytes)/sum(blocks) c6
from (select o.owner,
o.object_name,
o.subobject_name,
o.object_type,
count(distinct file# || block#) num_blocks
from dba_objects o, v$bh bh
where o.data_object_id = bh.objd
and o.owner not in ('SYS', 'SYSTEM')
and bh.status != 'free'
group by o.owner, o.object_name, o.subobject_name, o.object_type
order by count(distinct file# || block#) desc) a,
dba_segments s
where s.segment_name = a.object_name
and s.owner = a.owner
and s.segment_type = a.object_type
and nvl(s.partition_name, '-') = nvl(a.subobject_name, '-')
and s.segment_name = 'SEGMENT_NAME'
group by a.owner, object_name, object_type, buffer_pool
having sum(a.num_blocks) > 10
order by sum(a.num_blocks) desc;
| Caso você queira compartilhar algum script que não está na lista, entre em contato pelo e-mail anderson.graf87@gmail.com ou deixe um comentário. |
Parabéns.. gostei! Vou utilizar alguns com seus devidos créditos.
ResponderExcluir