在Oracle中，贵公司的数据库有多大？大一点的表有多大？有多少行？

DB宝 2019-10-24

1136

♣题目部分在Oracle中，贵公司的数据库有多大？大一点的表有多大？有多少行？

     
♣答案部分

对于数据库的大小，需要注意的问题是数据库的大小不能以表空间的分配大小而论，而应该以表空间的占用空间大小而论，并且需要减掉SYSTEM、SYSAUX、TEMP和Undo这些表空间占用的空间。因为有的系统Undo空间可能分配得很大，比如500G，所以，计算数据库大小的时候应该排除这些表空间。这里作者给出自己一个常用的查询表空间大小的SQL语句，该SQL语句列出了表空间的名称、表空间的分配大小和使用大小，并且列出了所有表空间的总体情况，如下所示：
 1SET PAGESIZE 9999 LINE 9999
 2COL TS_NAME FORMAT A30
 3WITH WT1 AS
 4 (SELECT TS.TABLESPACE_NAME,
 5         DF.ALL_BYTES,
 6         DECODE(DF.TYPE,
 7                'D',
 8                NVL(FS.FREESIZ, 0),
 9                'T',
10                DF.ALL_BYTES - NVL(FS.FREESIZ, 0)) FREESIZ,
11         DF.MAXSIZ,
12         TS.BLOCK_SIZE,
13         TS.LOGGING,
14         TS.FORCE_LOGGING,
15         TS.CONTENTS,
16         TS.EXTENT_MANAGEMENT,
17         TS.SEGMENT_SPACE_MANAGEMENT,
18         TS.RETENTION,
19         TS.DEF_TAB_COMPRESSION,
20         DF.TS_DF_COUNT 
21  FROM   DBA_TABLESPACES TS, 
22         (SELECT 'D' TYPE,
23                 TABLESPACE_NAME,
24                 COUNT(*) TS_DF_COUNT,
25                 SUM(BYTES) ALL_BYTES,
26                 SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ 
27          FROM   DBA_DATA_FILES D
28          GROUP  BY TABLESPACE_NAME
29          UNION ALL 
30          SELECT 'T',
31                 TABLESPACE_NAME,
32                 COUNT(*) TS_DF_COUNT,
33                 SUM(BYTES) ALL_BYTES,
34                 SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) 
35          FROM   DBA_TEMP_FILES D
36          GROUP  BY TABLESPACE_NAME) DF, 
37         (SELECT TABLESPACE_NAME,
38                 SUM(BYTES) FREESIZ
39          FROM   DBA_FREE_SPACE
40          GROUP  BY TABLESPACE_NAME
41          UNION ALL
42          SELECT TABLESPACE_NAME,
43                 SUM(D.BLOCK_SIZE * A.BLOCKS) BYTES 
44          FROM   GV$SORT_USAGE   A,
45                 DBA_TABLESPACES D
46          WHERE  A.TABLESPACE = D.TABLESPACE_NAME
47          GROUP  BY TABLESPACE_NAME) FS
48  WHERE  TS.TABLESPACE_NAME = DF.TABLESPACE_NAME
49  AND    TS.TABLESPACE_NAME = FS.TABLESPACE_NAME(+))
50SELECT (SELECT A.TS#
51        FROM   V$TABLESPACE A
52        WHERE  A.NAME = UPPER(T.TABLESPACE_NAME)) TS#,
53       T.TABLESPACE_NAME TS_NAME,
54       ROUND(T.ALL_BYTES / 1024 / 1024) TS_SIZE_M,
55       ROUND(T.FREESIZ / 1024 / 1024) FREE_SIZE_M,
56       ROUND((T.ALL_BYTES - T.FREESIZ) / 1024 / 1024) USED_SIZE_M,
57       ROUND((T.ALL_BYTES - T.FREESIZ) * 100 / T.ALL_BYTES, 3) USED_PER,
58       ROUND(MAXSIZ / 1024 / 1024/1024, 3) MAX_SIZE_G,
59       ROUND(DECODE(MAXSIZ, 0, TO_NUMBER(NULL), (T.ALL_BYTES - FREESIZ)) * 100 /
60             MAXSIZ,
61             3) USED_PER_MAX,
62       ROUND(T.BLOCK_SIZE) BLOCK_SIZE,
63       T.LOGGING,
64       T.TS_DF_COUNT 
65FROM   WT1 T
66UNION ALL
67SELECT TO_NUMBER('') TS#,
68       'ALL TS:' TS_NAME,
69       ROUND(SUM(T.ALL_BYTES) / 1024 / 1024, 3) TS_SIZE_M,
70       ROUND(SUM(T.FREESIZ) / 1024 / 1024) FREE_SIZE_M,
71       ROUND(SUM(T.ALL_BYTES - T.FREESIZ) / 1024 / 1024) USED_SIZE_M,
72       ROUND(SUM(T.ALL_BYTES - T.FREESIZ) * 100 / SUM(T.ALL_BYTES), 3) USED_PER,
73       ROUND(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_SIZE,
74       TO_NUMBER('') "USED,% OF MAX SIZE",
75       TO_NUMBER('') BLOCK_SIZE,
76       '' LOGGING,
77       TO_NUMBER('') TS_DF_COUNT 
78FROM   WT1 T
79ORDER BY TS#
80;

复制
执行以上的SQL语句，若有如下的结果：
可以说数据库大约有2205-751-629-14-2=809M，而并非是2.2G。
至于大一点的表有多大？有多少行？这个问题考察DBA对自己库的熟悉程度，通过下面的SQL语句可以查询一个表的大小：
 1SYS@RACLHR2> SELECT *
 2  2    FROM (SELECT D.OWNER, D.SEGMENT_NAME, (SUM(D.BYTES) / 1024 / 1024) SIZE_M
 3  3            FROM DBA_SEGMENTS D
 4  4           GROUP  BY D.OWNER, D.SEGMENT_NAME
 5  5           ORDER BY SIZE_M DESC)
 6  6   WHERE ROWNUM <= 5;
 7OWNER      SEGMENT_NAME                           SIZE_M
 8---------- ---------------------------------- ----------
 9LKILL      T_KILL                                   7103
10SYS        I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST             50
11SYS        IDL_UB1$                                   35
12SYS        WRI$_OPTSTAT_HISTGRM_HISTORY               31
13SYS        WRH$_SYSMETRIC_HISTORY                     27
14SYS@RACLHR2> SELECT D.OWNER, D.TABLE_NAME, D.NUM_ROWS
15  2    FROM DBA_TABLES D
16  3   WHERE D.TABLE_NAME = 'T_KILL'
17  4   AND D.OWNER='LKILL';
18OWNER                          TABLE_NAME                       NUM_ROWS
19------------------------------ ------------------------------ ----------
20LKILL                          T_KILL                           44719104

复制
可以看到最大的表是LKILL用户下的T_KILL表，大约7G，约有4400W条的数据量，读者应该以自己实际管理的库为准。
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