1 Reply Latest reply: Dec 2, 2012 6:05 AM by LiuMaclean(刘相兵) RSS

    java source 导致 system表空间 增长

    977170
      近期开发组有个需求从数据库中读取图片保存,采用java source 开发,但是system表空间一直不断增长。

      oracle@dev1/home/oracle>sqlplus / as sysdba

      SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 30 16:01:37 2012

      Copyright (c) 1982, 2010, Oracle. All rights reserved.


      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      SQL> select * from v$version;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE 11.2.0.2.0 Production
      TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production

      SQL> select * from (select owner,segment_name,tablespace_name,bytes from dba_segments where tablespace_NAME ='SYSTEM' ORDER BY BYTES DESC ) where rownum=1;

      OWNER
      ------------------------------
      SEGMENT_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME BYTES
      ------------------------------ ----------
      SYS
      IDL_UB1$
      SYSTEM 1.0057E+10
      (system表空间被这个数据字典占满,这个表占用10g空间)

      (dba_objects出现大量java data,并且随程序运行一直变化,最高峰到1400多个java data对象,程序结束后还有200多个对象,dba_segments查询不到)

      SQL> select * from dba_objects where last_ddl_time >sysdate -5 and object_type ='JAVA DATA' and rownum<10;

      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      SYS
      JAVA$POLICY$SHARED$000030db
      1425802 JAVA DATA

      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      30-NOV-12 30-NOV-12 2012-11-30:11:19:42 VALID N N N 32



      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      SYS
      JAVA$POLICY$SHARED$000030dc
      1425803 JAVA DATA

      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      30-NOV-12 30-NOV-12 2012-11-30:11:19:53 VALID N N N 32



      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      SYS
      JAVA$POLICY$SHARED$000030dd
      1425804 JAVA DATA

      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      30-NOV-12 30-NOV-12 2012-11-30:11:20:03 VALID N N N 32



      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      SYS
      JAVA$POLICY$SHARED$000030de
      1425805 JAVA DATA

      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      30-NOV-12 30-NOV-12 2012-11-30:11:20:13 VALID N N N 32



      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      SYS
      JAVA$POLICY$SHARED$000030df
      1425806 JAVA DATA

      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      30-NOV-12 30-NOV-12 2012-11-30:11:20:24 VALID N N N 32



      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      SYS
      JAVA$POLICY$SHARED$000030e0
      1425807 JAVA DATA

      OWNER
      ------------------------------
      OBJECT_NAME
      --------------------------------------------------------------------------------
      SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
      ------------------------------ ---------- -------------- -------------------
      CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S NAMESPACE
      ------------ ------------ ------------------- ------- - - - ----------
      EDITION_NAME
      ------------------------------
      30-NOV-12 30-NOV-12 2012-11-30:11:20:34 VALID N N N 32

      java source代码
      package util;
      import java.io.FileOutputStream;
      import java.io.OutputStream;
      import oracle.sql.BLOB;
      public class OracleBlobUtil {
      public static void exp(BLOB blob,String expDir) throws Exception{
      byte[] bt = blob.getBytes(1, (int)blob.length());
      OutputStream os = new FileOutputStream(expDir);
      os.write(bt);
      os.flush();
      os.close();
      }
      }

      请问 java source java data 具体什么含义?
        • 1. Re: java source 导致 system表空间 增长
          LiuMaclean(刘相兵)
          create table idl_ub1$                            /* idl table for ub1 pieces */
          ( obj#          number not null,                            /* object number */
            part          number not null,
                   /* part: 0 = diana, 1 = portable pcode, 2 = machine-dependent pcode */
            version       number,                                    /* version number */
            piece#        number not null,                             /* piece number */
            length        number not null,                             /* piece length */
            piece         long raw not null)                              /* ub1 piece */
            storage (initial 10k next 100k maxextents unlimited pctincrease 0)
          /
          该表用来存放 PL/SQL编译后的代码 可能是 diana 可能是pcode

          查一下:

          select count(*) from sys. idl_ub1$;

          select count(*),object_type from dba_objects group by object_type;

          select bytes/1024/1024 "MB" from dba_segments where segment_name='IDL_UB1$';