Thank you all for some wonderful solutions!
Liz
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Wednesday, January 17, 2018 3:27 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: DBA_TAB_PARTITIONS
What you can do depends on the version of Oracle you've got and what you know
about the partitioned object. You may not need to convert the high_value at
all, just query the database for the data_object_id of each partition you think
you will need by value e.g: assuming you have a partitioned table that is
partitioned by month and you want to compress all partitions prior to 1st Jan
2018, here's a literal string method to point the way:
select
dbms_rowid.rowid_object(rowid),
dbms_mview.pmarker(rowid)
from
transactions partition for (to_date('28-Dec-2017','dd-mon-yyyy'))
where
rownum = 1
;
DBMS_ROWID.ROWID_OBJECT(ROWID) DBMS_MVIEW.PMARKER(ROWID)
------------------------------ -------------------------
92485 92485
(pmarker() returns the data_object_id for the row, but it's not really
documented as such, so dbms_rowid is a better bet.)
select object_name, subobject_name from user_objects where object_name =
'TRANSACTIONS' and data_object_id = 92485;
OBJECT_NAME SUBOBJECT_NAME
-------------------- ----------------------
TRANSACTIONS SYS_P723
Regards,
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Reen, Elizabeth <dmarc-noreply@xxxxxxxxxxxxx>
Sent: 16 January 2018 19:46
To: oracle-l@xxxxxxxxxxxxx
Subject: DBA_TAB_PARTITIONS
We are trying to create a sql which will create a script of
partitions to compress which are older than a certain date. That information
is kept in High_value. When displayed high_value looks like this
TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')
So the first thing we did was to query where high_value like
'TO_DATE%'. We got an invalid character error. Long story short, we
discovered that high_value's data type is long. There must be a way to
translate this into text. SQLplus, Sqldeveloper, and TOAD all do that. The
question is how do they do it? Does anyone know how it is done?
Thanks,
Liz
--
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwIF-g&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=R6-0qLPlDh3xBoobvFzVFW2CKoxMjh98_aPUmykiF7U&s=4s4F0EUvfmYVfF4Q7zx8fflPJ1lFa-WLX6VAPvvAo4M&e=
--
//www.freelists.org/webpage/oracle-l