Oracle Releases high water level line

Oracle Releases high water level line

/*************************************** **************************
Cause: because the original exported database did not sort the table space, there are two main aspects. First, the user generated too many DELETE operations, causing the table's High Line (HWM) to be in a high position, therefore, although the data volume is small, it occupies a large table space. Second, the index is not rebuilt. Frequent deletion and updates make the index larger and larger. REBUILD index is a very necessary thing.
**************************************** *************************/

-- ** Prioritize CUX custom objects

-- Step 1
-- Tablespace Summary
Select a. TABLESPACE_NAME,
A. TOTAL M_TOTAL,
NVL (B. USED, 0) M_USED,
NVL (B. USED/A. TOTAL) * 100, 0) PCT_USED,
A. FILE_NAME
FROM (SELECT TABLESPACE_NAME,
SUM (BYTES)/(1024*1024) TOTAL,
WMSYS. WM_CONCAT (FILE_NAME) FILE_NAME
From sys. DBA_DATA_FILES
Group by TABLESPACE_NAME),
(SELECT TABLESPACE_NAME, BYTES/(1024*1024) 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) * 100, 0) DESC;

-- Tablespace Objects Detail
Select a. TABLESPACE_NAME,
A. TOTAL M_TOTAL,
NVL (B. USED, 0) M_USED,
NVL (B. USED/A. TOTAL) * 100, 0) PCT_USED,
A. FILE_NAME,
C. OJBECT_TYPE,
C. OJBECT_NAME,
C.M _ OBJ_USED,
NVL (C.M _ OBJ_USED/A. TOTAL) * 100, 0) PCT_OBJ_USED
FROM (SELECT TABLESPACE_NAME,
SUM (BYTES)/(1024*1024) TOTAL,
WMSYS. WM_CONCAT (FILE_NAME) FILE_NAME
From sys. DBA_DATA_FILES
Group by TABLESPACE_NAME),
(SELECT TABLESPACE_NAME, BYTES/(1024*1024) 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) * 100, 0) DESC;

-- Step 2
-------------------------------------
-- Release HWM
-- Only execute the command for table objects that have been cleaned up (Time consumed)
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 %'
AND 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;

-------------------------------
/*
-- Temporarily unavailable
-- 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;