查看表空间有多大
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临时表空间。