Print Friendly, PDF & Email

 
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?

 
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       2047

A 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-18

As 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        463

The 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(*)
----------
     50000

Gather 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-18

Now 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   COMPLETED

Review 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 COMPACT

Review 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.8

NOTE: 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-18

Get 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.8

Deallocate 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.8

The 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.

 
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