统一区大小表空间和系统管理区表空间规则区别

--统一区大小表空间和系统管理区表空间规则区别

--统一区大小表空间

EODA@PROD1> create tablespace tbs_ts1 datafile '/u01/app/oracle/oradata/PROD1/tbs1.dbf' size 50m uniform size 1m;  --创建表空间中子句使用uniform size则是统一区大小
 
Tablespace created.

EODA@PROD1> create table t1 (id varchar2(20)) tablespace tbs_ts1;

Table created.

EODA@PROD1> insert into t1 select 'abc' from all_objects;

72969 rows created.

EODA@PROD1> insert into t1 select 'abc' from all_objects;

72969 rows created.

EODA@PROD1> insert into t1 select 'abc' from all_objects;

72969 rows created.

EODA@PROD1> commit;

Commit complete.

EODA@PROD1> select extent_id, file_id, block_id, blocks from dba_extents where segment_name='T1' order by extent_id;  --所有区都是128个块，本库块大小为8KB，所以区大小就是1MB

 EXTENT_ID    FILE_ID	BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
	 0	   16	     128	128
	 1	   16	     256	128
	 2	   16	     384	128
	 3	   16	     512	128
	 

--系统管理区大小表空间
EODA@PROD1> create tablespace tbs_ts2 datafile '/u01/app/oracle/oradata/PROD1/tbs2.dbf' size 50m reuse;  --不指定uniform则是系统管理

Tablespace created.

EODA@PROD1> create table t2 (id varchar2(20)) tablespace tbs_ts2;

Table created.

EODA@PROD1> insert into t2 select 'abc' from all_objects;

72970 rows created.

EODA@PROD1> insert into t2 select 'abc' from all_objects;

72970 rows created.

EODA@PROD1> insert into t2 select 'abc' from all_objects;

72970 rows created.

EODA@PROD1> commit;                                       

Commit complete.

EODA@PROD1> select extent_id, file_id, block_id, blocks from dba_extents where segment_name='T2' order by extent_id;  --可以看到前16个区大小为8个块，即64KB，第16个区增大为区大小1MB，如果表进一步扩大，区大小会变成8MB，以此往复。

 EXTENT_ID    FILE_ID	BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
	 0	   17	     128	  8
	 1	   17	     136	  8
	 2	   17	     144	  8
	 3	   17	     152	  8
	 4	   17	     160	  8
	 5	   17	     168	  8
	 6	   17	     176	  8
	 7	   17	     184	  8
	 8	   17	     192	  8
	 9	   17	     200	  8
	10	   17	     208	  8
	11	   17	     216	  8
	12	   17	     224	  8
	13	   17	     232	  8
	14	   17	     240	  8
	15	   17	     248	  8
	16	   17	     256	128
	17	   17	     384	128

18 rows selected.