sexta-feira, 11 de junho de 2010

UTILIZAÇÃO DE ESPAÇO POR DATAFILE

Olá galera, como estamos?

Venho hoje para trazer até vocês um post bem rápido, ele contém um SQL que me ajudou muito quando trabalhei em ambientes onde não haviam interfaces gráficas e eu precisava visualizar de maneira coerente determinada informação. Trata-se de um script para checar a utilização de espaço por DATAFILE.

Vamos lá?!?

CONCEITOS

Antes de iniciarmos é necessário que você tenha em mente alguns conceitos como por exemplo o que é um datafile, o que é uma tablespace, etc.

DATAFILE: são o(s) conjunto(s) de arquivo(s) que estão vinculados de maneira lógica a uma tablespace , que por sua vez possui uma representação "FISICA", ou seja , alocada em disco e ou ASM se for o caso.

Bem, agora que vimos o que é um DATAFILE , vamos ao nosso script? 
Basicamente usaremos  2 (duas) visões de Banco de dados para retornar as informações que precisamos a cerca da utilização do espaço por datafile. São elas : DBA_FREE_SPACE e DBA_DATA_FILES.

DBA_FREE_SPACE

DBA_FREE_SPACE describes the free extents in all tablespaces in the database.
Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.
Related View
USER_FREE_SPACE describes the free extents in the tablespaces accessible to the current user.
Column Datatype NULL Description
TABLESPACE_NAME VARCHAR2(30)   Name of the tablespace containing the extent
FILE_ID NUMBER   File identifier number of the file containing the extent
BLOCK_ID NUMBER   Starting block number of the extent
BYTES NUMBER   Size of the extent (in bytes)
BLOCKS NUMBER   Size of the extent (in Oracle blocks)
RELATIVE_FNO NUMBER   Relative file number of the file containing the extent
 
 
 

DBA_DATA_FILES

DBA_DATA_FILES describes database files.
Column Datatype NULL Description
FILE_NAME VARCHAR2(513)   Name of the database file
FILE_ID NUMBER NOT NULL File identifier number of the database file
TABLESPACE_NAME VARCHAR2(30) NOT NULL Name of the tablespace to which the file belongs
BYTES NUMBER   Size of the file in bytes
BLOCKS NUMBER NOT NULL Size of the file in Oracle blocks
STATUS VARCHAR2(9)   File status: AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace that was dropped)
RELATIVE_FNO NUMBER   Relative file number
AUTOEXTENSIBLE VARCHAR2(3)   Autoextensible indicator
MAXBYTES NUMBER   Maximum file size in bytes
MAXBLOCKS NUMBER   Maximum file size in blocks
INCREMENT_BY NUMBER   Number of tablespace blocks used as autoextension increment. Block size is contained in the BLOCK_SIZE column of the DBA_TABLESPACES view.
USER_BYTES NUMBER   The size of the file available for user data. The actual size of the file minus the USER_BYTES value is used to store file related metadata.
USER_BLOCKS NUMBER   Number of blocks which can be used by the data
ONLINE_STATUS VARCHAR2(7)   Online status of the file:
  • SYSOFF
  • SYSTEM
  • OFFLINE
  • ONLINE
  • RECOVER
 
Acima podemos ver nossas ferramentas de trabalho e suas respectivas colunas, e com essas informações será mais fácil de entendermos nosso resultado do script.
 
Nota: é importante ressaltar que em caso de arquivos muito grandes de datafile , os processos que resgataram dados contidos nesses datafiles podem vir a demandar um pouco mais de tempo, ora por problemas de performance ora por problemas de contenção. Mas via de regra em arquivos muito grandes dependendo da sua estrutura e arquitetura utilizada podem ocorrer algumas surpresas quanto a tempo de resposta.
 
O SCRIPT 
 
Este script eu utilizo desde 2000, foi quando eu ainda era DBA Junior e resolvi comprar o meu primeiro livro de Oracle, um livro de nivel Intermediário/Avançado de um indiano chamado Rajendra Gutta, eis a capa dele para aqueles que quiserem adquirir, muito bom livro :
 
 
No cápitulo de GERENCIAMENTO DE ESPAÇO ele traz essa maravilha abaixo :
 
set lin 120 pages 60 feed off heading on
ttitle skip center "Relatorio de utilizacao de Espaco por Datafile" skip 2
col tablespace for a15
col file format a50
set numformat 99999999.9

accept tbsn prompt "Digite o nome da Tablespace [enter=todas]...:"

clear breaks
clear computes
break on Tablespace skip 1 on report skip 2

compute sum of Total(M) on Tablespace
compute sum of Used(M)  on Tablespace
compute sum of Free(M)  on Tablespace
compute avg of %Free    on Tablespace
compute sum of Total(M) on report
compute sum of Used(M)  on report
compute sum of Free(M)  on report


select b.file_name "File",
       b.tablespace_name "Tablespace",
       b.bytes/(1024*1024) "Total(M)",
       round((b.bytes-sum(nvl(a.bytes,0)))/(1024*1024),1) "Used(M)",
       round(sum(nvl(a.bytes,0))/(1024*1024),1) "Free(M)",
       round((sum(nvl(a.bytes,0))/(b.bytes))*100,1) "%_Free"
from   dba_free_space a,
       dba_data_files b
where  a.file_id(+) = b.file_id
and    b.tablespace_name like (upper(nvl('&tbsn',b.TABLESPACE_NAME)))
group by b.tablespace_name,b.file_name,b.bytes
order by b.file_name asc;

ttitle off

      
      
O script é aparentemente simples, porem o que dá a excelencia a ele são as formatações e o modelo de saida do mesmo, tendo como parametro o nome da tablespace que você deseja analisar, parametro esse solicitado no momento em que ele é executado, e vejam abaixo a saida do mesmo:
 
 
Vejam que você tem 3 colunas distintas, cada uma com a informação precisa sobre o total, livre, utilizado e ainda uma porcentagem de uso.
 
Bem, fica aqui a dica de leitura, um script pra você se quiser e necessitar colecionar, e mais um post sobre SQLAB, nunca se sabe quando não teremos ferramentas graficas pra gerar relatórios.
 
Um abraço á todos e sucesso sempre!!!