Hello
I have problem with automatically created extended statistics - they prevent from rename column:
-- rename column
alter table zpravy rename column odeslal to odeslal_old;
Error at line 1
ORA-54032: column to be renamed is used in a virtual column expression
-- extended statistics
SELECT EXTENSION_NAME, EXTENSION
FROM dba_stat_extensions
WHERE table_name='ZPRAVY' AND owner='M7ZULIMS';
EXTENSION_NAME EXTENSION
------------------------------------------------------------------- ------------------------------------------
SYS_STS23QG$GO$77VMC2G$1MGRSIU ("ODESLAL","TYPVZKAZU")
SYS_STSM$CMAQMNWQBJAWA84XJAQLG ("ADRESAT","TYPVZKAZU")
2 rows selected.
-- virtual column
select column_name, virtual_column, hidden_column
from user_tab_cols
where table_name='ZPRAVY'
AND NOT virtual_column='NO';
COLUMN_NAME VIRTUAL_COLUMN HIDDEN_COLUMN
-------------------------------------------------------------------- ------ --------
SYS_STS23QG$GO$77VMC2G$1MGRSIU YES YES
SYS_STSM$CMAQMNWQBJAWA84XJAQLG YES YES
2 rows selected.
These virtual columns content hash data created by SYS_OP_COMBINED_HASH()
-- drop extended statistics
exec DBMS_STATS.DROP_EXTENDED_STATS (ownname=>'M7ZULIMS', tabname=>'ZPRAVY', extension=>'("ODESLAL","TYPVZKAZU")');
exec DBMS_STATS.DROP_EXTENDED_STATS (ownname=>'M7ZULIMS', tabname=>'ZPRAVY', extension=>'("ADRESAT","TYPVZKAZU")');
These statistics are created by Oracle DB 12.1.0.1 SE itself. When I drop it, Oracle make it soon again.
... and my question: How can I prevent from creating these statistics? OR Is it a Oracle bug?
Thanks in advance