Define columns with their data types, precision, scale, and constraints during table creation. Column definitions include the column name, data type, size specifications, and optional constraints like NOT NULL.

CREATE TABLE table_column
(
    pkid NUMBER(1,0) NOT NULL
,   code VARCHAR2(2) NOT NULL
);

Verify column creation by querying the data dictionary view USER_TAB_COLUMNS, which contains metadata about all columns in your schema:

SELECT table_name, column_name, data_type, data_length, data_precision, data_scale, nullable
FROM user_tab_columns
WHERE upper(table_name) = 'TABLE_COLUMN';

Query result:

| TABLE_NAME   | COLUMN_NAME | DATA_TYPE | DATA_LENGTH | DATA_PRECISION | DATA_SCALE | NULLABLE |
+--------------+-------------+-----------+-------------+----------------+------------+----------+
| TABLE_COLUMN | PKID        | NUMBER    | 22          | 1              | 0          | N        |
| TABLE_COLUMN | CODE        | VARCHAR2  | 2           |                |            | N        |

Note: DATA_LENGTH shows the internal storage size (22 bytes for NUMBER), while DATA_PRECISION and DATA_SCALE show the defined numeric precision and scale.

Use ALTER TABLE with the ADD clause to add new columns to existing tables. New columns are always added at the end of the table structure and are initially NULL for existing rows unless a DEFAULT value is specified.

1. Add a Single Column
   Add a single column using the simple syntax without parentheses:
   

   ALTER TABLE table_column ADD desc1 VARCHAR2(50);
   

   Alternatively, use parentheses for consistency with multi-column operations:
   

   ALTER TABLE table_column ADD
   (
       desc2 VARCHAR2(50)
   );
   

2. Add Multiple Columns
   Add multiple columns in a single statement for better performance and transaction management:
   

   ALTER TABLE table_column ADD
   (
       desc3 VARCHAR2(50)
   ,   desc4 VARCHAR2(50)
   );
   

Use ALTER TABLE with the MODIFY clause to change column properties such as data type, size, default values, or NULL/NOT NULL constraints. Some modifications require the column to be empty or have compatible data.

1. Modify a Single Column
   Modify a single column to change its size, data type, or constraints:
   

   ALTER TABLE table_column MODIFY CODE VARCHAR2(10);
   

   Alternative syntax using parentheses:
   

   ALTER TABLE table_column MODIFY
   (
       code VARCHAR2(10)
   );
   

   Note: You can only increase VARCHAR2 size, or decrease it if all existing data fits in the new size.
   
2. Modify Multiple Columns
   Modify multiple columns simultaneously for better performance:
   

   ALTER TABLE table_column MODIFY
   (
       pkid NUMBER(10,0)
   ,   code VARCHAR2(10)
   );
   

   Note: Data type changes may require the column to be empty, or the new type must be compatible with existing data.
   

Use ALTER TABLE with RENAME COLUMN to change column names. This operation updates the column name in the data dictionary and all dependent objects like indexes, constraints, and views.

ALTER TABLE table_column RENAME COLUMN CODE TO CODE_NEW_NAME;

Impact: Renaming columns may invalidate dependent database objects like views, stored procedures, and triggers that reference the old column name. Plan to recompile or update these objects after renaming.

Use ALTER TABLE with DROP to permanently remove columns from tables. This operation cannot be undone and will free up storage space. Dropping columns also removes associated indexes and constraints.

1. Drop a Single Column
   Drop a single column using the COLUMN keyword:
   

   ALTER TABLE table_column DROP COLUMN desc1;
   

   Alternative syntax without the COLUMN keyword:
   

   ALTER TABLE table_column DROP
   (
       desc2
   );
   

   Note: For large tables, consider using SET UNUSED followed by DROP UNUSED COLUMNS for faster operation during business hours.
   
2. Drop Multiple Columns
   Drop multiple columns in a single operation for better performance:
   

   ALTER TABLE table_column DROP
   (
       desc3
   ,   desc4
   );