表空間是oracle數據庫中最大的邏輯組成部分。它在物理上與磁盤上的數據文件相對應（一個表空間由一個或多個數據文件組成，但一個數據文件只能屬于一個表空間）

■查看排序段的使用情況

? SQL> select * from v$sort_segment;
??
■查看使用排序段的會話和用戶信息
? SQL> select * from v$sort_usage;

■查詢數據字典視圖得到撤銷表空間中回退段的信息
? SQL> select * from dba_rollback_segs;

■創建大文件表空間
? SQL> create bigfile tablespace bigfilespace01?
????? datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/bigfilespace01.dbf' size 25G;?????????????????????????????????????
■創建小文件表空間
? SQL> create smallfile tablespace smallfilespace01
????? datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/smallfilespace01.dbf' size 25G;

■創建不同類型的表空間的SQL語句
? - CREATE TABLESPACE??創建(永久表空空)
? - CREATE BIGFILE TABLESPACE?創建大文件表空間
? - CREATE TEMPORARY TABLESPACE 創建臨時表空間
? - CREATE UNDO TABLESPACE????? 創建撤銷表空間

■使用UNIFORM區分配方式的表空間
? SQL> create tablespace myts01
? 2? datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_1.dbf' size 2m?
? 3? uniform size 128k;?????????????????????????????????????????????????????????????????????????????
? 優點:在表空間中不會產生磁盤碎片,可以節約磁盤空間

■使用AUTOALLOCATE區分配方式的表空間
? SQL> create tablespace myts02
? 2? datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts02_1.dbf' size 2m???????????
? 3? autoallocate;?
? 缺點:在表空間中會產生磁盤碎片,進而造成一些磁盤空間的浪費

■使用AUTO段管理方式的表空間
? SQL> create tablespace myts03
? 2? datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts03_1.dbf' size 2m???????
? 3? uniform size 128K?
? 4? segment space management AUTO;

■創建大文件表空間
? SQL> create bigfile tablespace mybigts05
? 2? datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts05_1.dbf' size 2m;
? 該表空間管理方式為本地管理(只有本地管理才支持大文件表空間,最高可達8EB),并且區大小由系統自動分配

■創建臨時表空間
? SQL> create smallfile temporary tablespace mytmpts01?
? 2? tempfile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/mytempts01_1.dbf' size 4m
? 3? extent management local
? 4? uniform size 1k;

■建立大文件臨時表空間
? SQL> create bigfile temporary tablespace mybigtempts01
? 2? tempfile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/mybigtempts01_1.dbf' size 4m
? 3? extent management local
? 4? uniform size 1k;

■創建撤銷表空間
? SQL> CREATE SMALLFILE UNDO TABLESPACE myundots01?????????????
? 2? datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myundots01_1.dbf' size 4m???????????
? 3? /
? 創建UNDO表空間時只能指定DATAFILE和EXTENT MANAGEMENT LOCAL選項,而不能指定其他選項,否則會有錯誤提示.
? UNDO表空間采用本地管理方式,區的大小自動分配.

■向表空間中添加數據文件
? SQL> alter tablespace myts01
? 2? add datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_2.dbf' size 3m;??????????? ?????????????

■向臨時表空間添加數據文件
? SQL> alter tablespace mytmpts01?
? 2? add tempfile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/mytmpts01_2.dbf' size 4m;

■改變數據文件
? SQL> alter database
? 2? datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_2.dbf'?
? 3? resize 4m;???????? ??????????????????

■充許數據文件自動擴展
? SQL> alter database?
? 2? datafile '/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_2.dbf'??
? 3? autoextend on next 1m maxsize 20m;?????（也可以不指定最大擴展值） ??????

■使表空間脫機
? SQL> alter tablespace myts01 offline;

■使表空間聯機
? SQL> alter tablespace myts01 offline;

■使表空間只讀?
? SQL> alter tablespace myts01 read only;

? 處于只讀狀態時,仍然充許執行DROP操作刪除該表空間上的對像,例如:
? SQL> drop table personnel;?????????????????????????????????????????????????????????????????????????????????????????
? 表已刪除。

■使表空間可讀寫
? SQL> alter tablespace myts01 read write;

■修改表空間的名稱
? SQL> alter tablespace myts03 rename to myts03_new;
? 提示:修改表空間名稱是Oracle 10g特有的,在此之前不能實現該功能.

■設置數據庫默認表空間
? SQL> alter database default tablespace myts01;

■設置數據庫默認臨時表空間?
? SQL> alter database default temporary tablespace mytmpts01;

■刪除myts03表空間,包括該表空間中的內容
? SQL> drop tablespace myts03 including contents;
? 這條語句只會從數據字典中刪除表空間信息,而其相關的數據文件仍在于磁盤上

