---查看表空间的名字及文件所在位置：
select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name
---如何查看oracle表空间是否自动扩展 （increment_by是块数【8192byte/块】）
select file_name,tablespace_name,bytes/1024/1024 "bytes MB",autoextensible,increment_by,maxbytes/1024/1024 "maxbytes MB"
from dba_data_files
where tablespace_name='HSML;
select file_name,autoextensible,increment_by from dba_data_files ;
--- 查看Oracle数据库表空间大小(空闲、已使用),是否要增加表空间的数据文件
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) "percent_used"
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
---查看数据文件的使用率
select b.file_name 物理文件名,b.tablespace_name 表空间,b.bytes/1024/1024 大小M,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)
*100,1,5) 利用率
from dba_free_space a,dba_data_files b where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;
---创建时加双引号，删除也加入双引号即可
SQL> DROP TABLESPACE "Liang" INCLUDING CONTENTS AND DATAFILES;
---修改表空间为只读
alter tablespace test read only;
---恢复表空间为正常状态
alter tablespace test read wirite ;
怎么察看Oracle 数据库表空间的使用情况
select dbf.tablespace_name,dbf.totalspace "总量(M)",dbf.totalblocks as 总块数,dfs.freespace "剩余总量(M)",dfs.freeblocks "剩余块数",(dfs.freespace / dbf.totalspace) * 100 "空闲
比例"
from (select t.tablespace_name,sum(t.bytes) / 1024 / 1024 totalspace,sum(t.blocks) totalblocks
from dba_data_files t group by t.tablespace_name) dbf,(select tt.tablespace_name,sum(tt.bytes) / 1024 / 1024 freespace,sum(tt.blocks) freeblocks
from dba_free_space tt group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)
--查询表空间数量 select * from v$tablespace