Arquivos Sem categoria - Alex Felipe Barbosa

Analisando o tamanho e espaço da tablespace Oracle

10 de março de 201617 de março de 2016 alexbarbosa 5 Comentários tablespace

Analisando o tamanho e espaço da tablespace no Oracle.

SELECT t.tablespace
      ,t.totalspace AS " Totalspace(MB)"
       ,round((t.totalspace - fs.freespace), 2) AS "Used Space(MB)"
       ,fs.freespace AS "Freespace(MB)"
       ,round(((t.totalspace - fs.freespace) / t.totalspace) * 100, 2) AS "% Used"
       ,round((fs.freespace / t.totalspace) * 100, 2) AS "% Free"
  FROM (SELECT round(SUM(d.bytes) / (1024 * 1024)) AS totalspace
              ,d.tablespace_name tablespace
          FROM dba_data_files d
         GROUP BY d.tablespace_name) t
      ,(SELECT round(SUM(f.bytes) / (1024 * 1024)) AS freespace
              ,f.tablespace_name tablespace
          FROM dba_free_space f
         GROUP BY f.tablespace_name) fs
WHERE t.tablespace = fs.tablespace
ORDER BY t.tablespace;

SELECT t.tablespace

,t.totalspace AS " Totalspace(MB)"

,round((t.totalspace - fs.freespace), 2) AS "Used Space(MB)"

,fs.freespace AS "Freespace(MB)"

,round(((t.totalspace - fs.freespace) / t.totalspace) * 100, 2) AS "% Used"

,round((fs.freespace / t.totalspace) * 100, 2) AS "% Free"

FROM (SELECT round(SUM(d.bytes) / (1024 * 1024)) AS totalspace

,d.tablespace_name tablespace

FROM dba_data_files d

GROUP BY d.tablespace_name) t

,(SELECT round(SUM(f.bytes) / (1024 * 1024)) AS freespace

,f.tablespace_name tablespace

FROM dba_free_space f

GROUP BY f.tablespace_name) fs

WHERE t.tablespace = fs.tablespace

ORDER BY t.tablespace;

Ler mais

Sem categoria

Estatísticas da sessão e consumo do Banco de Dados Oracle

10 de março de 2016 alexbarbosa 0 comentários

Script para verificar as estatisticas e consumo das sessões do

SELECT logon_time
      ,username
      ,upper(osuser) osuser
      ,( SELECT round(VALUE / 1024 / 1024, 2 )
          FROM v$sesstat
         WHERE statistic# = 25
           AND v$sesstat.sid = v$session.sid) AS "PGA Memory, in MB"
       ,( SELECT VALUE
          FROM v$sesstat
         WHERE statistic# = 12
           AND v$sesstat.sid = v$session.sid) AS "CPU, used by session"
       ,round(( SELECT VALUE
                FROM v$sesstat
               WHERE statistic# = 339
                 AND v$sesstat.sid = v$session.sid) /
             ( SELECT decode(VALUE , 0, 1, VALUE )
                FROM v$sesstat
               WHERE statistic# = 338
                 AND v$sesstat.sid = v$session.sid),
             2) AS "Hard Parse, %"
       ,( SELECT round(VALUE / 1024 / 1024, 2 )
          FROM v$sesstat
         WHERE statistic# = 58
           AND v$sesstat.sid = v$session.sid) AS "Physical read bytes, in MB"
       ,( SELECT round(VALUE / 1024 / 1024, 2 )
          FROM v$sesstat
         WHERE statistic# = 66
           AND v$sesstat.sid = v$session.sid) AS "Physical write bytes, in MB"
       ,( SELECT round(VALUE / 1024 / 1024, 2 )
          FROM v$sesstat
         WHERE statistic# = 139
           AND v$sesstat.sid = v$session.sid) AS "Redo size, in MB"
       ,( SELECT round(VALUE / 1024 / 1024, 2 )
          FROM v$sesstat
         WHERE statistic# = 344
           AND v$sesstat.sid = v$session.sid) AS "Received from client, in MB"
       ,( SELECT round(VALUE / 1024 / 1024, 2 )
          FROM v$sesstat
         WHERE statistic# = 343
           AND v$sesstat.sid = v$session.sid) AS "Sent to client, in MB"
       ,sid
       ,serial#
       ,v$session.status
       ,program
       ,user#
       ,command
       ,ownerid
       ,process
       ,machine
       ,object_name
  FROM v$session
  LEFT OUTER JOIN dba_objects ON v$session.row_wait_obj# = dba_objects.object_id
 WHERE v$session.logon_time BETWEEN trunc(SYSDATE - 30) AND SYSDATE
