Manage Space for schema object

可恢复空间工作：
如何工作?
1. 激活resumable space allocation
    The ALTER SESSION ENABLE RESUMABLE statement is issued in the session before the statement executes when the RESUMABLE_TIMEOUT initialization parameter is set to a nonzero value.
    The ALTER SESSION ENABLE RESUMABLE TIMEOUT timeout_value statement is issued in the session before the statement executes, and the timeout_value is a nonzero value.
2. 执行挂起条件，如下：
    Out of space condition
    Maximum extents reached condition
    Space quota exceeded condition.
3.当发生可恢复语句挂起时，执行如下内容：
    在alert.log中生存错误的报告
    系统生存可恢复会话的警告
    如果用户注册AFTER SUSPEND 系统事件的trigger，用户的trigger将被执行.用户能提供pl/sql存储过程访问错误信息，用DBMS_RESUMABLE包和视图DBA或者User_RESUMABLE
4.语句挂起事物自动回被挂起，在语句挂起和恢复过程中
5.当错误的条件被解决，挂起的语句会自动恢复执行和可恢复的挂起会话的警告会被清除
6.dba或者其他用户执行DBMS_RESUMABLE.ABORT()存储过程，可恢复的语句就会强制抛出异常
7.挂起超时的时间间隔的参数是RESUMABLE_TIMEOUT，通过ALTER SESSION ENABLE RESUMABLE TIMEOUT 来修改。
如果在超时的时间间隔没有解决错误的条件，被挂起的语句就会抛出异常给用户
8.可恢复语句在执行期间可挂起并且可恢复多次


可恢复操作有哪些？
1.Queries
  temporary 空间不足
2.ＤＭＬ
3.Import/Export
4.DDL


    CREATE TABLE ... AS SELECT

    CREATE INDEX

    ALTER INDEX ... REBUILD

    ALTER TABLE ... MOVE PARTITION

    ALTER TABLE ... SPLIT PARTITION

    ALTER INDEX ... REBUILD PARTITION

    ALTER INDEX ... SPLIT PARTITION

    CREATE MATERIALIZED VIEW

    CREATE MATERIALIZED VIEW LOG


什么是可正确的错误？
Out of space condition
Maximum extents reached condition
Space quota exceeded condition



设置RESUMABLE_TIMEOUT参数：
如果RESUMABLE_TIMEOUT参数设置为0,也就是关闭可恢复空间分配  ALTER SYSTEM SET RESUMABLE_TIMEOUT=0;



用ALTER SESSION开启和关闭Resumable Space Allocation
ALTER SESSION ENABLE RESUMABLE;
ALTER SESSION DISABLE RESUMABLE;
新会话的可恢复模式是关闭。 默认7200 seconds

指定超时时间间隔：
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;


命名可恢复语句：
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600 NAME 'insert into table';
命名值一直有效，除非ALTER SESSION ENABLE RESUMABL更改或者会话结束。默认的命名值由'User username(userid), Session sessionid, Instance instanceid'
可以通过 DBA_RESUMABLE 和  USER_RESUMABLE 查询定义resumable statement 的名称



查找resumable statement
USER_RESUMABLE ，
DBA_RESUMABLE views,
the DBMS_RESUMABLE.SPACE_ERROR_INFO function

CREATE OR REPLACE TRIGGER resumable_default_timeout
AFTER SUSPEND
ON DATABASE
BEGIN
   DBMS_RESUMABLE.SET_TIMEOUT(10800);
END;


Resumable Space Allocation Example: Registering an AFTER SUSPEND Trigger
In the following example, a system wide AFTER SUSPEND trigger is created and registered as user SYS at the database level. Whenever a resumable statement is suspended in any session, this trigger can have either of two effects:
    If an undo segment has reached its space limit, then a message is sent to the DBA and the statement is aborted.
    If any other recoverable error has occurred, the timeout interval is reset to 8 hours.


Reclaiming Unused Space
Using the Segment Advisor
1. 推荐在线段回收的，对于表空间不是自动段管理的，不能shrinking ,Segment Advisor推荐online table redefinition

 the object is a table that is not eligible for shrinking, as in the case of a table in a tablespace without
 automatic segment space management, the Segment Advisor recommends online table redefinition.
2.if the Segment Advisor determines that a table could benefit from compression with the advanced row compression method, it makes a recommendation to that effect.

3.If the Segment Advisor encounters a table with row chaining above a certain threshold, it records that fact that the table has an excess of chained rows.



手动 Segment Advisor 条件：
1.你想分析没被Automatic Segment Advisor选的tablespace or segment
2.你想反复采取个别的 tablespace or segment 最新的推荐

Segment Advisor三个级别：
1，Segment level
   an unpartitioned table, a partition or subpartition of a partitioned table, an index, or a LOB column.
2，Object level
   an entire object, such as a table or index. If the object is partitioned, advice is generated on all the partitions of the object. In addition, if you run Segment Advisor manually from Cloud Control, you can request advice on the object's dependent objects, such as indexes and LOB segments for a table
3，Tablespace level—Advice
   is generated for every segment in a tablespace.


Running the Segment Advisor Manually with Cloud Control
You must have the OEM_ADVISOR role to run the Segment Advisor manually with Cloud Control
1，Using the Segment Advisor Wizard
2，Using the Run Segment Advisor command on a schema object page.


Viewing Segment Advisor Results
1，With Cloud Control
2，By querying the DBA_ADVISOR_* views

   select task_name, status from dba_advisor_tasks
      where owner = 'STEVE' and advisor_name = 'Segment Advisor';
      
   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';

3，By calling the DBMS_SPACE.ASA_RECOMMENDATIONS procedure
   
    select tablespace_name, segment_name, segment_type, partition_name,   recommendations, c1
      from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));


Shrinking Database Segments Online：
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
    Tables compressed with the following compression methods:
        Basic table compression using ROW STORE COMPRESS BASIC
        Warehouse compression using COLUMN STORE COMPRESS FOR QUERY
        Archive compression using COLUMN STORE COMPRESS FOR ARCHIVE
However, tables compressed with advanced row compression using ROW STORE COMPRESS ADVANCED are eligible for online segment shrink.

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;


Deallocating Unused Space:
  DBMS_SPACE.UNUSED_SPACE
For segments in locally managed tablespaces with automatic segment space management, use the SPACE_USAGE procedure for more accurate information on 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.



Dropping Unused Object Storage:
The following example drops empty segments from every table in the database.
BEGIN
  DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS();
END;

The following drops empty segments from the HR.EMPLOYEES table, including dependent objects.
BEGIN
  DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS(
    schema_name  => 'HR',
    table_name   => 'EMPLOYEES');
END;