Oracle入门之表管理

Oracle入门之表管理

表是数据库中最基本的储存数据逻辑单元，它由行和列组成，Oracle将表分为堆组织表（heap table）,索引组织表（index organized table，IOT）和临时表（temporary table） 本节讲简要介绍下oracle的表管理(heap table)，主要包括添加，删除列，更改列名，表名；创建相同的表，将表置为只读和读写模式，删除表和回闪表等操作

1：添加列
yang SQL>select * from test order by 1;

        ID BANNER
---------- --------------------------------
         1 one
         2 two
         3 three
         4 four

yang SQL>alter table test add (address varchar(10));

Table altered.

yang SQL>desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 BANNER                                             VARCHAR2(32)
 ADDRESS                                            VARCHAR2(10)

2:删除列
yang SQL>alter table test drop(address);

Table altered.

yang SQL>desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 BANNER                                             VARCHAR2(32)

yang SQL>alter table test rename column id to num;

Table altered.

3:改列名
yang SQL>desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER(38)
 BANNER                                             VARCHAR2(32)

yang SQL>alter table test rename to t1;

Table altered.

4:更改表名
yang SQL>desc t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER(38)
 BANNER                                             VARCHAR2(32)

5:创建相同的表,在创建大表的时候，可以增加并发和关闭日志来提高效率
yang SQL>create table emp as select * from t1; //crate table  emp nologing parallel(degree 4)  as select * from t1;

Table created.

yang SQL>select * from emp order by 1;

       NUM BANNER
---------- --------------------------------
         1 one
         2 two7
         3 three
         4 four