logs archiveIRC Archive / Freenode / #oracle / 2010 / February / 25 / 1
PackratDragon
Ok... this is really confusing me. I am getting "not a group by function" in my queries ( still learning SQL / Oracle)
and all the resources I have found seem to give me the same examples; so something like
RJarett
are you trying to use a max or count with other stuff?
PackratDragon
http://pastebin.com/GinHmr0B
RJarett
youre doing nasty crap like selecting inline with other stuff
dont do that
and youre sort of using having wrong
learn how to subselect
PackratDragon
I figured I was doing something.....
wrong...
RJarett
or aggregate or windowing functions
see the max id for d_song is always going to be the same
its a single entry
PackratDragon
yes I realize that... actually if you look at the second query, that is the one I started with. I was given a set of queries that were broken and I have to fix them so they work... and I am not asking anyone to do the home work for me.... I am just missing a piece... something that I am not seeing...
RJarett
subselects...
plus you should format the data you have, and the output you wish to get
and thatll help you visualize it
PackratDragon
yeah... next term is sql/psl....
what I don't understand is even if I drop the max(id) and the having statment, I still get a group by error
         

RJarett
drop the having
PackratDragon
even without the having statement; still get a group by error
it should be select 1,2,3 from TABLE where 2 = x group by 1 as general format..
ok... I think there is something wrong on this end... even a select * from d_songs group by id; gives me an error
RJarett
try this...
select id, anothercolumn from d_songs group by id;
then.. add a 3rd column
and tell me if it gets a group by error
PackratDragon
ok...
select id, title, duration, artist, type_code from d_songs group by id == error
so does select * from d_songs group by id
RJarett
now... try this.
select id, title, duration, artist, type_code from d_songs group by id, artist, type_code, title
PackratDragon
same issue....
so I may have to check with my instructor tomorrow, there may be an issue somewhere... I have tried closing my browser and clearing the temp files
RJarett
usually the rule is you need N - 1 group by expressions for every N in the list
PackratDragon
because I get the same issue with other tables too...
Well this makes me feel better.... :) I can stop pulling out what little hair I have left... I could not figure out what it was that I was missing; but maybe I was not missing anything..... )
err ;)
welcome back
mumbles
question for someone who uses the iacademy website. "i am using windows 7 , ie8.0.7600 and latest flash and java when logging in with username and password that works on laptop running virtualbox and xp and dosent when on 7
why would this happen ?
the i learning website works as per normall
PackratDragon
I was having issues with the iacad website earlier myself; although a different isue
mumbles
PackratDragon: its realy irritating me
it works when im using the same setup on my laptop
im going to head to bed and hope it fixes itself tommow
Dormir
hey Bonsai
TheBonsai
m00 Dormir
RJarett
anyone ever see a case where an alter database rename file actually deletes it?
this is on a netapp
dont know if its deleted or just something is up with the locks before i have /data2 - 4 all on the same volume on different subnets
and sh*ts disappearing when i try to rename to make it go over different mounts
TheBonsai
doesn't datafile rename only change the controlfile entry?
RJarett
yeah
it should
but in my case all my files i rename in mount mode go missing
so i dont know if its something with netapps locks or what
or the fact that all the files are really on the same volume and just presented 3 times on 3 ips
TheBonsai
"go missing"? but the target file(-name) exists?
RJarett
no
goes missing on each of the 3 mounts to the same volume
TheBonsai
if the target filename doesn't exist then you have a missing datafile as result. maybe i don't fully understand what you mean
RJarett
Ok. i have /data2 /data3 /data4 all mounted from the same volume on my netapp
each to a different interface on the netapp
im trying to hillybilly loadbalance for a test
so /data2 is 192.168.2.61 /data3 is 192.168.3.61
etc
all point to the same volume so all are the same data
right now, all datafiles are in /data2 in the controlfile
im basically trying to rename a third of them to /data3 and a third to /data4
         

