logs archiveIRC Archive / Freenode / #oracle / 2010 / May / 4 / 1
b_52light
hi
ppl do you advice for or against using java procedure stored on oracle
Ramen
for?
b_52light
Ramen, i have this function http://pastebin.com/cyHkvZkt and i'm scared to convert it to sql :d
kaylee_work
hello?
hey, can someone point me in the right direction for the correct query format for this? http://pastebin.com/YEEyn9Lp
I'm trying to look up the text from the same table for two different columns
I'm not sure if I should use joins or maybe sub query?
Caelum
kaylee_work: select a.id, a.name, b.issue_text prim_issue, c.issue_text sec_issue from table_a a join issues b on a.prim_issue = b.issue_id join issues c on a.sec_issue = c.issue_id;
what's the difference between LONG and LONG RAW
kaylee_work
Caelum: but id for prim_issue and sec_issue both relate to the same table, b
Caelum
kaylee_work: but you're joining it twice, b and c
kaylee_work
ooooh
just giving it a different reference
I see
hum... toad doesn't seem to like that
invalid identifier
Caelum
kaylee_work: it should show you which one with a <*>
aha, LONG is a character type and LONG RAW is a binary type
         

kaylee_work
THANKS!
got it working!!
admin0
how do I enable one user to have different tabelspace .. i have one user to one tablespace .. how do I enable the same user to different tablespaces ?
dsdel
admin0: http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/users.htm#15280
admin0
dsdel, i need to create profile ?
dsdel
no, assign him quota to the tablespace
admin0
dsdel, thanks
found it
alter user username quota unlimited on tablespacename
dsdel
yes :)
you're welcome
admin0
(Action) passes a big pizza and beer to dsdel
does putting indexes on a different tablespace improve performance
dsdel
you put indexes on tables and if you use them, they nearly always improve performance
admin0
i have a big table with around 800M rows .. and on top of partitioning, i am checking other ways to improve performance
nope
hey
KeiKurono
morning
cmug
How do I grant user B to be able to create table in user A schema?
ygip
wow batch size is important for insert performance in JDBC, i started at 10, ended at 80 with more than twice the throughput
mator
cmug, http://www.experts-exchange.com/Database/Oracle/10.x/Q_22960116.html
cmug
mator, yeah, I found out that I need to give create any table.. I don't want to do that, so I flipped it the other way around
so the schema owner has select privilege from the source table now
stede
is tehre a bug in sqldeveloper exporting data as insert script? the insert-commands generated have more values than columns?!
ahh .. i see ... that dont work, because the generated inserts dont have the correct column-order
dsdel
as long as the first definition is ok
insert into blah ('columnb','columna') values ('colbvalue','colavalue')
stede
dsdel: yes .. and the geenrate insert don't care of the a-b-c-... order
generated
dsdel
strange :)
         

