Oracle运维--表空间--表空间--check脚本
普通表空间check脚本
check_tablespace.sql
1 set echo off 2 --format 3 set feedback off 4 5 prompt ******************* 6 prompt 表空间元数据 7 prompt ******************* 8 set linesize 200 pagesize 200 9 select 10 tablespace_name,BLOCK_SIZe,status,contents,retention,logging,force_logging, 11 extent_management,segment_space_management,def_tab_compression,encrypted,bigfile 12 from dba_tablespaces 13 order by contents,tablespace_name,status; 14 15 prompt 16 prompt ******************* 17 prompt 普通表空间使用率 18 prompt ******************* 19 SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g",round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 percent 20 FROM (SELECT tablespace_name,SUM(bytes) free FROM 21 DBA_FREE_SPACE GROUP BY tablespace_name ) a, 22 (SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b 23 WHERE a.tablespace_name=b.tablespace_name --and a.tablespace_name not in (select TABLESPACE_NAME from dba_tablespaces where contents='UNDO') 24 ORDER BY a.tablespace_name; 25 26 prompt 27 prompt ******************* 28 prompt 普通表空间local 29 prompt ******************* 30 select tablespace_name,file_name from dba_data_files where file_name not like '+%';
