oracle关于表空间的一些操作

查看 xxx 表空间是否为自动扩展:select file_name,autoextensible,increment_by from dba_data_files;select file_id,file_name,tablespace_name,autoextensible,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 MBfrom dba_data_filesgroup 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_Pctfrom (select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_free_spacegroup by tablespace_name) free,(select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_data_filesgroup by tablespace_name) totalwhere free.tablespace_name = total.tablespace_name;
扩展表空间：#首先查看表空间的名字和所属文件及空间select tablespace_name, file_id, file_name,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_use免费云主机域名d b,sys.sm$ts_free c where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name;

相关推荐: 云和恩墨打造业内领先数据库一体机

云和大数据时代的到来导致各行各业数据量的爆发，面对业务数据的日益剧增，企业的IT系统在性能、稳定性和扩展性等方面都面临前所未有的巨大挑战。如何有效应对云和大数据的浪潮去拥抱变化，成为企业迫切面临的问题。 数据驱动的时代，一切竞争的核心都会归结于IT系统的竞争，…