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