1、表空间和表都使用oracle默认参数 查看scott用户默认表空间：
1 2 3 4 select username,default_tablespace from dba_users where username='SCOTT'; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ SCOTT USERS
查看users表空间初始化extent大小：
1 2 3 4 5 select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE from user_tablespaces where tablespace_name='USERS'; TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS EXTENT_MAN ALLOCATIO ------------------------------ ---------- -------------- ----------- ----------- ----------- ---------- --------- USERS 8192 65536 1 2147483645 LOCAL SYSTEM
查看当前数据库块大小：
1 2 3 4 5 show parameter db_block_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
创建测试表：
1 2 3 4 5 11:43:49 SCOTT@ boston> create table t1 as select * from emp where 0=1; Table created. 表数据为空，查看是否有extent区在： 11:43:54 SYS@ boston> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T1'; no rows selected
向t1表内插入数据：
1 2 11:43:50 SCOTT@ boston> insert into t1 select * from emp; 14 rows created.
查看已分配出一个8个block的extent：
1 2 3 4 5 6 11:43:55 SYS@ boston> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T1'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T1 0 4 176 8 Elapsed: 00:00:00.50
因数据量太小，只分配了一个区，需插入大量数据：
1 2 3 4 5 6 12:00:40 SCOTT@ boston> insert into t1 select * from t1; 42 rows created. ... 12:00:57 SCOTT@ boston> / 2688 rows created. Elapsed: 00:00:00.02
再次查看extent，当分配16个extent之后，数据量达到了168 8/1024=1M，当达到1m之后，在分配下一个extent会直接分配128个块，大小分为128*8/1024=1M：
1 2 3 4 5 6 7 8 12:01:01 SYS@ boston> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T1'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T1 0 4 176 8 ... T1 15 4 296 8 T1 16 4 384 128
再次插入数据：
1 2 3 4 5 6 12:28:36 SCOTT@ boston> / 5376 rows created. Elapsed: 00:00:00.02 .... 12:30:11 SCOTT@ boston> / 688128 rows created.
查看extent情况：
1 2 3 4 5 6 7 8 9 10 11 12:30:14 SYS@ boston>select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T1'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T1 0 4 176 8 .... T1 15 4 296 8 T1 16 4 384 128 ... T1 78 4 8320 128 T1 79 4 8448 1024 80 rows selected.
当表数据量达到64M的时候，再次分配下一个extent会直接分配1024个block，大小为64M。
手动对表t1分配extent
1 2 3 4 5 6 7 8 9 13:35:52 SCOTT@ boston> alter table t1 allocate extent; Table altered. Elapsed: 00:00:00.49 14:18:45 SCOTT@ boston> / Table altered. Elapsed: 00:00:00.07 14:19:28 SCOTT@ boston> / Table altered. Elapsed: 00:00:00.06
查看extent情况：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 14:19:06 SYS@ boston> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T1'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T1 0 4 176 8 ... T1 15 4 296 8 T1 16 4 384 128 ... T1 78 4 8320 128 T1 79 4 8448 1024 T1 80 4 9472 128 T1 81 4 9600 128 T1 82 4 9728 128 83 rows selected. Elapsed: 00:00:00.45
使用alter table ** allocate extent手动分配的区大小为1M。
再次对表插入数据：
1 2 3 14:19:30 SCOTT@ boston> insert into t1 select * from t1; 1376256 rows created. Elapsed: 00:00:12.03
查看extent情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 14:20:34 SYS@ boston> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T1'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T1 0 4 176 8 ... T1 15 4 296 8 T1 16 4 384 128 ... T1 78 4 8320 128 T1 79 4 8448 1024 T1 80 4 9472 128 T1 81 4 9600 128 T1 82 4 9728 128 T1 83 4 9856 1024 ... T1 88 4 14976 1024 89 rows selected.
插入数据分配的extent大小均为64M。
2、表空间空间使用默认参数，表参数自定义（INITIAL参数小于65536）： 创建测试表：
1 2 3 4 5 6 create table t2 as select * from emp where 0=1; Table created. ALTER TABLE t2 MOVE STORAGE ( INITIAL 20k NEXT 40k) TABLESPACE users; Table altered. Elapsed: 00:00:00.23
查看表参数：
1 2 3 4 SELECT initial_extent, next_extent, min_extents, max_extents, pct_increase, blocks, sample_size FROM user_tables WHERE table_name = 'T2'; INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE BLOCKS SAMPLE_SIZE -------------- ----------- ----------- ----------- ------------ ---------- ----------- 24576 40960
表数据为空，查看是否有extent区在：
1 2 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T2'; no rows selected
向表空插入数据
1 2 insert into t2 select * from emp; insert into t2 select * from t2;
查看extent情况:
1 2 3 4 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T2'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T2 0 4 168 8
分配出来的extent最少8个块。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T2'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T2 0 4 168 8 ... T2 15 4 17184 8 T2 16 4 17280 128 ... T2 78 4 7936 128 T2 79 4 8064 1024 T2 80 4 9088 1024 T2 81 4 10112 1024 T2 82 4 11136 1024
extent分配增长情况为（64k-1m-64m-…)。
3、表空间空间使用默认参数，表参数自定义（INITIAL参数大于64k，next小于64k）： 创建测试表：
1 2 3 4 5 6 create table t3 as select * from emp where 0=1; Table created. ALTER TABLE t3 MOVE STORAGE ( INITIAL 100k NEXT 40k) TABLESPACE users; Table altered. Elapsed: 00:00:00.23
查看表参数：
1 2 3 4 5 SELECT initial_extent, next_extent, min_extents, max_extents, pct_increase, blocks, sample_size FROM user_tables WHERE table_name = 'T3'; INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE BLOCKS SAMPLE_SIZE -------------- ----------- ----------- ----------- ------------ ---------- ----------- 106496 40960
表数据为空，查看是否有extent区在：
1 2 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T3'; no rows selected
向表空插入数据
1 insert into t3 select * from emp;
一次分配出来两个extent，每个extent8个block：
1 2 3 4 5 6 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T3'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T3 0 4 176 8 T3 1 4 184 8
再次插入数据：
1 insert into t3 select * from t3;
不要插入太多数据，查看下一次分配的extent为1个，占8个块
1 2 3 4 5 6 7 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T3'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T3 0 4 208 8 T3 1 4 216 8 T3 2 4 224 8
再次插入数据，extent增长情况（64k-1m-64m）
4、表空间空间使用默认参数，表参数自定义（INITIAL参数大于64k，next大于64k）： 创建测试表：
1 2 3 4 5 6 create table t4 as select * from emp where 0=1; Table created. ALTER TABLE t4 MOVE STORAGE ( INITIAL 100k NEXT 140k) TABLESPACE users; Table altered. Elapsed: 00:00:00.23
查看表参数：
1 2 3 4 5 14:31:43 SCOTT@ boston> SELECT initial_extent, next_extent, min_extents, max_extents, pct_increase, blocks, sample_size FROM user_tables WHERE table_name = 'T4'; INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE BLOCKS SAMPLE_SIZE -------------- ----------- ----------- ----------- ------------ ---------- ----------- 106496 147456
表数据为空，查看是否有extent区在：
1 2 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T4'; no rows selected
向表空插入数据
1 insert into t4 select * from emp;
一次分配出来两个extent，每个extent8个block：
1 2 3 4 5 6 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T4'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T4 0 4 176 8 T4 1 4 184 8
再次插入数据：
1 insert into t4 select * from t4;
不要插入太多数据，查看下一次分配的extent为1个，占8个块
1 2 3 4 5 6 7 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T4'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T4 0 4 176 8 T4 1 4 184 8 T4 2 4 192 8
再次插入数据，extent增长情况（64k-1m-64m）
5、创建表空间使用并设置extent大小，统一为2m： 创建测试表空间
1 create tablespace test datafile '/data/u01/app/oracle/oradata/boston/test01.dbf' size 100M extent management local uniform size 2m;
查看表空间参数
1 2 3 4 5 15:28:39 SYS@ boston> select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,EXTENT_MANAGEMENT,ALLOCATION_TYPE from user_tablespaces where tablespace_name='TEST'; TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS EXTENT_MAN ALLOCATIO ------------------------------ ---------- -------------- ----------- ----------- ----------- ---------- --------- TEST 8192 2097152 2097152 1 2147483645 LOCAL UNIFORM
6、表空间使用上面创建的test表空间，表参数默认： 创建测试表：
1 2 3 drop table t1; create table t1 as select * from emp where 0=1; alter table t1 move tablespace test;
查看表t1参数
1 2 3 4 5 SELECT initial_extent,next_extent,min_extents,max_extents,pct_increase,blocks,sample_size FROM user_tables WHERE table_name = 'T1'; INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE BLOCKS SAMPLE_SIZE -------------- ----------- ----------- ----------- ------------ ---------- ----------- 2097152 2097152 1 2147483645 0
表数据为空，查看是否有extent区在：
1 2 3 4 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T1'; 15:32:51 SYS@ boston> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T1'; no rows selected Elapsed: 00:00:00.04
插入数据
1 2 15:33:16 SCOTT@ boston> insert into t1 select * from emp; 14 rows created.
为t1表分配了一个2m大小的extent：
1 2 3 4 15:32:54 SYS@ boston> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T1'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T1 0 5 128 256
批量插入数据
1 2 3 4 5 6 7 8 15:48:07 SYS@ boston> select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T1'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T1 0 5 128 256 ... T1 80 5 20608 256 81 rows selected.
不管插入多少数据，因为设置了统一大小为2m，一个extent永远都是256个块。
7、表空间使用上面创建的test表空间，表参数自定义： 创建测试表：
1 2 3 drop table t2; create table t2 as select * from emp where 0=1; ALTER TABLE t2 MOVE STORAGE ( INITIAL 5m NEXT 5m) TABLESPACE test;
查看表t2参数
1 2 3 4 5 SELECT initial_extent,next_extent,min_extents,max_extents,pct_increase,blocks,sample_size FROM user_tables WHERE table_name = 'T2'; INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE BLOCKS SAMPLE_SIZE -------------- ----------- ----------- ----------- ------------ ---------- ----------- 5242880 5242880
表数据为空，查看是否有extent区在：
1 2 3 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T2'; no rows selected Elapsed: 00:00:00.11
插入数据：
1 insert into t2 select * from emp;
因为初始化设置为5m，而每个extent大小统一为2m，因此分配出三个extent：
1 2 3 4 5 6 7 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T2'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T2 0 5 1920 256 T2 1 5 2176 256 T2 2 5 2432 256
再次插入数据，一次性不要插入太多：
1 2 3 insert into t2 select * from t2; ... insert into t2 (select * from t2 where rownum < 10000);
当前三个extent用满之后再次分配extent时，分配1个extent，大小为2m。
1 2 3 4 5 6 7 select SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where OWNER='SCOTT' AND SEGMENT_NAME='T2'; SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS --------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- T2 0 5 640 256 T2 1 5 896 256 T2 2 5 1152 256 T2 3 5 1408 256
8、测试总结 在本地管理表空间的模式下，oracle分配extent，会根据initial指定的参数分配（按相应的倍数），但是next指定的值会被忽略。官方文档如下所示：
