Specifying the Default Tablespace Type (78)

The SET DEFAULT...TABLESPACE clause of the CREATE DATABASE statement to
determines the default type of tablespace for this database in subsequent CREATE
TABLESPACE statements. Specify either SET DEFAULT BIGFILE TABLESPACE or
SET DEFAULT SMALLFILE TABLESPACE. If you omit this clause, the default is a
smallfile tablespace, which is the traditional type of Oracle Database tablespace. A
smallfile tablespace can contain up to 1022 files with up to 4M blocks each.

The use of bigfile tablespaces further enhances the Oracle-managed files feature,
because bigfile tablespaces make datafiles completely transparent for users. SQL
syntax for the ALTER TABLESPACE statement has been extended to allow you to
perform operations on tablespaces, rather than the underlying datafiles.

To dynamically change the default tablespace type after database creation, use the SET
DEFAULT TABLESPACE clause of the ALTER DATABASE statement:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

You can determine the current default tablespace type for the database by querying the
DATABASE_PROPERTIES data dictionary view as follows:

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TBS_TYPE';

指定預設的表空間檔案型別
1. 可利用 set defaukt tablespace 來指定預設的表空間檔案型別
2. 大檔案表空間增強了OMF特性 , 使資料檔案完全透明於使用者
3. 資料庫建立後 ,可利用 ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;
來改變預設的表空間檔案型別
4. 透過SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TBS_TYPE';
可查詢當前預設的表空間檔案型別

[@more@]