oracle segment space management and extent management幾則

--segment space mangement有2種方式；auto和manual
SQL> create tablespace tbs_newly datafile 'd:\oracle11g_64bit\oradata\second\tbs
_newly.dbf' size 10m  extent management local autoallocate;

Tablespace created.

SQL> select tablespace_name,segment_space_management,extent_management,allocatio
n_type from dba_tablespaces;

TABLESPACE_NAME      SEGMENT_SPAC EXTENT_MANAGEMENT    ALLOCATION_TYPE
-------------------- ------------ -------------------- ------------------
SYSTEM               MANUAL       LOCAL                SYSTEM
SYSAUX               AUTO         LOCAL                SYSTEM
UNDOTBS1             MANUAL       LOCAL                SYSTEM
TEMP                 MANUAL       LOCAL                UNIFORM
USERS                AUTO         LOCAL                SYSTEM
TBS_NEWLY            AUTO         LOCAL                SYSTEM

6 rows selected.

SQL> drop tablespace tbs_newly including contents and datafiles;

Tablespace dropped.

---extent management 有2種方式：dictionary和local;而local又分為：uniform. size和autoallocate,後者為1m;前者可以自定義
SQL> create tablespace tbs_newly datafile 'd:\oracle11g_64bit\oradata\second\tbs
_newly.dbf' size 10m  extent management local uniform. size 1m;

Tablespace created.

SQL> select tablespace_name,segment_space_management,extent_management,allocatio
n_type from dba_tablespaces;

TABLESPACE_NAME      SEGMENT_SPAC EXTENT_MANAGEMENT    ALLOCATION_TYPE
-------------------- ------------ -------------------- ------------------
SYSTEM               MANUAL       LOCAL                SYSTEM
SYSAUX               AUTO         LOCAL                SYSTEM
UNDOTBS1             MANUAL       LOCAL                SYSTEM
TEMP                 MANUAL       LOCAL                UNIFORM
USERS                AUTO         LOCAL                SYSTEM
TBS_NEWLY            AUTO         LOCAL                UNIFORM

6 rows selected.

SQL>

  1* drop tablespace tbs_newly including contents and datafiles
SQL>
SQL>
SQL>
SQL>
SQL> create tablespace tbs_newly datafile 'd:\oracle11g_64bit\oradata\second\tbs
 _newly.dbf' size 10m  extent management local uniform. size 3m;

Tablespace created.

--說明extent management local uniform. size的大小對應如下的列minextlen,
SQL> select tablespace_name,status,min_extlen from dba_tablespaces;

TABLESPACE_NAME      STATUS             MIN_EXTLEN
-------------------- ------------------ ----------
SYSTEM               ONLINE                  65536
SYSAUX               ONLINE                  65536
UNDOTBS1             ONLINE                  65536
TEMP                 ONLINE                1048576
USERS                ONLINE                  65536
TBS_NEWLY            ONLINE                3145728

6 rows selected.