清空schema中所有表的comment信息

今天中午一朋友问我怎么清空一个用户下面所有的表的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