, ,

Decrease HWM (high watermark) of a tablespace

пятница, 27 сентября 2013 г. Оставить комментарий

Script decreases HWM by moving objects within a database tablespace.

  • set your tablespace, which will be used to transfer;
  • set your file_id, which will reduce;
  • set maximum number of iterations;

declare
-- set your tablespace, which will be used to transfer
vts varchar2(30) := 'users';
-- set your file_id, which will reduce
vfile_id pls_integer := 5;
-- set maximum number of iterations
vi_max pls_integer := 10;

vsql varchar2(32000);
vsqlind varchar2(32000);
vsql_prev varchar(32000) := null;
vi pls_integer := 0;
vlob dba_lobs%rowtype;

procedure put_line(vs varchar2)
is
begin
dbms_output.put_line(to_char(sysdate, 'yyyy.mm.dd hh24:mi:ss') || ' ' || vs);
end put_line;

procedure saving(vfile_id pls_integer)
is
vsv varchar2(2000);
begin
select 'Saving space = ' ||
to_char(ceil(dba_data_files.blocks * c.db_block_size/1024/1024) - ceil((nvl(hwm, 1) * c.db_block_size)/1024/1024)) ||
'Mb from ' || ceil(d.bytes/1024/1024) || ' Mb'
into vsv
from dba_data_files,
(select file_id,
max(block_id + blocks - 1) as hwm
from dba_extents
where dba_extents.file_id = vfile_id
group by dba_extents.file_id
) b,
(select value db_block_size from v$parameter where name = 'db_block_size') c,
(
select dba_free_space.file_id,
sum(dba_free_space.bytes) as bytes
from dba_free_space
where dba_free_space.file_id = vfile_id
group by dba_free_space.file_id
) d
where dba_data_files.file_id = b.file_id and
dba_data_files.file_id = d.file_id(+) and
dba_data_files.file_id = vfile_id;

put_line(vsv);
end saving;

begin
saving(vfile_id);

<>
loop
vi := vi + 1;
dbms_output.put_line(vi);

<>
for vcur in (
select dba_extents.*
from dba_extents
where dba_extents.file_id = vfile_id and
dba_extents.block_id = (select max(dba_extents.block_id) from dba_extents where dba_extents.file_id = vfile_id)
) loop

if (vcur.segment_type = 'TABLE PARTITION') then
vsql := 'alter table ' || vcur.owner || '.' || vcur.segment_name || ' move partition ' || vcur.partition_name || ' tablespace ' || vts;
exit first_loop when vsql = vsql_prev or vi > vi_max;
vsql_prev := vsql;

put_line(vsql);
execute immediate vsql;

vsql := 'alter table ' || vcur.owner || '.' || vcur.segment_name || ' move partition ' || vcur.partition_name || ' tablespace ' || vcur.tablespace_name || ' update indexes';
put_line(vsql);
execute immediate vsql;
elsif (vcur.segment_type = 'TABLE SUBPARTITION') then
vsql := 'alter table ' || vcur.owner || '.' || vcur.segment_name || ' move subpartition ' || vcur.partition_name || ' tablespace ' || vts;
exit first_loop when vsql = vsql_prev or vi > vi_max;
vsql_prev := vsql;

put_line(vsql);
execute immediate vsql;

vsql := 'alter table ' || vcur.owner || '.' || vcur.segment_name || ' move subpartition ' || vcur.partition_name || ' tablespace ' || vcur.tablespace_name || ' update indexes';
put_line(vsql);
execute immediate vsql;
elsif (vcur.segment_type = 'TABLE') then
vsql := 'alter table ' || vcur.owner || '.' || vcur.segment_name || ' move tablespace ' || vts;
exit first_loop when vsql = vsql_prev or vi > vi_max;
vsql_prev := vsql;

put_line(vsql);
execute immediate vsql;

vsql := 'alter table ' || vcur.owner || '.' || vcur.segment_name || ' move tablespace ' || vcur.tablespace_name;
put_line(vsql);
execute immediate vsql;

-- rebuild index
for vcurind in (
select all_indexes.owner, all_indexes.index_name
from all_indexes
where all_indexes.table_owner = vcur.owner and
all_indexes.status = 'UNUSABLE' and
all_indexes.table_name = vcur.segment_name
order by all_indexes.table_name, all_indexes.index_name
) loop

vsqlind := 'alter index ' || vcurind.owner || '.' || vcurind.index_name || ' rebuild';
execute immediate vsqlind;
end loop;

elsif (vcur.segment_type = 'INDEX') then
vsql := 'alter index ' || vcur.owner || '.' || vcur.segment_name || ' rebuild tablespace ' || vts;
exit first_loop when vsql = vsql_prev or vi > vi_max;
vsql_prev := vsql;

put_line(vsql);
execute immediate vsql;

vsql := 'alter index ' || vcur.owner || '.' || vcur.segment_name || ' rebuild tablespace ' || vcur.tablespace_name;
put_line(vsql);
execute immediate vsql;
elsif (vcur.segment_type = 'INDEX PARTITION') then
vsql := 'alter index ' || vcur.owner || '.' || vcur.segment_name || ' rebuild partition ' || vcur.partition_name ||' tablespace ' || vts;
exit first_loop when vsql = vsql_prev or vi > vi_max;
vsql_prev := vsql;