TheBonsai
ah, and with your rename command, you want to change the path component in the datafile name
RJarett
so it goes over different nics
but..
when i do the rename from /data2 to /data3 or 4
the file goes missing
across all 3
TheBonsai
the file itself?
RJarett
yes
the file itself
TheBonsai
could it be that the instance removes the "old" file on a RENAME?
RJarett
im trying it again with /data2 not mounted
so it cant remove that reference on the os
but that would be seriously terrible if that were the case
TheBonsai
i'm trying to find docs that tell
but it would explain the behaviour 100%
RJarett
luckily its a test san and i can snap restore my data volume and oracle home back to what they were
lets see if it worked..
hmm db opened
good start
TheBonsai
the docs don't mention such a behaviour (or i still didn't find it)
RJarett
yeah
im going to have to turn on a low level trace on the netapp and try it later
TheBonsai
or really try it with an actual copy
RJarett
what do you mean?
this is a full reproduction of my main db
TheBonsai
i mean to see if the "source" file vanishes or not, while still being able to operate the database
RJarett
i have to rename in mount
and when i alter databsse open it says it cant find the file after i rename
then i check on the os, it none of the files i alter database rename file are there
TheBonsai
i mean you should try it with a real copy of the datafile, to see if the instance removes one of them
RJarett
thats what im doing
it is a real datafile
131 of them actually on a 1TB+ db
TheBonsai
but not a real copy, it's the same file
RJarett
i wouldnt care about the other way
i know it works
i want to know why this destroyed the original at all
TheBonsai
i don't doubt it works
so.. you know it doesn't remove the source file when you switch to a real copy?
RJarett
yes
TheBonsai
damn. then it can't be normal :( as far as i remember, oracle never removes files by itself (unless you really tell it to do, or using OMF stuff)
RJarett
this has got to be something with how nfs locks are being handled
after i test my current db on the san with a performance accel card, ill try an upgrade of 11gr2 and see if direct nfs does the same thing
i only have the san a few more days to demo
TheBonsai
only a trace could reveal that. anyways, i wonder why you don't use ASM to stripe (=balance), on top of real block devices (oracle also has a DirectNFS builtin NFS client, but i never tried it)
RJarett
#1 i hate asm. ive seen more dmg from it than good from poor drivers and interfaces and lack of direct backup support
#2 i figured id give this a shot since if i go with a netapp ill be able to push my db over bonded 10gigE nics to it
TheBonsai
define "direct backup"? you mean more than BACKUP ... TO COPY; in rman?
RJarett
yeah
i just dont trust asm
TheBonsai
believe me, it takes the guesswork out of database storage :)
i operate a 40TB diskgroup without any problems
RJarett
does your diskgroup have a 30% nightly changerate?
TheBonsai
not 30%, but several 100GB
(warehouse ETL)
i use ASM everywhere i can, especially for clusters
beside the ASM itself, i need the performance of (real) SAN, i can't use NFS (our ethernet core is only 2x 10Gbit)
i avoid 10R2 ASM since oracle 11 is out, regardless if i use 10 or 11 database, though
RJarett
right now to 1 db i have 4x 4gb hbas direct ot my san
still not pushing enough
TheBonsai
should give about 1.5GByte/s, if your storage can manage it
(at best i mean, not regulary)
RJarett
not enough
TheBonsai
then use 8GBit FC
RJarett
im going to 1 HDS and then also 2 ports to a nexsan with like 30 spindles, and then more to a data domain
TheBonsai
10GBit ethernet and 8GBit FC could be around the same (i guess FC is still a bit more, due to the high latency ethernet)
RJarett
8g fc is alot more expensive to deal with
thats why im doing this testing
see whats feasible
TheBonsai
more expensive than a complete 10GBit ethernet infrastructure? hm, maybe i missed some proce changes in the last months
*price
RJarett
nexus 5010 is cheaper than most 8gb capable fc switches and SFPs plus the additional cost on the san for those modules
TheBonsai
even (for a hypothetical 4-node cluster in one rack, as interlink) infiniband is/was cheapter than 10G eth, when i checked it some months ago
so i guess ethernet dropped the proces
RJarett
this is just 1 system
TheBonsai
*prices
RJarett
i push hundreds of gb through it from 12:30am-4am
i looked at xsigo
TheBonsai
same for me @FC
without any problem
i was surprised about the price of the NIC from HP, it was about $3500
RJarett
im seeing nics like 1k sometimes less
TheBonsai
well, i prefer to use those HP NICs in the proliants, for support reasons (and usually they use quality)
so i'm kind of bound to it
okay, work calls...
have fun sizing/managing your high performance rates dude ;-)
bye
(meant seriously, it's fun to work in this non-daily area)
RJarett
except when the budget isnt there to pull it off right
TheBonsai
well, there's always something that's not right, aye :)
knandan
Hi..
JPavleck[Laptop]
HELLO!
yopta
http://dpaste.com/164558/ - ppl, help me with OS roles
usn_work
Hi folkx, I'd need some advice with APEX. I want to update a known row in a known table, on button press. How can I do that?
I have a button, and I have the page that should do it, but I can't see the connection. Is it a computation? Or a process?
Rudemeister
test
hali: test
hali
tset
usn_work
ttes
sett
estt
;)
Rudemeister
ok
yopta
http://dpaste.com/164558/ - ppl, help me with OS roles
usn_work
I'd need some advice with APEX. I want to update a known row in a known table, on button press. How can I do that?
I have a button, and I have the page that should do it, but I can't see the connection. Is it a computation? Or a process?
yopta
usn_work, I don't know
usn_work
got it
dballester
hi usn_work ;)
usn_work
it's a process
and the button does a submit, the process runs at submit. anything else is an "execute immediate" because the process can do PL/SQL
hi dballester
dballester
how are you? long time :)
yopta
f1 f1!
os roles
help
idletask
Hello
I need to create a database link for a datapump data import operation, with the USING parameter
Can I use a full connection string in there and not obligatorily a symbolic name?
usn_work
yes
just use the brackets in on line
idletask
usn_work: I didn't quite get your last sentence
usn_work
you know tnsnames.ora?
idletask
Yes I do
usn_work
just copy the entry into one line, an wrap it in ''.
that'S YOUR USING CLAUSE
sorry for caps
idletask
Ah OK
I used simple quotes instead and it works just as well
create database link foobar connect to theuser identified by "thepasswd" using 'theentireconnectstringhere';
« prev 1 2 next »