scripts

-- CRESCIMENTO DO BANCO @CRESCIMENTO
-- VISULIZA DATAFILES @FDATAFILE
-- NOME DO BANCO @DB
-- VERIFICA EXTENT QUE EST� NO LIMITE @EXTENTS
-- MOSTRA O ESPACO LIVRE EM TODAS AS TABLESPACES
-- VERIFICA OBJETOS INVALIDOS @INVALIDO
-- CRIA SCRIPT PARA VALIAR OBJETOS INV�LIDOS
-- VERFICA ARCHIVES @LOG
-- MATA USU�RIO QUE EST� TAVANDO OS DEMAIS @MATALOCK
-- MATA USU�RIO PELO USERNAME
-- PESQUISA OBJETO NO BANCO @OBJETO
-- VERIFICA PRIVLEGIOS DO USUARIO @PRIVI
-- RECONSTROI INDICES GRANDES -- @REBUILD
-- VERIFICA SEGMENTOS DE ROLLBACK @ROLL
-- MOSTRA OS USUARIO COM TRASA��ES ATIVAS E NOS SEGMENTOS DE ROLLBACK
-- VERIFICA BLOCLO DE ROLLBACK POR SID DE USUARIO
-- MOSTRA SQL DE TODAS TRANSA��ES ATIVAS
-- MOSTRA O NOME E TIPO DO SEGMENTO ATRAV�S DO FILE E BLOCLO
-- MOSTRA O SQL PRO USERNAME @TEXTOUSER
-- MOSTRA O SQL POR SID @TEXTOUSERID
-- MOSTRA SQL E ESTATISTICAS DA SESS�O, ATRV� DO SID
-- ESTATISCAS DO SORT, -- DE VE ESTAR MAIS DE 99%
-- ESTATISTICAS DA LIBRARY CACHE - TEM QUE ESTAR ACIMA DE 90%
-- LATCHES ATIVOS NO MOMENTO
-- VERIFICA LATCHES EM WAIT
-- RELA��O DE TABLESPACES E DATAFILES
-- COLOCAR SESS�O EM TRACE PE USERNAME (PRECISAR ESTAR COMO SYS) @TRACE
-- USUARIOS ATIVOS
-- MOSTRA WAIT POR SID
-- MOSTRA TODOS WAITS INCLUSIVE SID E SEGUNDOS
---------------------------------------------------------------------------------------------------------------------------crescimento do banco @crescimento
select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');

-- visuliza datafiles @fdatafile
SELECT FILE_ID,SUBSTR(FILE_NAME,1,40),BYTES, TABLESPACE_NAME
FROM DBA_DATA_FILES
order by TABLESPACE_NAME;

-- nome do banco @db
select name from v$database;
/

-- verifica extent que est� no limite @extents

select owner,segment_type,segment_name,extents,max_extents from dba_segments where
segment_type in ('TABLE','INDEX') and extents = max_extents
order by segment_type,extents
/
-- FREE_DIF.SQL
-- Mostra o espaco livre em todas as tablespaces
--
TTITLE ' FREE TABLESPACE '
break on report
comp sum of ocup on report
comp sum of aloc on report
comp sum of free on report
col tablespace_name format a15
col ocup format 99,999,999,999
col aloc format 99,999,999,999
col free format 99,999,999,999
col "%FREE" format 999.99
select   a.tablespace_name,a.bytes-b.bytes ocup ,
a.bytes aloc ,b.bytes free ,
to_char(round((b.bytes/a.bytes)*100,2),'999.99') "%FREE"
from (select sum(bytes) bytes,tablespace_name
      from dba_data_files
      group by tablespace_name ) a,
     ( select sum(bytes) bytes , tablespace_name
       from dba_free_space
       group by tablespace_name ) b
