Menu de navegação

[airoospParticipant]

Boa tarde pessoal,

Estou desenvolvendo uma procedure para criar tabelas de histórico para um determinado schema. Estas tabelas serão anuais e cada 1 terá +/- 3.2GB com aproximadamente 14 milhões de linha.
É possível verificar se será necessário aumentar a tablespace antes de criar a tabela?

Banco 11g R2 em Windows.

Obrigado.

Airton

[José Laurindo ChiappaModerator]

Claro que sim : basicamente o que vc vai ter que fazer é checar quanto vc tem de espaço livre na tablespace, quanto de espaço as tais tabelas vão consumir E comparar as duas coisas, simples….
Pra avaliar consumo de espaço futuro em tabela, imho a melhor coisa é fazer é simular : cria a tabela com as colunas todas que vc precisa (inclusive com as colunas LOB, índices todos, completinha) , insere um décimo do volume de linhas que vc espera ter (necessariamente com dados PARECIDOS com o que vc espera ter em prod – não adianta inserir 10 bytes numa coluna VARCHAR2(4000) que vc em Prod espera ter uma média de caracteres preenchidos MUITO MAIOR), veja quanto espaço vc ocupou pra tabela E pros índices/LOBs dela, e multiplica por 10 , isso é mais ou menos o que vc vai consumir, okdoc ??

Aí seguem exemplos das queries que vc vai precisar :

=> tamanho ocupado por uma tabela chamada MINHA_TABELA que pertence a um schema/usuário chamado DONO_DAS_TABELAS:

SELECT tablespace_name, SUM(BYTES) FROM DBA_SEGMENTS WHERE OWNER=’DONO_DAS_TABELAS’ and SEGMENT_TYPE=’TABLE’ and SEGMENT_NAME=’MINHA_TABELA’
GROUP BY TABLESPACE_NAME;

=> tamanho ocupado pelos índices todos da tabela MINHA_TABELA :

SELECT tablespace_name, segment_name, SUM(BYTES) FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE=’INDEX’ and (OWNER,SEGMENT_NAME) in
(SELECT OWNER, INDEX_NAME
FROM DBA_INDEXES
WHERE TABLE_OWNER = ‘DONO_DAS_TABELAS’
AND TABLE_NAME =’MINHA_TABELA’
)
GROUP BY tablespace_name, segment_name;

=> tamanho ocupado pelos LOB SEGMENTS das colunas LOB da dita tabela :

select l.tablespace_name, l.table_name, l.column_name, sum(s.bytes)
from dba_lobs l, dba_segments s
where l.OWNER = ‘DONO_DAS_TABELAS’
and l.TABLE_NAME = ‘MINHA_TABELA’
and l.segment_name = s.segment_name
group by l.tablespace_name, l.table_name, l.column_name;

okdoc ??? Aí, como eu disse, em vc sabendo o espaço consumido por 10% dos dados , multiplica por 10 e põe uma gordurinha, isso é o espaço estimado total… Acredito que vc SABE como checar o espaço livre na(s) tablespace(s) envolvida(s), aí é só comparar o total livre com o total a ser ocupado e vc saberá se cabe ou não cabe…

[]s

Chiappa

[José Laurindo ChiappaModerator]

Detalhe importante : além de uma tabela ocupar espaço diretamente pra seus dados escalares, pra suas colunas LOB e pros seus índices, ela TAMBÉM pode conter PARTITIONS – se são Particionadas as tabelas e/ou os índices que vc quer mensurar tamanho ocupado, INCLUA também uma query na DBA_SEGMENTS somando segmentos do tipo TABLE PARTITION e do tipo INDEX PARTITION….

[]s

Chiappa

[airoospParticipant]

Boa tarde Chiappa,

Tudo beleza? Agradeço as tuas informações.

Para verificar o espaço disponível na tablespace, encontrei a consulta abaixo:

select ddf.tablespace_name as TablespaceName, ddf.file_name as Data_File, ddf.bytes/(1024*1024) as Total_MB,
round((ddf.bytes – sum(nvl(dfs.bytes,0)))/(1024*1024),1) as Used_MB,
round(sum(nvl(dfs.bytes,0))/(1024*1024),1) as Free_MB
from vw_ver_dba_free_space dfs
left join vw_ver_data_files ddf on dfs.file_id = ddf.file_id
where ddf.tablespace_name = ‘COMPRAS’
group by ddf.tablespace_name, ddf.file_name, ddf.bytes
order by ddf.tablespace_name, ddf.file_name;

