RE: DBA_TAB_PARTITIONS

  • From: "Reen, Elizabeth " <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "elizabeth.reen" for DMARC)
  • To: "'jonathan@xxxxxxxxxxxxxxxxxx'" <jonathan@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 17 Jan 2018 22:14:15 +0000

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


Other related posts: