dbms_space

procedure create_index_cost (
 ddl                                in                 varchar2        ,
 used_bytes                        out                   number        ,
 alloc_bytes                       out                   number        ,
 plan_table                         in                 varchar2 default
);

procedure 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        
);

procedure 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        
);

procedure free_blocks (
 segment_owner                      in                 varchar2        ,
 segment_name                       in                 varchar2        ,
 segment_type                       in                 varchar2        ,
 freelist_group_id                  in                   number        ,
 free_blks                         out                   number        ,
 scan_limit                         in                   number default,
 partition_name                     in                 varchar2 default
);

function object_dependent_segments returns dbms_space (
 objowner                           in                 varchar2        ,
 objname                            in                 varchar2        ,
 partname                           in                 varchar2        ,
 objtype                            in                   number        
);

function object_growth_trend returns dbms_space (
 object_owner                       in                 varchar2        ,
 object_name                        in                 varchar2        ,
 object_type                        in                 varchar2        ,
 partition_name                     in                 varchar2 default,
 start_time                         in                timestamp default,
 end_time                           in                timestamp default,
 interval                          day                 interval      to,
 skip_interpolated                  in                 varchar2 default,
 timeout_seconds                    in                   number default,
 single_datapoint_flag              in                 varchar2 default
);

function object_growth_trend_cur returns ref cursor (
 object_owner                       in                 varchar2        ,
 object_name                        in                 varchar2        ,
 object_type                        in                 varchar2        ,
 partition_name                     in                 varchar2 default,
 start_time                         in                timestamp default,
 end_time                           in                timestamp default,
 interval                          day                 interval      to,
 skip_interpolated                  in                 varchar2 default,
 timeout_seconds                    in                   number default
);

function object_growth_trend_curtab returns dbms_space (
);

function object_growth_trend_i_to_s returns number (
 interv                            day                 interval      to
);

function object_growth_trend_swrf returns dbms_space (
 object_owner                       in                 varchar2        ,
 object_name                        in                 varchar2        ,
 object_type                        in                 varchar2        ,
 partition_name                     in                 varchar2 default
);

function object_growth_trend_s_to_i returns interval day to second (
 secsin                             in                   number        
);

procedure object_space_usage (
 object_owner                       in                 varchar2        ,
 object_name                        in                 varchar2        ,
 object_type                        in                 varchar2        ,
 sample_control                     in                   number        ,
 space_used                        out                   number        ,
 space_allocated                   out                   number        ,
 partition_name                     in                 varchar2 default,
 preserve_result                    in                  boolean default,
 timeout_seconds                    in                   number default
);

function object_space_usage_tbf returns dbms_space (
 object_owner                       in                 varchar2        ,
 object_name                        in                 varchar2        ,
 object_type                        in                 varchar2        ,
 sample_control                     in                   number        ,
 partition_name                     in                 varchar2 default,
 preserve_result                    in                 varchar2 default,
 timeout_seconds                    in                   number default
);

procedure parse_space_adv_info (
 info                               in                 varchar2        ,
 used_space                        out                 varchar2        ,
 allocated_space                   out                 varchar2        ,
 reclaimable_space                 out                 varchar2        
);

procedure space_usage (
 segment_owner                      in                 varchar2        ,
 segment_name                       in                 varchar2        ,
 segment_type                       in                 varchar2        ,
 unformatted_blocks                out                   number        ,
 unformatted_bytes                 out                   number        ,
 fs1_blocks                        out                   number        ,
 fs1_bytes                         out                   number        ,
 fs2_blocks                        out                   number        ,
 fs2_bytes                         out                   number        ,
 fs3_blocks                        out                   number        ,
 fs3_bytes                         out                   number        ,
 fs4_blocks                        out                   number        ,
 fs4_bytes                         out                   number        ,
 full_blocks                       out                   number        ,
 full_bytes                        out                   number        ,
 partition_name                     in                 varchar2 default
);

procedure unused_space (
 segment_owner                      in                 varchar2        ,
 segment_name                       in                 varchar2        ,
 segment_type                       in                 varchar2        ,
 total_blocks                      out                   number        ,
 total_bytes                       out                   number        ,
 unused_blocks                     out                   number        ,
 unused_bytes                      out                   number        ,
 last_used_extent_file_id          out                   number        ,
 last_used_extent_block_id         out                   number        ,
 last_used_block                   out                   number        ,
 partition_name                     in                 varchar2 default
);

function verify_shrink_candidate returns boolean (
 segment_owner                      in                 varchar2        ,
 segment_name                       in                 varchar2        ,
 segment_type                       in                 varchar2        ,
 shrink_target_bytes                in                   number        ,
 partition_name                     in                 varchar2 default
);

function verify_shrink_candidate_tbf returns dbms_space (
 segment_owner                      in                 varchar2        ,
 segment_name                       in                 varchar2        ,
 segment_type                       in                 varchar2        ,
 shrink_target_bytes                in                   number        ,
 partition_name                     in                 varchar2 default
);

declare
  segment_name_in  varchar2( 30) := '&1';
  ---
  segment_owner    varchar2( 30);
  segment_name     varchar2( 30);
  segment_type     number;

  part2_dummy      varchar2( 30);
  dblink_dummy     varchar2(128);
  obj_num_dummy    number;

  free__blocks     number;
  total_blocks     number;
  unusedblocks     number;
  last__block      number;

  total_bytes      number;
  unusedbytes      number;
  last_ext_block   number;
  last_ext_file    number;



begin

  dbms_utility.name_resolve(
  --------------------------
    name                    => segment_name_in,
    context                 => 2,
    schema                  => segment_owner,
    part1                   => segment_name,
    part2                   => part2_dummy,
    dblink                  => dblink_dummy,
    part1_type              => segment_type,
    object_number           => obj_num_dummy);


  dbms_space.free_blocks(
  -----------------------
    segment_owner           => segment_owner,
    segment_name            => segment_name,
    segment_type            => case segment_type when 2 then 'TABLE' end,
    freelist_group_id       => 0,              --???
    free_blks               => free__blocks,
    scan_limit              => null,
    partition_name          => null
  );


  dbms_space.unused_space(
  ------------------------
    segment_owner             => segment_owner,
    segment_name              => segment_name,
    segment_type              => case segment_type when 2 then 'TABLE' end,
    total_blocks              => total_blocks,
    total_bytes               => total_bytes,
    unused_blocks             => unusedblocks,
    unused_bytes              => unusedbytes,
    last_used_extent_file_id  => last_ext_file,
    last_used_extent_block_id => last_ext_block,
    last_used_block           => last__block,
    partition_name            => null);


  dbms_output.put_line('  Free blocks: ' || free__blocks   );
  dbms_output.put_line(' Total blocks: ' || total_blocks   );
  dbms_output.put_line('Unused blocks: ' || unusedblocks   );
--dbms_output.put_line('  Last block : ' || last__block    );

end;
/