logs archiveIRC Archive / Freenode / #oracle / 2010 / March / 24 / 1
usn_work
doing
snipe_
hi all
Trengo
hi snipe_
bye snipe_
snipe_
hi trengo
xiaoshen
hi :)
which data types in oracle that match with mysql longtext data types
Junior
yello ;)
TheBonsai
m00
xiaoshen: CLOB i guess
xiaoshen: but beware, it's a LOB segment
xiaoshen
ic thQ theBonSai:)
remember me i used to use ^law^ nick
the nick i'm using now is the registered 1
TheBonsai
yea i remember
         

xiaoshen
now i'm trying to migrate mysql db into oracle db
have u ever used ESF Database Migration Toolkit Pro 6.3.02?
TheBonsai
no
a LOB column is organized as an own LOB segment in the database, i.e. it's not "just a column", it's another object
(you see that when you do table maintenance and stuff, you always have to treat LOB segements a bit special)
xiaoshen
hmm ic
TheBonsai
it's understandable, since (at least since oracle 10) the LOB data size is just limited by available physical storage
not just 4GB or so
internally it's organized as own segment, it's not part of the table. you see that e.g. on a ALTER TABLE MOVE, you have to move the LOB in a second step (means: LOBs can be stored on a tablespace independent from the tables they're used in, for example)
check http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/oralob.htm#CHDHIBFF
(that's from a developer point of view)
xiaoshen
it seems so complicated
TheBonsai
it isn't that complicated
do you know what a segment is?
the oracle "storage basics", databases, tablespaces, segments, extents, blocks
in other words: the LOB column is nor part of the table, it's an own object that has its own management and can be anywhere in the database (and of *any* size)
xiaoshen
oh
TheBonsai
a normal column is directly and physically stored inside the table data
a LOB isn't, it's an extra segment (object)
xiaoshen
i think i must read the lref link that u just given 1st
TheBonsai
that link is not the DBA view of LOB, it's just a developer summary (how big, pros, cons, ...)
for a DBA the primary points are, that 1) it can be big, 2) it is not directly inside the table
for 2) when you for example ALTER TABLE MOVE a table to another tablespace, the LOB still is where it was before. it is an own object
it doesn't mean that you can't SELECT MY_LOB_COLUMN FROM TABLE; of course you can do that.
but as DBA you have to remember that a table with a LOB column are physically 2 objects (the table and the LOB segement)
xiaoshen
ic
TheBonsai
from the query point of view, this is irrelevant, just a performance thing maybe when it reaches several terabytes ;)
do you port an application that does substring search in the mysql LONGTEXT columns?
(i.e. searches text there)
then this might be interesting: http://www.developerdotstar.com/community/node/68
xiaoshen
i'm not sure
must ask the programmer
TheBonsai
you can't do WHERE MYCOL LIKE '%xiaoshen%' on character LOBs
afair
xiaoshen
oh
TheBonsai
you need the DBMS_LOB.* procedures and functions
but that's just a matter of changing the source code, shouldn't be a really big deal
xiaoshen
ic
maybe i must discuss it 1st wit theprogrammer :)
TheBonsai
that will also be a part of your migration, the SQL is different
not a really big problem, but it needs work
xiaoshen
yea
hard work xD
TheBonsai
for a typical MySQL application, you usually just have some CONCAT(...,...,...) operators, LIMIT operators, maybe complicated join syntaxes
that are the "usual suspects"
it's more a problem to migrate from oracle -> mysql
         

