We can perform a segment shrink operation on the table
to reclaim space from numerous insert, delete, and update operations on the HR.EMPLOYEES
table. Because the WORK_RECORD column was added to the HR.EMPLOYEES table after the table
was already populated, we may have created some chained rows in the table; in addition, since
the WORK_RECORD column can be up to 4000 bytes long, updates or deletes of rows with big
Chapter 6: Monitoring Space Usage 187
WORK_RECORD columns may create blocks in the table with free space that can be reclaimed.
The view DBA_ADVISOR_RECOMMENDATIONS provides similar information:
SQL> select owner, task_id, task_name, benefit_type
2 from dba_advisor_recommendations
3 where task_id = 384;
OWNER TASK_ID TASK_NAME
---------- ------- ----------
RJB 384 TASK_00003
BENEFIT_TYPE
--------------------------------------------------
Perform shrink, estimated savings is 107602 bytes.
In any case, we will shrink the segment HR.EMPLOYEES to reclaim the free space. As an
added time-saving benefit to the DBA, the SQL needed to perform the shrink is provided in the
view DBA_ADVISOR_ACTIONS:
SQL> select owner, task_id, task_name, command, attr1
2 from dba_advisor_actions where task_id = 384;
OWNER TASK_ID TASK_NAME COMMAND
---------- ------- ---------- -----------------
RJB 6 TASK_00003 SHRINK SPACE
ATTR1
-----------------------------------------------------
alter table HR.EMPLOYEES shrink space
1 row selected.
Pages:
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341