logs archiveIRC Archive / Freenode / #oracle / 2009 / December / 14 / 1
nil_
Hi all!
I have a very simple question but I cannot get the answer for it with Google, etc.
I have a select statement which is quite long. Does not matter what it does.
I do not want to copy paste it into the client (Oracle SQL Developer), so I decided to put it into a procedure.
Which was not a successful idea so far... :-( Can someone please help me?
I just tried returning SYS_REFCURSOR which sounds fine, but now I don't know how to callit to get a result like a simple SELECT statement....
:-)
I managed to solve it. I just simply select it from dual;
"select fn(...) from dual;" displays it correctly.
Thanks for witnessing my adventures :-)
Have a nice day!
TheBonsai
does the flash metalink currently work? i'm getting ''security error #2048'' since some days, and i wonder if i'm the only one
mator
morning
TheBonsai
m00
fsck, the non-flash metalink only gives endless page updates for me when i re-select some field in the form
(Action) misses the good old buggy but working metalink
indu
hi all
i installed oracle 10g XE for project.net application
the connectivity was working fine , suddenyl due to power failue the server restarted
TheBonsai
happens, without a UPS
indu
afte rthe system is up
the oracle databse connectivy problem i am facing in the application when i am browsing
using /etc/init.d/oracle-xe restart works fine and shows that oracle started
but in browser the same is the error
ORA-12505 error
TheBonsai
does oracle run? (check the alert.log)
12505? sounds like something sql*net related
- is oracle running?
- is the listener running?
- does the listener know about the database instance?
indu
where would be the aler log
         

TheBonsai
no idea, i never used XE
indu
oracle is running, i see 1521 and 8080 ports listening in netstat
TheBonsai
1521 is not oracle
it's the listener
indu
how can i check whether the listener know the database instance
TheBonsai
lsnrctl status
usually
should list the service name you use, and if it has instances for it
indu
http://pastebin.ca/1714214
o/p for stauts
TheBonsai
12505 means "listener doesn't know the SID requested", in short
indu
this is tomcat log
http://pastebin.ca/1714218
hmm
echo ORACLE_SID
gives me XE
how can i bind the listener to my instance
TheBonsai
what does the listener say?
lsnrctl status
or lsnrctl services
is there a XE?
indu
the o/p for status i pasted here http://pastebin.ca/1714214
yes
DEFAULT_SERVICE: XE
TheBonsai
does it list an instance for XE?
indu
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
TheBonsai
hm
connect to the instance and say ''alter system register;''
and re-check the lsnrctl service names
indu
how can i connect to the instance?
mator
indu, with sqlplus usual procedure?
indu
sqlplus is showing me the port
mator
indu, clarify 'connect to the instance'
indu
butEnter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
TheBonsai
ORACLE_SID and ORACLE_HOME is set?
also the sqlplus is the one from that ORACLE_HOME?
mator
indu, http://www.tech-recipes.com/rx/226/login_to_oracle_as_sysdba_in_one_command/
         

