Oracle:获得数据文件的详细情况
数据文件,作为数据库存储数据的方式,其内具体的数据分布与差异是无法直观看到的。它有点像黑盒子,通常情况下,大家只知道向其中填数据、取数据,却不知道里面到底有些什么,大家都是把这些细枝末节的问题丢给了Oracle软件自己运行与管理。
在本文接下来的内容里,你可以初步的看到关于数据文件的这些信息的Summary。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> col file_name for a45 SQL> select b.file_name,count(a.segment_name) "Segment Count",sum(a.bytes)/1024/1024 "Sumary MB" from dba_segments a,dba_data_files b where a.header_file=b.file_id group by b.file_name; FILE_NAME Segment Count Sumary MB --------------------------------------------- ------------- ---------- /ora_data/allah/sysaux01.dbf 3789 694.375 /ora_data/allah/users01.dbf 11 .6875 /ora_data/allah/system01.dbf 2080 795.5625 /ora_data/allah/undotbs01.dbf 10 34.625 SQL> !du -sh /ora_data/allah/sysaux01.dbf 741M /ora_data/allah/sysaux01.dbf SQL> !du -sh /ora_data/allah/users01.dbf 201M /ora_data/allah/users01.dbf SQL> !du -sh /ora_data/allah/system01.dbf 801M /ora_data/allah/system01.dbf SQL> !du -sh /ora_data/allah/undotbs01.dbf 136M /ora_data/allah/undotbs01.dbf SQL> |
查看数据文件中具有的用户以及用户下的Segment数量与大小:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
SQL> select 2 cc.file_name,cc.tablespace_name,cc.bytes/1024/1024 "Datafile MB",cc.status "Datafile Status",cc.online_status "Online",aa.file_id,bb.owner,bb.OWNER_SEG_MB,bb.OWNER_SEG_COUNTS 3 from 4 (select b.file_id,count(a.segment_name) seg_counts,sum(a.bytes)/1024/1024 seg_sum_mb from dba_segments a,dba_data_files b where a.header_file=b.file_id group by b.file_id) aa, 5 (select distinct header_file,owner,count(*) owner_seg_counts,sum(bytes)/1024/1024 owner_seg_mb from dba_segments group by header_file,owner order by header_file,owner) bb, 6 dba_data_files cc 7 where 8 aa.file_id(+)=bb.header_file 9 and aa.file_id=cc.file_id(+) 10 order by 11 6,8 12 ; FILE_NAME TABLESPACE_NAME Datafile MB Datafile Online FILE_ID OWNER OWNER_SEG_MB OWNER_SEG_COUNTS --------------------------------------------- ----------------------- ----------- --------- ------- ---------- ----------------------- ------------ ---------------- /ora_data/allah/system01.dbf SYSTEM 800 AVAILABLE SYSTEM 1 LBACSYS .3125 5 SYSTEM 800 AVAILABLE SYSTEM 1 OUTLN .5625 9 SYSTEM 800 AVAILABLE SYSTEM 1 SYSTEM 16.375 262 SYSTEM 800 AVAILABLE SYSTEM 1 SYS 778.3125 1804 ********************************************* ------------ ---------------- 795.5625 2080 /ora_data/allah/sysaux01.dbf SYSAUX 740 AVAILABLE ONLINE 3 ORDSYS .4375 7 SYSAUX 740 AVAILABLE ONLINE 3 DBSNMP .5 8 SYSAUX 740 AVAILABLE ONLINE 3 GSMADMIN_INTERNAL 1.375 21 SYSAUX 740 AVAILABLE ONLINE 3 AUDSYS 1.5 6 SYSAUX 740 AVAILABLE ONLINE 3 CTXSYS 3.8125 59 SYSAUX 740 AVAILABLE ONLINE 3 DVSYS 4.4375 71 SYSAUX 740 AVAILABLE ONLINE 3 WMSYS 7.1875 108 SYSAUX 740 AVAILABLE ONLINE 3 SYSTEM 15.8125 185 SYSAUX 740 AVAILABLE ONLINE 3 ORDDATA 16.0625 192 SYSAUX 740 AVAILABLE ONLINE 3 XDB 68.5 704 SYSAUX 740 AVAILABLE ONLINE 3 MDSYS 93.75 339 SYSAUX 740 AVAILABLE ONLINE 3 SYS 239.4375 1280 SYSAUX 740 AVAILABLE ONLINE 3 APEX_040200 242.6875 810 ********************************************* ------------ ---------------- 695.5 3790 /ora_data/allah/undotbs01.dbf UNDOTBS1 135 AVAILABLE ONLINE 4 SYS 40.625 10 ********************************************* ------------ ---------------- 40.625 10 /ora_data/allah/users01.dbf USERS 200 AVAILABLE ONLINE 6 SCOTT .3125 5 USERS 200 AVAILABLE ONLINE 6 OJVMSYS .375 6 ********************************************* ------------ ---------------- .6875 11 ------------ ---------------- Grand Total: 1532.375 5891 20 rows selected. SQL> |
代码如下:
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 380
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
BREAK ON report ON file_name skip 2
col file_name for a45
col owner for a23
col tablespace_name for a23
COMPUTE sum LABEL “” of OWNER_SEG_COUNTS bytes on file_name
COMPUTE sum LABEL “” of OWNER_SEG_MB bytes on file_name
COMPUTE sum LABEL “Grand Total: ” OF OWNER_SEG_COUNTS bytes ON report
COMPUTE sum LABEL “Grand Total: ” OF OWNER_SEG_MB bytes ON report
select
cc.file_name,cc.tablespace_name,cc.bytes/1024/1024 “Datafile MB”,cc.status “Datafile Status”,cc.online_status “Online”,aa.file_id,bb.owner,bb.OWNER_SEG_MB,bb.OWNER_SEG_COUNTS
from
(select b.file_id,count(a.segment_name) seg_counts,sum(a.bytes)/1024/1024 seg_sum_mb from dba_segments a,dba_data_files b where a.header_file=b.file_id group by b.file_id) aa,
(select distinct header_file,owner,count(*) owner_seg_counts,sum(bytes)/1024/1024 owner_seg_mb from dba_segments group by header_file,owner order by header_file,owner) bb,
dba_data_files cc
where
aa.file_id(+)=bb.header_file
and aa.file_id=cc.file_id(+)
order by
6,8
;
————————————————————————
Ending。