SCRIPTS - ADMINISTRAÇÃO BANCO DE DADOS ORACLE

Área de scripts destinados à administração do banco de dados, armazenamento, sessões, locks entre outros.

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.

Um comentário:

  1. Parabéns.. gostei! Vou utilizar alguns com seus devidos créditos.

    ResponderExcluir

Página inicial

Translate

# ACE Program

#Oracle

#Oracle
Disclaimer: The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

#Blog reconhecido

#ARTICULISTA

Marcadores

Postagens populares