Oracle Tips - DDL

1. Add column to a table

ALTER TABLE table_name
ADD column_name data_type constraint;

2. To add multiple columns to a table -

ALTER TABLE table_name
ADD (
column_name data_type constraint,
column_name2 data_type constraint
);


3. Rename column

ALTER TABLE table_name
RENAME COLUMN column_name to column_new_name;

4. Modify column

ALTER TABLE table_name
MODIFY ( column_name data_type constraint);

5. Drop column

ALTER TABLE table_name
DROP COLUMN column_name;

6. Drop multiple columns

ALTER TABLE table_name
DROP (
column_name1, column_name2, ....);

7. Index Management

To see the constraints of a table:

select table_name, constraint_name, constraint_type from user_constraints;

To see the index of a table

select table_name, index_name, column_name from user_ind_columns;

Create index

create [bitmap] index index_name on table_name
(column_name)

Find and Rebuild Index

select * from user_indexes
Alter Index rebuild

Analyze index and table

Analyze index [index-name] [compute/delete/validate] statistics
Analyze table [table-name] [compute/delete/validate] statistics

8. Manage privileges on table

GRANT select, insert, update, delete on SCHEMA.TABLE_NAME to ROLE/USER

9. Create Synonym of table

CREATE OR REPLACE [public] SYNONYM SYNONYM_NAME for SCHEMA.TABLE_NAME

Check the synonyms created by the user

select * from all_synonyms

10. Manage Constraint
To see the constraints of a table:

select table_name, constraint_name, constraint_type from user_constraints;

Drop constraint

alter table table_name
drop constraint constraint_name

Add Foreign Key constraint to a table

Alter table Add Constraint
Foreign Key (column) References
On cascade;

11. Create CHECK constraint on a column

ALTER TABLE TABLE_NAME
ADD CONSTRAINT CHK_Y_N
CHECK (COLUMN_NAME IN ('Y', 'N'))
/

12. Partitioned Table



Resources:
https://www1.columbia.edu/sec/acis/dba/oracle/faq-sql.html
http://www.dba-oracle.com/ - has useful queries
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:969054700346631425 - Bulk move
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:273215737113 - Update columns from other table
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:974699100346868838 - should we use db user or application user?
http://www.adp-gmbh.ch/ora/misc/integrity_constraints.html - Oracle's Integrity Constraints
http://jenniferlinca.wordpress.com/ - Blog contains good Oracle stuffs
http://forums.oracle.com/forums/message.jspa?messageID=2348825 - Fun to read
http://www.dba-oracle.com/art_9i_indexing.htm - Performance tuning with index
http://www.orafaq.com/node/855 - Common errors using Outer Join
http://marist89.blogspot.com/ - Wonderful quote from the site - "The trouble with the world is that the stupid are cocksure and the intelligent are full of doubt." --Bertrand Russell. A good database blog
http://www.orafaq.com/forum/t/54714/0/ - Batch delete
http://www.dba-oracle.com/oracle_news/2004_1_19_using_function_based_indexes%20.htm - Function based index
http://www.oracle-dba-online.com/sql/oracle_table_partition.htm - Partitioned table