logs archiveIRC Archive / Freenode / #oracle / 2010 / January / 25 / 1
Junior
yello ;)
TheBonsai
y0
idletask
Hell
Hello, I meant
How do you relocate the listener's log/diag files with 11g?
I set TNS_ADMIN to a different location than the default but the listener won't obey the LISTENER_LOG_FILE directive
kanch
hey guys, I want to perform sum calculation in the select clause and then only output numbers greater than 0. How do I do that? eg. 'select (a*0.02 - b) as somevar from foo where somevar > 0'
sum = some
idletask
kanch: the having clause, maybe?
select sum(blah), blah from blah group by blah having sum(blah) != 0 ?
kanch
idletask, sorry i wrote the question wrongly. I want to perfome this calculation 'select (a*0.02 - b) as avar from foo where avar > 0'
no 'sum' involved
idletask
Well, what you just said should work, shouldn't it?
kanch
nope 'somevar invalid identifier'
sorry about the spelling by the way....it's early
sorry 'invalid sql statement
'
damn it i'm making so many mistakes today
idletask
There are days like this when you had better stay to bed :p
Such is one for me as well
         

kanch
if only i had that choice :)
damn it i was right the first time
the error is 'invalid identifier'
hmm maybe toad is being stupid
nope
idletask
toad? Duh
Use sqldeveloper
It's much better
kanch
I'll have a look when i get time....any reasons why it's better?
idletask
Standalone, syntax completion, much better UI
kanch
this is really annoying me.
idletask
So, how do you relocate the listener/net log files?
sidh
greetings gentlemen,
idletask
Grrr
(Action) HATES 11g logging
Why does Oracle think it's a good idea _at all_ to log under $ORACLE_BASE by default?
TheBonsai
because there must be *some* default
sidh
i would need your advice, please, i had to import huge csv file with sql*loader into blank table , so i disable constraints , and index, put the db in noarchivelog mode , and inserted the rows, everything ran fine (log file said nothing has been non reand and so on) , so now i'm reactivating the constraint and i get a ORA-02437 primary key violated. so i set up an EXCEPTIONS table , retry the contraint query with the "exceptions into exceptions". Whe i select
TheBonsai
if you don't like it, change it. it's just *a default*
idletask
TheBonsai: fine, but dbca doesn't even give the option to do so in the first place
TheBonsai
dbca doesn't give the option?
did they change that in 11?
idletask
No it doesn't
TheBonsai
in 10 you were able to edit init parameters
idletask
You still can
But there's no step in the wizard saying "where do you want your logs to be?"
If you don't know them, you're doomed
TheBonsai
oh, you have to know what you do?
idletask
What's more, background_dump_dest and user_dump_dest are obsolete and forced upon you
TheBonsai
now that's bad, yes ;)
yea, they changed it to one parameter iirc
idletask
Therefore rendering the log tree completely unfathomable
         

