Crecimiento de Tablas e Indices
Para saber como determinar el crecimiento de las tablas (y sus indices) ya que estos varian constantemente y se tienen que estar verificando la fragmentacion de estos, y el espacio disponible en los tablespaces.
Deben utilizar este script para determinar que tablespace esta en peligro de desbordamiento por un extent mayor al espacio disponible. Es lo comun organizar indices y data separadamente.
SET ECHO OFF
REM ******************************************************************
REM DBA REPORTS
REM ******************************************************************
REM
REM Program : rep_space_def.SQL
REM Function: to print the name of segments that allocate
REM extent for them will be fault
REM because there is not enought space on the
REM owner tablespace.
REM
REM ******************************************************************
create table system.free_space_help as
select tablespace_name, max(bytes) bytes from sys.dba_free_space
group by tablespace_name
/
create table system.frsp_anct as select * from sys.dba_free_space where 1=2
/
alter table system.frsp_anct add total_data_files number
/
alter table system.frsp_anct add total_free_space number
/
rem set newpage 0
ttitle center 'Print the name of segments that have not enought space on the'
skip -
center 'owner tablespace for extend.' -
center 'Segments without enought space on TS ' skip skip -
left 'File Name : REP_SPACE_DEF.SQL' -
right 'Page:' format 999 sql.pno skip skip
col ow format a18 heading 'Owner'
col ty format a10 heading 'Type'
col na format a30 heading 'Name'
col exts format 9,999 heading 'Extents'
col maxe format 9,999 heading 'Max'
col flag format a1 heading ' '
col nline newline
column segment_name format a30 heading 'Segment|Name'
column segment_type format a05 heading 'Segm |Type'
column tablespace_name format a14 heading 'Tablespace|Name'
column owner format a09 heading 'Owner'
column bytes format 999,999,999 heading 'Size Extent |on table byt'
column kbytes format 999,999,999 heading 'Size Extent |on table Kby'
column tsbytes format 999,999,999 heading 'Size Extent |on TS bytes '
column tskbytes format 999,999,999 heading 'Size Extent |on TS Kbytes'
column total_data_files format 999,999,999 heading 'Total Dat.Kb|Files for TS'
column total_free_space format 999,999,999 heading 'Total Fre.Kb|Space for TS'
column extents format 999 heading '# |Ext '
column KBytes format 999,999 heading 'Kilo |bytes '
define free_text_to_identify_report = 'Report Space Deficit'
set verify off
spool rep_space_def.out
start time
set verify off
set feedback 6
set heading on
set pages 28
set lines 130
set tab off
set time on
select segment_name, segment_type, a.tablespace_name, owner,
round(a.next_extent * (1 + pct_increase/100)) bytes,
b.bytes tsbytes, a.extents, (a.blocks * 8 ) KBytes
from sys.dba_segments a, system.free_space_help b
where b.tablespace_name = a.tablespace_name
and round(a.next_extent * (1 + pct_increase/100)) > b.bytes
order by 4, 3, 1
/
declare
cursor c1 is
select distinct b.tablespace_name
from sys.dba_segments a, system.free_space_help b
where b.tablespace_name = a.tablespace_name
and round(a.next_extent * (1 + pct_increase/100)) > b.bytes;
cursor c2 (ts char) is
select tablespace_name, file_id, block_id, bytes, blocks
from sys.dba_free_space
where tablespace_name = ts
order by bytes desc;
fts varchar2(30);
fts2 varchar2(30);
fi number;
bi number;
byt number;
bl number;
total_df number;
total_fs number;
begin
open c1 ;
loop
fetch c1 into fts ;
exit when c1%notfound;
select sum(bytes)
into total_df
from sys.dba_data_files
where tablespace_name = fts;
select sum(bytes)
into total_fs
from sys.dba_free_space
where tablespace_name = fts;
open c2(fts);
for i in 1..5 loop
fetch c2 into fts2, fi, bi, byt, bl;
if c2%notfound then
exit;
else
insert into system.frsp_anct values (fts2,fi,bi,byt,bl,total_df,total_fs);
end if;
end loop;
close c2;
end loop;
commit;
close c1;
end;
/
set feedback on
set pages 15
column dt format a45 heading 'Data File Name'
column file_id format 999 heading 'File|Id '
column block_id format 999999 heading 'Block |Id '
rem compute sum of bytes on tablespace_name
break on tablespace_name skip on total_data_files skip on total_free_space skip
select tablespace_name, file_id, block_id, trunc(bytes/1024) tskbytes, blocks,
trunc(total_data_files/1024) total_data_files,
trunc(total_free_space/1024) total_free_space, to_char(null) dt
from system.frsp_anct
union
select tablespace_name,
to_number(null), to_number(null), to_number(null),
to_number(null), to_number(null), to_number(null),
substr(file_name,1,45) file_name
from system.dba_data_files
where tablespace_name in (select distinct tablespace_name
from system.frsp_anct)
order by 1, 8, 4 desc
/
spool off
ttitle off
clear breaks
clear columns
clear computes
drop table system.free_space_help
/
drop table system.frsp_anct
/
set verify on
|
|
