Snippet://Oracle/Table

Lookup All Table
SELECT * FROM TAB;

Create Table
CREATE TABLE table_name
(
id NUMBER(6,0),
colname1 VARCHAR2(20 BYTE) CONSTRAINT constname_nn NOT NULL ENABLE,
colname2 NUMBER(8,2),
CONSTRAINT t_id_PK PRIMARY KEY ("id") ENABLE,
CONSTRAINT t_col1_UK UNIQUE ("colname1") ENABLE,
CONSTRAINT t_col2_MIN CHECK (colname2>0) ENABLE,
CONSTRAINT t_col_FK FOREIGN KEY ("colname")
REFERENCES other_table_name ("primarycol") ENABLE
);
COMMENT ON COLUMN table_name.colname IS 'something';
CREATE INDEX index_name ON table_name ("colname")

Use "Truncate" To Delete All Rows of a Table
The truncate command will permanently delete all rows from a table. No need for commit.
truncate table table_name;

Use "Alter Table" To Change Table Definition
Use the Alter Table command to change the definition of a table. You can either add another column or change the definition of an existing column.
alter table table_name add (colname varchar2(24));
alter table table_name modify (colname varchar2(32));

Constraints to Table Definition
There are four kinds of constraints
1. not null
2. check value
3. unique
4. foreign key.

To disable a constraint, use the alter table command. To enable a disabled constraint, again use the alter table command. The following examples disables and then re-enables the salary check condition

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

Delete Table
DROP TABLE table_name;
DROP TABLE table_name PURGE; -- To delete and purge from the recycle bin

Create Sequence
CREATE SEQUENCE name_seq INCREMENT BY 1 START WITH 1;

How To Use Sequence
INSERT INTO table_name (id,...) VALUES (name_seq.NextVal,...);
INSERT INTO table_name VALUES (name_seq.NextVal,...);

NOTE: This is actually the best way to to use auto increment for a primary key. The other way:

INSERT INTO table_name (id, ...)
(SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, ... FROM table_name);

won't work because if first session INSERTS something, then another session won't be able to insert anything until session 1 commits transaction. After that when session 2 tries to INSERT, a unique contraints voilation will occur.