AND v$session.STATUS = 'ACTIVE'
 ORDER BY 7 DESC;

SELECT logon_time

,username

,upper(osuser) osuser

,( SELECT round(VALUE / 1024 / 1024, 2 )

FROM v$sesstat

WHERE statistic# = 25

AND v$sesstat.sid = v$session.sid) AS "PGA Memory, in MB"

,( SELECT VALUE

FROM v$sesstat

WHERE statistic# = 12

AND v$sesstat.sid = v$session.sid) AS "CPU, used by session"

,round(( SELECT VALUE

FROM v$sesstat

WHERE statistic# = 339

AND v$sesstat.sid = v$session.sid) /

( SELECT decode(VALUE , 0, 1, VALUE )

FROM v$sesstat

WHERE statistic# = 338

AND v$sesstat.sid = v$session.sid),

2) AS "Hard Parse, %"

,( SELECT round(VALUE / 1024 / 1024, 2 )

FROM v$sesstat

WHERE statistic# = 58

AND v$sesstat.sid = v$session.sid) AS "Physical read bytes, in MB"

,( SELECT round(VALUE / 1024 / 1024, 2 )

FROM v$sesstat

WHERE statistic# = 66

AND v$sesstat.sid = v$session.sid) AS "Physical write bytes, in MB"

,( SELECT round(VALUE / 1024 / 1024, 2 )

FROM v$sesstat

WHERE statistic# = 139

AND v$sesstat.sid = v$session.sid) AS "Redo size, in MB"

,( SELECT round(VALUE / 1024 / 1024, 2 )

FROM v$sesstat

WHERE statistic# = 344

AND v$sesstat.sid = v$session.sid) AS "Received from client, in MB"

,( SELECT round(VALUE / 1024 / 1024, 2 )

FROM v$sesstat

WHERE statistic# = 343

AND v$sesstat.sid = v$session.sid) AS "Sent to client, in MB"

,sid

,serial#

,v$session.status

,program

,user#

,command

,ownerid

,process

,machine

,object_name

FROM v$session

LEFT OUTER JOIN dba_objects ON v$session.row_wait_obj# = dba_objects.object_id

WHERE v$session.logon_time BETWEEN trunc(SYSDATE - 30) AND SYSDATE

AND v$session.STATUS = 'ACTIVE'

ORDER BY 7 DESC;

banco de dados Oracle.

Ler mais

Sem categoria SQL

Verificar usuário “lockado” no banco de dados Oracle

10 de março de 201617 de março de 2016 alexbarbosa 0 comentários lock, sql

Script para identificar se algum usuário do banco de dados esta “lockando” algum objeto.

SELECT c.username AS usuario_bd
      ,c.osuser AS usuario_so
      ,decode(b.id2, 0, a.object_name, 'Trans-' || to_char(b.id1)) AS objeto_bd
      ,a.object_type AS tipo_objeto
      ,c.machine AS maquina
      ,c.logon_time AS inicio_conexao
      ,c.program AS programa
  FROM dba_objects a
      ,v$lock      b
      ,v$session   c
 WHERE (a.object_id(+) = b.id1 AND b.sid = c.sid AND c.username IS NOT NULL)
 ORDER BY b.sid
         ,b.id2;

SELECT c.username AS usuario_bd

,c.osuser AS usuario_so

,decode(b.id2, 0, a.object_name, 'Trans-' || to_char(b.id1)) AS objeto_bd

,a.object_type AS tipo_objeto

,c.machine AS maquina

,c.logon_time AS inicio_conexao

,c.program AS programa

FROM dba_objects a

,v$lock b

,v$session c

WHERE (a.object_id(+) = b.id1 AND b.sid = c.sid AND c.username IS NOT NULL)

ORDER BY b.sid

,b.id2;

Ler mais