Cannot Drop Unused Columns Using dbms_redefinition Without PK And Compressed Table (Doc ID 1233204.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1.0 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
Updated to 16-Jan-2013.


Symptoms

It is not possible to drop the unused column when using table compression and
dbms_redefinition without a Primary Key, indeed, this is not dropped neither once that the alter
table statement completes successfully.

For example:

SQL> create table t_test(a number, b number) compress for all operations;

Table created.

SQL> alter table t_test add (m_row$$ varchar2(255));

Table altered.

SQL> select owner, table_name, column_name, hidden_column, virtual_column
from dba_tab_cols where owner = 'U1' and table_name = 'T_TEST';

OWNER TABLE_NAME COLUMN_NAME HID VIR
----- ---------- ----------- --- ---
U1 T_TEST A NO NO
U1 T_TEST B NO NO
U1 T_TEST M_ROW$$ NO NO

SQL> insert into t_test (a, b, m_row$$) values (1,1,'sss');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t_test set unused (m_row$$);

Table altered.

SQL> select owner, table_name, column_name, hidden_column, virtual_column
from dba_tab_cols where owner = 'U1' and table_name = 'T_TEST';

OWNER TABLE_NAME COLUMN_NAME HID VIR
----- ---------- ----------- --- ---
U1 T_TEST A NO NO
U1 T_TEST B NO NO
U1 T_TEST SYS_C00003_10100808:28:13$ YES NO

SQL> alter table t_test drop unused columns;

Table altered.

SQL> select owner, table_name, column_name, hidden_column, virtual_column
from dba_tab_cols where owner = 'U1' and table_name = 'T_TEST';

OWNER TABLE_NAME COLUMN_NAME HID VIR
----- ---------- ----------- --- ---
U1 T_TEST A NO NO
U1 T_TEST B NO NO
U1 T_TEST SYS_C00003_10100808:28:13$ YES NO


For example, using dbms_redefinition.cons_use_rowid:

SQL> CREATE TABLE REDEF_TEST
(
STATEMENT_DATE DATE,
LOAD_COMPLETE_YN VARCHAR2(1 BYTE),
TOTAL_LINES VARCHAR2(9 BYTE)
);

Table created.

SQL> select owner, table_name, tablespace_name, compression, compress_for
from dba_tables
where owner = 'MEDSDBA'
and table_name = 'REDEF_TEST';

OWNER TABLE_NAME TABLESPACE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ ------------------------------ -------- ------------
MEDSDBA REDEF_TEST DBTOOLS DISABLED

SQL> select owner, table_name, column_name, hidden_column, virtual_column from dba_tab_cols where owner = 'MEDSDBA' and table_name = 'REDEF_TEST';

OWNER TABLE_NAME COLUMN_NAME HID VIR
------------------------------ ------------------------------ ------------------------------ --- ---
MEDSDBA REDEF_TEST STATEMENT_DATE NO NO
MEDSDBA REDEF_TEST LOAD_COMPLETE_YN NO NO
MEDSDBA REDEF_TEST TOTAL_LINES NO NO

SQL> -- Check to see whether redefinition is allowed for our table.
SQL> DECLARE
-- No PK so use ROWID to redefine
p_option PLS_INTEGER := dbms_redefinition.cons_use_rowid;
p_owner varchar2(30) := 'MEDSDBA';
p_tname varchar2(30) := 'REDEF_TEST';
BEGIN
dbms_redefinition.can_redef_table(p_owner, p_tname, p_option);
dbms_output.put_line('Option returned : '||p_option);
END;

PL/SQL procedure successfully completed.

SQL> -- Create interim table for start_redef in a new tablespace with compression.
SQL> CREATE TABLE REDEF_TEST_I
( STATEMENT_DATE DATE,
LOAD_COMPLETE_YN VARCHAR2(1),
TOTAL_LINES VARCHAR2(9)
) TABLESPACE PART_HIST_TAB_TS MONITORING
COMPRESS FOR ALL OPERATIONS;

Table created.

SQL> -- Start redef, copy dependants in this case there are none and sync and finish.
SQL> DECLARE
num_errors pls_integer;
BEGIN
dbms_redefinition.start_redef_table(uname => 'MEDSDBA',
orig_table => 'REDEF_TEST',
int_table => 'REDEF_TEST_I',
col_mapping => NULL,
options_flag => dbms_redefinition.cons_use_rowid);
dbms_redefinition.copy_table_dependents(uname => 'MEDSDBA',
orig_table => 'REDEF_TEST',
int_table => 'REDEF_TEST_I',
copy_indexes => dbms_redefinition.cons_orig_params,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => TRUE,
num_errors => num_errors);
dbms_output.put_line('Number of Errors: '||num_errors);
dbms_redefinition.sync_interim_table(uname => 'MEDSDBA',
orig_table => 'REDEF_TEST',
int_table => 'REDEF_TEST_I');
dbms_redefinition.finish_redef_table(uname => 'MEDSDBA',
orig_table => 'REDEF_TEST',
int_table => 'REDEF_TEST_I');
END;

PL/SQL procedure successfully completed.

SQL> -- Drop interim table.
SQL> drop table REDEF_TEST_I purge;

Table dropped.

SQL> -- Check columns after redefinition and new HIDDEN column SYS_C00004_10091412:35:56$.
SQL> select owner, table_name, column_name, hidden_column, virtual_column from dba_tab_cols where owner = 'MEDSDBA' and table_name = 'REDEF_TEST';

OWNER TABLE_NAME COLUMN_NAME HID VIR
------------------------------ ------------------------------ ------------------------------ --- ---
MEDSDBA REDEF_TEST STATEMENT_DATE NO NO
MEDSDBA REDEF_TEST LOAD_COMPLETE_YN NO NO
MEDSDBA REDEF_TEST TOTAL_LINES NO NO
MEDSDBA REDEF_TEST SYS_C00004_10092012:34:52$ YES NO

SQL> -- Check columns flagged as unused and our table shows up with a count of 1.
SQL> select * from dba_unused_col_tabs where owner = 'MEDSDBA';

OWNER TABLE_NAME COUNT
------------------------------ ------------------------------ ----------
MEDSDBA REDEF_TEST 1

SQL> -- Try and alter the table to drop unused columns.
SQL> alter table redef_test drop unused columns;

Table altered.

SQL> -- Check after DROP UNUSED table alter is performed.
SQL> select owner, table_name, column_name, hidden_column, virtual_column from dba_tab_cols where owner = 'MEDSDBA' and table_name = 'REDEF_TEST';

OWNER TABLE_NAME COLUMN_NAME HID VIR
------------------------------ ------------------------------ ------------------------------ --- ---
MEDSDBA REDEF_TEST STATEMENT_DATE NO NO
MEDSDBA REDEF_TEST LOAD_COMPLETE_YN NO NO
MEDSDBA REDEF_TEST TOTAL_LINES NO NO
MEDSDBA REDEF_TEST SYS_C00004_10092012:34:52$ YES NO

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms