Re: Do you ever have days where you dont want to think ?

[Rachel_Carmichael]

Stephane,

I *did* warn you about the OT list before I approved your registration on
it..

btw, this sounds like it should have gone private, not to the list

Rachel



|+--->
||   |
||   |
||  sfaroult@orio|
||  le.com   |
||   |
||  05/18/2002   |
||  11:13 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: Do you ever have days where |
  |   you dont want to think ? |
  >|




Larry Elkins wrote:
>
> The beach part sounds great, though I don't care to see naked men running
> around ;-)

 That's the problem in this job. Too many men. And among women, too many
are like honorary men.


Still preaching the gospel, as you can see :-). In fact, I would be
curious to compare the join on the inline-view with a MINUS to the hash
anti-join. Assuming all correct indexes, it is comparing two index fast
full scans plus sort plus index search to two table full scans (more or
less). I am almost certain that many costs could be put into equations.
The problem is if you have too many values (number of rows, number of
rows returned, storage, selectivity, etc...) to feed into your equations
or have trouble deriving these it is not of much use.
Wanted to do some tests on reverse indexes, following your posts, but I
have not had time. Not much progress on the book PL/SQL chapter either.
Currently working on real-time, home-made replication (which works on
the standard edition, BTW). Still revolving around the same topics
because one of my concerns is to minimize overhead when logging
(trigger-happy replication, I am suspicious of the Shareplex approach
and anyway as I want to be able to replicate between France, Japan and
the US, I cannot afford to transfer full redo logs and transactions I
shall have to rollback). I log into several tables (enough info to
rebuild statements, and values separately - with additional problems
when we reach the 4,000 characters mark), IOTs are the obvious choice
but I am not sure it is available with all licenses. Degradation of my
logs over timeis also something I have to watch. This is for the days.
Evenings are spent improving an intelligent loader able to take into
account complex FK relationships. I have prepared two 5,000,000 row
tables, one in my 8.1.7 database and one in my 9.0.1 database, but I
have not tested anything yet.
I had a flash of idiocy and subscribed to the OT list (out of the
blues). I doubt I will stay long here. Some people seems to be paid
doing nothing but e-mailing. Is this where you got the details about
Pierce? Pretty active there.

Cheers,

Stephane Faroult
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Do you ever have days where you dont want to think ?

[Rachel_Carmichael]

as one of the women in this profession, do I want to know who you consider
"honorary men"?  :)

What I do want to know is how you determine what the criteria are for being an
"honorary man".

Inquiring minds want to know :)

Rachel



|+--->
||   |
||   |
||  sfaroult@orio|
||  le.com   |
||   |
||  05/18/2002   |
||  11:13 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: Do you ever have days where |
  |   you dont want to think ? |
  >|




Larry Elkins wrote:
>
> The beach part sounds great, though I don't care to see naked men running
> around ;-)

 That's the problem in this job. Too many men. And among women, too many
are like honorary men.


Still preaching the gospel, as you can see :-). In fact, I would be
curious to compare the join on the inline-view with a MINUS to the hash
anti-join. Assuming all correct indexes, it is comparing two index fast
full scans plus sort plus index search to two table full scans (more or
less). I am almost certain that many costs could be put into equations.
The problem is if you have too many values (number of rows, number of
rows returned, storage, selectivity, etc...) to feed into your equations
or have trouble deriving these it is not of much use.
Wanted to do some tests on reverse indexes, following your posts, but I
have not had time. Not much progress on the book PL/SQL chapter either.
Currently working on real-time, home-made replication (which works on
the standard edition, BTW). Still revolving around the same topics
because one of my concerns is to minimize overhead when logging
(trigger-happy replication, I am suspicious of the Shareplex approach
and anyway as I want to be able to replicate between France, Japan and
the US, I cannot afford to transfer full redo logs and transactions I
shall have to rollback). I log into several tables (enough info to
rebuild statements, and values separately - with additional problems
when we reach the 4,000 characters mark), IOTs are the obvious choice
but I am not sure it is available with all licenses. Degradation of my
logs over timeis also something I have to watch. This is for the days.
Evenings are spent improving an intelligent loader able to take into
account complex FK relationships. I have prepared two 5,000,000 row
tables, one in my 8.1.7 database and one in my 9.0.1 database, but I
have not tested anything yet.
I had a flash of idiocy and subscribed to the OT list (out of the
blues). I doubt I will stay long here. Some people seems to be paid
doing nothing but e-mailing. Is this where you got the details about
Pierce? Pretty active there.

Cheers,

Stephane Faroult
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Compressing Export Dumps

[Rachel_Carmichael]

way back (7.3 version) I wrote a set of scripts to export to a pipe, split and
compress the split files.. It was based on the Metalink doc and I *think* copies
of them are still out on the archives on www.lazydba.com

but now, when you can split files via export, so the split is done by Oracle and
supposedly fool-proof, why not do it that way?




|+--->
||   |
||   |
||  lembark@wrkho|
||  rs.com   |
||   |
||  05/16/2002   |
||  10:38 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: Compressing Export Dumps|
  >|





>> If you are on Unix, you can pipe the export into a "split" command and
>> break the file into multiples and compress on the fly. There's a note on
>> metalink about it (note 30528.1)

Easier of you split the zipped result:

mknod /tmp/dump p;

gzip --fast < /tmp/dump | split -b $((1024*1024*1024)) date
+"dump-%Y%m%d";

Gives a set of 1GB files as output. Simpler to manage since
they all have the same size (whatever you set -b to) w/ a
runt file at the end.

--
Steven Lembark   2930 W. Palmer
Workhorse Computing   Chicago, IL 60647
+1 800 762 1582
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steven Lembark
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Compressing Export Dumps / WinZip

[Rachel_Carmichael]

there is a BIG difference between the "COMPRESS=Y" parameter on an export and
compressing a file!

the parameter changes the "create table" statement placed in the export file so
that the initial extent is large enough to hold the entire table. It does NOT
affect the size of the export dump file in the least.

Compressing a file at the OS level removes "waste" space and (usually)
significantly reduces the file size



|+->
|| |
|| |
||  [EMAIL PROTECTED]|
||  -mpo.gc.ca |
|| |
||  05/16/2002 |
||  11:08 AM   |
||  Please respond |
||  to ORACLE-L|
|| |
|+->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Compressing Export Dumps /  |
  |   WinZip   |
  >|




I don't know whether this is a tangent, but I notice that on the windows
platform, "compressed" exports can still get 85% compression when zipping
them with WinZip.

Obviously Oracle "compressed=y" doesn't mean "compress the export file", it
just means that it places all the segments contiguously in the export file.

Right?

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin & Operations | Admin. et Exploit. des systèmes
Technology Services| Services technologiques
Informatics Branch | Direction de l'informatique
Maritimes Region, DFO  | Région des Maritimes, MPO

E-Mail: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Boivin, Patrice J
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Compressing Export Dumps

[Rachel_Carmichael]

If you are on Unix, you can pipe the export into a "split" command and break the
file into multiples and compress on the fly. There's a note on metalink about it
(note 30528.1)

Also, I *think* in 8.1.7 you can specify the size and names of the export files,
so that Oracle will automatically break the large file into several smaller
ones.

Check the Utilities manual




|+--->
||   |
||   |
||  [EMAIL PROTECTED]|
||  o.ug |
||   |
||  05/16/2002   |
||  05:38 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Compressing Export Dumps|
  >|




Hi all,
My export dumps are too big (80 GB) for my filesystem and I'm looking for a
way
to compress them on the fly -ie without taking *.dmp to disk first but
straight *.dmp.gz
Anybody with an idea on how to archive this ?
Thanking you,

---
CSW




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Deadlock ORA-60

[Rachel_Carmichael]

true -- but 99% of the time, it's in the application code (and you listed 4, not
3, additional reasons :)   )


|+--->
||   |
||   |
||  kaygopal@yaho|
||  o.com|
||   |
||  05/15/2002   |
||  04:38 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: Deadlock ORA-60 |
  >|




Rachel:

Not very true. Deadlocks can happen for n number of reasons. You can not
always
tell ' the problem is with application code'.

For example you can deadlocks for

1. Missing indexes in Foreign keys
2. ITL Entry shortages
3. INVALID triggers
4. Oracle BUGS (!)

The above three are database/design issues. Application code has no role
here

Best Regards,
K Gopalakrishnan
Bangalore, INDIA



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, May 16, 2002 1:33 AM




the problem is in the application code... find the sql (it's in the trace
files)
and start from there




|+--->
||   |
||   |
||  oracledbam@ho|
||  tmail.com|
||   |
||  05/15/2002   |
||  03:18 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Deadlock ORA-60 |
  >|




Hi
I am getting "ORA-60: Deadlock detected" error.I know this is the
deadlock situation.But my question is how to correct this problem.
Thx
-Seema


_
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] 

RE: How to drop a datafile from a tablespace quickly

[Rachel_Carmichael]

no you can't recompile them... I tried that once, the recompiled procedure
referenced the renamed (old) table

it's the object_id thing. Oracle doesn't care what you name or rename an object,
it tracks it by the object_id.

Trust me, it screwed up triggers etc. real pita




|+>
|||
|||
||  [EMAIL PROTECTED]|
||  tate.ny.us|
|||
||  05/15/2002|
||  03:53 PM  |
||  Please respond|
||  to ORACLE-L   |
|||
|+>
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: How to drop a datafile from |
  |   a tablespace quickly |
  >|




Rachel,

you said
"It's not just grants.  Any procedure that references that table will have
to be
recreated, oracle uses object_id not object_name so the procedure will point
to
the old table etc etc"

Can't he just re-compile the procedures?  He doesn't have to re-create them.

Tom Mercadante
Oracle Certified Professional


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 15, 2002 3:19 PM
To: Multiple recipients of list ORACLE-L




It's not just grants.  Any procedure that references that table will have to
be
recreated, oracle uses object_id not object_name so the procedure will point
to
the old table etc etc

you CAN try to resize the datafile down to something really small, smaller
than
the smallest extent if possible, That will keep data out of it but you are
going
to need downtime to fix this



|+--->
||   |
||   |
||  dgoulet@vicr.|
||  com  |
||   |
||  05/15/2002   |
||  01:48 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re:How to drop a datafile from a|
  |   tablespace quickly   |
  >|




Tom,

Regrettably there is no way that you can drop a datafile from a
tablespace.
Your stuck with having to drop the entire tablespace.  If you can export and
import that's great.  Otherwise try creating a new tablespace, copying the
table
(with a new name) into the new tablespace, dropping the original table and
tablespace, and then rename the table back to it's old name.  Don't forget
the
grants as well.

Dick Goulet

Reply Separator
Author: "Xie; Tom" <[EMAIL PROTECTED]>
Date:   5/15/2002 8:58 AM

Dear gurus:

