表是数据库中最基本的储存数据逻辑单元,它由行和列组成,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