date:20031021

Quoting Pete Sharman's signature from my memory:
Controlling developers is harder then herding cats.
Tell your developers that there exists a thing called CBO which has
something called hints that can alleviate the need for such ridiculous
WHERE clauses.
On 2003.10.21 07:44, Hately, Mike (LogicaCMG) wrote:
Morning folks,

the developers here are looking at a view with a where clause which
specifies :
WHERE 1=1 AND
 ... AND
 ... etc.
I'd seen this used before as a way of tweaking the RBO into certain
behaviours but it was years ago and my recollection is very hazy.
The only explanation I've found so far is :
the 1=1 is in there to avoid doing repetitive index scans for single
rowids, when the app knows the result set is going to be manipulated
rowid's for a large subset of the table. I would guess your DUAL/CBO example
had some similar effect.
Is anyone familiar enough with this tweak to explain it ?

Cheers,
Mike
PS Maybe it'll head off some replies if I make it clear that this view
hasn't been generated by code so the 1=1 isn't an accidental artifact It
was custom written and is definitely supposed to have exactly this
structure.







E mail Disclaimer
You agree that you have read and understood this disclaimer and you agree to
be bound by its terms.
The information contained in this e-mail and any files transmitted with it
(if any) are confidential and intended for the addressee only.  If you have
received this  e-mail in error please notify the originator.
This e-mail and any attachments have been scanned for certain viruses prior
to sending but CE Electric UK Funding Company nor any of its associated
companies from whom this e-mail originates shall be liable for any losses as
a result of any viruses being passed on.
No warranty of any kind is given in respect of any information contained in
this   e-mail and you should be aware that that it might be incomplete, out
of date or incorrect. It is therefore essential that you verify all such
information with us before placing any reliance upon it.
CE Electric UK Funding Company
Lloyds Court
78 Grey Street
Newcastle upon Tyne
NE1 6AF
Registered in England and Wales: Number 3476201


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hately, Mike (LogicaCMG)
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

_DB_FILE_DIRECT_IO_COUNT

2003-10-21 Thread Robertson Lee - lerobe

Hi,

9.2.0.3 AIX 5L

Anyone used this in 9i ??

Can you set this as you would a normal parameter (ie. as I did in 8i with
undocumented parameters).

Sorry if this is a stupid question but I am still getting my head around
this 9i stuff.

Plus don't worry, this is not Production I am playing but a test instance. I
thought I would ask first as I would rather not recreate this.

Cheers

Lee







**
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Cache a table

Never. Altering the table to cache does not gurantee that it will be always
be available in the cache. It simply means the table will be placed in the
Least recently used end of the LRU list and it will age away as time goes
by, just like any other table.

A better approach is to use KEEP pool and place teh table (and all other
tables that are accessed frequently) there. This is particualrly true for
datawarehouses wherethe lookup tables or small dimension tables can be
placed in KEEP pool.

Ah, come tho think about it, actually there is one situation where I will
consider the CACHE option, when I restart the instance and want the hit
ratio to look good :)

HTH.

Arup Nanda

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 3:39 AM


 Hi all,

 when you would consider to put a table a cache...

 rgds

 gb

 
 Want to chat instantly with your online friends?  Get the FREE Yahoo!
 Messenger http://mail.messenger.yahoo.co.uk
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: =?iso-8859-1?q?Gunnar=20Berglund?=
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: using temp tables for staging databases?

Mark,

While waiting for Tim, I can offer another situation - in datawarehouses,
where the subsequent updates are not likely to occur. Also, space is a
premium and packing the blocks as densly as populated might be necessary.

I will also add to Tim's response of justifying a smaller PCTUSED. In
addition to the freelist problem he mentioned, there is also a greater
chance of buffer busy waits occuring when a block contains too many rows. In
an OLTP database that is certainly likely to happen - another case for the
default 40 setting for the parameter. In DW, however, the chances of BBW are
low, hence a higher setting may be possible.

HTH.

Arup Nanda

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 5:19 AM


 Tim,

 Can you sum up a few situations when the need *has* arisen to change these
 values?

 Cheers

 Mark



 -Original Message-
 Tim Gorman
 Sent: 21 October 2003 06:09
 To: Multiple recipients of list ORACLE-L


 Unless you typo'd, there are some serious problems here...

 Setting PCTFREE to 99 is not likely to pack in the blocks.  Rather the
 opposite;  you are instead leaving blocks 99% empty.  Quite a bit of
wasted
 I/O in performing a FULL table scan here...  :-)

 Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value
 greater than 70 or 80 or so, just as a rule of thumb.  Having them sum to
a
 value near 100 ensures that each insert, delete, or even update will
 potentially cause the block to be removed or reinserted to one of the
 segment's free list.  Think about it:  the width of a single row crossing
 the boundary from off the free list to on the free list.  Better to
 leave a bit of a no man's land between the two values.  The default
 settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings
 that need little manipulation for most situations.



 on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:

  we drop and recreate the temp tables every night. We also use PCTFREE
 PCTUSED
  at 99 and 1 to pack in the blocks and we use very small extent sizes.
then
 we
  analyze with an estimate size of 20 percent which is quite fast.
 
  All of them are used for full table scans and do not have indexes. Ive
 found
  that a 'create table as' is MUCH faster than inserting into global
 temporary
  tables when you do not have to worry about latch contention(ie 1-3 users
  logged in at a time).
 
  anyone else notice this? Seems to go against conventional wisdom which
 says
  never use them. So I want to make sure Im not missing something.
 
  From: Tim Gorman [EMAIL PROTECTED]
  Date: 2003/10/20 Mon AM 10:19:33 EDT
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Subject: Re: using temp tables for staging databases?
 
  All the time.  Oracle Apps's open interfaces are built this way, for
  example.
 
  However, the guys here covered their bases by specifying smaller
  temporary tables, as if they could prevent them from becoming large.
I
  suppose they might feel that they indemnify themselves if the tables
 should
  ever become large?
 
  As with OraApps open interface tables, it is when a large volume of
 data
  is pushed through that the trouble starts.  The high-water marks on
all
  the tables are pushed to a high level, thereafter causing full table
 scans
  on the interface/temporary tables to run slowly.  The only way to bring
 the
  HWM back down is quiesce the interface/app and then truncate the
tables.
 
 
 
  on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:
 
  This is for non-transactional data load instances. The guys here sware
 that
  by
  using smaller temporary tables(not global temp tables) they can
increase
 the
  speed of the data loads.
 
  Not worried about latch contention because its just for bulk loads. I
 know
  this bad in transactional instances. Has anyone used these in
  non-transactional data load instances?
 
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Tim Gorman
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting services
  -
  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.net
 --
 Author: Tim Gorman
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE

RE: Cache a table

2003-10-21 Thread Hately, Mike (LogicaCMG)

Hi,

I'm sure you know this but you had some finger trouble there. Just to
clarify it for others; Tables with the CACHE option are placed at the Most
Recently Used end of the LRU list.

Cheers,
Mike Hately

-Original Message-
Sent: 21 October 2003 12:21
To: Multiple recipients of list ORACLE-L


Never. Altering the table to cache does not gurantee that it will be always
be available in the cache. It simply means the table will be placed in the
Least recently used end of the LRU list and it will age away as time goes
by, just like any other table.

A better approach is to use KEEP pool and place teh table (and all other
tables that are accessed frequently) there. This is particualrly true for
datawarehouses wherethe lookup tables or small dimension tables can be
placed in KEEP pool.

Ah, come tho think about it, actually there is one situation where I will
consider the CACHE option, when I restart the instance and want the hit
ratio to look good :)

HTH.

Arup Nanda

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 3:39 AM


 Hi all,

 when you would consider to put a table a cache...

 rgds

 gb



E mail Disclaimer

You agree that you have read and understood this disclaimer and you agree to be bound 
by its terms.

The information contained in this e-mail and any files transmitted with it (if any) 
are confidential and intended for the addressee only.  If you have received this  
e-mail in error please notify the originator.

This e-mail and any attachments have been scanned for certain viruses prior to sending 
but CE Electric UK Funding Company nor any of its associated companies from whom this 
e-mail originates shall be liable for any losses as a result of any viruses being 
passed on.

No warranty of any kind is given in respect of any information contained in this   
e-mail and you should be aware that that it might be incomplete, out of date or 
incorrect. It is therefore essential that you verify all such information with us 
before placing any reliance upon it.

CE Electric UK Funding Company
Lloyds Court
78 Grey Street
Newcastle upon Tyne
NE1 6AF
Registered in England and Wales: Number 3476201



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hately, Mike (LogicaCMG)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: WHERE 1 = 1 (any info on this)

2003-10-21 Thread Nuno Souto

AFAIK, it has two origins:

1- To make editing text sql scripts easier,
in terms of alignment between the predicates.

2- As a trick to enhance the WHERE clause in
blocks in Forms, very old versions.

Other than that never heard it made the slightest
difference to the CBO.
Cheers
Nuno Souto
[EMAIL PROTECTED]
- Original Message - 
 
 the 1=1 is in there to avoid doing repetitive index scans for single
 rowids, when the app knows the result set is going to be manipulated
 rowid's for a large subset of the table. I would guess your DUAL/CBO example
 had some similar effect.
 
 Is anyone familiar enough with this tweak to explain it ?
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Cache a table



when the table is
1] frequently accessed
2] relatively small

Regards
B S Pradhan



On Tue, 21 Oct 2003 Gunnar Berglund wrote :
Hi all,

when you would consider to put a table a cache...

rgds

gb


Want to chat instantly with your online friends?  Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Gunnar=20Berglund?=
   INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

Data Modelling


Hi All

I have not done data modelling as a major task so far. Would like to know from where 
can I make a good start. I have basic(conceptual) knowledge of ER-Diagram and 
Normalization. But if anybody can give me some case studies/links etc.,

I see the responsibilities would include modelling and coping with Frequent changes of 
the database object structures.

Please Help

Thanks a Lot

Regards
B S Pradhan

RE: Cache a table

2003-10-21 Thread K Gopalakrishnan

Mike:

I guess we are aware there is no concept of LRU or MRU in current
versions of Oracle and I don't think CACHE option will influence the
behavior. With the new algorithm the MFU blocks are already in the hot
end (unless they are read using CR read in that case they will be in
cold end since we set the _db_aging_freeze_cr to TRUE) and we don't
need to cache the blocks explicitely.

You can monitor the behavior of this using the X$BH (espicially  the
last two columns TCH and TIM).




=
Have a nice day !!

Best Regards,
K Gopalakrishnan,
Bangalore, INDIA.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: WHERE 1 = 1 (any info on this)

I agree with Connor that it has zero effect on the optimizer, and that it
most likely was to make it easier to modify the view dynamically.

Either that or the author was some anal-nut-job that liked to see it for
some bizarre reason.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Tuesday, October 21, 2003 8:05 AM
To: Multiple recipients of list ORACLE-L


I'm pretty sure the optimizer can pick up 1=1 anyway
and ignore it as an always-true condition - so you get
no benefit.

The most common cause I've seen for 1=1 is so when
developers are building dynamic where-clause, they
don't need to worry about adding 'where' versus 'and'
to the sql string being constructed.

hth
connor

 --- Hately, Mike (LogicaCMG)
[EMAIL PROTECTED] wrote:  Morning folks,
 
 the developers here are looking at a view with a
 where clause which
 specifies :
 
 WHERE 1=1 AND
  ... AND
  ... etc.
 
 I'd seen this used before as a way of tweaking the
 RBO into certain
 behaviours but it was years ago and my recollection
 is very hazy. 
 The only explanation I've found so far is :
 
 the 1=1 is in there to avoid doing repetitive index
 scans for single
 rowids, when the app knows the result set is going
 to be manipulated
 rowid's for a large subset of the table. I would
 guess your DUAL/CBO example
 had some similar effect.
 
 Is anyone familiar enough with this tweak to explain
 it ?
 
 Cheers,
 Mike
 
 PS Maybe it'll head off some replies if I make it
 clear that this view
 hasn't been generated by code so the 1=1 isn't an
 accidental artifact It
 was custom written and is definitely supposed to
 have exactly this
 structure.
 
 
 
 
 
 
 



 E mail Disclaimer
 
 You agree that you have read and understood this
 disclaimer and you agree to be bound by its terms.
 
 The information contained in this e-mail and any
 files transmitted with it (if any) are confidential
 and intended for the addressee only.  If you have
 received this  e-mail in error please notify the
 originator.
 
 This e-mail and any attachments have been scanned
 for certain viruses prior to sending but CE Electric
 UK Funding Company nor any of its associated
 companies from whom this e-mail originates shall be
 liable for any losses as a result of any viruses
 being passed on.
 
 No warranty of any kind is given in respect of any
 information contained in this   e-mail and you
 should be aware that that it might be incomplete,
 out of date or incorrect. It is therefore essential
 that you verify all such information with us before
 placing any reliance upon it.
 
 CE Electric UK Funding Company
 Lloyds Court
 78 Grey Street
 Newcastle upon Tyne
 NE1 6AF
 Registered in England and Wales: Number 3476201
 



 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net
 -- 
 Author: Hately, Mike (LogicaCMG)
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
 San Diego, California-- Mailing list and web
 hosting services

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

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day


Want to chat instantly with your online friends?  Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services

Re: Data Modelling

ive been told 'data modelling for mere mortals' is a good place to start. 

 From: bhabani s pradhan [EMAIL PROTECTED]
 Date: 2003/10/21 Tue AM 08:54:25 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Data Modelling

Hi All

I have not done data modelling as a major task so far. Would like to know from where can I make a good start. I have basic(conceptual) knowledge of ER-Diagram and Normalization. But if anybody can give me some case studies/links etc.,

I see the responsibilities would include modelling and coping with Frequent changes of the database object structures.

Please Help

Thanks a Lot

Regards
B S Pradhan

Hi All

I have not done data modelling as a major task so far. Would like to know from where 
can I make a good start. I have basic(conceptual) knowledge of ER-Diagram and 
Normalization. But if anybody can give me some case studies/links etc.,

I see the responsibilities would include modelling and coping with Frequent changes of 
the database object structures.

Please Help

Thanks a Lot

Regards
B S Pradhan

RE: WHERE 1 = 1 (any info on this)

2003-10-21 Thread Hately, Mike (LogicaCMG)

OK, here's the interesting thing. At 8.1.6.3 with optimizer_mode=rule the
statement I'm looking at returns very different explain plans depending on
whether the 1=1 clause is included. It's a complicated join and the
explain plans are over 300 lines so it's not easy to see what's happening.
I'll try with a simpler join. 
And yes, I know the RBO is ancient technology these days. =)

Cheers,
Mike

-Original Message-
Sent: 21 October 2003 12:05
To: Multiple recipients of list ORACLE-L


I'm pretty sure the optimizer can pick up 1=1 anyway
and ignore it as an always-true condition - so you get
no benefit.

The most common cause I've seen for 1=1 is so when
developers are building dynamic where-clause, they
don't need to worry about adding 'where' versus 'and'
to the sql string being constructed.

hth
connor

 --- Hately, Mike (LogicaCMG)
[EMAIL PROTECTED] wrote:  Morning folks,
 
 the developers here are looking at a view with a
 where clause which
 specifies :
 
 WHERE 1=1 AND
  ... AND
  ... etc.
 
 I'd seen this used before as a way of tweaking the
 RBO into certain
 behaviours but it was years ago and my recollection
 is very hazy. 
 The only explanation I've found so far is :
 
 the 1=1 is in there to avoid doing repetitive index
 scans for single
 rowids, when the app knows the result set is going
 to be manipulated
 rowid's for a large subset of the table. I would
 guess your DUAL/CBO example
 had some similar effect.
 
 Is anyone familiar enough with this tweak to explain
 it ?
 
 Cheers,
 Mike
 
 PS Maybe it'll head off some replies if I make it
 clear that this view
 hasn't been generated by code so the 1=1 isn't an
 accidental artifact It
 was custom written and is definitely supposed to
 have exactly this
 structure.
 
 
 
 
 
 
 



 E mail Disclaimer
 
 You agree that you have read and understood this
 disclaimer and you agree to be bound by its terms.
 
 The information contained in this e-mail and any
 files transmitted with it (if any) are confidential
 and intended for the addressee only.  If you have
 received this  e-mail in error please notify the
 originator.
 
 This e-mail and any attachments have been scanned
 for certain viruses prior to sending but CE Electric
 UK Funding Company nor any of its associated
 companies from whom this e-mail originates shall be
 liable for any losses as a result of any viruses
 being passed on.
 
 No warranty of any kind is given in respect of any
 information contained in this   e-mail and you
 should be aware that that it might be incomplete,
 out of date or incorrect. It is therefore essential
 that you verify all such information with us before
 placing any reliance upon it.
 
 CE Electric UK Funding Company
 Lloyds Court
 78 Grey Street
 Newcastle upon Tyne
 NE1 6AF
 Registered in England and Wales: Number 3476201
 



 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net
 -- 
 Author: Hately, Mike (LogicaCMG)
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
 San Diego, California-- Mailing list and web
 hosting services

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

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day


Want to chat instantly with your online friends?  Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Hately, Mike (LogicaCMG)
  INET: [EMAIL PROTECTED]

Fat City Network Services--

RE: Cache a table

2003-10-21 Thread Khedr, Waleed

From 9.2 doc:

The LRU Algorithm and Full Table Scans
When the user process is performing a full table scan, it reads the blocks
of the table into buffers and puts them on the LRU end (instead of the MRU
end) of the LRU list. This is because a fully scanned table usually is
needed only briefly, so the blocks should be moved out quickly to leave more
frequently used blocks in the cache.

You can control this default behavior of blocks involved in table scans on a
table-by-table basis. To specify that blocks of the table are to be placed
at the MRU end of the list during a full table scan, use the CACHE clause
when creating or altering a table or cluster. You can specify this behavior
for small lookup tables or large static historical tables to avoid I/O on
subsequent accesses of the table.

Waleed

-Original Message-
Sent: Tuesday, October 21, 2003 8:59 AM
To: Multiple recipients of list ORACLE-L


Mike:

I guess we are aware there is no concept of LRU or MRU in current
versions of Oracle and I don't think CACHE option will influence the
behavior. With the new algorithm the MFU blocks are already in the hot
end (unless they are read using CR read in that case they will be in
cold end since we set the _db_aging_freeze_cr to TRUE) and we don't
need to cache the blocks explicitely.

You can monitor the behavior of this using the X$BH (espicially  the
last two columns TCH and TIM).




=
Have a nice day !!

Best Regards,
K Gopalakrishnan,
Bangalore, INDIA.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: K Gopalakrishnan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: using temp tables for staging databases?

2003-10-21 Thread Tim Gorman

For PCTFREE, setting it to less than the default of 10 is an option for
tables that are INSERT-only where someone wants to pack rows into the
blocks.  The attendent risks are that UPDATEs causing row expansion may be
forced to migrate to another block, thus hurting subsequent query
performance.  Setting PCTFREE greater than the default of 10 is a good idea
if you have experienced row-migration in the past (or expect to experience
it) and you want to leave more free space in each block to accommodate row
expansion from UPDATE statements.

For PCTUSED, I just can't imagine any practical real-world reasons to change
it from the default of 40.  I'm sure someone else can...