I just added a data file to a big tablespace (11GB) that has only one
table. Unfortunately, when it was being backed up, the file head head
corrupted. I don't have any backup of this file. I found that there is
no data in this file yet. So I want to drop the file from the
tablespace. As I know, to drop a data file, I have to export the data,
drop the tablespace and recreate it, and then import data back to
the tablespace. However, since our users can't stop using the table,
I won't have enough down time to do that.

Is there anyway I can quickly drop a data file from a tablespace?

Don't tell me using "alter database datafile '...' offline drop" command.
It won't work.

I am working on Oracle 7.3.4.

Thanks,

Tom Xie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Xie, Tom
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
-

Re: Deadlock ORA-60

[Rachel_Carmichael]

the problem is in the application code... find the sql (it's in the trace files)
and start from there




|+--->
||   |
||   |
||  oracledbam@ho|
||  tmail.com|
||   |
||  05/15/2002   |
||  03:18 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Deadlock ORA-60 |
  >|




Hi
I am getting "ORA-60: Deadlock detected" error.I know this is the
deadlock situation.But my question is how to correct this problem.
Thx
-Seema


_
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: How to drop a datafile from a tablespace quickly

[Rachel_Carmichael]

there is a hardware problem that necessitated Oracle coming down. the only
question is does the database come down cleanly when YOU want it to or does it
come down with a crash, time undetermined, when the file is accessed?




|+--->
||   |
||   |
||  [EMAIL PROTECTED]|
||  ms.osd.mil   |
||   |
||  05/15/2002 02:39 PM  |
||  Please respond to|
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: How to drop a datafile from |
  |   a tablespace quickly |
  >|




Not that I'm aware of.  Sounds like an interesting idea, but one I've never
tried.  I just told the users there was a hardware problem that necessitated
oracle coming down (whether there was one or not).  Dishonest?  Maybe, but
it bought me the brief, immediate downtime necessary to prevent more serious
problems in the future.

Scott Shafer
San Antonio, TX
210-581-6217


> -Original Message-
> From: DENNIS WILLIAMS [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, May 15, 2002 12:58 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:   RE: How to drop a datafile from a tablespace quickly
>
> Scott - I agree with your advice. Could he take the bad datafile offline
> to
> prevent Oracle from writing to it (until he rebuilds the table)? Would
> that
> cause any other problems that I am overlooking?
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 15, 2002 12:33 PM
> To: Multiple recipients of list ORACLE-L
>
>
> No.  Get some downtime, quickly(!) before data does get written to that
> file.  Go through the export/drop tablespace/recreate.  BTW, do you know
> _why_ the file header is corrupted?  Is there a disk hardware problem?
> You
> are gonna have downtime sooner or later.  Tell damagement to get over it.
>
> Scott Shafer
> San Antonio, TX
> 210-581-6217
>
>
> > -Original Message-
> > From:   Xie, Tom [SMTP:[EMAIL PROTECTED]]
> > Sent:   Wednesday, May 15, 2002 11:58 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: How to drop a datafile from a tablespace quickly
> >
> > Dear gurus:
> >
> > I just added a data file to a big tablespace (11GB) that has only one
> > table. Unfortunately, when it was being backed up, the file head head
> > corrupted. I don't have any backup of this file. I found that there is
> > no data in this file yet. So I want to drop the file from the
> > tablespace. As I know, to drop a data file, I have to export the data,
> > drop the tablespace and recreate it, and then import data back to
> > the tablespace. However, since our users can't stop using the table,
> > I won't have enough down time to do that.
> >
> > Is there anyway I can quickly drop a data file from a tablespace?
> >
> > Don't tell me using "alter database datafile '...' offline drop"
> command.
> > It won't work.
> >
> > I am working on Oracle 7.3.4.
> >
> > Thanks,
> >
> > Tom Xie
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Xie, Tom
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> > San Diego, California-- Public Internet access / Mailing Lists
> > 
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L

RE: How to drop a data file from a tablespace quickly

[Rachel_Carmichael]

ashoke,

that's going to corrupt his database.. at some point. If you do that, you'd
better be willing to immediately shutdown, recreate the tablespace and then do a
backup




|+->
|| |
|| |
||  ashoke.k.mandal@med|
||  tronic.com |
|| |
||  05/15/2002 01:43 PM|
||  Please respond to  |
||  ORACLE-L   |
|| |
|+->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: How to drop a data file from|
  |   a tablespace quickly |
  >|





ALTER DATABASE  DATAFILE '' OFFLINE DROP;
Then remove the data file physically from the directory in UNIX or NT or any
other server.

Thanks,
Ashoke
-Original Message-
Sent: Wednesday, May 15, 2002 11:58 AM
To: Multiple recipients of list ORACLE-L


Dear gurus:

I just added a data file to a big tablespace (11GB) that has only one
table. Unfortunately, when it was being backed up, the file head head
corrupted. I don't have any backup of this file. I found that there is
no data in this file yet. So I want to drop the file from the
tablespace. As I know, to drop a data file, I have to export the data,
drop the tablespace and recreate it, and then import data back to
the tablespace. However, since our users can't stop using the table,
I won't have enough down time to do that.

Is there anyway I can quickly drop a data file from a tablespace?

Don't tell me using "alter database datafile '...' offline drop" command.
It won't work.

I am working on Oracle 7.3.4.

Thanks,

Tom Xie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Xie, Tom
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mandal, Ashoke
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re:How to drop a datafile from a tablespace quickly

[Rachel_Carmichael]

It's not just grants.  Any procedure that references that table will have to be
recreated, oracle uses object_id not object_name so the procedure will point to
the old table etc etc

you CAN try to resize the datafile down to something really small, smaller than
the smallest extent if possible, That will keep data out of it but you are going
to need downtime to fix this



|+--->
||   |
||   |
||  dgoulet@vicr.|
||  com  |
||   |
||  05/15/2002   |
||  01:48 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re:How to drop a datafile from a|
  |   tablespace quickly   |
  >|




Tom,

Regrettably there is no way that you can drop a datafile from a tablespace.
Your stuck with having to drop the entire tablespace.  If you can export and
import that's great.  Otherwise try creating a new tablespace, copying the table
(with a new name) into the new tablespace, dropping the original table and
tablespace, and then rename the table back to it's old name.  Don't forget the
grants as well.

Dick Goulet

Reply Separator
Author: "Xie; Tom" <[EMAIL PROTECTED]>
Date:   5/15/2002 8:58 AM

Dear gurus:

I just added a data file to a big tablespace (11GB) that has only one
table. Unfortunately, when it was being backed up, the file head head
corrupted. I don't have any backup of this file. I found that there is
no data in this file yet. So I want to drop the file from the
tablespace. As I know, to drop a data file, I have to export the data,
drop the tablespace and recreate it, and then import data back to
the tablespace. However, since our users can't stop using the table,
I won't have enough down time to do that.

Is there anyway I can quickly drop a data file from a tablespace?

Don't tell me using "alter database datafile '...' offline drop" command.
It won't work.

I am working on Oracle 7.3.4.

Thanks,

Tom Xie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Xie, Tom
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Order rows

[Rachel_Carmichael]

I suppose you can if you don't care about wasted disk space, I know everyone
says "disk is cheap" but that's only until you explain to your manager that you
need a 100GB disk for a 10GB (real used space) database because you are forcing
order by storing each row in one block and never reusing space freed up by
deleted rows :)




|+--->
||   |
||   |
||  kaygopal@yaho|
||  o.com|
||   |
||  05/15/2002   |
||  10:28 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: Order rows  |
  >|




Rachel:


If you want to FORCE the order you can do couple of things like having a big
PCTFREE (to make sure that no rows are migrated because of the updates) and
small PCTUSED so that the blocks are never reused. In this case you will get
the
rows in the inserted order.

ANother alternative is your method (additional column SYSDATE)

Best Regards,
K Gopalakrishnan
Bangalore, INDIA




- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, May 15, 2002 7:33 PM


>
>
> Do you want to physically order them or do you just want to know by time
the
> order in which they were entered.
>
> if the first, no, not that I know of. If the later, yes, add another
column
> (ins_date date) and a trigger to populate that column with sysdate when
you
> insert a row. You can then order by ins_date
>


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Order rows

[Rachel_Carmichael]

that works unless there is also a reason to see insert not just in order but by
"inserted on a particular date or time". I suppose in that case, add two fields,
a date field for the time range and a numeric field for the sequence of insert




|+--->
||   |
||   |
||  ktoepke@trile|
||  giant.com|
||   |
||  05/15/2002   |
||  11:13 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Order rows  |
  >|




using a date will work unless you get multiple records created in a given
second. Use a sequence generated number. The larger the number, the newer
the record. Just order by the sequence to see the order the records were
inserted.

Caver

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 15, 2002 10:03 AM
To: Multiple recipients of list ORACLE-L




Do you want to physically order them or do you just want to know by time the
order in which they were entered.

if the first, no, not that I know of. If the later, yes, add another column
(ins_date date) and a trigger to populate that column with sysdate when you
insert a row. You can then order by ins_date




|+-->
||  |
||  |
||  "systems_ho/VGIL"@vguard.sat|
||  yam.net.in  |
||  |
||  05/15/2002 02:08 AM |
||  Please respond to ORACLE-L  |
||  |
|+-->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Order rows  |
  >|




Hi All

Is there any method in Oracle to capture or order the rows in a table in
the order they were entered.
I tried it with rowid but when a row is deleted, the rowid corresponding to
this row is reassigned for a new row
which is inserted into the table at a later stage.

Eg.

SQL> select rowid,abc.* from abc order by rowid;

ROWID   A
-- --
AAAFmYAASAAAYsqAAA100
AAAFmYAASAAAYsqAAB200
AAAFmYAASAAAYsqAAC300

I deleted one transaction.

delete from abc where a=200;
commit;

Then I inserted two rows.

insert into abc values(500);
insert into abc values(600);
commit;

Now when I order by rowid

SQL>  select rowid,abc.* from abc order by rowid;

ROWID   A
-- --
AAAFmYAASAAAYsqAAA100
AAAFmYAASAAAYsqAAB600
AAAFmYAASAAAYsqAAC300
AAAFmYAASAAAYsqAAD500

I.e The values I entered last appeared second.The rowid (AAAFmYAASAAAYsqAAB
) corresponding to the row I deleted was reassigned for the last entered
row (a=600).
What I want is that this must be sorted in the order of its entry.
Can anyone help me out.
Thanks in advance
Systems.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for

Re: Order rows

[Rachel_Carmichael]

Do you want to physically order them or do you just want to know by time the
order in which they were entered.

if the first, no, not that I know of. If the later, yes, add another column
(ins_date date) and a trigger to populate that column with sysdate when you
insert a row. You can then order by ins_date




|+-->
||  |
||  |
||  "systems_ho/VGIL"@vguard.sat|
||  yam.net.in  |
||  |
||  05/15/2002 02:08 AM |
||  Please respond to ORACLE-L  |
||  |
|+-->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Order rows  |
  >|




Hi All

Is there any method in Oracle to capture or order the rows in a table in
the order they were entered.
I tried it with rowid but when a row is deleted, the rowid corresponding to
this row is reassigned for a new row
which is inserted into the table at a later stage.

Eg.

SQL> select rowid,abc.* from abc order by rowid;

ROWID   A
-- --
AAAFmYAASAAAYsqAAA100
AAAFmYAASAAAYsqAAB200
AAAFmYAASAAAYsqAAC300

I deleted one transaction.

delete from abc where a=200;
commit;

Then I inserted two rows.

insert into abc values(500);
insert into abc values(600);
commit;

Now when I order by rowid

SQL>  select rowid,abc.* from abc order by rowid;

ROWID   A
-- --
AAAFmYAASAAAYsqAAA100
AAAFmYAASAAAYsqAAB600
AAAFmYAASAAAYsqAAC300
AAAFmYAASAAAYsqAAD500

I.e The values I entered last appeared second.The rowid (AAAFmYAASAAAYsqAAB
) corresponding to the row I deleted was reassigned for the last entered
row (a=600).
What I want is that this must be sorted in the order of its entry.
Can anyone help me out.
Thanks in advance
Systems.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: list of events

[Rachel_Carmichael]

You can use the oerr facility on Unix to look at the events... I don't know if
that file exists under Windows, I vaguely recall a conversation on the list a
while ago where people were saying it didn't. I know that I downloaded the file
from my Unix box to my PC so I'd have a copy.

Anyone out there with a Windows installation who can check this please?

And, in any case, the file just tells you what the event name is:

bash$ oerr ora 10053
10053, 0, "CBO Enable optimizer trace"
// *Cause:
// *Action:
bash$ oerr ora 10046
10046, 0, "enable SQL statement timing"
// *Cause:
// *Action:

and doesn't give you much other information. I'd love to see an Oracle doc just
on these events

Rachel



|+--->
||   |
||   |
||  Rajendra.Jamadagn|
||  [EMAIL PROTECTED]   |
||   |
||  05/14/2002 05:43 |
||  PM   |
||  Please respond to|
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: list of events  |
  >|




Rachel,

Actually, error messages between 1 and 10999 are events (i.e. 10046,
10053 etc). In 9i there are some new events in the 297xx range as well.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 14, 2002 4:53 PM
To: Multiple recipients of list ORACLE-L



actually that's the list of error messages, not events

and it's not under the admin directory in any case the path to the error
messages is:

$ORACLE_HOME/rdbms/mesg/roaus.msg
(See attached file: ESPN_Disclaimer.txt)




ESPN_Disclaimer.txt
Description: Text - character set unknown

RE: list of events

[Rachel_Carmichael]

yeah, but it's confusing when you look at that file...




|+--->
||   |
||   |
||  Rajendra.Jamadagn|
||  [EMAIL PROTECTED]   |
||   |
||  05/14/2002 05:43 |
||  PM   |
||  Please respond to|
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: list of events  |
  >|




Rachel,

Actually, error messages between 1 and 10999 are events (i.e. 10046,
10053 etc). In 9i there are some new events in the 297xx range as well.

Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 14, 2002 4:53 PM
To: Multiple recipients of list ORACLE-L



actually that's the list of error messages, not events

and it's not under the admin directory in any case the path to the error
messages is:

$ORACLE_HOME/rdbms/mesg/roaus.msg
(See attached file: ESPN_Disclaimer.txt)




ESPN_Disclaimer.txt
Description: Text - character set unknown

RE: list of events

[Rachel_Carmichael]

actually that's the list of error messages, not events

and it's not under the admin directory in any case the path to the error
messages is:

$ORACLE_HOME/rdbms/mesg/roaus.msg




|+--->
||   |
||   |
||  Rajendra.Jamadagn|
||  [EMAIL PROTECTED]   |
||   |
||  05/14/2002 04:23 |
||  PM   |
||  Please respond to|
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: list of events  |
  >|




$ORACLE_HOME/rdbms/admin/mesg/oraus.msg

Raj
__


Rajendra Jamadagni  MIS, ESPN Inc.


Rajendra dot Jamadagni at ESPN dot com


Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.


QOTD: Any clod can have facts, but having an opinion is an art!


 -Original Message-
 From: Babu Nagarajan [mailto:[EMAIL PROTECTED]]
 Sent: Tuesday, May 14, 2002 3:48 PM
 To: Multiple recipients of list ORACLE-L
 Subject: list of events

 All

 I know this has been mentioned on this list before but I forget...

 Which is the script in $ORACLE_HOME/rdbms/admin folder that contains the
 list of all events?

 TIA

 Babu(See attached file: ESPN_Disclaimer.txt)




ESPN_Disclaimer.txt
Description: Text - character set unknown

Re: Create synonyms in externally authenticated user's schema

[Rachel_Carmichael]

log on using an account with CREATE ANY SYNONYM privs and create the synonym as:

create .synonym for 




|+-->
||  |
||  |
||  Beth.Seefelt@tet|
||  leyusa.com  |
||  |
||  05/13/2002 04:38|
||  PM  |
||  Please respond  |
||  to ORACLE-L |
||  |
|+-->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Create synonyms in externally   |
  |   authenticated user's schema  |
  >|







Hi all,


When I create a new nt OS authenticated user I need to create several synonyms
in that users's schema?  How can I create synonyms as that user easily?


I know that I can get them to give me their nt password and log in as them, or
create a stored procedure that executes as that user, or a user logon trigger,
but I'm hoping there is a more straight forward way to do this?


TIA,


Beth







-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: A trigger vs. "default value" in a table

[Rachel_Carmichael]

a default value.




|+->
|| |
|| |
||  andreyb@elronte|
||  lesoft.com |
|| |
||  05/13/2002 |
||  12:23 PM   |
||  Please respond |
||  to ORACLE-L|
|| |
|+->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: A trigger vs. "default value" in|
  |   a table  |
  >|




Dear list !
I need a very simple thing : each time a certain field is null during an
insert, to substitiute a string "AAA" instead.
What is better : to write a trigger to do so or to define a default value
for that column in the table ?
Thanks !


DBAndrey

* 03-9254520
* 058-548133
* mailto:[EMAIL PROTECTED]




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Good HR vs. Bad HR...

[Rachel_Carmichael]

give me a break! It took me 25+ years to get to the point where I have a group
who will listen to me. And that's only one small group in the larger group I
support, I doubt I'll be that lucky across the board



|+--->
||   |
||   |
||  ksmith2@myfir|
||  stlink.net   |
||   |
||  05/10/2002   |
||  11:08 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Good HR vs. Bad HR...   |
  >|




Hum, I have to fight a bit more for mine suggestions to go through.  That's
not fair.  I guess its good for me as its helping my debating skills:-)  It
does not help that the folks I work with are half way across the country.

