Decrease the length of a column : Alter Table « Table « Oracle PL/SQL Tutorial






You can only decrease the length of a column if there are no rows in the table or all the columns contain null values.

create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

no rows selected

SQL>
SQL>
SQL> desc employee;
 Name                Null?    Type
 --------------------

 ID                  NOT NULL VARCHAR2(4)
 FIRST_NAME                   VARCHAR2(10)
 LAST_NAME                    VARCHAR2(10)
 START_DATE                   DATE
 END_DATE                     DATE
 SALARY                       NUMBER(8,2)
 CITY                         VARCHAR2(10)
 DESCRIPTION                  VARCHAR2(15)

SQL>
SQL>
SQL> ALTER TABLE employee
  2  MODIFY first_name VARCHAR2(3);

Table altered.

SQL>
SQL>
SQL> desc employee;
 Name             Null?    Type
 -----------------------------

 ID               NOT NULL VARCHAR2(4)
 FIRST_NAME                VARCHAR2(3)
 LAST_NAME                 VARCHAR2(10)
 START_DATE                DATE
 END_DATE                  DATE
 SALARY                    NUMBER(8,2)
 CITY                      VARCHAR2(10)
 DESCRIPTION               VARCHAR2(15)

SQL>
SQL> select * from Employee
  2  /

no rows selected

SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /

Table dropped.

SQL>








6.3.Alter Table
6.3.1.Altering a Table
6.3.2.Some of the aspects of a column you can modify using ALTER TABLE
6.3.3.Adding a Column
6.3.4.ADD initially_created DATE DEFAULT SYSDATE NOT NULL;
6.3.5.Changing the Size of a Column
6.3.6.Decrease the length of a column
6.3.7.Changing the Precision of a Numeric Column
6.3.8.Decrease the precision of a numeric column
6.3.9.Changing the Data Type of a Column
6.3.10.Fill data into new added table column
6.3.11.Changing the Default Value of a Column
6.3.12.Alter table to add new constraint for new added column
6.3.13.ALTER TABLE to DISABLE a CONSTRAINT
6.3.14.Alter table to add primary key
6.3.15.Alter table to add primary key across more than one columns
6.3.16.Dropping a Column
6.3.17.New added columns are empty
6.3.18.Alter table to add unique with tablespace and storage setting
6.3.19.Alter table to add primary key with tablespace and storage setting
6.3.20.alter table nologging