Resize tablespace

Tenho um banco de dados com o tamanho de 15G mas meu banco esta alocando em meu disco 30G Por que?

Simples, ao criar seus datafiles você não esta tomando cuidados com seu autoextent, ou você esta criando seus datafiles direto com o maxbytes.

E agora? não tem mais como resolver? Tem sim.

Primeiro vamos setar os autoextents para um valor menor, assim poderemos trabalhar com autoextents, sem ocupar espaço em disco indesejado.

Para isso temos de descobrir o valor do bloko que esta configurado no banco.

Geralmente é 8K mas não custa verificarmos.

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
db_block_size                        integer     8192

Tendo o tamanho dos blocos devemos fazer o select que deverá nos mostrar o quanto esta o maxbites de cada datafile e qual é o incremento deles.

SQL> set lines 155
SQL> col FILE_NAME for a50
SQL> select FILE_NAME,INCREMENT_BY*8192/1024/1024,MAXBYTES/1024/1024 from dba_data_files;
FILE_NAME                                          INCREMENT_BY*8192/1024/1024 MAXBYTES/1024/1024
-------------------------------------------------- --------------------------- ------------------
/oraprd02/oradata/dbprod/users01.dbf                                         1               3000
/oraprd02/oradata/dbprod/sysaux01.dbf                                     1000               3000
/oraprd02/oradata/dbprod/undotbs01.dbf                                     100               3000
/oraprd02/oradata/dbprod/system01.dbf                                       10               3000

Observe que inseri no select o numero de blocos do banco no increment_by.
Verifique que existe datafiles com o autoextent de 1000m, de 1m de 100m e de 10m.

Vamos alterar os datafiles para auto expandir a um máximo de 100M cada.


SQL> alter database datafile '/oraprd02/oradata/dbprod/users01.dbf' autoextend on next 100m maxsize 3000m;

Database altered.

SQL> alter database datafile '/oraprd02/oradata/dbprod/sysaux01.dbf' autoextend on next 100m maxsize 3000m;

Database altered.

SQL> alter database datafile '/oraprd02/oradata/dbprod/undotbs01.dbf' autoextend on next 100m maxsize 3000m;

Database altered.

SQL> alter database datafile '/oraprd02/oradata/dbprod/system01.dbf' autoextend on next 100m maxsize 3000m;

Database altered.

SQL> select FILE_NAME,INCREMENT_BY*8192/1024/1024,MAXBYTES/1024/1024 from dba_data_files;

FILE_NAME                                          INCREMENT_BY*8192/1024/1024 MAXBYTES/1024/1024
-------------------------------------------------- --------------------------- ------------------
/oraprd02/oradata/dbprod/users01.dbf                                       100               3000
/oraprd02/oradata/dbprod/sysaux01.dbf                                      100               3000
/oraprd02/oradata/dbprod/undotbs01.dbf                                     100               3000
/oraprd02/oradata/dbprod/system01.dbf                                      100               3000

Agora vamos ao Resize.
vamos fazer um select que busque todas as tablespaces do banco.

SQL> select tablespace_name
  2  from dba_data_files
  3  group by tablespace_name;

TABLESPACE_NAME
------------------------------
SYSAUX
UNDOTBS1
USERS
SYSTEM

Vamos juntar esses dados conforme o próximo select.