-Original Message-
[EMAIL PROTECTED]
Sent: Friday, May 10, 2002 6:38 AM
To: Multiple recipients of list ORACLE-L




Don,

Next time I need to resign, will you help me write the letter?

Having worked at several large and not-so large companies, all I can say is,
the
problems you documented seem to be ubiquitous throughout most management. I
have
found it nearly impossible in some places to do my job with any degree of
accuracy, because management has its own version of reality and if what I
said
didn't conform, it was thrown out.  Something akin to deciding what the
results
of that physics experiment should be before beginning it, and throwing out
all
results that don't support the hypothesis.

What amazes me is that these companies continue to survive in the face of
such
insanity.

So far, it does not look to be like that here. I have reviewed and made
suggestions in the past two weeks and the response has been "she's right,
let's
do it". And it gets done. I might actually like it here :)

Rachel



|+--->
||   |
||   |
||  granaman@cox.|
||  net  |
||   |
||  05/09/2002   |
||  06:37 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: Good HR vs. Bad HR...   |
  >|




I think enough has been said already - I didn't intend to name the company
at
all.  Actually, I
don't think that I said that all the managers were incompetent.  (A select
few
in the wrong places
perhaps.)  Since the cat is out of the bag though, I will try to end this
here
and now.

I had a number of specific and well-documented "complaints", so I fired off
this
kamikaze
resignation letter - straight through three layers of management, even
cc'ing
one of the two owners
of the company.  The original formatting was better, but here it is in
ASCII.
---
[...]
---
It is demoralizing also to see a parade of "experts", "gurus", and
"geniuses"
hand off botched,
half-baked, poorly designed, and dysfunctional systems, then get major
promotions and/or
commendations.

Case in point: The CIS applications were handed over to Wanda Kelley and I
as
upper management
mourned the loss of their "Oracle genius" (a direct quote). Well this
"genius"
left us a system with
almost no data integrity - there were only two foreign keys for 44 tables.
Only
about 70% of the
tables even had a primary key. There were no check constraints or triggers
to
enforce data
integrity.  Nothing was enforced in the application.  And the application
handled extremely few of
the business requirements.  For example, the application does not function
properly unless LOGONID
in the EMPLOYEE table is unique. There was no such uniqueness constraint.
Furthermore, the
application uses "where upper(LOGONID) = :some_variable", so a uniqueness
constraint alone is
insufficient - there also needs to be a method to enforce uppercase only on
this
column on inserts
and updates.  There was none.  One could enter a new employee record, then
immediately query for it,
exactly as entered, and not find it!  Both copies of the Oracle control file
were in the same
directory. The online redo logs were not mirrored. While these and other
basic
errors and omissions
were obv

Re: why does DBCA (DB Create Assist) reassign default tablesepace

[Rachel_Carmichael]

That's CORRECT... no one other than SYS should have the SYSTEM tablespace as the
default tablespace. No one should have SYSTEM as the temporary tablespace.

for years oracle would say "change the default tablespace". Now they do it for
you (and about bloodly time!)




|+-->
||  |
||  |
||  Bill.Magaliff@le|
||  ndware.com  |
||  |
||  05/10/2002 03:48|
||  PM  |
||  Please respond  |
||  to ORACLE-L |
||  |
|+-->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: why does DBCA (DB Create Assist)|
  |   reassign default tablesepace for |
  >|




Anyone know why, when using DBCA, it configures user SYSTEM to have a
default tablespace of TOOLS and not SYSTEM?

This is a separate script it runs at the end, after the db and data
dictionary are both created.

thought system should have system tablespace as default, no?

thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Rman ... what do YOU need

[Rachel_Carmichael]

Dennis,

It's possible the version you have was corrected. I know that it was the first
printing that had some serious, unintentinal omissions I think only in the
first few chapters.