on 10/21/03 2:19 AM, Mark Leith at [EMAIL PROTECTED] wrote:

 Tim,
 
 Can you sum up a few situations when the need *has* arisen to change these
 values?
 
 Cheers
 
 Mark
 
 
 
 -Original Message-
 Tim Gorman
 Sent: 21 October 2003 06:09
 To: Multiple recipients of list ORACLE-L
 
 
 Unless you typo'd, there are some serious problems here...
 
 Setting PCTFREE to 99 is not likely to pack in the blocks.  Rather the
 opposite;  you are instead leaving blocks 99% empty.  Quite a bit of wasted
 I/O in performing a FULL table scan here...  :-)
 
 Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value
 greater than 70 or 80 or so, just as a rule of thumb.  Having them sum to a
 value near 100 ensures that each insert, delete, or even update will
 potentially cause the block to be removed or reinserted to one of the
 segment's free list.  Think about it:  the width of a single row crossing
 the boundary from off the free list to on the free list.  Better to
 leave a bit of a no man's land between the two values.  The default
 settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings
 that need little manipulation for most situations.
 
 
 
 on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:
 
 we drop and recreate the temp tables every night. We also use PCTFREE
 PCTUSED
 at 99 and 1 to pack in the blocks and we use very small extent sizes. then
 we
 analyze with an estimate size of 20 percent which is quite fast.
 
 All of them are used for full table scans and do not have indexes. Ive
 found
 that a 'create table as' is MUCH faster than inserting into global
 temporary
 tables when you do not have to worry about latch contention(ie 1-3 users
 logged in at a time).
 
 anyone else notice this? Seems to go against conventional wisdom which
 says
 never use them. So I want to make sure Im not missing something.
 
 From: Tim Gorman [EMAIL PROTECTED]
 Date: 2003/10/20 Mon AM 10:19:33 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Re: using temp tables for staging databases?
 
 All the time.  Oracle Apps's open interfaces are built this way, for
 example.
 
 However, the guys here covered their bases by specifying smaller
 temporary tables, as if they could prevent them from becoming large.  I
 suppose they might feel that they indemnify themselves if the tables
 should
 ever become large?
 
 As with OraApps open interface tables, it is when a large volume of
 data
 is pushed through that the trouble starts.  The high-water marks on all
 the tables are pushed to a high level, thereafter causing full table
 scans
 on the interface/temporary tables to run slowly.  The only way to bring
 the
 HWM back down is quiesce the interface/app and then truncate the tables.
 
 
 
 on 10/20/03 6:39 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:
 
 This is for non-transactional data load instances. The guys here sware
 that
 by
 using smaller temporary tables(not global temp tables) they can increase
 the
 speed of the data loads.
 
 Not worried about latch contention because its just for bulk loads. I
 know
 this bad in transactional instances. Has anyone used these in
 non-transactional data load instances?
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Tim Gorman
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
 --
 Author: Tim Gorman
 INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED]

Re: Re: WHERE 1 = 1 (any info on this)

im assuming this is an old 'trick' from RBO days. Alot of these are still floating 
around. Wasnt there one when you wanted to force a full table scan you would go 

where num_field = 1 + 0;

the + 0 forced the full table scan? I was on a project earlier this year and one guy 
told people to use it. 

The myth about the ordered clause is out there too. Its 99% useless in the CBO. We had 
some poor .Net developers who knew basic sql. These poor guys were spending hours 
ordering 10-15 table joins I tried not to laugh. Wasnt successful. 
 
 From: Nuno Souto [EMAIL PROTECTED]
 Date: 2003/10/21 Tue AM 08:45:02 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Re: WHERE 1 = 1 (any info on this)
 
 AFAIK, it has two origins:
 
 1- To make editing text sql scripts easier,
 in terms of alignment between the predicates.
 
 2- As a trick to enhance the WHERE clause in
 blocks in Forms, very old versions.
 
 Other than that never heard it made the slightest
 difference to the CBO.
 Cheers
 Nuno Souto
 [EMAIL PROTECTED]
 - Original Message - 
  
  the 1=1 is in there to avoid doing repetitive index scans for single
  rowids, when the app knows the result set is going to be manipulated
  rowid's for a large subset of the table. I would guess your DUAL/CBO example
  had some similar effect.
  
  Is anyone familiar enough with this tweak to explain it ?
  
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Nuno Souto
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
-- 
Author: [EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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[2]: WHERE 1 = 1 (any info on this)

2003-10-21 Thread Jonathan Gennick

Tuesday, October 21, 2003, 8:05:10 AM, Conner wrote:
CM The most common cause I've seen for 1=1 is so when
CM developers are building dynamic where-clause, they
CM don't need to worry about adding 'where' versus 'and'
CM to the sql string being constructed.

Here's another use for you to consider. I recently talked to
someone who uses WHERE 1=1 for editing convenience. I asked
him about it once, and he provided the following
explanation:

Just convenience in editing/reading. These queries are
not in a view, they are in a function inside a package,
which returns a REF CURSOR with the results to Java.
Since I use gvim to edit procedures and it has
auto-alignment and auto-repeat, I tend to make all my
predicates and * and use where 1=1 for the only one
that is different. That also means I can cut-and-paste
entire predicate lines in gvim and always have them
conveniently aligned on the same word: and.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]

Join the Oracle-article list and receive one
article on Oracle technologies per month by 
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, 
or send email to [EMAIL PROTECTED] and 
include the word subscribe in either the subject or body.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Re: using temp tables for staging databases?

We set our staging tables to 1 percent free and 99 percent used. We do this to keep 
the datafile as small as possible. This is because we have to tranport it to other 
servers when we publish. There by cutting down on the time it takes to copy the file 
and send it across the pipe. We have cut our copy times down by 2/3s doing this. 

Row migration? We have been doing this for about 5 months now with large nightly 
loads. I have about 4-5 tables to rebuild in the next few days. That isnt bad. We 
actually had one table get to 25% migrated rows and in a light transaction database 
there was no noticeable performance degradation. Ill still rebuild it. 

If you have large tables and you need to do alot of full table scans percent used 40% 
is way too low. It blows up the table and increases the number of physical I/Os. You 
have alot of transactions on your system andthen your PIOs blow up. There is a script 
called 'sparse_tables' or something like that on steve adams site that tells you 
whether you should consider changing your pctused. 

Im on my first project using an NAS storage system. We have about 8-10 servers 
attached to the same cluster right now and I/O is a definite issue, since it all goes 
across the same pipe. 
 
 From: Tim Gorman [EMAIL PROTECTED]
 Date: 2003/10/21 Tue AM 09:24:25 EDT
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Re: using temp tables for staging databases?
 
 For PCTFREE, setting it to less than the default of 10 is an option for
 tables that are INSERT-only where someone wants to pack rows into the
 blocks.  The attendent risks are that UPDATEs causing row expansion may be
 forced to migrate to another block, thus hurting subsequent query
 performance.  Setting PCTFREE greater than the default of 10 is a good idea
 if you have experienced row-migration in the past (or expect to experience
 it) and you want to leave more free space in each block to accommodate row
 expansion from UPDATE statements.
 
 For PCTUSED, I just can't imagine any practical real-world reasons to change
 it from the default of 40.  I'm sure someone else can...
 
 
 
 on 10/21/03 2:19 AM, Mark Leith at [EMAIL PROTECTED] wrote:
 
  Tim,
  
  Can you sum up a few situations when the need *has* arisen to change these
  values?
  
  Cheers
  
  Mark
  
  
  
  -Original Message-
  Tim Gorman
  Sent: 21 October 2003 06:09
  To: Multiple recipients of list ORACLE-L
  
  
  Unless you typo'd, there are some serious problems here...
  
  Setting PCTFREE to 99 is not likely to pack in the blocks.  Rather the
  opposite;  you are instead leaving blocks 99% empty.  Quite a bit of wasted
  I/O in performing a FULL table scan here...  :-)
  
  Anyway, it is not a good idea to have PCTFREE and PCTUSED sum to a value
  greater than 70 or 80 or so, just as a rule of thumb.  Having them sum to a
  value near 100 ensures that each insert, delete, or even update will
  potentially cause the block to be removed or reinserted to one of the
  segment's free list.  Think about it:  the width of a single row crossing
  the boundary from off the free list to on the free list.  Better to
  leave a bit of a no man's land between the two values.  The default
  settings of PCTFREE=10 and PCTUSED=40 are one of the few default settings
  that need little manipulation for most situations.
  
  
  
  on 10/20/03 7:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:
  
  we drop and recreate the temp tables every night. We also use PCTFREE
  PCTUSED
  at 99 and 1 to pack in the blocks and we use very small extent sizes. then
  we
  analyze with an estimate size of 20 percent which is quite fast.
  
  All of them are used for full table scans and do not have indexes. Ive
  found
  that a 'create table as' is MUCH faster than inserting into global
  temporary
  tables when you do not have to worry about latch contention(ie 1-3 users
  logged in at a time).
  
  anyone else notice this? Seems to go against conventional wisdom which
  says
  never use them. So I want to make sure Im not missing something.
  
  From: Tim Gorman [EMAIL PROTECTED]
  Date: 2003/10/20 Mon AM 10:19:33 EDT
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Subject: Re: using temp tables for staging databases?
  
  All the time.  Oracle Apps's open interfaces are built this way, for
  example.
  
  However, the guys here covered their bases by specifying smaller
  temporary tables, as if they could prevent them from becoming large.  I
  suppose they might feel that they indemnify themselves if the tables
  should
  ever become large?
  
  As with OraApps open interface tables, it is when a large volume of
  data
  is pushed through that the trouble starts.  The high-water marks on all
  the tables are pushed to a high level, thereafter causing full table
  scans
  on the interface/temporary tables to run slowly.  The only way to bring
  the
  HWM back down is quiesce the interface/app and then truncate the tables.

Re: max open cursors exceeded

2003-10-21 Thread Craig Munday

Hi,

Is your application written in Java using JDBC?  I've seen ORA-1000 mostly 
with Java applications because developer's tend to not close ResultSets 
explicitly.  Of course it can occur with other environments too.  Anyway, I 
have a tool that can help you find the cause of the ORA-1000 if you're 
using JDBC.

Cheers,
Craig.
At 08:39 AM 20/10/2003 -0800, you wrote:
Hi,
I'm trying to troubleshoot ora-1000 (max cursors exceeded) for an 
application. When I ran the following query on the SID(192),

select hash_value ,count(*)
from v$open_cursor
where sid=192
group by hash_Value
having count(*) 1;
HASH_VALUE   COUNT(*)
-- --
670480087  5
563605149  3
1016653255 10
2005317811  3
hash value: 1016653255 is a simple SQL statement- select sysdate from dual;

the results returned hash_values with counts as high as 10. I understand 
that even if cursors are closed, they are still cached in server memory 
and will still show up in v$open_cursor. I do not understand why there are 
duplicate sql statements in v$open_cursor for the SID. Does that mean that 
Oracle is not reusing the cursor for whatever reasons and thus opens new 
ones? If that is true, what could be the reasons that 'select sysdate from 
dual' could not be reused?

Thanks.

elain

_
Get 10MB of e-mail storage! Sign up for Hotmail Extra Storage.
http://join.msn.com/?PAGE=features/es
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: elain he
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Craig Munday
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: WHERE 1 = 1 (any info on this)

2003-10-21 Thread Stephane Paquette

I'm using that predicate in FGAC functions so the function always return
something.
An FGAC metalink note advise to this .



Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]



-Original Message-
Hately, Mike (LogicaCMG)
Sent: 21 octobre, 2003 07:45
To: Multiple recipients of list ORACLE-L


Morning folks,

the developers here are looking at a view with a where clause which
specifies :

WHERE 1=1 AND
 ... AND
 ... etc.

I'd seen this used before as a way of tweaking the RBO into certain
behaviours but it was years ago and my recollection is very hazy.
The only explanation I've found so far is :

the 1=1 is in there to avoid doing repetitive index scans for single
rowids, when the app knows the result set is going to be manipulated
rowid's for a large subset of the table. I would guess your DUAL/CBO example
had some similar effect.

Is anyone familiar enough with this tweak to explain it ?

Cheers,
Mike

PS Maybe it'll head off some replies if I make it clear that this view
hasn't been generated by code so the 1=1 isn't an accidental artifact It
was custom written and is definitely supposed to have exactly this
structure.









E mail Disclaimer

You agree that you have read and understood this disclaimer and you agree to
be bound by its terms.

The information contained in this e-mail and any files transmitted with it
(if any) are confidential and intended for the addressee only.  If you have
received this  e-mail in error please notify the originator.

This e-mail and any attachments have been scanned for certain viruses prior
to sending but CE Electric UK Funding Company nor any of its associated
companies from whom this e-mail originates shall be liable for any losses as
a result of any viruses being passed on.

No warranty of any kind is given in respect of any information contained in
this   e-mail and you should be aware that that it might be incomplete, out
of date or incorrect. It is therefore essential that you verify all such
information with us before placing any reliance upon it.

CE Electric UK Funding Company
Lloyds Court
78 Grey Street
Newcastle upon Tyne
NE1 6AF
Registered in England and Wales: Number 3476201




--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hately, Mike (LogicaCMG)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Cache a table

2003-10-21 Thread Tim Gorman

Good points, Arup.

Actually, I would argue that there is better reason to consider using the
RECYCLE pool than to consider how to cache tables or use the KEEP pool.
The advantage of effective use of the RECYCLE pool is better behavior in the
rest of the Buffer Cache...

When you think of it, the default DEFAULT buffer pool and the KEEP pool have
essentially the same purpose:  long-term caching of blocks.  What keeps them
from accomplishing that mission but objects whose blocks waste space and
energy cycling into and out from the Buffer Cache?

It's kind of like a school teacher admonishing his/her class that a
troublesome few have ruined things for everybody.  When I was in school,
troublemakers were segregated from the rest of the class, sometimes
cumulatively into a separate classroom (we called ourselves the mentals
and read Mad magazines all the time, which accounts for a lot, then and
now).  Nowadays, I'm sure that such a measure isn't considered for fear of
lawsuit for hurting the self-esteem of the poor dears.  Never mind the
confusion between the useless feel-good phrase self-esteem and the more
useful and thought-provoking phrase self-respect.  Oh well, better stop
now...

Anyway, marking a table as CACHE and placing it in a KEEP buffer pool which
is large enough to accommodate all of the used blocks is the closest thing
to pinning a table into the Buffer Cache as you'll get, as Arup described.

Of course, there is little benefit from such a move, as Arup also mentioned.

Just yesterday, I visited a customer who had a series of SQL statements that
were executing some 10 million times _each_ per day, averaging about 20-1500
LIOs per execution.  They each had a 99.999% buffer cache hit
ratio, yet strangely enough the performance on the server is absolute crap
because the eight brand-new 2Ghz CPUs on the server are busy as hell with no
time to spare for anything.

Well, you know and I know that they simply need more CPUs, which is what HP
is busy telling them, today right as we speak.  Moreover, Oracle
Consulting is shoulder to shoulder with them, nodding their heads.  No way
does the crap custom-built application need to be altered in any minor way,
so that it doesn't keep performing the same useless validation query on the
same set of static lookup tables over and over again for each row inserted,
when the JDBC thin client can easily query these tables only once and store
the results.  Nope.  No sirree...

Cliff-Clavin-voice
It's a little-known fact that Java code actually has the consistency of
concrete, once in production.  There are so many interdependencies from
shared modules and RPCs that people are terrified of modifying anything,
probably for good reason.  Far easier to shift blame or say hear hear when
the vendor proposes another 4-8 CPUs.

Ah, I believe I'll have another beer when you're ready, Sammy...
/Cliff-Clavin-voice

Anyway, first tune the SQL.  Then, tune to the application to get rid of
unnecessary SQL.  Then and only then, consider tuning the Buffer Cache to
segregate bad tables to the RECYCLE pool or pinning tables to the KEEP
pool.  Reversing the order is a great way to convert a happy application
capable of running on a small server to an unhappy application demanding a
huge server...



on 10/21/03 5:21 AM, Arup Nanda at [EMAIL PROTECTED] wrote:

 Never. Altering the table to cache does not gurantee that it will be always
 be available in the cache. It simply means the table will be placed in the
 Least recently used end of the LRU list and it will age away as time goes
 by, just like any other table.
 
 A better approach is to use KEEP pool and place teh table (and all other
 tables that are accessed frequently) there. This is particualrly true for
 datawarehouses wherethe lookup tables or small dimension tables can be
 placed in KEEP pool.
 
 Ah, come tho think about it, actually there is one situation where I will
 consider the CACHE option, when I restart the instance and want the hit
 ratio to look good :)
 
 HTH.
 
 Arup Nanda
 
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Tuesday, October 21, 2003 3:39 AM
 
 
 Hi all,
 
 when you would consider to put a table a cache...
 
 rgds
 
 gb
 
 
 Want to chat instantly with your online friends?  Get the FREE Yahoo!
 Messenger http://mail.messenger.yahoo.co.uk
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: =?iso-8859-1?q?Gunnar=20Berglund?=
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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

RE: WHERE 1 = 1 (any info on this)

2003-10-21 Thread April Wells

Title: RE: WHERE 1 = 1 (any info on this)





BUT... a lot of the SQL still has rule hints in it for Apps... even though it is touted to be using the CBO extensively... 

April Wells
Oracle DBA/Oracle Apps DBA
Corporate Systems
Amarillo Texas
 /\
/ \
/ \
\ /
 \/
 \
 \
 \
 \
Few people really enjoy the simple pleasure of flying a kite
Adam Wells age 11




-Original Message-
From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 21, 2003 8:30 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: WHERE 1 = 1 (any info on this)



OK, here's the interesting thing. At 8.1.6.3 with optimizer_mode=rule the
statement I'm looking at returns very different explain plans depending on
whether the 1=1 clause is included. It's a complicated join and the
explain plans are over 300 lines so it's not easy to see what's happening.
I'll try with a simpler join. 
And yes, I know the RBO is ancient technology these days. =)


Cheers,
Mike


-Original Message-
Sent: 21 October 2003 12:05
To: Multiple recipients of list ORACLE-L



I'm pretty sure the optimizer can pick up 1=1 anyway
and ignore it as an always-true condition - so you get
no benefit.


The most common cause I've seen for 1=1 is so when
developers are building dynamic where-clause, they
don't need to worry about adding 'where' versus 'and'
to the sql string being constructed.


hth
connor


--- Hately, Mike (LogicaCMG)
[EMAIL PROTECTED] wrote:  Morning folks,
 
 the developers here are looking at a view with a
 where clause which
 specifies :
 
 WHERE 1=1 AND
 ... AND
 ... etc.
 
 I'd seen this used before as a way of tweaking the
 RBO into certain
 behaviours but it was years ago and my recollection
 is very hazy. 
 The only explanation I've found so far is :
 
 the 1=1 is in there to avoid doing repetitive index
 scans for single
 rowids, when the app knows the result set is going
 to be manipulated
 rowid's for a large subset of the table. I would
 guess your DUAL/CBO example
 had some similar effect.
 
 Is anyone familiar enough with this tweak to explain
 it ?
 
 Cheers,
 Mike
 
 PS Maybe it'll head off some replies if I make it
 clear that this view
 hasn't been generated by code so the 1=1 isn't an
 accidental artifact It
 was custom written and is definitely supposed to
 have exactly this
 structure.
 
 
 
 
 
 
 



 E mail Disclaimer
 
 You agree that you have read and understood this
 disclaimer and you agree to be bound by its terms.
 
 The information contained in this e-mail and any
 files transmitted with it (if any) are confidential
 and intended for the addressee only. If you have
 received this e-mail in error please notify the
 originator. 
 
 This e-mail and any attachments have been scanned
 for certain viruses prior to sending but CE Electric
 UK Funding Company nor any of its associated
 companies from whom this e-mail originates shall be
 liable for any losses as a result of any viruses
 being passed on.
 
 No warranty of any kind is given in respect of any
 information contained in this e-mail and you
 should be aware that that it might be incomplete,
 out of date or incorrect. It is therefore essential
 that you verify all such information with us before
 placing any reliance upon it.
 
 CE Electric UK Funding Company
 Lloyds Court
 78 Grey Street
 Newcastle upon Tyne
 NE1 6AF
 Registered in England and Wales: Number 3476201
 



 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net
 -- 
 Author: Hately, Mike (LogicaCMG)
 INET: [EMAIL PROTECTED]
 
 Fat City Network Services -- 858-538-5051
 http://www.fatcity.com
 San Diego, California -- Mailing list and web
 hosting services

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


=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]


GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day



Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
 INET: [EMAIL PROTECTED]


Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services

Re: Re[2]: job opportunity in Dallas

What a horrible problem it must be, if we are solution?

On 10/20/2003 05:39:33 PM, Jonathan Gennick wrote:
Monday, October 20, 2003, 4:54:26 PM, you wrote:
IN 2% - that's all we get? -:)
Didn't someone write a book once called The 2% Solution?
That's what we are: the solution!
Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit  
http://four.pairlist.net/mailman/listinfo/oracle-article,

or send email to [EMAIL PROTECTED] and
include the word subscribe in either the subject or body.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Gennick
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
Mladen Gogala
Oracle DBA


Note:
This message is for the named person's use only.  It may contain confidential, 
proprietary or legally privileged information.  No confidentiality or privilege is 
waived or lost by any mistransmission.  If you receive this message in error, please 
immediately delete it and all copies of it from your system, destroy any hard copies 
of it and notify the sender.  You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to 
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where 
the message states otherwise and the sender is authorized to state them to be the 
views of any such entity.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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_FILE_DIRECT_IO_COUNT

2003-10-21 Thread Muqthar Ahmed

Lee,

If you would like to see the default value for _DB_FILE_DIRECT_IO_COUNT, run the 
following query with SYSDBA privileges:

col Parameter for a50
col Session Value for a20
col Instance Value for a20
select a.ksppinm Parameter,
   b.ksppstvl Session Value,
   c.ksppstvl Instance Value
  from x$ksppi a,
   x$ksppcv b,
   x$ksppsv c
 where a.indx = b.indx
 and   a.indx = c.indx
 and   a.ksppinm like '_db_file%'
 order by ksppinm
