DBMS_SPACE

（一）WILL COST

1. index cost需要先分析对应表： 
execute dbms_stats.gather_table_stats(ownname =>'owner', tabname => 'table_name');
DBMS_SPACE.CREATE_INDEX_COST (
   ddl                IN    VARCHAR2,
   used_bytes      OUT   NUMBER,
   alloc_bytes     OUT   NUMBER,
   plan_table      IN    VARCHAR2 DEFAULT NULL);

2. table cost:
DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN VARCHAR2,
   avg_row_size       IN NUMBER,
   row_count          IN NUMBER,
   pct_free           IN NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN VARCHAR2,
   colinfos           IN  CREATE_TABLE_COST_COLUMNS,
   row_count          IN NUMBER,
   pct_free           IN NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

CREATE TYPE create_table_cost_colinfo IS OBJECT (
   COL_TYPE   VARCHAR(200),
   COL_SIZE   NUMBER);
type create_table_cost_columns is varray(50000) of create_table_cost_colinfo;

The used_bytes represent the actual bytes used by the data. This includes the overhead due to the block metadata, pctfree etc.
The alloc_bytes represent the size of the table when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.

set serveroutput on 
DECLARE 
 ub NUMBER; 
 ab NUMBER; 
 cl sys.create_table_cost_columns; 
BEGIN 
  cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10), 
        sys.create_table_cost_colinfo('VARCHAR2',30), 
        sys.create_table_cost_colinfo('VARCHAR2',30), 
        sys.create_table_cost_colinfo('DATE',NULL)); 
 
  DBMS_SPACE.CREATE_TABLE_COST('SYSTEM',cl,100000,0,ub,ab); 
 
  DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || TO_CHAR(ub)); 
  DBMS_OUTPUT.PUT_LINE('Alloc Bytes: ' || TO_CHAR(ab)); 
END; 
/ 

3. 数据增长趋势：
DBMS_SPACE.OBJECT_GROWTH_TREND (
   object_owner           IN    VARCHAR2,
   object_name            IN    VARCHAR2,
   object_type            IN    VARCHAR2,
   partition_name         IN    VARCHAR2 DEFAULT NULL,
   start_time             IN    TIMESTAMP DEFAULT NULL,      ----依赖统计数据的开始时间
   end_time               IN    TIMESTAMP DEFAULT NULL,    ---依赖统计数据的结束时间
   interval               IN    DSINTERVAL_UNCONSTRAINED DEFAULT NULL,    --The interval at which to sample
   skip_interpolated      IN    VARCHAR2 DEFAULT 'FALSE',    --Whether interpolation of missing values should be skipped
   timeout_seconds        IN    NUMBER DEFAULT NULL,    --The time-out value for the function in seconds
   single_datapoint_flag  IN    VARCHAR2 DEFAULT 'TRUE')     --Whether in the absence of statistics the segment should be sampled
 RETURN object_growth_trend_table PIPELINED;  

TYPE object_growth_trend_row IS RECORD(
   timepoint      TIMESTAMP,
   space_usage    NUMBER,
   space_alloc    NUMBER,
   quality        VARCHAR(20)); 
TYPE object_growth_trend_table IS TABLE OF object_growth_trend_row;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（二）SPACE USAGE

1. 查询MSSM表空间segment freelist上的free blocks
variable free_blocks number; 
exec DBMS_SPACE.FREE_BLOCKS(segment_owner=>'SCOTT', segment_name=>'CLUS', segment_type=>'CLUSTER', freelist_group_id=>3, partition_name=>'p1', free_blks=>:free_blocks); 
print free_blocks;

2. 查询ASSM表空间segment:
1) The first form of the procedure shows the space usage of data blocks under the segment High Water Mark. 
   You can calculate usage for LOBs, LOB PARTITIONS and LOB SUBPARTITIONS. 
   This procedure can only be used on tablespaces that are created with auto segment space management. 
   The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. Note that this overload cannot be used on SECUREFILE LOBs.
   For LOB segments, the number of blocks that is returned from full_blocks and unformatted_blocks is actually the number of chunks for the LOB segment.

