logs archiveIRC Archive / Freenode / #oracle / 2010 / June / 3 / 1
usn
qwak
Leftmost
Anyone happen to know where I can find the tnsping utility? I'm attempting to setup monitoring of one of our Oracle servers and can't do it without this.
rizzo
Leftmost: it is installed with oracle server or client
not sure about instantclient
should be in $ORACLE_HOME/bin
Leftmost
Hmm. Doesn't seem to be in instantclient.
rizzo
not surprised
but disappointed
I swear I had it though
I don' thave an instantclient setup available atm
Leftmost
Does the client have a headless install? Last time I tried to install it, it a) was GUI-based and b) failed.
rizzo
guess not
Leftmost: yeah it should
use slient mode with response file
just like with a server
http://jasonvogel.blogspot.com/2007/10/oracle-instantclient-tnsping-and-plsql.html
not really a good solution
but it's there if you need it
Leftmost
May have to do that. Don't really want a full client install on this box, since it's just a monitoring server. Thank you.
rizzo
Leftmost: I guess you could just do a login with sqlplus
if you're monitoring connectivity
if listener is down you'll get a TNS error
like ORA-12XXX
ORA-125XX even
iirc
what are you using to monitor? nagios?
         

Leftmost
Yeah.
rizzo
nagios needs tnsping?
I'd assume it is all perl-based
Leftmost
Well, the Oracle plugin I'm looking at uses tnsping for monitoring connectability.
rizzo
is it the german one?
Leftmost
Yeah.
I suppose I could use connection time.
rizzo
avaia_: you use that one?
http://labs.consol.de/lang/de/nagios/check_oracle_health/
that one?
Leftmost
Yeah.
rizzo
surprised that it would want to use tnsping
krb_hnd
hi
can I know the best way to transfer dmp file via network
BigODBA
krb_hnd: scp/ftp/rsync
use any one of the,
*them
krb_hnd
ok
thnaks
stede
which role i have to grant to the user "abc" to allow him to use dbms_lock.sleep() in his procedures?
dballester
hi to all
anyone tried the _disable_logging parameter? :)
hali
a few times
for initial massive bulk loads of new datawarehouse stuff
dballester
the same :)
rac 10g
but when i try
alter system set _disable_logging=TRUE scope=spfile sid='*'
i get ORA-00911:
invalid char for the first _
iirc, i activated previously undocumented params with alter system set without problem
this is normal?
stede
I used "myRow TABLE_NAME%ROWTYPE" in the declaration of a procedure and then load a row into it. how can i insert this row in a other table (with exact same rowtype indeed!)?
got it ...
drdimitri
stede: The best way is: INSERT INTO <table> SELECT ...
stede
drdimitri: i manipulate tham cols bfor inserting it ;-)
drdimitri
stede: Ok so if the manipulation is not possible in the select, you should use a collection and the forall statement: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/forall_statement.htm Its much fasteer then processing a single row at a time.
stede
ok.. I will take a look on that! Thanks. Now i have to find out why to_timestamp(sysdate,'dd-mm-yyyy hh24:mi:ss,ff') returns no time and only day/month/year
         

