Oracle Alter Table command syntax.

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;
/

Reference:

Related Tutorial

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

Leave a Reply