freelist管理空闲段

关于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 语句来修改这些参数。