PCTFREE 指数据块中保留的空间以便更新现有的行。所以对于update造成的行变长而言更大的PCTFREE是更亲和的，更少造成migrated rows的。这个参数是一个百分比，代表块大小的百分比。

 

在块中剩余空间达到PCTFREE指定的百分比之前，这些空闲空间可以被插入行数据和block header的增长、以及UPDATE造成的行变长等等操作所用。  换而言之当剩余空间等于或者小于PCTFREE时，这些空闲空间不会再为插入所用了，而只是给block header的增长、以及UPDATE造成的行变长等等操作所用。注意在ASSM下 当剩余空间等于或者小于PCTFREE时  数据块就被 bitmap block标记为 FULL了，而不是真的”FULL”了。

PCTUSED参数(ASSM Automatic Segment Space Management下被废弃，仅仅MSSM下具有效果) 决定数据块在达到PCTFREE而被标记为FULL后，当数据块中被使用的比例下降到比PCTUSED(其实也是百分比)更小时，这个数据块被认为又可用来插入新的行数据了，这是PCTUSED的作用。

 

由于当数据被插入到数据块中时，行数据时从块的底部往上存储的。 而块头block header所占的空间又是浮动的，这是由于interested transaction lists (ITLs)也是要占用空间的，而一个块中ITL的数量是变化的，这种空间使用时从块的头部往下扩展的。  设置一个较大的INITRANS初始ITL数据将为块头保留更多的空间，这是因为一开始就会创建空的ITL(对于已有的块要MOVE才生效)，但空的ITL已经占了地方了，所以别人就占不走了， 但是这样也会造成每个块中可用的空间减少。  DBA需要自行在 并发需要和可用空间上权衡利弊。

From askmac.cn

需要注意 ， 这2个参数在MSSM和ASSM 数据段管理方式下的不同。

在MSSM freelist-managed模式下，当PCTFREE指定的值被达到后，由于该块会从Freelist上被移除所以不会有新的数据被插入到该块中。 但余下来的所有空间均只能用来更新现有的行。 DELETE删除显然会释放块上的空间，当DELETE和UPDATE(指让行缩小的UPDATE)营造出更多的空闲空间，以致于被使用的空间已经小于PCTUSED了，那么此时这个数据块又会被放到FREELIST上，这之后用户又可以插入数据到该块中。

还需要注意，PCTUSED在ASSM段上是不生效的。

 

 

 

 

create or replace
package dbms_space_admin is
————
— OVERVIEW
—
— This package provides tablespace/segment space administration
— not available through the standard sql.

— SECURITY
—
— run with SYS privileges. thus any user who has privilege to execute the
— the package gets to manipulate the bitmapes.

— CONSTANTS to be used as OPTIONS for various procedures
— refer comments with procedure(s) for more detail

SEGMENT_VERIFY_EXTENTS constant positive := 1;
— used to verify that the space owned by segment is appropriately reflected
— in the bitmap as used
SEGMENT_VERIFY_EXTENTS_GLOBAL constant positive := 2;
— used to verify that the space owned bu segment is appropriately reflected
— in the bitmap as used and that no other segment claims any of this space
— to be used by it
SEGMENT_MARK_CORRUPT constant positive := 3;
— used to mark a temp segment as corrupt whereby facilitating its
— elimination from the dictionary (without space reclaim)
SEGMENT_MARK_VALID constant positive := 4;
— used to mark a corrupt temp segment as valid. Useful when the corruption
— in the segment extent map or elsewhere has been resolved and the segment
— can be dropped normally
SEGMENT_DUMP_EXTENT_MAP constant positive := 5;
— dump the extent map for a given segment
TABLESPACE_VERIFY_BITMAP constant positive := 6;
— verifies the bitmap of the tablespace with extent maps of the segments
— in that tablespace to make sure everything is consistent
TABLESPACE_EXTENT_MAKE_FREE constant positive := 7;
— makes this range (extent) of space free in the bitmaps
TABLESPACE_EXTENT_MAKE_USED constant positive := 8;
— makes this range (extent) of space used in the bitmaps

SEGMENT_VERIFY_BASIC constant positive := 9;
SEGMENT_VERIFY_DEEP constant positive := 10;
SEGMENT_VERIFY_SPECIFIC constant positive := 11;
HWM_CHECK constant positive := 12;
BMB_CHECK constant positive := 13;
SEG_DICT_CHECK constant positive := 14;
EXTENT_TS_BITMAP_CHECK constant positive := 15;
DB_BACKPOINTER_CHECK constant positive := 16;
EXTENT_SEGMENT_BITMAP_CHECK constant positive := 17;
BITMAPS_CHECK constant positive := 18;
TS_VERIFY_BITMAPS constant positive := 19;
TS_VERIFY_DEEP constant positive := 20;
TS_VERIFY_SEGMENTS constant positive := 21;

SEGMENT_DUMP_BITMAP_SUMMARY constant positive := 27;

NGLOB_HBB_CHECK constant positive := 12;
NGLOB_FSB_CHECK constant positive := 13;
NGLOB_PUA_CHECK constant positive := 14;
NGLOB_CFS_CHECK constant positive := 15;

 

—————————-
— PROCEDURES AND FUNCTIONS

procedure segment_verify(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
verify_option in positive DEFAULT SEGMENT_VERIFY_EXTENTS
);
—
— Verifies the consistency of the extent map of the segment
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— verify_option – SEGMENT_VERIFY_EXTENTS or
— SEGMENT_VERIFY_EXTENTS_GLOBAL
—
procedure segment_corrupt(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
corrupt_option in positive DEFAULT SEGMENT_MARK_CORRUPT
);
—
— Marks the segment corrupt/valid so that appropriate error recovery
— can be done/skipped. Only for segments already temporary.
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— corrupt_option – SEGMENT_MARK_CORRUPT or
— SEGMENT_MARK_VALID
—
procedure segment_drop_corrupt(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive
);
—
— Drops a segment currently marked corrupt (without reclaiming space)
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
—
procedure segment_dump(
tablespace_name in varchar2 ,
header_relative_file in positive ,
header_block in positive ,
dump_option in positive DEFAULT SEGMENT_DUMP_EXTENT_MAP
);

—
— Dumps the extent map of a given segment
— Input arguments:
— tablespace_name – name of tablespace in which segment resides
— header_relative_file – relative file number of segment segment header
— header_block – block number of segment segment header
— dump_option – SEGMENT_DUMP_EXTENT_MAP
—

procedure tablespace_verify(
tablespace_name in varchar2 ,
verify_option in positive DEFAULT TABLESPACE_VERIFY_BITMAP
);
—
— Verifies that the extent maps and the bitmaps are in sync.
— Input arguments:
— tablespace_name – name of tablespace
— verify_option – TABLESPACE_VERIFY_BITMAP
—
procedure tablespace_fix_bitmaps(
tablespace_name in varchar2 ,
dbarange_relative_file in positive ,
dbarange_begin_block in positive ,
dbarange_end_block in positive ,
fix_option in positive
);
—
— Marks the appropriate dba range (extent) as free/used in bitmap
— Input arguments:
— tablespace_name – name of tablespace
— dbarange_relative_file – relative fileno of dba range (extent)
— dbarange_begin_block – block number of beginning of extent
— dbarange_end_block – block number (inclusive) of end of extent
— fix_option – TABLESPACE_EXTENT_MAKE_FREE or
— TABLESPACE_EXTENT_MAKE_USED
—
procedure tablespace_rebuild_bitmaps(
tablespace_name in varchar2 ,
bitmap_relative_file in positive DEFAULT NULL,
bitmap_block in positive DEFAULT NULL
);
—
— Rebuilds the appropriate bitmap(s). If no bitmap block dba specified then
— rebuilds all bitmaps for the given tablespace
— Input arguments:
— tablespace_name – name of tablespace
— bitmap_relative_file – relative file number of bitmap block to rebuild
— bitmap_block – block number of bitmap block to rebuild
—

—
procedure tablespace_rebuild_quotas(
tablespace_name in varchar2
);
—
— Rebuilds quotas for given tablespace
— Input arguments:
— tablespace_name – name of tablespace
—

—
procedure tablespace_migrate_from_local(
tablespace_name in varchar2
);
—
— Migrates a locally-managed tablespace to dictionary managed
— Input arguments:
— tablespace_name – name of tablespace

—
procedure tablespace_migrate_to_local(
tablespace_name in varchar2,
unit_size in positive DEFAULT NULL,
rfno in positive DEFAULT NULL
);
—
— Migrates a dictionary-managed tablespace to locally managed
— Input arguments:
— tablespace_name – name of tablespace
— unit_size – bitmap unit size for the tablespace
—

—
procedure tablespace_relocate_bitmaps(
tablespace_name in varchar2,
filno in positive,
blkno in positive);

—
— Tablespace relocate bitmaps to a different location
— Input arguments:
— tablespace_name – name of tablespace
—

