No question at this time
DBA Top 10
1 B. Vroman 15100
2 M. Cadot 14200
3 A. Kavsek 9300
4 J. Schnackenberg 6100
5 T. Boles 5500
6 M. Hidayathullah ... 5400
7 P. Wisse 2500
8 T. P 900
9 R. Wauben 500
9 H. Steijntjes 500
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 users48179
Total active users1662
Act. users last 24h2
Act. users last hour0
Registered user hits last week193
Registered user hits last month1054
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, 107 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, 107 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, 104 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