We recently had to reorganize 7 bigfile tablespaces/datafiles which had almost 8-10TB free in each of them and only consumed less than 1TB of space.
Te datafiles contained weekly/daily partitions from a few tables and thus after housekeeping, it left lot of fragmented space.
We utilized online move feature of 19c for all the objects - TABLE, TABLE PARTITIONS, LOB, LOB PARTITIONS, INDEXES
To answer "why we didn't use dbms_redefinition?" - the segments to reorganize for us were smaller and using alter ... move online update indexes was much quicker for us. The partitions of indexes/tables/lobs were scattered across multiple tablespaces and they belonged to single parent table(s). To reorganize/defragment individual datafiles, this approach worked efficiently for us.
Here are the steps.
Find HWM of datafile. Let's assume datafile belonged to tablespace=APP_DATA, file_id=8
Select max(block_id)*8192/1024/1024/1024 hwm_gb from dba_extents where FILE_ID = 8;
Find type of segments/objects contained in this datafile above 1 TB (this is a value I used, you may change depending upon how much you need in your datafile to be retained)
Select file_id, segment_type, count(distinct segment_name ||'.'||partition_name) as count
FROM DBA_EXTENTS
WHERE ((block_id + blocks-1)*8192)/1024/1024/1024/1024 > 1
AND FILE_ID = 8
group by file_id,segment_type
ORDER BY 1
/
Example:
FILE_ID SEGMENT_TYPE COUNT
---------- ------------------ ----------
8 TABLE PARTITION 306
8 LOB PARTITION 6
8 LOBSEGMENT 3
8 TABLE 2
8 INDEX 1
Now that you know all the object types, just prepare SQL statements as below. Feel free to adjust parallelism and other items based on your environment. We were on an Oracle Engineered System so hardware was not an issue.
-- TABLE
set pages 0 lines 300 long 999 echo off feed off head off
spool 01_tables.sql
select 'set time on timing on echo on' from dual;
set serveroutput on
begin
for i in (select distinct owner as town, segment_name as segname
FROM DBA_EXTENTS
WHERE ((block_id + blocks-1)*8192)/1024/1024/1024/1024 > 1
AND FILE_ID = 8
AND SEGMENT_TYPE LIKE 'TABLE'
ORDER BY 1)
loop
dbms_output.put_line('alter table ' || i.town || '.' || i.segname || ' move tablespace SCRATCH update indexes parallel 64;');
for j in (select owner as idxown, index_name as indx from dba_indexes where owner=i.town and table_name=i.segname)
loop
dbms_output.put_line('alter index ' || j.idxown || '.' || j.indx || ' rebuild tablespace scratch online parallel 64;');
dbms_output.put_line('alter index ' || j.idxown || '.' || j.indx || ' noparallel;');
end loop;
end loop;
end;
/
select 'select ''Completed> '' || systimestamp from dual;' from dual;
spool off;
-- TABLE PARTITION
set pages 0 lines 300 long 999 echo off feed off head off
spool 02_tab_parts.sql
select 'set time on timing on echo on' from dual;
select 'select ''Started> '' || systimestamp from dual;' from dual;
select distinct 'alter table ' || owner || '.' || segment_name
|| ' move partition ' || partition_name
|| ' tablespace SCRATCH update indexes online parallel 64;' src
FROM DBA_EXTENTS
WHERE ((block_id + blocks-1)*8192)/1024/1024/1024/1024 > 1
AND FILE_ID = 8
AND SEGMENT_TYPE LIKE '%TABLE%PARTITION%'
ORDER BY 1;
select 'select ''Completed> '' || systimestamp from dual;' from dual;
spool off;
-- LOB PARTITION
set pages 0 lines 300 long 999 echo off feed off head off
spool 03_lob_parts.sql
select 'set time on timing on echo on' from dual;
select distinct 'alter table ' || e.owner || '.' || l.table_name
|| ' move partition ' || l.partition_name
|| ' lob (' || l.COLUMN_NAME || ') store as'
|| case when l.securefile='YES' then ' securefile (tablespace SCRATCH) parallel 64 online update indexes;'
else 'basicfile (tablespace SCRATCH) parallel 64 online update indexes;' end as src
FROM DBA_EXTENTS e, dba_lob_partitions l
WHERE ((e.block_id + e.blocks-1)*8192)/1024/1024/1024/1024 > 1
AND e.owner=l.table_owner AND e.segment_name=l.lob_name AND e.partition_name=l.LOB_PARTITION_NAME
AND e.FILE_ID = 8
AND e.SEGMENT_TYPE LIKE '%LOB%PARTITION%'
ORDER BY 1;
spool off;
-- LOBSEGMENT
set pages 0 lines 300 long 999 echo off feed off head off
spool 04_lobseg.sql
select 'set time on timing on echo on' from dual;
select distinct 'alter table ' || e.owner || '.' || l.table_name
|| ' move lob (' || l.COLUMN_NAME || ') store as'
|| case when l.securefile='YES' then ' securefile (tablespace SCRATCH) parallel 64 online update indexes;'
else 'basicfile (tablespace SCRATCH) parallel 64 online update indexes;' end as src
FROM DBA_EXTENTS e, dba_lobs l
WHERE ((e.block_id + e.blocks-1)*8192)/1024/1024/1024/1024 > 1
AND e.owner=l.owner AND e.segment_name=l.SEGMENT_NAME
AND e.FILE_ID = 8
AND e.SEGMENT_TYPE LIKE 'LOBSEGMENT'
ORDER BY 1;
spool off;
-- INDEX
set pages 0 lines 300 long 999 echo off feed off head off
spool 05_indexes.sql
select 'set time on timing on echo on' from dual;
select 'select ''Started> '' || systimestamp from dual;' from dual;
select distinct 'alter index ' || owner || '.' || segment_name
|| ' rebuild tablespace scratch online parallel 64;' src
FROM DBA_EXTENTS
WHERE ((block_id + blocks-1)*8192)/1024/1024/1024/1024 > 1
AND FILE_ID = 8
AND SEGMENT_TYPE LIKE 'INDEX'
ORDER BY 1;
select 'select ''Completed> '' || systimestamp from dual;' from dual;
spool off;
Once you have the statements, create SCRATCH tablespace and move all the objects above 1TB into SCRATCH tablespace
Create bigfile tablespace SCRATCH datafile size 100m autoextend on maxsize 2000G;
Run SQL statements prepared in previous steps. I would suggest running them using nohup as:
nohup sqlplus / as sysdba @/<file_location>/file_name.sql &
Upon completion of script, check HWM of datafile an resize datafile to smallest possible and limit autoextend capacity
Select max(block_id)*8192/1024/1024/1024 hwm_gb from dba_extents where FILE_ID = 8;
alter database datafile 8 resize 1000G;
alter database datafile 8 autoextend on maxsize 1200G;
Now that we have our datafile shrinked, we can move back all the objects back into it.
To prepare move back statements, let's use the same scripts we prepared in earlier steps:
Eg:
sed 's/SCRATCH/APP_DATA/g' 01_tables.sql > rb_01_tables.sql
Sed tool will search and replace staging tablespace name with application tablespace name. The script can be run again with nohup.
Once all the scripts are run to move data back into original tablespace. Use the query to check no segments are remaining in your tablespace=SCRATCH, file_id=20. If any remaining, then generate appropriate command using steps given earlier and move them back to original tablespace
select file_id, segment_type, count(distinct segment_name ||'.'||partition_name) as count
FROM DBA_EXTENTS
WHERE FILE_ID = 20
group by file_id,segment_type
ORDER BY 1
/
No rows selected.
Done.Enjoy!!!
