Oracle基础学习笔记（一）(Create Table, ALTER, RENAME, SEQUENCE)

1)

CREATE [GLOBAL TEMPORARY] TABLE name (

 

)

[ON COMMIT {DELETE|PRESERVE} ROWS]

TABLESPACE name;

 

ON COMMIT -- to set the duration of the rows persist in the temporary table

DELETE(by default) -- end of transaction

PRESERVE -- end of user session

 

2)

ALTER TABLE tab_name ADD col_name TYPE CONSTRAINT;

ALTER TABLE tab_name ADD (col_name AS (low_salary+high_salary)); (add a virtual column, new in Oracle 11g, the virtual column refers only to existing columns)

ALTER TABLE tab_name MODIFY col_name TYPE CONSTRAINT;

ALTER TABLE tab_name DROP COLUMN col_name;

ALTER TABLE tab_name ADD CONSTRAINT cons_name CONSTRAINT;

ALTER TABLE tab_name ADD CONSTRAINT cons_name_FK col_name REFERENCES tab_name(col_name) ON DELETE CASCADE;

ALTER TABLE tab_name ADD CONSTRAINT cons_name_FK col_name REFERENCES tab_name(col_name) ON DELETE SET NULL;

ALTER TABLE tab_name MODIFY col_name CONSTRAINT cons_name CONSTRAINT; (add constraint by MODIFY clause);

ALTER TABLE tab_name DROP CONSTRAINT cons_name;

 

Disabling a Constraint

ALTER TABLE tab_name ADD CONSTRAINT cons_name UNIQUE(col_name) DISABLE;

ALTER TABLE tab_name DISABLE CONSTRAINT cons_name; (disable existing constraint)

 

Enable a Constraint

ALTER TABLE tab_name ENABLE CONSTRAINT cons_name; (all rows should satisfy the constraint)

ALTER TABLE tab_name ENABLE NOVALIDATE CONSTRAINT cons_name; (only apply for new rows, by default it is ENABLE VALIDATE)

 

Deferred Constraints

(the constraint is enforces only when a transaction is committed. INITIALLY IMMEDIATE(by default) or INITIALLY DEFERRED)

ALTER TABLE tab_name ADD CONSTRAINT cons_name UNIQUE(col_name) DEFERRABLE INITIALLY DEFERRED;

 

3)

RENAME tab_name TO new_tab_name;

ALTER TABLE tab_name RENAME col_name TO new_col_name;

 

4)

CREATE SEQUENCE seq_name

[START WITH start_num]

[INCREMENT BY increment_num]

[{MAXVALUE maximum_num | NOMAXVALUE}]

[{MINVALUE minimum_num | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE cache_num | NOCACHE}]

[{ORDER | NOORDER}] -- guarantee the integers are generated in the order by the request,used in the real application clusters.

(Real Application Clusters are multiple database servers that share the same memory)

 

ALTER SEQUENCE seq_name INCREMENT BY 2;

seq_name.nextval

seq_name.currval