联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
今天中午一朋友问我怎么清空一个用户下面所有的表的comment信息(估计是系统要发布或者买出去,不想让人知道表结构的含义),我当时的感觉就是直接去基表中去修改,这样可以一次性实现,于是就做了下面试验,并给他提供了相关sql语句
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Tue Nov 8 12:17:24 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options --我们可以通过DBA_COL_COMMENTS视图(或者同义词)查询到表的comment信息 --那么我们通过这个表找到comment的基表是什么表 SQL> set long 100000 SQL> set pages 0 SQL> SELECT DBMS_METADATA.get_ddl('VIEW','DBA_COL_COMMENTS','SYS') FROM DUAL; CREATE OR REPLACE FORCE VIEW "SYS"."DBA_COL_COMMENTS" ("OWNER", "TABLE_NAME", select u.name, o.name, c.name, co.comment$ from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co where o.owner# = u.user# and o.type# in (2, 4) and o.obj# = c.obj# and c.obj# = co.obj#(+) and c.intcol# = co.col#(+) and bitand(c.property, 32) = 0 /* not hidden column */ --通过上面的语句,我们发现col$是存储commet的基表 SQL> desc sys.com$ Name Null? Type ----------------------------------------- -------- ---------------------------- OBJ# NOT NULL NUMBER COL# NUMBER COMMENT$ VARCHAR2(4000) --查询CHF用户下面表的commet情况 SQL> col comment$ for a30 SQL> SELECT * 2 FROM SYS.COM$ A 3 WHERE EXISTS (SELECT 1 4 FROM DBA_OBJECTS 5 WHERE OWNER = 'CHF' 6 AND OBJECT_TYPE LIKE 'TABLE%' 7 AND OBJECT_ID = A.OBJ#) 8 AND COMMENT$ IS NOT NULL; OBJ# COL# COMMENT$ ---------- ---------- ------------------------------ 67405 1 xifenfei1 67405 2 xifenfei2 67405 3 xifenfei3 67405 8 惜分飞 67405 13 chf 67405 17 xifenfei88 71926 1 feifei 71926 2 chf 71926 3 xff 70870 1 xifenfei 10 rows selected. --更新基表的comment$的信息为null SQL> UPDATE SYS.COM$ 2 SET COMMENT$ = NULL 3 WHERE EXISTS (SELECT 1 4 FROM DBA_OBJECTS 5 WHERE OWNER = 'CHF' 6 AND OBJECT_TYPE LIKE 'TABLE%' 7 AND OBJECT_ID = OBJ#) 8 AND COMMENT$ IS NOT NULL; 10 rows updated. SQL> commit; Commit complete. --验证更新成功,chf下面的所有comment信息都变成了null SQL> SELECT * 2 FROM SYS.COM$ A 3 WHERE EXISTS (SELECT 1 4 FROM DBA_OBJECTS 5 WHERE OWNER = 'CHF' 6 AND OBJECT_TYPE LIKE 'TABLE%' 7 AND OBJECT_ID = A.OBJ#) 8 AND COMMENT$ IS NOT NULL; no rows selected SQL> SELECT * FROM DBA_COL_COMMENTS WHERE comments IS NOT NULL AND owner='CHF'; no rows selected
《清空schema中所有表的comment信息》有 1 条评论