oracle修改表结构与数据字典表的实例说解

① 添加一个字段
    SQL> alter table stu add ( addr varchar2(100) );
② 删除一个字段
    SQL> alter table stu drop (addr);
③ 修改现有字段
    SQL> alter table stu modify ( addr varchar2(200) );
    注: 如果已有数据, 则不能损失数据的精度及大小
 

① 删除一个约束
    delete from class where id = 1
    ORA-02292: 违反完整约束条件 (SCOTT.STU_CLASS_FK) - 已找到子记录
    SQL> alter table stu drop constraint stu_class_fk;
    SQL> delete from class where id = 1;
    已删除 1 行。
② 添加一个约束
    SQL> alter table stu add constraint stu_class_fk foreign key (class) references class (id);

drop table table_name;
    SQL> create table emp2 as select * from emp;
    表已创建。
    SQL> drop table emp2;
    表已删除。

0, dict 数据字典表 的 表
    TABLE_NAME  数据字典表名
    COMMENTS    描述
1, user_tables
   当前用户下所有表的描述  
    SQL> desc user_tables;
    SQL> select * from dict where table_name = 'USER_TABLES';
    SQL> select table_name from user_tables;
2, user_views
   Description of the user's own views
    SQL> desc user_views;
    SQL> select * from dict where table_name = 'USER_VIEWS';
    SQL> select view_name from user_views;
3, user_constraints
   Constraint definitions on user's own tables
    SQL> desc user_constraints
    SQL> select * from dict where table_name = 'USER_CONSTRAINTS';

cat tab
SQL> desc cat
 Name        Null?      Type
 ----------- ---------  -----------
 TABLE_NAME  NOT NULL   VARCHAR2(30)
 TABLE_TYPE             VARCHAR2(11)

SQL> desc tab
 Name        Null?      Type
 ----------- ---------  -----------
 TNAME       NOT NULL   VARCHAR2(30)
 TABTYPE                VARCHAR2(7)
 CLUSTERID              NUMBER

SQL> select * from dict where table_name='CAT';

TABLE_NAME COMMENTS
---------- -----------------------------------
CAT        Synonym for USER_CATALOG

SQL> select * from dict where table_name='USER_CATALOG';

TABLE_NAME           COMMENTS
-------------------- -------------------------
USER_CATALOG         Tables, Views, Synonyms and Sequences owned by the user