put_line(vsql);
execute immediate vsql;

vsql := 'alter index ' || vcur.owner || '.' || vcur.segment_name || ' rebuild partition ' || vcur.partition_name ||' tablespace ' || vcur.tablespace_name;
put_line(vsql);
execute immediate vsql;
elsif (vcur.segment_type = 'INDEX SUBPARTITION') then
vsql := 'alter index ' || vcur.owner || '.' || vcur.segment_name || ' rebuild subpartition ' || vcur.partition_name ||' tablespace ' || vts;
exit first_loop when vsql = vsql_prev or vi > vi_max;
vsql_prev := vsql;

put_line(vsql);
execute immediate vsql;

vsql := 'alter index ' || vcur.owner || '.' || vcur.segment_name || ' rebuild subpartition ' || vcur.partition_name ||' tablespace ' || vcur.tablespace_name;
put_line(vsql);
execute immediate vsql;
elsif (vcur.segment_type = 'LOBSEGMENT') then
select dba_lobs.*
into vlob
from dba_lobs
where dba_lobs.owner = vcur.owner and
dba_lobs.segment_name = vcur.segment_name;

vsql := 'alter table ' || vlob.owner || '.' || vlob.table_name || ' move lob (' || vlob.column_name ||') store as (tablespace ' || vts || ')';
exit first_loop when vsql = vsql_prev or vi > vi_max;
vsql_prev := vsql;

put_line(vsql);
execute immediate vsql;

vsql := 'alter table ' || vlob.owner || '.' || vlob.table_name || ' move lob (' || vlob.column_name ||') store as (tablespace ' || vcur.tablespace_name || ')';
put_line(vsql);
execute immediate vsql;
elsif (vcur.segment_type = 'LOBINDEX') then
select dba_lobs.*
into vlob
from dba_lobs
where dba_lobs.owner = vcur.owner and
dba_lobs.index_name = vcur.segment_name;

vsql := 'alter table ' || vlob.owner || '.' || vlob.table_name || ' move lob (' || vlob.column_name ||') store as (tablespace ' || vts || ')';
exit first_loop when vsql = vsql_prev or vi > vi_max;
vsql_prev := vsql;

put_line(vsql);
execute immediate vsql;

vsql := 'alter table ' || vlob.owner || '.' || vlob.table_name || ' move lob (' || vlob.column_name ||') store as (tablespace ' || vcur.tablespace_name || ')';
put_line(vsql);
execute immediate vsql;
else
exit first_loop;
end if;
end loop second_loop;

end loop first_loop;

saving(vfile_id);
end;


Result:



2013.09.26 16:24:07 Saving space = 18Mb from 22 Mb
1
2013.09.26 16:24:16 alter index SH.FW_PSC_S_MV_WD_BIX rebuild tablespace users
2013.09.26 16:24:17 alter index SH.FW_PSC_S_MV_WD_BIX rebuild tablespace EXAMPLE
2
2013.09.26 16:24:26 alter index SH.FW_PSC_S_MV_PROMO_BIX rebuild tablespace users
2013.09.26 16:24:26 alter index SH.FW_PSC_S_MV_PROMO_BIX rebuild tablespace EXAMPLE
3
2013.09.26 16:24:36 alter index SH.FW_PSC_S_MV_CHAN_BIX rebuild tablespace users
2013.09.26 16:24:36 alter index SH.FW_PSC_S_MV_CHAN_BIX rebuild tablespace EXAMPLE
4
2013.09.26 16:24:45 alter index SH.FW_PSC_S_MV_SUBCAT_BIX rebuild tablespace users
2013.09.26 16:24:45 alter index SH.FW_PSC_S_MV_SUBCAT_BIX rebuild tablespace EXAMPLE
5
2013.09.26 16:24:55 alter table SH.FWEEK_PSCAT_SALES_MV move tablespace users
2013.09.26 16:24:55 alter table SH.FWEEK_PSCAT_SALES_MV move tablespace EXAMPLE
6
2013.09.26 16:25:05 alter table SH.CAL_MONTH_SALES_MV move tablespace users
2013.09.26 16:25:05 alter table SH.CAL_MONTH_SALES_MV move tablespace EXAMPLE
7
2013.09.26 16:25:14 alter index SH.CUSTOMERS_YOB_BIX rebuild tablespace users
2013.09.26 16:25:14 alter index SH.CUSTOMERS_YOB_BIX rebuild tablespace EXAMPLE
8
2013.09.26 16:25:23 alter index SH.CUSTOMERS_MARITAL_BIX rebuild tablespace users
2013.09.26 16:25:23 alter index SH.CUSTOMERS_MARITAL_BIX rebuild tablespace EXAMPLE
9
2013.09.26 16:25:33 alter index SH.CUSTOMERS_GENDER_BIX rebuild tablespace users
2013.09.26 16:25:33 alter index SH.CUSTOMERS_GENDER_BIX rebuild tablespace EXAMPLE
10
2013.09.26 16:25:42 alter index SH.PRODUCTS_PROD_CAT_IX rebuild tablespace users
2013.09.26 16:25:42 alter index SH.PRODUCTS_PROD_CAT_IX rebuild tablespace EXAMPLE
11
2013.09.26 16:25:52 Saving space = 20Mb from 22 Mb

0 коммент. »

Оставьте Ваш комментарий