Home » SQL & PL/SQL » SQL & PL/SQL » SOLUTION NEEDED FOR QUERY RELATED TO EM
SOLUTION NEEDED FOR QUERY RELATED TO EM [message #310252] Mon, 31 March 2008 13:29 Go to next message
kuldip
Messages: 10
Registered: November 2007
Junior Member
Hi,
can anyone help me in writing this query ? i need its solution AS SOON AS POSSIBLE.

select e1.sgasize,e1.sganame
from mgmt_db_sga_ecm e1,mgmt_ecm_gen_snapshot a
where a.snapshot_guid = e1.ecm_snapshot_id
and a.target_name = 'PXANAM_pxanam1';


output :

sganame sgasize
---------------------------
buffered_cache 3536
shared pool 1456
total sga 5040
variable sga 1104
fixed sga 2029
java pool 16
large pool 16384
maximum sga 4656
redo buffer 14352


select e2.sgasize,e2.sganame
from mgmt_db_sga_ecm e2,mgmt_ecm_gen_snapshot a
where a.snapshot_guid = e2.ecm_snapshot_id
and a.target_name = 'PXANAM_pxanam1';

output:

sganame sgasize
-----------------------------
buffered_cache 3040
shared pool 2209
total sga 5279
variable sga 1600
fixed sga 2029
java pool 16
large pool 16384
maximum sga 4656
redo buffer 14352


u can see that the first 4 rows o/p (sgasize values) of the above 2 queries are different.

I want the o/p as (only different values-- sgasize like buffered_cache,shared_pool,total_sga,variable sga)

sganame sgasize sgasize1
----------------------------------------------------
buffered_cache 3536 3040
shared pool 1456 2209
total sga 5040 5297
variable sga 1104 1600
fixed sga 2029 2029
java pool 16 16
large pool 16384 16384
maximum sga 4656 4656
redo buffer 14352 14352


tables used : mgmt_ecm_gen_snapshot a
mgmt_db_sga_ecm e1
mgmt_db_sga_ecm e2


could any one give the solution please ?
i think the self join between
mgmt_db_sga_ecm e1
mgmt_db_sga_ecm e2
will work. but it does not work . it gives multiple values an d duplicate also.
i also tried in subquery. but went in vain. i may be wrong. but please anyone help me.

THANKS
Re: EM related query -- solution needed [message #310255 is a reply to message #310252] Mon, 31 March 2008 13:33 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Too bad for everyone that you did not read & FOLLOW the posting guidelines as stated in URL below.

http://www.orafaq.com/forum/t/88153/0/

At least for this reader, I have no good idea what you expect/need.

> it does not work
My car does not work. Tell me how to make it go, please.

you REALLY need to use <code tags> along with CUT & PASTE of the whole SQL*Plus session.

[Updated on: Mon, 31 March 2008 13:35] by Moderator

Report message to a moderator

Re: EM related query -- solution needed [message #310258 is a reply to message #310252] Mon, 31 March 2008 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I stopped to read at "AS SOON AS POSSIBLE"

Regards
Michel
Re: SOLUTION NEEDED FOR QUERY RELATED TO EM [message #310493 is a reply to message #310252] Tue, 01 April 2008 07:27 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
TRY THIS OUT:-

select 
FirstTable.FIRSTSGANAME,FirstTable.FIRSTSGASIZE,
SECONDTABLE.SECONDSGASIZE
from 
 (select 
      e1.sgasize As FIRSTSGASIZE,
      e1.sganame AS FIRSTSGANAME 
  from 
      mgmt_db_sga_ecm e1,mgmt_ecm_gen_snapshot a
  where 
      a.snapshot_guid = e1.ecm_snapshot_id
      and a.target_name = 'PXANAM_pxanam1'
  ) FirstTable,
  (select 
      e2.sgasize As SECONDSGASIZE,
      e2.sganame AS SECONDSGANAME
   from 
      mgmt_db_sga_ecm e2,mgmt_ecm_gen_snapshot b
   where 
      b.snapshot_guid = e2.ecm_snapshot_id
      and a.target_name = 'PXANAM_pxanam1'
  ) SecondTable
where FirstTable.FIRSTSGANAME=SECONDTABLE.SECONDSGANAME
AND FirstTable.FIRSTSGASIZE<>SECONDTABLE.SECONDSGASIZE
Re: SOLUTION NEEDED FOR QUERY RELATED TO EM [message #310660 is a reply to message #310493] Wed, 02 April 2008 00:01 Go to previous messageGo to next message
kuldip
Messages: 10
Registered: November 2007
Junior Member
Thanks Rajat,
I have tried this one. and something wrong was there in my query condition that in my second query, the second condition was a.target_name = 'PXANAM_pxanam1' but it was wrong. it would be a.target_name = 'PXANAM_pxanam2'.and all are same.

it would be nice if you look into it and reply me.
thanks,
kuldip
Re: SOLUTION NEEDED FOR QUERY RELATED TO EM [message #310664 is a reply to message #310660] Wed, 02 April 2008 00:15 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I think that this will have no effect.
Have you tried this query???

post your results on forum.

Re: SOLUTION NEEDED FOR QUERY RELATED TO EM [message #310679 is a reply to message #310493] Wed, 02 April 2008 01:12 Go to previous message
kuldip
Messages: 10
Registered: November 2007
Junior Member
hi rajat,

i tried the query. and its working.

thanks
kuldip
Previous Topic: Getting the start and End date of a week
Next Topic: Pl/Sql procedure update statistics
Goto Forum:
  


Current Time: Sat Dec 03 18:08:42 CST 2016

Total time taken to generate the page: 0.06598 seconds