No question at this time
DBA Top 10
1 M. Cadot 15900
2 A. Kavsek 11800
3 B. Vroman 10800
4 M. Hidayathullah ... 7600
5 T. Boles 4800
6 P. Wisse 4000
7 J. Schnackenberg 3000
8 K. Van Nieuwenhov... 2100
9 A. Hudspith 700
9 T. P 700
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48114
Total active users1700
Act. users last 24h12
Act. users last hour1
Registered user hits last week507
Registered user hits last month1127
Go up

Query
Next thread: Procedure Partition exchange
Prev thread: PLS-00307: too many declarations of match this call

Message Score Author Date
Hi all; I have the following query and it is wo...... John Baet Jan 05, 2018, 13:40
<pre>select p.high_value, p.table_name, p.partitio...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Jan 05, 2018, 15:06
Thanks Michel. Regards, J... John Baet Jan 08, 2018, 09:48

Follow up by mail Click here


Subject: Query
Author: John Baet, Netherlands
Date: Jan 05, 2018, 13:40, 12 days ago
Os info: Linux
Oracle info: 11.2.0.4
Message: Hi all;

I have the following query and it is working fine:
 select high_value,table_name, partition_name,interval

from user_tab_partitions
where get_high_value_as_date(table_name, partition_name) <= sysdate -310



HIGH_VALUE TABLE_NAME PARTITION_NAME INT
------------------------------------------------------------------------------------------------------------------------ ------------------------------ ------------------------------ ---
TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PRODUCT SYS_P279 NO
TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PRODUCT SYS_P280 NO
TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PRODUCT SYS_P282 NO


But instead of 310 (RETENTION_DURATION of a partition), I want to read the RETENTION_DURATION from a driver table as every table has own RETENTION_DURATION value.

Now question is, how to achieve this?
the driver table is like :

SQL> select owner,TABLE_NAME,RETENTION_DURATION from move_or_drop_partition_sche;


OWNER TABLE_NAME RETENTION_DURATION
------------------------------ ------------------------------ ------------------
ARC_OWN PRODUCT 36
RO_OWN PRODUCT 310


Thanks.

Regards, J
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Query
Author: Michel Cadot, France
Date: Jan 05, 2018, 15:06, 12 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
select p.high_value, p.table_name, p.partition_name, p.interval

from user_tab_partitions p, move_or_drop_partition_sche d
where get_high_value_as_date(p.table_name, p.partition_name) <= sysdate - d.retention_duration
and d.owner = user
and d.table_name = p.table_name
/



Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Query
Author: John Baet, Netherlands
Date: Jan 08, 2018, 09:48, 9 days ago
Message: Thanks Michel.

Regards, J
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here