ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DROP USER过程中出现的奇怪的对象信息

DROP USER过程中出现的奇怪的对象信息

原创 Linux操作系统 作者:yangtingkun 时间:2009-10-07 23:50:57 0 删除 编辑

在删除用户的时候,在后台查询了一下DBA_SEGMENTS视图,结果发现其他的对象信息。

 

 

在一个会话上执行用户的删除操作:

SQL> SELECT DISTINCT OWNER, TABLESPACE_NAME FROM DBA_SEGMENTS;

OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------
SYSTEM                         SYSAUX
SYSMAN                         SYSAUX
SYS                            SYSTEM
OUTLN                          SYSTEM
DBSNMP                         SYSAUX
GPO_OPERATOR                   GPO
WMSYS                          SYSAUX
ORDSYS                         SYSAUX
MDSYS                          SYSAUX
ZHEJIANG_KHD                   ZHEJIANG
SYS                            UNDOTBS2
SYSTEM                         SYSTEM
TSMSYS                         SYSAUX
EXFSYS                         SYSAUX
CTXSYS                         SYSAUX
DMSYS                          SYSAUX
NDMAIN                         NDMAIN
SPOTLIGHT                      SYSAUX
SYS                            UNDOTBS1
SYS                            SYSAUX
XDB                            SYSAUX
OLAPSYS                        SYSAUX
GPO                            GPO
GPO_BUYER_CATALOG              GPO
ZHEJIANG                       ZHEJIANG
GPO_SHANGHAI_DATA              GPO
ZHEJIANG_OPERATOR              ZHEJIANG
GPO_SALER_BID                  GPO

已选择28行。

SQL> SELECT COUNT(*) 
  2  FROM DBA_SEGMENTS
  3  WHERE TABLESPACE_NAME = 'GPO';

  COUNT(*)
----------
      1550

SQL> BEGIN
  2  FOR I IN (SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE 'GPO%') LOOP
  3  EXECUTE IMMEDIATE 'DROP USER ' || I.USERNAME || ' CASCADE';
  4  END LOOP;
  5  END;
  6  /

PL/SQL 过程已成功完成。

DROP USER的过程中,在另一个会话中检查DBA_SEGEMNTS视图,意外发现了奇怪的对象:

SQL> SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME
  2  FROM DBA_SEGMENTS
  3  WHERE TABLESPACE_NAME = 'GPO';

OWNER                          SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
GPO                            GPO_BAL_ORG_DEFRAY_HIS         GPO
GPO                            GPO_BAL_ORG_DEFRAY_LOG         GPO
GPO                            GPO_BAL_ORG_PRO                GPO
GPO                            GPO_BAL_USER_INFO              GPO
GPO                            GPO_BID_NOTICE                 GPO
.
.
.
GPO                            IDX_GPO_BID_RESULT_PROD        GPO
GPO                            IDX_GPO_HIT_COMM_1             GPO
GPO                            41.97672                       GPO
GPO                            41.16904                       GPO

已选择153行。

其他对象都很正常,但是最后两个对象名称很奇怪,可以确定这两个对象的名称不是用户创建时指定的。

由于同时在执行删除操作,很可能是Oracle在删除对象时从数据字典中去掉了一些信息,导致了这样的显示结果。

不过这两个对象的名称肯定有一定的意义,肯定不可能是两个任意的小数。

前面那个41难道是执行DROP用户的会话:

SQL> SELECT COUNT(*) 
  2  FROM DBA_SEGMENTS
  3  WHERE TABLESPACE_NAME = 'GPO';

  COUNT(*)
----------
         0

SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1;

       SID
----------
       312

回到删除用户的会话执行上面的SQL,发现会话ID312。看来和会话信息没有关系。

既然是DBA_SEGMENTS,那么这个数字多半和存储位置有关系,难道是FILE.BLOCK的格式,检查数据文件:

SQL> SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME
  2  FROM DBA_DATA_FILES
  3  WHERE FILE_ID = 41;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
        41 +MEMBER/tradedb/tradedb_gpo_2_4g                   GPO

这个结果已经足够说明问题了。不过由于对象已经被删除,因此从DBA_EXTENTS视图中无法获得删除对象的BLOCK信息,即使使用闪回查询也得不到结果。

幸好当时数据库是利用利用源数据库的备份恢复出来的,因此物理结构和源数据库保持一致。在源数据库中对DBA_EXTENTS进行查询:

SQL> SELECT OWNER, SEGMENT_NAME
  2  FROM DBA_EXTENTS
  3  WHERE FILE_ID = 41
  4  AND BLOCK_ID <= 16904
  5  AND BLOCK_ID + BLOCKS >= 16904;

OWNER                          SEGMENT_NAME
------------------------------ --------------------------------------------------
GPO                            GPO_BAL_ORG_DEFRAY

SQL> SELECT OWNER, SEGMENT_NAME
  2  FROM DBA_EXTENTS
  3  WHERE FILE_ID = 41
  4  AND BLOCK_ID <= 97672
  5  AND BLOCK_ID + BLOCKS >= 97672;

OWNER                          SEGMENT_NAME
------------------------------ -------------------------------------------------
GPO                            PK_GPO_BAL_ORG_DEFRAY

不用再做进一步的查询,从名称上已经可以看到二者是表和主键的关系。

其实不只是删除对象的过程,在添加对象时,也可能出现这种临时对象:

SQL> BEGIN
  2  FOR I IN 1..2 LOOP
  3  EXECUTE IMMEDIATE 'CREATE TABLE T' || I || ' AS SELECT * FROM DBA_OBJECTS';
  4  END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

在这个SQL的运行过程中,在另一个节点上执行查询:

SQL> SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME
  2  FROM DBA_SEGMENTS
  3  WHERE TABLESPACE_NAME = 'YANGTK'
  4  AND WNER = 'A';

OWNER                          SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
A                              T1                             YANGTK
A                              T2                             YANGTK
A                              T3                             YANGTK
A                              T4                             YANGTK
A                              T5                             YANGTK
A                              T6                             YANGTK
A                              T7                             YANGTK
A                              T8                             YANGTK
A                              T9                             YANGTK
A                              T10                            YANGTK
A                              T11                            YANGTK
A                              T12                            YANGTK
A                              T13                            YANGTK
A                              T14                            YANGTK
A                              8.7571                         YANGTK

15 rows selected.

等语句执行完成,检查创建的对象信息:

SQL> SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME
  2  FROM DBA_EXTENTS
  3  WHERE FILE_ID = 8
  4  AND BLOCK_ID <= 7571
  5  AND BLOCK_ID + BLOCKS >= 7571;

OWNER                          SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
A                              T15                            YANGTK

Oracle在创建或删除对象的过程中,可能会临时使用对象的存储位置信息来代替对象的名称。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-616089/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10405776