logs archiveIRC Archive / Freenode / #oracle / 2010 / February / 23 / 1
idle_task
Good evening
krb_hnd
hi all
how can I remove duplicate rows in my returned query
idle_task
First step: rewrite your query so that there are no duplicate rows
Second and easier step: use distinct
But be aware that distinct implies a sort, and therefore is costly
krb_hnd
can I use distinct, if my query returned more than 1 result
how about "having" will it be costly
idle_task
Yes, because having works on aggregates
And whatever aggregate operation you use implies a sort
And therefore is costly :p
krb_hnd
:(
idle_task
What about you pasted your query on a pastebin?
krb_hnd
sure can
how about row_number
idle_task
That will be of no help
         

krb_hnd
I just want to return 1st row
idle_task
Err
The query, please :p
krb_hnd
I'm using OBRM table
anyone here used OBRM
idle_task
Not me
krb_hnd
ok
where is everybody?
idle_task
Sleeping, probably
krb_hnd
oo ok
idle_task
According to EU time, it's past midnight already
krb_hnd
idle_task : :)
idle_task: anyway thanks for the help
idle_task
Well, you still haven't pasted your query
krb_hnd
more than 100 lines
idle_task
Eh?
krb_hnd
I think I've already the ans
I'll use row_number
idle_task
You want only the first row of a 100+ lines SQL query?
(Action) recalls a saying of St├ęphane Faroult there
krb_hnd
1st row for any duplicate rows
sileni
hello everyone
does anyone know what it means when they have "x -> y, x -> z"
where xyz are three attributes
i'm somehow supposed to identify the primary key and the highest normal form
krb_hnd
bye idle_task
have to go now
sileni
bye
krb_hnd
:)
idle_task
sileni: x, y, z all being columns of a same table?
         

