How can I determine in advance if a table with COMPRESS BASIC will or will not generate the ORA-39726 error?
SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 31 08:37:58 2023
Version 19.14.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected.
SQL>column banner_full format A71
SQL>select banner_full from v$version
2 /
BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
Given I have a table with COMPRESS BASIC that was created recently whose had a column added to it when I drop the new column then I am not receiving "ORA-39726: unsupported add/drop column operation on compressed tables" and the drop column is being silently converted into a SET UNUSED COLUMN.
This behavior is different than what Tom posted on AskTom, albeit back in 2006, but the 19c docs don't seem to suggest things have changed in this regard:
- 19c drop_column_clause SQL reference.
"You can set unused a column from a table that uses COMPRESS BASIC, but you cannot drop the column."
- 19c table_compression clauses Restrictions on Table Compression SQL reference.
"You cannot drop a column from a table that uses COMPRESS BASIC, although you can set such a column as unused."
Why am I not getting the ORA-39726 error in 19c?
Note: To actually drop the column, I then have to MOVE NOCOMPRESS the table and then DROP UNUSED COLUMNS.
SQL>create table t_new compress as select * from all_users
2 /
Table created.
SQL>alter table t_new add x number
2 /
Table altered.
SQL>alter table t_new drop column x
2 /
Table altered.
SQL>column table_name format A10
SQL>select * from user_unused_col_tabs
2 /
TABLE_NAME COUNT
---------- ----------
T_NEW 1
SQL>alter table t_new nocompress
2 /
Table altered.
SQL>alter table t_new drop unused columns
2 /
Table altered.
SQL>select * from user_unused_col_tabs
2 /
TABLE_NAME COUNT
---------- ----------
T_NEW 1
SQL>alter table t_new move nocompress
2 /
Table altered.
SQL>alter table t_new drop unused columns
2 /
Table altered.
SQL>select * from user_unused_col_tabs
2 /
no rows selected
SQL>alter table t_new compress
2 /
Table altered.
SQL>drop table t_new
2 /
Table dropped.
SQL>
However, if I repeat this in 19c with a table that was created previously (likely when the database was 12c), then I do get the ORA-39726 error.
Furthermore, I don't actually have to MOVE NOCOMPRESS the table, I can instead simply alter the table's data dictionary to NOCOMPRESS and then the DROP COLUMN is now allowed and actually drops the column (not just a silent UNUSED).
This too seems like it has changed as the NOCOMPRESS only applies to future direct-path inserts, so I don't understand how I can get away with only doing a data dictionary change without actually moving and uncompressing those table blocks.
Note: I cannot recreate the ORA-39726 when dropping columns with newly created tables in 19c; I can only recreate it within 19c using tables that were originally created prior to 19c.
Hence, the distinction of t_new and t_old table names in the examples.
I don't know how to show what Oracle version was in effect when a table was created (perhaps some block version in a block header) or even if it is relevant to this question.
How come a data dictionary NOCOMPRESS table alter is sufficient, and do I not have to MOVE NOCOMPRESS the table as previously required 18c db<>fiddle?
-- In the same 19c environment as above, but using a table created when the database was 12c.
SQL>alter table t_old add x number
2 /
Table altered.
SQL>update t_old set x = 1
2 /
3845 rows updated.
SQL>alter table t_old drop column x
2 /
alter table t_old drop column x
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
SQL>alter table t_old nocompress
2 /
Table altered.
SQL>alter table t_old drop column x
2 /
Table altered.
SQL>select * from user_unused_col_tabs
2 /
no rows selected
SQL>alter table t_old compress
2 /
Table altered.
SQL>