Só que ficou uma dúvida, após executar o comando abaixo, como aparecerão na consulta acima, os
dados da tablespace?

ALTER TABLESPACE COMPRAS ADD DATAFILE ‘F:ORACLECOMPRASDADOSCOMPRAS08.ORA’ SIZE 100M AUTOEXTEND ON NEXT 1024K MAXSIZE 3000M;

Obrigado.

Airton

[José Laurindo ChiappaModerator]

Blz ? Então, primeiro deixando um pouco de lado por enquanto a questão do AUTOEXTEND, eu digo : com 100% de certeza essa tal de vw_ver_dba_free_space *** NÃO É *** uma view oficial da Oracle (então sei lá o que ela faz, se é apropriada)… Não vejo muito sentido em fazer invencionices se o objetivo é saber o espaço livre/usável na tablespace, veja só :

DEMO@DESENV:SQL>select sum(bytes) TAMANHO_TOTAL_DA_TABLESPACE from dba_data_files where tablespace_name=’USER_DATA’;

TAMANHO_TOTAL_DA_TABLESPACE
——————————–
21474836480

DEMO@DESENV:SQL>select sum(bytes) TAMANHO_OCUPADO from dba_segments where tablespace_name=’USER_DATA’;

TAMANHO_OCUPADO
——————————–
21269118976

==> Veja que há uma ** MÍNIMA ** diferença entre vc fazer a soma do total – ocupado versus a view dba_free_space :

DEMO@DESENV:SQL>select 21474836480 – 21269118976 from dual;

21474836480-21269118976
——————————–
205717504

DEMO@DESENV:SQL>select sum(bytes) TOTAL_LIVRE_NA_TABLESPACE from dba_free_space where tablespace_name=’USER_DATA’;

TOTAL_LIVRE_NA_TABLESPACE
——————————–
205062144

DEMO@DESENV:SQL>

==> mas isso é MÍNIMO e é devido aos poucos blocos de controle/usados internamente pelo Oracle que a dba_free_space já leva em conta, cfrme https://querycsm.wordpress.com/2013/02/16/dba_data_files-dba_free_space-and-dba_segmentsa-myth-buster/ ….

Assim sendo, em princípio eu iria de DBA_FREE_SPACE pra saber o quanto tenho efetivamente usável na tablespace , e como dito pra saber quanto vc vai precisar é somar os segmentos cfrme indiquei…

===>>>>> Voltando agora à questão dos datafiles AUTOEXTENSÍVEIS : como Documentado, a coluna BYTES na DBA_DATA_FILES mesmo para datafiles autoextensíveis registra o valor efetivamente alocado pra tablespace, vejamos um exemplo :

SYSTEM:@O11GR2SE:SQL>create tablespace TS_TESTE_11G datafile ‘D:O11G_DATAFILESts_teste_01.dbf’ size 1G;

Tablespace criado.

SYSTEM:@O11GR2SE:SQL>ALTER tablespace TS_TESTE_11G add datafile ‘D:O11G_DATAFILESts_teste_02.dbf’ size 500M;

Tablespace alterado.

SYSTEM:@O11GR2SE:SQL>create table TAB_TESTE tablespace TS_TESTE_11G as (select * from dba_objects);

Tabela criada.

SYSTEM:@O11GR2SE:SQL>

==> veja como está o consumido e o free :

SYSTEM:@O11GR2SE:SQL>select sum(bytes) from dba_data_files where tablespace_name=’TS_TESTE_11G’;

SUM(BYTES)
————————
1.598.029.824

SYSTEM:@O11GR2SE:SQL>select sum(bytes) from dba_segments where tablespace_name=’TS_TESTE_11G’;

SUM(BYTES)
————————
9.437.184

SYSTEM:@O11GR2SE:SQL>select sum(bytes) from dba_free_space where tablespace_name=’TS_TESTE_11G’;

SUM(BYTES)
————————
1.586.495.488

==> legal : vou adicionar datafiles AUTOEXTENSÍVEIS, vc vai ver que só vai aumentar na free space o que foi EFETIVAMENTE alocado :

SYSTEM:@O11GR2SE:SQL>ALTER tablespace TS_TESTE_11G add datafile ‘D:O11G_DATAFILESts_teste_03.dbf’ size 20M autoextend on maxsize 20G;

Tablespace alterado.

SYSTEM:@O11GR2SE:SQL>ALTER tablespace TS_TESTE_11G add datafile ‘D:O11G_DATAFILESts_teste_04.dbf’ size 20M autoextend on maxsize 20G;

Tablespace alterado.

SYSTEM:@O11GR2SE:SQL>

==> olha só :

SYSTEM:@O11GR2SE:SQL>select sum(bytes) from dba_data_files where tablespace_name=’TS_TESTE_11G’;

SUM(BYTES)
————————
1.639.972.864

SYSTEM:@O11GR2SE:SQL>select sum(bytes) from dba_segments where tablespace_name=’TS_TESTE_11G’;

SUM(BYTES)
————————
9.437.184

SYSTEM:@O11GR2SE:SQL>select sum(bytes) from dba_free_space where tablespace_name=’TS_TESTE_11G’;

SUM(BYTES)
————————
1.626.341.376

==>> OU SEJA, tanto a soma de bytes na DBA_DATA_FILES quanto na DBA_FREE_SPACE cresceu SEM LEVAR EM CONTA o futuro autocrescimento, okdoc ?

SYSTEM:@O11GR2SE:SQL>select file_name, bytes, autoextensible, maxbytes from dba_data_files where tablespace_name=’TS_TESTE_11G’;

FILE_NAME BYTES AUT MAXBYTES
——————————————————————– ———- — ———-
D:O11G_DATAFILESTS_TESTE_01.DBF 1073741824 NO 0
D:O11G_DATAFILESTS_TESTE_02.DBF 524288000 NO 0
D:O11G_DATAFILESTS_TESTE_03.DBF 20971520 YES 21474836480
D:O11G_DATAFILESTS_TESTE_04.DBF 20971520 YES 21474836480

SYSTEM:@O11GR2SE:SQL>

====>>> ESSA é a sua reposta à sua pergunta : a coluna BYTES só aumenta quando o espaço é EFETIVAMENTE alocado, e como a DBA_FREE_SPACE (cfrme Documentado) usa ESSA coluna , não vai ser levado em consideração o espaço FUTURO, isso é uma Promessa….

E este exemplo foi Cuidadosamente preparado para DEMONSTRAR o principal ponto fraco quando nós usamos datafiles autoextensíveis, que eu não tinha citado antes : NÃO HÁ CONTROLE se o espaço livre no Sistema Operacional EFETIVAMENTE vai ser capaz de atender à promessa feita pro banco pelo MAXSIZE… Olha só meu exemplo :

D:O11G_DATAFILES>dir
O volume na unidade D não tem nome.
O Número de Série do Volume é 6002-9724

Pasta de D:O11G_DATAFILES

10/07/2018 18:31 .
10/07/2018 18:31 ..
10/07/2018 18:25 1.073.750.016 TS_TESTE_01.DBF
10/07/2018 18:26 524.296.192 TS_TESTE_02.DBF
10/07/2018 18:30 20.979.712 TS_TESTE_03.DBF
10/07/2018 18:31 20.979.712 TS_TESTE_04.DBF
4 arquivo(s) 1.640.005.632 bytes
2 pasta(s) 25.859.796.992 bytes disponíveis

D:O11G_DATAFILES>

==> tenho apenas 25 GB livre no meu disco D: mas fui capaz de ENGANAR o database e pedir pra ele criar dois datafiles que podem crescer até 20 GB cada um, somando portanto 40 GB que eu NÂO TENHO !!!
Assim, quando se usa datafiles AUTOEXTENSÍVEIS, muda um pouco de figura : antes de mais nada vc TEM que obter o espaço disponível no SO, somar o MAXBYTES pra cada datafile autoextensível e CONFIRMAR que esse espaço PROMETIDO efetivamente VAI EXISTIR na hora que o datafile crescer…. SE existir, aí é a soma de MAXBYTES que vai compor o total geral que pode vir a ser usado, desse total usável vc tira a soma da DBA_SEGMENTS pra ver quanto efetivamente usou e aí SIM um menos outro vai ser quanto vc tem livre…

[]s

Chiappa

[airoospParticipant]

Bom dia Chiappa,

Tudo beleza, agradeço mais uma vez as informações. A view mencionada acima foi criada apenas para que o schema utilize apenas os campos necessários e não tenha privilégio direto na DBA.

create view vw_ver_dba_free_space as
select file_id, tablespace_name, bytes
from sys.dba_free_space;

Obrigado.

Airton

[José Laurindo ChiappaModerator]

Okdoc, fico contente de poder ter ajudado : espero ter esclarecido as dúvidas e indicado o melhor procedimento ….

[]s

Chiappa

[Author]

Posts