oracle关于表空间的一些操作
查看 xxx 表空间是否为自动扩展: select file_name,autoextensible,increment_by from dba_data_files; select file_id,file_name,tablespace_name,increment_by from dba_data_files order by file_id desc; 打开表空间自动扩展功能: alter database datafile '/u01/app/oracle/oradata/testdb/test01.dbf' autoextend on; 查看数据文件大小: select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 M from dba_data_files order by TABLESPACE_NAME; 查询表空间的总容量: select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name; 查询表空间使用率: select total.tablespace_name, round(total.MB,2) as Total_MB, round(total.MB - free.MB,2) as Used_MB, round((1 - free.MB / total.MB) * 100,2) || '%' as Used_Pct from (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free, (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name = total.tablespace_name; 扩展表空间: #首先查看表空间的名字和所属文件及空间 select tablespace_name,file_id,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; #三种扩展方法 1.alter tablespace test add datafile '/u01/test02.dbf' size 10M(自动加一个datafile) 2.alter database datafile '/u01/test.dbf' resize 20M; 3.alter database datafile '/u01/test.dbf' autoextend on next 10M maxsize 1G; 设定后查看表空间信息: select a.tablespace_name,a.bytes total,b.bytes used,c.bytes free,(b.bytes*100)/a.bytes "% used",(c.bytes*100)/a.bytes "% free" from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |