In Oracle, the alter table command is used to modify the table structure. Modify table structure includes, adding columns, constraints, changing data types and sizes, dropping and renaming columns.
Oracle Alter Table Add Column Syntax
Alter Table table_name add (new_column data_type(size));
Examples
In the following example, it will add the admission_date column to the student table.
Alter Table student add (admission_date date);
Example to add multiple columns in a table:
Alter Table student add (admission_date date, tc_submitted varchar2(1));
Adding a column with a default value example:
Alter Table student add (status varchar2(10) default 'ACTIVE');
Alter Table Modify Column Syntax
Alter Table table_name modify (column_name data_type(size));
Examples
Increase the size of a column:
Alter Table student modify (tc_submitted varchar2(3));
Changing data type of a column example:
Alter Table student modify (roll_no varchar2(20));
Make sure the column you are changing the data type having no data, else you will get the following error:
ORA-01439: column to be modified must be empty to change datatype
Alter Table Add Constraint Syntax
Alter Table table_name add constraint constraint_name constraint_type (columns-condition);
Examples
Adding a primary key constraint to the student table:
Alter Table student add constraint pk_student primary key (roll_no);
The primary key with multiple columns example:
Alter Table student add constraint pk_student primary key (stud_id, roll_no);
Adding a foreign key constraint to the student table:
Alter Table student add constraint fk_student roll_no references student_master(roll_no);
Dropping a constraint:
Alter table student drop constraint fk_student;
Alter Table Rename Column Syntax
Alter Table table_name rename column old_column_name to new_column_name;
Example
Alter Table student rename column st_name to student_name;
Alter Table Drop Column Syntax
Alter Table table_name drop column column_name;
Example
Alter Table student drop column status;
Note:
After altering a table structure, make a thumbrule to check for the invalid objects in the schema you are altering the table. Because if any PL/SQL procedure, function or trigger is depending on the table you are modifying, that PL/SQL object will be get invalid. The below is an example to check and recompile invalid objects in the current schema.
Check for Invalid Objects After Altering a Table in Oracle
The following is the SQL query to check for the invalid objects in the current schema.
Select * From user_objects Where status = 'INVALID';
In the output, you will get the list of PL/SQL procedures, functions, packages or triggers, etc if got invalid after altering the table structure.
Now you have to recompile those objects to work properly.
To do so, you can use the database tools such as Oracle SQL Developer, Toad, and PL/SQL developer to recompile the invalid objects easily because these tools having an option to do it.
But if you don't have installed or you are having the only command-line tools such as SQLcl and SQL*Plus to interact with Oracle database then you can run the following PL/SQL program to compile the invalid objects in the current schema.
PL/SQL Program: Compile All Invalid objects of the schema
Set Serveroutput On Size 1000000
Begin
For cur In (
Select
object_name,
object_type,
Decode(object_type, 'PACKAGE', 1, 'PACKAGE BODY', 2, 3) As recompile_order
From
user_objects
Where
status != 'VALID'
Order By
3
) Loop
Begin
If cur.object_type = 'PACKAGE' Then
Execute Immediate 'ALTER '
|| cur.object_type
|| ' '
|| cur.object_name
|| ' COMPILE';
Elsif cur.object_type = 'PACKAGE BODY' Then
Execute Immediate 'ALTER PACKAGE '
||
' '
|| cur.object_name
|| ' COMPILE BODY';
Else
EXECUTE Immediate 'Alter '
|| cur.object_type
|| ' '|| cur.object_name
|| ' Compile';
End If;
Exception
When Others Then
dbms_output.put_line(cur.object_type
|| ' : '
|| cur.object_name);
End;
End Loop;
End;
/
