We often need to Alter Table in Oracle depending on Requirement changes and upgrade. Here are the some of the important points about Alter table statement
- You alter or modify a table using the ALTER TABLE statement.
- The table must be contained in your schema To alter a table, or you should either have ALTER object privilege for the table or the ALTER ANY TABLE system privilege.
- If a view, materialized view, trigger, function-based index, check constraint, function, procedure of package depends on a base table, the alteration of the base table or its columns can affect the dependent object For example plsql objects become invalid if the dependent table object is changed and you have to make them valid again
Modify physical characteristics (INITRANS or storage parameters)
We can modify the storage parameter of the table using Alter table statement. We can modify inittrans like this
ALTER TABLE TABLE_NAME INITRANS 10;
Moving the table to new segments or tablespace
We can move the non-partition table or partition of the table to a new segment or new tablespace if required
Alter table table_name move tablespace <tablespace name>
We can even use the move command to change any storage parameter of the tables which are not modified using alter table command
Alter Table in oracle to Change Data Type
We can change the data type of any column using alter table modify command
ALTER TABLE <table_name> MODIFY (<column_name> <new_data_type>);
Before
SQL> desc emp
Name Null? Type
----------------------------------------- -------- -----------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
After
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(20)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
modify column datatype in oracle
Add a new column/Drop a column/rename a column
We can add a new column to the table definition
Using alter table add command
ALTER TABLE <table_name>
ADD (<New column_name> <new_data_type>);
Oracle allows you to rename existing columns in a table. Use the RENAME COLUMN clause of the ALTER TABLE statement to rename a column
Oracle allows you to drop the column in the table using the command
Alter table <table_name> drop column;
Please check the below article to learn about these in details
Set A Column Unused and Drop the unused column
if you are concerned about the resource consumed in dropping the column then you can use the ALTER TABLE…SET UNUSED statement.
This statement marks one or more columns as unused but does not actually remove the target column data or restore the disk space occupied by these columns.
- A column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.
Example
ALTER TABLE <table_name>
SET UNUSED COLUMN <column_name>;
We can drop the unused column later on when the resource is lower using the command
ALTER TABLE <table_name>
Drop UNUSED COLUMN ;
Add, modify or drop integrity constraints associated with the table or you can enable/disable the constraints also
Add constraints is done using alter table add constraints
ALTER TABLE EMP ADD CONSTRAINT EMP_FK FOREIGN KEY (DEPT_NO) REFERENCES DEPT(DEPT_NO);
Dropping Constraints – is done using the
ALTER TABLE DROP CONSTRAINT <constraint_name> command;
Enabling/Disabling Constraints – Constraints can be created in DISABLE/ENABLE mode or can be disabled or enabled using the command
ALTER TABLE ENABLE/DISABLE CONSTRAINT <constraint_name>
Rename table name
Oracle allows you to rename the table name also
Rename <table name> to <new table name>;
Alteration of table cache/no-cache, Compression, parallelism
Oracle allows Alteration of table cache/no-cache, Compression, parallelism
Related Articles
List All Tables in Oracle
create table oracle
ora-20005: object statistics are locked
alter table move
how to check all constraints on a table in oracle
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm