Oracle Create Table AS和表注释以及列注释 [英] Oracle Create Table AS and table comments and column comments
问题描述
是否可以创建另一个表作为CREATE TABLE AS并保留列的注释?
Is it possible to create another table as CREATE TABLE AS and also preserve columns' comments ?
CREATE TABLE TABLE1_COPY AS SELECT * FROM TABLE1;
CREATE TABLE TABLE1_COPY AS SELECT * FROM TABLE1;
上一条语句不包含列的注释.因此,TABLE1_COPY保留无列注释.使用USER_COL_COMMENTS也是在我新创建的表上重现相同注释的唯一方法吗?
The previous statement does not include columns' comments. Therefore TABLE1_COPY is left without columns' comments. Is using USER_COL_COMMENTS the only way to reproduce the same comments on my newly created table too?
推荐答案
至于DMBS_METADATA.GET_DDL,它似乎没有产生COMMENT ON 除非缺少某些属性,否则使用COLUMN语句.
As for DMBS_METADATA.GET_DDL it doesn't seem to genereate COMMENT ON COLUMN statements unless I am missing some properties.
一种方法是将dbms_metadata.get_dependent_ddl与dbms_metadata.get_ddl结合使用
One method is to use dbms_metadata.get_dependent_ddl in combination with dbms_metadata.get_ddl
以下是使用SQL plus创建的示例:
Here is an example created using SQL plus:
SQL> set long 1000000
SQL> create table t (x number);
Table created.
SQL> comment on column T.X IS 'this is the column comment';
Comment created.
SQL> comment on table T IS 'this is the table comment';
Comment created.
SQL> SELECT dbms_metadata.get_ddl( 'TABLE', 'T' ) || ' ' ||
2 dbms_metadata.get_dependent_ddl( 'COMMENT', 'T', USER ) the_ddl
3 FROM dual
4 /
THE_DDL
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "X" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
COMMENT ON COLUMN "SCOTT"."T"."X" IS 'this is the column comment'
COMMENT ON TABLE "SCOTT"."T" IS 'this is the table comment'
这篇关于Oracle Create Table AS和表注释以及列注释的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!