DBMS_SPACE.SPACE_USAGE(
   segment_owner           IN  VARCHAR2,
   segment_name            IN  VARCHAR2,
   segment_type            IN  VARCHAR2,
   unformatted_blocks      OUT NUMBER,     --For LOB segments, the number of blocks that is returned from unformatted_blocks is actually the number of chunks for the LOB segment.
   unformatted_bytes       OUT NUMBER,
   fs1_blocks              OUT NUMBER,    --Number of blocks having at least 0 to 25% free space
   fs1_bytes               OUT NUMBER,
   fs2_blocks              OUT NUMBER,    --Number of blocks having at least 25 to 50% free space
   fs2_bytes               OUT NUMBER,
   fs3_blocks              OUT NUMBER,    --Number of blocks having at least 50 to 75% free space
   fs3_bytes               OUT NUMBER,
   fs4_blocks              OUT NUMBER,    --Number of blocks having at least 75 to 100% free space
   fs4_bytes               OUT NUMBER,
   full_blocks             OUT NUMBER,    --The number of blocks that is returned from full_blocks is actually the number of chunks for the LOB segment
   full_bytes              OUT NUMBER,
   partition_name          IN  VARCHAR2 DEFAULT NULL); 

EXAMPLE:
variable unf number; 
variable unfb number; 
variable fs1 number; 
variable fs1b number; 
variable fs2 number; 
variable fs2b number; 
variable fs3 number; 
variable fs3b number; 
variable fs4 number; 
variable fs4b number; 
variable full number; 
variable fullb number; 
begin 
dbms_space.space_usage('U1','T', 
                        'TABLE', 
                        :unf, :unfb, 
                        :fs1, :fs1b, 
                        :fs2, :fs2b, 
                        :fs3, :fs3b, 
                        :fs4, :fs4b, 
                        :full, :fullb); 
end; 
/ 
print unf ; 
print unfb ; 
print fs4 ; 
print fs4b; 
print fs3 ; 
print fs3b; 
print fs2 ; 
print fs2b; 
print fs1 ; 
print fs1b; 
print full; 
print fullb; 

2) The second form of the procedure returns information about SECUREFILE LOB space usage. 
    It will return the amount of space in blocks being used by all the SECUREFILE LOBs in the LOB segment. 
    The procedure displays the space actively used by the LOB column, freed space that has retention expired, and freed space that has retention unexpired. 
    Note that this overload can be used only on SECUREFILE LOBs.

DBMS_SPACE.SPACE_USAGE(
   segment_owner           IN    VARCHAR2,
   segment_name            IN    VARCHAR2,
   segment_type            IN    VARCHAR2,
   segment_size_blocks     OUT   NUMBER,    --Number of blocks allocated to the segment
   segment_size_bytes      OUT   NUMBER,
   used_blocks             OUT   NUMBER,        --Number blocks allocated to the LOB that contains active data
   used_bytes              OUT   NUMBER,
   expired_blocks          OUT   NUMBER,        --Number of expired blocks used by the LOB to keep version data
   expired_bytes           OUT   NUMBER,
   unexpired_blocks        OUT   NUMBER,    --Number of unexpired blocks used by the LOB to keep version data
   unexpired_bytes         OUT   NUMBER,
   partition_name          IN    VARCHAR2 DEFAULT NULL);

3. 查询unused blocks

DBMS_SPACE.UNUSED_SPACE (
   segment_owner              IN  VARCHAR2, 
   segment_name               IN  VARCHAR2,
   segment_type               IN  VARCHAR2,
   total_blocks               OUT NUMBER,        --Returns total number of blocks in the segment
   total_bytes                OUT NUMBER,
   unused_blocks              OUT NUMBER,        --Returns number of blocks which are not used
   unused_bytes               OUT NUMBER,
   last_used_extent_file_id   OUT NUMBER,    --Returns the file ID of the last extent which contains data
   last_used_extent_block_id  OUT NUMBER,    --Returns the starting block ID of the last extent which contains data
   last_used_block            OUT NUMBER,     --Returns the last block within this extent which contains data
   partition_name             IN  VARCHAR2 DEFAULT NULL);

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（三）DEPENDENT

