SQL> alter table HR.EMPLOYEES shrink space;
Table altered.
As mentioned earlier, the shrink operation does not require extra disk space and does not
prevent access to the table during the operation, except for a very short period of time at the end of
the process to free the unused space. All indexes are maintained on the table during the operation.
In addition to freeing up disk space for other segments, there are other benefits to shrinking a
segment. Cache utilization is improved because fewer blocks need to be in the cache to satisfy
SELECT or other DML statements against the segment. Also, because the data in the segment is
more compact, the performance of full table scans is improved.
There are a couple of caveats and minor restrictions. First, segment shrink will not work
on LOB segments if you are using Oracle Database 10g. Online table reorganization is a more
appropriate method in this case. Also, segment shrink is not allowed on a table that contains
any function-based indexes regardless of whether you are using Oracle Database 10g or 11g.
Undo Advisor and the Automatic Workload Repository
New to Oracle 10g, the Undo Advisor provides tuning information for the undo tablespace,
whether it??™s sized too large, it??™s too small, or the undo retention (via the initialization parameter
UNDO_RETENTION) is not set optimally for the types of transactions that occur in the database.
188 Oracle Database 11g DBA Handbook
Using the Undo Advisor is similar to using the Segment Advisor in that we will call the
DBMS_ADVISOR procedures and query the DBA_ADVISOR_* data dictionary views to see
the results of the analysis.
Pages:
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342