■刪除myts03表空間,包括該表空間中的內容及數據
? SQL> drop tablespace myts04 including contents and datafiles;
? 刪除表空間的同時刪除與它相關的數據文件

■獲得數據庫中各個表空間的名稱、區管理方式、區分配方式、段管理方式、類型等信息
SQL> select tablespace_name,extent_management,allocation_type,segment_space_management,contents from dba_tablespaces;??????????????

TABLESPACE_NAME??????????????? EXTENT_MAN ALLOCATIO SEGMEN CONTENTS
------------------------------ ---------- --------- ------ ---------
SYSTEM???????????????????????? LOCAL????? SYSTEM??? MANUAL PERMANENT
UNDOTBS1?????????????????????? LOCAL????? SYSTEM??? MANUAL UNDO
SYSAUX???????????????????????? LOCAL????? SYSTEM??? AUTO?? PERMANENT
TEMP?????????????????????????? LOCAL????? UNIFORM?? MANUAL TEMPORARY
USERS????????????????????????? LOCAL????? SYSTEM??? AUTO?? PERMANENT
BIGFILESPACE01???????????????? LOCAL????? SYSTEM??? AUTO?? PERMANENT
SMALLFILESPACE01?????????????? LOCAL????? SYSTEM??? MANUAL PERMANENT
MYTS01???????????????????????? LOCAL????? UNIFORM?? MANUAL PERMANENT
MYTS02???????????????????????? LOCAL????? SYSTEM??? MANUAL PERMANENT
MYBIGTS05????????????????????? LOCAL????? SYSTEM??? AUTO?? PERMANENT
MYBIGTEMPTS01????????????????? LOCAL????? UNIFORM?? MANUAL TEMPORARY
MYUNDOTS01???????????????????? LOCAL????? SYSTEM??? MANUAL UNDO
MYTMPTS01????????????????????? LOCAL????? UNIFORM?? MANUAL TEMPORARY?

■查詢數據庫中所有表空間的名稱和默認的存儲參數
SQL> select tablespace_name,initial_extent,next_extent,min_extents,max_extents,pct_increase from dba_tablespaces;?????????????
TABLESPACE_NAME??????????????? INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
------------------------------ -------------- ----------- ----------- ----------- ------------
SYSTEM????????????????????????????????? 65536?????????????????????? 1? 2147483645
UNDOTBS1??????????????????????????????? 65536?????????????????????? 1? 2147483645
SYSAUX????????????????????????????????? 65536?????????????????????? 1? 2147483645
TEMP????????????????????????????????? 1048576???? 1048576?????????? 1??????????????????????? 0
USERS?????????????????????????????????? 65536?????????????????????? 1? 2147483645
BIGFILESPACE01????????????????????????? 65536?????????????????????? 1? 2147483645
SMALLFILESPACE01??????????????????????? 65536?????????????????????? 1? 2147483645
MYTS01???????????????????????????????? 131072????? 131072?????????? 1? 2147483645??????????? 0
MYTS02????????????????????????????????? 65536?????????????????????? 1? 2147483645
MYBIGTS05?????????????????????????????? 65536?????????????????????? 1? 2147483645
MYBIGTEMPTS01?????????????????????????? 16384?????? 16384?????????? 1??????????????????????? 0
MYUNDOTS01????????????????????????????? 65536?????????????????????? 1? 2147483645
MYTMPTS01?????????????????????????????? 16384?????? 16384?????????? 1??????????????????????? 0

■查詢數據庫中數據文件的位置、名稱、大小及其所屬的表空間
SQL> col file_name for a75?
SQL> select file_name,blocks,tablespace_name from dba_data_files;???????????????????????????????????????????????????????????????????
FILE_NAME?????????????????????????????????????????????????????????????????????? BLOCKS TABLESPACE_NAME
--------------------------------------------------------------------------- ---------- ------------------
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/users01.dbf???????????????????? 640 USERS
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/sysaux01.dbf????????????????? 35840 SYSAUX
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/undotbs01.dbf????????????????? 3200 UNDOTBS1
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/system01.dbf????????????????? 57600 SYSTEM
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/bigfilespace01.dbf???????????? 3200 BIGFILESPACE01
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/smallfilespace01.dbf?????????? 3200 SMALLFILESPACE01
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_1.dbf??????????????????? 256 MYTS01
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts02_1.dbf??????????????????? 256 MYTS02
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts05_1.dbf??????????????????? 256 MYBIGTS05
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myundots01_1.dbf??????????????? 512 MYUNDOTS01
/export/home/opt/product/10.1.0.3.0/oradata/ldfsys/myts01_2.dbf??????????????????? 512 MYTS01

