Home » SQL & PL/SQL » SQL & PL/SQL » sql query help (oracle 10g,)
sql query help [message #313627] Mon, 14 April 2008 00:13 Go to next message
kuldip
Messages: 10
Registered: November 2007
Junior Member
hi, i stuck in a query. i need help.

the 1st query is for sga:

select
FirstTable.FIRSTSGANAME SGANAME,FirstTable.FIRSTSGASIZE,
SECONDTABLE.SECONDSGASIZE
from
(select
e1.sgasize As FIRSTSGASIZE,
e1.sganame AS FIRSTSGANAME
from
sysman.mgmt_db_sga_ecm e1,sysman.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
sysman.mgmt_db_sga_ecm e2,sysman.mgmt_ecm_gen_snapshot a
where
a.snapshot_guid = e2.ecm_snapshot_id
and a.target_name = 'PXANAM_pxanam2'
) SecondTable
where FirstTable.FIRSTSGANAME=SECONDTABLE.SECONDSGANAME
AND FirstTable.FIRSTSGASIZE<>SECONDTABLE.SECONDSGASIZE

---------
OUTPUT
---------

BUFFERED CACHE 3536 3040
SHARED POOL 1456 2209
TOTAL SGA 5040 5297
VARIABLE SGA 1104 1600

the second query is for redolog:

select firstTable.target_name,firstTable.file_name,SecondTable.target_name,SecondTable.file_name
from

(select a.target_name,r1.file_name
from sysman.mgmt_ecm_gen_snapshot a,sysman.mgmt_db_redologs_ecm r1
where r1.ecm_snapshot_id = a.snapshot_guid
and a.target_name = 'PXANAM_pxanam1'
and r1.file_name like '%.log') FirstTable,
(select a.target_name,r2.file_name
from sysman.mgmt_ecm_gen_snapshot a,sysman.mgmt_db_redologs_ecm r2
where r2.ecm_snapshot_id = a.snapshot_guid
and a.target_name = 'PXANAM_pxanam2'
and r2.file_name like '%.log') SecondTable
where firstTable.file_name = SecondTable.file_name

--------
OUTPUT
--------
PXANAM_pxanam1 /pxanam/oradata/data03/redo06-2.log PXANAM_pxanam2 /pxanam/oradata/data03/redo06-2.log
PXANAM_pxanam1 /pxanam/oradata/data03/redo06-1.log PXANAM_pxanam2 /pxanam/oradata/data03/redo06-1.log
PXANAM_pxanam1 /pxanam/oradata/data03/redo05-2.log PXANAM_pxanam2 /pxanam/oradata/data03/redo05-2.log
PXANAM_pxanam1 /pxanam/oradata/data03/redo05-1.log PXANAM_pxanam2 /pxanam/oradata/data03/redo05-1.log

now i want both the output in one query. i tried it, but instead of getting 4 rows,
i get 4(from the 1st query)*4(from the 2nd query)=16 rows. (as cartesian join)
here is the query:

select
FirstTable.FIRSTSGANAME,FirstTable.FIRSTSGASIZE,
SECONDTABLE.SECONDSGASIZE,firstTable.file_name,SecondTable.file_name
from
( select
e1.sgasize As FIRSTSGASIZE,e1.sganame AS FIRSTSGANAME,
r1.file_name
from sysman.mgmt_db_sga_ecm e1,sysman.mgmt_ecm_gen_snapshot a,
sysman.mgmt_db_redologs_ecm r1
where a.snapshot_guid = e1.ecm_snapshot_id
and r1.ecm_snapshot_id = a.snapshot_guid
and a.target_name = 'PXANAM_pxanam1'
and r1.file_name like '%.log') FirstTable,

( select
e2.sgasize As SECONDSGASIZE,e2.sganame AS SECONDSGANAME,
r2.file_name
from sysman.mgmt_db_sga_ecm e2,sysman.mgmt_ecm_gen_snapshot a,
sysman.mgmt_db_redologs_ecm r2
where a.snapshot_guid = e2.ecm_snapshot_id
and r2.ecm_snapshot_id = a.snapshot_guid
and a.target_name = 'PXANAM_pxanam2'
and r2.file_name like '%.log') SecondTable

where FirstTable.FIRSTSGANAME=SECONDTABLE.SECONDSGANAME
AND FirstTable.FIRSTSGASIZE<>SECONDTABLE.SECONDSGASIZE
and firstTable.file_name = SecondTable.file_name

i got the output as:

BUFFERED CACHE 3536 3040 /pxanam/oradata/data03/redo05-1.log /pxanam/oradata/data03/redo05-1.log
SHARED POOL 1456 2209 /pxanam/oradata/data03/redo05-1.log /pxanam/oradata/data03/redo05-1.log
TOTAL SGA 5040 5297 /pxanam/oradata/data03/redo05-1.log /pxanam/oradata/data03/redo05-1.log
VARIABLE SGA 1104 1600 /pxanam/oradata/data03/redo05-1.log /pxanam/oradata/data03/redo05-1.log

BUFFERED CACHE 3536 3040 /pxanam/oradata/data03/redo05-2.log /pxanam/oradata/data03/redo05-2.log
SHARED POOL 1456 2209 /pxanam/oradata/data03/redo05-2.log /pxanam/oradata/data03/redo05-2.log
TOTAL SGA 5040 5297 /pxanam/oradata/data03/redo05-2.log /pxanam/oradata/data03/redo05-2.log
VARIABLE SGA 1104 1600 /pxanam/oradata/data03/redo05-2.log /pxanam/oradata/data03/redo05-2.log

BUFFERED CACHE 3536 3040 /pxanam/oradata/data03/redo06-1.log /pxanam/oradata/data03/redo06-1.log
SHARED POOL 1456 2209 /pxanam/oradata/data03/redo06-1.log /pxanam/oradata/data03/redo06-1.log
TOTAL SGA 5040 5297 /pxanam/oradata/data03/redo06-1.log /pxanam/oradata/data03/redo06-1.log
VARIABLE SGA 1104 1600 /pxanam/oradata/data03/redo06-1.log /pxanam/oradata/data03/redo06-1.log

BUFFERED CACHE 3536 3040 /pxanam/oradata/data03/redo06-2.log /pxanam/oradata/data03/redo06-2.log
SHARED POOL 1456 2209 /pxanam/oradata/data03/redo06-2.log /pxanam/oradata/data03/redo06-2.log
TOTAL SGA 5040 5297 /pxanam/oradata/data03/redo06-2.log /pxanam/oradata/data03/redo06-2.log
VARIABLE SGA 1104 1600 /pxanam/oradata/data03/redo06-2.log /pxanam/oradata/data03/redo06-2.log


but i need the output as:

BUFFERED CACHE 3536 3040 /pxanam/oradata/data03/redo05-1.log /pxanam/oradata/data03/redo05-1.log
SHARED POOL 1456 2209 /pxanam/oradata/data03/redo05-2.log /pxanam/oradata/data03/redo05-2.log
TOTAL SGA 5040 5297 /pxanam/oradata/data03/redo06-1.log /pxanam/oradata/data03/redo06-1.log
VARIABLE SGA 1104 1600 /pxanam/oradata/data03/redo06-2.log /pxanam/oradata/data03/redo06-2.log

thanks
Re: sql query help [message #313635 is a reply to message #313627] Mon, 14 April 2008 00:38 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As already said to you:
read OraFAQ Forum Guide, especially "How to format your post?" section.

I add:
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: i need result in concat ,manner
Next Topic: regarding shift operator
Goto Forum:
  


Current Time: Wed Dec 07 14:25:02 CST 2016

Total time taken to generate the page: 0.18335 seconds