Hi,
On Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit (probably on 11gR2 too) I can not rename column when extended stats are calculated manually or auto-magically by Oracle db itself (in case primary key or materialized view etc.. is (or was) there).
See simple demonstration below. Even when extended stat is flagged as DROPPABLE I can not find syntax how to DROP/DELETE that extended stats so I can rename column afterwards.
> COLUMN TABLE_NAME FORMAT A12
> COLUMN COLUMN_NAME FORMAT A32
> COLUMN DATA_DEFAULT FORMAT A40
> COLUMN EXTENSION A32
> COLUMN EXTENSION_NAME A32
> REM == Create table
> create table FRUITS(APPLE number, ORANGE number)
table FRUITS created.
> prompt
> REM == See cols
> select table_name, column_name, data_default, VIRTUAL_COLUMN, HIDDEN_COLUMN
from user_tab_cols where table_name='FRUITS'
TABLE_NAME COLUMN_NAME DATA_DEFAULT VIRTUAL_COLUMN HIDDEN_COLUMN
------------ -------------------------------- ---------------------------------------- -------------- -------------
FRUITS APPLE NO NO
FRUITS ORANGE NO NO
> prompt
> REM == Calculate EXTENDED stats (if primary key, view etc.. defined oracle database does itself sometimes!)
> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>null, tabname=>'FRUITS', method_opt=>'FOR COLUMNS (APPLE, ORANGE) SIZE AUTO')
anonymous block completed
> prompt
> REM == See new hidden column based on extended stats
> select table_name, column_name, data_default, VIRTUAL_COLUMN, HIDDEN_COLUMN
from user_tab_cols where table_name='FRUITS'
TABLE_NAME COLUMN_NAME DATA_DEFAULT VIRTUAL_COLUMN HIDDEN_COLUMN
------------ -------------------------------- ---------------------------------------- -------------- -------------
FRUITS APPLE NO NO
FRUITS ORANGE NO NO
FRUITS SYS_STUL_A8_XOJI866YW$FOZG#Y95 SYS_OP_COMBINED_HASH("APPLE","ORANGE") YES YES
> prompt
> REM == See stats reference for new hidden column
> SELECT TABLE_NAME, DROPPABLE, SUBSTR(EXTENSION_NAME,1,35) EXTENSION_NAME, SUBSTR(EXTENSION,1,35) EXTENSION
FROM dba_stat_extensions WHERE table_name = 'FRUITS'
TABLE_NAME DROPPABLE EXTENSION_NAME EXTENSION
------------ --------- ----------------------------------- --------------------------------------------------------------------------------
FRUITS YES SYS_STUL_A8_XOJI866YW$FOZG#Y95 ("APPLE","ORANGE")
> SELECT table_name, COLUMN_NAME FROM user_tab_col_statistics WHERE table_name = 'FRUITS'
TABLE_NAME COLUMN_NAME
------------ --------------------------------
FRUITS SYS_STUL_A8_XOJI866YW$FOZG#Y95
> prompt
> REM == An attempt to get rid of stats and stats related hidden column
> exec DBMS_STATS.delete_column_stats( ownname=>null, tabname=>'FRUITS', colname => 'SYS_STUL_A8_XOJI866YW$FOZG#Y95')
anonymous block completed
> SELECT table_name, COLUMN_NAME FROM user_tab_col_statistics WHERE table_name = 'FRUITS'
no rows selected
> exec DBMS_STATS.DELETE_TABLE_STATS (ownname=>null, tabname=>'FRUITS')
anonymous block completed
> SELECT TABLE_NAME, DROPPABLE, SUBSTR(EXTENSION_NAME,1,35) EXTENSION_NAME, SUBSTR(EXTENSION,1,35) EXTENSION
FROM dba_stat_extensions WHERE table_name = 'FRUITS'
TABLE_NAME DROPPABLE EXTENSION_NAME EXTENSION
------------ --------- ----------------------------------- --------------------------------------------------------------------------------
FRUITS YES SYS_STUL_A8_XOJI866YW$FOZG#Y95 ("APPLE","ORANGE")
> exec DBMS_STATS.DROP_EXTENDED_STATS (ownname=>null, tabname=>'FRUITS', expresion='(APPLE,ORANGE)')
Error starting at line : 34 in command -
exec DBMS_STATS.DROP_EXTENDED_STATS (ownname=>null, tabname=>'FRUITS', expresion='(APPLE,ORANGE)')
Error report -
ORA-06550: line 1, column 73:
PLS-00201: identifier 'EXPRESION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
> exec DBMS_STATS.DROP_EXTENDED_STATS (ownname=>null, tabname=>'FRUITS', expresion='("APPLE","ORANGE")')
Error starting at line : 35 in command -
exec DBMS_STATS.DROP_EXTENDED_STATS (ownname=>null, tabname=>'FRUITS', expresion='("APPLE","ORANGE")')
Error report -
ORA-06550: line 1, column 73:
PLS-00201: identifier 'EXPRESION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
> prompt
> REM == Stats related column still remains!
> SELECT TABLE_NAME, DROPPABLE, SUBSTR(EXTENSION_NAME,1,35) EXTENSION_NAME, SUBSTR(EXTENSION,1,35) EXTENSION
FROM dba_stat_extensions WHERE table_name = 'FRUITS'
TABLE_NAME DROPPABLE EXTENSION_NAME EXTENSION
------------ --------- ----------------------------------- --------------------------------------------------------------------------------
FRUITS YES SYS_STUL_A8_XOJI866YW$FOZG#Y95 ("APPLE","ORANGE")
> prompt
> REM == Hidden columns prevents various operations on table however :(
> alter table FRUITS rename column ORANGE to LEMON
Error starting at line : 42 in command -
alter table FRUITS rename column ORANGE to LEMON
Error report -
SQL Error: ORA-54032: column to be renamed is used in a virtual column expression
There is 'dirty' solution to rename system generated column SYS_STUL_A8_XOJI866YW$FOZG#Y95 to "MANGO" for example and then drop that MANGO column, which enables me to rename ORANGE column, but I find it as not recommended approach.
So question stays how to drop extended stats in example above with Oracle DML/DDL commands regularly?