Categoria: Sem categoria

Analisando o tamanho e espaço da tablespace Oracle

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

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;

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

alexbarbosa 10 de março de 2016 Nenhum comentário

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.

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

alexbarbosa 10 de março de 2016 Nenhum comentário

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;

Alex Felipe Barbosa

Blog relacionado a Banco de Dados – Oracle PL/SQL – SQL Server Transact-SQL

Categoria: Sem categoria

Analisando o tamanho e espaço da tablespace Oracle

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

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