shrink segment online

shrink

shrink segemnt 分两部：
1，compact
2. 降低高水位

1.Running the Segment Advisor Manually  
The example that follows shows how to use the DBMS_ADVISOR procedures to run the Segment Advisor for the sample table hr.employees.
The user executing these package procedures must have the EXECUTE object privilege on the package or the ADVISOR system privilege

variable id number;
begin
  declare
  name varchar2(100);
  descr varchar2(500);
  obj_id number;
  begin
    name:='Manual_Employees';
    descr:='Segment Advisor Example';
    dbms_advisor.create_task (
       advisor_name => 'Segment Advisor',
       task_id => :id,
       task_name => name,
       task_desc => descr);
       dbms_advisor.create_object (
       task_name => name,
       object_type => 'TABLE',
       attr1 => 'HR',
       attr2 => 'EMPLOYEES',
       attr3 => NULL,
       attr4 => NULL,
       attr5 => NULL,
       object_id => obj_id);
    dbms_advisor.set_task_parameter(
       task_name => name,
       parameter => 'recommend_all',
       value => 'TRUE');
    dbms_advisor.execute_task(name);
  end;
end;
/

Viewing Segment Advisor Results


Viewing Segment Advisor Results by Querying the DBA_ADVISOR_* Views
Before querying the DBA_ADVISOR_* views, you can check that the Segment Advisor task is complete by querying the STATUS column in DBA_ADVISOR_TASKS.

   select  task_name, status
     from dba_advisor_tasks
   where owner = 'STEVE' and advisor_name = 'Segment Advisor';
   TASK_NAME STATUS
   ------------------------------ -----------
   Manual Employees COMPLETED
   

The following example shows how to query the DBA_ADVISOR_* views to retrieve findings from all Segment Advisor runs submitted by user STEVE:

   select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
   from dba_advisor_findings af, dba_advisor_objects ao
   where ao.task_id = af.task_id
   and ao.object_id = af.object_id
   and ao.owner = 'STEVE';
   

The following query returns recommendations by the most recent run of the Auto Segment Advisor, with the suggested command to run to follow the recommendations:

   select tablespace_name, segment_name, segment_type, partition_name,recommendations, c1
     from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
   
   TABLESPACE_NAME     SEGMENT_NAME    SEGMENT_TYPE                    PARTITION_NAME      RECOMMENDATIONS                                            C1
   ------------------------------ ------------------------------ --------------
   TVMDS_ASSM           ORDERS1         TABLE PARTITION                ORDERS1_P2   Perform shrink, estimated savings is 57666422 bytes.       alter table "STEVE"."ORDERS1" modify partition "ORDERS1_P2" shrink space
   TVMDS_ASSM           ORDERS1         TABLE PARTITION                ORDERS1_P1   Perform shrink, estimated savings is 45083514 bytes.       alter table "STEVE"."ORDERS1" modify partition "ORDERS1_P1" shrink space


2.Shrinking Database Segments Online
Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any
rowid-based triggers defined on the object. You enable row movement in a table with the ALTER TABLE ... ENABLE ROW MOVEMENT command.

Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all
segment types are eligible for online segment shrink except these:
■ IOT mapping tables
■ Tables with rowid based materialized views
■ Tables with function-based indexes
■ SECUREFILE LOBs
■ Compressed tables

You can shrink space in a table, index-organized table, index, partition, subpartition,materialized view, or materialized view log. You do this using ALTER TABLE, ALTER
INDEX, ALTER MATERIALIZED VIEW, or ALTER MATERIALIZED VIEW LOG statement with the SHRINK SPACE clause.

Two optional clauses let you control how the shrink operation proceeds:
■ 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.

Examples
Shrink a table and all of its dependent segments (including BASICFILE LOB segments):
  ALTER TABLE employees SHRINK SPACE CASCADE;
 
Shrink a BASICFILE LOB segment only:
  ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE);
 
Shrink a single partition of a partitioned table:
  ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;
 
Shrink an IOT index segment and the overflow segment:
  ALTER TABLE cities SHRINK SPACE CASCADE;
 
Shrink an IOT overflow segment only:
  ALTER TABLE cities OVERFLOW SHRINK SPACE;
 


3.Deallocating Unused Space
The following statements deallocate unused space in a segment (table, index or cluster):
ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;
The KEEP clause is optional and lets you specify the amount of space retained in the
segment. You can verify that the deallocated space is freed by examining the DBA_
FREE_SPACE view.