[20120816]快速修改表的schema.txt

    前几天在测试环境,开发人员把几个表建成system schema,需要修改成别的schema模式.当然方法很多,ctas,exp/imp等等.数据库建立在他自己的机器(数据库是11G的),我建议他可以直接修改sys.obj$对象.最后他发现修改后,查看dba_objects依旧是原来的schema.我自己也做了测试.

测试如下:

1.测试环境

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

grant dba to test1 identified by test1;

grant dba to test2 identified by test2;

connect test1/test1

create table t as select rownum id,'test' name from dual connect by level <=1000;

create unique index i_t_id on t(id);

2.查看建立的对象:

SQL> column owner format a10

SQL> column object_name format a10

SQL> column segment_name format a10

SQL> SELECT owner,object_name,object_id,data_object_id,object_type FROM dba_objects WHERE owner LIKE 'TEST_' AND object_name IN ('T', 'I_T_ID');

OWNER      OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

---------- ---------- ---------- -------------- -------------------

TEST1      I_T_ID         104533         104533 INDEX

TEST1      T              104532         104532 TABLE

SQL> SELECT owner, segment_name,segment_type, header_file, header_block FROM dba_segments WHERE owner LIKE 'TEST_' AND segment_name IN ('T', 'I_T_ID');

OWNER      SEGMENT_NA SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK

---------- ---------- ------------------ ----------- ------------

TEST1      I_T_ID     INDEX                        4        52474

TEST1      T          TABLE                        4        13922

3.修改底层sys.obj$

SQL> select obj#,dataobj#,owner#,name from obj$ where obj# in (104532,104533);

      OBJ#   DATAOBJ#     OWNER# NAME

---------- ---------- ---------- ------------------------------

    104532     104532        100 T

    104533     104533        100 I_T_ID

SQL> select user#,name from user$ where name like 'TEST_';

     USER# NAME

---------- ------------------------------

       100 TEST1

       101 TEST2

--再次强调,不要在生产系统做这样的操作!!!!

update (select obj#,dataobj#,owner#,name from obj$ where obj# in (104532,104533)) set owner#=101;

commit;

4.查看视图dba_objects,dba_segments:

SQL> SELECT owner,object_name,object_id,data_object_id,object_type FROM dba_objects WHERE owner LIKE 'TEST_' AND object_name IN ('T', 'I_T_ID');

OWNER      OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

---------- ---------- ---------- -------------- -------------------

TEST1      I_T_ID         104533         104533 INDEX

TEST1      T              104532         104532 TABLE

SQL> SELECT owner, segment_name,segment_type, header_file, header_block FROM dba_segments WHERE owner LIKE 'TEST_' AND segment_name IN ('T', 'I_T_ID');

OWNER      SEGMENT_NA SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK

---------- ---------- ------------------ ----------- ------------

TEST2      I_T_ID     INDEX                        4        52474

TEST2      T          TABLE                        4        13922

--奇怪视图dba_objects显示的owner=test1,没有变,why?少修改了什么?


5.查看执行计划:

SQL> SELECT owner,object_name,object_id,data_object_id,object_type FROM dba_objects WHERE owner LIKE 'TEST_' AND object_name IN ('T', 'I_T_ID');

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  bz93xvx2xynu5, child number 0

-------------------------------------

SELECT owner,object_name,object_id,data_object_id,object_type FROM

dba_objects WHERE owner LIKE 'TEST_' AND object_name IN ('T', 'I_T_ID')

Plan hash value: 4063009738

-------------------------------------------------------------------------------

| Id  | Operation                         | Name        | E-Rows | Cost (%CPU)|

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |             |        |     8 (100)|

|   1 |  VIEW                             | DBA_OBJECTS |      2 |     8   (0)|

|   2 |   UNION-ALL                       |             |        |            |

|*  3 |    TABLE ACCESS BY INDEX ROWID    | SUM$        |      1 |     1   (0)|

|*  4 |     INDEX UNIQUE SCAN             | I_SUM$_1    |      1 |     0   (0)|

|*  5 |    FILTER                         |             |        |            |

|   6 |     NESTED LOOPS                  |             |      1 |     7   (0)|

|   7 |      NESTED LOOPS                 |             |      1 |     6   (0)|

|   8 |       TABLE ACCESS BY INDEX ROWID | USER$       |      1 |     2   (0)|

|*  9 |        INDEX RANGE SCAN           | I_USER1     |      1 |     1   (0)|

|  10 |       INLIST ITERATOR             |             |        |            |

|* 11 |        TABLE ACCESS BY INDEX ROWID| OBJ$        |      1 |     4   (0)|

|* 12 |         INDEX RANGE SCAN          | I_OBJ5      |      1 |     3   (0)|

|* 13 |      INDEX RANGE SCAN             | I_USER2     |      1 |     1   (0)|

|* 14 |     TABLE ACCESS BY INDEX ROWID   | IND$        |      1 |     2   (0)|

|* 15 |      INDEX UNIQUE SCAN            | I_IND1      |      1 |     1   (0)|

|  16 |     NESTED LOOPS                  |             |      1 |     2   (0)|

|* 17 |      INDEX FULL SCAN              | I_USER2     |      1 |     1   (0)|

|* 18 |      INDEX RANGE SCAN             | I_OBJ4      |      1 |     1   (0)|

|  19 |    NESTED LOOPS                   |             |      1 |     1   (0)|

|* 20 |     INDEX FULL SCAN               | I_LINK1     |      1 |     0   (0)|

|* 21 |     TABLE ACCESS CLUSTER          | USER$       |      1 |     1   (0)|

|* 22 |      INDEX UNIQUE SCAN            | I_USER#     |      1 |     0   (0)|

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)

   4 - access("S"."OBJ#"=:B1)

   5 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1

              AND =1)) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND

              "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND

              "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND

              "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND

              "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR

              (INTERNAL_FUNCTION("O"."TYPE#") AND

              ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND

              "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND

              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR

               IS NOT NULL)))))

   9 - access("U"."NAME" LIKE 'TEST_')

       filter("U"."NAME" LIKE 'TEST_')

  11 - filter(BITAND("O"."FLAGS",128)=0)

  12 - access("O"."SPARE3"="U"."USER#" AND (("O"."NAME"='I_T_ID' OR

              "O"."NAME"='T')) AND "O"."LINKNAME" IS NULL)

       filter(("O"."NAME"<>'_NEXT_OBJECT' AND

              "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL))

  13 - access("O"."OWNER#"="U"."USER#")

  14 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR

              "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))

  15 - access("I"."OBJ#"=:B1)

  17 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('us

              erenv','current_edition_id')))

       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('u

              serenv','current_edition_id'))))

  18 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND

              "O2"."OWNER#"="U2"."USER#")

  20 - filter(("L"."NAME"='I_T_ID' OR "L"."NAME"='T'))

  21 - filter("U"."NAME" LIKE 'TEST_')

  22 - access("L"."OWNER#"="U"."USER#")

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

78 rows selected.

检查发现如下:
  12 - access("O"."SPARE3"="U"."USER#" AND (("O"."NAME"='I_T_ID' OR
              "O"."NAME"='T')) AND "O"."LINKNAME" IS NULL)
       filter(("O"."NAME"<>'_NEXT_OBJECT' AND
              "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL))

O.spare3=u.user# ?习惯连接条件应该是"O"."OWNER#"="U"."USER#",自己检查相关视图:

CREATE OR REPLACE FORCE VIEW SYS."_CURRENT_EDITION_OBJ" (obj#,

                                                         dataobj#,

                                                         defining_owner#,

                                                         NAME,

                                                         namespace,

                                                         subname,

                                                         type#,

                                                         ctime,

                                                         mtime,

                                                         stime,

                                                         status,

                                                         remoteowner,

                                                         linkname,

                                                         flags,

                                                         oid$,

                                                         spare1,

                                                         spare2,

                                                         spare3,

                                                         spare4,

                                                         spare5,

                                                         spare6,

                                                         owner#,

                                                         defining_edition

                                                        )

AS

   SELECT o."OBJ#", o."DATAOBJ#", o."OWNER#", o."NAME", o."NAMESPACE", o."SUBNAME", o."TYPE#", o."CTIME", o."MTIME", o."STIME",

          o."STATUS", o."REMOTEOWNER", o."LINKNAME", o."FLAGS", o."OID$", o."SPARE1", o."SPARE2", o."SPARE3", o."SPARE4",

          o."SPARE5", o."SPARE6", o.spare3,

          CASE

             WHEN (o.type# NOT IN (4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 22, 87) OR BITAND (u.spare1, 16) = 0)

                THEN NULL

             WHEN (u.type# = 2)

                THEN (SELECT eo.NAME

                        FROM obj$ eo

                       WHERE eo.obj# = u.spare2)

             ELSE 'ORA$BASE'

          END

     FROM obj$ o, user$ u

    WHERE o.owner# = u.user#

      AND (                                                                                         /* non-versionable object */

              (o.type# NOT IN (4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 22, 87, 88) OR BITAND (u.spare1, 16) = 0

              )

           /* versionable object visible in current edition */

           OR (    o.type# IN (4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 22, 87)

               AND (   (u.type# <> 2 AND SYS_CONTEXT ('userenv', 'current_edition_name') = 'ORA$BASE')

                    OR (u.type# = 2 AND u.spare2 = SYS_CONTEXT ('userenv', 'current_edition_id'))

                    OR EXISTS (

                          SELECT 1

                            FROM obj$ o2, user$ u2

                           WHERE o2.type# = 88

                             AND o2.dataobj# = o.obj#

                             AND o2.owner# = u2.user#

                             AND u2.type# = 2

                             AND u2.spare2 = SYS_CONTEXT ('userenv', 'current_edition_id'))

                   )

              )

          );

--注意 owner# 对应的是  o.spare3 小写,很明显是补进去的.
--与Edition-based Redefinition有关

6.增加修改obj#.spare3字段:

SQL> update (select obj#,dataobj#,owner#,name,spare3 from obj$ where obj# in (104532,104533)) set spare3=101;

2 rows updated.

SQL> commit ;

SQL> SELECT owner,object_name,object_id,data_object_id,object_type FROM dba_objects WHERE object_name IN ('T', 'I_T_ID');

OWNER      OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

---------- ---------- ---------- -------------- -------------------

TEST2      T              104532         104532 TABLE

TEST2      I_T_ID         104533         104533 INDEX

--OK现在一切正常!