logs archiveIRC Archive / Freenode / #oracle / 2010 / February / 19 / 1
EFFINBUCKY
hello
tragicx
Hello
I'm looking for an expert in Oracle. Anybody awake tonight?
EFFINBUCKY
anyone here?
lol I don't think anyone is ever here.
room is filled with quiet bots
RJarett
anyone here use netapp? how many network file ops/sec are you seeing on a heavily loaded netapp during batch processing?
im trying a 3170 out and seeing about 6000 nfo/sec not sure how to compare that.
the throughput seems very low.
Reign1
hello, could anyone tell me if compres for OLTP is advanced compressing and needs separate licencing? as i cant use DEFAULT COMPRESS FOR OLTP, get error that i dont specify compression type, nonsence, so what can i do on 11.2? only DEFAULT COMPRESS?
idletask
(Action) doesn't use netapp, he uses compellent
I have a basic Oracle question about something I have never used... I need to refer, as a user, to a table owned by another user
Instead of typing theuser.thetable each time, is there a way to add a reference to this table, for instance thetable, as long as there's no table by that name?
Is that what is called synonyms?
OK, well "oracle 11g create synonym" in Google gave the answer
DimmuR
Hello everyone. I'm using oracle 8 and i can't find way to get table definition (for view i can go to all_views.text) anyone can point me?
idletask
desc thetablename
(short for "describe")
DimmuR
thanks idletask but desc shows only column names/types - no foreign key references
         

