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

■查看排序段的使用情況

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

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

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

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

■使用UNIFORM區(qū)分配方式的表空間
? 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;?????????????????????????????????????????????????????????????????????????????
? 優(yōu)點:在表空間中不會產(chǎn)生磁盤碎片,可以節(jié)約磁盤空間

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

■使用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;

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

■創(chuàng)建臨時表空間
? 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;

■創(chuàng)建撤銷表空間
? 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? /
? 創(chuàng)建UNDO表空間時只能指定DATAFILE和EXTENT MANAGEMENT LOCAL選項,而不能指定其他選項,否則會有錯誤提示.
? UNDO表空間采用本地管理方式,區(qū)的大小自動分配.

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

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

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

■充許數(shù)據(jù)文件自動擴展
? 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;

■使表空間聯(lián)機
? SQL> alter tablespace myts01 offline;

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

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

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

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

■設(shè)置數(shù)據(jù)庫默認表空間
? SQL> alter database default tablespace myts01;

■設(shè)置數(shù)據(jù)庫默認臨時表空間?
? SQL> alter database default temporary tablespace mytmpts01;

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

■刪除myts03表空間,包括該表空間中的內(nèi)容及數(shù)據(jù)
? SQL> drop tablespace myts04 including contents and datafiles;
? 刪除表空間的同時刪除與它相關(guān)的數(shù)據(jù)文件

■獲得數(shù)據(jù)庫中各個表空間的名稱、區(qū)管理方式、區(qū)分配方式、段管理方式、類型等信息
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?

■查詢數(shù)據(jù)庫中所有表空間的名稱和默認的存儲參數(shù)
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

■查詢數(shù)據(jù)庫中數(shù)據(jù)文件的位置、名稱、大小及其所屬的表空間
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

■生成數(shù)據(jù)庫中每個表空間的空閑盤區(qū)的信息
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列顯示該表空間文件中的空閑空間盤區(qū)數(shù)；
maximun和minimum列顯示數(shù)據(jù)庫塊中最大和最小的空間區(qū)域；
average列顯示一個空閑空間盤區(qū)以塊計的平均大小；
total列顯示每個表空間文件中空閑空間盤區(qū)以塊計的數(shù)量

■查詢表空間的空閑空間大小
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

■創(chuàng)建臨時表空間組
? SQL> alter tablespace mybigtempts01 tablespace group mytmptsgp01;?
? SQL> alter tablespace mytmpts01 tablespace group mytmptsgp01;

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

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