From: Jonathan Lewis <jonathan@jlcomp.demon.co.uk>
To: "oracle-l@freelists.org" <oracle-l@freelists.org>
Subject: Re: DBA_TAB_PARTITIONS
Thread-Topic: DBA_TAB_PARTITIONS
Date: Wed, 17 Jan 2018 20:27:21 +0000
Message-ID: <MM1P12301MB1658182AE544CF4E8EA9D64CA5E90@MM1P12301MB1658.GBRP123.PROD.OUTLOOK.COM>
References: <258575162B63424EB58DAE3A5475B6ED012CD0646B@EXNJMB25.nam.nsroot.net>
In-Reply-To: <258575162B63424EB58DAE3A5475B6ED012CD0646B@EXNJMB25.nam.nsroot.net>
Accept-Language: en-GB, en-US
Content-Language: en-GB
received-spf: None (protection.outlook.com: jlcomp.demon.co.uk does not
 designate permitted sender hosts)
spamdiagnosticoutput: 1:99
spamdiagnosticmetadata: NSPM
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-Network-Message-Id: 10c4ae4d-6155-4be2-9036-08d55de8b654
X-MS-Exchange-CrossTenant-originalarrivaltime: 17 Jan 2018 20:27:21.4959
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: c5ec3dea-08bd-405e-942c-f61dff9a4831
X-MS-Exchange-Transport-CrossTenantHeadersStamped: MM1P12301MB1657
X-archive-position: 70355
X-ecartis-version: Ecartis v1.0.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
X-original-sender: jonathan@jlcomp.demon.co.uk
Precedence: normal
Reply-To: jonathan@jlcomp.demon.co.uk
List-help: <mailto:ecartis@freelists.org?Subject=help>
List-unsubscribe: <oracle-l-request@freelists.org?Subject=unsubscribe>
List-software: Ecartis version 1.0.0
List-Id: oracle-l <oracle-l.freelists.org>
X-List-ID: oracle-l <oracle-l.freelists.org>
List-subscribe: <oracle-l-request@freelists.org?Subject=subscribe>
List-owner: <mailto:>
List-post: <mailto:oracle-l@freelists.org>
List-archive: <http://www.freelists.org/archives/oracle-l>
X-list: oracle-l

What you can do depends on the version of Oracle you've got and what you kn=
ow 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 y=
ou 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 =3D 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 docu=
mented as such, so dbms_rowid is a better bet.)


select object_name, subobject_name from user_objects where object_name =3D =
'TRANSACTIONS' and data_object_id =3D 92485;

OBJECT_NAME          SUBOBJECT_NAME
-------------------- ----------------------
TRANSACTIONS         SYS_P723


Regards,
Jonathan Lewis
________________________________________
From: oracle-l-bounce@freelists.org <oracle-l-bounce@freelists.org> on beha=
lf of Reen, Elizabeth  <dmarc-noreply@freelists.org>
Sent: 16 January 2018 19:46
To: oracle-l@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 informati=
on 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=3DG=
REGORIAN')

               So the first thing we did was to query where high_value like=
 =91TO_DATE%=92.  We got an invalid character error.   Long story short, we=
 discovered that high_value=92s data type is long.  There must be a way to =
translate this into text.  SQLplus, Sqldeveloper, and TOAD all do that.  Th=
e question is how do they do it?  Does anyone know how it is done?

Thanks,

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