drdimitri
because to format a timestamp you have to use to_char
and to print the value oracle implicit uses you NLS settingt to convert the timestamp back to a string
stede
ah .. but to_char donÄt support fractals of seconds .. right?
yea it don't
drdimitri
Of course it does, but sysdate is a date and not a timestamp. Use current_timestamp instead
A date has only seconds
stede
ahhh THAT easy? ;-)
drdimitri
yeah ;)
stede
so .. now i got a pl/sql block which works fine. but if i put it in a while-loop it don't work.
do not wonder about the loop ... it's an endless loop on purpose: http://pastebin.com/agLmqWAs
ygip
Can someone explain why the first statement of these takes a lot more than twice as long as the second? select tbl1.*, tbl2.* FROM tbl1 LEFT JOIN ON tbl2 WHERE tbl1.col1 = tbl2.col1; AND the second only select one col in the second table: select tbl1.*, tbl2.col1 FROM tbl1 LEFT JOIN ON tbl2 WHERE tbl1.col1 = tbl2.col1
stede
stede: : nervermind the paste above ... !
ygip
tbl1 and tbl2 obly consist of col types which are types: date, nvarchar2 and number
drdimitri
stede: about your pastebin: You can do this in one singe sql statement. No need for all this slow-by-slow processing in pl/sql: insert into rfm_meas select fixmsecs_down(current_timestamp,400),null as id,3 as box_id, other_columns ... from rfm_meas_loop where ...
By the way: I don't understand why you convert between string an timestamp. Just us the timestamp field as it is. Don't convert anything around it.
stede
timestamp ... because before with sysdate i'd tried with to_char and dont change it afterwards ;-)
ok ... i will change that ... but for know it dont cause any performance problems
but
drdimitri
Do you use this code in a multiuser environment?
stede
nope
drdimitri
Ok because in a multiuser env it will not work proper
stede
that procedure is only for a testing/demo enviornment
schoud be okay for that
... insert into * select ... -> have i to name EVERY field? or only those ones i want to change?
drdimitri
every field
stede
:-/ ... 61 cols ... okay :-) i'll take a cup of coffee
drdimitri
well just generate them: select column_name||',' cols from user_tab_columns where table_name='RFM_MEAN_LOOP'; Now export the result in a text file and you have all your columns.
ygip
insert into doesnt need name oif columns to be inserted into if you are inserting something into ALL ROWS
stede
ygip: it was only ONE row i would select with the where statement ... but 61 cols and 3 of them i wish to manipulate like drdimitri mentioned above
kingping
Hello
Is Oracle 11g shipped with APEX ?
drdimitri
kingping as far as I know yes, but there are already newer versions available
kingping
drdimitri: I see, thanks.
Insolit
hello guys i really need some help here
i need to unset a parameter in oracle
in my case, optimizer_dynamic_sampling
if i execute show parameter optimizer_dynamic_sampling i get the value 6, but trying to remove it, i get the error that the parameter is not defined in spfile
which seems to be trye
as i've create the pfile from spfile and can't find the parameter there
so my question is, if the parameter is not set by the spfile, what is setting the parameter then?
drdimitri
optimizer_dynamic_sampling is set to 1 in 9i and to 2 in 10g and above by default
Insolit
no way i can delete it?
or shall i put it back to 2?
drdimitri
alter system set optimizer_dynamic_sampling=0 scope=both; will switch dynamic sampling off.
But why do you want to do this?
Insolit
SAP recommendation for OLTP systems
OLTP: Do not set OPTIMIZER_DYNAMIC_SAMPLING
so by this they want me to put it to 0
drdimitri
SAP... well but ok ;)
Insolit
i need to follow their recommendations, otherwise they'll be messing with anything whenever an error is reported
drdimitri
Of course.
Insolit
anyway i don't understand if they want it as the default, or as 0.
tipme
how do you define full column reference in Oracle, i.e somedatabase.tableN.columnM?
stede
why this works on one db but not on another one: WHERE acqtime > to_timestamp('30-05-2010 17:10:00','DD-MM-YYYY HH24:MI:SS') ?
and there a rows with acqtime-timestamp newer than the given date. .. but the query returns nothing
confused
wedjat
hello
is it possible to create a trigger in oracle in sqlplus ?
hlavaty
hi is there a way to write query "select :1, ..." where :1 is a number parameter? I could substitute the actual number in time of the quary, but I would rather pass it as a parameter, thanks
s/quary/query
knandan
Hi
how can we find the time consumed my mview to refresh itself..?
oops
how can we find the time consumed by mview to refresh itself..?
can any expert guide me on this?
dddh
hm
what if I have just one datafile from our old instance
is that possible to make that restore that schema and everything that was in there?
nachox
http://www.oracle.com/technology/support/patches.htm always required you to login? where can i check the latest patches without having to authenticate?
drdimitri
nachox: If you don't have an oracle support contract you don't get any patches.
nachox
i do not intend to get any patches, i want to know whether a db i'm reviewing is patched already. i only have a version number
drdimitri
Post the version number
nachox
a sec
10.2.0.4.0
drdimitri
which platform?
nachox
linux
drdimitri
Ok. It is patched but not with the latest patch which would be 5
nachox
i need a support contract to check even that?
drdimitri
No. You only should take a look here: http://www.oracle.com/technology/software/products/database/index.html
here you can download the latest release version for a specific platform ;)
hlavaty
hi any oracle expert around? how could i reformulate the query "select :1, ..." for oracle?
drdimitri
hlavaty: don't know what you mean. select :1 from table is a correct query in oracle if you use pl/sql
hlavaty
drdimitri: thanks, and if i'm not using pl/sql but sending the query via oci?
oracle gives me ORA-01008: not all variables bound
i think it cannot cope with the bind variable straight after select...
if i replace the :1 with the value, it works
but I want to pass the value as a parameter
any ideas?
drdimitri
which programming language do you use?
hlavaty
drdimitri: well, i'm compiling sql queries in sexp format (in common-lisp) to strings + bind parameters and calling oracle via oci
the compiled query seems fine, it just seems oracle doesn't like the parameter in select_list
if i use "select 123, ..." oracle doesn't complain
drdimitri
The parameter is ok, but you did not bind any value to it
hlavaty
i did, that's why i'm puzzled
drdimitri
I'm not sure what you mean "via OCI" you need a driver for that and I don't find a Lisp driver for oracle
hlavaty
i have a lisp -> cffi -> libxoci.so
c bindings for oci lib
drdimitri
Ah ok. C
hlavaty
my query has three parameters

