TABLE:
An existing table can be modified even if it contains data. However, it is recommended that you create a table after putting sufficient thought into it.
ALTER TABLE table_name
[ADD (col_name col_datatype col_constraint,….)]
[ADD (table_constraint)]
[DROP CONSTRAINT constraint_name]
[MODIFY existing_col_name new col_col_datatype new_constraint new_default]
[DROP COLUMN existing_col_name]
[SET UNUSED COLUMN existing_col) name];
Table_name must be an existing table.
The rules for adding a column to the table are:
1) Can add any column without a NOT NULL specification.
2) Adding a NOT NULL column is possible in three steps.
- Add a column without NULL specification.
- Filling every row in that column with data.
- Modifying the column to be NOT NULL.
The rules for modifying the columns are:
1) Can increase a character column’s width any time.
2) Can increase the number of digits in a number at any time.
3) Can increase or decrease the number of decimal places in a number column at any time. Any reduction on precision and scale can be on empty columns only.
4) You can add only NOT NULL constraint using column constraints. Rest all constraints have to be specified as table constraints.
For adding three more columns to the EMP table.
ALTER TABLE emp
ADD (sal NUMBER (7, 2) CONSTRAINT SAL_GRT_0 CHECK (sal >0),
Mgr NUMBER (4),
Comm. NUMBER (9, 2));
For adding referential Integrity on mgr column
ALTER TABLE emp
ADD CONSTRAINT EMP_FMGR_KEY FOREIGN KEY (mgr) REFERENCES
EMP (empno);
For modifying the width of sal column
ALTER TABLE emp MODIFY (sal NUMBER (8, 2));
For dropping the FOREIGN KEY constraint on mgr
ALTER TABLE emp
DROP CONSTRAINT EMP_F_KEY;
Rules for dropping column
Oracle allows you to drop a column from a table. This can be done in two ways.
1) Marking the columns as unused and then later dropping them
ALTER TABLE emp SET UNUSED COLUMN comm.;
ALTER TABLE emp SET UNUSED (sal, hiredate);
Once all the required columns have been marked as unused, we can use the following command to remove the columns permanently. Columns once marked as unused cannot be recovered. Marking the columns as unused does not release the space occupied by them back to the database. Also until you drop these columns actually, they continue to count towards the absolute limit of 1000 columns per table. Also, if you mark a column of data type LONG as UNUSED, you cannot add another LONG column to the table until you actually drop the unused LONG column.
ALTER TABLE emp DROP UNUSED COLUMNS;
The advantage of the above mentioned steps is that marking the columns is much faster process than dropping the columns.
You can refer to the data dictionary table USER_UNUSED_COL_TABS to get information regarding the tables with columns marked as unused.
2) Dropping the columns directly
ALTER TABLE emp DROP COLUMN sal;
This command should be used with caution.
Note:
For DROP COLUMN command to work successfully, the table should be locked exclusively by the user giving the command.
When you drop a column
- All indexes defined on any of the target columns are also dropped.
- All constraints that reference a target column are removed.
SEQUENCE:
ALTER SEQUENCE s1
INCREMANT BY n1
MAXVALUE n3
CYCLE;
1) s1 is an existing sequence
2) To make the sequence start from a new number, drop the sequence and create again.
3) ALTER SEQUENCE has no effect on numbers already generated.
ALTER SEQUENCE s1
INCREMANT BY 5
MAXVALUE 25000
CYCLE;