tijnie
DimmuR, have a look at user_constraints: desc user_constraints.
DimmuR
hmm there was some info in all_constraints but no info about fk
tijnie
Dimmur, that's not quite true
Columns constraint_type tells you what type of constraint (R for referential integrity == foreign key)
column R_CONSTRAINT_NAME tells you to which unique constraint in the referred table the constraint is pointing.
I believe all the information is in there.
idletask
Well, too late, he's gone
Can I know how many cursors are open at a given point in time?
Actively open, that is
Since if I select count(*) from v$open_cursor, the count returned is more than the open_cursors instance parameter :/
tijnie
idletask, try following : select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
(It basicly looks in v$sesstat and shows the statistic for "opened cursors current"
Also have a look at : http://www.orafaq.com/node/758
idletask
tijnie: thanks, investigatin
s,$,g,
tijnie
closed cursors are actually closable. Not all cursors are actually closed :-(
[-Haza-]
Morning folks. trying to delete records from one table that do not exist in another table (using a composite key)
This is what i have...
delete from TABRANCH
where IDBRANCH || IDCUSTOMER || IDDEST THEID NOT IN (
SELECT IDBRANCH || IDCUSTOMER || IDDEST THEID FROM TABRANCH@prod);
but i get the error "Error: ORA-00920: invalid relational operator"
Whats wrong with my syntax?
I must be missing something obvious
tijnie
[-Haza-], did you try removing the aliases? I'm not sure whether that is understood by oracle, whitin a delete statement.
(bit of a wild guess)
[-Haza-]
tijnie: Worth a try
hali
[-Haza-]: any why the concatinations?
it looks horrible
[-Haza-]
hali: it does but its a composite key
hali
where (a,b,c) not in (select a,b,c) will be quicker
[-Haza-]
hali: or really?
Okay. i'll give it a try
hali
you skip the whole concatination step
[-Haza-]
right after lunch! :D
hali
times two
tijnie
The concat probably is only necesarry if ther is some index on the concatenated values I guess
stede
is i tpossible to check the query execution time in sqldeveloper?!
idletask
It is by default
Is it there by default, I mean
You have the execution time right below the menu bar, on the left of the right pane
         

stede
whats the name of that pane/menu?
idletask
I don't remember offhand, sorry
knandan
Hi..
I am trying to execute this command:
EXECUTE DBMS_REFRESH.ADD('FRESHY', 'CRS_WEB_MATCH, CRS_WEB_HEADER, CRS_WEB, CRS_WEB_TARGET, CRS_WEB_CRITERIA'); COMMIT;
but i get the following error..
http://pastebin.com/m7b6b73bd
i see that there is no job called FRESHY
i just deleted that..
Grim147
Any apex users in here ? , is it possible change the address ? in oracle-xe using the default web server ?
knandan
how do i create a job?
usn_work
stede, set timing on;
and execute as script
knandan
I am new to sql/oracle
usn_work
knandan, ORA-23404: refresh group "VYRGYL"."FRESHY" does not exist
define it
it has nothing to to with a job
ADD menad add an entry, CREATE might be different
knandan
usn_work: thanks for the response..
how do we define that?
usn_work
use the docs, I don't know
vinnix
guys, do you know if Oracle has official suport at Fedora 10?
usn_work
yes we know. It has not. Use RHEL
Or SLES or OEL
idletask
Or CentOS
usn_work
Or AIX, Solaris and several others
idletask
Which is just nothing else than an RHEL
usn_work
CentOS is not supported
idletask
Yes, but it's an RHEL
It works on CentOS, it works on RHEL
usn_work
that's not relevant. :)
idletask
And vice versa
usn_work
If he asks for support, he does not ask for a cheat.
idletask
That's not really cheating... All my test installs are on CentOS, and the bugs I found there are as real as they are on RHEL (or even Gentoo)
usn_work
yeah, I know. But they don't support the name.
WORKING platforms are something entirely different
And I agree, I've never seen a bug on an unsupported Linux that would have been caused by the OS. After surviving the install- and db create process, it works usually
Finally, it's just a userspace software running where it belongs
idletask
Exactly
(well, except for the shm settings at the kernel level)
usn_work
hehe
idletask
Anyway, problem solved here: all our test platforms run CentOS, and we have RHEL in production
corpxicle
you know you can get free rhel licenses if you join their partner program
for testing that is
idletask
Well, we are not partners
corpxicle
it costs you literally nothing
no money and no time
idletask
I guess we could be in the future, though, we're very interested in their virtualization technology
In replacement for ESX
corpxicle
it was a nobrainer for me after i realised its free in every possible regard to become a partner
and instant licenses to all redhat releases
idletask
Hmwell, it deserves more attention, I guess
But I'm not the one who handles the money bag...
corpxicle
as i said, no money =P
stede
can i use sum(), avg() etc on cursors?
idletask
No, since they compute on result sets
Oh, err, sorry
Yes you can
I was misunderstanding your question
stede
so i'm going to find out how to do that ;)
background: now there is a loop with fetch cursor into cr and then calculations will be processed for each row. I want to try if i can do some things with oracle functions - so maybe the performance would be better
idletask
Hmm, very unlikely
What sort of calculations?
stede
idletask: should i really eyplain? ...it will take some minute ;)
... the affected table has columns like: acqtime, a, b, c, d, e, f, .... z (61 columns over all) ... one row is a entry with measurement entrys and a time when it is acquiered
for example, one row for each second
now we want to merge alle rows for an 10minute interval into one row ... for this, there are different treatments for the columns a,b,c, etc
thats one of the cases i have to do ... another is
get a merged value (like above) each 10minutes and build an avergae over multiple days
yopta
re
ppl, I am working on OS auth with Oracle XE
got strange: http://dpaste.com/161536/
stede
now, for example, i'd like to do the last job. then i have query in php like this: select b,c,d from table(summultrows([some arguments])) .. summultrows inside calls vor each 10minute block: select * into row from table(sumrow(cursor(rfm_meas where acqtime>start and acqtime<end)))
yopta
at those time the os_roles=false, I don't understand what is happened
stede
i think i'll better paste somthing ;)
yopta
btw, I am RaD, just from home
stede
idletask: do you understand me?
yopta
f1 f1
I've broke my brain
stede
and why min(colname) dont return the minimumvalue?
ehh because i misstyped the colname :-P
idletask
stede: ow, that's complicated?
s,\?$,,
yopta
why I couldn't create view for a table from other scheme, if I easily can select from it
?
idletask
yopta, what's the error?
yopta
http://dpaste.com/161536/
idletask
yopta: well, doesn't line 7 say it all?
You don't have the create view privilege
yopta
I have, I granted
idletask
And what other privileges do you have?
select any table, I gather
yopta
http://dpaste.com/161659/
idletask
Or did issled_xx explicitly grant select on the table to arm_xx?
yopta
also, connect, resource
idletask
Do select privilege from user_sys_privs as user arm_xx, what does it have?
yopta
wait
http://dpaste.com/161662/
stede
idletask: logically nto complicated, but maybe to omplement it in oracle - yes .... why select a,b,c,d,e into myRow from ... dont work? (to much values)
yopta
stede, because :)
idletask
stede: no idea, I don't know PL/SQL enough to answer that one
yopta
select a,b,c,d into va, vb, vc, vd...
idletask
yopta: what does select owner, table_name, privilege, grantor from user_tab_privs where grantee = 'ARM_XX' return?
yopta
also, you may try to create a record variable and do select a,b,c into record_var
no rows selected
idletask
yopta: this is why you cannot create the view
yopta
I use roles
grant select, insert, update, delete on &issled_login..data_a to issled_group_a;
idletask
yopta: that won't do it, you must have the select privilege on the table granted directly to the user
yopta
grant issled_group_a to arm_xx;
ah, ok
I see
hm, no
why role doesn't apply the select from this table?
idletask
yopta: I don't know... A design decision, probably
But that's how it is :p
yopta
ok,
idletask
I don't see any real reason why this doesn't work, though
yopta
I need to create a solution which will use OS roles to allow access users some tables
idletask
You could ask this question on the forums, I'm interested in the answer as well :p
yopta
heh
I kill myself trying to describe the problem in english :)
idletask
Well, don't complain, I have to use dbvault :p
stede
idletask: no problem ... i will work a bit on it each day .. maybe i'll find a nice soulution some weeks later
idletask: it works for now, but i think with better solution it will take only 30% of the computing time it takes now
idletask
stede: you should try and pack the maximum amount of computing in the SQL query itself, this is how it will perform best
Even if it's a monster query in the end
The only thing to remember is that the relational part of the query must be isolated
stede
idletask: yeas i yust tested this few sconds ago ... with cursor in the function: ~440ms and with a direkt select using sum(),avg(), sqrt() and so on: ~140ms
idletask
You can do such things as select sum(c), avg(c), min(c), max(c) from atable group by <way to extract the time column by 10 mn intervals>
Or even select sum(c), avg(c), min(c), max(c), <way to extract the time column by 10 mn intervals> group by 5;
stede
idletask: yes, thats the plan ... next problem is, if i will keep it modular call a function like select a,b,c from table(sum3_d(stepsize,interval)) ... inside the function the query will be executed several times .. but i only need a,b,c not all the colums
which colums are needed depends onthe php-side code
idletask
What is this table(sum_3d()) stuff?
stede
maybe i have to create for each use-case one function... but then, if something changes in calulation i have to change it at several places
its vor comparing daily measured data ... buil 10min averages for each day and then calculate averages over the days ... so average every 0:10-value from each day
mayby a temp-tableis a good solution, putting the 10min averages for each day in one row ... next day, next row
« prev 1 2 next »