关于freelists的官方文档： sql reference--------create tablespace------------segment_management_clause sql reference--------create tablespace---------storage_clause------搜索freelist 1、创建测试表空间example sys@prodcreate tablespace exampl
关于freelists的官方文档：
sql reference--------create tablespace------------‘segment_management_clause‘sql reference--------create tablespace---------storage_clause------搜索‘freelist’1、创建测试表空间example
sys@prod>create tablespace example
2 datafile'/u01/app/oracle/oradata/prod/disk1/example_01.dbf'
3 size 400m
4*segment space management manual;
tablespace created.
2、了解关于表空间的数据字典
sys@prod>desc dba_tablespaces;
name null? type
------------------------------------------------- ----------------------------
tablespace_name not nullvarchar2(30)
block_size not null number
initial_extent number
next_extent number
min_extents not null number
max_extents number
pct_increase number
min_extlen number
status varchar2(9)
contents varchar2(9)
logging varchar2(9)
force_logging varchar2(3)
extent_management varchar2(10)
allocation_type varchar2(9)
plugged_in varchar2(3)
segment_space_management varchar2(6)
def_tab_compression varchar2(8)
retention varchar2(11)
bigfile varchar2(3)
sys@prod>desc v$tablespace
name null? type
------------------------------------------------- ----------------------------
ts# number
name varchar2(30)
included_in_database_backup varchar2(3)
bigfile varchar2(3)
flashback_on varchar2(3)
encrypt_in_backup varchar2(3)
3、在测试表空间中创建测试表t（指定freelists 为1）
sys@prod>create table t (x int) storage(freelists 1) tablespace example;
table created.
4、了解与表相关的数据字典：user_tables
sys@prod>desc user_tables;
name null? type
------------------------------------------------- ----------------------------
table_name not nullvarchar2(30)
tablespace_name varchar2(30)
cluster_name varchar2(30)
iot_name varchar2(30)
status varchar2(8)
pct_free number
pct_used number
ini_trans number
max_trans number
initial_extent number
next_extent number
min_extents number
max_extents number
pct_increase number
freelists number
freelist_groups number
logging varchar2(3)
backed_up varchar2(1)
num_rows number
blocks number
empty_blocks number
avg_space number
chain_cnt number
avg_row_len number
avg_space_freelist_blocks number
num_freelist_blocks number
degree varchar2(10)
instances varchar2(10)
cache varchar2(5)
table_lock varchar2(8)
sample_size number
last_analyzed date
partitioned varchar2(3)
iot_type varchar2(12)
temporary varchar2(1)
secondary varchar2(1)
nested varchar2(3)
buffer_pool varchar2(7)
row_movement varchar2(8)
global_stats varchar2(3)
user_stats varchar2(3)
duration varchar2(15)
skip_corrupt varchar2(8)
monitoring varchar2(3)
cluster_owner varchar2(30)
dependencies varchar2(8)
compression varchar2(8)
dropped varchar2(3)
5、查看测试表t是否为freelists管理
sys@prod>select freelists,table_namefrom user_tables where table_name='t';
freelists table_name
---------- ------------------------------
1 t
6、在测试表空间上创建测试表t1（不指定freelists 为1）
sys@prod>create table t1 (x int)tablespace example;
table created.
7、查看测试表t1是否为freelists管理
sys@prod>select freelists,table_namefrom user_tables where table_name='t1';
freeliststable_name
---------- ------------------------------
1 t1
8、找到段空间自动管理的表空间
sys@prod>selecttablespace_name,segment_space_management from dba_tablespaces;
tablespace_name segmen
------------------------------ ------
system manual
undotbs manual
sysaux auto
temp01 manual
example manual
9、从上得知sysaux为段空间自动管理，因此在sysaux上创建测试表t2
sys@prod>create table t2 (x int)tablespace sysaux;
table created.
10、查看测试表t2是否为freelists管理空闲段
sys@prod>select freelists,table_namefrom user_tables where table_name='t2';
freelists table_name
---------- ------------------------------
t2
总结：从上面的实验我们得知，想要让一个表空间freelists管理空闲段，只需表空间为手动管理段空间。且通过官方文档我们得知freelists、freelists_group等参数不可以在表空间创建时指定，也不可以通过alter 语句来修改这些参数。