[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
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现在一切正常!
时间： 2024-09-29 10:10:54