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!