stede
and extras->databse export throws exception about some heap-stuff
the table has 60 cols .. so i checkt first 20 and they arent in the correct order
and now i dont noe how to get the data from one db to another
noe=know
i will write a litte php script
.. it seems the problem is the decimal seperator (which in this case is , and not .)
some ideas?!
mator
stede, possibly check your/database locate, decimal separator is vary between different locales
s/locate/locale/
sytse
hmmm... that's interesting.
if I base a forms data block on a table with a clob, and set the maximum maximum length (64k) on it, and query a very long clob (well, around 64k.. it was almost 70k in this case), and then execute the query and do an edit to the clob field, the application server hangs..
but when I do it with the length limited to 40000 bytes/characters, there's no problem
(forms 10g btw, dunno if it's fixed in 11g)
stede
mator: sure and it should be the way it is ... but "," is fix for use as seperator on Inserts to seperate colnames and values
there it should be reconvert the decimal seperator from , to . while it generates the insert script.
mator
stede, depends on how you do export, dig for options and fields delimiter, possibly changing it to something else than comma
TheBonsai
1
stede
mator: that works for CSV .. but not for insert ;) ... now i export as csv ... import to worksheet, save it, replace to_date with to_timestamp and then launch the script
usn_work
.
stede
i get troubke with an insertscript. sqldeveloper calls some errors ... it is better to copy the insertscript to the server and run the script in sqlplus (or is this possible?)
usn_work
without knowing the errors its hard to tell
stede
as the language of sqldeveloper is set to german i dont know the english read of the message
hlavaty
hi guys what is the difference between datasource and database (as per create/drop database)?
stede
something with blocked conenction maybe ... seems it try to open several conenction at once and block it self ... in the script output i see some unique constraint errors so i guess it try to insert more then one time
usn_work
what about an ORA-XXX error code?
And less "maybes"
stede
there is no ora-code ...its an simple retry/abort message from sqldeveloper
usn_work
I speak german, just tell what the message is
stede
"Verbindung belegt"
now i run the script on ssh-shell with sqldeveloper directly on the server and there are no more errors
usn_work
Verbindung belegt means, that SQL developer does run anything else against this DB at this time
multiple tabs are using one session in SQL dev.
stede
but why sql-dev tries to call the insert script multiple times?
usn_work
if you want to run things simultanously, you need to open multiple SQL developers
stede
no i only want to run that single script
usn_work
did you have more than one tab open?
stede
only the file was open
usn_work
strange
stede
start sqldev -> open file *.sql -> hit f5
usn_work
strange
stede
may be it depends on the wlan/vpn connection!? i donno ... but its okay if it works with sqlplus
usn_work
I don't think so
stede
agree
hlavaty
what is the oracle version of (echo 'create database `%s`;' | mysql -u %s -p%s) or postgresql createdb -h %s -w %s -U %s? for testing purposes i'd like to drop and create database from scratch to make sure all is clean but i don't understand how it really works Oracle Express Edition. any ideas?
DiscordianUK
A mysql 'database' is more like an Oracle schema than a seperate oracle instance
*warning* MySQL is a toy database
hlavaty
DiscordianUK: i see thanks.
DiscordianUK
Oracle-XE limits you to a single instance called 'XE'
hlavaty
DiscordianUK: so when i create a user via apex, it creates a new schema for that user?
DiscordianUK
Nope
It justv creates a new oracle user
which can have it's own tables etc
hlavaty
so why i can see different tables depending on the user i use for login via apex?
DiscordianUK
because they have grants to view/etc different tables
hlavaty
so all these tables live in the single instance called 'XE' but different users see different tables depending on permissions?
DiscordianUK
Correct
Trengo
tables dont belong to databases
tables belong to users
its not the same as mysql
hlavaty
Trengo: i don't understand, and how does schema relate to all this?
usn_work
a schema is a namespace, every user has one single schema
Trengo
schema is almost the same as a user
usn_work
if you have two schemas / two users, each can have a tbale with the same name and the RDBMS does not complain
hlavaty
usn_work: so this schema is "created implicitly" when creating a user?
usn_work
If you CREATE USER, you create his schema implicitely
Trengo
user is schema plus authorization
usn_work
yes :)
Trengo
and schema is set of objects belonging to user
usn_work
for the poor MySQL converters, say SCHEMA(Oracle)=database(MySQL)
simplified, but I think in this case it will do
hlavaty
usn_work: good, now if i want to run tests with guaratied empty schema, how do i create/drop schema (similarily to create/drop database on mysql/postgresql)? just create/drop schema?
usn_work
drop user something cascade;
create user something...;
crant connect, resource, unlimited tablespace to something;
=> empty
hlavaty
looks complicated, and these drop/creare user command must be run from sqlplus as user system?
usn_work
yes, or as sysdba
hlavaty
usn_work: thanks a lot!
usn_work
np
look up the create user syntax carefully please
hlavaty
is there any catch?
usn_work
its just a bi**h (not intuitive)
hlavaty
ok thanks
RJarett
not really a good testing method
usn_work
Clearing the schema, or clearing the schema this way?
hlavaty
RJarett: what would be a better approach
RJarett
clearing the schema
what do you want to accomplish with your test?
hlavaty
i want to get test results;-)
RJarett
of?
hlavaty
a comprehensive test suite of our application
RJarett
and... how exactly is an empty schema going to help?
how an app works after having data and running for a while is MUCH different than on fresh install
hlavaty
well, these tests should not be influenced by any garbage that could potentially be left over in db
RJarett
so are you just testing the install process
and nothing else
or trying to tune an active app?
hlavaty
i also tests several different code branches, with several different db backends etc. these branches can have different db content etc. so the db should really be empty i think
RJarett
and you keep purging your schema... so if something happens, you dont really know what the difference was
hlavaty
what do you mean? i know why the tests failed from test logs, i don't need to preserve db content
RJarett
so you are doing only functional testing and nothing else?
hlavaty
i suppose, what else do you have in mind?
RJarett
well when you get into performance testing, the age of data and its usage changes things greatly. so purging tends to cause alot of problems
hlavaty
RJarett: yes, but i'm not doing performance testing. thanks for ideas!
rizzo
Anyone here run 11gR2 ASM non-RAC?
avaia: you do, right?
avaia
yeah
rizzo
I'm having trouble getting the stack started after a server bounce
linux x86-64
avaia
did you start it harder?
rizzo
what are you, my wife?
hali: hi5
is it supposed to start automatically on reboot?
I'm talking about all the CSS and HAS services
avaia
there is a bug, I think
rizzo
avaia: also note that the PSU for GI is only for RAC GI
avaia
something with the init scripts
oh, good
rizzo
but you still need to apply patch 88--something to GI before applying PSU for DBMS
which is what I'm trying to do
hali
rizzo: i do, nfs
usn_work
my GI does not start as well :(
rizzo
I try both "/etc/init.d/ohasd start" as root or "crsctl start has"
usn_work
I changed the ASM_DISKSTRING, now ASM gives me a ORA-0600
ohasd runs, but CRS refuses due to missing OCR due to missing ASM
rizzo
both fire off some procs, but in the end I just have $GRID_HOME/ccr/bin/nmz -cron -silent running
avaia
if you start asm by hand, everything is good, right?
rizzo
ASM won't start because the underlying HAS services aren't running
I'll try again
usn_work
nope, if I start ASM by hand I get a end of file in communication channel
rizzo
SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
$ ps -ef | grep grid
oracle 5198 1 0 May03 ? 00:00:00 /opt/oracle/app/grid/product/11.2.0/grid_1/ccr/bin/nmz -cron -silent
oracle 13841 13615 0 08:26 pts/0 00:00:00 grep grid
usn_work
this is a typical single instance error :)
rizzo
never had such problems with 10g and 11gR1 :p
usn_work
ack
rizzo
usn_work: so what's the solution
avaia
guess I must have done something right, damned if I know what though
usn_work
rizzo, I don't know, I've got to finbd out why ASM does crash that ugly way
sidh
greetings gentlemen
usn_work
has somebody an idea how to create a pfile from spfile for the Grid Infrastruture's ASM instance? I need to revert a parameter that causes a 0600 on startup
sidh
i'm creating an instance on 11gR2
avaia
usn_work: strings spfile > pfile
sidh
i create a 'create database' script with all the paths where control file and so on should be written, but a the execution of the create database script, it appends $ORACLE_HOME/dbs in the path of my control files and obviously i get a file not found error
RJarett
dont create your own create db script
use dbca
sidh
i never had this error before, and my 'create database' script has run successfully before, so did i miss something ?
RJarett
well obviously you did if its not running right
everytime people make their own script they screw crap up seriously
« prev 1 2 next »