查看表空間有多大
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
查看表空間及其數據文件
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
查看表空間總大小，已使用，剩下多少
select a.tablespace_name,total,free,round(free/total*100,2) free_precent,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
1.create tablespaces
sql> create tablespace tablespace_name datafile 'c:"oracle"oradata"file1.dbf' size 100m,
sql> 'c:"oracle"oradata"file2.dbf' size 100m minimum extent 550k [logging/nologging]
sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)
sql> [online/offline] [permanent/temporary] [extent_management_clause]
2.locally managed tablespace
sql> create tablespace user_data datafile 'c:"oracle"oradata"user_data01.dbf'
sql> size 500m extent management local uniform size 10m;
3.temporary tablespace
sql> create temporary tablespace temp tempfile 'c:"oracle"oradata"temp01.dbf'
sql> size 500m extent management local uniform size 10m;
4.change the storage setting
sql> alter tablespace app_data minimum extent 2m;
sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999);
5.taking tablespace offline or online
sql> alter tablespace app_data offline;
sql> alter tablespace app_data online;
6.read_only tablespace
sql> alter tablespace app_data read only|write;
7.droping tablespace
sql> drop tablespace app_data including contents;
8.enableing automatic extension of data files
sql> alter tablespace app_data add datafile 'c:"oracle"oradata"app_data01.dbf'size 200m
sql> autoextend on next 10m maxsize 500m;
9.change the size fo data files manually
sql> alter database datafile 'c:\oracle\oradata\app_data.dbf' resize 200m;
9.1改變表空間大小有三種方法:
a)sql>alter database datafile 'c:\oracle\oradata\app_data.dbf' autoextend on .. 自動擴大
b)sql>alter database datafile 'c:\oracle\oradata\app_data.dbf' resize 200m; #同9一樣
c) alter tablesapce users add datafile 'c:\oracle\oradata\app_data01.dbf' size 10M #為表空間,手動新增一個datafile
10.Moving data files: alter tablespace
sql> alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf' to 'c:\oracle\app_data.dbf';
11.moving data files:alter database
sql> alter database rename file 'c:\oracle\oradata\app_data.dbf' to 'c:\oracle\app_data.dbf';
12.OMF管理表空間
設置參數:db_create_file_dest
sql>alter system set db_create_file_dest = 'c:\oradata';
sql>create tablespace usertb; #默認100M，并存放于db_create_file_dest 目錄下
sql>drop tablespace usertb; #相應的物理文件也自動刪除
13.表空間的一些數據字典與動態性能表
dba_tablespaces v$tablespace v$datafile dba_data_files,
14.查詢系統回滾段
sql>select * from dba_rollback_segs
15.限制用戶使用表空間大小
sql>alter user eton quota 10M on users(表空間名 )
16.何為臨時表空間
由于Oracle工作時經常需要一些臨時的磁盤空間，這些空間主要用作查詢時帶有排序（Group by，Order by等）等算法所用，當用完后就立即釋放，對記錄在磁盤區的信息不再使用，因此叫臨時表空間。一般安裝之后只有一個TEMP臨時表空間。