Another way to get to last partition
Dan, October 07, 2015 - 1:51 pm UTC
My guess is that you are looking for the partition name, not really wanting to deal with longs...
Step 1 - use the PARTITION FOR (xxx) construct to get a row from the desired partition
select rowid from my_date_partitioned_table for ('1-jan-2016') where rownum=1;
Step 2. Then convert the rowid to the partition name
with z as (select
tbl$or$idx$part$num("scott"."my_partitioned_table",
0,
4,
0,
"ROWID") objn from my_partitioned_table tp)
select subobject_name , object_type from dba_objects d,z where d.object_id = z.objn
Then use that partition name in whatever maintenance your are doing. No "magical" knowledge of partition naming conventions needed.
What you do need is knowledge of the partition key (date in this example). Also, the strings are literals, so you will be using dynamic sql. Can't use bind variable in the PARTITION FOR.
A reader, September 28, 2016 - 4:17 am UTC
Solved my problem
Nam Doan, September 28, 2016 - 4:20 am UTC
My issue is truncate some partitioned tables, and Chris's suggesstion had already help to solved my issue. Thank you!
A reader, April 18, 2018 - 3:25 pm UTC
What if ,if we want to delete the partition
Mansi, March 29, 2019 - 2:00 pm UTC
I have requirement where we keep backup of last 15days data. I used interval partition to create partition for each day but I am also deleting data of sysdate -1th day.
How to maintain the interval partition in this case,as it will pile up the date partition in database.
March 29, 2019 - 5:02 pm UTC
Then drop or truncate the partition with the data you want to remove!
Maintenance of interval partition
A reader, April 01, 2019 - 6:37 am UTC
Insert and delete of data happens with user with limited access, that user is not having access to drop partition.
April 01, 2019 - 10:12 am UTC
So delete the rows then...
extractvalue deprecated
AndyP, April 02, 2019 - 7:55 am UTC
Regarding the response to the original question, about inventive ways to deal with LONGs
https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/whatsnew.htm#ADXDB5978 Oracle Database 11g Release 2 (11.2.0.1) Deprecated Oracle XML DB Constructs
The following Oracle XML DB constructs are deprecated in Oracle Database 11g Release 2 (11.2.0.1)
They are still supported in 11.2.0.1 for backward compatibility, but Oracle recommends that you do not use them in new applications
Oracle SQL function extract – Use SQL/XML function XMLQuery instead. See "XMLQUERY SQL/XML Function in Oracle XML DB".
Oracle SQL function
extractValue – Use SQL/XML function XMLTable or SQL/XML functions XMLCast and XMLQuery insteadAnd so, I think this would be the direct replacement for that query
with xmlform as
(
select dbms_xmlgen.getxmltype('select table_name,partition_name,high_value from user_tab_partitions where table_name=''T''') as x
from dual
)
select xmltab.*
from xmlform
,xmltable('/ROWSET/ROW'
passing xmlform.x
columns table_name varchar2(10) path 'TABLE_NAME'
,partition varchar2(9) path 'PARTITION_NAME'
,high_value varchar2(85) path 'HIGH_VALUE'
) xmltab;
TABLE_NAME PARTITION HIGH_VALUE
---------- --------- -------------------------------------------------------------------------------------
T P0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
T P1 TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
T PMAX MAXVALUE
April 02, 2019 - 10:39 am UTC
Great point, thanks for sharing.
A reader, April 30, 2019 - 7:20 am UTC
Regarding Chris's suggestion of deleting rows from table,
will it delete interval partition which no longer needed,
It will just pile up the partitions,right?
April 30, 2019 - 10:06 am UTC
Yes. If you delete all the rows from a partition, it's still there. Just empty.
You need to drop a partition to completely remove it.
Idea
John Keymer, May 01, 2019 - 8:46 am UTC
I posted an idea on the community forums to have a framework which automates this somewhat. It hasn't had much traction, so I doubt will be implemented, but feel free to upvote it all the same. :)
https://community.oracle.com/ideas/24831
May 01, 2019 - 9:44 am UTC
I could see that some people may want this. Thanks for sharing.