Oracle运维--表空间--普通表空间--check脚本

 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 ‘+%‘;