Gerência Teblespaces e DataFiles no SQL*Plus

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (6)  (0)

Mostraremos o poder que o SQL*Plus tem para o controle do tamanho das tablespaces e datafiles.

 

Gostaríamos de apresentar a versatilidade que o SQL*Plus tem de gerenciar as tablespaces e datafiles através de scripts. Da mesma forma com o Oracle é relacional para manipular as informações e dados contidos nele, ele também é relacional para dar resposta sobre todos os aspectos referentes à administração de banco de dados. Esse conhecimento é essencial quando não dispomos de ferramenta gráfica como o Entreprise Manager ou PL/SQL Developer. Devemos ter em mente que quando solicitamos uma informação a uma ferramenta gráfica sobre o banco de dados esta seguramente fará uma consulta ao dicionário de dados.

Portanto um bom DBA Oracle, quando quiser conhecer o comportamento e os elementos de um banco de dados Oracle, deverá saber a melhor forma de “coletar” informações. Para isso é necessário um conhecimento profundo do dicionário de dados http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datadict.htm#i2112 que é um conjunto de tabelas (read-only) e views que provêem informações sobre o banco de dados.

Na prática, o bom DBA deverá saber “disparar” instruções SELECT´s contra o dicionário de dados para conhecer o comportamento e os elementos de um banco de dados. O SQL*Plus nos dá a possibilidade, através de scripts, de combinarmos comandos de ambiente de SQL*Plus e instruções SELECT´s para montarmos consultas poderosas, úteis para a administração de banco de dados. Neste artigo, apresentaremos dois scripts que apresentarão informações sobre as tablepaces e datafiles através do dicionário de dados.

Grosseiramente podemos diferenciar datafiles e tablespaces como organizações físicas e organizações lógicas respectivamente do banco de dados Oracle. Uma tablespace(lógica) tem ao menos um datafiles(físico) associado a ela. Não existe tablespace sem datafiles.

tablespaces.sql

 

A seguinte sequência de comandos formará o primeiro script. A sugestão de nome é tablespaces.sql. Use o editor de texto de preferência, crie um arquivo tablespaces.sql e em seguida cole a sequência de comandos apresentados. O scripts será acionado da seguinte maneira SQL>[caminho]@tablespaces  a partir do prompt do SQL*Plus.

 

COL tablespace_name          FORMAT A20         HEADING "Tablespace"

COL pct_used                 FORMAT 990.00      HEADING "%Used"

COL TS#                      FORMAT 99          HEADING "#"

COL SIZE_MB                  FORMAT 999,990.00  HEADING "Size(Mb)"  

COL USED_MB                  FORMAT 999,990.00  HEADING "Used(Mb)"  

COL FREE_MB                  FORMAT 999,990.00  HEADING "Free (Mb)"  

col ideal_size               FORMAT 999,990.00  HEADING "Ideal|Size"  

 

SET PAGESIZE 100 LINES 130 HEaDING on FEED OFF NULL ''

break on report ON contents SKIP 1

 

SELECT

    a.contents

   ,TS#

   ,a.tablespace_name

   ,b.bytes/(1024*1024) size_mb

   ,(b.bytes - nvl(c.free_bytes,0))/(1024*1024) USED_MB

   ,(nvl(c.free_bytes,0))/(1024*1024) FREE_MB

   ,(((b.bytes-nvl(c.free_bytes,0))/(1024*1024))*100)/(b.bytes/(1024*1024)) pct_used

FROM

    DBA_TABLESPACES A,

    (SELECT tablespace_name, sum(bytes) bytes

       FROM dba_data_files

      group by tablespace_name

     UNION

     SELECT tablespace_name, sum(bytes) bytes

       FROM dba_TEMP_files

      group by tablespace_name) B,

    (SELECT TABLESPACE_NAME,SUM(BYTES) free_bytes

       FROM dba_free_space

      GROUP BY TABLESPACE_NAME

     UNION

     SELECT tablespace_name, sum(bytes_free) free_bytes

       FROM v$temp_space_header

      GROUP BY tablespace_namE) C,

    V$TABLESPACE D

WHERE

    a.tablespace_name = b.tablespace_name

and a.tablespace_name = c.tablespace_name(+)

AND a.tablespace_name = D.NAME

ORDER BY

    a.contents , TS#

/

 

PROMPT

PROMPT. @tablespaces     @datafiles

PROMPT

 

 

O resultado da execução do script será algo parecido com:

 

SQL>@tablespaces

 

CONTENTS    # Tablespace              Size(Mb)    Used(Mb)   Free (Mb)   %Used

--------- --- -------------------- ----------- ----------- ----------- -------

PERMANENT   0 SYSTEM                  1,024.00      548.63      475.38   53.58

            2 SYSAUX                  1,024.00      428.44      595.56   41.84

            4 USERS                   1,000.00      406.31      593.69   40.63

            5 DAT01                  15,000.00   10,179.50    4,820.50   67.86

            6 DAT02                  10,000.00    1,854.06    8,145.94   18.54

            7 DAT03                  30,240.00   18,106.94   12,133.06   59.88

            8 DAT04                   6,000.00      681.81    5,318.19   11.36

            9 DAT05                  10,000.00    6,914.00    3,086.00   69.14

 

TEMPORARY   3 TEMP                    5,120.00    1,703.00    3,417.00   33.26

 

UNDO        1 UNDOTBS1               30,000.00       65.88   29,934.13    0.22

 

*********                          ----------- ----------- -----------

Total Use                           109,408.00   40,888.56   68,519.44

 

. @tablespaces.sql     @datafiles

 

 

As colunas apresentadas informam o seguinte :

 

Colunas

Descrição

Contents

Tipo de tablespace

#

Identificação numérica da tablespace

Tablespace

Nome tablespace

Size(Mb)

Tamanho tablespace

Used(Mb)

Espaço utilizado da tablespace

Free(MB)

Espaço disponível

%Used

Porcentagem de utilização

 

As totalizações são úteis para que tenhamos uma noção de tamanho, espaço utilizado e espaço disponível de todo o banco de dados.

 

datafiles.sql

 

A seguinte sequência de comandos formará o segundo script. A sugestão de nome é datafiles.sql. Use o editor de texto de preferência, crie um arquivo datafiles.sql e em seguida cole a sequência de comandos apresentados. O scripts será acionado da seguinte maneira SQL>[caminho]@datafiles  a partir do prompt do SQL*Plus.

COL TS#             FORMAT 99     

col FILE_ID         format 99      HEADING "DF#"

col file_name       format a60                            truncated      

COL BYTES           format 99,990   heading "Size|(Mb)"

col autoextensible  format a4      heading "Auto|Ext?"

col INCREMENT_BY    FORMAT 999,990 heading "Next|(Kb)"

COL MAXBYTES        FORMAT 99,990  heading "MaxSize|(Mb)"

 

set line 1000 PAGES 40 feed off

 

break ON TYPE_FILE on TS#

 

select

    TS#,

    FILE_ID ,

    f.file_name,

    f.BYTES,

    f.autoextensible,

    (f.INCREMENT_BY * (SELECT VALUE FROM V$PARAMETER WHERE NAME='db_block_size')) /1024 INCREMENT_BY,

    f.MAXBYTES

from

    v$tablespace v$ts,

    (

     select

         dt.TABLESPACE_NAME        ,

         dt.file_name,

         round(dt.BYTES/(1024*1024),2) BYTES,

         dt.autoextensible,

         round(dt.MAXBYTES/(1024*1024),2) MAXBYTES

         ,dt.FILE_ID

         ,'PERM' TYPE_FILE

         ,dt.INCREMENT_BY

         ,dt$.STATUS,

         dt$.ENABLED

     from

         dba_data_files dt,

         v$datafile dt$

     where dt.file_name = dt$.NAME

     union

     select

         tf.TABLESPACE_NAME,

         tf.FILE_NAME,

         round(tf.BYTES/(1024*1024),2) BYTES,

         tf.AUTOEXTENSIBLE,

         round(tf.MAXBYTES/(1024*1024),2) MAXBYTES

         ,tf.FILE_ID

         ,'TEMP' TYPE_FILE

         ,tf.INCREMENT_BY

         ,tf$.STATUS

         ,tf$.ENABLED

     from

         dba_TEMP_files tf

         ,v$tempfile tf$

     where tf.file_name = tf$.NAME

    )f

where

    f.tablespace_name(+) = v$ts.NAME

order by

    DECODE(f.TYPE_FILE,

               'PERM',1,

               'TEMP',2),

    v$ts.TS#,

    f.FILE_ID 

 

/

 

prompt

prompt.    @datafiles   @tablespaces

prompt

 

 

 

O resultado da execução do script será algo parecido com:

 

SQL> @datafiles

 

                                                       Size Auto     Next MaxSize

  # DF# FILE_NAME                                      (Mb) Ext?     (Kb)    (Mb)

--- --- ------------------------------------------- ------- ---- -------- -------

  0   1 /oracle/oradata/salmao/system01.dbf           1,024 YES    10,240  32,768

  1   2 /oracle/oradata/salmao/undotbs01.dbf         10,000 YES     5,120  32,768

     10 /oracle/datafiles/salmao/undotbs01_01.dbf    20,000 NO          0       0

  2   3 /oracle/oradata/salmao/sysaux01.dbf           1,024 YES    10,240  32,768

  4   4 /oracle/oradata/salmao/users01.dbf            1,000 YES     1,280  32,768

  5   5 /oracle/oradata/salmao/tbs_dat01.dbf         15,000 NO          0       0

  6   6 /oracle/oradata/salmao/tbs_dat02.dbf         10,000 NO          0       0

  7   7 /oracle/datafiles/salmao/tbs_dat03.dbf       20,000 NO          0       0

     11 /oracle/oradata/salmao/tbs_dat03_01.dbf      10,240 NO          0       0

  8   8 /oracle/oradata/salmao/tbs_dat04.dbf          6,000 NO          0       0

  9   9 /oracle/oradata/salmao/tbs_dat05.dbf         10,000 YES         8   1,024

  3   1 /oracle/oradata/salmao/temp01.dbf             5,120 YES       640  32,768

 

.    @datafiles   @tablespaces

 

As colunas apresentadas informam o seguinte :

 

Colunas

Descrição

#

Identificação numérica da tablespace formada pelo(s) datafile(s)

DF#

Identificação numérica do datafile

Size(Mb)

Tamanho do datafile

Auto Ext?

Indica se o datafile é auto-incrementável ou não

Next(MB)

Qual tamanho do incremento o datafile receberá quando necessário

MaxSize(MB)

Tamanho máximo permitido para o datafile

 

Observações:

 

·         Os datafiles sem auto-incremento tem os valores next e maxsize zerados

·         Os datafiles com a propriedade maxsize ilimitados apresentam o valor 32,768

·         No exemplo percebemos que os datafiles de identificação 2 e 10 formam o tablespace de identificação 1(undotbs1)

·         No exemplo percebemos que os datafiles de identificação 7 e 11 formam o tablespace de identificação 7(dat03)

 

Espero que essas informações lhes sejam úteis quando precisarem obter informações de tablespace e datafile na ausência de ferramentas gráficas. Lembramos que todas as informações sobre o comportamento e os aspectos físicos do banco de dados Oracle podem ser obtidos através de consultas no dicionário de dados, basta pesquisar e aprender a usá-la!

 

Até o próximo artigo!

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?