数据库表空间查询和扩展

数据库表空间查询和扩展--表空间扩展:ALTERTABLESPACEDEV_SOAINFRAADDDATAFILE'/soadb/app/soadb/oradata/soadb/DEV_soainfra02.dbf'SIZE4096M;alterdatabasedatafile'/soadb/app/soadb/oradata/soadb/system01.dbf'resize1024M;--查询表空间使用情况SELECTupper(f.tablespace_name)表空间名,d.tot_grootte_mb表空间大小(M),d.tot_grootte_mb-f.total_bytes已使用空间(M),to_char(round((d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb*100,2),'990.99')||'%'使用比,f.total_bytes空闲空间(M),f.max_bytes最大块(M)FROM(SELECTtablespace_name,round(SUM(bytes)/(1024*1024),2)total_bytes,round(MAX(bytes)/(1024*1024),2)max_bytesFROMsys.dba_free_spaceGROUPBYtablespace_name)f,(SELECTdd.tablespace_name,round(SUM(dd.bytes)/(1024*1024),2)tot_grootte_mbFROMsys.dba_data_filesddGROUPBYdd.tablespace_name)dWHEREd.tablespace_name=f.tablespace_nameORDERBY1--查询表空间的freespaceSELECTtablespace_name,COUNT(*)ASextends,round(SUM(bytes)/1024/1024,2)ASmb,SUM(blocks)ASblocksFROMdba_free_spaceGROUPBYtablespace_name;--查询表空间的总容量SELECTtablespace_name,SUM(bytes)/1024/1024ASmbFROMdba_data_filesGROUPBYtablespace_name;--查询表空间使用率SELECTtotal.tablespace_name,round(total.mb,2)AStotal_mb,round(total.mb-free.mb,2)ASused_mb,round((1-free.mb/total.mb)*100,2)||'%'ASused_pctFROM(SELECTtablespace_name,SUM(bytes)/1024/1024ASmbFROMdba_free_spaceGROUPBYtablespace_name)free,(SELECTtablespace_name,SUM(bytes)/1024/1024ASmbFROMdba_data_filesGROUPBYtablespace_name)totalWHEREfree.tablespace_name=total.tablespace_name;编辑于联动北方技术论坛