■生成數據庫中每個表空間的空閑盤區的信息
SQL> select tablespace_name,file_id,max(blocks) maximum,?
???? count(*) pieces,
???? min(blocks) minimum,??
???? avg(blocks) average,??
???? sum(blocks) total???????????
???? from dba_free_space??????????????
???? group by tablespace_name,file_id;?????????????

TABLESPACE_NAME?????????????????? FILE_ID??? MAXIMUM???? PIECES??? MINIMUM??? AVERAGE????? TOTAL
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
USERS?????????????????????????????????? 4??????? 584????????? 1??????? 584??????? 584??????? 584
MYTS01????????????????????????????????? 7??????? 208????????? 2???????? 16??????? 112??????? 224
MYTS01???????????????????????????????? 12??????? 496????????? 1??????? 496??????? 496??????? 496
MYTS02????????????????????????????????? 8??????? 248????????? 1??????? 248??????? 248??????? 248
SYSAUX????????????????????????????????? 3??????? 248???????? 19????????? 8 45.0526316??????? 856
SYSTEM????????????????????????????????? 1?????? 1272????????? 1?????? 1272?????? 1272?????? 1272
UNDOTBS1??????????????????????????????? 2?????? 1656???????? 11????????? 8 204.363636?????? 2248
MYBIGTS05????????????????????????????? 10??????? 240????????? 1??????? 240??????? 240??????? 240
MYUNDOTS01???????????????????????????? 11??????? 344????????? 1??????? 344??????? 344??????? 344
BIGFILESPACE01????????????????????????? 5?????? 3184????????? 1?????? 3184?????? 3184?????? 3184
SMALLFILESPACE01??????????????????????? 6?????? 3192????????? 1?????? 3192?????? 3192?????? 3192

pices列顯示該表空間文件中的空閑空間盤區數；
maximun和minimum列顯示數據庫塊中最大和最小的空間區域；
average列顯示一個空閑空間盤區以塊計的平均大小；
total列顯示每個表空間文件中空閑空間盤區以塊計的數量

■查詢表空間的空閑空間大小
SQL> select tablespace_name,sum(bytes) free_space from dba_free_space?????????
? 2? group by tablespace_name;?????????????????

TABLESPACE_NAME??????????????? FREE_SPACE
------------------------------ ----------
BIGFILESPACE01?????????????????? 26083328
MYBIGTS05???????????????????????? 1966080
MYTS01??????????????????????????? 5898240
MYTS02??????????????????????????? 2031616
MYUNDOTS01??????????????????????? 2818048
SMALLFILESPACE01???????????????? 26148864
SYSAUX??????????????????????????? 7012352
SYSTEM?????????????????????????? 10420224
UNDOTBS1???????????????????????? 18415616
USERS???????????????????????????? 4784128

■創建臨時表空間組
? SQL> alter tablespace mybigtempts01 tablespace group mytmptsgp01;?
? SQL> alter tablespace mytmpts01 tablespace group mytmptsgp01;

? 必須先創建臨時表空間,然后才能創建臨時表空間組.
? SQL> ALTER TABLESPACE 臨時表空間名 TABLESPACE GROUP 臨時表空間組名

■查詢表空間信息
? V$TABLESPACE??來自控制文件的所有表空間名稱和編號
? DBA_TABLESPACE?所有(或用戶可訪問的)表空間的信息
? USER_TABLESPACE
? DBA_SEGMENTS? ?所有(或用戶可訪問的)表空間中的段的信息
? USER_SEGMENTS
? DBA_EXTENTS??所有(或用戶可訪問的)表空間中的數據盤區的信息
? USER_EXTENTS
? DBA_FREE_SPACE?所有(或用戶可訪問的)表空間中的空閑盤區的信息
? USER_FREE_SPACE
? V$DATAFILE??所有數據文件的信息,包括所屬表空間的名稱和編號
? V$TEMPFILE??所有臨時文件的信息,包括所屬表空間的名稱和編號
? DBA_DATA_FILES?數據文件及其所屬的表空間的信息
? DBA_TEMP_FILES?臨時文件及其所屬的臨時表空間的信息
? V$TEMP_EXTENT_MAX?本地管理的臨時表空間中的所有盤區的信息
? V$TEMP_EXTENT_POOL?本地管理的臨時表空間的緩存信息,使用的臨時表空間的狀態信息
? V$TEMP_SPACE_HEADER?每個臨時文件的已用/空閑空間信息
? DBA_USERS??所有用戶默認的和臨時的表空間的信息
? DBA_TS_QUOTAS??所有用戶的表空間配額信息
? V$SORT_SEGMENT?例程的每個排序段的信息.該視圖只在表空間為TEMPORARY類型時更新
? V$SORT_USER??用戶使用的臨時排序段的信息