ORA-54002 when trying to create Virtual Column using REGEXP_REPLACE on Oracle 12cR2

I encountered an issue today trying to create a table in an Oracle 12cR2 database, the DDL for which, I extracted from an Oracle 11gR2 database. The error returned when trying to create the table was:

ORA-54002: only pure functions can be specified in a virtual column expression

The definition of the table included a Virtual Column which used a REGEXP_REPLACE call to derive a value from another column on the table.

Here is a simplified test case illustrating the scenario (Thanks Tim for the REGEXP_REPLACE example code):

select * from v$version
/
create table test_ora54002_12c(
 col1 VARCHAR2(20 CHAR) NOT NULL
 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
)
/
drop table test_ora54002_12c purge
/

Running this on 11gR2 gives:

SQL> select * from v$version
 2 /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

5 rows selected.

Elapsed: 00:00:00.40
SQL> create table test_ora54002_12c(
 2 col1 VARCHAR2(20 CHAR) NOT NULL
 3 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
 4 )
 5 /

Table created.

Elapsed: 00:00:00.24
SQL> drop table test_ora54002_12c purge
 2 /

Table dropped.

Running this on 12cR2 gives:

SQL> select * from v$version
/
 2
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0

SQL> create table test_ora54002_12c(
 col1 VARCHAR2(20 CHAR) NOT NULL
 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
)
/
 2 3 4 5 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
 *
ERROR at line 3:
ORA-54002: only pure functions can be specified in a virtual column expression


SQL> drop table test_ora54002_12c purge
/
 2 drop table test_ora54002_12c purge
 *
ERROR at line 1:
ORA-00942: table or view does not exist

As you can see, 12cR2 gives the ORA-54002 error.

Looking on MOS, highlights this article, which suggests that you shouldn’t have been able to do this in 11gR2, i.e. it was a bug and that 12cR2 has fixed this bug and thus you can no longer create such a virtual column (the article refers to functional index and check constraint use cases as well).

In my case, I was able to rewrite the virtual column to use simple string functions such as SUBSTR, TRANSLATE and INSTR to achieve what I wanted and the virtual column was allowed to be created with these – problem solved – a shame really as the REGEXP_REPLACE approach was far neater.

$deleted$ tablespace names bug

This one turned out to be a an interesting bug the other day…

I did a simple select from DBA_TAB_PARTITIONS and noticed that some tablespace_names were of the form “_$deleted$n$m” where n and m are numbers. Slightly worrying, but at least the data was all present and correct, when I checked. I knew the DBA team had been doing some reorganisations the previous weekend, to recover some space, so I wondered if that was connected….it was, and after opening an SR, the DBA, Phil, found an explanation (from Oracle Note: 604648.1) and a resolution.

Reproducing the issue and the way to fix it, is simple, using this script…

DROP TABLESPACE old_tbs INCLUDING CONTENTS AND DATAFILES;

CREATE TABLESPACE new_tbs 
DATAFILE 'C:APPORACLEORADATAT111NEW_TBS.DBF'
SIZE 100M
ONLINE;

CREATE TABLESPACE old_tbs 
DATAFILE 'C:APPORACLEORADATAT111OLD_TBS.DBF'
SIZE 100M
ONLINE;

SELECT ts#,name FROM sys.ts$ WHERE name LIKE '%TBS';

CREATE TABLE jeff_test(col1 DATE NOT NULL
                      ,col2 NUMBER NOT NULL
                      ,col3 VARCHAR2(200) NOT NULL
                      )
TABLESPACE old_tbs
PARTITION BY RANGE(col1)
SUBPARTITION BY LIST(col2)
SUBPARTITION TEMPLATE(
 SUBPARTITION "S1" VALUES(1)
,SUBPARTITION "S2" VALUES(2)
)
(PARTITION p1 VALUES LESS THAN(TO_DATE('31-DEC-2009','DD-MON-YYYY'))
,PARTITION p2 VALUES LESS THAN(TO_DATE('31-DEC-2010','DD-MON-YYYY'))
)
/

SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST';
SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST';

ALTER TABLE jeff_test MOVE SUBPARTITION p1_s1 TABLESPACE NEW_TBS;
ALTER TABLE jeff_test MOVE SUBPARTITION p1_s2 TABLESPACE NEW_TBS;
ALTER TABLE jeff_test MOVE SUBPARTITION p2_s1 TABLESPACE NEW_TBS;
ALTER TABLE jeff_test MOVE SUBPARTITION p2_s2 TABLESPACE NEW_TBS;

DROP TABLESPACE old_tbs INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE new_tbs RENAME TO old_tbs;

SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST';
SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST';

ALTER TABLE jeff_test MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE old_tbs;
ALTER TABLE jeff_test MODIFY DEFAULT ATTRIBUTES FOR PARTITION p2 TABLESPACE old_tbs;

SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST';
SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST';

Which, when run in 11.1.0.6 on Windows 2003 Server 64 bit, gives:

DROP TABLESPACE old_tbs succeeded.
CREATE TABLESPACE succeeded.
CREATE TABLESPACE succeeded.
TS#                    NAME                           
---------------------- ------------------------------ 
9                      NEW_TBS                        
10                     OLD_TBS                        

2 rows selected

CREATE TABLE succeeded.
PARTITION_NAME                 TABLESPACE_NAME                
------------------------------ ------------------------------ 
P1                             OLD_TBS                        
P2                             OLD_TBS                        

2 rows selected

SUBPARTITION_NAME              TABLESPACE_NAME                
------------------------------ ------------------------------ 
P1_S2                          OLD_TBS                        
P1_S1                          OLD_TBS                        
P2_S2                          OLD_TBS                        
P2_S1                          OLD_TBS                        

4 rows selected

ALTER TABLE jeff_test succeeded.
ALTER TABLE jeff_test succeeded.
ALTER TABLE jeff_test succeeded.
ALTER TABLE jeff_test succeeded.
DROP TABLESPACE old_tbs succeeded.
ALTER TABLESPACE new_tbs succeeded.
PARTITION_NAME                 TABLESPACE_NAME                
------------------------------ ------------------------------ 
P1                             _$deleted$10$0                 
P2                             _$deleted$10$0                 

2 rows selected

SUBPARTITION_NAME              TABLESPACE_NAME                
------------------------------ ------------------------------ 
P1_S2                          OLD_TBS                        
P1_S1                          OLD_TBS                        
P2_S2                          OLD_TBS                        
P2_S1                          OLD_TBS                        

4 rows selected

ALTER TABLE jeff_test succeeded.
ALTER TABLE jeff_test succeeded.
PARTITION_NAME                 TABLESPACE_NAME                
------------------------------ ------------------------------ 
P1                             OLD_TBS                        
P2                             OLD_TBS                        

2 rows selected

SUBPARTITION_NAME              TABLESPACE_NAME                
------------------------------ ------------------------------ 
P1_S2                          OLD_TBS                        
P1_S1                          OLD_TBS                        
P2_S2                          OLD_TBS                        
P2_S1                          OLD_TBS                        

4 rows selected

Notice that the $n in “_$deleted$n$m” is 10, which is the ts# of the OLD_TBS before the rename. The problem revolves around entries in TS$, when you rename tablespaces to names that have previously been used and then dropped, basically because the old entries are not removed from TS$.

Related references:
Bug Numbers:8291493, itself a duplicate of 5769963
Note: 604648.1

According to the SR and bug, it was noticed in 10.2.0.4 and is fixed in 10.2.0.5. We’ve reproduced it in 11.1.0.6 on various ports, (results above) and updated our SR, so I guess the fix might also find it’s way into 11.1.0.7, perhaps.

Problem with _gby_hash_aggregation_enabled parameter

Here’s a tale about an Oracle initialisation parameter…and a lesson we should all take note of…

For three days, my colleagues in the support team on one of the warehouses I’m involved in, were struggling with a piece of code which was exhausting the available temp space and after trying everything they could think of, they asked me to take a look. I must admit I was a little baffled at first because the piece of code in question had been happily running for some time now and every time I’d run it, I’d never noticed that TEMP was anywhere near being exhausted so, whilst I could tell the process had some kind of problem, I was in the dark as to exactly what that problem was.

After trying to break down the large query into several smaller steps, I realised that it was an early step in the query that was exhibiting the problem of temp exhaustion – the step being a pivot of around 300 million measure rows into a pivot target table.

This process runs monthly and it had run without issue on all of the previous 20 occurrences and for roughly the same, if not more rows to pivot…so it didn’t appear to be the volume that was causing the problem.

There had been no changes in the code itself for many months and the current version of the code had been run successfully in previous months, so it didn’t appear to be a code fault.

I obtained the actual execution path for the statement whilst it was running and it looked reasonable, although looking at it, triggered a thought in my mind…what if something had changed in the database configuration?

Why would I get that thought from looking at the execution path?

Well, a while back, we had received some advice that a line in our init.ora as follows, should be changed to set the feature to TRUE instead of FALSE, so that the feature became active:

_gby_hash_aggregation_enabled = FALSE

