This week a colleaque asked me for help while droping a table column. Inside of a PL/SQL procedure a table column has to be droped to prepare the table for an partition exchange. But while execute the partition exchange the procedure always returns an error.

Analyzing the code and execute the code step by step we found out that droping the table column will not work as expected. After droping the table column we will not get any error, but the table column will reside into the table as a hidden column with an internal generated name.

We created the following testcase to reprocedure it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE RAINER_RH(
    a NUMBER
  , b NUMBER
  , c NUMBER
);
 
SELECT TABLE_NAME||'['||COLUMN_NAME||']' AS NAME
     , COLUMN_ID
     , HIDDEN_COLUMN
     , VIRTUAL_COLUMN
     , INTERNAL_COLUMN_ID
FROM  USER_TAB_COLS
WHERE TABLE_NAME = 'RAINER_RH'
ORDER BY 2
;
 
ALTER TABLE RAINER_RH DROP COLUMN B;
 
SELECT TABLE_NAME||'['||COLUMN_NAME||']' AS NAME
     , COLUMN_ID
     , HIDDEN_COLUMN
     , VIRTUAL_COLUMN
     , INTERNAL_COLUMN_ID
FROM  USER_TAB_COLS
WHERE TABLE_NAME = 'RAINER_RH'
ORDER BY 2
;

will generate the following output:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

TABLE created.
 
NAME                                  COLUMN_ID HID VIR INTERNAL_COLUMN_ID
------------------------------------ ---------- --- --- ------------------
RAINER_RH[A]                                  1 NO  NO                   1
RAINER_RH[B]                                  2 NO  NO                   2
RAINER_RH[C]                                  3 NO  NO                   3
 
TABLE altered.
 
NAME                                  COLUMN_ID HID VIR INTERNAL_COLUMN_ID
------------------------------------ ---------- --- --- ------------------
RAINER_RH[A]                                  1 NO  NO                   1
RAINER_RH[C]                                  2 NO  NO                   <strong>3</strong>
RAINER_RH[SYS_C00002_17111217:36:59$]           <strong>YES</strong> NO                   <strong>2</strong>

Looking at Oracle Documentation I found a hint at Restrictions on Dropping Columns:

You can set unused a column from a table that uses COMPRESS BASIC, but you cannot drop the column. However, all clauses of the drop_column_clause are valid for tables that use ROW STORE COMPRESS ADVANCED. See the semantics for table_compression for more information.

In our case all tables in the user’s tablespaces are created with „ROW STORE COMPRESS ADVANCED“. Therefore the table in our testcase above was also created with „ROW STORE COMPRESS ADVANCED“.

However, if we create the table with the NOCOMPRESS option, everything works fine and as expected. The column will be removed.

So, reading this part of the documentation: all „valid“ drop_table_columns clauses seem to be those, they will not result any error. But, their behaviour seems to be more or less unexpected.

Have Fun

Rainer