oracle表空间改名语句,[Oracle] 表空间语句

1  查看表空间名字与文件所在的位置

select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space

from dba_data_files where tablespace_name = 'TS_ESB_LOG'

order by tablespace_name

2  修改表空间的大小(三种方式)

1. 方式一：修改关联表空间的文件大小

alter database datafile '\oracle\oradate\xxx_2012.dbf' resize 4000m

2. 方式二：为表空间添加一个新的文件

alter database ESPS_2012 add datafile '\oracle\oradata\xxx.2012.dbf' size 1000m

3. 方式三：设置表空间自动扩展

alter database datafile '\oracle\oradata\xxx.2012.dbf' autoextend on next 100m maxsize 1000m

3  查询表空间使用情况

select a.tablespace_name, a.bytes/1024/1024 "sum MB",

(a.bytes - b.bytes)/1024/1024 "used MB", b.bytes/1024/1024 "free MB",

round (((a.bytes - b.bytes)/a.bytes)*100,2) "userd%" from

(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,

(select tablespace_name, sum(bytes) bytes, max(bytes) largest from dba_free_space group by

tablespace_name) b

where a.tablespace_name = b.tablespace_name

order by ((a.bytes - b.bytes)/a.bytes) desc; 本文转载：http://wenku.baidu.com/view/918b2246336c1eb91a375df8.html