What really hurts the author is when they catch the error, send the correction
in in time to be fixed for the bound printing and it doesn't make it in. I mean,
it's bad enough that I miss things and they get into print (and thank goodness
marlene and I back each other up and find things the other misses). Fortunately,
we've never had the problem where we corrected the errors and they went through
anyway.

We have an excellent project editor, who is as anal as we are about putting out
a clean book.. so I sometimes get several sets of page proofs.  Sigh, and I
wonder why my eyes ache all the time and I can't see anymore!

Rachel



|+--->
||   |
||   |
||  DWILLIAMS@lif|
||  etouch.com   |
||   |
||  05/10/2002   |
||  04:13 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Rman ... what do YOU need   |
  >|




Thanks Rachel. To choose between speedy publication (copyright 2002) and
waiting another 6 months for an error-free book, I'll choose speedy.
Especially since I've become increasingly desperate for a good RMAN tutorial
over the past months. And after all, this book is designed to teach you to
BACKUP your database. And honestly, I haven't noticed the typos. But I do
appreciate your pointing out the errata site.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Friday, May 10, 2002 2:28 PM
To: Multiple recipients of list ORACLE-L




Folks, just a word of warning. Through NO FAULT of the authors, there are a
number of errata in the first edition of the book.

There is a complete errata list on the Osborne site:

http://shop.osborne.com/cgi-bin/oraclepress/errata.html


Page proofing is one of the worst tasks on earth (I know, I'm doing it now
for
the 9i version of DBA 101). We found one chapter that is totally messed up,
we
had to have it resubmitted to be put into proofs. With deadlines and print
schedules what they are (TIGHT), we are lucky that there is time to redo it
before the book comes out in June.

Rachel





|+--->
||   |
||   |
||  DWILLIAMS@lif|
||  etouch.com   |
||   |
||  05/10/2002   |
||  01:58 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Rman ... what do YOU need   |
  >|




Kitri - Thanks so much for pointing this book out. I have purchased it and
worked through almost all the examples. It is excellent. It was exactly what
I needed. I had found it difficult to get started with RMAN. I had read the
Oracle manual, and taken the Oracle 8i Backup & Recovery class (briefly
covers RMAN, no classroom exercises), and felt I was getting nowhere. I
needed some concrete practice exercises.
   About half of Backup & Recovery 101 is devoted to RMAN. He takes
you
step-by-step through creating a practice database, creating an RMAN catalog
database, configuring RMAN, performing backups, listing RMAN information
from both the RMAN catalog and the target database control file, performing
recoveries using RMAN, creating an RMAN duplicate database, creating an RMAN
standby database, and performing an RMAN tablespace point-in-time recovery.
Each chapter has several relevant exercises to work through.
   He has instructions for both Linux and NT, but he worked the
exercises on Linux, so for NT you will have to adjust more, but there is
probably enough information for you to succeed. I used the Linux
instructions on a Unix system and had no problems, other than the fact that
my practice system is Oracle 8.1.6 and his instructions are for Oracle
8.1.7. But the adjustments were simple and even added to my learning.
   So, 

Re:Oracle Diagnostic and Tuning Packs

[Rachel_Carmichael]

supposedly no additional cost in 9i, but when I checked Oracle's store, there
was a $20 charge for each. Considering that for 7.3 and 8.0 they were $100 for
EVERY NAMED USER, regardless of how many people were actually going to use them,
that's "no cost" :)




|+--->
||   |
||   |
||  dgoulet@vicr.|
||  com  |
||   |
||  05/10/2002   |
||  02:33 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re:Oracle Diagnostic and Tuning |
  |   Packs|
  >|




We've go them & I think so.

Dick Goulet


Reply Separator
Author: "Schauss; Peter" <[EMAIL PROTECTED]>
Date:   5/10/2002 7:58 AM

Are these addons to OEM worth buying?

Thanks
Peter Schauss
Northrop Grumman Corporation
[EMAIL PROTECTED]

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Schauss, Peter
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Rman ... what do YOU need

[Rachel_Carmichael]

Folks, just a word of warning. Through NO FAULT of the authors, there are a
number of errata in the first edition of the book.

There is a complete errata list on the Osborne site:

http://shop.osborne.com/cgi-bin/oraclepress/errata.html


Page proofing is one of the worst tasks on earth (I know, I'm doing it now for
the 9i version of DBA 101). We found one chapter that is totally messed up, we
had to have it resubmitted to be put into proofs. With deadlines and print
schedules what they are (TIGHT), we are lucky that there is time to redo it
before the book comes out in June.

Rachel





|+--->
||   |
||   |
||  DWILLIAMS@lif|
||  etouch.com   |
||   |
||  05/10/2002   |
||  01:58 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Rman ... what do YOU need   |
  >|




Kitri - Thanks so much for pointing this book out. I have purchased it and
worked through almost all the examples. It is excellent. It was exactly what
I needed. I had found it difficult to get started with RMAN. I had read the
Oracle manual, and taken the Oracle 8i Backup & Recovery class (briefly
covers RMAN, no classroom exercises), and felt I was getting nowhere. I
needed some concrete practice exercises.
   About half of Backup & Recovery 101 is devoted to RMAN. He takes you
step-by-step through creating a practice database, creating an RMAN catalog
database, configuring RMAN, performing backups, listing RMAN information
from both the RMAN catalog and the target database control file, performing
recoveries using RMAN, creating an RMAN duplicate database, creating an RMAN
standby database, and performing an RMAN tablespace point-in-time recovery.
Each chapter has several relevant exercises to work through.
   He has instructions for both Linux and NT, but he worked the
exercises on Linux, so for NT you will have to adjust more, but there is
probably enough information for you to succeed. I used the Linux
instructions on a Unix system and had no problems, other than the fact that
my practice system is Oracle 8.1.6 and his instructions are for Oracle
8.1.7. But the adjustments were simple and even added to my learning.
   So, if you are considering RMAN, but don't know where to start, I
enthusiastically recommend that you buy Oracle Backup & Recovery 101.
http://shop.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=1G60ZMKA1J&;
mscssid=G50N06L3282V9M7H7E1C63LT2FLNDC69&isbn=0072194618
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Tuesday, April 09, 2002 1:41 PM
To: Multiple recipients of list ORACLE-L


FWIW --
Oracle Press recently published a "Backup & Recovery 101" book by Kenny
Smith and Stephan Haisley.
I have not yet read it, but it claims to have RMAN coverage.
Since it is part of the '101' series, I presume it covers most of the basic
stuff.
Has anyone purchased it? And read it?
I may check it out at IOUG-A next week :)

Regards,

- Kirti


-Original Message-
Sent: Tuesday, April 09, 2002 12:54 PM
To: Multiple recipients of list ORACLE-L


I'm contemplating doing an Rman backup and recovery handbook. I'm wondering
what you would like to see in such a book and would you use such a book?
Ideas and comments welcome.

Robert G. Freeman - Oracle8i OCP
Oracle DBA Technical Lead
CSX Midtier Database Administration

The Cigarette Smoking Man: Anyone who can appease a man's conscience can
take his freedom away from him.



-Original Message-
Sent: Monday, April 08, 2002 2:54 PM
To: Multiple recipients of list ORACLE-L


I don't think you can do it.. I mean, you could change it to trunc the
oracle_date field (that eliminates the minutes) and then do a to_date
of :b1 but you will still be operating on the oracle_date field.

Okay, I HATE to suggest this, but since the table is small:

add another field to the table oracle_date_2 as a date field. Update
the table set oracle_date_2=trunc(oracle_date)

add a trigger to fill in oracle_date_2 when you insert a row or update
the oracle_date column


create an index on oracle_date_2 and change the query to use that
column


--- [EMAIL PROTECTED] wrote:
>
> I've got the following SQL statement that is running very long on a
> nightly
> data load.   The problem is the TO_CHAR function which is preventing
> me from using the index on this small (20,000-row table).
>
> This is an 8.0.4 database so it is not possi

Re: Good HR vs. Bad HR...

[Rachel_Carmichael]

Don,

Next time I need to resign, will you help me write the letter?

Having worked at several large and not-so large companies, all I can say is, the
problems you documented seem to be ubiquitous throughout most management. I have
found it nearly impossible in some places to do my job with any degree of
accuracy, because management has its own version of reality and if what I said
didn't conform, it was thrown out.  Something akin to deciding what the results
of that physics experiment should be before beginning it, and throwing out all
results that don't support the hypothesis.

What amazes me is that these companies continue to survive in the face of such
insanity.

So far, it does not look to be like that here. I have reviewed and made
suggestions in the past two weeks and the response has been "she's right, let's
do it". And it gets done. I might actually like it here :)

Rachel



|+--->
||   |
||   |
||  granaman@cox.|
||  net  |
||   |
||  05/09/2002   |
||  06:37 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: Good HR vs. Bad HR...   |
  >|




I think enough has been said already - I didn't intend to name the company at
all.  Actually, I
don't think that I said that all the managers were incompetent.  (A select few
in the wrong places
perhaps.)  Since the cat is out of the bag though, I will try to end this here
and now.

I had a number of specific and well-documented "complaints", so I fired off this
kamikaze
resignation letter - straight through three layers of management, even cc'ing
one of the two owners
of the company.  The original formatting was better, but here it is in ASCII.
---
[...]
---
It is demoralizing also to see a parade of "experts", "gurus", and "geniuses"
hand off botched,
half-baked, poorly designed, and dysfunctional systems, then get major
promotions and/or
commendations.

Case in point: The CIS applications were handed over to Wanda Kelley and I as
upper management
mourned the loss of their "Oracle genius" (a direct quote). Well this "genius"
left us a system with
almost no data integrity - there were only two foreign keys for 44 tables. Only
about 70% of the
tables even had a primary key. There were no check constraints or triggers to
enforce data
integrity.  Nothing was enforced in the application.  And the application
handled extremely few of
the business requirements.  For example, the application does not function
properly unless LOGONID
in the EMPLOYEE table is unique. There was no such uniqueness constraint.
Furthermore, the
application uses "where upper(LOGONID) = :some_variable", so a uniqueness
constraint alone is
insufficient - there also needs to be a method to enforce uppercase only on this
column on inserts
and updates.  There was none.  One could enter a new employee record, then
immediately query for it,
exactly as entered, and not find it!  Both copies of the Oracle control file
were in the same
directory. The online redo logs were not mirrored. While these and other basic
errors and omissions
were obvious to us untrusted flunkies, the "genius" overlooked them. The users
were connecting to
the database as the SYSTEM user (with the default password "MANAGER" of course).
His project plan
included installing SQL*Net on hundreds of PC's using WCSGCOPY.  This doesn't
work, all it does is
remove the ORACLE_HOME directory and replace it with files copied from a server.
It doesn't update
the registry and it doesn't consider that there may already be an Oracle client
on the machine
(typically, there is - and it includes things that this method would delete,
like the Oracle forms
runtime).  This last item became an issue when I first heard of it less than a
week before the
implementation date. (Incidentally, that was the first time that most of us even
heard about this
project.)  We tested a variation of this method for CIT client installations and
determined that it
was impractical. How does one do such a poor job and yet convince everyone they
are a "genius"? Is
perception is truly everything here?

