No question at this time
DBA Top 10
1 M. Cadot 15900
2 B. Vroman 15500
3 A. Kavsek 10000
4 T. Boles 6800
5 J. Schnackenberg 5800
6 M. Hidayathullah ... 3900
7 P. Wisse 1800
8 T. P 700
9 B. Derous 500
9 R. Wauben 500
9 H. Steijntjes 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48209
Total active users1645
Act. users last 24h6
Act. users last hour0
Registered user hits last week186
Registered user hits last month812
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, 167 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, 167 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, 164 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