Oracle table columns

Modifying the table structures

create table tq84_col_test (
   col_one   number
);

Add one new column

alter table tq84_col_test add col_two varchar2(10);

Add muliple columns at once

alter table tq84_col_test add (
   col_three   date,
   col_four    number not null
);

Change a column nullability:

alter table tq84_col_test modify col_three not null;

A column that contains null values only can be converted to another data type.

If the column already has data, Oracle will throw error ORA-01439: column to be modified must be empty to change datatype.

alter table tq84_col_test modify col_four varchar2(10);

Modifying multiple columns at once:

alter table tq84_col_test modify (
   col_one    number(10, 2),
   col_two    varchar2(20)  
);

Renaming a column

alter table tq84_col_test rename column col_four to col_text;

Dropping a column:

alter table tq84_col_test drop column col_two;

In order to drop multiple columns at once, the column names need to be enclosed in parantheses and the keyword column omitted:

ater table tq84_xyz drop (col_one, col_two, col_three,…);

Before dropping a column, it might be considered, especially on large tables, to first mark it as unused (which is way faster then dropping it directly) and later drop it «physically», when the database is not heavily used:

alter table tq84_col_test set unused (col_three);

select * from user_tab_cols
where
   table_name    = 'TQ84_COL_TEST' and
   hidden_column = 'YES';

// … later …:

alter table tq84_col_test drop unused columns;

Find columns that might be nullable

After (fully) analyzing a table with dbms_stats.gather_table_stats, it is possible to find columns that are nullable but have no null values in them:

select
   col.column_name
from
    user_tab_columns col
where
   col.table_name = 'DATA' and
   col.nullable   = 'Y'    and
   col.num_nulls  =  0;

The following statement generates an alter table statement with which all such columns can be changed to not null:

select
  'alter table ' || col.table_name || ' modify (' || 
   listagg(col.column_name || ' not null', ', ')  ||
  ')'  stmt
from
    user_tab_columns col
where
   col.table_name = 'DATA' and
   col.nullable   = 'Y'    and
   col.num_nulls  =  0
group by
   col.table_name;

Invisible columns

Gone here

Commenting columns

It's possible to add a comment for a column (for example to document its intended purpose):

comment on column rene.tab_xyz.col_1  is '…';

These comments are stored in the data dictionary and can be queried from either dba_col_comments, all_col_comments or user_col_comments.

Maximum number of columns

As of 21c, the maximum number of columns in a table or view is 1000.

In 23c, this limit was upped to 4096 (which requires the init parameter max_columns to be set to extended).

However, a select statement can return up to 8160 columns after which the error message ORA-00913: too many values is thrown.

The maximum number of columns that can be stored in a data block is 255.