To further compound the problem, we were never allowed to question or even
review anything - not the
application, not the database, and not the schema.  The whole mess was a
well-guarded secret until
the very Friday afternoon that this consultant's contract was over and he left
the company.  At that
time, it was turned over to us.  We very quickly di

Re: script to show heirarchical list of object dependencies for a

[Rachel_Carmichael]

How is this essentially different than what you can get from utldtree.sql?

when Kevin Loney and I were writing the Annotated Archives a few years ago, I
tried to write a script that would show the dependencies, because part of the
reason for the book was to provide "home-grown" scripts that wre documented. I
ended up essentially rewriting the cod from utldtree.sql and we gave up that
particular effort.




|+--->
||   |
||   |
||  jack_silvey@y|
||  ahoo.com |
||   |
||  05/09/2002   |
||  07:03 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: script to show heirarchical list|
  |   of object dependencies for a given object|
  >|




Greetings listers,

Ever worry about wrinkles, loss of hair, and bad
breath?

Well, the following script can't help you with THAT,
but it CAN show you a heirarchical list of objects
that depend upon the given object! Just pass in
&1=owner and &2=object name, and viola! No more
changing objects without knowing what depends on them
(and might break). (The reason for the temp table is
that you can't do recursive self-join sql on complex
views.) This can be a very useful script if you make
production database changes.

prompt
prompt objects depending on &1. &2:
prompt

set termout off
set head off
set verify off

drop table depends;

create table depends as select * from
sys.dba_dependencies tablespace tools;

set termout on

select  '*'||lpad(' ',level+3)||type||'
'||owner||'.'||name
from depends
connect by prior owner = referenced_owner
and prior name = referenced_name
and prior type = referenced_type
start with referenced_owner = upper('&1')
and referenced_name = upper('&2')
and owner is not null
/

good luck,

jack silvey


__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: optimizer not using function based indexes

[Rachel_Carmichael]

And I respectfully agree that I was wrong :)It happens, I think I already
posted a note to the list apologizing and correcting my error.

This is what happens when I post from memory without double checking
obviously I had done a "flush" of my shared_pool!

Rachel


|+--->
||   |
||   |
||  MGogala@oxhp.|
||  com  |
||   |
||  05/09/2002   |
||  05:43 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: optimizer not using function|
  |   based indexes|
  >|




I respectfully disagree. In order to use a function based index
you do need the upper. If optimizer doesn't pick it up straigt
away, he should give a hand with a hint (/*+ INDEX(alias,index) */)

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 09, 2002 4:49 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: optimizer not using function based indexes
>
>
>
>
> you don't need the upper(ename) in your where clause... that
> forces oracle to
> NOT use an index
>
>
>
>
> |+->
> || |
> || |
> ||  Harvinder.Singh@met|
> ||  ratech.com |
> || |
> ||  05/09/2002 04:18 PM|
> ||  Please respond to  |
> ||  ORACLE-L   |
> || |
> |+->
>   >|
>   ||
>   |   To: [EMAIL PROTECTED] |
>   |   cc: (bcc: Rachel Carmichael) |
>   |   Subject: optimizer not using function|
>   |   based indexes|
>   >|
>
>
>
>
> Hi,
>
> We are trying to use function based indexes in our
> application but it looks like
>
> optimizer is  not using them.
> So we created a sample table emp with 3 columns
> empno,ename,sal and populate
> this table with 10 rows and created function based index
> on upper(ename).
> we try to run simple query like below and optimzer is not using index:
> select sal from emp
> where upper(ename)=upper('abc98');
>
> Even we use index hint optimizer is still not using index.
>
> 1) table is analyzed as compute after creating index.
> 2) query rewrite is granted to user
> 3) query_rewrite_enabled=true.
> 4) optimzer mode is choose.
> 5) optimer should use index as there is no way full scan can
> be faster than
> index in this case as i tried to create simple
> index on ename and remove upper function from where clause of query..
>
>
> What can be the possible reason that optimzer is not using index??
>
> Thanks
> --Harvinder
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Harvinder Singh
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
  INET: [EMAIL PROTECTED]

Fat City Network 

RE: optimizer not using function based indexes

[Rachel_Carmichael]

and stats have to have been collected on the index and base table as well

just checked the 9i docs... I was wrong you do need the upper on the column, my
bad.. but you need the stats, the compatibility set (as Beth says)




|+-->
||  |
||  |
||  Beth.Seefelt@tet|
||  leyusa.com  |
||  |
||  05/09/2002 05:24|
||  PM  |
||  Please respond  |
||  to ORACLE-L |
||  |
|+-->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: optimizer not using function|
  |   based indexes|
  >|





Another requirement is that your compatibility init.ora parameter must
be set to 8.1.7 or higher, I believe, but check the doco.

If that doesn't fix it, then trying using a hint to force the use of
that index.  If it doesn't work with the hint, then you know you have an
environment problem that is preventing the use of function-based
indexes.  If it does work with the hint, then you know that function
based is working but you have to figure out why the optimizer is not
choosing it.

HTH,

Beth

-Original Message-
Sent: Thursday, May 09, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L


Hi,

We are trying to use function based indexes in our application but it
looks like
optimizer is  not using them.
So we created a sample table emp with 3 columns empno,ename,sal and
populate
this table with 10 rows and created function based index on
upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');

Even we use index hint optimizer is still not using index.

1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster
than index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..


What can be the possible reason that optimzer is not using index??

Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: optimizer not using function based indexes

[Rachel_Carmichael]

you don't need the upper(ename) in your where clause... that forces oracle to
NOT use an index




|+->
|| |
|| |
||  Harvinder.Singh@met|
||  ratech.com |
|| |
||  05/09/2002 04:18 PM|
||  Please respond to  |
||  ORACLE-L   |
|| |
|+->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: optimizer not using function|
  |   based indexes|
  >|




Hi,

We are trying to use function based indexes in our application but it looks like

optimizer is  not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');

Even we use index hint optimizer is still not using index.

1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..


What can be the possible reason that optimzer is not using index??

Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: DBA_EXTENTS problem

[Rachel_Carmichael]

I knew someone on the list  would have it :)  thanks Suzy.

actually, I'd do dbms_stats.delete_schema_stats('SYS') this time through to fix
the problem and then change the dbms_stats command to
dbms_stats.gather_schema_stats instead of database




|+--->
||   |
||   |
||  lvordos@qwest|
||  .com |
||   |
||  05/09/2002   |
||  03:44 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: DBA_EXTENTS problem |
  >|





dbms_stats.gather_database_stats has a bug, it analyzes SYS objects.

>From Metalink:
Bug:1422285 is a severity 3 bug that was logged for the
dbms_stats.gather_database_stats collecting statistics on the data
dictionary objects (FET$ was the example). This bug is fixed in 9i. A
workaround is to execute dbms_stats.gather_database_statistics +
dbms_stats.delete_schema_stats('SYS').


Janardhana Babu Donga wrote:
>
> I have recently changed the analyze script. Earliar it was
> dbms_utility.analyze_schema(...) statement, It is now changed to
> dbms_stats.gather_database_stats();
>
> Is this a problem?
>
> The response time for select count(*) from dba_extents is also 30 minutes.
> It is not specific to any table.
> Thanks,
> -- Babu
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 09, 2002 11:36 AM
> To: Multiple recipients of list ORACLE-L
>
> that's interesting... since the data dictionary is NOT analyzed, setting
> optimizer_mode=choose would force the query against dba_extents to RULE
> which it
> what it is supposed to be doing anyway, Hm. The question now is, what is the
> optimizer_mode set to when the problem happens? Did any of the data
> dictionary
> tables get accidentally analyzed? And -- how many extents are in use in the
> database? Could it just be a symptom of missized tables and indexes so that
> the
> number of extents is way high?
>
> Although Oracle is *supposed* to allow unlimited extents, in practice
> anything
> higher than 4096 extents in an object (at least in 8i) tends to slow things
> down.
>
> |+--->
> ||   |
> ||   |
> ||  jack_silvey@y|
> ||  ahoo.com |
> ||   |
> ||  05/09/2002   |
> ||  02:18 PM |
> ||  Please   |
> ||  respond to   |
> ||  ORACLE-L |
> ||   |
> |+--->
>   >|
>   ||
>   |   To: [EMAIL PROTECTED] |
>   |   cc: (bcc: Rachel Carmichael) |
>   |   Subject: Re: DBA_EXTENTS problem |
>   >|
>
> Babu,
>
> We had a similiar problem, and setting optimizer_mode
> = choose in our session solved it. Something to do
> with optimizer and DD access. Give that a try. I had
> the same problem with DBA_INDEXES and that fixed it.
>
> hth,
>
> Jack
>
> --- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote:
> > Dear List,
> >
> > When I query dba_extents , Iam getting response
> > after half an hour to 40
> > minutes, but when I query any other dictionary view
> > it is spontaneous.
> > Everything else is fine in the database and there
> > are no problems, except
> > the above problem. Iam not getting any clue how to
> > fix this. Iam thinking of
> > running catalog.sql and catproc.sql as a last
> > resort. Iam not sure it would
> > fix the problem. Please let me know if there is any
> > other way to fix this
> > problem.
> >
> > Thanks,
> > Babu
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Janardhana Babu Donga
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051  FAX:
> > (858) 538-5051
> > San Diego, California-- Public Internet
> > access / Mailing Lists
> >
> 
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from).  You may
> > als

RE: DBA_EXTENTS problem

[Rachel_Carmichael]

analyzing SYSTEM is not the same as analyzing SYS, which is what you have done
by using gather_database_stats

run dbms_stats.delete_schema_stats('SYS') to remove the statistics

then either always run the delete after the gather_database_stats or switch to
gather_schema_stats




|+--->
||   |
||   |
||  jbdonga@ucdav|
||  is.edu   |
||   |
||  05/09/2002   |
||  03:41 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: DBA_EXTENTS problem |
  >|




I used to run the following analyze every week:
dbms_utility.analyze_schema(...) statement which included SYSTEM schema,
Two weeks back it was changed to
dbms_stats.gather_database_stats();

Is there anyway to de-analyze SYSTEM schema?

Thanks,
-- Babu


-Original Message-
Sent: Thursday, May 09, 2002 11:04 AM
To: 'Janardhana Babu Donga'


Babu - Among the suggestions you have received, the one that seems to have
the most merit is whether any of your system tables have been analyzed. Were
I in your position, I would check that out.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Thursday, May 09, 2002 12:51 PM
To: '[EMAIL PROTECTED]'
Cc: DENNIS WILLIAMS