xiaoshen
ic
have u ever experienced migrating from mysql to oracle
TheBonsai
no, i ported some (private) app from mysql to postgres once. it was hard enough (because i don't know SQL)
xiaoshen
ic
ok then thank you theBonsai , now i must to go to client place :)
TheBonsai
have fun
KeiKurono
morning
Trengo
morning
usn_work
morning
rhqq
hello. i have little problem with plsql
is it possible to write sth like that: IF( tab.surname LIKE '%a') THEN dmbs.....
is it correct?
hali
no
rhqq
ah, tab is type rowtype
so tab.surname is vc2 cell
not column
knandan
Hi
Michael_Knight
Hello. Does somebody use Oracle Retail Data Model? I have some questions about data agregation in its.
MaL0
hi
please any oas administrator ?
we have oas 10.1.3 on cluster (5 nodes), we have session replication, but need to disable it
does somebody know how to do it ? please
RJarett
hi
MaL0: so you dont need state sharing or anything?
MaL0: try commenting out or removing the cluster entry in ORACLE_HOME\\j2ee\home\application-deployments\state\orion-application and it may do it for you
you would need to redeploy the state app from oem to make it take effect
vmnew
looking for a simple loop statement or something to stress my server a bit from oracle's side. tried to select * from all_table but it barely touched it
RJarett
what part of the db do you want to stress?
vmnew
cpu and i/o
RJarett
well a single threaded query like that wont do much
rethink your test idea
vmnew
k, will make a procedure of some sort. thanks
RJarett
theres a big database i forgetw here its from. its like 9gb of raw data of lat/lon of locations of everything in the world
find that
load it in, start doing some queries on it
vmnew
got a strange ORA-25153: Temporary Tablespace is Empty when trying to run a simple loop procedure I wrote. All temp tablespaces for all users are Open and named TEMP.
brb, adding a file
RJarett
did you manually create the db or use dbca?
vmnew
manually
RJarett
yeah dont do that
this is the first of many problems you will face
usn_work
:)
dsdel
lol we always create databases manually
:D the trick is to know what todo
usn_work
but maybe you don't forget nothing after doing it several times
yes
dsdel
but you don't learn without running into the errors :)
vmnew: check the database setting for: default temporary tablespace <tsname>
with at last one TEMPFILE associated to the tablespace
if you add datafiles there is nothing inside :D
Suva
Hey, can I ask SQL Developer question?
RJarett
yeah but we probably wont answer it
tool based questions tend to be rather specific
Suva
I have .pkb files... Why won't autocomplete work? :)
dsdel
Suva: ask google :D
that sounds like the right question for googel answer in 15seconds
RJarett
even table names wont?
Suva
I tried
RJarett: nope, nothing
Works fine if I open some object from database, but if I open file the autocomplete doesn't work
RJarett
well is it associated w/ an active session?
Suva
It should be, if I compile it the name in header changes to reflect the database too
usn_work
check the pulldown menu on the top right
RJarett
2.1?
Suva
2.1.0.63
Which pulldown menu? The one which shows current database?
It shows current database :)
dsdel: I tried googling it, but I guess I didn't figure out the right search for it
usn_work
hum
I don't believe that the effect is connected to the file type
Suva
Well the problem is that it doesn't work when I open file
If open the same package from database it works
If I open the file, press compile
usn_work
does completion work in anonymous blocks in general?
Suva
It doesn'
Yeah
usn_work
sh...
does it work in a fresh tab?
Suva
Yes
usn_work
just to check, can you cahnge the file extension to .sql and try again?
just to make sure
Suva
hah, silly, it works if the extension is .sql :)
usn_work
not really, is it?
Suva
It also opens it in sql editor not package editor too
usn_work
how can we test the package editor stand alone?
dsdel
naturaly it is :D
Suva
Just make a .pkb or .pks file and open it
dsdel
you can open any file with sqldeveloper and on the extension normally IDE's detect the parser to use
usn_work
hehe, no without opening a file
Suva
Open a package from a database?
dsdel
if you open a huge text csv file for anything to use with oracle and the sql parser tries to parse.. oh what a lack of cpu cycles
usn_work
is there something like a NEW option, so we don't need a file or existing package?
Suva
Yeah new... package
usn_work
does it work there?
Suva
File -> New -> Package
Yes
usn_work
bah
crap
dsdel
bah just firing up sqldev and it is slow like eclipse :D
usn_work
Maybe you want to try to open a thread in technet sqldev forum, maybe Sue has a hint for you
some people from the sql developer team are present there
Suva
Heh, I guess I have to do that
It's still totally silly, specially the fact that nobody apparently has had that problem before
usn_work
NACK
People using SQL developer simply don't think about that tool any more. :) I'm using 1.5 versions, and don't waste my time with these questions ;)
Suva
Is there anything else I could use?
I am tired of browsing around the files to find the elements
usn_work
I don't know, I believe not. Just learn to live with the maladies and try to publish bug reports
or improvement requests
they seem to be open for them
Suva
Ehh, okay
usn_work
sorry, but there are no better news AFAIK
avaia
is 2.1 anywhere close to stable now?
usn_work
somewhat
but not perfect - 1.5 took up to 1.5.6 to become really usable
so be patient ;)
Suva
I think 1.5 only autocompletes table names anyway
Yeah, it's apparently a bug
usn_work
they did a wide rewrite of the software, obviously they forgot something. :)
Suva
Yeah, I guess
Hah: http://forums.oracle.com/forums/thread.jspa?threadID=1048419&tstart=0
3.0
Amazing
TheBonsai
2038...
Suva
:D
Anyway, thanks to everyone
Cya
KeiKurono
a good sqlplus manual?
TheBonsai
oracle.com
KeiKurono
nmn found one good enough, thanks
freezer
Hi. Is it possible to delete a single partition from a partitioned table during an update on another part. of this table? I get the ORA-00054 error: resource busy and acquire with NOWAIT specified
telles
hey, any body can help me with an strange ora-60?
freezer
One would thing, the UPDATE <table> PARTITION (part1) would only put a lock on this very partition, but not on the whole table
telles
In trace see just one session in blocker and waiter sides, how can it be?
MaL0
thx RJarett
sorry I had lots of problems today and couldn't see your response.
hali
freezer: depends on the indexing of the table too, do you have a global index?
or local bitmap indexes
freezer
hali, there is no index, only statistics
« prev 1 2 next »