yello ;)

y0

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

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

kanch: the having clause, maybe?
select sum(blah), blah from blah group by blah having sum(blah) != 0 ?

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

Well, what you just said should work, shouldn't it?

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

There are days like this when you had better stay to bed :p
Such is one for me as well

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

toad? Duh
Use sqldeveloper
It's much better

I'll have a look when i get time....any reasons why it's better?

Standalone, syntax completion, much better UI

this is really annoying me.

So, how do you relocate the listener/net log files?

greetings gentlemen,

Grrr
(Action) HATES 11g logging
Why does Oracle think it's a good idea _at all_ to log under $ORACLE_BASE by default?

because there must be *some* default

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

if you don't like it, change it. it's just *a default*

TheBonsai: fine, but dbca doesn't even give the option to do so in the first place

dbca doesn't give the option?
did they change that in 11?

No it doesn't

in 10 you were able to edit init parameters

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

oh, you have to know what you do?

What's more, background_dump_dest and user_dump_dest are obsolete and forced upon you

now that's bad, yes ;)
yea, they changed it to one parameter iirc

Therefore rendering the log tree completely unfathomable

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 ?

And that's the same for the listener - netca doesn't give you the option at all to relocate the log files

yea i know that
the old story

It was bad enough having multiple subdirectories in 9i and 10g, but at least there was only one level deep

"Welcome to ORACLE's world"

In 11g, "duh"

file a metalink bugreport

That's my intention, but I'm still filling the "complaints sheet"
When I'm done, you can be sure I'll do :p

heh

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?

i never seen that before, i have rowid exeptions pointer in exceptions table , that i can not found in the table itself ...

Hmm
Is it possible to set audit_trail to "os, db"?

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

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?

Does anyone know how to recreate a database from a template generated by dbca?
Well, hello first

did you SAVE the template?

Yes I did
I'm wondering about the next step now :p

then select existing template in dbca when given the option. it should be in the middle of the list

Well no

all it does is uses the same settings to put into the pfile. nothing more really

I didn't save it in the default directory
_and_ I want to do it non interactively

you either save it or you dont. it doesnt ask where
then you need a response file

Yes it does
Ah, there

no thats the script
and dont ever question me

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)?

why would you save a template somewhere else?

Because I mount $ORACLE_HOME read-only on purpose

whats your extension of said template?
hmmm doesnt seem like a compliant OFA installation

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

whats your extension of said template?

I have two files, xxx.ctl and xxx.dbf

dbf?
not dfb?
or dbc?

Uh
dfb, sorry

learn to be specific

Well, that was a user error :/

typically you record a response file from a first install
you can use -cloneTemplate command

You, or dbca? I didn't see an option anywhere in dbca to save in a response file

yeah dbca -record whatever.rsp

Argh
(Action) looks at dbca -help again

there are default response files in the installer

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

put the template in $ORACLE_HOME/dbs/assistants/dbca/templates
and use the name

Yeah, that's what I feared :(

so where do you save your pfile/spfile and all the EM crap if not in O_H since its ro?

dbs is a symlink to /etc/oracle (post engine install script of mine)
And I don't use EM

wow thats terrible setup
really? /etc/oracle?
is this 10g or 11?

Terrible? Not to me
I call this "FHS compliant"

Idiots dont kow they are stupid

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

if you say so

OFA wants everything under the same directory, that's just absurd

no it doesnt actually
try OFA is at least 4 mount points
so your assessment of it is incorrect

None of which can be read only

the sh*ts all in use. why would it need to be readonly?

Well, why would the _engine_ in itself be touched in any way? Why install data files under $OH?
There's /usr/share for that

you wouldnt install data files in O_H
and you NEVER touch /usr/share with oracle apps

idletask: fyi in the 10g DBA Handbook they said OFA could be implemented on 3 mount points minimum but 4 is recommended

youre fu*ked in the head with your concepts ;)
sorry

Counter example: dbca's templates
If you want to create one, you don't seem to have a choice in the matter

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

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

stfu about FHS
you dont even really know what that is

I do

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

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

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

welcome to our world

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 ?

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?

yes with bcp (bulk copy) i choose the encodings unicode

bcp?

yes this is a command line tool for MSSQL

what db are we talking about?

RJarett1: yeah, I see that

RJarett1: i'm migrating MSSQL to oracle

ok

so i export MSSQL tables to csv files
and import them with SQL*Loader

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?

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

so how does it know where to put it?
any sort of charset issue could cause lobs being loaded in the wrong spot otherwise

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

and you are 100% sure none of your lobfield are multi-line?
or have" in them?

in fact i set a special '|\n' delimiter in them because they are all multiline

sidh: any chance you can use base64? That would guarantee at least byte to byte correctness

you have utl_file_dir set?

RJarett1: never heard about that ?

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