Dennis,

It is happenning for any table in the database. I have just created a new
table:
create table x1 (col1  number) tablespace data_ts, inserted one row and run:
select * from dba_extents where segment_name = 'X1'; It is still sitting
there. The response to any other dictionary view is spontaneous except this
dba_extents, which is confusing me.
I issued: select * from dba_objects where ..., select * from dba_segments
where  etc. They are all responding fine.

There are not many users at the moment. System tablespace is 500M size and
300MB is free. DB is 40Gig in size. It is on HP_UX 11.0 and Oracle 8.1.7.2.0
(64bit) database.

Any other ideas?

Thanks,
-- Babu

-Original Message-
Sent: Thursday, May 09, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L


Babu - Wow, never had that one. Of course, I don't use that table much. How
many rows are in this table? Mine has 12,937 rows, and is kinda slow to
respond compared to the other system tables. Do you have a test database to
compare with? Is it possible that your system tablespace has become badly
fragmented? What is your system load, is it possibly a contention problem of
some type. Why not run STATSPACK (or utlbstat) while you do this query and
see what is being waited for? Why don't I shut up and send this?
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Thursday, May 09, 2002 12:58 PM
To: Multiple recipients of list ORACLE-L


Dear List,

When I query dba_extents , Iam getting response after half an hour to 40
minutes, but when I query any other dictionary view it is spontaneous.
Everything else is fine in the database and there are no problems, except
the above problem. Iam not getting any clue how to fix this. Iam thinking of
running catalog.sql and catproc.sql as a last resort. Iam not sure it would
fix the problem. Please let me know if there is any other way to fix this
problem.

Thanks,
Babu


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janardhana Babu Donga
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send th

Re: Cloning Question

[Rachel_Carmichael]

In some versions of Oracle, you need a SECOND rollback segment created in the
SYSTEM tablespace in order to create another tablespace.  It can't hurt to add
it (you can drop it immediately after you get the rollback tablespace created)
but it could be the solution.

Since you didn't include the version of the database you are working with, we
have to be somewhat generic in our answers

Rachel



|+--->
||   |
||   |
||  srcdco@ritvax|
||  .rit.edu |
||   |
||  05/09/2002   |
||  02:28 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: Cloning Question|
  >|




I thought there was a rollback segment in system that would be there.  It should
be enough to get things started.  After all, there isn't a separate one when you
create a database, until you create it.

DENNIS WILLIAMS wrote:

> Scott - I think that Rachel offered better advice, and I'm sure you've read
> her note by now. I have some experience with missing files when cloning a
> database :-), but I haven't had the rollback tablespace missing, and that
> would be different. One thought would be to create an extra rollback
> tablespace on production, so that when you perform the clone operation,
> Oracle still has some rollback segments available.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
> -Original Message-
> Sent: Thursday, May 09, 2002 12:03 PM
> To: Multiple recipients of list ORACLE-L
>
> Dennis,
> No.  I did a create controlfile set database...  I commented out the
> rollback_segments line in the init.ora and removed the reference to the file
> in
> the create controlfile statement.  With no reference (that I know of) to the
> rollback segments or tablespace, I thought that the database should have
> come
> up, then I could create a new one.  I must have missed something, but I
> don't
> know what that was.
>
> DENNIS WILLIAMS wrote:
>
> > Scott - Let's see if I understand specifically what you did. You ran the
> > CREATE DATABASE script that was generated from your production system.
> Now,
> > since the datafile for rollback tablespace isn't there, Oracle will come
> up
> > without it. Once your new instance was up, then you did a DROP TABLESPACE
> on
> > the rollback tablespace, followed by a CREATE TABLESPACE. I've done that
> > scenario quite a few times on Oracle 8.1.6, just yesterday, as a matter of
> > fact (that was a temp tablespace). If those weren't your steps, then
> provide
> > more details.
> > Dennis Williams
> > DBA
> > Lifetouch, Inc.
> > [EMAIL PROTECTED]
> >
> > -Original Message-
> > Sent: Thursday, May 09, 2002 8:48 AM
> > To: Multiple recipients of list ORACLE-L
> >
> > I spent a long time trying to clone our production data warehouse
> > into test, to refresh it.  I do this frequently, as both a way to
> > refresh test instances and to test the backups.  After fighting through
> > 2 bad backups, I finally got a good one.  However, the problem I had was
> > that the production rollback tablespace is larger than the disk on test.
> >
> > My thought was that I could restore everything except the rollback
> > tablespace, which is not the way I usually do it.  I commented out the
> > rollback_segments line in the init.ora and created a script that would
> > create a new rollback tablespace and rollback segments, smaller than
> > those in production.  This didn't work, even though Oracle support said
> > it should have.  When I did the clone, the database would not open.  I
> > kept getting the following error:
> >
> > ORA-00600: internal error code, arguments: [25012], [1], [2], [], [],
> > [], [], []
> >
> > Does anyone know what I was doing wrong?  I can't figure it out, and
> > Oracle support couldn't figure it out, either.  I was able to get the
> > database up by getting more disk added, so that there was enough space
> > to restore the rollback tablespace file.
> >
> > Thank you.
> >
> > --
> > Scott Canaan ([EMAIL PROTECTED])
> > (585) 475-7886
> > "Life is like a sewer, what you get out of it depends on what you put
> > into it" - Tom Lehrer
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Scott Canaan
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> > San Diego, California-- Public Internet access / Mailing Lists
> > 

Re: DBA_EXTENTS problem

[Rachel_Carmichael]

that's interesting... since the data dictionary is NOT analyzed, setting
optimizer_mode=choose would force the query against dba_extents to RULE which it
what it is supposed to be doing anyway, Hm. The question now is, what is the
optimizer_mode set to when the problem happens? Did any of the data dictionary
tables get accidentally analyzed? And -- how many extents are in use in the
database? Could it just be a symptom of missized tables and indexes so that the
number of extents is way high?

Although Oracle is *supposed* to allow unlimited extents, in practice anything
higher than 4096 extents in an object (at least in 8i) tends to slow things
down.




|+--->
||   |
||   |
||  jack_silvey@y|
||  ahoo.com |
||   |
||  05/09/2002   |
||  02:18 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: DBA_EXTENTS problem |
  >|




Babu,

We had a similiar problem, and setting optimizer_mode
= choose in our session solved it. Something to do
with optimizer and DD access. Give that a try. I had
the same problem with DBA_INDEXES and that fixed it.

hth,

Jack


--- Janardhana Babu Donga <[EMAIL PROTECTED]> wrote:
> Dear List,
>
> When I query dba_extents , Iam getting response
> after half an hour to 40
> minutes, but when I query any other dictionary view
> it is spontaneous.
> Everything else is fine in the database and there
> are no problems, except
> the above problem. Iam not getting any clue how to
> fix this. Iam thinking of
> running catalog.sql and catproc.sql as a last
> resort. Iam not sure it would
> fix the problem. Please let me know if there is any
> other way to fix this
> problem.
>
> Thanks,
> Babu
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Janardhana Babu Donga
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).


__
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Datawarehousing help

[Rachel_Carmichael]

it stops me. I refuse to deliberately look like an idiot... I do enough damage
inadvertently




|+--->
||   |
||   |
||  [EMAIL PROTECTED]|
||  ms.osd.mil   |
||   |
||  05/07/2002 12:58 PM  |
||  Please respond to|
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




Come on, Rachel!  "Zero Experence" has never stopped anyone from publishing.
Just look at academia and MCSE study guides...

Scott Shafer
San Antonio, TX
210-581-6217


> -Original Message-
> From: [EMAIL PROTECTED]
> [SMTP:[EMAIL PROTECTED]]
> Sent: Tuesday, May 07, 2002 8:19 AM
> To:   Multiple recipients of list ORACLE-L
> Subject:   RE: Datawarehousing help
>
> you people are soo funny.  Writing a book takes time, hard work and
> more
> energy than I care to commit to the project.. especially on a subject with
> which
> I have zero experience
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Good DBA vs. Bad DBA

[Rachel_Carmichael]

HEY! I resent that. ask the last person who worked for me, I think I was a
pretty good manager!



|+--->
||   |
||   |
||  bthater2@nets|
||  cape.net |
||   |
||  05/07/2002   |
||  09:08 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: Good DBA vs. Bad DBA|
  >|




[EMAIL PROTECTED] wrote:

> Dare I ask... do you have a Good Manager vs. Bad Manager list?


you mean there is such a thing as a good manager?  why don't people tell
me these things?;-)



--
--
Bill "Shrek" Thater  ORACLE DBA
 [EMAIL PROTECTED]

You gotta program like you don't need the money,
You gotta compile like you'll never get hurt,
You gotta run like there's nobody watching,
It's gotta come from the heart if you want it to work.

Never violate the Prime Directory!  C:\




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: bill thater
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Datawarehousing help

[Rachel_Carmichael]

you people are soo funny.  Writing a book takes time, hard work and more
energy than I care to commit to the project.. especially on a subject with which
I have zero experience




|+--->
||   |
||   |
||  ksmith2@myfir|
||  stlink.net   |
||   |
||  05/06/2002   |
||  10:38 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




Oh, good idea!  When's it being published Rachel?

-Original Message-
Chris
Sent: Monday, May 06, 2002 1:55 PM
To: Multiple recipients of list ORACLE-L


Cool!!  Here comes Oracle Data Warehousing 101...

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Monday, May 06, 2002 4:18 PM
To: Multiple recipients of list ORACLE-L




Right now I'm collecting information.. I don't KNOW what this will be..
other
than a "learning experience" of course.

That which does not kill us makes us strong, right?

rachel, anticipating great strength




|+--->
||   |
||   |
||  Jared.Still@r|
||  adisys.com   |
||   |
||  05/06/2002   |
||  02:23 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




A DW is not simply a collection of data marts.

A DW may be a true 'warehouse' of enterprise data from which
DM may be built.

Extracts go to the DW, DW is used to build DM.

A DW may in fact very much resemble an OLTP database, with
a temporal component thrown in to track changes to data over time.

Users are not (generally) allowed acces to the DW.

This is a full blown DW architecture though, and you may only
wish to start with some DM to get your feet wet, or maybe that's
all that is actually needed.

Jared






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/06/2002 06:53 AM
Please respond to ORACLE-L


To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Datawarehousing help




Dennis,

Forgetting about normalization won't be a problem, I've always been more
practical than "by the book". As for amounts of data being collected, I
can see
them wanting data aggregated hourly.

I greatly doubt the tech people will allow adhoc queries, they seem to "do
things right" here. What will happen is that they will be contacted by
marketing
with an "I need this new report NOW" request, but tech will generate it.
But
*my* problem is that the data warehouse will supposedly be only a small
part of
what I'm responsible for, I don't think they understand the scope of what
they
are asking for, as yet. They will, I'll make sure of it.