—
procedure tablespace_fix_segment_states(
tablespace_name in varchar2);

—
— Tablespace fix segment states. During migration of tablespaces
— the segments are put in a transition state. If migration fails,
— the state of the segments can be corrected by calling this
— procedure. This procedure should be called if migration failed
— and the user tries to run in an incompatible mode.

—
procedure tablespace_fix_segment_extblks(
tablespace_name in varchar2);

—
— Tablespace fix segment extents and blocks based on segment
— header entries

—
procedure tablespace_dump_bitmaps(
tablespace_name in varchar2);

—
— Tablespace space header of files

function segment_number_extents(
header_tablespace_id in natural ,
header_relative_file in positive ,
header_block in positive ,
segment_type in positive ,
buffer_pool_id in natural ,
dictionary_flags in natural ,
data_object_id in number,
dictionary_extents in number
) return pls_integer;
pragma RESTRICT_REFERENCES(segment_number_extents,WNDS,WNPS,RNPS);
—
— Returns the number of extents which belong to the segment. Will return
— NULL if segment has disappeared. IS NOT to be used for any other
— purposes but by the views which need it and are sure that there info
— is correct. Else internal errors will abound
—
function segment_number_blocks(
header_tablespace_id in natural ,
header_relative_file in positive ,
header_block in positive ,
segment_type in positive ,
buffer_pool_id in natural ,
dictionary_flags in natural ,
data_object_id in number,
dictionary_blocks in number
) return pls_integer;
pragma RESTRICT_REFERENCES(segment_number_blocks,WNDS,WNPS,RNPS);
—
— Returns the number of blocks which belong to the segment. Will return
— NULL if segment has disappeared. IS NOT to be used for any other
— purposes but by the views which need it and are sure that there info
— is correct. Else internal errors will abound
—
procedure segment_moveblocks(
segment_owner in varchar2,
segment_name in varchar2,
partition_name in varchar2,
segment_type in varchar2,
group_id in natural,
minimum_size in positive,
move_count in positive,
pause_time in natural,
iterations in positive
);
—
— Moves blocks from the segment header to the process freelist
— It only moves blocks if the list is shorter than the minimum
— size for the move_count blocks
— segment_owner – name of the object’s owner
— segment_name – name of the object
— partition_name – name of the partition (NULL if not partitioned)
— segment_type – object type (TABLE, INDEX, etc – see DBMS_SPACE)
— group_id – freelist group (0 for the segment header)
— minimum_size – do not move if process free list is longer
— move_count – move up to this number of blocks
— pause_time – pause between loop iterations
— iterations – number of iterations (infinite if NULL)
—

 

procedure assm_segment_verify(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2,
verify_option in positive default SEGMENT_VERIFY_BASIC ,
attrib in positive default NULL);

—

— Verifies the consistency of the segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— verify_option – one of SEGMENT_VERIFY_BASIC, DEEP , SPECIFIC
— attrib – used when option SEGMENT_VERIFY_SPECIFIC
procedure nglob_segment_verify(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2,
verify_option in positive default SEGMENT_VERIFY_BASIC ,
attrib in positive default NULL);

—

— Verifies the consistency of the segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— verify_option – one of SEGMENT_VERIFY_BASIC, DEEP , SPECIFIC
— attrib – used when option SEGMENT_VERIFY_SPECIFIC

procedure assm_tablespace_verify(
tablespace_name in varchar2,
ts_option in positive,
segment_option in positive default NULL);

—
— Verifies that the tablespace consistency.
— Input arguments:
— tablespace_name – name of tablespace
— ts_option – TS_VERIFY_BITMAPS, TS_VERIFY_DEEP, TS_VERIFY_SEGMENTS
— segment option – used when TS_VERIFY_SEGMENTS, one of SEGMENT_VERIFY_DEEP, SPECIFIC
—
function assm_segment_synchwm(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
check_only in number default 1
) return pls_integer;

—
— Synchronize HWMs of the ASSM segment
— Input arguments:
— segment_owner – owner
— segment_name – name of the segment
— segment_type – type of segment
— partition_name – name of partition default NULL
— check_only – whether it is check only default YES
— Output:
— Return TRUE if the segment requires HWM synchronization
— Return FALSE otherwise

procedure flush_lobsegment_stats;

procedure purge_lobsegment_stats;
————————————————————————-
— PROCEDURE securefile_segment_repair
————————————————————————-

— segment repair option: repair metadata blocks
SEGMENT_REPAIR_METADATA constant positive := 1;

procedure segment_repair(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
repair_option in number default SEGMENT_REPAIR_METADATA
);