/

Parameter  Session ValueInstance Value
--  

_db_file_direct_io_count   1048576  1048576
_db_file_noncontig_mblock_read_count   11   11

SQL 

Muqthar Ahmed
DBA

-Original Message-
Sent: Tuesday, October 21, 2003 8:16 AM
To: Multiple recipients of list ORACLE-L


Hi,

9.2.0.3 AIX 5L

Anyone used this in 9i ??

Can you set this as you would a normal parameter (ie. as I did in 8i with
undocumented parameters).

Sorry if this is a stupid question but I am still getting my head around
this 9i stuff.

Plus don't worry, this is not Production I am playing but a test instance. I
thought I would ask first as I would rather not recreate this.

Cheers

Lee







**
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Muqthar Ahmed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Data Transfer between two instances

Jared,

Correction, was.  Hasn't been updated in several years as I understand.  
That's why they farmed it out to MySql recently.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-Original Message-
Sent: Tuesday, October 21, 2003 12:54 AM
To: Multiple recipients of list ORACLE-L



Actually, that 'cheap thing' is SAP DB, which is a simply
what SAP renamed Adabas when they purchased it.  Apparently it
is quite a capable databas.


On Mon, 2003-10-20 at 05:59, Goulet, Dick wrote:
 SAP runs on Oracle.  Unless you use that cheap thing that MYSql currently offers, in 
 which case use heterogeneous services, if you can find the odbc driver.  Then a DB 
 Link or the COPY command will work.
 
 Dick Goulet
 Senior Oracle DBA
 Oracle Certified 8i DBA
 
 -Original Message-
 Sent: Monday, October 20, 2003 8:37 AM
 To: Multiple recipients of list ORACLE-L
 
 
 Iz SAP DB Oracle??
 
 
 On 2003.10.20 07:34, Goulet, Dick wrote:
  Have you tried SQL*Plus's COPY command??
  
  
  Dick Goulet
  Senior Oracle DBA
  Oracle Certified 8i DBA
  
  -Original Message-
  Sent: Monday, October 20, 2003 1:39 AM
  To: Multiple recipients of list ORACLE-L
  
  
  Hi all,
  
  we have an application which needs data from other environment (which is
  actually SAP db). Currently we have implemented it the way we create flat
  files and put them in using pl/sql -procedures but I don't like this because
  the data in the flat files are visible and it is somehow secret.
  
  What other options we might have if we do not want to use db links (because
  of its slowness.
  
  I very much appreciate all your suggestions...
  
  TIA
  gb
  
  
  
_
  
  Want to chat instantly with your online friends?
  http://uk.rd.yahoo.com/mail/tagline_messenger/*http://uk.messenger.yahoo.com/ 
  
  Get the FREE Yahoo! Messenger
  
 
 
 -- 
 Mladen Gogala
 Oracle DBA
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Mladen Gogala
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
 -- 
 Author: Goulet, Dick
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Goulet, Dick
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

Block size written

2003-10-21 Thread Robertson Lee - lerobe

Hi,


Oracle 9.2.0.3
AIX 5L

Is there a way of finding the block sizes that are being written from the
database. We are experiencing write waits now, after solving our read
issues.

We have looked at truss -p on AIX and it doesn`t seem to show this.

Regards

Lee






**
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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_FILE_DIRECT_IO_COUNT

2003-10-21 Thread Robertson Lee - lerobe

Thanks 

-Original Message-
Sent: 21 October 2003 15:24
To: Multiple recipients of list ORACLE-L


Lee,

If you would like to see the default value for _DB_FILE_DIRECT_IO_COUNT, run
the following query with SYSDBA privileges:

col Parameter for a50
col Session Value for a20
col Instance Value for a20
select a.ksppinm Parameter,
   b.ksppstvl Session Value,
   c.ksppstvl Instance Value
  from x$ksppi a,
   x$ksppcv b,
   x$ksppsv c
 where a.indx = b.indx
 and   a.indx = c.indx
 and   a.ksppinm like '_db_file%'
 order by ksppinm
/

Parameter  Session Value
Instance Value
-- 

_db_file_direct_io_count   1048576
1048576
_db_file_noncontig_mblock_read_count   11   11

SQL 

Muqthar Ahmed
DBA

-Original Message-
Sent: Tuesday, October 21, 2003 8:16 AM
To: Multiple recipients of list ORACLE-L


Hi,

9.2.0.3 AIX 5L

Anyone used this in 9i ??

Can you set this as you would a normal parameter (ie. as I did in 8i with
undocumented parameters).

Sorry if this is a stupid question but I am still getting my head around
this 9i stuff.

Plus don't worry, this is not Production I am playing but a test instance. I
thought I would ask first as I would rather not recreate this.

Cheers

Lee







**
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Muqthar Ahmed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Robertson Lee - lerobe
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

11.5.8 installation Problem

2003-10-21 Thread Natalia Laracca




Hi,  I have readed on FORUM metalink that you have 
had the same problem that me. My situation is: 

I have Windows XP professional. I'm trying to install PROD db 11.5.8 
and on 5 CD, it stops at the sqlplus screen with usernmae. The message on 
the SQLPLUS screen is: ERROR: ORA-1033: ORACLE initialization or 
shutdown in progress. 

Enter user-name: 

If you had some solution to successfully install PRODdatabase, 
it's much appreciated. 

Thanks in advanced, Natalia 
L. Laracca

RE: WHERE 1 = 1 (any info on this)

2003-10-21 Thread Gints Plivna

I'm personally using it for large search screens with ~100 attributes to
fulfil. At first comes head of select statement let's say
SELECT a, b FROM table_a
then comes where clause WHERE 1=1
and after that I can simply add variable v_comma = ' AND ';
So for every search attribute I can simply add to where clause
IF in_attribute_ IS NOT NULL 
  v_where_clause = v_comma || 'attribute_ = ' || in_attribute_;
END IF;
Of course where clause additions depends on attribute type, there may be
two search attributes for an attribute in the table eg. limits from both
sides for numbers or dates etc.

Of course I'm not building views, but simple dynamic select statements.

Gints

 -Original Message-
 From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, October 21, 2003 4:30 PM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: WHERE 1 = 1 (any info on this)
 
 OK, here's the interesting thing. At 8.1.6.3 with optimizer_mode=rule
the
 statement I'm looking at returns very different explain plans
depending on
 whether the 1=1 clause is included. It's a complicated join and the
 explain plans are over 300 lines so it's not easy to see what's
happening.
 I'll try with a simpler join.
 And yes, I know the RBO is ancient technology these days. =)
 
 Cheers,
 Mike
 
 -Original Message-
 Sent: 21 October 2003 12:05
 To: Multiple recipients of list ORACLE-L
 
 
 I'm pretty sure the optimizer can pick up 1=1 anyway
 and ignore it as an always-true condition - so you get
 no benefit.
 
 The most common cause I've seen for 1=1 is so when
 developers are building dynamic where-clause, they
 don't need to worry about adding 'where' versus 'and'
 to the sql string being constructed.
 
 hth
 connor
 
  --- Hately, Mike (LogicaCMG)
 [EMAIL PROTECTED] wrote:  Morning folks,
 
  the developers here are looking at a view with a
  where clause which
  specifies :
 
  WHERE 1=1 AND
   ... AND
   ... etc.
 
  I'd seen this used before as a way of tweaking the
  RBO into certain
  behaviours but it was years ago and my recollection
  is very hazy.
  The only explanation I've found so far is :
 
  the 1=1 is in there to avoid doing repetitive index
  scans for single
  rowids, when the app knows the result set is going
  to be manipulated
  rowid's for a large subset of the table. I would
  guess your DUAL/CBO example
  had some similar effect.
 
  Is anyone familiar enough with this tweak to explain
  it ?
 
  Cheers,
  Mike
 
  PS Maybe it'll head off some replies if I make it
  clear that this view
  hasn't been generated by code so the 1=1 isn't an
  accidental artifact It
  was custom written and is definitely supposed to
  have exactly this
  structure.
 
 
 
 
 
 
 
 


**
 **
 
  E mail Disclaimer
 
  You agree that you have read and understood this
  disclaimer and you agree to be bound by its terms.
 
  The information contained in this e-mail and any
  files transmitted with it (if any) are confidential
  and intended for the addressee only.  If you have
  received this  e-mail in error please notify the
  originator.
 
  This e-mail and any attachments have been scanned
  for certain viruses prior to sending but CE Electric
  UK Funding Company nor any of its associated
  companies from whom this e-mail originates shall be
  liable for any losses as a result of any viruses
  being passed on.
 
  No warranty of any kind is given in respect of any
  information contained in this   e-mail and you
  should be aware that that it might be incomplete,
  out of date or incorrect. It is therefore essential
  that you verify all such information with us before
  placing any reliance upon it.
 
  CE Electric UK Funding Company
  Lloyds Court
  78 Grey Street
  Newcastle upon Tyne
  NE1 6AF
  Registered in England and Wales: Number 3476201
 
 


**
 **
 
 
  --
  Please see the official ORACLE-L FAQ:
  http://www.orafaq.net
  --
  Author: Hately, Mike (LogicaCMG)
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051
  http://www.fatcity.com
  San Diego, California-- Mailing list and web
  hosting services
 
 -
  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).
 
 =
 Connor McDonald
 web: http://www.oracledba.co.uk
 web: http://www.oaktable.net
 email: [EMAIL PROTECTED]
 
 GIVE a man a fish and he will eat for a day. But TEACH him how to
fish,
 and...he will sit in a boat and drink beer all day

RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE:

2003-10-21 Thread Hemant K Chitale

Unfortunately, the lines
Unoccupied space on indexes occurs when a key value changes, and the 
index
row is deleted from one place (Leaf Block) and inserted into another.
Deleted Leaf Rows are not reused.  Therefore, indexes whose columns are
subject to intensive value change should be rebuilt periodically, since
they become naturally fragmentated. 
are still visible in Note 182699.1

Hemant

At 08:29 AM 20-10-03 -0800, you wrote:
Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements
about index fragmentation have been removed.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- SQL Optimization 101: 12/8-12 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
-Original Message-
Richard Foote
Sent: Friday, October 17, 2003 6:29 AM
To: Multiple recipients of list ORACLE-L
Separate
Hi Hemant,

One word perfectly describes the Metalink article you highlighted:

Crap ;)

A nice example of  how Oracle Corp is the greatest myth generator of
them
all !! It's all rather sad and embarressing isn't.
Thanks for the headsup. Anyone in a position to get the note removed ?

Cheers

Richard

Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds
Fragmentated Indexes (8.0-9.0)

Index fragmentation occurs when a key value changes, and the index row
is
deleted from one place (Leaf Block) and inserted into another.

 Deleted Leaf Rows are not reused. Therefore indexes whose columns are
 subject to value change must be rebuilt periodically since they become
naturally fragmentated.

 An index is considered to be 'fragmentated' when more than 20% of its
Leaf
Rows space is
empty because of the implicit deletes caused by indexed columns value
changes.

 Fragmentated indexes degrade the performance of index range scan
operations.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Richard Foote
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Cary Millsap
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :  http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Data Modeling

2003-10-21 Thread DENNIS WILLIAMS

B. S.
I am no expert at data modeling but have made some modest attempts in
that direction. First, what is your objective? Casual or intense?
I have heard that data modeling is not learned from a book, but you need
to take a class. The classes I took were so long ago and I didn't do
anything with it so I can't vouch for that.
If you are need to review the data models of others, get the book The
Data Modeling Handbook by  Reingruber and Gregory. There is nothing like
it. The authors give  will sound really profound and if you get someone that
wants to argue, you can point them to the book.
There is a data modeling email list where you could get better answers
for this and other questions: DataModel.Org Mailing List Here are the
removal instructions, I'm sure you can figure out how to add yourself.
 To be removed from this list, please send an E-mail to
 mailto:[EMAIL PROTECTED] in the Subject line, put REMOVE {your
 email address}.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 
-Original Message-
Sent: Tuesday, October 21, 2003 7:54 AM
To: Multiple recipients of list ORACLE-L



Hi All

I have not done data modelling as a major task so far. Would like to know
from where can I make a good start. I have basic(conceptual) knowledge of
ER-Diagram and Normalization. But if anybody can give me some case
studies/links etc.,

I see the responsibilities would include modelling and coping with Frequent
changes of the database object structures.

Please Help

Thanks a Lot

Regards
B S Pradhan

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: using temp tables for staging databases?

2003-10-21 Thread Barbara Baker

Hi, Mark.
I'm not Tim, but I did encounter such a situation.
This was not a temp table, but a permanent one.

We have a db with a very strange block size of 4608
(actually Tim is painfully aware of this one). We have
a very large table in this database.  It was expanding
at about 200 megs per week -- way out of control for a
relataively small database.

The database was not reusing blocks.  Oracle
recommends that  (100% - (pctfree+pcused)) be greater
than the maximum sie of a row. So we did an exact
calculation of the blocksize less %free+%used 

 1% of a block is 46.08
80% of a block is 3686.4
4608 - (46.08 + 3686.4) = 875.52


our largest row length is 860

So we set pctfree at 1% and pctused at 80%
One of the reasons we can get by with this is because
the vendor designed the database with all char (not
varchar2), so we pretty much know exactly what each
row is going to consume. (It's a Cobol app)

After this change, the database stopped it's wild
expansion.

Not a normal situation, but then nothing here is
normal.  (Kids -- don't try this at home!)

Barb



--- Mark Leith [EMAIL PROTECTED] wrote:
 Tim,
 
 Can you sum up a few situations when the need *has*
 arisen to change these
 values?
 
 Cheers
 
 Mark
 
 
 
 -Original Message-
 Tim Gorman
 Sent: 21 October 2003 06:09
 To: Multiple recipients of list ORACLE-L
 
 
 Unless you typo'd, there are some serious problems
 here...
 
 Setting PCTFREE to 99 is not likely to pack in the
 blocks.  Rather the
 opposite;  you are instead leaving blocks 99% empty.
  Quite a bit of wasted
 I/O in performing a FULL table scan here...  :-)
 
 Anyway, it is not a good idea to have PCTFREE and
 PCTUSED sum to a value
 greater than 70 or 80 or so, just as a rule of
 thumb.  Having them sum to a
 value near 100 ensures that each insert, delete, or
 even update will
 potentially cause the block to be removed or
 reinserted to one of the
 segment's free list.  Think about it:  the width of
 a single row crossing
 the boundary from off the free list to on the
 free list.  Better to
 leave a bit of a no man's land between the two
 values.  The default
 settings of PCTFREE=10 and PCTUSED=40 are one of the
 few default settings
 that need little manipulation for most situations.
 
 
 
 on 10/20/03 7:34 AM, [EMAIL PROTECTED] at
 [EMAIL PROTECTED] wrote:
 
  we drop and recreate the temp tables every night.
 We also use PCTFREE
 PCTUSED
  at 99 and 1 to pack in the blocks and we use very
 small extent sizes. then
 we
  analyze with an estimate size of 20 percent which
 is quite fast.
 
  All of them are used for full table scans and do
 not have indexes. Ive
 found
  that a 'create table as' is MUCH faster than
 inserting into global
 temporary
  tables when you do not have to worry about latch
 contention(ie 1-3 users
  logged in at a time).
 
  anyone else notice this? Seems to go against
 conventional wisdom which
 says
  never use them. So I want to make sure Im not
 missing something.
 
  From: Tim Gorman [EMAIL PROTECTED]
  Date: 2003/10/20 Mon AM 10:19:33 EDT
  To: Multiple recipients of list ORACLE-L
 [EMAIL PROTECTED]
  Subject: Re: using temp tables for staging
 databases?
 
  All the time.  Oracle Apps's open interfaces
 are built this way, for
  example.
 
  However, the guys here covered their bases by
 specifying smaller
  temporary tables, as if they could prevent them
 from becoming large.  I
  suppose they might feel that they indemnify
 themselves if the tables
 should
  ever become large?
 
  As with OraApps open interface tables, it is
 when a large volume of
 data
  is pushed through that the trouble starts.  The
 high-water marks on all
  the tables are pushed to a high level, thereafter
 causing full table
 scans
  on the interface/temporary tables to run slowly. 
 The only way to bring
 the
  HWM back down is quiesce the interface/app and
 then truncate the tables.
 
 
 
  on 10/20/03 6:39 AM, [EMAIL PROTECTED] at
 [EMAIL PROTECTED] wrote:
 
  This is for non-transactional data load
 instances. The guys here sware
 that
  by
  using smaller temporary tables(not global temp
 tables) they can increase
 the
  speed of the data loads.
 
  Not worried about latch contention because its
 just for bulk loads. I
 know
  this bad in transactional instances. Has anyone
 used these in
  non-transactional data load instances?
 
  --
  Please see the official ORACLE-L FAQ:
 http://www.orafaq.net
  --
  Author: Tim Gorman
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
  San Diego, California-- Mailing list and
 web hosting services
 

-
  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

Re: using temp tables for staging databases?



Binley,The 
cause of Buffer Busy Waits (BBW) is not exclusively the setting of PCTUSED and 
PCTFREE; they just two of the causes. To understand the connection, let me 
explain a little bit on the cause of BBWs.When a session requests some 
data element from a table, the server process of the session gets the block from 
the disk to the cache (assume the block is not present in the cache). The event 
of the block coming from the disk to occupy a buffer in the caceh is pretty 
straight forward. Now, imagaine, at the exact same time another session selects 
a row from the same block. A *different* row but from the *same* block. That 
session will search the cache buffer chain and see that the buffer is not 
present and will attempt the same maneuevre, i.e. get the buffer from the disk. 
However, the first session is currently moving the buffer; the second session 
has to *wait* till the process is complete. This wait is known as buffer busy 
wait (BBW); but I guess you already knew that. The two sessions are not in 
conflict over the same row, but the same buffer; so it's not locking 
contention.How can we eliminate BBWs? Unfortunately we can't bring it to 
zero. There is always a probability that two sessions will try to get the same 
block. The only exception is when a block contains only one row. In that case 
the sessions will select different blocks for different rows. Again, this is not 
practical. We can reduce BBW by reducing the *possibility* that two 
sessions will not try to access the same block. This can be done using several 
ways:(1) reducing the block size(2) making a block less compact, so 
that each block holds less number of rows. The fewer the number of rows in a 
block, the lesser the probability that two sessions will access rows in the same 
block.The first option is not a very practical one in most cases. The 
second option is. It can be effected by allocating less space in a block, which 
can be done by using a large value of PCTFREE, e.g. 40 and/or small value of 
PCTUSED, such as 40, instead of 99. Other ways to achieve the same result is 
using a higher value of INITRANS, or anything that will cause less number of 
rows to fill up a block. Less rows = less chance of BBW occuring.I 
wrote a paper in Select Journal a few months ago explaining this very situation. 
Although the article is on Segment Level Statistics, it has an example which you 
can simulate to see the effect of PCTFREE/PCTUSED/INITRANS on Buffer Busy Waits. 
It can be downlaoded from my website at www.proligence.com/downloads.html and 
choose New Tool on the Block - Segment Level Statistics. Please feel free to 
give it a whirl.Further qualifying the case for higher PCTUSED and lower 
PCTFREE in datawarehouse environments, the chance that two sessions will access 
the row in same block is much less in DW than in OLTP. Hence the values can be 
different in DW.HTH.Arup Nanda- Original Message 
- From: "Binley Lim" [EMAIL PROTECTED]To: "Multiple 
recipients of list ORACLE-L" [EMAIL PROTECTED]Sent: Tuesday, 
October 21, 2003 10:24 AMSubject: Re: using temp tables for staging 
databases?  I'm unclear how BBW is related to PCTUSED. 
PCTUSED is used to control when blocks are returned to the freelist due 
to deletions. Blocks already-off the freelist, and above PCTUSED, remain 
unavailable for inserts.  PCTUSED does not prevent a "block 
contains too many rows" -since a low PCTFREE will pack the rows tightly 
anyway. If BBW wait is a problem, then there are other causes. PCTUSED 
is not one of them, or at least should not be an attempted 
solution.   I will also add to Tim's response of justifying 
a smaller PCTUSED. In  addition to the freelist problem he 
mentioned, there is also a greater  chance of buffer busy waits 
occuring when a block contains too many rows. In  an OLTP 
database that is certainly likely to happen - another case for the  
default 40 setting for the parameter. In DW, however, the chances of BBW 
are  low, hence a higher setting may be possible.  
  --  Please see the official ORACLE-L FAQ: 
http://www.orafaq.net --  Author: Binley Lim 
INET: [EMAIL PROTECTED]  Fat City Network 
Services -- 858-538-5051 http://www.fatcity.com San 
Diego, California -- Mailing list and 
web hosting services 
- 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: Not able to add DB which is ruuning on the local node

2003-10-21 Thread tamizh

Babu,

Thanks for your response.

I have only one ORACLE_HOME set in my machine. Also both OEM and Oracle DB are running 
on my local machine(Windows-NT).

--Sami

- Original Message -
Date: Monday, October 20, 2003 11:04 pm

 Is this database on the same O_H as OEM or a different one ?
 
 Also you may want to review MEtalink articles on How OEM 
 discovers a node
 on Metalink...
 
 Babu
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Saturday, October 18, 2003 10:44 AM
 
 
  Hi List:
 
  I installed Oracle 9i on Windows NT,configured OEM on Windows 
 and could
 successfully connect to OEM.
 
  Discover node works fine for remote machines(adds 
 database,listener,httpserver,ect) but not allowing me to add 
 database which isrunning on local
 machine.
 
  Even OEM is not allowing me to add DB using manual configuration.
 
  What could be wrong? Did anyone come across this
  scenario?
 
  Any help would be really appreciated.
  TIA
  -Sami
 
 
 
 
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author:
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting 
 services -
 
  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.net
 -- 
 Author: Babu Nagarajan
  INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 ---
 --
 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.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

New to DBA !

2003-10-21 Thread Rama, Shreekantha (K.)




Hi All, 

 I am new to the world of Oracle.. 

 I am having this issue.. 


 I have 
created a database on Oracle 8.1.7.4.. 

 But I am not 
able to access as the password is lost..
 Now, I am 
not able to login.. 
 I tried 
login using system /manager.. but oracle is not allowing me to login .. 


 What's the 
solution ?? 
Regards Shreekanth

Re: WHERE 1 = 1 (any info on this)

Or just use NULL as a return predicate in FGAC; it will be the same effect,
but may be slightly better.

Arup Nanda
- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 9:49 AM


 I'm using that predicate in FGAC functions so the function always return
 something.
 An FGAC metalink note advise to this .



 Stephane Paquette
 Administrateur de bases de donnees
 Database Administrator
 Standard Life
 www.standardlife.ca
 Tel. (514) 499-7999 7470 and (514) 925-7187
 [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]



 -Original Message-
 Hately, Mike (LogicaCMG)
 Sent: 21 octobre, 2003 07:45
 To: Multiple recipients of list ORACLE-L


 Morning folks,

 the developers here are looking at a view with a where clause which
 specifies :

 WHERE 1=1 AND
  ... AND
  ... etc.

 I'd seen this used before as a way of tweaking the RBO into certain
 behaviours but it was years ago and my recollection is very hazy.
 The only explanation I've found so far is :

 the 1=1 is in there to avoid doing repetitive index scans for single
 rowids, when the app knows the result set is going to be manipulated
 rowid's for a large subset of the table. I would guess your DUAL/CBO
example
 had some similar effect.

 Is anyone familiar enough with this tweak to explain it ?

 Cheers,
 Mike

 PS Maybe it'll head off some replies if I make it clear that this view
 hasn't been generated by code so the 1=1 isn't an accidental artifact It
 was custom written and is definitely supposed to have exactly this
 structure.









 
 E mail Disclaimer

 You agree that you have read and understood this disclaimer and you agree
to
 be bound by its terms.

 The information contained in this e-mail and any files transmitted with it
 (if any) are confidential and intended for the addressee only.  If you
have
 received this  e-mail in error please notify the originator.

 This e-mail and any attachments have been scanned for certain viruses
prior
 to sending but CE Electric UK Funding Company nor any of its associated
 companies from whom this e-mail originates shall be liable for any losses
as
 a result of any viruses being passed on.

 No warranty of any kind is given in respect of any information contained
in
 this   e-mail and you should be aware that that it might be incomplete,
out
 of date or incorrect. It is therefore essential that you verify all such
 information with us before placing any reliance upon it.

 CE Electric UK Funding Company
 Lloyds Court
 78 Grey Street
 Newcastle upon Tyne
 NE1 6AF
 Registered in England and Wales: Number 3476201



 

 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Hately, Mike (LogicaCMG)
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
 -- 
 Author: Stephane Paquette
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Cache a table

OOPS! Fat finger it is, indeed. It should be MRU end of the LRU list, not
LRU end.

Thanks for the correction, Mike.

Arup

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 8:39 AM


 Hi,

 I'm sure you know this but you had some finger trouble there. Just to
 clarify it for others; Tables with the CACHE option are placed at the Most
 Recently Used end of the LRU list.

 Cheers,
 Mike Hately

 -Original Message-
 Sent: 21 October 2003 12:21
 To: Multiple recipients of list ORACLE-L


 Never. Altering the table to cache does not gurantee that it will be
always
 be available in the cache. It simply means the table will be placed in the
 Least recently used end of the LRU list and it will age away as time goes
 by, just like any other table.

 A better approach is to use KEEP pool and place teh table (and all other
 tables that are accessed frequently) there. This is particualrly true for
 datawarehouses wherethe lookup tables or small dimension tables can be
 placed in KEEP pool.

 Ah, come tho think about it, actually there is one situation where I will
 consider the CACHE option, when I restart the instance and want the hit
 ratio to look good :)

 HTH.

 Arup Nanda

 - Original Message - 
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Tuesday, October 21, 2003 3:39 AM


  Hi all,
 
  when you would consider to put a table a cache...
 
  rgds
 
  gb





 E mail Disclaimer

 You agree that you have read and understood this disclaimer and you agree
to be bound by its terms.

 The information contained in this e-mail and any files transmitted with it
(if any) are confidential and intended for the addressee only.  If you have
received this  e-mail in error please notify the originator.

 This e-mail and any attachments have been scanned for certain viruses
prior to sending but CE Electric UK Funding Company nor any of its
associated companies from whom this e-mail originates shall be liable for
any losses as a result of any viruses being passed on.

 No warranty of any kind is given in respect of any information contained
in this   e-mail and you should be aware that that it might be incomplete,
out of date or incorrect. It is therefore essential that you verify all such
information with us before placing any reliance upon it.

 CE Electric UK Funding Company
 Lloyds Court
 78 Grey Street
 Newcastle upon Tyne
 NE1 6AF
 Registered in England and Wales: Number 3476201





 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Hately, Mike (LogicaCMG)
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Block size written

2003-10-21 Thread Cary Millsap

Lee,

Are you sure you're specifying the right process? When I use truss -p on
my DBWR, it gives exactly what you need. In the following test, I used
truss (strace on my Linux machine) to show what DBWR did in response to
a create table test as select * from dual; drop table test; executed
in another session:

 09:25:31 $ ps -ef | grep dbw
oracle1133 1  0 Oct09 ?00:00:01 ora_dbw0_V901
cvm  12013 11962  0 09:29 pts/000:00:00 grep dbw
 09:25:32 $ strace -p 1133 -e trace=write,pwrite
--- SIGALRM (Alarm clock) ---
..
--- SIGALRM (Alarm clock) ---
pwrite(17, [EMAIL PROTECTED]...,
4096, 12234752) = 4096
--- SIGALRM (Alarm clock) ---
..
--- SIGALRM (Alarm clock) ---
 09:27:09 $

By doing a man pwrite, I can see the explanation of the pwrite return
value of 4096: 

  RETURN VALUE
   On success,  the  number  of  bytes  read  or  written  is
   returned  (zero indicates that nothing was written, in the
   case of pwrite, or end of file, in the case of pread),  or
   -1  on  error,  in which case errno is set to indicate the
   error.

Therefore, my DBWR is writing 4,096 bytes at a time.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- SQL Optimization 101: 12/8-12 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...


-Original Message-
Robertson Lee - lerobe
Sent: Tuesday, October 21, 2003 9:34 AM
To: Multiple recipients of list ORACLE-L

Hi,


Oracle 9.2.0.3
AIX 5L

Is there a way of finding the block sizes that are being written from
the
database. We are experiencing write waits now, after solving our read
issues.

We have looked at truss -p on AIX and it doesn`t seem to show this.

Regards

Lee






**
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Cary Millsap
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Refresh option for Materialized view , want to use it during

2003-10-21 Thread Stephen.Lee




One possibility 
would be to replicate each of the four source tables, then perform the join on 
the replicated tables. This will require you to create snapshot logs on 
the source tables. Obviously, this will slow the performance of the 
whatever report is running against the replicated data since the join must be 
done for each query instead of when the data was originally replicated. 
But the data will remain continuously available.

  -Original Message-From: Siddharth Haldankar 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, October 21, 2003 2:59 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  Refresh option for Materialized view , want to use it during 
  refresh
  
  Hi 
  Gurus,
  
  I 
  have a materialized view, which is based on Oracle Apps tables and on remote 
  database. The view refresh takes around ½ hour, during this time period I 
  cannot see any records in the materialized view and therefore my application 
  faces errors.
  The 
  following is the view definition
  
  CREATE 
  MATERIALIZED VIEW 
  CT_PRODUCTID_VW 
  BUILD 
  IMMEDIATE
  REFRESH START WITH 
  SYSDATE
  NEXT (SYSDATE + 
  1)
  AS 
  SELECT
   
  msi.segment1 
  productid, 
  
   
  msi.description 
  description,
   
  msi.inventory_item_id 
  inventory_item_id,
   
  mc.segment1 
  product_family,
   
  mc.segment2 
  product_type
  FROM 
  [EMAIL PROTECTED] 
  mcs,
   
  [EMAIL PROTECTED] 
  mc,
   
  [EMAIL PROTECTED] mic,
   
  [EMAIL PROTECTED] 
  msi
  where 
1=1
  and 
  mc.structure_id = 
  50112
  and 
  mc.segment3 != 
  'SPARE'
  and 
  mc.global_name = 
  'US'
  and 
  mc.enabled_flag = 'Y'
  and 
  mcs.global_name = 
  mc.global_name
  and 
  mcs.category_set_name = 'PROD GROUP'
  and 
  mic.category_set_id = mcs.category_set_id
  and 
  mic.category_id = mc.category_id 
  
  and 
  mic.global_name = 
  mc.global_name
  and 
  mic.organization_id = 1
  and 
  mic.inventory_item_id = msi.inventory_item_id
  and 
  msi.organization_id = mic.organization_id
  and 
  msi.global_name = 
  mc.global_name
  AND 
  msi.auto_created_config_flag = 'N'
  AND 
  msi.item_type IN ('ATO 
  MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST$0','PTO 
  MODEL','SPARE')
  and 
  msi.inventory_item_status_code IN 
  ('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
  
  Please note that the tables referenced are remote 
  tables and Oracle Apps tables and not logging on it is 
  possible.
  Please suggest an appropriate refresh mechanism to see 
  the records even during refresh period.
  
  Thanks in advance.
  
  With Warm 
Regards
  
  
  
  Siddharth 
  Haldankar
  Zensar Technologies 
  Ltd.
  Cisco Systems Inc. 
  
  (Offshore 
  Development 
  Center)
  # : 091 020 
  4128394
  [EMAIL PROTECTED]
  [EMAIL PROTECTED]

Re: New to DBA !

2003-10-21 Thread Rachel Carmichael

is this a Unix box?

if so, log on as the oracle account to the Unix server. Then in
sqlplus, login as follows

sqlplus / as sysdba

this will get you in as the database owner and will allow you to change
the system password 

alter user system identified by your_new_password;

then log out of sqlplus and log in as system to do what you need to


--- Rama, Shreekantha (K.) [EMAIL PROTECTED] wrote:
 Hi All, 
  
 I am new to the world of Oracle.. 
 I am having this issue.. 
  
 I have created a database on Oracle 8.1.7.4.. 
  
 But I am not able to access as the password is lost..
 Now, I am not able to login.. 
 I tried login using system /manager.. but oracle is not
 allowing me to login .. 
  
 What's the solution ?? 
 
 Regards 
 Shreekanth 
 
 
 


__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: using temp tables for staging databases?

This is definitely one for the Hall of [F|Sh]ame! 4608 byte block size! But
how did someone arrive at that number - Typo? Wheel of Fortune? DBMS_RANDOM?

Arup

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 11:19 AM


 Hi, Mark.
 I'm not Tim, but I did encounter such a situation.
 This was not a temp table, but a permanent one.

 We have a db with a very strange block size of 4608
 (actually Tim is painfully aware of this one). We have
 a very large table in this database.  It was expanding
 at about 200 megs per week -- way out of control for a
 relataively small database.

 The database was not reusing blocks.  Oracle
 recommends that  (100% - (pctfree+pcused)) be greater
 than the maximum sie of a row. So we did an exact
 calculation of the blocksize less %free+%used

  1% of a block is 46.08
 80% of a block is 3686.4
 4608 - (46.08 + 3686.4) = 875.52


 our largest row length is 860

 So we set pctfree at 1% and pctused at 80%
 One of the reasons we can get by with this is because
 the vendor designed the database with all char (not
 varchar2), so we pretty much know exactly what each
 row is going to consume. (It's a Cobol app)

 After this change, the database stopped it's wild
 expansion.

 Not a normal situation, but then nothing here is
 normal.  (Kids -- don't try this at home!)

 Barb



 --- Mark Leith [EMAIL PROTECTED] wrote:
  Tim,
 
  Can you sum up a few situations when the need *has*
  arisen to change these
  values?
 
  Cheers
 
  Mark
 
 
 
  -Original Message-
  Tim Gorman
  Sent: 21 October 2003 06:09
  To: Multiple recipients of list ORACLE-L
 
 
  Unless you typo'd, there are some serious problems
  here...
 
  Setting PCTFREE to 99 is not likely to pack in the
  blocks.  Rather the
  opposite;  you are instead leaving blocks 99% empty.
   Quite a bit of wasted
  I/O in performing a FULL table scan here...  :-)
 
  Anyway, it is not a good idea to have PCTFREE and
  PCTUSED sum to a value
  greater than 70 or 80 or so, just as a rule of
  thumb.  Having them sum to a
  value near 100 ensures that each insert, delete, or
  even update will
  potentially cause the block to be removed or
  reinserted to one of the
  segment's free list.  Think about it:  the width of
  a single row crossing
  the boundary from off the free list to on the
  free list.  Better to
  leave a bit of a no man's land between the two
  values.  The default
  settings of PCTFREE=10 and PCTUSED=40 are one of the
  few default settings
  that need little manipulation for most situations.
 
 
 
  on 10/20/03 7:34 AM, [EMAIL PROTECTED] at
  [EMAIL PROTECTED] wrote:
 
   we drop and recreate the temp tables every night.
  We also use PCTFREE
  PCTUSED
   at 99 and 1 to pack in the blocks and we use very
  small extent sizes. then
  we
   analyze with an estimate size of 20 percent which
  is quite fast.
  
   All of them are used for full table scans and do
  not have indexes. Ive
  found
   that a 'create table as' is MUCH faster than
  inserting into global
  temporary
   tables when you do not have to worry about latch
  contention(ie 1-3 users
   logged in at a time).
  
   anyone else notice this? Seems to go against
  conventional wisdom which
  says
   never use them. So I want to make sure Im not
  missing something.
  
   From: Tim Gorman [EMAIL PROTECTED]
   Date: 2003/10/20 Mon AM 10:19:33 EDT
   To: Multiple recipients of list ORACLE-L
  [EMAIL PROTECTED]
   Subject: Re: using temp tables for staging
  databases?
  
   All the time.  Oracle Apps's open interfaces
  are built this way, for
   example.
  
   However, the guys here covered their bases by
  specifying smaller
   temporary tables, as if they could prevent them
  from becoming large.  I
   suppose they might feel that they indemnify
  themselves if the tables
  should
   ever become large?
  
   As with OraApps open interface tables, it is
  when a large volume of
  data
   is pushed through that the trouble starts.  The
  high-water marks on all
   the tables are pushed to a high level, thereafter
  causing full table
  scans
   on the interface/temporary tables to run slowly.
  The only way to bring
  the
   HWM back down is quiesce the interface/app and
  then truncate the tables.
  
  
  
   on 10/20/03 6:39 AM, [EMAIL PROTECTED] at
  [EMAIL PROTECTED] wrote:
  
   This is for non-transactional data load
  instances. The guys here sware
  that
   by
   using smaller temporary tables(not global temp
  tables) they can increase
  the
   speed of the data loads.
  
   Not worried about latch contention because its
  just for bulk loads. I
  know
   this bad in transactional instances. Has anyone
  used these in
   non-transactional data load instances?
  
   --
   Please see the official ORACLE-L FAQ:
  http://www.orafaq.net
   --
   Author: Tim Gorman
 INET: [EMAIL PROTECTED]
  
   Fat City Network Services-- 858-538-5051
  http://www.fatcity.com

ORA-02049: timeout: distributed transaction waiting for lock

2003-10-21 Thread David Boyd

Hi List,

We have a job that copies data in a table on a remote database to a local 
database through a database link.  Here are the steps in the job:

1. truncate the table of t1 on the local database
2. insert into t1 select * from [EMAIL PROTECTED]
3. commit
There are only 847 records in the table.  The job completes in 1 sec 
normally.  However, last Sunday we got ORA-02049: timeout: distributed 
transaction waiting for lock during commit process.  As my understanding, 
the error comes from a DML statement that requires locks on a remote 
database can be blocked if another transaction own locks on the requested 
data.  I'm pretty sure that there were no any activities on the remote 
database since the application was not open.  Also I can see from the log 
file (see below) that 847 records were inserted into the t1 table on the 
local database.  The error was generated during the commit process.  Does 
any one have any comments?  Thanks for any input.

Here is the job log file:
847 rows created.
commit
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock
We are in Oracle 8.1.7.4 and SunOS 5.8.  We take the default value for 
DISTRIBUTED_LOCK_TIMEOUT .

Dave

_
Get a FREE computer virus scan online from McAfee. 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Boyd
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Data Modeling

2003-10-21 Thread Gints Plivna

I think the main task here is practise, practise and more practise.
Prefferable under supervision of a more experienced person.
Some more books I'v read and found useful
Data Model Patterns: Conventions of Thought
http://www.amazon.com/exec/obidos/ASIN/0932633293/qid=1066746321/sr=2-1/
ref=sr_2_1/102-9109544-4984930
Case*Method: Entity Relationship Modelling
http://www.amazon.com/exec/obidos/tg/detail/-/0201416964/qid=1066747540/
sr=1-1/ref=sr_1_1/102-9109544-4984930?v=glances=books
The Data Model Resource Book: A Library of Logical Data and Data
Warehouse Models
http://www.amazon.com/exec/obidos/tg/detail/-/0471153664/qid=1066747769/
sr=1-8/ref=sr_1_8/102-9109544-4984930?v=glances=books

Gints


 -Original Message-
 From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, October 21, 2003 6:14 PM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: Data Modeling
 
 B. S.
 I am no expert at data modeling but have made some modest attempts
in
 that direction. First, what is your objective? Casual or intense?
 I have heard that data modeling is not learned from a book, but
you
 need
 to take a class. The classes I took were so long ago and I didn't do
 anything with it so I can't vouch for that.
 If you are need to review the data models of others, get the book
The
 Data Modeling Handbook by  Reingruber and Gregory. There is nothing
like
 it. The authors give  will sound really profound and if you get
someone
 that
 wants to argue, you can point them to the book.
 There is a data modeling email list where you could get better
answers
 for this and other questions: DataModel.Org Mailing List Here are the
 removal instructions, I'm sure you can figure out how to add yourself.
  To be removed from this list, please send an E-mail to
  mailto:[EMAIL PROTECTED] in the Subject line, put REMOVE
 {your
  email address}.
 
 Dennis Williams
 DBA
 Lifetouch, Inc.
 [EMAIL PROTECTED]
 -Original Message-
 Sent: Tuesday, October 21, 2003 7:54 AM
 To: Multiple recipients of list ORACLE-L
 
 
 
 Hi All
 
 I have not done data modelling as a major task so far. Would like to
know
 from where can I make a good start. I have basic(conceptual) knowledge
of
 ER-Diagram and Normalization. But if anybody can give me some case
 studies/links etc.,
 
 I see the responsibilities would include modelling and coping with
 Frequent
 changes of the database object structures.
 
 Please Help
 
 Thanks a Lot
 
 Regards
 B S Pradhan
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: DENNIS WILLIAMS
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
-- 
Author: Gints Plivna
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: using temp tables for staging databases?

2003-10-21 Thread Barbara Baker

Too many drugs?
Not enough??

Here's an exact quote from the vendor -- they placed
this line in our init file.  Sadly, they did not plan
for any overhead . . .

(the app was installed before we had an oracle db on
board)

###
# The db_block_size is set at 9 multiples of 512
bytes(OpenVMS block size)
# This is to accomodate the WO table. The average row
length of the WO table is
# 900 bytes. A 4608 parameter allows 5 rows to be
stored in a single Oracle bloc
k
# Do not change without consulting NWI!

--- Arup Nanda [EMAIL PROTECTED] wrote:
 This is definitely one for the Hall of [F|Sh]ame!
 4608 byte block size! But
 how did someone arrive at that number - Typo? Wheel
 of Fortune? DBMS_RANDOM?
 
 Arup
 
 - Original Message - 
 To: Multiple recipients of list ORACLE-L
 [EMAIL PROTECTED]
 Sent: Tuesday, October 21, 2003 11:19 AM
 
 
  Hi, Mark.
  I'm not Tim, but I did encounter such a situation.
  This was not a temp table, but a permanent one.
 
  We have a db with a very strange block size of
 4608
  (actually Tim is painfully aware of this one). We
 have
  a very large table in this database.  It was
 expanding
  at about 200 megs per week -- way out of control
 for a
  relataively small database.
 
  The database was not reusing blocks.  Oracle
  recommends that  (100% - (pctfree+pcused)) be
 greater
  than the maximum sie of a row. So we did an exact
  calculation of the blocksize less %free+%used
 
   1% of a block is 46.08
  80% of a block is 3686.4
  4608 - (46.08 + 3686.4) = 875.52
 
 
  our largest row length is 860
 
  So we set pctfree at 1% and pctused at 80%
  One of the reasons we can get by with this is
 because
  the vendor designed the database with all char
 (not
  varchar2), so we pretty much know exactly what
 each
  row is going to consume. (It's a Cobol app)
 
  After this change, the database stopped it's wild
  expansion.
 
  Not a normal situation, but then nothing here is
  normal.  (Kids -- don't try this at home!)
 
  Barb
 
 
 
  --- Mark Leith [EMAIL PROTECTED] wrote:
   Tim,
  
   Can you sum up a few situations when the need
 *has*
   arisen to change these
   values?
  
   Cheers
  
   Mark
  
  
  
   -Original Message-
   Tim Gorman
   Sent: 21 October 2003 06:09
   To: Multiple recipients of list ORACLE-L
  
  
   Unless you typo'd, there are some serious
 problems
   here...
  
   Setting PCTFREE to 99 is not likely to pack in
 the
   blocks.  Rather the
   opposite;  you are instead leaving blocks 99%
 empty.
Quite a bit of wasted
   I/O in performing a FULL table scan here...  :-)
  
   Anyway, it is not a good idea to have PCTFREE
 and
   PCTUSED sum to a value
   greater than 70 or 80 or so, just as a rule of
   thumb.  Having them sum to a
   value near 100 ensures that each insert, delete,
 or
   even update will
   potentially cause the block to be removed or
   reinserted to one of the
   segment's free list.  Think about it:  the width
 of
   a single row crossing
   the boundary from off the free list to on the
   free list.  Better to
   leave a bit of a no man's land between the two
   values.  The default
   settings of PCTFREE=10 and PCTUSED=40 are one of
 the
   few default settings
   that need little manipulation for most
 situations.
  
  
  
   on 10/20/03 7:34 AM, [EMAIL PROTECTED] at
   [EMAIL PROTECTED] wrote:
  
we drop and recreate the temp tables every
 night.
   We also use PCTFREE
   PCTUSED
at 99 and 1 to pack in the blocks and we use
 very
   small extent sizes. then
   we
analyze with an estimate size of 20 percent
 which
   is quite fast.
   
All of them are used for full table scans and
 do
   not have indexes. Ive
   found
that a 'create table as' is MUCH faster than
   inserting into global
   temporary
tables when you do not have to worry about
 latch
   contention(ie 1-3 users
logged in at a time).
   
anyone else notice this? Seems to go against
   conventional wisdom which
   says
never use them. So I want to make sure Im not
   missing something.
   
From: Tim Gorman [EMAIL PROTECTED]
Date: 2003/10/20 Mon AM 10:19:33 EDT
To: Multiple recipients of list ORACLE-L
   [EMAIL PROTECTED]
Subject: Re: using temp tables for staging
   databases?
   
All the time.  Oracle Apps's open
 interfaces
   are built this way, for
example.
   
However, the guys here covered their bases
 by
   specifying smaller
temporary tables, as if they could prevent
 them
   from becoming large.  I
suppose they might feel that they indemnify
   themselves if the tables
   should
ever become large?
   
As with OraApps open interface tables, it
 is
   when a large volume of
   data
is pushed through that the trouble starts. 
 The
   high-water marks on all
the tables are pushed to a high level,
 thereafter
   causing full table
   scans
on the interface/temporary tables

Re:Re:Re: job opportunity in Dallas

2003-10-21 Thread system manager

Thanks,
--
Original Message
Date: Tue, 21 Oct 2003 08:49:37 -0600

Thanks for the response. I am a DBA, not a developer. Especially, I do not 
have any forms experience. I therefore will not pursue this further.

At 08:42 AM 10/21/2003, you wrote:
based on the requirements below, please write a 1-paragraph bio
summarizing how your skills and experience match these specs. Please
include
the number of years that you have worked in the various skill areas listed
below. (This will help to speed up the submittal process).  Here are the
specs:

Sr. Oracle Developer - Financials (core; AR, AP, GL a must) , v11i

Overall IT Industry experience minimum of 5 - 7 years, with at least 3 - 5
years of Oracle Financials

Experience with Forms (4.5/6/6i) and Reports (3.0/6/6i) is critical
Strong PL/SQL
SQL*Plus
Developer 2000
Oracle Tools or client products
TCP/IP
Unix scripting (Perl, Bourne, Korn)
Technical Documentation
Unit Testing

DBA experience required

Must have excellent communication skills, confidence in abilities, and be
able to interact with customers and users.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com 




_
Free email with personality! Over 200 domains!
http://www.MyOwnEmail.com
Looking for friendships,romance and more?
http://www.MyOwnFriends.com

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Refresh option for Materialized view , want to use it during refresh



Siddharth,I will offer a slightly out-of-the-box 
solution. Please read it through till the end to determine its applicability in 
your case.It seems yours refresh interval is once a day and you don't 
mind stale data for a max of 24 hours. You also refresh is complete, not 
incremental. So, I would suggest the follwoing approach.(1) Create a 
table firstCREATE TABLE CT_PRODUCTID_VW 
TABLESPACE NOLOGGINGASSELECT .(2) When 
you are ready to "refresh", drop the MVDROP 
MATERIALIZED VIEW CT_PRODUCTID_VW;(3) Create the MV with the 
PREBUILT TABLE option.CREATE MATERIALIZED VIEW 
CT_PRODUCTID_VW BUILD IMMEDIATEREFRESH START WITH SYSDATENEXT 
(SYSDATE + 1)ON PREBUILT TABLEAS SELECT 
msi.segment1 
productid, Your MV is not 
accessible between STEP 2 and STEP3, which is really a dictionary update and 
takes about a second or so. So the "outage" is really 1 second, not 1/2 
hr.A few explanations are in order here.(1) Creating an MV on a 
Prebuilt Table does not consume more space. The segment that used to be a table 
simply becomes an MV.(2) When you drop the MV, the MV is gone, but the table 
remains instact.(3) The table can be create by any means - export/import, 
SQL*Loader, INSERT APPEND, etc.(4) IT places less strain on the system 
comapred to the MV refresh option, simply because the MV refresh truncates the 
segment and then builds it.I presented a paper to the same effect at 
IOUG Live 2003. You can download a modified versionof the same from my 
website www.proligence.com/downlaods.html, 
titled "Painless Master Table Alter" from the Presentations 
Section.HTH.Arup Nanda- Original 
Message - From: Siddharth Haldankar To: Multiple recipients of list 
ORACLE-L Sent: Tuesday, October 21, 2003 3:59 AMSubject: Refresh option 
for Materialized view , want to use it during refreshHi 
Gurus,I have a materialized view, which is based on Oracle Apps tables 
and on remote database. The view refresh takes around ½ hour, during this time 
period I cannot see any records in the materialized view and therefore my 
application faces errors.The following is the view definitionCREATE 
MATERIALIZED VIEW CT_PRODUCTID_VW BUILD IMMEDIATEREFRESH START WITH 
SYSDATENEXT (SYSDATE + 1)AS SELECT 
msi.segment1 
productid, 
 
msi.description 
description, 
msi.inventory_item_id 
inventory_item_id, 
mc.segment1 
product_family, 
mc.segment2 
product_typeFROM [EMAIL PROTECTED] 
mcs, 
[EMAIL PROTECTED] 
mc, 
[EMAIL PROTECTED] 
mic, 
[EMAIL PROTECTED] msiwhere 
1=1and mc.structure_id 
= 50112and 
mc.segment3 != 
'SPARE'and 
mc.global_name = 
'US'and mc.enabled_flag = 
'Y'and mcs.global_name = 
mc.global_nameand mcs.category_set_name = 'PROD 
GROUP'and mic.category_set_id = 
mcs.category_set_idand 
mic.category_id = mc.category_id 
and mic.global_name = 
mc.global_nameand mic.organization_id = 
1and mic.inventory_item_id = 
msi.inventory_item_idand msi.organization_id = 
mic.organization_idand 
msi.global_name = 
mc.global_nameAND msi.auto_created_config_flag = 
'N'AND 
msi.item_type IN ('ATO 
MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST$0','PTO 
MODEL','SPARE')and msi.inventory_item_status_code IN 
('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')Please note that the 
tables referenced are remote tables and Oracle Apps tables and not logging on it 
is possible.Please suggest an appropriate refresh mechanism to see the 
records even during refresh period.Thanks in advance.With Warm 
RegardsSiddharth HaldankarZensar Technologies Ltd.Cisco 
Systems Inc. (Offshore Development Center)# : 091 020 
4128394[EMAIL PROTECTED][EMAIL PROTECTED]

RE: Can I concatenate several rows without a procedure?

2003-10-21 Thread Bob Metelsky

select username||user_id||' freestyle!! '|| created as concat from
all_users;


Hello,
I am trying to concatenate several records with simple sql.  Is this
possible?


-- 
Thanks,
Jake Johnson
[EMAIL PROTECTED]

__
Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on
Rims, Tires, and Wheel Packages.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Bob Metelsky
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: WHERE 1 = 1 (any info on this)

2003-10-21 Thread Jamadagni, Rajendra

I have seen people use this in pseudo-dynamic sql in a beast called SQR. Especially 
when they have code like ...

select ...
union 
select ...
union
select ...
union 
select ... limit to your imagination.

Lot of people think that using 1=1 in absence of a valid limiting condition will let 
them evaluate (and get data from) a union clause where as putting 1=2 will help them 
avoid ...

I don't hate just because I don't like SQR, I don't like it because until CBO 
encounters and evaluates 1=2 clause, it still does all the work of selecting etc and 
that is just a waste.

Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


**
This e-mail message is confidential, intended only for the named recipient(s) above 
and may contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank you.
**5
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: New to DBA !

2003-10-21 Thread Rama, Shreekantha (K.)

No ! this is is on Windows 2000.. 

Warm Regards
Shreekanth

Satyam Computer Services Ltd 
BSAQ Project 
Dearborn, MI 
( (313) 206 9132 
* [EMAIL PROTECTED]



-Original Message-
Sent: Tuesday, October 21, 2003 11:45 AM
To: Multiple recipients of list ORACLE-L


is this a Unix box?

if so, log on as the oracle account to the Unix server. Then in
sqlplus, login as follows

sqlplus / as sysdba

this will get you in as the database owner and will allow you to change
the system password 

alter user system identified by your_new_password;

then log out of sqlplus and log in as system to do what you need to


--- Rama, Shreekantha (K.) [EMAIL PROTECTED] wrote:
 Hi All, 
  
 I am new to the world of Oracle.. 
 I am having this issue.. 
  
 I have created a database on Oracle 8.1.7.4.. 
  
 But I am not able to access as the password is lost..
 Now, I am not able to login.. 
 I tried login using system /manager.. but oracle is not
 allowing me to login .. 
  
 What's the solution ?? 
 
 Regards 
 Shreekanth 
 
 
 


__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Rama, Shreekantha (K.)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

unique index

2003-10-21 Thread David Boyd

Hi List,

We have a job that appends records to a table using SQL Loader 
(DIRECT=TRUE).  The table has two unique indexes (no constraints).  Last 
Sunday, the job loaded 11839 records into the table successfully, but the 
one of the unique indexes became unusable for unknown reason.  I dropped the 
unusable index and recreated it.  The index became valid.  Then the 
developer reran the job and loaded the same 11839 records into the table (at 
that time we did not know the first run already loaded the records).  Of 
course, two unique indexes became unusable again.  I could not recreate the 
unique indexes due to the duplicate keys found.  Finally, I deleted all of 
23678 newly loaded records, recreated the unique indexes, and reloaded the 
11839 records.  Every thing is fine now.  Here are my questions:

1. Why the same data crashed the index at the first time, but not at the end
2. After I recreated the unique index at the first time, those records were 
already in the table.  Why did not the unique index complain for the 
duplicates when we reloaded the same 11839 records into the table?

Dave

_
Send and receive larger attachments with Hotmail Extra Storage.   
http://join.msn.com/?PAGE=features/es

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Boyd
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Data Modeling

I would read some of C.J. Date's papers, or books from his Relational
Database Writings series. Also, there is a recent book called Data
Modeling for Everyone by Sharon Allen (Curlingstone Press) which is good. 

Most importantly, understand the fundamental principles of relational theory
as it pertains to relational databases.  If you make an effort at this
you'll be ahead of 90% of developers/DBAs in this area, in my opinion. I've
heard database experts say that relational databases are called that
because they relate one table to another. This is false. It is called
relational because it is based on relational math, and because columns are
grouped together into special relations called relational tables. We call
them tables for short. 

The important thing to note here is this: the relationship that matters most
is the relationship among the columns of the SAME TABLE. That they really do
belong together is the most important thing to be sure of in data modeling.
They need to be functionally dependent on the same set of primary key
columns. Functional dependency is hugely important to understand and is the
basis of good data modeling.

Some authors:

C.J. Date
Fabian Pascal
Sharon Allen

many others as well.

HTH,

Michael Milligan
Oracle DBA
Ingenix, Inc.
2525 Lake Park Blvd.
Salt Lake City, Utah 84120
wrk 801-982-3081
mbl 801-628-6058
[EMAIL PROTECTED]
 


This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Milligan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: New to DBA !

2003-10-21 Thread AK

why one can't  do same on widoz as well  ? 
works well .

-ak


- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 8:44 AM


 is this a Unix box?
 
 if so, log on as the oracle account to the Unix server. Then in
 sqlplus, login as follows
 
 sqlplus / as sysdba
 
 this will get you in as the database owner and will allow you to change
 the system password 
 
 alter user system identified by your_new_password;
 
 then log out of sqlplus and log in as system to do what you need to
 
 
 --- Rama, Shreekantha (K.) [EMAIL PROTECTED] wrote:
  Hi All, 
   
  I am new to the world of Oracle.. 
  I am having this issue.. 
   
  I have created a database on Oracle 8.1.7.4.. 
   
  But I am not able to access as the password is lost..
  Now, I am not able to login.. 
  I tried login using system /manager.. but oracle is not
  allowing me to login .. 
   
  What's the solution ?? 
  
  Regards 
  Shreekanth 
  
  
  
 
 
 __
 Do you Yahoo!?
 The New Yahoo! Shopping - with improved product search
 http://shopping.yahoo.com
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Rachel Carmichael
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
-- 
Author: AK
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: re Rebuilding Indexes in Oracle Apps -- was RE: RE:

2003-10-21 Thread Cary Millsap

Oops, I didn't see that part. Thanks for the catch, Hemant.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- SQL Optimization 101: 12/8-12 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...


-Original Message-
Hemant K Chitale
Sent: Tuesday, October 21, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L


Unfortunately, the lines
Unoccupied space on indexes occurs when a key value changes, and
the 
index
 row is deleted from one place (Leaf Block) and inserted into
another.
 Deleted Leaf Rows are not reused.  Therefore, indexes whose columns
are
 subject to intensive value change should be rebuilt periodically,
since
 they become naturally fragmentated. 
are still visible in Note 182699.1

Hemant

At 08:29 AM 20-10-03 -0800, you wrote:
Fyi, Oracle updated note 182699.1 last Friday. The inaccurate
statements
about index fragmentation have been removed.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- SQL Optimization 101: 12/8-12 Dallas
- Hotsos Symposium 2004: March 7-10 Dallas
- Visit www.hotsos.com for schedule details...


-Original Message-
Richard Foote
Sent: Friday, October 17, 2003 6:29 AM
To: Multiple recipients of list ORACLE-L
Separate

Hi Hemant,

One word perfectly describes the Metalink article you highlighted:

Crap ;)

A nice example of  how Oracle Corp is the greatest myth generator of
them
all !! It's all rather sad and embarressing isn't.

Thanks for the headsup. Anyone in a position to get the note removed ?

Cheers

Richard

 Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds
Fragmentated Indexes (8.0-9.0)
 
 Index fragmentation occurs when a key value changes, and the index
row
is
 deleted from one place (Leaf Block) and inserted into another.
 
  Deleted Leaf Rows are not reused. Therefore indexes whose columns
are
  subject to value change must be rebuilt periodically since they
become
naturally fragmentated.
 
  An index is considered to be 'fragmentated' when more than 20% of
its
Leaf
Rows space is
 empty because of the implicit deletes caused by indexed columns value
changes.
 
  Fragmentated indexes degrade the performance of index range scan
operations.


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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Cary Millsap
   INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :  http://hkchital.tripod.com


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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Cary Millsap
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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:

RE: using temp tables for staging databases?

I believe Oracle will round that block size off.  What I would not be sure of is what 
Oracle did during database creation.  I believe it should have gone with an 8K (8192 
bytes) block size since the specified size of 4608 Bytes is above a 4K (4096 Bytes) 
block size.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-Original Message-
Sent: Tuesday, October 21, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L


Too many drugs?
Not enough??

Here's an exact quote from the vendor -- they placed
this line in our init file.  Sadly, they did not plan
for any overhead . . .

(the app was installed before we had an oracle db on
board)

###
# The db_block_size is set at 9 multiples of 512
bytes(OpenVMS block size)
# This is to accomodate the WO table. The average row
length of the WO table is
# 900 bytes. A 4608 parameter allows 5 rows to be
stored in a single Oracle bloc
k
# Do not change without consulting NWI!

--- Arup Nanda [EMAIL PROTECTED] wrote:
 This is definitely one for the Hall of [F|Sh]ame!
 4608 byte block size! But
 how did someone arrive at that number - Typo? Wheel
 of Fortune? DBMS_RANDOM?
 
 Arup
 
 - Original Message - 
 To: Multiple recipients of list ORACLE-L
 [EMAIL PROTECTED]
 Sent: Tuesday, October 21, 2003 11:19 AM
 
 
  Hi, Mark.
  I'm not Tim, but I did encounter such a situation.
  This was not a temp table, but a permanent one.
 
  We have a db with a very strange block size of
 4608
  (actually Tim is painfully aware of this one). We
 have
  a very large table in this database.  It was
 expanding
  at about 200 megs per week -- way out of control
 for a
  relataively small database.
 
  The database was not reusing blocks.  Oracle
  recommends that  (100% - (pctfree+pcused)) be
 greater
  than the maximum sie of a row. So we did an exact
  calculation of the blocksize less %free+%used
 
   1% of a block is 46.08
  80% of a block is 3686.4
  4608 - (46.08 + 3686.4) = 875.52
 
 
  our largest row length is 860
 
  So we set pctfree at 1% and pctused at 80%
  One of the reasons we can get by with this is
 because
  the vendor designed the database with all char
 (not
  varchar2), so we pretty much know exactly what
 each
  row is going to consume. (It's a Cobol app)
 
  After this change, the database stopped it's wild
  expansion.
 
  Not a normal situation, but then nothing here is
  normal.  (Kids -- don't try this at home!)
 
  Barb
 
 
 
  --- Mark Leith [EMAIL PROTECTED] wrote:
   Tim,
  
   Can you sum up a few situations when the need
 *has*
   arisen to change these
   values?
  
   Cheers
  
   Mark
  
  
  
   -Original Message-
   Tim Gorman
   Sent: 21 October 2003 06:09
   To: Multiple recipients of list ORACLE-L
  
  
   Unless you typo'd, there are some serious
 problems
   here...
  
   Setting PCTFREE to 99 is not likely to pack in
 the
   blocks.  Rather the
   opposite;  you are instead leaving blocks 99%
 empty.
Quite a bit of wasted
   I/O in performing a FULL table scan here...  :-)
  
   Anyway, it is not a good idea to have PCTFREE
 and
   PCTUSED sum to a value
   greater than 70 or 80 or so, just as a rule of
   thumb.  Having them sum to a
   value near 100 ensures that each insert, delete,
 or
   even update will
   potentially cause the block to be removed or
   reinserted to one of the
   segment's free list.  Think about it:  the width
 of
   a single row crossing
   the boundary from off the free list to on the
   free list.  Better to
   leave a bit of a no man's land between the two
   values.  The default
   settings of PCTFREE=10 and PCTUSED=40 are one of
 the
   few default settings
   that need little manipulation for most
 situations.
  
  
  
   on 10/20/03 7:34 AM, [EMAIL PROTECTED] at
   [EMAIL PROTECTED] wrote:
  
we drop and recreate the temp tables every
 night.
   We also use PCTFREE
   PCTUSED
at 99 and 1 to pack in the blocks and we use
 very
   small extent sizes. then
   we
analyze with an estimate size of 20 percent
 which
   is quite fast.
   
All of them are used for full table scans and
 do
   not have indexes. Ive
   found
that a 'create table as' is MUCH faster than
   inserting into global
   temporary
tables when you do not have to worry about
 latch
   contention(ie 1-3 users
logged in at a time).
   
anyone else notice this? Seems to go against
   conventional wisdom which
   says
never use them. So I want to make sure Im not
   missing something.
   
From: Tim Gorman [EMAIL PROTECTED]
Date: 2003/10/20 Mon AM 10:19:33 EDT
To: Multiple recipients of list ORACLE-L
   [EMAIL PROTECTED]
Subject: Re: using temp tables for staging
   databases?
   
All the time.  Oracle Apps's open
 interfaces
   are built this way, for
example.
   
However, the guys here covered their bases
 by
   specifying smaller
temporary tables, as if they could

Re: ORA-02049: timeout: distributed transaction waiting for lock

David,

Take a look at Note 19332.1, which explains the error and what to do next.

In short, the essence of the note is: The error comes if the time waited is
mor than the value of the distributed_lock_timeout parameter. Even if you do
a select from the remote database, it acquires a TX lock and that can wait.
Increase the value of the timeout or, just use an exception handler on the
commit statement to retry.

HTH.

Arup Nanda

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 11:49 AM


 Hi List,

 We have a job that copies data in a table on a remote database to a local
 database through a database link.  Here are the steps in the job:

 1. truncate the table of t1 on the local database
 2. insert into t1 select * from [EMAIL PROTECTED]
 3. commit

 There are only 847 records in the table.  The job completes in 1 sec
 normally.  However, last Sunday we got ORA-02049: timeout: distributed
 transaction waiting for lock during commit process.  As my understanding,
 the error comes from a DML statement that requires locks on a remote
 database can be blocked if another transaction own locks on the requested
 data.  I'm pretty sure that there were no any activities on the remote
 database since the application was not open.  Also I can see from the log
 file (see below) that 847 records were inserted into the t1 table on the
 local database.  The error was generated during the commit process.  Does
 any one have any comments?  Thanks for any input.

 Here is the job log file:
 847 rows created.

 commit
 *
 ERROR at line 1:
 ORA-02049: timeout: distributed transaction waiting for lock

 We are in Oracle 8.1.7.4 and SunOS 5.8.  We take the default value for
 DISTRIBUTED_LOCK_TIMEOUT .

 Dave

 _
 Get a FREE computer virus scan online from McAfee.
 http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: unique index

Dave,

If memory is functioning normally:  When you use direct=y in Sql*Loader it 
flags all of your indexes as invalid and then revalidates/rebuilds then when the load 
is complete.  The reason is that loading data is faster when you don't have to parse 
index entries all the time and an invalid index does not need to be maintained.  It 
would appear from your message that something caused the one index to not validate 
during the Sql*Loader run.  Why might be revealed in the loader's log file.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-Original Message-
Sent: Tuesday, October 21, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L


Hi List,

We have a job that appends records to a table using SQL Loader 
(DIRECT=TRUE).  The table has two unique indexes (no constraints).  Last 
Sunday, the job loaded 11839 records into the table successfully, but the 
one of the unique indexes became unusable for unknown reason.  I dropped the 
unusable index and recreated it.  The index became valid.  Then the 
developer reran the job and loaded the same 11839 records into the table (at 
that time we did not know the first run already loaded the records).  Of 
course, two unique indexes became unusable again.  I could not recreate the 
unique indexes due to the duplicate keys found.  Finally, I deleted all of 
23678 newly loaded records, recreated the unique indexes, and reloaded the 
11839 records.  Every thing is fine now.  Here are my questions:

1. Why the same data crashed the index at the first time, but not at the end
2. After I recreated the unique index at the first time, those records were 
already in the table.  Why did not the unique index complain for the 
duplicates when we reloaded the same 11839 records into the table?

Dave

_
Send and receive larger attachments with Hotmail Extra Storage.   
http://join.msn.com/?PAGE=features/es

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Goulet, Dick
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: using temp tables for staging databases?

All that is nice, but from my practice so far, by far the most
frequent cause of the buffer busy waits id DBWR being unable to catch
up. This can come as a consequence of several things:
- Poorly written transaction that modifies thousands of blocks during
 peak time hours. Typical example is bill generation, which generates
 the table from which the bills are printed, and it'usually done
 during peak hours. It generally slows down everybody else, causes
 a lot of screaming and cannot be resolved by increasing the cache hit
 ratio. Moving bill generation to operational data store, combined
 with replication and spreading the load over a period of time can
 solve these. Alternative solution is not available ever since Richard
 Kuklinsky, the Ice Man, is off the market.
- Slow peripherals and insufficient I/O bandwidth, usually caused by
 magazine reading PHB. DBA needs to develop a healthy cynical attitude
 and desperately try spreading the workload throughout the 24 hours
 and all 7 days in a week. Disproportionately high number of these
 sites are running windoze and are easily recognized when the IT
 manager tells you about the wonderful Matrox Millennium card that
 he has in the database server and quotes the number of OpenGL
 operations his new database server can do.
- Very high transaction rates and inability of the CPUs to handle the
 load. In this case there are so many transactions that DBWR is unable
 to catch up. That happens when the system is in desperate need of a
 good upgrade. This usually happens in places where the system is
 stabilized and the business users say that they have what they need
 and that no major work should be done on the boxes. Candidates are
 sites which are running things like 7.3.4 and 8.0.6 today. Of course,
 when an upgrade actually is needed, panic spreads and hit and run
 consultants are brought in to make things worse.


On 10/21/2003 11:19:32 AM, Arup Nanda wrote:
Binley,

The cause of Buffer Busy Waits (BBW) is not exclusively the setting  
of
PCTUSED and PCTFREE; they just two of the causes. To understand the
connection, let me explain a little bit on the cause of BBWs.

When a session requests some data element from a table, the server
process of the session gets the block from the disk to the cache
(assume the block is not present in the cache). The event of the  
block
coming from the disk to occupy a buffer in the caceh is pretty
straight forward. Now, imagaine, at the exact same time another
session selects a row from the same block. A *different* row but from
the *same* block. That session will search the cache buffer chain and
see that the buffer is not present and will attempt the same
maneuevre, i.e. get the buffer from the disk. However, the first
session is currently moving the buffer; the second session has to
*wait* till the process is complete. This wait is known as buffer  
busy
wait (BBW); but I guess you already knew that. The two sessions are
not in conflict over the same row, but the same buffer; so it's not
locking contention.

How can we eliminate BBWs? Unfortunately we can't bring it to zero.
There is always a probability that two sessions will try to get the
same block. The only exception is when a block contains only one row.
In that case the sessions will select different blocks for different
rows. Again, this is not practical.
We can reduce BBW by reducing the *possibility* that two sessions  
will
not try to access the same block. This can be done using several  
ways:

(1) reducing the block size
(2) making a block less compact, so that each block holds less number
of rows. The fewer the number of rows in a block, the lesser the
probability that two sessions will access rows in the same block.
The first option is not a very practical one in most cases. The  
second
option is. It can be effected by allocating less space in a block,
which can be done by using a large value of PCTFREE, e.g. 40 and/or
small value of PCTUSED, such as 40, instead of 99. Other ways to
achieve the same result is using a higher value of INITRANS, or
anything that will cause less number of rows to fill up a block. Less
rows = less chance of BBW occuring.

I wrote a paper in Select Journal a few months ago explaining this
very situation. Although the article is on Segment Level Statistics,
it has an example which you can simulate to see the effect of
PCTFREE/PCTUSED/INITRANS on Buffer Busy Waits. It can be downlaoded
from my website at www.proligence.com/downloads.html and choose New
Tool on the Block - Segment Level Statistics. Please feel free to  
give
it a whirl.

Further qualifying the case for higher PCTUSED and lower PCTFREE in
datawarehouse environments, the chance that two sessions will access
the row in same block is much less in DW than in OLTP. Hence the
values can be different in DW.
HTH.

Arup Nanda

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 10:24 AM

 I'm unclear how

Re: New to DBA !

2003-10-21 Thread Thomas Day


It works if you precede it with

SET ORACLE_SID=yourinstancename

(Windoz uses registry settings, not environmental variables)

and run sqlplus from the command line (not the GUI).

Also, the Oracle owner is the ADMINISTRATOR userid (at least on my boxes).

Of course, your milage may vary.



   

  AK oramagic   

  @hotmail.comTo:  Multiple recipients of list 
ORACLE-L [EMAIL PROTECTED]
  Sent by: cc: 

  ml-errorsSubject: Re: New to DBA !   

   

   

  10/21/2003 12:34 

  PM   

  Please respond   

  to ORACLE-L  

   

   





why one can't  do same on widoz as well  ?
works well .

-ak


- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 8:44 AM


 is this a Unix box?

 if so, log on as the oracle account to the Unix server. Then in
 sqlplus, login as follows

 sqlplus / as sysdba

 this will get you in as the database owner and will allow you to change
 the system password

 alter user system identified by your_new_password;

 then log out of sqlplus and log in as system to do what you need to


 --- Rama, Shreekantha (K.) [EMAIL PROTECTED] wrote:
  Hi All,
 
  I am new to the world of Oracle..
  I am having this issue..
 
  I have created a database on Oracle 8.1.7.4..
 
  But I am not able to access as the password is lost..
  Now, I am not able to login..
  I tried login using system /manager.. but oracle is not
  allowing me to login ..
 
  What's the solution ??
 
  Regards
  Shreekanth
 
 
 


 __
 Do you Yahoo!?
 The New Yahoo! Shopping - with improved product search
 http://shopping.yahoo.com
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Rachel Carmichael
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
--
Author: AK
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Thomas Day
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message

object compilation scripts

2003-10-21 Thread Seema Singh

Hi,
Can someone send all object compilation script?
Thx
-Seema
_
Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet 
Service.  Try it FREE for one month!   http://join.msn.com/?page=dept/dialup

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Seema Singh
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: object compilation scripts

2003-10-21 Thread Tim Gorman

Seema,

SQL*Plus script gen_recompile.sql at
http://www.EvDBT.com/tools.htm;.  It's the eighth one in
the list...

-Tim

 Hi,
 Can someone send all object compilation script?
 Thx
 -Seema
 
 __
 ___ Enjoy MSN 8 patented spam control and more with
 MSN 8 Dial-up Internet  Service.  Try it FREE for one
 month!   http://join.msn.com/?page=dept/dialup 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Seema Singh
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- 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.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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_block_lru_latches and servers with multiple instances

2003-10-21 Thread Jared . Still

As someone has pointed out to me, latch contention is a consumer
of CPU. If you are familiar with Oracle's process of spinning on a
latch, you will realize that while waiting for a latch, the CPU will 'spin',
rather than go to the expense of doing a context switch and moving
on to something else, then coming back to try and grap the latch.

This is in the belief that the latch will be released soon, which it
is in most cases.

Resist all inclinations to modify _spin_count: the solution is still
to reduce the latch contention.

Jared

[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/20/2003 06:59 PM
Please respond to ORACLE-L

To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: db_block_lru_latches and servers with multiple instances

Interesting question.

Some initial thoughts on that are that latches don't actually consume
much CPU. In a poorly written app (or in the extremely rare event
of a database bug, but probability of that is so low as to not be
worth discussing) you may encounter latch contention that will cause
your application to run very slowly, but not actually use too much
cpu in the process.

Rather than worry about latch contention on multiple databases, you
might want to watch for excessive LIO's ( which do consume cpu), and
consider the worst case load for each database in terms of physical
IO, CPU and memory (it seems that saying RAM has become politically
incorrect, as you are really dealing with virtual memory. but you really do
want your databases to fit in RAM), and determine if your server is up
to the task.

Don't forget to consider the IO generated by backups.

Notice I didn't say anything about db_block_lru_latches. You can rarely
tune problems away by twisting the knobs.

Now watch someone more knowledgable chime in and make me
wish I hadn't replied.

Jared

[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/20/2003 07:39 AM
Please respond to ORACLE-L

To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:db_block_lru_latches and servers with multiple instances

We have some servers with 6-8 instances. These are typically staging instances and maybe 1 low transaction production instance.

We keep multiple instances on one server strictly for cost. Licensing additional servers would be prohibitively expensive so we bought higher end servers and stacked them with multiple instances.

My concern here is with latch contention. I have read that its often best to have db_block_lru_latches set to 2 *CPU. We have 4 CPUs on each server and are an NAS hard disk array from network appliances.

Do I have to take into consideration the additional instances on the server when considering latch contention?

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

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: object compilation scripts

2003-10-21 Thread Stephen.Lee


connect / as sysdba

@?/rdbms/admin/utlrp

 -Original Message-
 
 
 Hi,
 Can someone send all object compilation script?
 Thx
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

Performance tuning book

Cary,

I don't mean to ask you to brag, but can you please tell me if your new
book, of which I've heard good things, is different in any way than other
Oracle Performance Tuning books out. Does it take a different approach? Does
it
teach different methodologies? Is it more readable? I'd be very interested
in your own assessment. What did you try to accomplish with this book?

TIA,

Michael Milligan
Oracle DBA
Ingenix, Inc.
2525 Lake Park Blvd.
Salt Lake City, Utah 84120
wrk 801-982-3081
mbl 801-628-6058
[EMAIL PROTECTED]


This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Milligan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: WHERE 1 = 1 (any info on this)

2003-10-21 Thread Jared . Still


We looked at SQR in 1994.  We chose Perl instead, it was much
more flexible. The fact that Perl was free didn't have anything to
do with the decision.

Perl was just much more capable for data processing and reporting.

Jared








Mercadante, Thomas F [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/21/2003 10:24 AM
Please respond to ORACLE-L


To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: WHERE 1 = 1 (any info on this)


Raj,

What's wrong with SQR? I used it for a few years and found it great.
Especially for batch processing (both report writing and batch updating).
We chose it when we ran away from Cobol about 9 years ago. At the time, our
choices were Oracle Rpt (can you say RPG?), the very first version of Oracle
Reports (boy, did that ever suck) and staying with Cobol.

We were very happy with our choice. it is still the tool used by Banner
(College Erp software).

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Tuesday, October 21, 2003 12:14 PM
To: Multiple recipients of list ORACLE-L


I have seen people use this in pseudo-dynamic sql in a beast called SQR.
Especially when they have code like ...

select ...
union 
select ...
union
select ...
union 
select ... limit to your imagination.

Lot of people think that using 1=1 in absence of a valid limiting condition
will let them evaluate (and get data from) a union clause where as putting
1=2 will help them avoid ...

I don't hate just because I don't like SQR, I don't like it because until
CBO encounters and evaluates 1=2 clause, it still does all the work of
selecting etc and that is just a waste.

Raj


Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



**
This e-mail message is confidential, intended only for the named
recipient(s) above and may contain information that is privileged, attorney
work product or exempt from disclosure under applicable law. If you have
received this message in error, or are not the named recipient(s), please
immediately notify corporate MIS at (860) 766-2000 and delete this e-mail
message from your computer, Thank you.

**5
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
 INET: [EMAIL PROTECTED]

Fat City Network Services  -- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Mercadante, Thomas F
 INET: [EMAIL PROTECTED]

Fat City Network Services  -- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

Shutdown takes 20+ minutes

2003-10-21 Thread Tortorelli, Mary Jo

An 8.1.7.4 production database on HP-UX 11.0 running Apps 11.5.7 takes long to 
shutdown and is causing cluster failover testing to time out.

Upon shutdown immediate it takes 1-3 minutes for the DATABASE DISMOUNTED, DATABASE 
CLOSED, and Archival stopped messages in the alert log but then it takes another 15+ 
minutes for the ORACLE database shutdown message to return to sqlplus.  During this 
time, a ps -ef shows no background or ghost database processes - only the sqlplus 
process.

If a shutdown abort, startup restrict, shutdown is done after five minutes, the 
shutdown after the abort/startup takes 4 minutes total.

Does anyone know what Oracle does between DATABASE CLOSED, ARCHIVE STOPPED and the 
ORACLE database shutdown message?   Has anyone else run into this?

Thanks in advance.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tortorelli, Mary Jo
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: object compilation scripts

2003-10-21 Thread Brian McGraw

I would suggest the $ORACLE_HOME/rdbms/admin/utlrp.sql script, which
recompiles any invalid objects, or the $ORACLE_HOME/rdbms/admin/utlirp.sql
script, which will invalidate and recompile objects for you.

-
| Brian McGraw  -+-  Senior DBA |
| mailto:[EMAIL PROTECTED] |
-

-Original Message-
Seema Singh
Sent: Tuesday, October 21, 2003 11:59 AM
To: Multiple recipients of list ORACLE-L

Hi,
Can someone send all object compilation script?
Thx
-Seema

_
Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet 
Service.  Try it FREE for one month!   http://join.msn.com/?page=dept/dialup

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Brian McGraw
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Refresh option for Materialized view , want to use it during

2003-10-21 Thread Stephen.Lee




Here's a weird 
idea to consider: You might replicate to a dummy table XYZ, then rename 
CT_PRODUCTIED_VW to CRAP; then rename XYZ to CT_PRODUCTIED_VW. Then rename 
CRAP to XYZ. If there are any dependent stored procedures, you will 
probably be required to recompile them. But, if the dependencies aren't 
toohairy,this could be put into a little script that executes in 
about two seconds.

Then there's the 
idea of fiddling with partitions and swapping them in and 
out.

  -Original Message-From: Siddharth Haldankar 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, October 21, 2003 2:59 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  Refresh option for Materialized view , want to use it during 
  refresh
  
  Hi 
  Gurus,
  
  I 
  have a materialized view, which is based on Oracle Apps tables and on remote 
  database. The view refresh takes around ½ hour, during this time period I 
  cannot see any records in the materialized view and therefore my application 
  faces errors.
  The 
  following is the view definition
  
  CREATE 
  MATERIALIZED VIEW 
  CT_PRODUCTID_VW 
  BUILD 
  IMMEDIATE
  REFRESH START WITH 
  SYSDATE
  NEXT (SYSDATE + 
  1)
  AS 
  SELECT
   
  msi.segment1 
  productid, 
  
   
  msi.description 
  description,
   
  msi.inventory_item_id 
  inventory_item_id,
   
  mc.segment1 
  product_family,
   
  mc.segment2 
  product_type
  FROM 
  [EMAIL PROTECTED] 
  mcs,
   
  [EMAIL PROTECTED] 
  mc,
   
  [EMAIL PROTECTED] mic,
   
  [EMAIL PROTECTED] 
  msi
  where 
1=1
  and 
  mc.structure_id = 
  50112
  and 
  mc.segment3 != 
  'SPARE'
  and 
  mc.global_name = 
  'US'
  and 
  mc.enabled_flag = 'Y'
  and 
  mcs.global_name = 
  mc.global_name
  and 
  mcs.category_set_name = 'PROD GROUP'
  and 
  mic.category_set_id = mcs.category_set_id
  and 
  mic.category_id = mc.category_id 
  
  and 
  mic.global_name = 
  mc.global_name
  and 
  mic.organization_id = 1
  and 
  mic.inventory_item_id = msi.inventory_item_id
  and 
  msi.organization_id = mic.organization_id
  and 
  msi.global_name = 
  mc.global_name
  AND 
  msi.auto_created_config_flag = 'N'
  AND 
  msi.item_type IN ('ATO 
  MODEL','CONFIG SPARE','CONFIG SUB','FEATURE PACK','PRODUCT LIST$0','PTO 
  MODEL','SPARE')
  and 
  msi.inventory_item_status_code IN 
  ('ENABLE-MAJ','ENABLE-NON','ENABLE-OPT','NONORD')
  
  Please note that the tables referenced are remote 
  tables and Oracle Apps tables and not logging on it is 
  possible.
  Please suggest an appropriate refresh mechanism to see 
  the records even during refresh period.
  
  Thanks in advance.
  
  With Warm 
Regards
  
  
  
  Siddharth 
  Haldankar
  Zensar Technologies 
  Ltd.
  Cisco Systems Inc. 
  
  (Offshore 
  Development 
  Center)
  # : 091 020 
  4128394
  [EMAIL PROTECTED]
  [EMAIL PROTECTED]

RE: WHERE 1 = 1 (any info on this)

Raj,

What's wrong with SQR?  I used it for a few years and found it great.
Especially for batch processing (both report writing and batch updating).
We chose it when we ran away from Cobol about 9 years ago.  At the time, our
choices were Oracle Rpt (can you say RPG?), the very first version of Oracle
Reports (boy, did that ever suck) and staying with Cobol.

We were very happy with our choice.  it is still the tool used by Banner
(College Erp software).

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Tuesday, October 21, 2003 12:14 PM
To: Multiple recipients of list ORACLE-L


I have seen people use this in pseudo-dynamic sql in a beast called SQR.
Especially when they have code like ...

select ...
union 
select ...
union
select ...
union 
select ... limit to your imagination.

Lot of people think that using 1=1 in absence of a valid limiting condition
will let them evaluate (and get data from) a union clause where as putting
1=2 will help them avoid ...

I don't hate just because I don't like SQR, I don't like it because until
CBO encounters and evaluates 1=2 clause, it still does all the work of
selecting etc and that is just a waste.

Raj


Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



**
This e-mail message is confidential, intended only for the named
recipient(s) above and may contain information that is privileged, attorney
work product or exempt from disclosure under applicable law. If you have
received this message in error, or are not the named recipient(s), please
immediately notify corporate MIS at (860) 766-2000 and delete this e-mail
message from your computer, Thank you.

**5
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: object compilation scripts

Here is the script you requested.
Rem
Rem $Header: utlrp.sql 15-nov-2001.10:56:31 rburns Exp $
Rem
Rem utlrp.sql
Rem
Rem Copyright (c) 1998, 2001, Oracle Corporation.  All rights reserved.
Rem
RemNAME
Rem  utlrp.sql - UTiLity script Recompile invalid Pl/sql modules
Rem
RemDESCRIPTION
Rem This is a fairly general script that can be used at any time to
Rem recompile all existing invalid PL/SQL modules in a database.
Rem
Rem If run as one of the last steps during migration/upgrade/ 
downgrade
Rem (see the README notes for your current release and the Oracle
Rem Migration book), this script  will validate all PL/SQL modules
Rem (procedures, functions, packages, triggers, types, views,  
libraries)
Rem during the migration step itself.
Rem
Rem Although invalid PL/SQL modules get automatically recompiled on  
use,
Rem it is useful to run this script ahead of time (e.g. as one of  
the last
Rem steps in your migration), since this will either eliminate or
Rem minimize subsequent latencies caused due to on-demand automatic
Rem recompilation at runtime.
Rem
Rem Oracle highly recommends running this script towards the end of
Rem of any migration/upgrade/downgrade.
Rem
Rem   NOTES
Rem  * This script must be run using SQL*PLUS.
Rem  * You must be connected AS SYSDBA to run this script.
Rem  * This script expects the following packages to have been  
created with
RemVALID status:
Rem  STANDARD  (standard.sql)
Rem  DBMS_STANDARD (dbmsstdx.sql)
Rem  * There should be no other DDL on the database while running  
the
Remscript.  Not following this recommendation may lead to  
deadlocks.
Rem
Rem   MODIFIED   (MM/DD/YY)
Remgviswana11/12/01 - Use utl_recomp.recomp_serial
Remrdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
Remrburns  11/12/01 - validate all components after compiles
Remrburns  11/06/01 - fix invalid CATPROC call
Remrburns  09/29/01 - use 9.2.0
Remrburns  09/20/01 - add check for CATPROC valid
Remrburns  07/06/01 - get version from instance view
Remrburns  05/09/01 - fix for use with 8.1.x
Remarithikr04/17/01 - 1703753: recompile object type# 29,32,33
Remskabraha09/25/00 - validate is now a keyword
Remkosinski06/14/00 - Persistent parameters
Remskabraha06/05/00 - validate tables also
Remjdavison04/11/00 - Modify usage notes for 8.2 changes.
Remrshaikh 09/22/99 - quote name for recompile
Remncramesh08/04/98 - change for sqlplus
Remusundara06/03/98 - merge from 8.0.5
Remusundara04/29/98 - creation (split from utlirp.sql).
Rem   Mark Ramacher (mramache) was the original
Rem   author of this script.
Rem

Rem  
===
Rem BEGIN utlrp.sql
Rem  
===

--
--
--  
*
-- NOTE: Package STANDARD and DBMS_STANDARD must be valid before  
running
-- this part.  If these are not valid, run standard.sql and
-- dbms_standard.sql to recreate and validate STANDARD and  
DBMS_STANDARD;
-- then run this portion.
--  
*

@@utlrcmp.sql
execute utl_recomp.recomp_serial();
Rem  
=
Rem Run component validation procedure
Rem  
=

EXECUTE dbms_registry.validate_components;

Rem  
===
Rem END utlrp.sql
Rem  
===

On 10/21/2003 12:59:25 PM, Seema Singh wrote:
Hi,
Can someone send all object compilation script?
Thx
-Seema
_
Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up  
Internet Service.  Try it FREE for one month!
http://join.msn.com/?page=dept/dialup

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Seema Singh
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
Mladen Gogala
Oracle DBA


Note:
This message is for the named person's use only.  It may contain confidential,

RE: ORA-02049: timeout: distributed transaction waiting for lock

If your on Oracle 8.1.x or above also check doc id 1018919.102.  
Distributed_lock_timeout has become a hidden or more properly a deprecated parameter.  
Namely change the default at your own risk.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-Original Message-
Sent: Tuesday, October 21, 2003 12:40 PM
To: Multiple recipients of list ORACLE-L
lock


David,

Take a look at Note 19332.1, which explains the error and what to do next.

In short, the essence of the note is: The error comes if the time waited is
mor than the value of the distributed_lock_timeout parameter. Even if you do
a select from the remote database, it acquires a TX lock and that can wait.
Increase the value of the timeout or, just use an exception handler on the
commit statement to retry.

HTH.

Arup Nanda

- Original Message - 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 11:49 AM


 Hi List,

 We have a job that copies data in a table on a remote database to a local
 database through a database link.  Here are the steps in the job:

 1. truncate the table of t1 on the local database
 2. insert into t1 select * from [EMAIL PROTECTED]
 3. commit

 There are only 847 records in the table.  The job completes in 1 sec
 normally.  However, last Sunday we got ORA-02049: timeout: distributed
 transaction waiting for lock during commit process.  As my understanding,
 the error comes from a DML statement that requires locks on a remote
 database can be blocked if another transaction own locks on the requested
 data.  I'm pretty sure that there were no any activities on the remote
 database since the application was not open.  Also I can see from the log
 file (see below) that 847 records were inserted into the t1 table on the
 local database.  The error was generated during the commit process.  Does
 any one have any comments?  Thanks for any input.

 Here is the job log file:
 847 rows created.

 commit
 *
 ERROR at line 1:
 ORA-02049: timeout: distributed transaction waiting for lock

 We are in Oracle 8.1.7.4 and SunOS 5.8.  We take the default value for
 DISTRIBUTED_LOCK_TIMEOUT .

 Dave

 _
 Get a FREE computer virus scan online from McAfee.
 http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Goulet, Dick
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

disaster recovery doc

2003-10-21 Thread AK




Hi Guys ,
I have been recently tasked to write up 
procedures and steps / documentation for disaster recovery of db/system 
.

Any help , pointers ,links related to this is 
highly appreciated .

Thanks,
-ak

OCP DBA 8i

RE: Performance tuning book

Sorry to double post. It didn't show up on the board and after about an hour
I thought there was a problem. Of course as soon as I posted again, they
both showed up! I'll be more patient next time.

Michael Milligan
Oracle DBA
Ingenix, Inc.
2525 Lake Park Blvd.
Salt Lake City, Utah 84120
wrk 801-982-3081
mbl 801-628-6058
[EMAIL PROTECTED]


This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Milligan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: unique index

Damed Duhvelopers! *-)

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-Original Message-
Sent: Tuesday, October 21, 2003 2:19 PM
To: Multiple recipients of list ORACLE-L


Dick,

Thanks for your reply.  Unfortunately, the loader's log file was overwritten 
before our developer called me since she tried to rerun the job.

Dave

From: Goulet, Dick [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: unique index
Date: Tue, 21 Oct 2003 08:44:32 -0800

Dave,

   If memory is functioning normally:  When you use direct=y in Sql*Loader it 
flags all of your indexes as invalid and then revalidates/rebuilds then 
when the load is complete.  The reason is that loading data is faster when 
you don't have to parse index entries all the time and an invalid index 
does not need to be maintained.  It would appear from your message that 
something caused the one index to not validate during the Sql*Loader run.  
Why might be revealed in the loader's log file.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-Original Message-
Sent: Tuesday, October 21, 2003 12:29 PM
To: Multiple recipients of list ORACLE-L


Hi List,

We have a job that appends records to a table using SQL Loader
(DIRECT=TRUE).  The table has two unique indexes (no constraints).  Last
Sunday, the job loaded 11839 records into the table successfully, but the
one of the unique indexes became unusable for unknown reason.  I dropped 
the
unusable index and recreated it.  The index became valid.  Then the
developer reran the job and loaded the same 11839 records into the table 
(at
that time we did not know the first run already loaded the records).  Of
course, two unique indexes became unusable again.  I could not recreate the
unique indexes due to the duplicate keys found.  Finally, I deleted all of
23678 newly loaded records, recreated the unique indexes, and reloaded the
11839 records.  Every thing is fine now.  Here are my questions:

1. Why the same data crashed the index at the first time, but not at the 
end
2. After I recreated the unique index at the first time, those records were
already in the table.  Why did not the unique index complain for the
duplicates when we reloaded the same 11839 records into the table?

Dave

_
Send and receive larger attachments with Hotmail Extra Storage.
http://join.msn.com/?PAGE=features/es

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
--
Author: Goulet, Dick
   INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

_
Surf and talk on the phone at the same time with broadband Internet access. 
Get high-speed for as low as $29.95/month (depending on the local service 
providers in your area).  https://broadband.msn.com

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Goulet, Dick
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services

RE: WHERE 1 = 1 (any info on this)

2003-10-21 Thread Jamadagni, Rajendra

Well  we use SQR  too ... but it smells of COBOL and BASIC ... plus its use (maybe 
it is us) of gloal variables stinks.

Somehow I never liked it, whatever reports I wrote in my development days, I wrote a 
pl/sql package to do processign and then use SQR to retrieve from temp table and print 
it.

Raj

Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !


-Original Message-
Sent: Tuesday, October 21, 2003 1:24 PM
To: Multiple recipients of list ORACLE-L


Raj,

What's wrong with SQR?  I used it for a few years and found it great.
Especially for batch processing (both report writing and batch updating).
We chose it when we ran away from Cobol about 9 years ago.  At the time, our
choices were Oracle Rpt (can you say RPG?), the very first version of Oracle
Reports (boy, did that ever suck) and staying with Cobol.

We were very happy with our choice.  it is still the tool used by Banner
(College Erp software).

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Tuesday, October 21, 2003 12:14 PM
To: Multiple recipients of list ORACLE-L


I have seen people use this in pseudo-dynamic sql in a beast called SQR.
Especially when they have code like ...

select ...
union 
select ...
union
select ...
union 
select ... limit to your imagination.

Lot of people think that using 1=1 in absence of a valid limiting condition
will let them evaluate (and get data from) a union clause where as putting
1=2 will help them avoid ...

I don't hate just because I don't like SQR, I don't like it because until
CBO encounters and evaluates 1=2 clause, it still does all the work of
selecting etc and that is just a waste.

Raj


Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !



**
This e-mail message is confidential, intended only for the named
recipient(s) above and may contain information that is privileged, attorney
work product or exempt from disclosure under applicable law. If you have
received this message in error, or are not the named recipient(s), please
immediately notify corporate MIS at (860) 766-2000 and delete this e-mail
message from your computer, Thank you.

**5
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

**
This e-mail message is confidential, intended only for the named recipient(s) above 
and may contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank you.
**4
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL

Re: ORA-02049: timeout: distributed transaction waiting for lock

2003-10-21 Thread David Boyd

Arup,

Thanks for your reply.  We don't have a metalink account.  Could you please 
send the note to me?  My puzzle is that it seems the lock was acquired since 
all of records were inserted into the table.  How did the error come from 
commit command?

Dave


From: Arup Nanda [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: ORA-02049: timeout: distributed transaction waiting for lock
Date: Tue, 21 Oct 2003 08:39:32 -0800
David,

Take a look at Note 19332.1, which explains the error and what to do next.

In short, the essence of the note is: The error comes if the time waited is
mor than the value of the distributed_lock_timeout parameter. Even if you 
do
a select from the remote database, it acquires a TX lock and that can wait.
Increase the value of the timeout or, just use an exception handler on the
commit statement to retry.

HTH.

Arup Nanda

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 11:49 AM
 Hi List,

 We have a job that copies data in a table on a remote database to a 
local
 database through a database link.  Here are the steps in the job:

 1. truncate the table of t1 on the local database
 2. insert into t1 select * from [EMAIL PROTECTED]
 3. commit

 There are only 847 records in the table.  The job completes in 1 sec
 normally.  However, last Sunday we got ORA-02049: timeout: distributed
 transaction waiting for lock during commit process.  As my 
understanding,
 the error comes from a DML statement that requires locks on a remote
 database can be blocked if another transaction own locks on the 
requested
 data.  I'm pretty sure that there were no any activities on the remote
 database since the application was not open.  Also I can see from the 
log
 file (see below) that 847 records were inserted into the t1 table on the
 local database.  The error was generated during the commit process.  
Does
 any one have any comments?  Thanks for any input.

 Here is the job log file:
 847 rows created.

 commit
 *
 ERROR at line 1:
 ORA-02049: timeout: distributed transaction waiting for lock

 We are in Oracle 8.1.7.4 and SunOS 5.8.  We take the default value for
 DISTRIBUTED_LOCK_TIMEOUT .

 Dave

 _
 Get a FREE computer virus scan online from McAfee.
 http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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.net
--
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
_
Never get a busy signal because you are always connected  with high-speed 
Internet access. Click here to comparison-shop providers.  
https://broadband.msn.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Boyd
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Your new book

I'm not Cary but a satisfied reader who read the book in a
very detailed way and probably caused some headache to Cary.
Allow me, nevertheless, to respond to your question.
Cary's book IS different because it does not cover the classical
approach to tuning and explaining in detail all well known and less
well known V$ and X$ tables. The only thing in the book that can
be considered classic, is a lecture in proper business conduct.
Cary, unfortunately, regards performance optimization projects as
business and not art or an opportunity to express one's personality.
Nuff said. That part is covered in Chris Lawson's book in a similar
fashion.
Then there is a very detailed reference of .trc files format, and  
DBMS_SUPPORT package, together with perl scripts to parse them and
accounting principles for various forms of spent time (elapsed, spent  
CPU time and alike.) Parts of that can be found on Metalink, but not
described with such clarity and in such detail.
A part that not everybody will enjoy is a part of queuing theory which
helps predict the exact response times. Cary is, actually, taking  
things one step further and he explains how the exact response time can  
be calculated from 10046 trace files. That is what they do at Hotsos.
Shortcoming of that part is that BCS in math is recommended.  
Fortunately for me, I have a batchelor degree in math, so I was able to
follow. Even as layman not consecrated into the deepest mysteries of
mathematics and even if you don't know what a Bannach fixed point  
theorem or a Cauchy sequence is, you can still learn interesting things
about predictability and principles from that chapter.
As an additional value, there are many practical interesting examples  
from the Hotsos practice. It was almost a feeling of deja vu, something  
like: ah THAT is what those guys at Hotsos are doing! Add an  
extraordinary clarity and subtle sense of humor and you get an  
excellent book which I hat to rate as a strong buy.



On 10/21/2003 12:49:25 PM, Michael Milligan wrote:
Cary,

I don't mean to ask you to brag, but can you please tell me if your
new
book, of which I've heard good things, is different in any way than
other
Oracle Performance Tuning books out. Does it take a different
approach? Does
it
teach different methodologies? Is it more readable? I'd be very
interested
in your own assessment. What did you try to accomplish with this  
book?

TIA,

Michael Milligan
Oracle DBA
Ingenix, Inc.
2525 Lake Park Blvd.
Salt Lake City, Utah 84120
wrk 801-982-3081
mbl 801-628-6058
[EMAIL PROTECTED]
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to
which it is addressed. If the reader of this e-mail is not the
intended
recipient or his or her authorized agent, the reader is hereby
notified that
any dissemination, distribution or copying of this e-mail is
prohibited. If
you have received this e-mail in error, please notify the sender by
replying
to this message and delete this e-mail immediately.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Michael Milligan
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
Mladen Gogala
Oracle DBA


Note:
This message is for the named person's use only.  It may contain confidential, 
proprietary or legally privileged information.  No confidentiality or privilege is 
waived or lost by any mistransmission.  If you receive this message in error, please 
immediately delete it and all copies of it from your system, destroy any hard copies 
of it and notify the sender.  You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to 
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where 
the message states otherwise and the sender is authorized to state them to be the 
views of any such entity.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note

Re: RE: Data Modeling


Hey Michael

I enjoyed your write-up.. especially a few db guys telling 'its relational because 
they are related'... I have also heard about it and the fact is that SQL the language 
for all the RDBMA is based on relational algebra and relational calcus and there in 
maths a rwo-column structure is called a relation.

During my acadamics I have studied the book by Korth and Sudarshan and i know 
er-diagram and normalization theoritically. My major experience is with production dbs 
(where the schema structure hardly changes) and the new assignment mostly involves 
data modelling.. and that too the model will not be freezed soon. it will change very 
frequently for quite sometime. Was wondering how can i manage db table/proc  structure 
changes from different sectors and integrate them at the end of the day.

I donot know erwin or designer etc.


Thanks a lot

Regards
B S Pradhan

--

On Tue, 21 Oct 2003 Michael Milligan wrote :
I would read some of C.J. Date's papers, or books from his Relational
Database Writings series. Also, there is a recent book called Data
Modeling for Everyone by Sharon Allen (Curlingstone Press) which is good.

Most importantly, understand the fundamental principles of relational theory
as it pertains to relational databases.  If you make an effort at this
you'll be ahead of 90% of developers/DBAs in this area, in my opinion. I've
heard database experts say that relational databases are called that
because they relate one table to another. This is false. It is called
relational because it is based on relational math, and because columns are
grouped together into special relations called relational tables. We call
them tables for short.

The important thing to note here is this: the relationship that matters most
is the relationship among the columns of the SAME TABLE. That they really do
belong together is the most important thing to be sure of in data modeling.
They need to be functionally dependent on the same set of primary key
columns. Functional dependency is hugely important to understand and is the
basis of good data modeling.

Some authors:

C.J. Date
Fabian Pascal
Sharon Allen

many others as well.

HTH,

Michael Milligan
Oracle DBA
Ingenix, Inc.
2525 Lake Park Blvd.
Salt Lake City, Utah 84120
wrk 801-982-3081
mbl 801-628-6058
[EMAIL PROTECTED]



This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Michael Milligan
   INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Cache a table

2003-10-21 Thread John Kanagaraj

My understanding is that the KEEP and RECYCLE Pools are just 'names' in the
sense that they are placeholders for assigning  an object to the BUFFER_POOL
{ KEEP | RECYCLE | DEFAULT } clause, and that the 'aging' algorithms for
KEEP and RECYCLE are exactly the same. Assigning a specific object to one of
these named pools segregates objects by retention-requirements. Thus, KEEP
does not imply a different treatment of the Buffers - rather it makes sure
that objects that you would like to 'keep' around are specifically directed
to a common pool and vice versa

Does anyone have additional information that can verify this? I heard this
from a knowledgeable Oracle instructor in an Oracle Tuning training Class.

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional! 

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

-Original Message-
From: Tim Gorman [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 21, 2003 6:59 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Cache a table


Good points, Arup.

Actually, I would argue that there is better reason to 
consider using the
RECYCLE pool than to consider how to cache tables or use the 
KEEP pool.
The advantage of effective use of the RECYCLE pool is better 
behavior in the
rest of the Buffer Cache...

When you think of it, the default DEFAULT buffer pool and the 
KEEP pool have
essentially the same purpose:  long-term caching of blocks.  
What keeps them
from accomplishing that mission but objects whose blocks waste 
space and
energy cycling into and out from the Buffer Cache?

It's kind of like a school teacher admonishing his/her class that a
troublesome few have ruined things for everybody.  When I was 
in school,
troublemakers were segregated from the rest of the class, sometimes
cumulatively into a separate classroom (we called ourselves 
the mentals
and read Mad magazines all the time, which accounts for a lot, then and
now).  Nowadays, I'm sure that such a measure isn't considered 
for fear of
lawsuit for hurting the self-esteem of the poor dears.  
Never mind the
confusion between the useless feel-good phrase self-esteem 
and the more
useful and thought-provoking phrase self-respect.  Oh well, 
better stop
now...

Anyway, marking a table as CACHE and placing it in a KEEP 
buffer pool which
is large enough to accommodate all of the used blocks is the 
closest thing
to pinning a table into the Buffer Cache as you'll get, as 
Arup described.

Of course, there is little benefit from such a move, as Arup 
also mentioned.

Just yesterday, I visited a customer who had a series of SQL 
statements that
were executing some 10 million times _each_ per day, averaging 
about 20-1500
LIOs per execution.  They each had a 99.999% buffer cache hit
ratio, yet strangely enough the performance on the server is 
absolute crap
because the eight brand-new 2Ghz CPUs on the server are busy 
as hell with no
time to spare for anything.

Well, you know and I know that they simply need more CPUs, 
which is what HP
is busy telling them, today right as we speak.  Moreover, Oracle
Consulting is shoulder to shoulder with them, nodding their 
heads.  No way
does the crap custom-built application need to be altered in 
any minor way,
so that it doesn't keep performing the same useless validation 
query on the
same set of static lookup tables over and over again for each 
row inserted,
when the JDBC thin client can easily query these tables only 
once and store
the results.  Nope.  No sirree...

Cliff-Clavin-voice
It's a little-known fact that Java code actually has the consistency of
concrete, once in production.  There are so many interdependencies from
shared modules and RPCs that people are terrified of modifying 
anything,
probably for good reason.  Far easier to shift blame or say 
hear hear when
the vendor proposes another 4-8 CPUs.

Ah, I believe I'll have another beer when you're ready, Sammy...
/Cliff-Clavin-voice

Anyway, first tune the SQL.  Then, tune to the application to 
get rid of
unnecessary SQL.  Then and only then, consider tuning the 
Buffer Cache to
segregate bad tables to the RECYCLE pool or pinning tables 
to the KEEP
pool.  Reversing the order is a great way to convert a happy 
application
capable of running on a small server to an unhappy application 
demanding a
huge server...



on 10/21/03 5:21 AM, Arup Nanda at [EMAIL PROTECTED] wrote:

 Never. Altering the table to cache does not gurantee that it 
will be always
 be available in the cache. It simply means the table will be 
placed in the
 Least recently used end of the LRU list and it will age away 
as time goes
 by, just like any other table.
 
 A better approach is to use KEEP pool and place teh table 
(and all other
 tables that are accessed frequently) there. This is 
particualrly true for
 datawarehouses wherethe lookup tables or small dimension

Re: Re: Data Modelling



Thank you all for all the help/suggestion/links and information about data modelling.

Regards
B S Pradhan

---

On Tue, 21 Oct 2003 [EMAIL PROTECTED] wrote :
ive been told 'data modelling for mere mortals' is a good place to start.
 
  From: bhabani s pradhan [EMAIL PROTECTED]
  Date: 2003/10/21 Tue AM 08:54:25 EDT
  To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
  Subject: Data Modelling
 
 
Hi All
I have not done data modelling as a major task so far. Would like to know from where 
can I make a good start. I have basic(conceptual) knowledge of ER-Diagram and 
Normalization. But if anybody can give me some case studies/links etc.,
I see the responsibilities would include modelling and coping with Frequent changes 
of the database object structures.
Please Help
Thanks a Lot
Regards
B S Pradhan

Hi All

I have not done data modelling as a major task so far. Would like to know from where 
can I make a good start. I have basic(conceptual) knowledge of ER-Diagram and 
Normalization. But if anybody can give me some case studies/links etc.,

I see the responsibilities would include modelling and coping with Frequent changes 
of the database object structures.

Please Help

Thanks a Lot

Regards
B S Pradhan

RE: RE: Data Modeling

Hi Again,

What I do when a model is going to change is try to make it as flexible as
possible from the start. Build more abstraction into the model than you
normally would. Normalization is even more important here, even going to 4th
or 5th form, or at least Boyce-Codd 3rd. You want to design it so that when
someone wants to change the structure, it may be facilitated by the addition
of a new record instead of a new column. A very simplistic example would be
to have a separate address entity allowing for the possibility of multiple
addresses per customer, instead of building the address attributes right
into the customer entity. A good rule of thumb: whatever will change should
be changeable by addition or subtraction of a row. Whatever won't change is
a candidate for a column. That's a generalization, but a good rule
nonetheless.

Data Architect from Sybase, ER/Studio from Embarcadero, Erwin from Computer
Associates are all good tools and easy to learn. QDesigner is the Data
Architect physical modeler repackaged by Quest and sold for less. Excellent
tool.

Michael


This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Milligan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).

Block size : what is the gain ?

2003-10-21 Thread Stephane Paquette

Hi,

All our DB have an 8k block size (8172/aix).
Even the reporting/dss database where data is accessed mainly by full scan.

Can we quantify the gain in % of switching from an 8k to 16k block size from
a performance point of view ?



Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]



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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Cache a table

I always wondered why Oracle thought this was a useful table attribute.

My gut feeling is that it is an extra that does little.

For example, say we want to keep a code table in memory because it is
constantly being hit for column verifiction.  By definition, if a table is
constantly being queried, it's segments will be in memory because they never
age out.  That sounds like cacheing to me.

And then I remember a specific piece of Oracle documentation saying that,
even though we may mark a table to be cached, it *still* may be aged out
if memory is needed for other data blocks.

Like I said, sounds a little like here you have it, and here you don't.

I'm sure that my impression is wrong and someone will correct me.  But I
doubt I will use the CACHE option anytime soon.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Tuesday, October 21, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L


My understanding is that the KEEP and RECYCLE Pools are just 'names' in the
sense that they are placeholders for assigning  an object to the BUFFER_POOL
{ KEEP | RECYCLE | DEFAULT } clause, and that the 'aging' algorithms for
KEEP and RECYCLE are exactly the same. Assigning a specific object to one of
these named pools segregates objects by retention-requirements. Thus, KEEP
does not imply a different treatment of the Buffers - rather it makes sure
that objects that you would like to 'keep' around are specifically directed
to a common pool and vice versa

Does anyone have additional information that can verify this? I heard this
from a knowledgeable Oracle instructor in an Oracle Tuning training Class.

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Disappointment is inevitable, but Discouragement is optional! 

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

-Original Message-
From: Tim Gorman [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, October 21, 2003 6:59 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Cache a table


Good points, Arup.

Actually, I would argue that there is better reason to 
consider using the
RECYCLE pool than to consider how to cache tables or use the 
KEEP pool.
The advantage of effective use of the RECYCLE pool is better 
behavior in the
rest of the Buffer Cache...

When you think of it, the default DEFAULT buffer pool and the 
KEEP pool have
essentially the same purpose:  long-term caching of blocks.  
What keeps them
from accomplishing that mission but objects whose blocks waste 
space and
energy cycling into and out from the Buffer Cache?

It's kind of like a school teacher admonishing his/her class that a
troublesome few have ruined things for everybody.  When I was 
in school,
troublemakers were segregated from the rest of the class, sometimes
cumulatively into a separate classroom (we called ourselves 
the mentals
and read Mad magazines all the time, which accounts for a lot, then and
now).  Nowadays, I'm sure that such a measure isn't considered 
for fear of
lawsuit for hurting the self-esteem of the poor dears.  
Never mind the
confusion between the useless feel-good phrase self-esteem 
and the more
useful and thought-provoking phrase self-respect.  Oh well, 
better stop
now...

Anyway, marking a table as CACHE and placing it in a KEEP 
buffer pool which
is large enough to accommodate all of the used blocks is the 
closest thing
to pinning a table into the Buffer Cache as you'll get, as 
Arup described.

Of course, there is little benefit from such a move, as Arup 
also mentioned.

Just yesterday, I visited a customer who had a series of SQL 
statements that
were executing some 10 million times _each_ per day, averaging 
about 20-1500
LIOs per execution.  They each had a 99.999% buffer cache hit
ratio, yet strangely enough the performance on the server is 
absolute crap
because the eight brand-new 2Ghz CPUs on the server are busy 
as hell with no
time to spare for anything.

Well, you know and I know that they simply need more CPUs, 
which is what HP
is busy telling them, today right as we speak.  Moreover, Oracle
Consulting is shoulder to shoulder with them, nodding their 
heads.  No way
does the crap custom-built application need to be altered in 
any minor way,
so that it doesn't keep performing the same useless validation 
query on the
same set of static lookup tables over and over again for each 
row inserted,
when the JDBC thin client can easily query these tables only 
once and store
the results.  Nope.  No sirree...

Cliff-Clavin-voice
It's a little-known fact that Java code actually has the consistency of
concrete, once in production.  There are so many interdependencies from
shared modules and RPCs that people are terrified of modifying 
anything,
probably for good reason.  Far easier to shift blame or say 
hear hear when
the vendor proposes another 4-8 CPUs.

Ah, I believe I'll have another beer when you're ready, Sammy...

RE: Your new book

MLaden,

Thank you very, very much for a great review. I hope you'll post that to
Amazon. As a matter of fact, I enjoy queuing theory. I remember almost
buying a book called Practical Queuing Analysis by Mike Tanner.

I was a biology major in college, so I may muddle through the math, but
it'll be good for me anyway.

Thanks again for taking the time to write that great review,

Michael Milligan
Oracle DBA
Ingenix, Inc.
2525 Lake Park Blvd.
Salt Lake City, Utah 84120
wrk 801-982-3081
mbl 801-628-6058
[EMAIL PROTECTED]


This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you have received this e-mail in error, please notify the sender by replying
to this message and delete this e-mail immediately.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Milligan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Your new book

I'll try to correct spelling errors before I post it to the Amazon,
but I will do it, despite the fact that I'm not very fond of Amazon.
On 10/21/2003 03:09:32 PM, Michael Milligan wrote:
MLaden,

Thank you very, very much for a great review. I hope you'll post that
to
Amazon. As a matter of fact, I enjoy queuing theory. I remember  
almost
buying a book called Practical Queuing Analysis by Mike Tanner.

I was a biology major in college, so I may muddle through the math,
but
it'll be good for me anyway.
Thanks again for taking the time to write that great review,

Michael Milligan
Oracle DBA
Ingenix, Inc.
2525 Lake Park Blvd.
Salt Lake City, Utah 84120
wrk 801-982-3081
mbl 801-628-6058
[EMAIL PROTECTED]
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to
which it is addressed. If the reader of this e-mail is not the
intended
recipient or his or her authorized agent, the reader is hereby
notified that
any dissemination, distribution or copying of this e-mail is
prohibited. If
you have received this e-mail in error, please notify the sender by
replying
to this message and delete this e-mail immediately.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Michael Milligan
  INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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).
Mladen Gogala
Oracle DBA


Note:
This message is for the named person's use only.  It may contain confidential, 
proprietary or legally privileged information.  No confidentiality or privilege is 
waived or lost by any mistransmission.  If you receive this message in error, please 
immediately delete it and all copies of it from your system, destroy any hard copies 
of it and notify the sender.  You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to 
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where 
the message states otherwise and the sender is authorized to state them to be the 
views of any such entity.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: disaster recovery doc




poor 
bastard. you'll spend hours and hours developing a document nobody really 
wants, nobody will read, and (hopefully) you will never use.

if I 
were you, I'd start lobbying for an outside agency to write it for you. 
sure, you'd have to work with them. but they would get it done faster, 
create a better report because they could focus on just it, and would have 
better experience in what to put into it.

good 
luck!

Tom Mercadante Oracle Certified Professional 

  -Original Message-From: AK 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, October 21, 2003 2:14 
  PMTo: Multiple recipients of list ORACLE-LSubject: 
  disaster recovery doc
  Hi Guys ,
  I have been recently tasked to write up 
  procedures and steps / documentation for disaster recovery of db/system 
  .
  
  Any help , pointers ,links related to this is 
  highly appreciated .
  
  Thanks,
  -ak
  
  OCP DBA 8i

RE: Performance tuning book

2003-10-21 Thread Cary Millsap

Michael, I've responded by preceding your questions with MM: and my
answers with CVM:.


MM: ...can you please tell me if your new book, of which I've heard good
things, is different in any way than other Oracle Performance Tuning
books out. Does it take a different approach?

CVM: Drastically different. Probably the most important difference is
that it's the first Oracle book that doesn't espouse a method that
consists of just trying things until you find something that helps. It
prescribes a step-by-step process, which is the same every time, for
diagnosing your performance problem. The method works for finding
performance problem causes whatever in the technology stack they may be.
I didn't do it this way for the sake of being different. I did it this
way because the traditional ways of tuning don't work.

I think some other things like the queueing chapter make it different,
too, but I feel that there's been too much focus placed upon the
apparently deep mathematical nature of this chapter. The point of the
chapter is to show people how to use a model (one that's already
completely worked out for you) to gain insight into your real Oracle
performance problems. At the end of the chapter is a 14-page, fully
worked example. No other book does this. There are a lot of formulas in
this chapter, but I show them only to help people recreate (or test) my
results. For every formula, there is an Excel spreadsheet function that
automates the use of that formula (some of the Excel formulas took years
to develop, by the way). The chapter is all about showing the reader why
performance behaves in the surprising ways that it sometimes does. It's
not about showing you how cool math can be.


MM: Does it teach different methodologies?

CVM: It teaches a single method that is radically different from the
ones most Oracle professionals are taught. You can get a drift of what I
mean by reading the sample chapter at
http://www.oreilly.com/catalog/optoraclep/index.html. (By the way, I
distinguish carefully between the words method and methodology. I
have a note about this in the book's Glossary, and at
http://www.hotsos.com/e-library/oop.html as well.)


MM: Is it more readable? I'd be very interested in your own assessment.

CVM: There are three parts to the book, and the readability varies by
design across those three parts. Parts I and III are meant to be read
front-to-back by DBAs and analysts, and also their managers. Part II is
reference material that I hope technical people are reading, but Part II
is definitely too much to swallow in a few sittings. There's just too
much detail. You can see more information about the structure of the
book at http://www.hotsos.com/e-library/oop.html. 

There are some tricky concepts you have to understand before you can
optimize an Oracle database, so it can be difficult to write about these
concepts in a manner that people can understand. I find virtually
nothing more offensive in technical literature than the author who tells
you that something is so complicated that you would never understand it,
even if he bothered to explain it to you. I think it should be the
reader's right to see the facts and decide whether to skip them or dive
into them.

I think that most authors who try to complicate things are really just
afraid to admit publicly that they don't know something. It's fine not
to know some things. We all don't know a lot of things! But it's not
helpful when an author's ultimate goal is to look authoritative instead
of trying to help the reader understand what we know and what needs
further study.

I know I've scared a lot of people with all the arithmetic in the
queueing chapter, but here I've been especially careful to explain how
to use what our good mathematical forefathers have worked out for us.
You can read the entire chapter without having to know what any of the
formulas mean. I've focused on what the models *mean* and how to use
them, not on why they work.

So, how readable is it? There's a lot of stuff out there that I hope
we're much, much better than. But it would be difficult to be more
readable than, for example, Ensor, Kyte, Lewis, Morle,
Vaidyanatha/Deshpande, or Lawson, who, in my opinion, write beautifully.
So far, much of the feedback I've received is that the book is fun to
read, which was definitely a principal design goal of the project.


MM: What did you try to accomplish with this book?

CVM: I covered much of this in the preface. Our whole company was borne
of deep frustration with some of the very popular tips  techniques
work out there that I consider to be absolute garbage. One of the
principal motives of the book was to create a better classroom
experience for our students (see
http://www.hotsos.com/courses/PD101.php, for example).

With the book, Jeff and I have tried to lay out a system that enables a
reader to determine whether the performance information he's getting at
conferences, classes, books, magazines, etc. is valid or not. We have

RE: Your new book

2003-10-21 Thread Freeman Robert - IL

Well I got the honor of being the first to publish a review on Amazon for
Cary's book it is a good read!

Robert

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 10/21/2003 2:24 PM

I'll try to correct spelling errors before I post it to the Amazon,
but I will do it, despite the fact that I'm not very fond of Amazon.
On 10/21/2003 03:09:32 PM, Michael Milligan wrote:
 MLaden,
 
 Thank you very, very much for a great review. I hope you'll post that
 to
 Amazon. As a matter of fact, I enjoy queuing theory. I remember  
 almost
 buying a book called Practical Queuing Analysis by Mike Tanner.
 
 I was a biology major in college, so I may muddle through the math,
 but
 it'll be good for me anyway.
 
 Thanks again for taking the time to write that great review,
 
 Michael Milligan
 Oracle DBA
 Ingenix, Inc.
 2525 Lake Park Blvd.
 Salt Lake City, Utah 84120
 wrk 801-982-3081
 mbl 801-628-6058
 [EMAIL PROTECTED]
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Re[2]: job opportunity in Dallas

2003-10-21 Thread Bellow, Bambi

We are approximately 1/3 of The 7% Solution... (an old Sherlock Holmes movie
detailing his cocaine addiction)

-Original Message-
Sent: Tuesday, October 21, 2003 9:14 AM
To: Multiple recipients of list ORACLE-L


What a horrible problem it must be, if we are solution?

On 10/20/2003 05:39:33 PM, Jonathan Gennick wrote:
 Monday, October 20, 2003, 4:54:26 PM, you wrote:
 IN 2% - that's all we get? -:)
 
 Didn't someone write a book once called The 2% Solution?
 That's what we are: the solution!
 
 Best regards,
 
 Jonathan Gennick --- Brighten the corner where you are
 http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
 
 Join the Oracle-article list and receive one
 article on Oracle technologies per month by
 email. To join, visit  
 http://four.pairlist.net/mailman/listinfo/oracle-article,
 
 or send email to [EMAIL PROTECTED] and
 include the word subscribe in either the subject or body.
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Jonathan Gennick
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 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).


Mladen Gogala
Oracle DBA



Note:
This message is for the named person's use only.  It may contain
confidential, proprietary or legally privileged information.  No
confidentiality or privilege is waived or lost by any mistransmission.  If
you receive this message in error, please immediately delete it and all
copies of it from your system, destroy any hard copies of it and notify the
sender.  You must not, directly or indirectly, use, disclose, distribute,
print, or copy any part of this message if you are not the intended
recipient. Wang Trading LLC and any of its subsidiaries each reserve the
right to monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender,
except where the message states otherwise and the sender is authorized to
state them to be the views of any such entity.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Bellow, Bambi
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: Your new book

2003-10-21 Thread Igor Neyman

Is it (review) as good as Mladen's? -:)

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
Freeman Robert - IL
Sent: Tuesday, October 21, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L

Well I got the honor of being the first to publish a review on Amazon
for
Cary's book it is a good read!

Robert

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 10/21/2003 2:24 PM

I'll try to correct spelling errors before I post it to the Amazon,
but I will do it, despite the fact that I'm not very fond of Amazon.
On 10/21/2003 03:09:32 PM, Michael Milligan wrote:
 MLaden,
 
 Thank you very, very much for a great review. I hope you'll post that
 to
 Amazon. As a matter of fact, I enjoy queuing theory. I remember  
 almost
 buying a book called Practical Queuing Analysis by Mike Tanner.
 
 I was a biology major in college, so I may muddle through the math,
 but
 it'll be good for me anyway.
 
 Thanks again for taking the time to write that great review,
 
 Michael Milligan
 Oracle DBA
 Ingenix, Inc.
 2525 Lake Park Blvd.
 Salt Lake City, Utah 84120
 wrk 801-982-3081
 mbl 801-628-6058
 [EMAIL PROTECTED]
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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.net
-- 
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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: RE: Performance tuning book