Oracle releases high watermark

/*****************************************************************
Reason: Because the original export database does not tidy up the table space which mainly includes two aspects,
One is that the user generates too many deletes, causing the table's high-level line (HWM) to be in a high position,
So although the amount of data is small, it occupies a large table space, and second, the index is not rebuilt,
Frequent deletions and updates make the index more and more large, and the rebuild index is a necessary thing.
*****************************************************************/

--** priority processing of Cux custom-made objects

--step1
--tablespace Summary
SELECT A.tablespace_name,
A.total M_total,
NVL (b.used, 0) m_used,
NVL ((b.used/a.total) * 0) pct_used,
A.file_name
From (SELECT Tablespace_name,
SUM (BYTES)/(1024x768 * 1024x768) Total,
Wmsys. Wm_concat (file_name) file_name
From SYS. Dba_data_files
GROUP by Tablespace_name) A,
(SELECT Tablespace_name, BYTES/(1024x768 * 1024x768) used
From SYS. sm$ts_used) B
WHERE A.tablespace_name = b.tablespace_name (+)
--and a.tablespace_name like ' cux% '
ORDER by NVL ((b.used/a.total) * 0) DESC;

--tablespace Objects Detail
SELECT A.tablespace_name,
A.total M_total,
NVL (b.used, 0) m_used,
NVL ((b.used/a.total) * 0) pct_used,
A.file_name,
C.ojbect_type,
C.ojbect_name,
C.m_obj_used,
NVL ((c.m_obj_used/a.total) * 0) pct_obj_used
From (SELECT Tablespace_name,
SUM (BYTES)/(1024x768 * 1024x768) Total,
Wmsys. Wm_concat (file_name) file_name
From SYS. Dba_data_files
GROUP by Tablespace_name) A,
(SELECT Tablespace_name, BYTES/(1024x768 * 1024x768) used
From SYS. sm$ts_used) B,
(SELECT Tablespace_name,
Segment_name Ojbect_name,
Segment_type Ojbect_type,
(bytes/1024/1024) M_obj_used
From Dba_segments
WHERE Segment_type in (' TABLE ', ' INDEX ')) C
WHERE A.tablespace_name = b.tablespace_name (+)
and A.tablespace_name = C.tablespace_name (+)
ORDER by NVL ((b.used/a.total) * 0) DESC;

--step2
-------------------------------------
--Release high watermark HWM
--executes only on table objects that have cleansed data (takes too long)
DECLARE
   l_sql1        VARCHAR2 (1000);
   l_sql2        VARCHAR2 (1000);
BEGIN
   for R in (SELECT (OWNER | | '. ' | | segment_name) OBJ
               from Dba_segments
              WHERE Segment_ TYPE = ' TABLE ' and tablespace_name like ' cux% '
               segment_name in (' Cux_inv_issue_oa_log ')
   LOOP
      l_ SQL1: = ' ALTER TABLE ' | | R.obj | | ' Enable row movement ';
      L_SQL2: = ' ALTER TABLE ' | | R.obj | | ' Shrink space ';
      Dbms_output. Put_Line (r.obj);

EXECUTE IMMEDIATE L_SQL1;
EXECUTE IMMEDIATE l_sql2;
END LOOP;
END;

-------------------------------
/*
--not applied at this stage
--rebuild Index
DECLARE
L_sql VARCHAR2 (1000);
BEGIN
For R in (SELECT (owner| | '. ' | | Segment_name) OBJ
From Dba_segments
WHERE segment_type = ' INDEX ' and tablespace_name = ' cux_index ')
LOOP
L_sql: = ' alter index ' | | R.obj | | ' Rebuild online ';
Dbms_output. Put_Line (L_sql);

EXECUTE IMMEDIATE L_sql;
END LOOP;
END;
*/

--Delete the table and delete the Recycle Bin Shift+delete
--drop TABLE Cux. Cux_inv_issue_oa_log_test1 PURGE;
--alter database datafile ' filename ' resize size;

Oracle releases high watermark