—
— Description:
— Repair SecureFile segment
— Input parameters:
— segment_owner – owner
— segment_name – name of the LOB segment
— segment_type – type of segment
— values: ‘LOB’, ‘LOB PARTITION’, ‘LOB SUBPARTITION’
— partition_name – name of the LON partition segment
— default: NULL
— repair_option – segment repair option,
— values: see SEGMENT_REPAIR_*** definitions
— default: SEGMENT_REPAIR_METADATA
— Note:
— This function is only used internally and does not require
— documentation.
————————————————————————-
— PROCEDURE segment_extend
————————————————————————-

procedure segment_extend(
segment_owner in varchar2,
segment_name in varchar2,
segment_type in varchar2,
partition_name in varchar2 default NULL,
target_size in number default 1
);

—
— Description:
— SecureFile segment extend in background
— Input parameters:
— segment_owner – owner
— segment_name – name of the LOB segment
— segment_type – type of segment
— values: ‘LOB’, ‘LOB PARTITION’, ‘LOB SUBPARTITION’
— partition_name – name of the LOB partition segment
— default: NULL
— target_size – segment target size in GB
— Note:
— This function is only used internally and does not require
— documentation.
————————————————————————-
— PROCEDURE drop_empty_segments
————————————————————————-

procedure drop_empty_segments(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL
);

—
— Description:
— Drop segments from empty table(s)/table fragments and dependent
— objects.
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— Note:
— Given a schema name, this procedure scans all tables in the schema
— For each table, if the table or any of its fragments are found to be
— empty, and the table satisfies certain criteria [restrictions being
— the same as those imposed by segment creation on demand], the empty
— fragments and associated index segments are dropped. A subsequent
— insert will create segments with the same properties.
— Optionally,
— a. no schema name may be specified in which case we would scan
— tables belonging to all schemas
— b. both schema_name and table_name may be specified to do this
— operation on one particular table
— c. all three arguments may be supplied, in which case we will
— restrict this operation to that partition and its dependent
— objects.
————————————————————————-
— PROCEDURE materialize_deferred_segments
————————————————————————-

procedure materialize_deferred_segments(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL
);

—
— Description:
— Materialize segments for tables/table fragments with deferred
— segment creation (and their dependent objects)
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— Note:
— Given a schema name, this procedure scans all tables in the schema.
— For each table, if the deferred/delayed segment property is set for
— the table or any of its fragments, a new segment is created for
— those fragments and their dependent objects.
— Optionally,
— a. no schema name may be specified in which case we would scan tables
— belonging to all schemas
— b. both schema_name and table_name may be specified to do this
— operation on one particular table
— c. all three arguments may be supplied, in which case we will
— restrict this operation to that partition and its dependent
— objects
————————————————————————-
— PROCEDURE materialize_deferred_with_opt
————————————————————————-

procedure materialize_deferred_with_opt(
schema_name in varchar2 default NULL,
table_name in varchar2 default NULL,
partition_name in varchar2 default NULL,
partitioned_only in boolean default FALSE
);

—
— Description:
— Materialize segments for tables/table fragments with deferred
— segment creation (and their dependent objects), with an additional
— option.
— Input parameters:
— schema_name – schema name, default: NULL
— table_name – table name, default: NULL
— partition_name – partition name, default: NULL
— partitioned_only – apply materialize procedure on partitioned
— tables only, default: FALSE
— Note:
— The materialize_deferred_segments procedure is a wrapper around
— this. This procedure is required for downgrading (from 11.2.0.2)
— to materialize segments for partitioned tables only. The
— partitioned_only argument supports this limited behavior.
————————————————————————-
— PROCEDURE tablespace_fix_affinity
————————————————————————-
procedure tablespace_fix_affinity(
tablespace_name in varchar2,
block_num in binary_integer,
instance_id in binary_integer DEFAULT NULL
);

—
— Description:
— This procedure sets instance affinity of a File Bitmap Block
— in bigfile tablespace. The block can be either a Level 1 bitmap
— block (FFB) or a Level 2 bitmap block (FSB).
— The report will be generated in the corresponding trace file of
— the session that runs this procedure.
—
— Input parameters:
— tablespace_name – name of tablespace
— block_num – block number
— instance_id – id of instance to be affined, by default
— it is the current instance
—
— Note:
— (1) If the block is Second-level File Bitmap block (FSB), and if
— there is another FSB that has already been affined to the instance
— specified, this FSB will be freed.
— (2) This function is only used internally and does not require
— documentation.
end;