RECLAIMING WASTED SPACE IN A SEGMENT
QUESTIONS
How to reduce the size of a segment?
How to run the Segment Advisor manually with a PL/SQL package for a specific segment?
How to perform an online segment shrink?
How to run the Segment Advisor manually with a PL/SQL package for a specific segment?
How to perform an online segment shrink?
SOLUTION
Run Segment Advisor against a segment and review results in dba_advisor_* views.
TESTCASE
The testcase is made up of the next steps:
A. Create a table with an index and fill it with random data. Gather details about the segments.
B. Delete half of all rows in the table. Gather details about the segments.
C. Run the Segment Advisor and review recommendations.
D. Shrink the segments.
E. Gather details about the segments after shrinking operation.
F. Clean up the database of the testcase data.
A. Create a table with an index and fill it with random data. Gather details about the segments.
A tablespace for this testcase
CREATE TABLESPACE TESTCASE_TBS DATAFILE '/oracle/oradata/DBPILOT/testcase_tbs_001.dbf' SIZE 2G;
COL TABLESPACE_NAME FOR A15
SELECT A.TABLESPACE_NAME,
ROUND(A.MB,1) ALLOCATED_MB,
ROUND(A.MB - B.MB,1) USED_MB,
ROUND(B.MB,1) FREE_MB
FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A
INNER JOIN
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
ON A.TABLESPACE_NAME=B.TABLESPACE_NAME
WHERE A.TABLESPACE_NAME='TESTCASE_TBS';
TABLESPACE_NAME ALLOCATED_MB USED_MB FREE_MB
--------------- ------------ ---------- ----------
TESTCASE_TBS 2048 1 2047A user for this testcase
CREATE USER TESTCASE IDENTIFIED BY TESTCASE DEFAULT TABLESPACE TESTCASE_TBS;
GRANT CONNECT, RESOURCE TO TESTCASE;
GRANT SELECT ON V_$MYSTAT TO TESTCASE;
GRANT SELECT ON V_$SESSION TO TESTCASE;
GRANT SELECT ON DBA_OBJECTS TO TESTCASE;
GRANT SELECT ON DBA_INDEXES TO TESTCASE;
GRANT SELECT ON DBA_FREE_SPACE TO TESTCASE;
GRANT SELECT ON DBA_DATA_FILES TO TESTCASE;
GRANT SELECT ON SYS.DBA_ADVISOR_TASKS TO TESTCASE;
GRANT SELECT ON SYS.DBA_ADVISOR_OBJECTS TO TESTCASE;
GRANT SELECT ON SYS.DBA_ADVISOR_FINDINGS TO TESTCASE;
GRANT SELECT ON SYS.DBA_ADVISOR_ACTIONS TO TESTCASE;
GRANT SELECT ON DBA_ADVISOR_OBJECT_TYPES TO TESTCASE;
GRANT SELECT ON SYS.WRI$_ADV_MESSAGE_GROUPS TO TESTCASE;
GRANT SELECT ON SYS.DBA_ADVISOR_TASKS TO TESTCASE;
GRANT SELECT ON SYS.WRI$_ADV_FINDINGS TO TESTCASE;
GRANT SELECT ON SYS.WRI$_ADV_OBJECTS TO TESTCASE;
GRANT SELECT ON SYS.WRI$_ADV_RECOMMENDATIONS TO TESTCASE;
GRANT SELECT ON SYS.WRI$_ADV_ACTIONS TO TESTCASE;
GRANT SELECT ON DBA_SEGMENTS TO TESTCASE;
GRANT SELECT ON DBA_TABLES TO TESTCASE;
GRANT UNLIMITED TABLESPACE TO TESTCASE;
GRANT ADVISOR TO TESTCASE;
GRANT ANALYZE ANY TO TESTCASE;
GRANT EXECUTE ON DBMS_SPACE TO TESTCASE;A table for this testcase
SQL> CONNECT TESTCASE/TESTCASE
Connected.
SQL> SHO USER
USER is "TESTCASE"
CREATE TABLE S_SHRINK (ROW_NUMBER NUMBER, TEXT VARCHAR2(4000));
Table created.Fill the table with a random data and then create an index
SET TIMING ON
BEGIN
FOR I IN 1..100000
LOOP
INSERT INTO S_SHRINK VALUES (I, DBMS_RANDOM.STRING('P','4000'));
COMMIT;
END LOOP;
END;
/
SQL> CREATE INDEX TESTCASE.S_SHRINK_TEXT ON TESTCASE.S_SHRINK(TEXT);
Index created.Gather details about the created segments
--------------------------
-- Analyze segments
--------------------------
SQL> ANALYZE TABLE TESTCASE.S_SHRINK COMPUTE STATISTICS;
Table analyzed.
SQL> ANALYZE INDEX TESTCASE.S_SHRINK_TEXT COMPUTE STATISTICS;
Index analyzed.
--------------------------
-- Segments size
--------------------------
COL SEGMENT_NAME FOR A15
SELECT SEGMENT_TYPE,
SEGMENT_NAME,
ROUND(BYTES/1024/1024,1) MB,
BLOCKS,
EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('S_SHRINK','S_SHRINK_TEXT');
SEGMENT_TYPE SEGMENT_NAME MB BLOCKS EXTENTS
------------------ --------------- ---------- ---------- ----------
TABLE S_SHRINK 792 101376 170
INDEX S_SHRINK_TEXT 792 101376 170
--------------------------
-- Tables details
--------------------------
SET LINES 300
SET PAGES 999
COL TABLE_NAME FOR A20
SELECT TABLE_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
PCT_FREE,
LAST_ANALYZED
FROM DBA_TABLES
WHERE TABLE_NAME='S_SHRINK';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE PCT_FREE LAST_ANALYZED
-------------------- ---------- ---------- ------------ ---------- ---------- ------------------
S_SHRINK 100000 100877 499 4062 10 14-FEB-18
--------------------------
-- Index details
--------------------------
COL INDEX_NAME FOR A15
COL INDEX_TYPE FOR A10
SELECT INDEX_NAME,
INDEX_TYPE,
NUM_ROWS,
LEAF_BLOCKS,
DISTINCT_KEYS,
PCT_FREE,
LAST_ANALYZED
FROM DBA_INDEXES
WHERE INDEX_NAME='S_SHRINK_TEXT';
INDEX_NAME INDEX_TYPE NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS PCT_FREE LAST_ANALYZED
--------------- ---------- ---------- ----------- ------------- ---------- ------------------
S_SHRINK_TEXT NORMAL 100000 100000 100000 10 14-FEB-18As it might be seen the size of the index is exactly the same as the table. It’s due to unique values in the TEXT column of the table segment. NUM_ROWS = DISTINCT_KEYS confirmed that all values in index unique too.
Tablespace usage after the data was inserted into the segments
COL TABLESPACE_NAME FOR A15
SELECT A.TABLESPACE_NAME,
ROUND(A.MB,1) ALLOCATED_MB,
ROUND(A.MB - B.MB,1) USED_MB,
ROUND(B.MB,1) FREE_MB
FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A
INNER JOIN
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
ON A.TABLESPACE_NAME=B.TABLESPACE_NAME
WHERE A.TABLESPACE_NAME='TESTCASE_TBS';
TABLESPACE_NAME ALLOCATED_MB USED_MB FREE_MB
--------------- ------------ ---------- ----------
TESTCASE_TBS 2048 1585 463The tablespace is 77.3% filled.
B. Delete half of all rows in the table. Gather details about the segments.
Reducing the number of rows is performed by deleting the odd rows. It will take a while
SQL> SELECT COUNT(*) FROM TESTCASE.S_SHRINK;
COUNT(*)
----------
100000
SET TIMING ON
BEGIN
FOR I IN (SELECT ROW_NUMBER FROM TESTCASE.S_SHRINK)
LOOP
IF MOD(I.ROW_NUMBER,2) != 0
THEN
DELETE TESTCASE.S_SHRINK WHERE ROW_NUMBER=I.ROW_NUMBER;
COMMIT;
END IF;
END LOOP;
END;
/So, now the table has twice fewer rows
SQL> SELECT COUNT(*) FROM TESTCASE.S_SHRINK;
COUNT(*)
----------
50000Gather details about created segments after rows were deleted
--------------------------
-- Analyze segments
--------------------------
SQL> ANALYZE TABLE TESTCASE.S_SHRINK COMPUTE STATISTICS;
Table analyzed.
SQL> ANALYZE INDEX TESTCASE.S_SHRINK_TEXT COMPUTE STATISTICS;
Index analyzed.
--------------------------
-- Segments size
--------------------------
COL SEGMENT_NAME FOR A15
SELECT SEGMENT_TYPE,
SEGMENT_NAME,
ROUND(BYTES/1024/1024,1) MB,
BLOCKS,
EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('S_SHRINK','S_SHRINK_TEXT');
SEGMENT_TYPE SEGMENT_NAME MB BLOCKS EXTENTS
------------------ --------------- ---------- ---------- ----------
TABLE S_SHRINK 792 101376 170
INDEX S_SHRINK_TEXT 792 101376 170
--------------------------
-- Tables details
--------------------------
SET LINES 300
SET PAGES 999
COL TABLE_NAME FOR A20
SELECT TABLE_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
PCT_FREE,
LAST_ANALYZED
FROM DBA_TABLES
WHERE TABLE_NAME='S_SHRINK';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE PCT_FREE LAST_ANALYZED
-------------------- ---------- ---------- ------------ ---------- ---------- ------------------
S_SHRINK 50000 100877 499 6065 10 14-FEB-18
--------------------------
-- Index details
--------------------------
COL INDEX_NAME FOR A15
COL INDEX_TYPE FOR A10
SELECT INDEX_NAME,
INDEX_TYPE,
NUM_ROWS,
LEAF_BLOCKS,
DISTINCT_KEYS,
PCT_FREE,
LAST_ANALYZED
FROM DBA_INDEXES
WHERE INDEX_NAME='S_SHRINK_TEXT';
INDEX_NAME INDEX_TYPE NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS PCT_FREE LAST_ANALYZED
--------------- ---------- ---------- ----------- ------------- ---------- ------------------
S_SHRINK_TEXT NORMAL 50000 100000 50000 10 14-FEB-18Now the segments have twice fewer rows but the statistics are mostly the same as when the segments were full (excepting the number of rows). It’s because the delete operation simply releases used space in a block and it doesn’t affect on the total number of the already allocated blocks in the segments.
C. Run the Segment Advisor and review recommendations.
SQL> SHO USER
USER is "TESTCASE"
--------------
-- Table
--------------
SET SERVEROUTOUT ON
DECLARE
TSK_ID NUMBER;
TSK_NAME VARCHAR2(100);
TSK_DESCRIPTION VARCHAR2(500);
OBJ_ID NUMBER;
BEGIN
TSK_NAME := 'S_SHRINK';
TSK_DESCRIPTION := 'Segment advice for S_SHRINK';
-- 1. Create a Task
DBMS_ADVISOR.CREATE_TASK(
ADVISOR_NAME => 'Segment Advisor',
TASK_ID => TSK_ID,
TASK_NAME => TSK_NAME,
TASK_DESC => TSK_DESCRIPTION);
-- 2. Assign the object to the task
DBMS_ADVISOR.CREATE_OBJECT(
TASK_NAME => TSK_NAME,
OBJECT_TYPE => 'TABLE',
ATTR1 => 'TESTCASE',
ATTR2 => 'S_SHRINK',
ATTR3 => NULL,
ATTR4 => NULL,
ATTR5 => NULL,
OBJECT_ID => OBJ_ID);
-- 3. Set the task parameters
DBMS_ADVISOR.SET_TASK_PARAMETER(
TASK_NAME => TSK_NAME,
PARAMETER => 'recommend_all',
VALUE => 'TRUE');
-- 4. Execute the task
DBMS_ADVISOR.EXECUTE_TASK(TSK_NAME);
END;
/
--------------
-- Index
--------------
SET SERVEROUTOUT ON
DECLARE
TSK_ID NUMBER;
TSK_NAME VARCHAR2(100);
TSK_DESCRIPTION VARCHAR2(500);
OBJ_ID NUMBER;
BEGIN
TSK_NAME := 'S_SHRINK_TEXT';
TSK_DESCRIPTION := 'Segment advice for S_SHRINK_TEXT';
-- 1. Create a Task
DBMS_ADVISOR.CREATE_TASK(
ADVISOR_NAME => 'Segment Advisor',
TASK_ID => TSK_ID,
TASK_NAME => TSK_NAME,
TASK_DESC => TSK_DESCRIPTION);
-- 2. Assign the object to the task
DBMS_ADVISOR.CREATE_OBJECT(
TASK_NAME => TSK_NAME,
OBJECT_TYPE => 'INDEX',
ATTR1 => 'TESTCASE',
ATTR2 => 'S_SHRINK_TEXT',
ATTR3 => NULL,
ATTR4 => NULL,
ATTR5 => NULL,
OBJECT_ID => OBJ_ID);
-- 3. Set the task parameters
DBMS_ADVISOR.SET_TASK_PARAMETER(
TASK_NAME => TSK_NAME,
PARAMETER => 'recommend_all',
VALUE => 'TRUE');
-- 4. Execute the task
DBMS_ADVISOR.EXECUTE_TASK(TSK_NAME);
END;
/Verify the jobs status. It should have status ‘completed’
COL TASK_NAME FOR A15
COL STATUS FOR A10
SELECT TASK_NAME,
STATUS
FROM DBA_ADVISOR_TASKS
WHERE ADVISOR_NAME='Segment Advisor' AND
TASK_NAME IN ('S_SHRINK','S_SHRINK_TEXT');
TASK_NAME STATUS
--------------- ----------
S_SHRINK COMPLETED
S_SHRINK_TEXT COMPLETEDReview the Segment Advisor results by querying DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_ACTIONS and DBA_ADVISOR_OBJECTS
SET LINES 300
SET PAGES 999
COL SEGNAME FOR A15
COL PARTITION FOR A10
COL TYPE FOR A10
COL MESSAGE FOR A60
SELECT DAO.ATTR2 SEGNAME,
DAO.ATTR3 PARTITION,
DAO.TYPE,
DAF.MESSAGE
FROM DBA_ADVISOR_FINDINGS DAF,
DBA_ADVISOR_OBJECTS DAO
WHERE DAO.TASK_ID = DAF.TASK_ID AND
DAO.OBJECT_ID = DAF.OBJECT_ID AND
DAF.TASK_NAME IN ('S_SHRINK','S_SHRINK_TEXT');
SEGNAME PARTITION TYPE MESSAGE
--------------- ---------- ---------- ------------------------------------------------------------
S_SHRINK TABLE Enable row movement of the table TESTCASE.S_SHRINK and perfo
rm shrink, estimated savings is 341578268 bytes.
S_SHRINK_TEXT INDEX Perform shrink, estimated savings is 352775827 bytes.Get command to execute a shrinking
SET LINES 300
SET PAGES 999
COL COMMAND FOR A12
COL ATTR1 FOR A51
COL ATTR2 FOR A62
COL ATTR3 FOR A55
COL ATTR4 FOR A10
COL ATTR5 FOR A10
SELECT COMMAND,
ATTR1,
ATTR2,
ATTR3,
ATTR4,
ATTR5
FROM DBA_ADVISOR_ACTIONS
WHERE TASK_NAME IN ('S_SHRINK','S_SHRINK_TEXT');
COMMAND ATTR1 ATTR2 ATTR3 ATTR4 ATTR5
------------ --------------------------------------------------- -------------------------------------------------------------- ------------------------------------------------------- ---------- ----------
SHRINK SPACE alter table "TESTCASE"."S_SHRINK" shrink space alter table "TESTCASE"."S_SHRINK" shrink space COMPACT alter table "TESTCASE"."S_SHRINK" enable row movement
SHRINK SPACE alter index "TESTCASE"."S_SHRINK_TEXT" shrink space alter index "TESTCASE"."S_SHRINK_TEXT" shrink space COMPACTReview the Segment Advisor results by querying DBMS_SPACE.ASA_RECOMMENDATIONS
COL TABLESPACE_NAME FOR A13
COL SEGMENT_OWNER FOR A15
COL SEGMENT_NAME FOR A15
COL SEGMENT_TYPE FOR A12
SELECT TABLESPACE_NAME,
SEGMENT_OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
ROUND(ALLOCATED_SPACE/1024/1024,1) ALLOCATED_MB,
ROUND(USED_SPACE/1024/1024,1) USED_MB,
ROUND(ALLOCATED_SPACE/1024/1024 - USED_SPACE/1024/1024,1) WASTED_MB
FROM TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS()) ORDER BY 1;
TABLESPACE_NA SEGMENT_OWNER SEGMENT_NAME SEGMENT_TYPE ALLOCATED_MB USED_MB WASTED_MB
------------- --------------- --------------- ------------ ------------ ---------- ----------
TESTCASE_TBS TESTCASE S_SHRINK_TEXT INDEX 792 455.6 336.4
TESTCASE_TBS TESTCASE S_SHRINK TABLE 792 466.2 325.8NOTE: When executing the testcase, you might have a slight difference in size of USED_MB and WASTED_MB columns, but it would be close to current ones.
Drop the Segment Advisor tasks as they aren’t needed anymore
EXEC DBMS_ADVISOR.DELETE_TASK(TASK_NAME => 'S_SHRINK');
EXEC DBMS_ADVISOR.DELETE_TASK(TASK_NAME => 'S_SHRINK_TEXT');The Segment Advisor provides 2 ways of running a shrink, COMPACT AND CASCADE. According to official documentation.
The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase. The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table will also be shrunk. (You need not specify CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run the OBJECT_DEPENDENT_SEGMENTS procedure of the DBMS_SPACE package. As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify the COMPACT clause.
D. Shrink the segments.
So, to defragment unused space in the segments you must execute the following commands
SQL> SHO USER
USER is "TESTCASE"
------------------------
-- Find current session
------------------------
SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
376
------------------------
-- Run the shrink
------------------------
SET TIMING ON
ALTER TABLE TESTCASE.S_SHRINK ENABLE ROW MOVEMENT;
ALTER TABLE TESTCASE.S_SHRINK SHRINK SPACE CASCADE;
ALTER TABLE TESTCASE.S_SHRINK DISABLE ROW MOVEMENT;NOTE: It’s not necessary to shrink index explicitly. The CASCADE option does it automatically.
If you need to find already running shrink session when you don’t know session ID
SET LINES 300
SET PAGES 999
COL SID FOR 99999
COL SER# FOR 9999999
COL OS_ID FOR A5
COL STATUS FOR A8
COL SQL_FULLTEXT FOR A60
SELECT
SES.SID,
SES.SERIAL# SER#,
SES.PROCESS OS_ID,
SES.STATUS,
SQL.SQL_FULLTEXT
FROM
V$SESSION SES,
V$SQL SQL,
V$PROCESS PRC
WHERE
SES.SQL_ID=SQL.SQL_ID AND
SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND
SES.PADDR=PRC.ADDR AND
UPPER(SQL.SQL_FULLTEXT) LIKE UPPER('ALTER TABLE%SHRINK%');
SID SER# OS_ID STATUS SQL_FULLTEXT
------ -------- ----- -------- ------------------------------------------------------------
376 48918 12428 ACTIVE ALTER TABLE TESTCASE.S_SHRINK SHRINK SPACE CASCADE
E. Gather details about the segments after the shrinking operation.
--------------------------
-- Analyze segments
--------------------------
SQL> ANALYZE TABLE TESTCASE.S_SHRINK COMPUTE STATISTICS;
Table analyzed.
SQL> ANALYZE INDEX TESTCASE.S_SHRINK_TEXT COMPUTE STATISTICS;
Index analyzed.
--------------------------
-- Segments size
--------------------------
COL SEGMENT_NAME FOR A15
SELECT SEGMENT_TYPE,
SEGMENT_NAME,
ROUND(BYTES/1024/1024,1) MB,
BLOCKS,
EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('S_SHRINK','S_SHRINK_TEXT');
SEGMENT_TYPE SEGMENT_NAME MB BLOCKS EXTENTS
------------ --------------- ---------- ---------- ----------
TABLE S_SHRINK 393 50304 121
INDEX S_SHRINK_TEXT 399.3 51104 121
--------------------------
-- Tables details
--------------------------
SET LINES 300
SET PAGES 999
COL TABLE_NAME FOR A20
SELECT TABLE_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
PCT_FREE,
LAST_ANALYZED
FROM DBA_TABLES
WHERE TABLE_NAME='S_SHRINK';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE PCT_FREE LAST_ANALYZED
--------------- ---------- ---------- ------------ ---------- ---------- ------------------
S_SHRINK 50000 50001 303 4057 10 14-FEB-18
--------------------------
-- Index details
--------------------------
COL INDEX_NAME FOR A15
COL INDEX_TYPE FOR A10
SELECT INDEX_NAME,
INDEX_TYPE,
NUM_ROWS,
LEAF_BLOCKS,
DISTINCT_KEYS,
PCT_FREE,
LAST_ANALYZED
FROM DBA_INDEXES
WHERE INDEX_NAME='S_SHRINK_TEXT';
INDEX_NAME INDEX_TYPE NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS PCT_FREE LAST_ANALYZED
--------------- ---------- ---------- ----------- ------------- ---------- ------------------
S_SHRINK_TEXT NORMAL 50000 50000 50000 10 14-FEB-18Get details about tablespace usage after the shrink
COL TABLESPACE_NAME FOR A15
SELECT A.TABLESPACE_NAME,
ROUND(A.MB,1) ALLOCATED_MB,
ROUND(A.MB - B.MB,1) USED_MB,
ROUND(B.MB,1) FREE_MB
FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A
INNER JOIN
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
ON A.TABLESPACE_NAME=B.TABLESPACE_NAME
WHERE A.TABLESPACE_NAME='TESTCASE_TBS';
TABLESPACE_NAME ALLOCATED_MB USED_MB FREE_MB
--------------- ------------ ---------- ----------
TESTCASE_TBS 2048 793.3 1254.8Deallocate unused space (space above HWM) of the segments to the tablespace. It makes sense only if there are lots of space above HWM.
ALTER TABLE TESTCASE.S_SHRINK DEALLOCATE UNUSED;
ALTER INDEX TESTCASE.S_SHRINK_TEXT DEALLOCATE UNUSED;
-- Check tablespace usage
TABLESPACE_NAME ALLOCATED_MB USED_MB FREE_MB
--------------- ------------ ---------- ----------
TESTCASE_TBS 2048 793.3 1254.8The tablespace is 38.6% filled.
F. Clean up the database of the testcase data.
DROP USER TESTCASE CASCADE;
DROP TABLESPACE TESTCASE_TBS INCLUDING CONTENTS AND DATAFILES;
CONCLUSION
The more a segment is active (many deleting and/or update operations) the more wasted space in it. A lot of wasted space can lead to poor performance because an operation will read more blocks than necessary to retrieve data. It especially applies to full scan operations.
The shrinking of the segment can significantly reduce the size of the segment and improve a query performance by reducing the number of reads. Reducing reads for multiple segments, in turn, can improve overall database performance as more data can be held in the buffer cache.
Full scans access all blocks below the high watermark. This occurs even it they’re empty. That’s why the segment should be reorganied to solve such a problem.
REFERENCES
Reclaiming Unused Space
Select * From TABLE(dbms_space.asa_recommendations()) Gets ORA-00942: Table Or View Does Not Exist when not run as SYS user (Doc ID 1564264.1)
Why is no space released after an ALTER TABLE … SHRINK? (Doc ID 820043.1)
Version : 15:41 14.02.2018
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0
Platform : Oracle Linux Server 7.3
Database : Oracle database 12.2.0.1.0