TheBonsai
if everything is okay, then ''sqlplus / nas sysdba'' should give you a connection
if the instance is offline, you can use this to startup
unless XE does something i don't know
indu
mator as per that link, everything is fine
still i get insufficient priviliges
DiscordianUK
Is your user in the dba group?
indu
i am using the main user: system itseld
haven't created any other user
DiscordianUK
Err right
How did you install Oracle-XE ?
indu
debian package
oracle-xe_10.2.0.1-1.0_i386.deb
DiscordianUK
I presume that will do the same as the RPM package does on fedora
You should now have a user called 'oracle'
use su to become the oracle user
then do 'sqlplus / as sysdba'
mator
or add your root user to dba group
DiscordianUK
aieee
phimic
hi all
mator
ciao phimic
(some sort of italian greeting) =)
phimic
@825B mator
mator
:-D
phimic
;)
mator
privet
salut!
phimic
mator: have u got an rac running?
mator
nope :)
i have oracle installed on my local machine, but i rarely runs it
phimic
ok
mator
our section (4 people) almost doesn't have sql db's running, except mysql/postgresql
on my prev. job place we had some oracle installations
phimic
mator: ok i see, i have some issues starting crs on one node
since today one rac node wont start /etc/init.d/init.crs start doest not start the services
crsctl check crs returns "Failure 1 contacting CSS daemon"
where should i start to resolv the problem, interconnect is up
ping works properly
telles
drop public synonym SETEMVIEWUSERCONTEXT; drop ROLE MGMT_USER; drop PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS; drop user sysman cascade; drop user MGMT_VIEW cascade;
phimic
is there a tool to cleanup $CRS_HOME?
TheBonsai
that sounds like you still didn't check your CRS/CSS logs :)
phimic
TheBonsai: $CRS_HOME/logs/{crs,rac1} are empty
TheBonsai
phimic: is the CSS running at all?
phimic
TheBonsai: on one nodes yes but cannot start it on second node (no space left on device) X-)
TheBonsai
hehe
well, remove some old logs and check for core files and stuff
phimic
TheBonsai: u know the location for most logs and cores
TheBonsai
is there a database instance, too?
or asm?
on the same (full) disks i mean
mator
phimic, coreadm on solaris
/proc/sys/kernel/core_pattern on linux, but this could be tricky
most linux'es don't produce core files
TheBonsai
i think server systems should
because nothing is worse than a crash without any trace
anyways, in short i said "free some space"
whatever that means on his system
mator
rm -rf / ?
(Action) runs
hali
most implementations of rm rejects that these days :)
TheBonsai
if he takes this serious, then he deserves it
mator
echo 'hey kernel, get me some free space now asap!' > /proc/kernel
TheBonsai
you mix up kernel and admin
and redirection and phone
:D
mator
support call... this reminds me video on youtube about webserver restart, did you seen it? :-D
pretty funny imho
http://www.youtube.com/watch?v=7wRxASytPuQ
TheBonsai
i remember it
ever
how can i find out how an index was created ?
TheBonsai
"how"?
ever
which sql statement shows me how an index was created
like.. on what columns
TheBonsai
dba_indexes may contain that info
or user_indexes
ever
ok thx
hali
ever: dba_ind_columns
HeatHawk[AP3]
is there an archive for downloading older versions of oracle? I need 9i 9.2.0
mator
HeatHawk[AP3], http://forums.oracle.com/forums/thread.jspa?threadID=981354
just checked, at least it have 9i version listed, but i don't tried to download it
i mean edelivery
HeatHawk[AP3]
mator, wow, nice find dude! :D
phimic
i have problems to get the start the crs daemon http://pastebin.com/d33151fef
crsctl check crs returns "CSS appears healthy" but "Cannot communicate with CRS" and "Cannot communicate with EVM"
idletask
Hello
I have downloaded the two zip files of database 11gr2. I have unzipped them in two different directories. I have 'find -type f' in both directories and have found that they have no file in common
Given that these two directories weigh less than 3G together, does that mean that I can make a DVD out of them both?
Less than 2.5G, even
phimic
no rac user here?
idletask
Not me
TheBonsai
should work, both zip files belong to the same directory tree anyways (i.e. you'd need to unzip them in the same place anyways)+
phimic
hali: ping
mfilipe
I have a table with 20 fields and I want do a select without one field. The table is the PCFORNEC and the field that I don't want is CODFORNEC. Is there any way?
Xgc
mfilipe: Not directly. You could dynamically generate the SQL.
mfilipe
Xgc: how?
Xgc
mfilipe: or just explicitly specify only those columns you wish.
mfilipe
I want all fields without CODFORNEC
Xgc
mfilipe: Correct. SELECT field1, field2, ... adnd just don't specify that one you don't want.
mfilipe
hum...
ok, thanks!
mastro
is there a way to print a message in a oracle script? I'm catching exception and want to just print a message
i just want to "try" dropping a table (cascade constraints), catch an exception if doesn't exist and "log" a message saying: "table X doesn't exist, not dropped". Or something like this.
this is ok: begin execute immediate 'drop table banana'; exception when others then null; end; but it does not print anything
Dormir
afternoon
mator
fetch banana into a bucket
(Action) & banana run till tomorrow
mastro
:)
mator do you know how to do that?
boingolov
why I get ORA-31685: Object type DB_LINK: failed due to insufficient privileges when doing impdp ?
I am doing a schema level import, even get this error when using sysdba
can't find much info in metalink
this is oracle 11.2.0.1
interesting, it's just that one database link, it goes to a sqlserver gateway
other db links seem to work, even ones to the same gateway
probably irrelevant
next »