Gerenciando tablespaces no Oracle 11g

Um banco de dados é armazenado logicamente em uma ou mais tablespaces que, por sua vez, é armazenada fisicamente no disco em um ou mais arquivos para cada tablespace. Devemos ser capazes de alocar corretamente os arquivos, ter controle do crescimento e saber quando agir. Você sabe onde estão os seus tablespaces ? Sabe o tamanho deles ? Eles estão no mesmo disco ? Enfim, pretendo fazer dois artigos com informações de gerenciamento de tablespace no banco de dados Oracle.

É de grande importância que as tablespaces estejam em discos diferentes, que tenha algum método de RAID e/ou melhor, que seja gerenciado pelo ASM (Automatic Storage Management), assim podemos garantir garantir um alto nível de desempenho, disponibilidade e facilidade de recuperação. Entretanto, devemos distribuir em vários discos seus arquivos de dados, mantendo cópias espelhadas dos archive logs e control files.

Smallfile X Bigfile

A partir da versão do 10g, é possível criar um tipo de tablespace, chamada bigfile. Essa novidade também se aplica no Oracle 11g. Com esse novo recurso, podemos criar um arquivo de dados de terabytes, utilizando a opção bigfile. A tablespace bigfile, contém somente um arquivo de datafile ou um tempfile, que contém aproximadamente 4 bilhões de blocks. O tamanho máximo de um único datafile ou temfile é de 128 terabytes, para uma tablespace de 32k de blocos e 32TB para uma tablespace com 8k blocos.

Já a tablespace smallfile é padrão, que pode contém 1022 datafiles ou tempfiles, cada uma podendo ter aproximadamente 4 milhões de blocos.

Como no próprio site da Oracle, existe algumas restrições na criação do bigfile, as mais importantes são: Você só pode especificar apenas um datafile na cláusula DATAFILE e/ou na TEMPFILE, Você também não pode especificar EXTENT MANAGEMENT DICTIONARY.

Vamos começar verificando quantos tablespace temos nesta instância:

SQL> select * from v$tablespace;

TS#   NAME            INC BIG FLA ENC

0    SYSTEM            YES NO YES
1     SYSAUX            YES NO YES
2    UNDOTBS1       YES NO YES
4    USERS               YES NO YES
3     TEMP                NO NO YES
6     EXAMPLE        YES NO YES

6 rows selected.

Com este select, podemos dizer que temos 7 tablespace, que seis são tablespace permanentes e uma do tipo temporária (TEMP).

Ao criar um tablespace, me deparei com a mensagem de erro:

SQL> create tablespace william;
create tablespace william

*

ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

O erro ocorre porque não definimos nada no parmetro: db_create_file_dest. Para verificar se tem algo setado, use este select:

SQL> show parameter file_dest
NAME                                                      TYPE        VALUE

—————————————— ———– ——————————

audit_file_dest                                       string            /u01/app/oracle/admin/orcl/adump
db_create_file_dest                              string
db_recovery_file_dest                         string             $ORACLE_BASE/flash_recovery_area
db_recovery_file_dest_size                big integer    2G
SQL>

Para setar este parâmetro, rodamos um alter system set, segue exemplo abaixo.

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST=’/u01/app/oracle/oradata/orcl’ SCOPE=BOTH;
System altered.

Com isso, definidos que o caminho de qualquer tablespace default, será criada nesta pasta acima e o parâmetro SCOPE, aplica a configuração no ambiente atual, sem a necessidade de parar o banco e iniciar novamente, também já configura este parâmetro no arquivo do SPFILE. Tentamos criar novamente a tablespace:

SQL> create tablespace william;
Tablespace created.

Verificando onde a tablespace foi criada:

SQL> select tablespace_name, name, status, bytes/1024/1024 “Megas”
FROM V$DATAFILE_HEADER;

Space usage report by Tablespace

TABLESPACE_NAME NAME                                                                         STATUS      Megas
SYSTEM        /u01/app/oracle/oradata/orcl/system01.dbf                       ONLINE      730.0
SYSAUX        /u01/app/oracle/oradata/orcl/sysaux01.dbf                         ONLINE      640.0
UNDOTBS1     /u01/app/oracle/oradata/orcl/undotbs01.dbf                  ONLINE      100.0
USERS        /u01/app/oracle/oradata/orcl/users01.dbf                             ONLINE        6.3
EXAMPLE        /u01/app/oracle/oradata/orcl/example01.dbf                 ONLINE      100.0
WILL        ../../../orcl/ORCL/datafile/o1_mf_william_73ccjo34_.dbf    ONLINE      100.0

Renomeando uma tablspace, Lembrando que você não pode renomear tablespace do system, sysaux e tablespace offline:

SQL> alter tablespace william rename to will;
Tablespace altered.

Redimensionando tablespaces, com alter database, dimunuindo de 100m para 50m:

SQL> alter database
datafile ‘/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_william_73ccjo34_.dbf’
resize 50m;
Database altered.

Reduzindo para 1k:

SQL> alter database
datafile ‘/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_william_73ccjo34_.dbf’
resize 1k
ERROR at line 1:ORA-03214: File Size specified is smaller than minimum required

Com esse erro, percebemos que não podemos reduzir muito, porque já contém dados no arquivo, que estão usando este espaço. Abaixo, mostro também que se aumentarmos mais do que temos no Sistema operacional, pode dar erro também:

SQL> alter database
datafile ‘/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_william_73ccjo34_.dbf’
resize 1t;
alter database datafile ‘/u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_william_73ccjo34_.dbf’ resize 1t
ERROR at line 1:
ORA-01144: File size (134217728 blocks) exceeds maximum of 4194303 blocks

Criando uma tablespace, com um tamanho de 100megas, que irá crescer em 50 a 50 megas, até completar 20gigas.

SQL> CREATE tablespace wiliam datafile ‘/u01/app/oracle/oradata/orcl/william.dbf’
size 100m
autoextend on
next 50m
maxsize 20g;

Tablespace created.