sidh
does someone know how is this possible to list some rowid in exceptions table , that are not in the table that have generete the exception table ?
idletask
And that's the same for the listener - netca doesn't give you the option at all to relocate the log files
TheBonsai
yea i know that
the old story
idletask
It was bad enough having multiple subdirectories in 9i and 10g, but at least there was only one level deep
TheBonsai
"Welcome to ORACLE's world"
idletask
In 11g, "duh"
TheBonsai
file a metalink bugreport
idletask
That's my intention, but I'm still filling the "complaints sheet"
When I'm done, you can be sure I'll do :p
TheBonsai
heh
idletask
brb
Hello again
Is there a way to specify the "diagnostic area" base directory via an environment variable instead of specifying diagnostic_dest for each created instance?
sidh
i never seen that before, i have rowid exeptions pointer in exceptions table , that i can not found in the table itself ...
idletask
Hmm
Is it possible to set audit_trail to "os, db"?
TomTom
hi there, any idea how to get the definition(DDL) of a function based index?
i find a lot of docs how to CREATE such indices, but no information how to check them. also sqldeveloper does not provide some useful inforation
CBro2007
I have a string like say '3/6/7' and I want a SQL that will convert that to a table with rows 3, 6 and 7 instead
was wondering if you knew the SQL
do I use the TABLE keyword?
idletask
Does anyone know how to recreate a database from a template generated by dbca?
Well, hello first
RJarett1
did you SAVE the template?
idletask
Yes I did
I'm wondering about the next step now :p
RJarett1
then select existing template in dbca when given the option. it should be in the middle of the list
idletask
Well no
RJarett1
all it does is uses the same settings to put into the pfile. nothing more really
idletask
I didn't save it in the default directory
_and_ I want to do it non interactively
RJarett1
you either save it or you dont. it doesnt ask where
then you need a response file
idletask
Yes it does
Ah, there
RJarett1
no thats the script
and dont ever question me
idletask
No, not the scripts
I saved it as a template, which I want to replay, and which is not in the standard dbca directory
It is said to be much faster to recreate a db this way than doing it with the shell/sql scripts, so I seek knowledge on how to do it
And dbca -help, well, doesn't tell much about that :/
So, let's tackle the problem another way
Will a dbca response file allow me to specify a template located in another location than the default (which, frankly, sucks)?
RJarett1
why would you save a template somewhere else?
idletask
Because I mount $ORACLE_HOME read-only on purpose
RJarett1
whats your extension of said template?
hmmm doesnt seem like a compliant OFA installation
idletask
There's no need to write files in there unless I update the db engine itself, so it's mounted read only by default
No it isn't - I use the FHS, not OFA
RJarett1
whats your extension of said template?
idletask
I have two files, xxx.ctl and xxx.dbf
RJarett1
dbf?
not dfb?
or dbc?
idletask
Uh
dfb, sorry
RJarett1
learn to be specific
idletask
Well, that was a user error :/
RJarett1
typically you record a response file from a first install
you can use -cloneTemplate command
idletask
You, or dbca? I didn't see an option anywhere in dbca to save in a response file
RJarett1
yeah dbca -record whatever.rsp
idletask
Argh
(Action) looks at dbca -help again
RJarett1
there are default response files in the installer
idletask
OK, I need to look more carefully in the install, then
dbca -help doesn't mention the -record option at all :(
OK, I can try and try again so I'll do dbca -record
Thanks for the tip!
Hmm, anyway, I don't see anywhere that even with a response file, a template can be used that is out of $ORACLE_HOME :(
Unless the -templateName option is a misnomer and you have to specify the absolute path to the template file
RJarett1
put the template in $ORACLE_HOME/dbs/assistants/dbca/templates
and use the name
idletask
Yeah, that's what I feared :(
RJarett1
so where do you save your pfile/spfile and all the EM crap if not in O_H since its ro?
idletask
dbs is a symlink to /etc/oracle (post engine install script of mine)
And I don't use EM
RJarett1
wow thats terrible setup
really? /etc/oracle?
is this 10g or 11?
idletask
Terrible? Not to me
I call this "FHS compliant"
RJarett1
Idiots dont kow they are stupid
idletask
It's 11gR2
RJarett1: eh, fine, I mount /usr and /opt read only on all my production systems with no problem and only Oracle defies this rule
I am guaranteed that nothing is touched, only the stuff that needs to be touched - and that's in /var or /etc
RJarett1
if you say so
idletask
OFA wants everything under the same directory, that's just absurd
RJarett1
no it doesnt actually
try OFA is at least 4 mount points
so your assessment of it is incorrect
idletask
None of which can be read only
RJarett1
the sh*ts all in use. why would it need to be readonly?
idletask
Well, why would the _engine_ in itself be touched in any way? Why install data files under $OH?
There's /usr/share for that
RJarett1
you wouldnt install data files in O_H
and you NEVER touch /usr/share with oracle apps
sidh
idletask: fyi in the 10g DBA Handbook they said OFA could be implemented on 3 mount points minimum but 4 is recommended
RJarett1
youre fu*ked in the head with your concepts ;)
sorry
idletask
Counter example: dbca's templates
If you want to create one, you don't seem to have a choice in the matter
RJarett1
template isnt a 'data file'
dont mix oracle teminology with something else
and i hope to hell you really diidnt install anything oracle in /usr/share
idletask
No, because Oracle won't allow me to
Otherwise dbca templates would be in there, that's for sure
In an FHS setup, that's their intended location
RJarett1
stfu about FHS
you dont even really know what that is
idletask
I do
RJarett1
youre jizz for some fantasy of file architecture makes you a crappy DBA
sorry
if anything broke on yoru box and you called oracle, you would make them cry looking at your box
asking... why god... why
couldnt we have had a mysql dba call in instead? it wouldnt be this bad
sidh
i would need your advice/opinion, I nearly finish to export as CSV file a MSSQL DB , and sql*loded them into Oracle (i begin to understand all cool features of sqlloader) but even with reading the documentation , i dont very understand how can i load the only table with one CLOB column it remains
idletask
RJarett1: let's take it this way... I know about FHS, I've been administering Linux machines for years - all software I've dealt with, from named to postgresql, to custom apps, obeyed them and finding each piece was easy
Not so with Oracle
RJarett1
welcome to our world
sidh
i read i could export all non clob colum in a first csv file, and the clob field in a second csv file, but i didn't success to laod it
how would you do to do so ?
RJarett1
if you want it as a csv you ened to unload it in a way to encode it to ascii
is it unicode or what charset?
sidh
yes with bcp (bulk copy) i choose the encodings unicode
RJarett1
bcp?
sidh
yes this is a command line tool for MSSQL
RJarett1
what db are we talking about?
idletask
RJarett1: yeah, I see that
sidh
RJarett1: i'm migrating MSSQL to oracle
RJarett1
ok
sidh
so i export MSSQL tables to csv files
and import them with SQL*Loader
RJarett1
so whats the format of the clob field? did you also export out the pri key with the clob so it knows where to go?
sidh
no in the doc i read they said to only export clob column, and IT must be in the order of the "others colums' csv file
RJarett1
so how does it know where to put it?
any sort of charset issue could cause lobs being loaded in the wrong spot otherwise
sidh
in fact when you load the non clob columns ,you specify "My_CLOB_Field LOBFILE(OCNSTANT 'my_clob.csv') TERMINATED BY '|\n' ENCLOSED BY '"',
for exemple
RJarett1
and you are 100% sure none of your lobfield are multi-line?
or have" in them?
sidh
in fact i set a special '|\n' delimiter in them because they are all multiline
idletask
sidh: any chance you can use base64? That would guarantee at least byte to byte correctness
RJarett1
you have utl_file_dir set?
sidh
RJarett1: never heard about that ?
RJarett1
it lets you call out to directories on the OS
i was going to have you setup this as an external table instead of sqlldr so that you can do a query on the table to see whats happening
« prev 1 2 3 next »