Página Inicial > Tablespace > Gerenciando tablespaces no Oracle 11g – Parte I

Gerenciando tablespaces no Oracle 11g – Parte I

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.


Vou parar por aqui, e em breve a segunda parte!

Um abraço.

Tags:
  1. 15, agosto, 2011 em 22:57 | #1

    William,
    parabéns … esse ponto é bem legal. Acho que vale a dica de um post sobre ASM, quais os benefícios de utilizar, porque está tão melhor na versão 11g, quais as vantagens e desvantagens de utilizar … acho que tens muito a explorar nessa linha.
    abraços
    capin

  2. Neubert Borges
    17, agosto, 2011 em 18:52 | #3

    Muito bom William, parabéns mais uma vez.

    Agora uma dúvida de iniciante mesmo: Existe algum select especifico que eu possa usar para detectar um possivel estouro na Tablespace?

    • 20, agosto, 2011 em 10:50 | #4

      Neubert, segue dois exemplos para verificar um possúvel estouro na tablespace, veja se te ajuda:

      Listando as tablespaces do banco de dados:
      select tablespace_name, file_name, bytes/1024/1024 from dba_data_files;

      Verificando o espaço livre na tablespace USERS:
      select tablespace_name, bytes/1024/1024 “RESTANTE EM MG” from dba_free_space where tablespace_name = ‘USERS’;

      Um abraço!

  3. Ulisses
    15, maio, 2014 em 21:13 | #5

    Cara, muito top este seu post, apesar de fazer muito tempo que foi postado, pude aproveitar o máximo e para mim foi um dos melhores que já vi em termos gerais. Parabéns!!!

  4. 7, março, 2016 em 01:11 | #6

    Bom dia

    Gostei muito do seu artigo no seu conteúdo.

    Cumprimentos!

  5. 15, março, 2016 em 17:57 | #7

    Oi,

    que texto demais!
    Eu sei o quanto é complicado é ter um blog!

    Tenho escrito sobre Descomplica no meu site http://maiseducativo.com.br/descomplica-e-bom/

    Beijos!

  6. claudia
    16, maio, 2016 em 10:33 | #8

    Oi,
    como aumentar uma tablespace quando está já está com tamanho de 32500mb. Quando tento colocar tamanho maior ocorre erro
    Falha de commit: ORA-01144: O tamanho do arquivo (4224000 blocos) excede o máximo de 4194303 blocos.
    Meu banco é oracle 11

  1. Nenhum trackback ainda.