Right now, as this is a new internal group, I'm still collecting
information on
which databases I will be responsible for. Then I just have to remember
that
when I set deadliines, I am prone to underestimation. :)

Rachel


|+--->
||   |
||   |
||  DWILLIAMS@lif|
||  etouch.com   |
||   |
||  05/03/2002   |
||  08:48 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




Rachel - I always find it helpful to understand something if I know the
origins. I worked with SAS several years ago. At that time it was a
statistical analysis package. A scientist or engineer could load a set of
test data into 

RE: Datawarehousing help

[Rachel_Carmichael]

won't be me... why don't YOU write one and then talk to me about the joys of
authorship (says the woman going blind looking a page proofs that are totally
messed up)




|+--->
||   |
||   |
||  cgrabowy@fcg.|
||  com  |
||   |
||  05/06/2002   |
||  06:13 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




Someone's got to pick up Marlene's slack...

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Monday, May 06, 2002 5:44 PM
To: Multiple recipients of list ORACLE-L




nuh uh, for two reasons, the first and foremost being, there already IS one

the second is that I have no plans to write any new books



|+--->
||   |
||   |
||  cgrabowy@fcg.|
||  com  |
||   |
||  05/06/2002   |
||  04:55 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




Cool!!  Here comes Oracle Data Warehousing 101...

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Monday, May 06, 2002 4:18 PM
To: Multiple recipients of list ORACLE-L




Right now I'm collecting information.. I don't KNOW what this will be..
other
than a "learning experience" of course.

That which does not kill us makes us strong, right?

rachel, anticipating great strength




|+--->
||   |
||   |
||  Jared.Still@r|
||  adisys.com   |
||   |
||  05/06/2002   |
||  02:23 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




A DW is not simply a collection of data marts.

A DW may be a true 'warehouse' of enterprise data from which
DM may be built.

Extracts go to the DW, DW is used to build DM.

A DW may in fact very much resemble an OLTP database, with
a temporal component thrown in to track changes to data over time.

Users are not (generally) allowed acces to the DW.

This is a full blown DW architecture though, and you may only
wish to start with some DM to get your feet wet, or maybe that's
all that is actually needed.

Jared






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/06/2002 06:53 AM
Please respond to ORACLE-L


To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Datawarehousing help




Dennis,

Forgetting about normalization won't be a problem, I've always been more
practical than "by the book". As for amounts of data being collected, I
can see
them wanting data aggregated hourly.

I greatly doubt the tech people will allow adhoc queries, they seem to "do
things right" here. What will happen is that they will be contacted by
marketing
with an "I need this new report NOW" request, but tech will generate it.
But
*my* problem is that the data warehouse will supposedly be only a small
part of
what I'm responsible for, I don't think they understand the scope of what
they
are asking for, as yet. They will, I'll make sure of it.

Right now, as this is a new internal group, I'm still collecting
information on
which databases I will be responsible for. Then I just have to remember
that
when I set deadliines, I am prone to underestimation. :)

Rachel


|+--->
||   |
|| 

Re: 9i Automatic UNDO bugs

[Rachel_Carmichael]

I know Joe Testa blew up his DB with it... it was in the initial release and was
supposedly fixed in the patchsets



|+--->
||   |
||   |
||  optimaldba@ya|
||  hoo.com  |
||   |
||  05/06/2002   |
||  05:57 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: 9i Automatic UNDO bugs  |
  >|




I have "heard" of 9i AMU bugs that cause database
outages, but nothing shows up on metalink. Does anyone
have any direct experience with 9i AMU causing
database corruption and loss? If so, what platform was
this on and what bug was identified as the culprit?

Daniel W. Fink

__
Do You Yahoo!?
Yahoo! Health - your guide to health and wellness
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Daniel Fink
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Datawarehousing help

[Rachel_Carmichael]

nuh uh, for two reasons, the first and foremost being, there already IS one

the second is that I have no plans to write any new books



|+--->
||   |
||   |
||  cgrabowy@fcg.|
||  com  |
||   |
||  05/06/2002   |
||  04:55 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




Cool!!  Here comes Oracle Data Warehousing 101...

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Monday, May 06, 2002 4:18 PM
To: Multiple recipients of list ORACLE-L




Right now I'm collecting information.. I don't KNOW what this will be..
other
than a "learning experience" of course.

That which does not kill us makes us strong, right?

rachel, anticipating great strength




|+--->
||   |
||   |
||  Jared.Still@r|
||  adisys.com   |
||   |
||  05/06/2002   |
||  02:23 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




A DW is not simply a collection of data marts.

A DW may be a true 'warehouse' of enterprise data from which
DM may be built.

Extracts go to the DW, DW is used to build DM.

A DW may in fact very much resemble an OLTP database, with
a temporal component thrown in to track changes to data over time.

Users are not (generally) allowed acces to the DW.

This is a full blown DW architecture though, and you may only
wish to start with some DM to get your feet wet, or maybe that's
all that is actually needed.

Jared






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/06/2002 06:53 AM
Please respond to ORACLE-L


To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Datawarehousing help




Dennis,

Forgetting about normalization won't be a problem, I've always been more
practical than "by the book". As for amounts of data being collected, I
can see
them wanting data aggregated hourly.

I greatly doubt the tech people will allow adhoc queries, they seem to "do
things right" here. What will happen is that they will be contacted by
marketing
with an "I need this new report NOW" request, but tech will generate it.
But
*my* problem is that the data warehouse will supposedly be only a small
part of
what I'm responsible for, I don't think they understand the scope of what
they
are asking for, as yet. They will, I'll make sure of it.

Right now, as this is a new internal group, I'm still collecting
information on
which databases I will be responsible for. Then I just have to remember
that
when I set deadliines, I am prone to underestimation. :)

Rachel


|+--->
||   |
||   |
||  DWILLIAMS@lif|
||  etouch.com   |
||   |
||  05/03/2002   |
||  08:48 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




Rachel - I always find it helpful to understand something if I know the
origins. I worked with SAS several years ago. At that time it was a
statistical analysis package. A scientist or engineer could load a set of
test data into it and perform various arithmetic and statistical analyses.
Today most of that can be done with Oracle or MS Excel. My point is that I
would expect it to be heavily biased toward mathematical capabilities.
Lik

RE: Datawarehousing help

[Rachel_Carmichael]

I've always been a cynic about storage -- too much is never enough.




|+-->
||  |
||  |
||  JayMiller@tdwate|
||  rhouse.com  |
||  |
||  05/06/2002 04:43|
||  PM  |
||  Please respond  |
||  to ORACLE-L |
||  |
|+-->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




Just wanted to reiterate the grain recommendation.  The growth rate of our
data warehouse increased app. 20x when the business side changed their mind
from monthly to daily on our largest fact table.  They did this one week
after we got the monthly table into production as per their original
requirements.
Had to redo all the tablespace structures to make it easier save historical
data to tape and drop it from the database.  We now have 13 tablespaces each
of which will be holding one month's worth of data.

-Original Message-
Sent: Friday, May 03, 2002 10:18 PM
To: Multiple recipients of list ORACLE-L


Excellent dude.

-Original Message-
WILLIAMS
Sent: Friday, May 03, 2002 5:48 PM
To: Multiple recipients of list ORACLE-L

 Ralph Kimball warns that often people
will get the grain wrong. They will size it for data summarized at the
weekly level, then after it is built they will realize that isn't going to
cut it and need a daily level. You must start almost from scratch and get 7
times the disk capacity. That is the fun side of being a DW DBA. Your
cynical instincts will still serve you well, just get them away from
normalization and worry about getting the grain right.
   Okay, I've rambled along here too long. Hope that gets you off on
the right foot.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Miller, Jay
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Datawarehousing help

[Rachel_Carmichael]

Right now I'm collecting information.. I don't KNOW what this will be.. other
than a "learning experience" of course.

That which does not kill us makes us strong, right?

rachel, anticipating great strength




|+--->
||   |
||   |
||  Jared.Still@r|
||  adisys.com   |
||   |
||  05/06/2002   |
||  02:23 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




A DW is not simply a collection of data marts.

A DW may be a true 'warehouse' of enterprise data from which
DM may be built.

Extracts go to the DW, DW is used to build DM.

A DW may in fact very much resemble an OLTP database, with
a temporal component thrown in to track changes to data over time.

Users are not (generally) allowed acces to the DW.

This is a full blown DW architecture though, and you may only
wish to start with some DM to get your feet wet, or maybe that's
all that is actually needed.

Jared






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/06/2002 06:53 AM
Please respond to ORACLE-L


To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: Datawarehousing help




Dennis,

Forgetting about normalization won't be a problem, I've always been more
practical than "by the book". As for amounts of data being collected, I
can see
them wanting data aggregated hourly.

I greatly doubt the tech people will allow adhoc queries, they seem to "do
things right" here. What will happen is that they will be contacted by
marketing
with an "I need this new report NOW" request, but tech will generate it.
But
*my* problem is that the data warehouse will supposedly be only a small
part of
what I'm responsible for, I don't think they understand the scope of what
they
are asking for, as yet. They will, I'll make sure of it.

Right now, as this is a new internal group, I'm still collecting
information on
which databases I will be responsible for. Then I just have to remember
that
when I set deadliines, I am prone to underestimation. :)

Rachel


|+--->
||   |
||   |
||  DWILLIAMS@lif|
||  etouch.com   |
||   |
||  05/03/2002   |
||  08:48 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




Rachel - I always find it helpful to understand something if I know the
origins. I worked with SAS several years ago. At that time it was a
statistical analysis package. A scientist or engineer could load a set of
test data into it and perform various arithmetic and statistical analyses.
Today most of that can be done with Oracle or MS Excel. My point is that I
would expect it to be heavily biased toward mathematical capabilities.
Like
Data Mining, which is all statistics. Learn what that term means.
   To learn Data Warehousing, I would encourage you to just do
some
"Googling" and find good tutorials. An excellent newslist is dwlist.
Instructions:

For help with list commands, send a message
to  with the
word "help" in the body of the message.

The magazine http://www.intelligententerprise.com/ has some excellent
information. I would search for "Ralph Kimball". He is one of the leading
figures in the DW arena. Look for some of his earliest columns on the
magazine site. He also answers questions on dwlist from time to time.

The main change you need yourself is to forget normalization. DBAs that
can't get past that point don't last long in the DW field. In the early
days
the DW people would patiently explain the reasons to a DBA, but today
there
are enough DBAs that have made the leap that a hard-headed normalization
bigot just isn't tolerated. It is much easier to just ask for a
replacement
DBA.
   The reason normalization isn't adhered to in DW is that users
will
be creating their own queri

Re: Datawarehousing help

[Rachel_Carmichael]

Yechiel,