返回对象的dependent object
DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS(
   objowner    IN     VARCHAR2,
   objname     IN     VARCHAR2,
   partname    IN     VARCHAR2,    ---partition_name
   objtype     IN     NUMBER) 
   RETURN dependent_segments_table PIPELINED; 

TYPE object_dependent_segment IS RECORD (
   segment_owner      VARCHAR2(100),
   segment_name       VARCHAR2(100),
   segment_type       VARCHAR2(100),
   tablespace_name    VARCHAR2(100),
   partition_name     VARCHAR2(100),
   lob_column_name    VARCHAR2(100)); 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

（四）自定义函数方便查询
效果：
SQL> set serveroutput on
SQL> exec show_space('T1');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           1,419
Total Blocks............................           1,536
Total Bytes.............................      12,582,912
Total MBytes............................              12
Unused Blocks...........................              85
Unused Bytes............................         696,320
Last Used Ext FileId....................               7
Last Used Ext BlockId...................           1,664
Last Used Block.........................              43

set define off
create or replace procedure show_space
( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
    l_free_blks                 number;
    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_LAST_USED_BLOCK           number;
    l_segment_space_mgmt        varchar2(255);
    l_unformatted_blocks number;
    l_unformatted_bytes number;
    l_fs1_blocks number; l_fs1_bytes number;
    l_fs2_blocks number; l_fs2_bytes number;
    l_fs3_blocks number; l_fs3_bytes number;
    l_fs4_blocks number; l_fs4_bytes number;
    l_full_blocks number; l_full_bytes number;

    -- inline procedure to print out numbers nicely formatted
    -- with a simple label
    procedure p( p_label in varchar2, p_num in number )
    is
    begin
        dbms_output.put_line( rpad(p_label,40,'.') ||
                              to_char(p_num,'999,999,999,999') );
    end;
begin
   -- this query is executed dynamically in order to allow this procedure
   -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
   -- via a role as is customary.
   -- NOTE: at runtime, the invoker MUST have access to these two
   -- views!
   -- this query determines if the object is a ASSM object or not
   begin
      execute immediate 
          'select ts.segment_space_management
             from dba_segments seg, dba_tablespaces ts
            where seg.segment_name      = :p_segname
              and (:p_partition is null or 
                  seg.partition_name = :p_partition)
              and seg.owner = :p_owner
              and seg.segment_type = :p_type
              and seg.tablespace_name = ts.tablespace_name'
             into l_segment_space_mgmt
            using p_segname, p_partition, p_partition, p_owner, p_type;
   exception
       when too_many_rows then
          dbms_output.put_line
          ( 'This must be a partitioned table, use p_partition => ');
          return;
   end;

   -- if the object is in an ASSM tablespace, we must use this API
   -- call to get space information, else we use the FREE_BLOCKS
   -- API for the user managed segments
   if l_segment_space_mgmt = 'AUTO'
   then
     dbms_space.space_usage 
     ( p_owner, p_segname, p_type, l_unformatted_blocks,
       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
     
     p( 'Unformatted Blocks ', l_unformatted_blocks );
     p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );
     p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
     p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
     p( 'FS4 Blocks (75-100)', l_fs4_blocks );
     p( 'Full Blocks        ', l_full_blocks );
  else
     dbms_space.free_blocks(
       segment_owner     => p_owner,
       segment_name      => p_segname,
       segment_type      => p_type,
       freelist_group_id => 0,
       free_blks         => l_free_blks);
     
     p( 'Free Blocks', l_free_blks );
  end if;

  -- and then the unused space API call to get the rest of the 
  -- information
  dbms_space.unused_space
  ( segment_owner     => p_owner,
    segment_name      => p_segname,
    segment_type      => p_type,
    partition_name    => p_partition,
    total_blocks      => l_total_blocks,
    total_bytes       => l_total_bytes,
    unused_blocks     => l_unused_blocks,
    unused_bytes      => l_unused_bytes,
    LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
    LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
    LAST_USED_BLOCK => l_LAST_USED_BLOCK );

    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
    p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
set define on