Re: DBA_TAB_PARTITIONS

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 17 Jan 2018 20:27:21 +0000
Message-ID: <MM1P12301MB1658182AE544CF4E8EA9D64CA5E90_at_MM1P12301MB1658.GBRP123.PROD.OUTLOOK.COM>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 17 2018 - 21:27:21 CET