背景:有些表的字段类似如字典表,1 代表 A,2 代表B。。。
问题是后面又来了 3,4,。。如是经常要求修改该字段的注释。
但该操作属于ddl操作,会是对于的sql失效,如果该操作不是ddl操作多好:
先通过一个10704得到如下trace( TX,6001f,1fcd8是对应的记录锁,在update OBJ$ 和 update COM$ ? 通过10046有更新这两个字典表)
alter session set events '10704 trace name context forever ,level 12';
SQL> comment on column TEST.B_TYPE
2 is '1:AA 2:BB';
alter session set events '10704 trace name context off';
*** 2011-05-20 16:25:00.777
ksqgtl *** TM-00000061-00000000 mode=3 flags=0x401 timeout=21474836 ***
*** 2011-05-20 16:25:00.777
ksqgtl *** TX-0006001f-0001fcd8 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TM-00000061-00000000 mode=3 flags=0x401 timeout=21474836 ***
*** 2011-05-20 16:25:00.779
ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***
*** 2011-05-20 16:25:00.779
ksqrcl: TX,6001f,1fcd8
ksqrcl: returns 0
*** 2011-05-20 16:25:00.779
ksqrcl: TM,12,0
ksqrcl: returns 0
*** 2011-05-20 16:25:00.779
ksqrcl: TM,61,0
ksqrcl: returns 0
SQL> select object_name,object_type from dba_objects
2 where object_id in (18,97)
3 ;
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
OBJ$ TABLE
COM$ TABLE
comment on column属于ddl操作,会是对于的sql失效:
SQL> select aa.SQL_TEXT,aa.LAST_LOAD_TIME,aa.LAST_ACTIVE_TIME from v$sql aa
2 where upper(aa.SQL_TEXT) like '%SELECT COUNT(*) FROM TEST%'
3 ;
SQL_TEXT LAST_LOAD_TIME LAST_ACTIVE_TIME
------------------------------------------------------------ --------------------------------------------------------- ------------------------------
select count(*) from TEST 2011-05-20/16:55:50 2011-5-20 16:55:50
SQL>
SQL> comment on column TEST.B_TYPE
2 is '1:AA 2:BB';
Comment added
SQL>
SQL> select aa.SQL_TEXT,aa.LAST_LOAD_TIME,aa.LAST_ACTIVE_TIME from v$sql aa
2 where upper(aa.SQL_TEXT) like '%SELECT COUNT(*) FROM TEST%'
3 ;
SQL_TEXT LAST_LOAD_TIME LAST_ACTIVE_TIME
------------------------------------------------------------ --------------------------------------------------------- ------------------------------
SQL> SELECT COUNT(*) FROM TEST;
COUNT(*)
----------
137411
SQL>
SQL> select aa.SQL_TEXT,aa.LAST_LOAD_TIME,aa.LAST_ACTIVE_TIME from v$sql aa
2 where upper(aa.SQL_TEXT) like '%SELECT COUNT(*) FROM TEST%'
3 ;
SQL_TEXT LAST_LOAD_TIME LAST_ACTIVE_TIME
------------------------------------------------------------ --------------------------------------------------------- ------------------------------
SELECT COUNT(*) FROM TEST 2011-05-20/16:56:57 2011-5-20 16:56:56
SQL> SELECT COUNT(*) FROM TEST;
COUNT(*)
----------
137411
SQL>
SQL> select aa.SQL_TEXT,aa.LAST_LOAD_TIME,aa.LAST_ACTIVE_TIME from v$sql aa
2 where upper(aa.SQL_TEXT) like '%SELECT COUNT(*) FROM TEST%'
3 ;
SQL_TEXT LAST_LOAD_TIME LAST_ACTIVE_TIME
------------------------------------------------------------ --------------------------------------------------------- ------------------------------
SELECT COUNT(*) FROM TEST 2011-05-20/16:56:57 2011-5-20 16:57:23
问题是后面又来了 3,4,。。如是经常要求修改该字段的注释。
但该操作属于ddl操作,会是对于的sql失效,如果该操作不是ddl操作多好:
先通过一个10704得到如下trace( TX,6001f,1fcd8是对应的记录锁,在update OBJ$ 和 update COM$ ? 通过10046有更新这两个字典表)
alter session set events '10704 trace name context forever ,level 12';
SQL> comment on column TEST.B_TYPE
2 is '1:AA 2:BB';
alter session set events '10704 trace name context off';
*** 2011-05-20 16:25:00.777
ksqgtl *** TM-00000061-00000000 mode=3 flags=0x401 timeout=21474836 ***
*** 2011-05-20 16:25:00.777
ksqgtl *** TX-0006001f-0001fcd8 mode=6 flags=0x401 timeout=0 ***
ksqgtl *** TM-00000061-00000000 mode=3 flags=0x401 timeout=21474836 ***
*** 2011-05-20 16:25:00.779
ksqgtl *** TM-00000012-00000000 mode=3 flags=0x401 timeout=21474836 ***
*** 2011-05-20 16:25:00.779
ksqrcl: TX,6001f,1fcd8
ksqrcl: returns 0
*** 2011-05-20 16:25:00.779
ksqrcl: TM,12,0
ksqrcl: returns 0
*** 2011-05-20 16:25:00.779
ksqrcl: TM,61,0
ksqrcl: returns 0
SQL> select object_name,object_type from dba_objects
2 where object_id in (18,97)
3 ;
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
OBJ$ TABLE
COM$ TABLE
comment on column属于ddl操作,会是对于的sql失效:
SQL> select aa.SQL_TEXT,aa.LAST_LOAD_TIME,aa.LAST_ACTIVE_TIME from v$sql aa
2 where upper(aa.SQL_TEXT) like '%SELECT COUNT(*) FROM TEST%'
3 ;
SQL_TEXT LAST_LOAD_TIME LAST_ACTIVE_TIME
------------------------------------------------------------ --------------------------------------------------------- ------------------------------
select count(*) from TEST 2011-05-20/16:55:50 2011-5-20 16:55:50
SQL>
SQL> comment on column TEST.B_TYPE
2 is '1:AA 2:BB';
Comment added
SQL>
SQL> select aa.SQL_TEXT,aa.LAST_LOAD_TIME,aa.LAST_ACTIVE_TIME from v$sql aa
2 where upper(aa.SQL_TEXT) like '%SELECT COUNT(*) FROM TEST%'
3 ;
SQL_TEXT LAST_LOAD_TIME LAST_ACTIVE_TIME
------------------------------------------------------------ --------------------------------------------------------- ------------------------------
SQL> SELECT COUNT(*) FROM TEST;
COUNT(*)
----------
137411
SQL>
SQL> select aa.SQL_TEXT,aa.LAST_LOAD_TIME,aa.LAST_ACTIVE_TIME from v$sql aa
2 where upper(aa.SQL_TEXT) like '%SELECT COUNT(*) FROM TEST%'
3 ;
SQL_TEXT LAST_LOAD_TIME LAST_ACTIVE_TIME
------------------------------------------------------------ --------------------------------------------------------- ------------------------------
SELECT COUNT(*) FROM TEST 2011-05-20/16:56:57 2011-5-20 16:56:56
SQL> SELECT COUNT(*) FROM TEST;
COUNT(*)
----------
137411
SQL>
SQL> select aa.SQL_TEXT,aa.LAST_LOAD_TIME,aa.LAST_ACTIVE_TIME from v$sql aa
2 where upper(aa.SQL_TEXT) like '%SELECT COUNT(*) FROM TEST%'
3 ;
SQL_TEXT LAST_LOAD_TIME LAST_ACTIVE_TIME
------------------------------------------------------------ --------------------------------------------------------- ------------------------------
SELECT COUNT(*) FROM TEST 2011-05-20/16:56:57 2011-5-20 16:57:23
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/758322/viewspace-695848/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/758322/viewspace-695848/