sileni
idle_task, see i don't know
it just says consider the relation XYZ, with three attributes, X, Y, and Z
also the notation -> is supposed to mean functional dependency
idle_task
a->b meaning what? a depends on b or b depends on a?
sileni
i think a depends on b
like warehouse -> warehouse-Address
i'm supposed to identify the primary key and the highest normal form for each of the cases, but i don't understand where to begin
idle_task
Well, as far as I can see it, it's 2NF here
It's 1NF because each attribute is atomic, and it's 2NF because you cannot derive an attribute from any other attribute, or combination of these attributes
sileni
idle_task, hmm
idle_task, what is the primary key
x?
cause it is what everything depends on ?
hello everyone
idletask
Hello world
Is there any particular system privilege that needs to be granted for creating Java procedures, or is create procedure enough?
Grrr
I thought my datapump import wouldn't generate archive logs, and it did :(
OK, what are the system views to use for querying what columns exist for a given table?
hali
user_tab_cols
idletask
hali: yep, found it, thanks
I have BFILE columns
Great :/
Rudemeister
any sso gurus here?
hmm
any sso gurus here?
idletask
Not in the Oracle meaning of the term, no
SSO, Single Source of Outages
Heh
Rudemeister
hmm
idletask
(Action) HATES iFS
bfiles, raw, long raw
Everything to make data pump _avoid_ direct path loading :/
hali
ifs' old HQ is in my home town in sweden :)
idletask
hali: err, do you know anyone by the name of Bert Vanderstraten by any chance? :p
Vanderstraeten, even - or not
hali
no
usn_work
Where are you from in sweden, hali?
idletask
This guy's an iFS guru and has helped me countless time with iFS
hali
usn_work: Linkoping
usn_work
nice southern, warm, developed area ;)
idletask
Meh
I have to work in overblown Paris
I'd like a lean, small town instead
usn_work
I am working in a 1000-people-city
one quarter of them works here in our company ;)
idletask
Sounds nice :p
hali
i live in london, about 125.000 people use my train stop every day :)
usn_work
it was a downgrade. I live in a 7000-ppl-town
idletask
I live in a... 30-people village, heh
And I am _glad_ about the fact
usn_work
I think below-100.000-ppl-towns are fully ok
anything above might be nasty
idletask
Back to Oracle... Is there a way to know the last time a particular table has been modified by DML/DDL?
RJarett
select OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME from dba_objects where owner not in ('SYS','SYSTEM');
for DML you need a trigger or an application aware column to sysdate into
or enable auditing on it
enjoytheday
how hard is to configure RAC on linux box?
usn_work
Do you read carefully?
RJarett
enjoytheday: avoid rac like the plague
its rarely the best solution
usn_work
This is FUD
RJarett
and even more rarely worth the price
usn_work
- RAC works well for lots of small operations
- RAC is included in Standard Edition
enjoytheday
Yes, I'm trying to setup through iscsi
idletask
RJarett: fair enough - and DML? :p
tijnie
idletask, you should use auditing or triggers
usn_work
If you read the whitepapers / best practices for half a day, it takes you the other half to set it up
given you are a skilled linux guy
enjoytheday
I'm just reading the doc, does it work like veritas cluster or bit different?
usn_work
Preferably you use the Oracle Clusterware
Just read the best practices
RJarett
well its 2 instances with 1 set of disks, with virtual IPs to connect to.
tijnie
idletask, be aware that auditing can have some performance issues.
RJarett, tha makes it sound easy :-)
idletask
tijnie: this has nothing to do with auditing, I'm looking to have a data pump path avoid non-direct path import, and there are suspect tables which I think are relics from prehistory... So I'd like to know where these were last updated
dml-wise
RJarett
im not a big RAC fan 98% of the time
tijnie
idletask, Ah ok...well I think you can't then. BUt if you find a solution please report back (always eager to learn)
hali
idletask: you could have stab at checking the max(rowscn)
idletask
hali: is that pseudo row available on all tables?
hali
idletask: pseudo-column yes
idletask: you can convert it to current timestamp (sometimes at least, depending on how far it goes back)
idletask
hali: thanks for the tip!
I'll investigate further
Hmm
What is "supplemental logging"??
RJarett
for oracle streams
idletask
Oh
RJarett
lets the DB know what its done to replicate which data
idletask
I don't use streams
RJarett
dataguard too
idletask
I don't use that either
RJarett
hmmm
idletask
It's in the data pump description, it says that direct path load cannot be used if "supplemental logging is enabled and the table has at least one LOB column"
RJarett
well then you should be fine
hali
required for stuff like goldengate
idletask
OK, off
Have fun
hali
anyone know if/what datapumps default parallel'ism value is?
usn_work
1
hali
sure? im seeing some odd (not a problem but it looks strange)
i thought it did tables in size order? but i can see it completing a bunch of small tables then a big one pops up
it's a 500gb blob table though, could be why
RJarett
is your blob inline or not?
hali
inline most likely
i doubt the developer writing this knows what that means :)
hm, docs do say 1 as well
double checked even in 11gr2 docs
Dormir
afternoon
usn_work
g2g, good evening
alexx1523
Hi everyone: I'm reading a pl/sql tutorial at java2s that contains the following example - http://pastebin.com/375neizq - Can someone explain why the first select statement is necessary in the INTERSECT clause? It seems to me that the second statement on it's own would return the same records
For that matter, isn't the UNION also rather contrived?
steelnwool
um.. what are oracles recommended standards for passwords?
i'm using a 8char, alpha numeric password and its bi**hing at me.
idletask
Good evening
Any data pump professional? I'm lost :(
One thing is, I don't see in the logs of an import job (or export for that matter) whether the direct path method is used
pedronero
(Action) boa noite
steelnwool
where does oracle put its startup scripts on RHEL? i'm searching, can't find em.
PuroOsso
hi guys
Dormir
idletask: it may not say. expdp chooses the best method automatically. http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm#sthref16
PuroOsso
I'm having a SP2-0640 error, can anybody help me?
Dormir
steelnwool: you're looking for dbstart and dbshut?
steelnwool
Dormir: i... think :) i have about 12 hours of oracle experience :)
idletask
steelnwool: it won't create sysv init scripts
steelnwool: you have to create them yourself if you want them
steelnwool
okay.
hrmm, i could have sworn i rebooted and it all came back tho. lemme try rebooting again, see what happens.
Dormir
PuroOsso: what were you doing?
PuroOsso
Dormir, a backup
Dormir
steelnwool: RAC or single-node?
steelnwool
single node.
11g EE on rhel.
Dormir
they don't install startup scripts, unless they changed the install with 11
PuroOsso: using RMAN?
chuckh1958
Every time I use "flasback table xxx to scn xxx" I get "ORA-00942: table or view does not exist". Why? I'm using version 10.2.0.4.
Dormir
chuckh1958: obvious first...does the table exist?
chuckh1958
Not any longer. I have several versions of it in the recycelbin. I'm trying to specify which one to recover by the scn.
Is the only way to do that by the recylebin table name? Is "to scn" for something else?
steelnwool
hehe, i was mistaken.doesn't automatically start.
hopefully thats in the 2 day dba :)
Dormir
chuckh1958: select from the table
looks like if you drop the table, then flashback, you get the error, but it flashes back the table
chuckh1958
Dormir: I can't it was dropped. I'm trying to use "to scn" to restore a particular version of the table.
Dormir: Here's what I did. (1) create table, (2) insert rows, (3) drop table. I did this several times. Now I'm trying to recover a specific version of the table, not just "before drop" and the last one isn't the one I want.
PuroOsso
Dormir, thanks for your time! It works now...
idletask
Grr, I just think I'll have to go through the temporary instance again and use tts :(
direct_path just won't work
chuckh1958
Dormir: Not for me.
Dormir
chuckh1958: from the note I found, you get the ORA-942, but the table is there
chuckh1958
Dormir: It's not there.
Dormir are you using 10gR2?
« prev 1 2 next »