Create table [schema.]table [organization heap] (
column datatype [DEFAULT expression]
[,column datatype [DEFAULT expression]…);
|
|
|
|
建表 | Create table hr.emp(empno number(4), ename varchar2(10),hiredate date default trunc(sysdate), salary number(9,2) default 1234567.89); |
|
|
子查询建表 | Copy table employees_copy as select * from employees Where 0=1; insert into employees_copy select * from employees ; | Not null和check应用到新表,其它索引类约束PK,Unique,Foreign KEY丢失。 | 日常修改行内容前,可对范围内容备份 |
增列 | Alter table emp add(job_id number); |
|
|
改列名 | Alter table emp rename column hiredate to recruited; |
|
|
改列类型 | Alter table emp modify (job_id number(4,2) default 0.05); |
|
|
标记不使用列 | Alter table emp set unused column job_id; | 忙时设置 |
|
删除标记不可用列 | Alter table emp drop unused columns; | 闲时删除 |
|
删列 | Alter table emp drop column comm; |
|
|
截断表内容 | Truncate table emp | 降低高水位线释放空间 |
|
表只读
| Alter table emp read only; |
|
|
删表 | Drop table [schema.]tablename; |
|
|
建临时表 | Create global temporary table emp2(empno number(4));
Create global temporary table tmp_emps2 on commit preserve rows as Select * from employees | 表结构能看到,但会话隔离是原则 再分 [on commit {DELETE|PRESERVE} ROWS] 提交|会话结束 | 会话临时表不经过redo不落盘,快速 |
|
|
|
|