This results in a line in the plan that reads:

HASH GROUP BY

instead of

SORT GROUP BY

The parameter was set to FALSE due to a known bug and the issues we’d seen with it, however the recent advice we’d received, indicated that the bug had been resolved at the version level we were on and that by enabling the feature – which enables GROUP BY and Aggregation using a hash scheme – we’d gain a performance boost for certain queries.

So, the DBA team researched the advice and it appeared to be the case, that the bug (4604970) which led to the disabling of the feature was fixed at our version level (10.2.0.3 on HP-UX). We duly turned on the feature in a pre production environment and ran it for a while without noticing any issues. We then enabled it in production and again, for a while, we’ve not noticed any issues…until now.

After a check back through the logs, it appeared that since the parameter was first enabled, the queries which were now failing, had not been run at all…they had only run prior to the parameter change…so with my suspicions aroused further, I disabled the feature at the session level and reran the process. It completed in a normal time frame and used a small amount of TEMP – hooray!

So, now we have to go back to support to try and understand if the original bug is not quite fixed or whether this is a different scenario…in any event, we’re going to disable the feature for now, even though we’re only getting problems with the feature on 2 processes out of perhaps thousands.

So, what’s the lesson to learn?

Well, quite simply, that you need to have a thorough audit of what configuration changes you’ve made together with a good audit of the processes you’ve run so that you can work out what has changed since the last time you successfully ran a process. This gives you a fighting chance of spotting things like the above.

Partition exchange loading and ORA-14097

Continuing the theme of this post by Howard, I came across a scenario today which was resulting in this error:

ORA-14097 - column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

I ran my checker script to try and identify exactly what the mismatch was but it came back with nothing. My script, whilst useful, isn’t perfect – and indeed there was an error in it (fixed now) which led to it not identifying the problem for this scenario – but given that it couldn’t find the problem, I had to manually look at all the attributes across the tables to try and identify a difference.

For a long time I was left perplexed because my script was suggesting that everything was OK and my script checks quite a few things now – what I wasn’t taking into account was that the script was wrong and that one of the things it was supposedly checking for, it was in fact overlooking.

In the end I found that a number of columns on the source table were NULLable whilst they were NOT NULL on the target. My script was supposed to be checking for this – which is why I was struggling to fix the problem for so long. After matching the nullability on both tables in the partition, the exchange ran through fine…but I guess my point would be, that the error message above doesn’t really convey the message that the problem might be a mismatch in the optionality of a column or columns on the tables involved.

Being a fan of fictional detective, Sherlock Holmes, I should have considered his position that “It is an old maxim of mine that when you have excluded the impossible, whatever remains, however improbable, must be the truth”…even if that truth is that your own script is at fault!

In attempting to investigate the problem I knocked up a simple script to demonstrate the problem and then the fix:

conn alpha/alpha@j4134

drop table source purge
/

create table source(pk_col1 number not null
,other_col1 number
,other_col2 date
,other_col3 varchar2(20)
)
/

create unique index test_pki on source(pk_col1)
/

alter table source add constraint test_pk primary key(pk_col1) using index
/

grant select on source to beta
/

insert into source(pk_col1,other_col1,other_col2,other_col3)
select l
, l
, trunc(sysdate+l)
, 'XXX'to_char(l)
from (select level l from dual connect by level < 1000)
/
commit
/

conn beta/beta@j4134

drop table target purge
/

create table target(pk_col1 number not null
,other_col1 number not null
,other_col2 date
,other_col3 varchar2(20)
)
partition by range(pk_col1)
(partition p1 values less than(1000)
,partition p2 values less than(2000)
)
/

create unique index test_pki on target(pk_col1) local
/

alter table target add constraint test_pk primary key(pk_col1) using index
/

alter table target exchange partition p1 with table alpha.source
/

conn alpha/alpha@j4134

alter table source modify(other_col1 number not null)
/

conn beta/beta@j4134

alter table target exchange partition p1 with table alpha.source
/

…and the results…

Connected.

Table dropped.


Table created.


Index created.


Table altered.


Grant succeeded.


999 rows created.


Commit complete.

Connected.

Table dropped.


Table created.


Index created.


Table altered.

alter table target exchange partition p1 with table alpha.source
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


Connected.

Table altered.

Connected.

Table altered.

Whilst in the example Howard gave, I think the issue revolved around the use of the word “shape” in error ORA-42016 and whether shape includes “data type” or not, error ORA-14097 seems to revolve around whether nullability is included under the phrase “column type” – I think both errors could do with being either slightly reworded or perhaps split out into separate errors which are more indicative of the true problem or problems at hand.