Friday, January 4, 2019

How to solve the problem with ORA-00997: illegal use of LONG datatype when quering the dba_tab_partitions



If you need to selectively find partitions based on their upper-bound value, like this:

select partition_name,high_value
from dba_tab_partitions
where table_name='MYTABLE'
and high_value='201805';

you will run into the following error:
ORA-00997: illegal use of LONG datatype

One solution I found was to create a temporary table, like this:
CREATE GLOBAL TEMPORARY TABLE PARTITION_HIGH_VAL
ON COMMIT PRESERVE ROWS
AS
select 
     PARTITION_NAME,
     to_lob(high_value) as text_clob
     from dba_tab_partitions 
     where TABLE_OWNER='SH'
     and table_name='SALES';

Then, use the table PARTITION_HIGH_VAL to view the upper bounds of each partition:
select PARTITION_NAME,TO_CHAR(TEXT_CLOB) "high value" 
from partition_high_Val;

PARTITION_NAME high value
SYS_P79451 201806
SYS_P71617 201805
SYS_P70243 201803
SYS_P70231 201804
SYS_P70112 201802


Since this is a temporary table, your rows will be deleted upon your session exit because of the directive ON COMMIT PRESERVE ROWS.
Obviously, you could create a heap-organized table to store these results in, too, if you need to store your data for a longer period.

No comments:

Post a Comment