SQL> l
  1   select 'alter database datafile ''' || file_name || ''' resize ' ||
  2  ceil( (nvl(hwm,1)*8192)/1024/1024+1 )|| 'm;' smallest,
  3  ceil( blocks*8192/1024/1024) currsize,
  4  ceil( blocks*8192/1024/1024) -
  5  ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings
  6  from dba_data_files a,
  7  ( select file_id, max(block_id+blocks-1) hwm
  8  from dba_extents where tablespace_name in ('SYSAUX','UNDOTBS1','USERS','SYSTEM')
  9  group by file_id ) b
 10  where a.file_id = b.file_id(+)
 11  and tablespace_name in
 12  ('SYSAUX','UNDOTBS1','USERS','SYSTEM')
 13* order by savings
SQL> /

SMALLEST                                                                                    CURRSIZE    SAVINGS
------------------------------------------------------------------------------------------- ---------- ----------
alter database datafile '/oraprd02/oradata/dbprod/undotbs01.dbf' resize 86m;                        85          0
alter database datafile '/oraprd02/oradata/dbprod/system01.dbf' resize 438m;                       440          3
alter database datafile '/oraprd02/oradata/dbprod/users01.dbf' resize 2m;                            5          4
alter database datafile '/oraprd02/oradata/dbprod/sysaux01.dbf' resize 245m;                       250          6

Com estas três colunas temos o seguinte.

SMALLEST é apenas o comando para efetuar o resize, o comando já vem pronto.
CURRSIZE é o tamanho original do datafile
SAVINGS é o quanto irá salvar em “M” de espaço no disco.

Neste banco como é apenas um banco criado para testes internos de backup não há necessidade de efetuar resize.
Mas vamos demonstrar o problema criando um novo datafile para a tablespace USERS

SQL> alter tablespace USERS add datafile '/oraprd02/oradata/dbprod/users02.dbf' size 500m autoextend on next 100m maxsize 3000m;

Tablespace altered.

SQL> l
  1   select 'alter database datafile ''' || file_name || ''' resize ' ||
  2  ceil( (nvl(hwm,1)*8192)/1024/1024+1 )|| 'm;' smallest,
  3  ceil( blocks*8192/1024/1024) currsize,
  4  ceil( blocks*8192/1024/1024) -
  5  ceil( (nvl(hwm,1)*8192)/1024/1024 ) savings
  6  from dba_data_files a,
  7  ( select file_id, max(block_id+blocks-1) hwm
  8  from dba_extents where tablespace_name in ('USERS')
  9  group by file_id ) b
 10  where a.file_id = b.file_id(+)
 11  and tablespace_name in
 12  ('USERS')
 13* order by savings
SQL> /

SMALLEST                                                                              CURRSIZE    SAVINGS
----------------------------------------------------------------------------------- ---------- ----------
alter database datafile '/oraprd02/oradata/dbprod/users01.dbf' resize 2m;                    5          4
alter database datafile '/oraprd02/oradata/dbprod/users02.dbf' resize 2m;                  500        499

Observe agora que o datafile “/oraprd02/oradata/dbprod/users02.dbf” tem um CURRSIZE de 500M e o SAVINGS de 499M

SQL> select FILE_NAME,MAXBYTES/1024/1024,BYTES/1024/1024 from dba_data_files;

FILE_NAME                                          MAXBYTES/1024/1024 BYTES/1024/1024
-------------------------------------------------- ------------------ ---------------
/oraprd02/oradata/dbprod/users01.dbf                             3000               5
/oraprd02/oradata/dbprod/sysaux01.dbf                            3000             250
/oraprd02/oradata/dbprod/undotbs01.dbf                           3000              85
/oraprd02/oradata/dbprod/system01.dbf                            3000             440
/oraprd02/oradata/dbprod/users02.dbf                             3000             500

SQL> alter database datafile '/oraprd02/oradata/dbprod/users02.dbf' resize 2m;

Database altered.

SQL> select FILE_NAME,MAXBYTES/1024/1024,BYTES/1024/1024 from dba_data_files;

FILE_NAME                                          MAXBYTES/1024/1024 BYTES/1024/1024
-------------------------------------------------- ------------------ ---------------
/oraprd02/oradata/dbprod/users01.dbf                             3000               5
/oraprd02/oradata/dbprod/sysaux01.dbf                            3000             250
/oraprd02/oradata/dbprod/undotbs01.dbf                           3000              85
/oraprd02/oradata/dbprod/system01.dbf                            3000             440
/oraprd02/oradata/dbprod/users02.dbf                             3000               2

Pronto recuperamos 498M de espaço em disco que estava sendo usado sem necessidade.

Caso você crie datafiles sem a clausula de autoextend fica facil resolver esta situação dando este autoextend a ele, conforme abaixo.

SQL> alter tablespace USERS add datafile '/oraprd02/oradata/dbprod/users03.dbf' size 3000m;

Tablespace altered.

SQL> select FILE_NAME,MAXBYTES/1024/1024,BYTES/1024/1024 from dba_data_files;

FILE_NAME                                          MAXBYTES/1024/1024 BYTES/1024/1024
-------------------------------------------------- ------------------ ---------------
/oraprd02/oradata/dbprod/users01.dbf                             3000               5
/oraprd02/oradata/dbprod/sysaux01.dbf                            3000             250
/oraprd02/oradata/dbprod/undotbs01.dbf                           3000              85
/oraprd02/oradata/dbprod/system01.dbf                            3000             440
/oraprd02/oradata/dbprod/users02.dbf                             3000               2
/oraprd02/oradata/dbprod/users03.dbf                                0            3000

6 rows selected.

Criei um datafile já com 3G observe no select que o mesmo não existe maxbites e o bytes já esta com 3000M


SQL> alter database datafile '/oraprd02/oradata/dbprod/users03.dbf' autoextend on next 100m maxsize 3000m;

Database altered.

Agora setei o datafile para utilizar autoextend e setar o maximo dele para 3000M


SQL> alter database datafile '/oraprd02/oradata/dbprod/users03.dbf' resize 2m;

Database altered.

SQL> select FILE_NAME,MAXBYTES/1024/1024,BYTES/1024/1024 from dba_data_files;

FILE_NAME                                          MAXBYTES/1024/1024 BYTES/1024/1024
-------------------------------------------------- ------------------ ---------------
/oraprd02/oradata/dbprod/users01.dbf                             3000               5
/oraprd02/oradata/dbprod/sysaux01.dbf                            3000             250
/oraprd02/oradata/dbprod/undotbs01.dbf                           3000              85
/oraprd02/oradata/dbprod/system01.dbf                            3000             440
/oraprd02/oradata/dbprod/users02.dbf                             3000               2
/oraprd02/oradata/dbprod/users03.dbf                             3000               2

6 rows selected.

Acima eu efetuei o resize e já fiz o select mostrando o ganho no bytes utilizados pelo datafile.

%name Resize tablespace

Autor: Leandro Lana

Trabalho com banco de dados Oracle desde 2006, já trabalhei com as plataformas 9i, 10G, 11G, 12C, 18C, 19C e 21(ainda em testes).

Trabalhando atualmente como consultor Oracle na MigraTI Soluções em TI como administrador de banco de dados Oracle, SQL-Server, MySQL e Postgresql.

Contato: leandro.lana@migrati.com.br

Fone: (47) 9191-6052 / (47) 3328 0996

Certificações:

OCA 10G.

OCP 10G.

OCE Linux.

OCE RAC/Cluster.

MCP SQL-Server 2008.

MCITP SQL-Server 2008.

DB2 Fundamentals.