have you used their tools? We are trying to decide whether or not to use them,
so if anyone has had recent experience with them, I'd appreciate your thoughts
on ease of use, understandability, quality of the product, etc

Thanks

Rachel


|+--->
||   |
||   |
||  adaryechiel@h|
||  otmail.com   |
||   |
||  05/05/2002   |
||  07:23 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Re: Datawarehousing help|
  >|




Hello Dennis

SAS has progressed a little in the last years and now offer a complete
DW solution, including ETL tools.

You can use their tools also to populate and query oracle.

Yechiel Adar
Mehish

- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Saturday, May 04, 2002 2:48 AM


> Rachel - I always find it helpful to understand something if I know the
> origins. I worked with SAS several years ago. At that time it was a
> statistical analysis package. A scientist or engineer could load a set of
> test data into it and perform various arithmetic and statistical analyses.
> Today most of that can be done with Oracle or MS Excel. My point is that I
> would expect it to be heavily biased toward mathematical capabilities.
Like
> Data Mining, which is all statistics. Learn what that term means.
> To learn Data Warehousing, I would encourage you to just do some
> "Googling" and find good tutorials. An excellent newslist is dwlist.
> Instructions:
>
> For help with list commands, send a message
> to  with the
> word "help" in the body of the message.
>
> The magazine http://www.intelligententerprise.com/ has some excellent
> information. I would search for "Ralph Kimball". He is one of the leading
> figures in the DW arena. Look for some of his earliest columns on the
> magazine site. He also answers questions on dwlist from time to time.
>
> The main change you need yourself is to forget normalization. DBAs that
> can't get past that point don't last long in the DW field. In the early
days
> the DW people would patiently explain the reasons to a DBA, but today
there
> are enough DBAs that have made the leap that a hard-headed normalization
> bigot just isn't tolerated. It is much easier to just ask for a
replacement
> DBA.
> The reason normalization isn't adhered to in DW is that users will
> be creating their own queries and they can't understand 10-table joins
with
> outer joins, etc. A DW is usually loaded and then queried. Our DW is
loaded
> each weekend and then queried all week. So a DW is deliberately
denormalized
> and contains redundant data for ease of use.
> OLTP databases have no concept of "time". A DW is all about time. To
> reconstruct what the situation is at various points of time, the DW has
> loads of historical data. For example, marketing people need to be able to
> reconstruct the amount of business they did with a customer over a period
of
> time last year and compare it with the same period this year.
> So between denormalization and tons of detailed historical data, DWs
> are normally BIG! Fortunately they are usually read-only.
> For Oracle, you want Enterprise Edition with the partitioning
> option. And study Oracle Materialized Views.
> In schema, a DW is usually a central fact table and 4-6 dimension
> tables. Less than 4 dimensions and you don't need a DW. More than 6 and
> marketing people can't understand the model. Normally the fact table is
much
> larger than the others, but not always. One of Wal-Mart's dimension tables
> is each person in the U.S. Just size each of those tables, and you've got
> your size. Growth is easy to predict. Ralph Kimball warns that often
people
> will get the grain wrong. They will size it for data summarized at the
> weekly level, then after it is built they will realize that isn't going to
> cut it and need a daily level. You must start almost from scratch and get
7
> times the disk capacity. That is the fun side of being a DW DBA. Your
> cynical instincts will still serve you well, just get them away from
> normalization and worry about getting the grain right.
> Okay, I've rambled along here too long. Hope that gets you off on
> the right foot.
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Friday, May 03, 2002 5:08 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
>
> Okay, my background is OLTP, but we are looking at a 

RE: Datawarehousing help

[Rachel_Carmichael]

Dennis,

Forgetting about normalization won't be a problem, I've always been more
practical than "by the book". As for amounts of data being collected, I can see
them wanting data aggregated hourly.

I greatly doubt the tech people will allow adhoc queries, they seem to "do
things right" here. What will happen is that they will be contacted by marketing
with an "I need this new report NOW" request, but tech will generate it. But
*my* problem is that the data warehouse will supposedly be only a small part of
what I'm responsible for, I don't think they understand the scope of what they
are asking for, as yet. They will, I'll make sure of it.

Right now, as this is a new internal group, I'm still collecting information on
which databases I will be responsible for. Then I just have to remember that
when I set deadliines, I am prone to underestimation. :)

Rachel


|+--->
||   |
||   |
||  DWILLIAMS@lif|
||  etouch.com   |
||   |
||  05/03/2002   |
||  08:48 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: Datawarehousing help|
  >|




Rachel - I always find it helpful to understand something if I know the
origins. I worked with SAS several years ago. At that time it was a
statistical analysis package. A scientist or engineer could load a set of
test data into it and perform various arithmetic and statistical analyses.
Today most of that can be done with Oracle or MS Excel. My point is that I
would expect it to be heavily biased toward mathematical capabilities. Like
Data Mining, which is all statistics. Learn what that term means.
   To learn Data Warehousing, I would encourage you to just do some
"Googling" and find good tutorials. An excellent newslist is dwlist.
Instructions:

For help with list commands, send a message
to  with the
word "help" in the body of the message.

The magazine http://www.intelligententerprise.com/ has some excellent
information. I would search for "Ralph Kimball". He is one of the leading
figures in the DW arena. Look for some of his earliest columns on the
magazine site. He also answers questions on dwlist from time to time.

The main change you need yourself is to forget normalization. DBAs that
can't get past that point don't last long in the DW field. In the early days
the DW people would patiently explain the reasons to a DBA, but today there
are enough DBAs that have made the leap that a hard-headed normalization
bigot just isn't tolerated. It is much easier to just ask for a replacement
DBA.
   The reason normalization isn't adhered to in DW is that users will
be creating their own queries and they can't understand 10-table joins with
outer joins, etc. A DW is usually loaded and then queried. Our DW is loaded
each weekend and then queried all week. So a DW is deliberately denormalized
and contains redundant data for ease of use.
   OLTP databases have no concept of "time". A DW is all about time. To
reconstruct what the situation is at various points of time, the DW has
loads of historical data. For example, marketing people need to be able to
reconstruct the amount of business they did with a customer over a period of
time last year and compare it with the same period this year.
   So between denormalization and tons of detailed historical data, DWs
are normally BIG! Fortunately they are usually read-only.
   For Oracle, you want Enterprise Edition with the partitioning
option. And study Oracle Materialized Views.
   In schema, a DW is usually a central fact table and 4-6 dimension
tables. Less than 4 dimensions and you don't need a DW. More than 6 and
marketing people can't understand the model. Normally the fact table is much
larger than the others, but not always. One of Wal-Mart's dimension tables
is each person in the U.S. Just size each of those tables, and you've got
your size. Growth is easy to predict. Ralph Kimball warns that often people
will get the grain wrong. They will size it for data summarized at the
weekly level, then after it is built they will realize that isn't going to
cut it and need a daily level. You must start almost from scratch and get 7
times the disk capacity. That is the fun side of being a DW DBA. Your
cynical instincts will still serve you well, just get them away from
normalization and worry about getting the grain right.
   Okay, I've rambled

Datawarehousing help

[Rachel_Carmichael]

Okay, my background is OLTP, but we are looking at a data warehousing project
here

any and all help appreciated! Specifically:

1) does anyone have any experience with a product called "SAS Datawarehousing
Administrator" (or SAS)?
2) how do I go about doing rough estimates of sizing needs, assuming I will get
rough numbers of information being collected, growth rates, length of history to
keep, etc.

help?

Rachel


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: DB Size

[Rachel_Carmichael]

you really might want to read the manuals -- specifically the reference manual
on dba_data_files

select sum(bytes) from dba_data_files will tell you the total size (in bytes) of
all the datafiles in your database. There will also be space used by the control
files, redo log files, archived log files and binaries etc.




|+--->
||   |
||   |
||  [EMAIL PROTECTED]|
||  o.ug |
||   |
||  05/03/2002   |
||  10:53 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: DB Size |
  >|




Hi all,
How could one collect data from an Oracle Server to respond to the question:
"How big is (what is the size of your) Database ?"

Thaking you,

---
CSW
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Simon Waibale
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Licences for testing

[Rachel_Carmichael]

You could talk to your sales rep about short-term licensing, or you could just
do it and hope they don't discover it. I've found that "temporary servers" have
a tendency to become permanent though.


|+--->
||   |
||   |
||  Sean.ONeill@o|
||  rganon.ie|
||   |
||  05/03/2002   |
||  08:08 AM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Licences for testing|
  >|




We're planning to set-up some "test" servers here and I'm wondering what the
"practice" is out there regarding licences for same.  This is only for short
term testing and to "try out" various DR scenarios etc.  I'd guess there's
an "Oracle" view which would be if you install on server you pay a licence,
(correct?).  Is there some workaround for this?.  All creative ideas welcome
:)

-
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode]


This message, including attached files, may contain confidential
information and is intended only for the use by the individual
and/or the entity to which it is addressed. Any unauthorized use,
dissemination of, or copying of the information contained herein is
not allowed and may lead to irreparable harm and damage for which
you may be held liable. If you receive this message in error or if
it is intended for someone else please notify the sender by
returning this e-mail immediately and delete the message.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: O'Neill, Sean
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Advice on going about a minor upgrade.

[Rachel_Carmichael]

Option 4:

Install 8.1.7
shutdown the database
change ORACLE_HOME etc etc to point to the 8.1.7 ORACLE_HOME
startup the database

You shouldn't have to rebuild the database when moving to a minor release

Rachel



|+--->
||   |
||   |
||  [EMAIL PROTECTED]|
||  ate.nh.us|
||   |
||  05/02/2002   |
||  12:45 PM |
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: Advice on going about a minor   |
  |   upgrade. |
  >|




Greetings fellow-DBAs,

We have a web application which exists on a Windows/NT 4 box running Oracle
Standard Server 8.1.6.  A new application to be added to that platform needs
Oracle 8.1.7 (They've even specified needing Patch Set  8.1.7.2.1 and ODBC
driver 8.01.74.00).

I have just ordered the Oracle 8.1.7 Server for Windows NT and would like to
ask what you think in terms of direction I should take for the upgrade.

Here is my plan:
(1) Have the NT System Administrator back up the entire system
(2) Take a full export of the existing database

Either Option1:
(3a) Install Oracle 8.1.7
(4a) Build the database from scratch
(5a) Import the full export from #2

Or Option 2:
(3b) Deinstall Oracle 8.1.6
(4b) Install Oracle 8.1.7
(5b) Build the database from scratch
(6b) Import the full export from #2

Or Option 3:
(3c) Install Oracle 8.1.7
(4c) Migrate the existing database.

Any pros, cons, or suggestions with respect to what I've laid out here?

As always, thank you very much in advance for all the sound advice you've
historically offered.

Jim Damiano


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: James Damiano
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).