where a.tablespace_name=b.tablespace_name
/
-- verifica objetos invalidos @invalido
col object_name for a30
select object_name,object_type from DBA_objects where
status = 'INVALID' order by object_type,object_name
/
-- CRIA SCRIPT PARA VALIAR OBJETOS INV�LIDOS
select
    decode( OBJECT_TYPE, 'PACKAGE BODY',
    'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
    'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
   dba_objects
where
   STATUS = 'INVALID' and OBJECT_TYPE in
   ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' )
order by OWNER, OBJECT_TYPE, OBJECT_NAME
/

-- verfica archives @log
select sequence#,status,first_change#,first_time,ARCHIVED from v$log
/
-- mata usu�rio que est� tavando os demais @matalock
select'alter system kill session '||''''|| sid||','|| serial#||''''
from v$session where sid in (select * from dba_blockers)
/
-- mata usu�rio pelo username
select username||' '||machine|| chr(10)||'alter system kill session '||''''||sid||','||serial#||''''||';' from v$session
where username =upper('&username')
/
-- pesquisa objeto no banco @objeto
SET LINESIZE 1000
SELECT OWNER,OBJECT_TYPE,OBJECT_NAME FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE UPPER('%&ALE%');

-- VERIFICA PRIVLEGIOS DO USUARIO @PRIVI
spool privilegio.ale
select * from dba_role_privs
where grantee = UPPER('&ALE')
/
select * from dba_sys_privs
where grantee = UPPER('&&ALE')
/
select privilege ||' on '|| owner||'.'|| table_name Privilegio
from dba_tab_privs
where grantee = UPPER('&&ALE')
/
spool off
-- RECONSTROI INDICES GRANDES -- @REBUILD
SELECT 'ALTER INDEX ' || SEGMENT_NAME || ' REBUILD STORAGE (INITIAL ' ||
ROUND((BYTES*1.3)/1048576) || 'M NEXT ' || ROUND(((BYTES*1.3)*.25)/1048576) ||
'M MINEXTENTS 1 MAXEXTENTS 5);' FROM DBA_SEGMENTS WHERE OWNER = UPPER('%&OWNER%'); AND
SEGMENT_TYPE = 'INDEX' AND BYTES < 100000000 AND EXTENTS > 1 ORDER BY BYTES
-- VERIFICA SEGMENTOS DE ROLLBACK @ROLL
select substr(r.name,1,6), a.extents,a.rssize Tamanho, a.xacts TRans ,a.gets,a.hwmsize M_agua,
       a.wraps,a.extends,a.curext,a.optsize optimal
from   v$rollname r , v$rollstat a
where r.usn = a.usn
/
-- MOSTRA OS USUARIO COM TRASA��ES ATIVAS E OS RESPECITVOS SEGMENTOS DE
-- ROLLBACK UTILIZADOS
select s.username,a. xidusn, a.used_ublk
from v$transaction a, v$session s
where s.saddr = a.ses_addr
order by xidusn
/

-- VERIFICA BLOCLO DE ROLLBACK POR SID DE USUARIO
    SELECT a.used_ublk
    FROM v$transaction a, v$session b
    WHERE a.addr = b.taddr
    AND b.sid = &n_sid;

-- MOSTRA SQL DE TODAS TRANSA��ES ATIVAS
set linesize 80
set verify off message off echo off pause off timing off time off
set feedback off
column o format a8 heading 'O/S|User'
column u format a10 heading 'Oracle|Userid'
column s format a12 heading 'R-S|Name'
column txt format a45 heading 'Current Statement' word
select osuser o,
username u,
segment_name s,
sa.sql_text txt
from v$session s,
v$transaction t,
dba_rollback_segs r,
v$sqlarea sa
where s.taddr = t.addr
and t.xidusn = r.segment_id(+)
and s.sql_address = sa.address(+)
/

-- MOSTRA O NOME E TIPO DO SEGMENTO ATRAV�S DO FILE E BLOCLO
select SEGMENT_NAME, SEGMENT_TYPE from dba_extents
where dba_extents.FILE_ID = &FIL AND
&BLK between dba_extents.block_id and dba_extents.block_id + dba_extents.blocks - 1
/
select segment_name
from dba_extents
where file_id =&n_FILE and &N_BLOCK between block_id and block_id+blocks;
/
-- MOSTRA O SQL PRO USERNAME @TEXTOUSER
SELECT OSUSER,
     sid,SERIAL#,
     SQL_TEXT
     FROM V$SESSION, V$SQL
     WHERE V$SESSION.SQL_ADDRESS = V$SQL.ADDRESS
     AND V$SESSION.STATUS = 'ACTIVE'
     and v$session.username = upper('&ale')
/
-- MOSTRA O SQL POR SID @TEXTOUSERID
SELECT OSUSER,
     sid,SERIAL#,
     SQL_TEXT
     FROM V$SESSION, V$SQL
     WHERE V$SESSION.SQL_ADDRESS = V$SQL.ADDRESS
     AND V$SESSION.STATUS = 'ACTIVE'
     and v$session.sid = &ale
/
-- MOSTRA SQL E ESTATISTICAS DA SESS�O, ATRV� DO SID
select a.username,a.status,b.sql_text,b.sorts,b.executions,b.disk_reads,
b.buffer_gets,b.rows_processed,A.SQL_ADDRESS from v$session a, v$sqlarea b
where
a.sid= &n_sid and
a.sql_address = b.address
/
-- ESTATISCAS DO SORT, -- DE VE ESTAR MAIS DE 99%
SELECT DISK.VALUE "disk", mem.value "mem",(1 - (disk.value/mem.value))*100 "ratio"
from v$sysstat mem, v$sysstat disk where mem.name = 'sorts (memory)' and
disk.name = 'sorts (disk)'
/

-- ESTATISTICAS DA LIBRARY CACHE - TEM QUE ESTAR ACIMA DE 90%
SELECT NAMESPACE,PINS "EXECUTIONS",RELOADS "CHACHE MISSES",
(1 - RELOADS/PINS)*100 "HIT_RATIO" FROM V$LIBRARYCACHE WHERE PINS <> 0
/
SELECT NAMESPACE,GETHITRATIO FROM V$LIBRARYCACHE
/
-- LATCHES ATIVOS NO MOMENTO
select * from v$latchholder ;
-- VERIFICA LATCHES EM WAIT
select event,p1 "file",p2 "block",p3 "blocks",state,SID from v$session_wait
where event not like 'SQL%' ORDER BY EVENT DESC
/
-- RELA��O DE TABLESPACES E DATAFILES
select TABLESPACE_NAME,bytes,FILE_NAME from dba_data_files
order by 1;

-- COLOCAR SESS�O EM TRACE PE USERNAME (PRECISAR ESTAR COMO SYS) @TRACE
select 'EXECUTE dbms_system.set_sql_trace_in_session('||sid||','|| serial# ||',TRUE);'
from   v$session
where username = upper('&ale');
-- USUARIOS ATIVOS
col username for a12
col osuser for a20
col status for a6
SELECT USERNAME,OSUSER,SID,serial#,sTATUS,LOCKWAIT,
decode(command,
0,'None',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY') COMMAND,ROW_WAIT_FILE# DATAFILE,server
from    V$SESSION
WHERE STATUS = 'ACTIVE' AND USERNAME <> ' ' ORDER BY USERNAME
/
-- MOSTRA WAIT POR SID
select event,p1,p2,P3,seconds_in_wait sec,state from v$session_wait
where sid = &n_sid
/
-- MOSTRA TODOS WAITS INCLUSIVE SID E SEGUNDOS
COL EVENT FOR A33
select event,p1,p2,seconds_in_wait sec,state,SID from v$session_wait
ORDER BY EVENT DESC
/