and binding works
just the confusing error when the parameter is there
drdimitri
This might help you: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci05bnd.htm#i436225
hlavaty
maybe, can a parameter be reused? i.e. :1 referenced twice in the query?
drdimitri
The whole Statement can be reused because it is stored in the Shared pool (a memory area in oracle)
hlavaty
but i mean can i write select :1, :1 from dual?
or do i have to write select :1, :2 from dual and duplicate the parameters if they are the same?
drdimitri
You can do that, but you only would have constants in your sql
hlavaty
what do you mean by constants?
the query select :1, :1... should give me whatever i passed in as parameter
drdimitri
Lets say you bind the String abc to the Bindvariable :1 now it would be select 'abc', 'abc' from dual
And 'abc' is a constant value
for every row
hlavaty
yes that's fine
that's what i want
i just don't want the constant to be hardcoded in the query
drdimitri
Well you don't have to use Binds for that. Create you sql String dynamically using string functions or use the OCI Bind Functions I posted you but using OCI is a "little" mor complicated the just building a string
Linke that select ###1###, ###2### from table and then stringfunion.replace('###1###',myvalue)
hlavaty
that's what i'd like to avoid, building queries for each constant; better would be pass it in
thanks for the link, btw. i went through how to do binding already and don't see an issue there
i actually haven't seen any example on the web where would somebody use a parameter inside the select_list
drdimitri
well because normally you use binds in the WHERE Clause
hlavaty
i know, but i have this case, where the bind parameter is in select_list
so it is possible in pl/sql but not in sql queries via oci?
or is there a way of rewriting the query to get it out of select_list?
drdimitri
It is, use the OCI Bind Functions explained in my link.
hlavaty
that's what i'm using, OCIDefineByPos for the parameters
but oracle doesn't seem to notice the binding when the parameter is in the select_list
hmm just tried simple example and it works SELECT :1, :1 FROM DUAL => (123 123)
path
any recommended web application for logging and stadistic purporses ?
Xgc
Oracle for sadistic purposes?
path
haha
nope, I just need a web application that parses logs and draw them
BigODBA
path: If you know what you need parsed out of logs, then there will be numerous ways you can write a web app or use one of the open source stuff
path: If the logs are Oracle logs then you have EM dbconsole or Grindcontrol
« prev 1 2 next »