RE: DBA_TAB_PARTITIONS

From: Reen, Elizabeth <"Reen,>
Date: Wed, 17 Jan 2018 22:14:15 +0000
Message-ID: <258575162B63424EB58DAE3A5475B6ED012CD0B31E_at_EXNJMB25.nam.nsroot.net>

Thank you all for some wonderful solutions!

Liz

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Wednesday, January 17, 2018 3:27 PM To: oracle-l_at_freelists.org
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Reen, Elizabeth <dmarc-noreply_at_freelists.org> Sent: 16 January 2018 19:46
To: oracle-l_at_freelists.org
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=

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jan 17 2018 - 23:14:15 CET