logs archiveIRC Archive / Freenode / #oracle / 2010 / February / 1 / 1
usn
buzz
rhqq
hi! anyone here?
holly crap, according to orafaq that channel has to be most active.. and noone here :/
Chancellor
lol
RuslanPopov
morning
usn_work
morning
MOUD
Hello
I have a varchar2 field that contains both letters and numbers and I want to search for all numbers that are equal or greater than 9 digits. Also, I use substr because the field contains many different data. How can I do it? I tried using some functions but didn't work.
hali
MOUD: you probably need a subselect which filters out all rows with letters, you can use a regexp to do that... then just treat the varchar as a number (or cast it)
cofeineSunshine
helo
can I use database links to call stored procedures on remote databases?
crono-
I've installed the oracle XE client, how can i set it up so that sqlplus connects to the right database system? Theres no "server/network/admin/tnsnames.ora" file/path so i'm not sure thats it
Ok, how can i specify my database in the form of an IP/PORT ? I tried using user/pw@10.21.4.102:1521 but thats no good
         

hali
no
user/pw@tnsname
or
user/pw@//host/SID
crono-
aha
hali
port defaults to 1521
crono-
sweet
yeah it worked
with just one database, the tnslistener aspect is kindof moot
solar_sea
Where can I find documentation about the KEY="..." in the listener.ora for PROTOCOL=IPC ?
After a crast, my listener.ora went missing, I've returned the listener with ipc but it doesn't show up in EM
idletask
Question wrt v$session... Is it normal that it will NOT display the session we are currently connected with?
RuslanPopov
as far I know, it displays every sessions
TheBonsai
it does
knandan
Hi..
i am using prepared statements in my code..
why is it that sometimes the prepared stetement fetches the data and sometimes not..
stede
how to create a trigger which prevent delete/update on a row? some solutions i found via google don't work :-/
hali
stede: use an 'instead of' trigger
stede
hali: not sure what you mean ... i tried it like this so far: http://pgsql.privatepaste.com/8ad38075c6
hali
http://www.psoug.org/reference/instead_of_trigger.html
or if you want it with the condition, stick the condition in the actual trigger, not 'WHEN' in the defintion
sinesio
hi guys, can someone please explain me how SNMP works on oracle?
hali
stede: or even add a check constraint...
stede
hali: no ... id 1-9 may exist but should be not touched by the user
sinesio
e.g: if i hava an oracle Database 11gr2 and I want to send SNMP traps to an external platform in case the DB went down
hali
so whats wrong with a check constraint?
sinesio
sorry for interrupting your conversation :(
stede
hali: i cant set the check because there is a entry with id=1 ... i get an error from the dba if i now try to set up the check constraint for id>=10
and i cant create a instead of trigger on a table (error msg from oracle if i try to comile the trigger)
okay.. it compiles successfully like this: http://pgsql.privatepaste.com/212eec438f but dont work - i can delete/update it anyway
         

hali
stede: http://pastebin.ca/1774266 works
stede
so "new" was my fault
yea
thanks .. with "old" it works as it should
sinesio
can someone help me?
can someone please explain me how SNMP works on oracle?
hali
stede: if oracle has crashed, it can't really kick out a trap can it?
sinesio
or waht do i need to get oracle database to send SNMP traps to an external platform?
nop, but OMA or OMS
stede
hali: mhm .. you're right. it cant work after it raises the error
sinesio
you are talking about SNMP traps right?
stede
no
hali: thanks for help! i'll shut down this vm.. cya
idletask
RuslanPopov, TheBonsai: well, it's not what I see here, but maybe it's because I have dbvault installed
brb
phimic
hi all
i get a "ORA-12505:TNS:listener does not currently know of SID given in connect descriptor" error
how can i start i specific listnener, srvctl start listener -n node1 -l DB1 does not the trick
TheBonsai
it doesn't say the listener isn't found
it says the listener doesn't know about the database (SID) you requested
idletask
Hello again
Anyone using TDE?
(with 11gR2)
phimic
TheBonsai: Service "VPX" has 1 instance(s) <-- but it should be have 2 instances
TheBonsai: tnsnames.ora looks ok
mastro
I'm creating a table and oracle automatically create an index for the primary column. I don't want oracle to create the index because I'll create it on my own later. Can you explain me why oracle is creating that index and how can I avoid it? here the queries: http://pastebin.com/m3e4828c8
hali
mastro: the primary key is already indexed
mastro
hali, I know, i'm asking "why" it is already indexed since I haven't indexed it.. the index name as been called something like sys_XXXX
hali, I mean: oracle is creating the index without me telling it to do so. Why? How do I say to oracle to do not create that index?
hali, but i'd like to understand why it is creating the index in the first place
idletask
mastro: Oracle, and any RDBMS for that matter, will always automatically create an index for a primary key, there's no avoiding that, for the simple reason that otherwise, inserting into the table would take too long
Namely, it would take O(n) to insert instead of O(log(n))
mastro
idletask, that's strange because I have other tables with primary keys and I haven't had this problem with anyone else. I see anyway.. may be I just haven't manually created indexes for the others
idletask
mastro: are your primary keys declared as such in the DDL in these other cases? Somehow I doubt it
mastro
idletask, yes they are... like I said.. i probably don't manually create index on them in the other tables
idletask
mastro: the primary key constraint implies the unique constraint, don't forget about that
And Oracle needs to check that constraint before it can insert
mastro
idletask, yes I got it :)
idletask
Just imagine inserting into a million-row table _without_ an index on the primary key
mastro
idletask, can't I tell oracle to substitute his internal index with the one I define?
idletask
mastro: which would be, what, a composite index?
mastro
idletask, no, substitute it, rename it or whatever
idletask
Ah
Well, that's untested, but you can create another index by your name on this same key and declare the "builtin" index as unusable
That _may_ work
mastro
idletask, thanks
idletask
But if you do so and it does work, document it, for sanity's sake :p
So, no one is using TDE?
phimic
usn_work: hi Martin
idletask
OK, I'll ask my question straight away, then
Is it possible, with TDE, to use several encryption keys in one instance (say, one per tablespace)?
usn_work
hi phimic
phimic
hi usn_work
usn_work
all sup?
phimic
usn_work: i have a little problem with my rac, ORA-12505
usn_work: i tried srvctl start listener -n orac1 -l VPX1 but i get "CRS-0210: Could not find resource ora.orac1.VPX1.lsnr"
lsnctr status returns only one running instance "Service "VPX" has 1 instance(s)"
usn_work
does the CRS think the ressource is on or off?
is remote_listener properly configured on both instances?
does the listener run on both nodes?
phimic
usn_work: http://pastebin.com/d5d3f2ea0
usn_work
this is 1)
phimic
usn_work: and http://pastebin.com/d3b3292e4
usn_work
what does lsnrctl status say on both nodes?
what's the instance you want to connect?
phimic
usn_work: second node http://pastebin.com/d16467338, to instance VPX
usn_work
okay, it's registered on both instances
err
it's registered with both listeners
Who does get the error? A client?
phimic
usn_work: sqlplus SYS@VPX1 AS SYSDBA and srvctl status database -d VPX shows me that instance VPX1 is not up
idletask
Maybe it's a tnsnames.ora configuration issue then
usn_work
okay, what happens if you start it with startup? alert log entry?
the db start should not depend on the tnsnames entry
phimic
usn_work: i will try
idletask
Well, the user@instance form of sqlplus does depend on it being correct, doesn't it?
usn_work
yes, but this is a flu, a dead instance is a heart attack that's to be cured first
phimic
usn_work: srvctl start instance -d VPX -i VPX1
returns CRS-0215: Could not start resource 'ora.VPX.VPX1.inst'
usn_work
didid you try with sqlplus and startup?
and what dos alert log of this instance tell you?
phimic
usn_work: $ORACLE_HOME/orac1_vpx1/sysman/log ?
usn_work
what version? 10g?
usually $ORACLE_HOME/admin/SID(bdump/alert_SID.log
usually $ORACLE_HOME/admin/SID/bdump/alert_SID.log
sinesio
how can i check my repository version? I have oracle 11gr2 version installed
phimic
usn_work: /opt/oracle/admin/VPX/bdump/alert_VPX1.log is empty
sinesio
cause i need to install oracle grid control 10.2.0.5
and i need repository version to be something like 11.1.0.6 (Pre - Certified), 11.1.0.7
usn_work
phimic, on your node1?
phimic
usn_work: yes
usn_work
don't know your setup, what's configured for bdump destination on VPX1?
phimic
usn_work: bdump direcotory was right only file does not contain any lines
idletask
Grr
usn_work
do you get an error when you start it with sqlplus / as sysdba?
idletask
I hate database vault :(
usn_work
if yes, please copy&paste includign command
idletask
PL/SQL question: I take a parameter as an argument, how do I do a "composite" parameter out of it in the procedure? ie, I have foo as a parameter and I need to have it as is in one part of the procedure and use foo$cm in another part
dogmeat
how can i reset inactive connections, from 'status' of v$session?
zasz
alter system kill session
usn_work
immediate ;)
zasz
that is rude
usn_work
but effective. DBAs are always rude, isn't it? :P
zasz
BDBAFH
usn_work
?
ah
ok
was slow
zasz
that's what she said
usn_work
:)
KWhat_Work
is it possilbe to have a foreign key in side a type as object ?
rizzo
I've never heard of that
why would you want to
types are not storage
sinesio
how can I check my repository version?
i'm running oracle database 11gr2
but installing Grid Control 10.2.0.5 it says that my oracle repository has to be 10.1.0.7
is there someway to verify this?
does this have something to do with the parameter 'compatible' in show parameters?
* sorry it as to be 11.1.0.7 not 10.1.0.7
KWhat_Work
What about a table with REF Type (I am assuming thats a pointer) Can I have restrictions on that? On delete cascade? Not Null? Maybe others?
ramdac
werd
« prev next »