How to Alter Table in Oracle

-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 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 Change Data Type

We can change the datatype  of any column using alter table  modify command

ALTER TABLE <table_name>
MODIFY (<column_name>  <new_data_type>);

 

Add a new column/Drop a column/rename a column

We can add 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 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

How to alter table add column oracle

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 are 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

Leave a Reply