logs archiveIRC Archive / Freenode / #oracle / 2010 / March / 22 / 1
admin0
hi all
i am runing out of space on /u01 .. can i shutdown oracle, move one file from /u01 to /u02 and create a softlink ? will oracle be OK this way ?
could :)
thanks for listening
Junior
yello ;)
sidh
Greetigns Gentlemen
Greetings
Trengo
hello sidh
Kaoru_Nagisa
Someone knows if there is a minimum time of experience to pass the ocm certification ?
ziga^work
Hi
I got a problem with apex...
If i change something for example the label of a button
the change don't take affect in case of runtime
I see the change in the editor and it stay there but if I run the page the change take affect if a few minutes are gone
I think there is a problem with the database cache or the apex editor don't commit the changes in the database?
KeiKurono
hi
do you recognize this sql syntax? is from a dump file that i should load to a postgres db, and a oracle one.. :/
http://www.pastie.org/880769
it's oracle, how can i convert an oracle dump file to a postgres one? :/
Kaoru_Nagisa
KeiKurono, http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
First result in google search "oracle to postgresql"
KeiKurono
thanks i was wondering however if there exist some state of the art open source tool that does that automagically
         

Kaoru_Nagisa
don't know
but look at the bottom
KeiKurono
let's try it, thanks
nothing...
hali
KeiKurono: check in pg's contrib tree, there used to be a perl script or something in there
KeiKurono
really?
i have only some .sql files
ASDXX
hi, was trying to set up netdump to help oracle with some RAC nodes crashing but when they were fenced off, it only wrote out a 50gb file and no coredump. does a fence off technically not result in a crash and that's why nothing was written out?
hali
depends on what fencing mechanism you use
ASDXX
the fencing is done by the RAC software (if that's what you mean)?
hali
clusterware generally just asks the node to reboot
if oracle detects the node being "unhealthy" it just reboots it
and hopes it will rejoin the cluster
if the kernel crashes etc it will obviously spin up a netdump
outside of oracles control
ASDXX
the only thing is that the messages doesn't show all the services shutting down, so it's an abrupt reboot but not a crash when it's fencing a node?
i meant 50kb, not 50gb in my original message!
hali
have you got kernel.panic set?
in sysctl
not sure if that is honored when you netdump
ASDXX
what value should it be set to for netdump to honor it?
hali
no sure
it's just the number of seconds linux will wait until it reboots after a kernel panic
0 disables it
ASDXX
ahhh, yeh, so to get a full dump, would need to set that to a high value being cluster it's probably set to a low value to reboot asap
cant check now but will take a look combined with some googling of netdump
KeiKurono
i'm trying to learn sql-translator (sqlfairy), is the correct tool to make a porting of a oracle dump file to a postgres dump file (create db)
monk12
how can you tell where oracle is installed versus just the client. is there a specific file(s) i can do a locate/find for? I dont want to set ORACLE_HOME unless the db is installed (i think i saw some people incorrectly set the client folder to be home. Although LD_LIBRARY_PATH seems correct in those tutorials to be set to $client_home/lib i think)
RJarett
which one has a binary called oracle
KeiKurono
realHans
Hi. Dumb question: we run 10g (widnwos - 32bit) because this is the environment we *have* to. Now my boss just got a new Laptop with windows 7. It it really necessary to install the 64 bit Version of your 10g or would the 32 bit also work - in some kind of compatibility mode ?
KeiKurono
any help with sqlfairy please?
RJarett
realHans: just for the client? what apps is he runnign on his system? why will instantclient not suffice?
KeiKurono: are you sure (12 char) is proper syntax?
are you going to oracle or to postgres?
         

KeiKurono
RJarett: i'm trying to convert oracle to postgres
the dump file should be fine...
the parse failed saying it's not a recognized command but it's a simple create table...
RJarett
ditch the char in varchar2(12 char) and other ones like it
KeiKurono
isn't it correct in oracle syntax? all the tables uses varchar2(n char) in that dump file
RJarett
no
thats so very wrong
wtf would you need char after the number?
KeiKurono
really? i don't know about oracle
RJarett
its wither char(12) or varchar2(12)
those 2 datatypes are different
realHans
RJarett: on Laptop is Server + Client functionality. Only our App is on the laptop (VB6 app with some VC++ components)
KeiKurono
i was given that dump file and boss says, put it into a postgres db
hali
you can specify varchar2(12 char)
you can have the size in either chars or bytes
depending on charsets a char can take more than a byte
RJarett
hali: ive never seen that done in my 15 years
but eh
hali
china stuff :)
RJarett
ahhh
hali
i've never seen it actaully used, just in examples
RJarett
lol i dont even think ive ever seen that in docs
KeiKurono
have you guys ever ported a dumped oracle into a postgres ? i'm having trouble, that sqlfairy doesn't work to me
RJarett
KeiKurono: yeah but i used my own stuff to do it
hali
KeiKurono: i think you are better off askin in #postgresql
RJarett
dbms_metadata to dump all the ddl, then pushed data over
or selected into new objects over a dblink
hali
KeiKurono: did you look in posgre contrib dir for the ora2pg stuff?
KeiKurono
yes i do not have ora2pg or similar..
but i can download it i guess
thanks for the hint
i hope it will works given a oracle dump.. for now i have no access to oracle db
RJarett
how large is the dump?
KeiKurono
66k
RJarett
thats it?
really?
KeiKurono
yes
RJarett
is it an exp or an expdp dump?
KeiKurono
uh? is a plain text dump..
RJarett
oh
KeiKurono
only the db creation, without data inserting
RJarett
thats not a dump :P
KeiKurono
what is ?
hali
schema
KeiKurono
ah i see
sytse
select convert(to_clob('Test'), 'AL32UTF8', 'WE8ISO8859P1') from dual; <-- all right.. why the HELL does that give me a clob with 'Test' in UTF-16, and not UTF-8?
RJarett
how are you determining the output style? java app?
KeiKurono
is there an online oracle create table tester? i have no oracle db access but i want to test a statement
RJarett
ill load up pg and give it a try
i usually only use pg when i need opengis stuff
Kaoru_Nagisa
hello
i'm wondering if it's possible to give create table on a schema, SCOTT for example to another user, TEST for example ?
RJarett
you want scott to create a table for test in tests schema?
Kaoru_Nagisa
no i want test can create a table on scott schema
but it's the same thing lol
RJarett
grant create any table to test;
test@db> create table scott.whatever(id number);
Kaoru_Nagisa
but test can creat a table on another user shcema then ?
RJarett
just be weary of how you have quotas and TS perms setup
KeiKurono
hi
again
RJarett
KeiKurono: http://www.pastebin.org/119933
Kaoru_Nagisa
i want test can create a table only in scott schema RJarett
RJarett
thats how you make your DAV_POLLUTED_AREA table
Kaoru_Nagisa
an not in another user schema
RJarett
Kaoru_Nagisa: do like i just showed you
thats makes a table AS TEST user, in SCOTTs schema
Kaoru_Nagisa
ok
KeiKurono
RJarett: i see.. so it's correct afterall
RJarett
no
what i did to it made it correct
KeiKurono
ah you changed it...
what errors return the original one?
RJarett
invalid datatype errors
LINE 1: ...ble DAV_POLLUTED_AREA (Polluted_Area_Code varchar2(20) not n
PG should accept varchar2 and number but i guess in a fresh install they dont
KeiKurono
thank you for the feedback RJarett
RJarett
i figure, anyone going out of their way to pollute the environment, ill be willing to help
screw the damn tree hugging hippies
KeiKurono
so the schema dumped seems to be incorrect.. wonderful...
reboot
RJarett
well the dump is fine, what ever conversion app you used didnt realize fresh PG doesnt have compatibility enabled for oracle
Kaoru_Nagisa
RJarett, there is a "problem" with any table, because test can also create a table on "test2" schema, or i want test can create only on scott schema...
RJarett
well the only other way without giving a user create any table, is to give the user proxy ability as scott
and then he proxys scott and creates the table as scott while logged in as test
if you dont like it, call oracle and complain to them
sytse
so, nobody has any idea about the convert (clob version) bug with utf-8 I mentioned?
RJarett
its not a bug
how are you determining its utf16?
Kaoru_Nagisa
ok RJarett :)
RJarett
Kaoru_Nagisa: or you can make a procedure in scott's schema that creates tables with values you pass to it. and then grant execute on the proc to test
Kaoru_Nagisa
i'm french so i know how to complain about something :D
RJarett
but thats a potential security nightmare
Kaoru_Nagisa
yes :s
RJarett
http://jonathanlewis.wordpress.com/2006/12/18/proxy-users/
proxy user is a good way to do this
Kaoru_Nagisa
i will see, thx !
sytse
RJarett: sorry, I was wrong... the result is not utf16, it's the unconverted input but with every byte prefixed by a null byte
KeiKurono
RJarett: sorry to bother can you please repaste me your version of the statement ?
RJarett
http://www.pastebin.org/119933
KeiKurono
thanks
RJarett
sytse: select lengthb(convert(to_clob('Test'), 'UTF8', 'WE8ISO8859P1')) from dual;
whats that return?
8?
And this: select lengthb(convert(to_clob('Test'), 'AL32UTF8', 'WE8ISO8859P1')) from dual;
And this should return 4 in most DBs: select lengthb(to_clob('TEST')) from dual;
seems like its working find for me
KeiKurono
uhm my sqlfairy fail no matter if i change that line
RJarett
KeiKurono: parse a script, edit it manually and run it
sytse
RJarett: yes, returns 8
KeiKurono
changing double precision to float(126) parse ok in sqlfairy.. still errors in next lines lol
sytse
RJarett: is there an error in db configuration?
RJarett
why would you think that?
sytse
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
RJarett: dunno, maybe it's a bug in this specific version of oracle?
RJarett
its not a bug
wtf is the result you expect?
sytse
um, what would you expect? I would expect 4 bytes, with 'Test' in utf8 (which is the same as in ascii), in a clob
and you said it worked fine for you, it doesn't for me (length 8 bytes)
so that makes me think it's an oracle bug
RJarett
seriously stop fu*king thinking anything is a bug
sytse
all right, so what is this if not a bug?
RJarett
#1 you are using a db version that was desupported like 4 years ago. and #2 even that doesnt mean anything is a bug
its an understanding of the functions you are using issue
sytse
btw, I wasn't -saying- it was a bug, I was -asking- if it could be a bug, and later -supposing-
RJarett
its in no way a bug
sytse
all right, so what do you think it is? And if this is how it was 'supposed' to work, then how do I convert a clob to utf8?
RJarett
play with the dump() function and check the output in multiple charsets
sytse
thx
btw, I have an oracle client that can show clobs in hex, doesn't that suffice?
RJarett
dump is better
sytse
select dump(to_char(convert(to_clob('Test'), 'AL32UTF8', 'WE8ISO8859P1'))) from dual gives 'Typ=1 Len=8: 0,84,0,101,0,115,0,116' (dump on the clob itself doesn't work, gives an ORA-00932)
RJarett: and that's not what it should give, it should give 'Typ=1 Len=4: 84,101,115,116', or do you disagree?
RJarett
disagree.
sytse
why?
RJarett
why should it?
sytse
isn't convert supposed to convert from the charset in the third parameter to the charset in the second parameter?
KeiKurono
float = float(126) right?
sytse
0,84,0,101,0,115,0,116 is not AL32UTF8
RJarett
you dont think 0084 is valid?
sytse
I know 0084 isn't valid (I know a thing or two about UTF-8)
RJarett
actually it is valid
sytse
and btw, it does no conversion at all, even 255 gets converted to 0,255
1 2 next »