No question at this time
DBA Top 10
1 A. Kavsek 8900
2 B. Vroman 6600
3 M. Cadot 5000
4 T. Boles 4550
5 P. Wisse 4500
6 J. Schnackenberg 3700
7 . Lauri 1300
8 G. Lambregts 800
9 T. P 700
9 R. Pattyn 700
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48392
Total active users1527
Act. users last 24h5
Act. users last hour3
Registered user hits last week277
Registered user hits last month713
Go up

how to query last 5 partitions of some tables
Next thread: About rebuilding houses
Prev thread: 11.2.0.4 Jan'19 Combo Patch not found

Message Score Author Date
Dear All, I am trying make a query to fetch las...... M Adl Jan 20, 2019, 21:30
Use ROW_NUMBER function, partitioning by table, ...... Michel Cadot Jan 20, 2019, 21:44
That is: <pre>select * from ( select table_na...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Jan 20, 2019, 21:48
of course <b>user_tab_partitions</b> not user_ta...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Jan 20, 2019, 21:53
Dear Mr. Michel, Great mind! Thanks for your im...... M Adl Jan 20, 2019, 22:39

Follow up by mail Click here


Subject: how to query last 5 partitions of some tables
Author: M Adl, India
Date: Jan 20, 2019, 21:30, 176 days ago
Os info: any
Oracle info: 11g
Message: Dear All,

I am trying make a query to fetch last 5 partitions of say some 5 tables.

select * from (select table_name,partition_name ,partition_position from dba_tab_partitions where table_name in (a,b,c) order by partition_position desc) where rownum <=5

here I get only top 5 , how can I get each tables top 5,

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

Subject: Re: how to query last 5 partitions of some tables
Author: Michel Cadot, France
Date: Jan 20, 2019, 21:44, 176 days ago
Message:
Use ROW_NUMBER function, partitioning by table, instead of ROWNUM.

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: how to query last 5 partitions of some tables
Author: Michel Cadot, France
Date: Jan 20, 2019, 21:48, 176 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
That is:
select * 

from ( select table_name, partition_name, partition_position,
row_number() over (partition by table_name order by partition_position desc) rn
from user_tables
where table_name in ('a','b','c') )
where rn <= 5
/


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: how to query last 5 partitions of some tables
Author: Michel Cadot, France
Date: Jan 20, 2019, 21:53, 176 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
of course user_tab_partitions not user_tables.

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

Subject: Re: how to query last 5 partitions of some tables
Author: M Adl, India
Date: Jan 20, 2019, 22:39, 176 days ago
Message: Dear Mr. Michel,

Great mind! Thanks for your immediate reply. It saved my day.

Sample output
1 EXTRACT SYS_P29732 44 1
2 EXTRACT SYS_P29252 43 2
3 EXTRACT SYS_P28792 42 3
4 EXTRACT SYS_P28252 41 4
5 EXTRACT SYS_P27648 40 5
6 NLOG SYS_P30272 45 1
7 NLOG SYS_P29672 44 2
8 NLOG SYS_P29192 43 3
9 NLOG SYS_P28752 42 4
10 NLOG SYS_P28172 41 5


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