数据库增长异常、怎样查看数据表的实际大小

单位一个Oracle 11g的生产数据库，之前一直很稳定，用了一年多，表空间达到700多MB。最近半个月不知道怎么回事，业务没发生大的变化，但是数据库增长迅速，表空间已经到了2G多。应该怎样查看数据库中各个对象的实际大小呢？或者怎样检查什么地方有异常？

5 个解决方案

#1

1、空间增长后，数据库不会自动收缩。
另外，即使原来占用表空间的对象被删除(drop)，从表空间的角度看，空间并不减少。但在表空间内，这部分空间可以被其它对象所使用。如果原来占用表空间的对象中的数据被delete,那么该对象所占用的空间也是不会减少，而且这些被删除数据占用的空间不能被其它对象所使用，而只能由该对象继续使用。

2、对于你的这种情况，建议首先通过dba_segments视图，查找出占用相应表空间最多的前10个对象。然后通过show_space存储过程，分别查看各个对象的占用空间的使用情况。show_space存储过程代码附后。

3、以下链接中有使用show_space的实际样例供参考。
http://www.blogjava.net/decode360/archive/2009/07/14/287767.html

#2

procedure SHOW_SPACE(P_SEGNAME IN VARCHAR2,
           P_OWNER  IN VARCHAR2 DEFAULT USER,
           P_TYPE  IN VARCHAR2 DEFAULT 'TABLE',
           P_PARTITION IN VARCHAR2 DEFAULT NULL)
 -- THIS PROCEDURE USES AUTHID CURRENT USER SO IT CAN QUERY DBA_*
  -- VIEWS USING PRIVILEGES FROM A ROLE AND SO IT CAN BE INSTALLED
  -- ONCE PER DATABASE, INSTEAD OF ONCE PER USER WHO WANTED TO USE IT.
 AUTHID CURRENT_USER AS
  L_FREE_BLKS  NUMBER;
  L_TOTAL_BLOCKS NUMBER;
  L_TOTAL_BYTES  NUMBER;
  L_UNUSED_BLOCKS NUMBER;
  L_UNUSED_BYTES NUMBER;
  L_LASTUSEDEXTFILEID NUMBER;
  L_LASTUSEDEXTBLOCKID NUMBER;
  L_LAST_USED_BLOCK NUMBER;
  L_SEGMENT_SPACE_MGMT VARCHAR2(255);
  L_UNFORMATTED_BLOCKS NUMBER;
  L_UNFORMATTED_BYTES NUMBER;
  L_FS1_BLOCKS  NUMBER;
  L_FS1_BYTES  NUMBER;
  L_FS2_BLOCKS  NUMBER;
  L_FS2_BYTES  NUMBER;
  L_FS3_BLOCKS  NUMBER;
  L_FS3_BYTES  NUMBER;
  L_FS4_BLOCKS  NUMBER;
  L_FS4_BYTES  NUMBER;
  L_FULL_BLOCKS  NUMBER;
  L_FULL_BYTES  NUMBER;
  -- INLINE PROCEDURE TO PRINT OUT NUMBERS NICELY FORMATTED
  -- WITH A SIMPLE LABEL.
  PROCEDURE P(P_LABEL IN VARCHAR2, P_NUM IN NUMBER) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD(P_LABEL, 40, '.') ||
    TO_CHAR(P_NUM, '999,999,999,999'));
  END;
 BEGIN
  -- THIS QUERY IS EXECUTED DYNAMICALLY IN ORDER TO ALLOW THIS PROCEDURE
  -- TO BE CREATED BY A USER WHO HAS ACCESS TO DBA_SEGMENTS/TABLESPACES
  -- VIA A ROLE AS IS CUSTOMARY.
  -- NOTE: AT RUNTIME, THE INVOKER MUST HAVE ACCESS TO THESE TWO
  -- VIEWS!
  -- THIS QUERY DETERMINES IF THE OBJECT IS AN ASSM OBJECT OR NOT.
  BEGIN
    EXECUTE IMMEDIATE 'SELECT TS.SEGMENT_SPACE_MANAGEMENT
 FROM DBA_SEGMENTS SEG, DBA_TABLESPACES TS
 WHERE SEG.SEGMENT_NAME = :P_SEGNAME
 AND (:P_PARTITION IS NULL OR
 SEG.PARTITION_NAME = :P_PARTITION)
 AND SEG.OWNER = :P_OWNER
 AND SEG.TABLESPACE_NAME = TS.TABLESPACE_NAME'
      INTO L_SEGMENT_SPACE_MGMT
      USING P_SEGNAME, P_PARTITION, P_PARTITION, P_OWNER;
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('THIS MUST BE A PARTITIONED TABLE, USE P_PARTITION => ');
      RETURN;
  END;
  -- IF THE OBJECT IS IN AN ASSM TABLESPACE, WE MUST USE THIS API
  -- CALL TO GET SPACE INFORMATION; ELSE WE USE THE FREE_BLOCKS
  -- API FOR THE USER MANAGED SEGMENTS.
  IF L_SEGMENT_SPACE_MGMT = 'AUTO' THEN
    DBMS_SPACE.SPACE_USAGE(P_OWNER,
      P_SEGNAME,
      P_TYPE,
      L_UNFORMATTED_BLOCKS,
      L_UNFORMATTED_BYTES,
      L_FS1_BLOCKS,
      L_FS1_BYTES,
      L_FS2_BLOCKS,
      L_FS2_BYTES,
      L_FS3_BLOCKS,
      L_FS3_BYTES,
      L_FS4_BLOCKS,
      L_FS4_BYTES,
      L_FULL_BLOCKS,
      L_FULL_BYTES,
      P_PARTITION);
    P('UNFORMATTED BLOCKS ', L_UNFORMATTED_BLOCKS);
    P('FS1 BLOCKS (0-25) ', L_FS1_BLOCKS);
    P('FS2 BLOCKS (25-50) ', L_FS2_BLOCKS);
    P('FS3 BLOCKS (50-75) ', L_FS3_BLOCKS);
    P('FS4 BLOCKS (75-100)', L_FS4_BLOCKS);
    P('FULL BLOCKS ', L_FULL_BLOCKS);
  ELSE
    DBMS_SPACE.FREE_BLOCKS(SEGMENT_OWNER => P_OWNER,
      SEGMENT_NAME  => P_SEGNAME,
      SEGMENT_TYPE  => P_TYPE,
      FREELIST_GROUP_ID => 0,
      FREE_BLKS  => L_FREE_BLKS);
    P('FREE BLOCKS', L_FREE_BLKS);
  END IF;
  -- AND THEN THE UNUSED SPACE API CALL TO GET THE REST OF THE
  -- INFORMATION.
  DBMS_SPACE.UNUSED_SPACE(SEGMENT_OWNER  => P_OWNER,
     SEGMENT_NAME  => P_SEGNAME,
     SEGMENT_TYPE  => P_TYPE,
     PARTITION_NAME => P_PARTITION,
     TOTAL_BLOCKS  => L_TOTAL_BLOCKS,
     TOTAL_BYTES  => L_TOTAL_BYTES,
     UNUSED_BLOCKS  => L_UNUSED_BLOCKS,
     UNUSED_BYTES  => L_UNUSED_BYTES,
     LAST_USED_EXTENT_FILE_ID => L_LASTUSEDEXTFILEID,
     LAST_USED_EXTENT_BLOCK_ID => L_LASTUSEDEXTBLOCKID,
     LAST_USED_BLOCK  => L_LAST_USED_BLOCK);
  P('TOTAL BLOCKS', L_TOTAL_BLOCKS);
  P('TOTAL BYTES', L_TOTAL_BYTES);
  P('TOTAL MBYTES', TRUNC(L_TOTAL_BYTES / 1024 / 1024));
  P('UNUSED BLOCKS', L_UNUSED_BLOCKS);
  P('UNUSED BYTES', L_UNUSED_BYTES);
  P('LAST USED EXT FILEID', L_LASTUSEDEXTFILEID);
  P('LAST USED EXT BLOCKID', L_LASTUSEDEXTBLOCKID);
  P('LAST USED BLOCK', L_LAST_USED_BLOCK);
 END;

#3

user_segments 查查这个。

#4

select t.owner,t.segment_name,sum(t.bytes)/1024/1024 from dba_segments t group by t.owner,t.segment_name order by 3 desc

 看看什么对象占用的空间最大，然后看看是正常的吗

#5

另外， 11g 会执行自动统计，所以，你先对比看看，是什么数据文件大了很多