date:20021227





It would appear we're looking into yet another hit ratio, namely the ASS
Hit Ratio. Used to be rather high in my younger days.

Mogens

Jonathan Lewis wrote:

  Depending on your circumstances, ASS Management
can eliminate severe contention on the freelists / freelist
groups area.  However, because Oracle is overgenerous
with its allocation of bitmap blocks (which may turn
out to be in excess of 1% of your database), you
may end up thrashing your system because most of
your buffer space is flooded with hot BMBs and the
data has to keep thrashing on and off disk.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

England__January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 19 December 2002 17:51


  
  
We were the other way around in our testing lately:-)

We turned on auto space management to remove the contention.

  
  Afterwards-we
  
  
removed quite a bit of header block/free list contention..

anyway, more tests to follow

-Original Message-
Sent: Thursday, December 19, 2002 10:09 AM
To: Multiple recipients of list ORACLE-L



As part of a RAC benchmark with 9.2 we had faced severe LOCKING
on setting segment space management AUTO  had to REMOVE it

HTH

join after saving rowid Value into a Field - For Design , Dev. Gurus

2002-12-27 Thread VIVEK_SHARMA

Title: Message





let us 
suppose there are two tablesM and P. 
bothContain the 
fieldemp_id. other columnsmay bedifferent.


All recordsof M 
also Exist in P .Table M will haverecords in the range 1-5 
lakhs.
P 
table will containAdditional Records such 
that the Total Number of Records in P is15-20 times the number of 
records in M.

one way to 
join the two tables is to say M.emp_id = P.emp_id. but because P has high number 
of records the select is slower.

we 
foundthat select of a row fromtable"P" 
using "rowid" columnwas veryQUICK.

Is it a Correct practice 
:-

1) to Store the ROWID of Table P 
inMin a separate column(say "P_rowid")

2) Is it possible 
to do a Joinlikethe follows :-

select 
field1, field2,... from M,P 
where M.emd_id = '6223' 

andlike M.P_rowid= P.rowid

this way we 
hoped to select from M table (which has less number of records) and do a rowid 
based select on P table, which we found out is not allowed by ORACLE. 


we want a 
join because we want to create a view over table M and P.

We do NOT want to use the following way 
:-

select 
field1, field2 ..,P_rowid from M where emp_id = '6223'from a Cursor  passing it to the Query as 
follows :-

select * 
from P where rowid = P_rowid (selected earlier)

Are there Some Standard Practices that Should be 
Followed during Designing Tables , Fields, SQL Writing 
?

Any Dos , Don'ts ?

RE: join after saving rowid Value into a Field - For Design , Dev

2002-12-27 Thread Richard Ji

Title: Message



Not a 
good idea to store rowid in table M. If you ever move table P to a 
different tablespace
or 
within the same tablespace, all it's rowid would change.

Richard Ji

  -Original Message-From: VIVEK_SHARMA 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 
  5:29 AMTo: Multiple recipients of list ORACLE-LSubject: 
  join after saving rowid Value into a Field - For Design , Dev. Gurus 
  
  
  
  let 
  us suppose there are two tablesM and P. 
  bothContain the 
  fieldemp_id. other columnsmay bedifferent.
  
  
  All 
  recordsof M also Exist in P .Table M will haverecords in 
  the range 1-5 lakhs.
  P 
  table will containAdditional Records such 
  that the Total Number of Records in P is15-20 times the number of 
  records in M.
  
  one way to 
  join the two tables is to say M.emp_id = P.emp_id. but because P has high 
  number of records the select is slower.
  
  we 
  foundthat select of a row fromtable"P" 
  using "rowid" columnwas veryQUICK.
  
  Is 
  it a Correct practice :-
  
  1) 
  to Store the ROWID of Table P inMin a 
  separate column(say 
  "P_rowid")
  
  2) Is it possible to do a 
  Joinlikethe follows :-
  
  select 
  field1, field2,... from M,P 
  where M.emd_id = '6223' 
  
  andlike M.P_rowid= P.rowid
  
  this way 
  we hoped to select from M table (which has less number of records) and do a 
  rowid based select on P table, which we found out is not allowed by ORACLE. 
  
  
  we want a 
  join because we want to create a view over table M and P.
  
  We do NOT want to use the following way 
  :-
  
  select field1, field2 ..,P_rowid from M where emp_id = 
  '6223'from a Cursor 
   passing it to the Query as follows :-
  
  select * 
  from P where rowid = P_rowid (selected earlier)
  
  Are there Some Standard Practices that Should be 
  Followed during Designing Tables , Fields, SQL Writing 
  ?
  
  Any Dos , Don'ts ?

RE: join after saving rowid Value into a Field - For Design , Dev. Gurus

2002-12-27 Thread Stephane Faroult

Vivek,

  Bad, bad, bad idea. You can play with rowids in your programs - as long as you 
consider them to be transient values (get it/use it). Don't forget that they are 
physical addresses (BTW, DBMS were invented in the first place to hide the physical 
implementation from programs). Any export/import, ALTER TABLE MOVE, partition split 
etc., basically any kind of reorg which may shuffle your data on your disks will 
quietly make everything implode. Think about something as trivial as refreshing the 
development database.
If your join doesn't run as fast as you hope, check that your stats are up-to-date, 
check the execution plan, and if then you still are left unsatisfied, try some hints 
(FIRST_ROWS, ALL_ROWS and ORDERED are the safest and my favorite ones). But never ever 
consider rowids as reliable technical data.

HTH,

SF

- Original Message -
From: VIVEK_SHARMA [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Fri, 27 Dec 2002 02:28:41

let us suppose there are two tables M and P. =0D
both Contain the field emp_id. other columns may be
different. =0D
 =0D
 =0D
All records of M also Exist in P .Table M will have
records in the range=0D
1-5 lakhs.=0D
P table will contain Additional Records such that
the Total Number of=0D
Records in P is 15-20 times the number of records
in M.=0D
 =0D
one way to join the two tables is to say M.emp_id
=3D P.emp_id. but=0D
because P has high number of records the select is
slower.=0D
 =0D
we found that select of a row from table P using
rowid column was=0D
very QUICK . =0D
 =0D
Is it a Correct practice :-=0D
 =0D
1) to Store the ROWID of Table P in M in a separate
column (say=0D
P_rowid)=0D
 =0D
2) Is it possible to do a Join like the follows :-
=0D
 =0D
select field1, field2,... from M,P =0D
where M.emd_id =3D '6223' =0D
and like M.P_rowid =3D P.rowid=0D
 =0D
this way we hoped to select from M table (which has
less number of=0D
records) and do a rowid based select on P table,
which we found out is=0D
not allowed by ORACLE. =0D
 =0D
we want a join because we want to create a view
over table M and P.=0D
 =0D
We do NOT want to use the following way :-=0D
 =0D
select field1, field2 ..,P_rowid from M where
emp_id =3D '6223' from a=0D
Cursor  passing it to the Query as follows :-
=0D
 =0D
select * from P where rowid =3D P_rowid (selected
earlier)=0D
 =0D
Are there Some Standard Practices that Should be
Followed during=0D
Designing Tables , Fields, SQL Writing ?=0D
 =0D
Any Dos , Don'ts ?=0D
 =0D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroul
  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: Row Migration

Someone asked in a back channel email if parallelism is used. The select
portion of the update statement uses parallelism (though the updates
themselves get serialized) through the use of an in-line join update (to
avoid the second sub-query commonly used to constrain the rows being
updated):

Update (Select /*+ parallel hints */ 
From   a,b
Where  a.key = b.key)
Set a.col1 = b.col1,
a.col2 = b.col2
.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Larry
 Elkins
 Sent: Thursday, December 26, 2002 6:09 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Row Migration


 Listers,

 8.1.7.4 64 Bit Solaris

 Does row migration utilize DB File Sequential Reads on the table? Off the
 top of my head I would expect so, but I've never tested something
 like that
 before.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  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: Script to recreate schema

2002-12-27 Thread Richard Ji

Yes.  And bvi for binary files.

-Original Message-
Sent: Thursday, December 26, 2002 2:04 AM
To: Multiple recipients of list ORACLE-L



exp userid=system/manager file=schema.dmp rows=n owner=scott
vi schema.dmp

really.

Jared

On Wednesday 25 December 2002 09:53, Andrey Bronfin wrote:
 Dear gurus !
 I'm sure many of you have scripts to recreate an Oracle schema including
 objects (i am interested in tables, indexes , comments, views, sequences,
 triggers, stored procs/functions etc..)
 Would you please share.
 Many thanks in advance !

 Merry X-mas and Happy New Year to you all !


Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
Content-Transfer-Encoding: 7bit
Content-Description: 

-- 
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: Richard Ji
  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: Row Migration




Well, 
yes, Iwould agree with that ;-) 

What 
we are trying to determine here in this particular case is how much or what 
percentage of the slowdown in the process is due to the migration of rows. We 
aren't ready (until we do some testing) tomake a blanket statement that 
row migration *alone* is the cause of the significant slowdown. In other words, 
I'm not willing to make a statement to the powers that be that simply increasing 
the pctfree is going to make things normal again until we have a chance to do 
some more detailed monitoring and testing.

Regards,Larry G. 
Elkins[EMAIL PROTECTED]214.954.1781 

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar NSent: 
  Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Re: Row Migration
  yes, row migration will 
  degrade the performance..

Re: Row Migration





Row migration means extra IO's. If IO is taking up any significant part of
your response time, then you don't want extra IO, of course. And the IO will
be single-block IO (sequential reads) because a stub is left in the originating
block pointing to the new block where the row migrates to - and that requires
a single-block IO.

Your test with pctfree 10, then collect stats, etc., then repeat the test
with pctfree 50 sounds fine to me. Good luck.

Mogens

Anand Kumar N wrote:
  
  
 
  
 

  yes, row migration
will  degrade the performance..
 
  
 
   
  
-
Original Message - 
   
From:
   Larry Elkins
   
   
To:
Multiple
recipients of list ORACLE-L
   
Sent:
Friday, December 27, 2002 5:38AM
   
Subject:
Row Migration
   


Listers,

8.1.7.4 64 Bit Solaris

Does rowmigration utilize DB File Sequential Reads on the table? Off
the
top of myhead I would expect so, but I've never tested something like
   that
before.

Trying to figure out if row migration is the cause ofthe slowdown in
a
package (well, it's probably slowing it down, just tryingto gauge the
impact). PctFree is 10, and new feeds contain lots of elementsthat had
been
empty before. As a result, a very large number of rows arebeing updated
with the new info being applied, effectively doubling the rowlength.
Would
certainly expect row migration to occur. When running,execution time
has
quadrupled, and we see significant waits on DB FileSequential Reads,
with
the file/block values and dba_extents indicating thetable, not an index.
The working idea at this point is that all those DBFile Sequential Read
waits on the table are possibly related to rows beingmigrated. Anyone
tested for this?

We will be building a test case onFriday. One with PctFree 10 and the
columns being updated having nulls.Will gather the waits, before and
after
sesstat's, analyze list chainedrows, both before and after, total blocks,
rows per block, etc. Thenrebuild the test having a PCTFREE of 50 and
do the
same thing. Somewildcards -- with the blocks less tightly packed, we
will
have to visitnearly double the number of blocks (maybe offset by
migration), contention,and various other things to take into account.
But
the main thing we arefocusing in on is if we continue to see the db file
sequential read waitson the table. I guess the fact that we are seeing
waits is indicative ofsome I/O contention, but trying to determine if,
and
how much, of that I/Ois due to row migration, in which case a larger
PCTFREE could provide somemore immediate relief. No FK/PK stuff, unique
index is there, but it shouldresolve uniqueness using the index, not
the
table. Maybe have left somethings out. This came up a few days ago, but
just really started thinkingabout it and digging into it. And the end
result is we don't want migratedrows, just looking to see if the row
migration is the primary cause of theperformance downturn.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

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

Fat City NetworkServices -- 858-538-5051 http://www.fatcity.com
San Diego,California -- Mailing list and webhostingservices
-
ToREMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note
EXACTspelling of 'ListGuru') and in
the message BODY, include a line containing:UNSUB ORACLE-L
(or the name of mailing list you want to be removedfrom). You may
also send the HELP command for other information (likesubscribing).

RE: Cache on sysdate? --From 9i performance planning manual

2002-12-27 Thread Richard Ji

Title: RE: Cache on sysdate? --From 9i performance planning manual



Thanks 
Raj. That's very cool. Now I can do:

SQL delete from dual;

1 row 
deleted.

SQLdeclare 2 a 
date :=sysdate; 3 begin 4 
dbms_output.put_line(to_char(a,'MMDD HH24:MI:SS')); 5* 
end;
20021227 05:36:54

PL/SQL 
procedure successfully completed.

That 
further proves it no longer uses "select sysdate into a from 
dual;".

Richard Ji

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 26, 
  2002 5:14 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Cache on sysdate? --From 9i performance 
  planning manual
  Richard, 
  If you look in the tracefile ... there is no select from 
  dual. It used to be like that but things changed (as Anjo mentions maybe 
  be around 8iR3). The sysdate call is now a C function call.
  Raj __ Rajendra Jamadagni 
   MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
  opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, but having an opinion 
  is an art! 
  -Original Message- From: 
  Richard Ji [mailto:[EMAIL PROTECTED]] 
  Sent: Thursday, December 26, 2002 3:59 PM To: Multiple recipients of list ORACLE-L Subject: RE: Cache on sysdate? --From 9i performance planning 
  manual 
  That's because doing dt := sysdate is more optimized, it's 
  still select sysdate into dt from dual but more 
  optimized. select from dual doing 4 db block 
  gets in 8i and 2 db block gets in 9i, but you can tune it to cut it 
  down. 
  Richard Ji 
  -Original Message- Sent: 
  Thursday, December 26, 2002 2:45 PM To: Multiple 
  recipients of list ORACLE-L 
   That's what I thought too. But the results of testing 
  are somewhat different. Maybe 
  it's evaluated within the PL/SQL engine and does not require a context 
  
  switch to the SQL engine. 
  Platform : Sun Solaris 2.6 Oracle : 8.1.7.4 (32 bit) 
  DEV:43#14739-23049@dual1 DEV:43#14739-23049declare  
  2 dt date;  3 
  begin  4  
  5 for i in 
  1..1  
  6 loop  7 select sysdate into dt from 
  dual;  8 -- dt := 
  sysdate;  9 end loop; 
  10 end; 11 
  / 
  PL/SQL procedure successfully completed. 
  Elapsed: 00:00:01.97 
  DEV:43#14739-23049@dual1 DEV:43#14739-23049declare  
  2 dt date;  3 
  begin  4  
  5 for i in 
  1..1  
  6 loop  7 -- select sysdate into dt from 
  dual;  8 dt := 
  sysdate;  9 end loop; 
  10 end; 11 
  / 
  PL/SQL procedure successfully completed. 
  Elapsed: 00:00:00.92 
  Regards, Denny 
  Quoting K Gopalakrishnan [EMAIL PROTECTED]: 
  
   Raj:   Both are same. It is internally translated as a select call to 
  dual.   KG 
--- "Jamadagni, Rajendra" 
  [EMAIL PROTECTED] wrote:   
  Can someone please explain me why you have to use  
 select sysdate   from dual   
  / when 
  
  my_date_Var := sysdate; just works fine? Maybe I am clueless ... but I can take 
  an   explanation  
 Raj  
   =  Have a nice 
  day !!  
    Best Regards,  K 
  Gopalakrishnan,  Bangalore, INDIA. 
  -- 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: Richard Ji  
  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: Rebuilding Indexes...

Here's a reason:

have you ever tried to find the three duplicate rows in a 12 million
row table without using the primary key constraint? I've had to disable
or drop the constraint in order to use the exceptions table. Once I do
that, even if I've built a separate index that enforces the primary key
constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow
the index to be rebuilt when I re-enable the primary key constraint, it
builds it in the default tablespace of the table owner, not where I
want it.

if anyone has a better way to fix this problem, I'm more than happy to
hear it! It's a data warehouse and the third party app has a bug we
can't find and on occasion sqlloads (via direct path) duplicate rows

Rachel

--- Jared Still [EMAIL PROTECTED] wrote:
 
 Though I have published a script for determining indexes that
 need to be rebuilt, and then rebuilding them,  I have to say that
 this is almost never necessary.
 
 Why are you rebuilding indexes?  About the only reason for ever
 doing so is that the BLEVEL = 5.
 
 goto asktom.oracle.com, and do a search on 'index rebuild'.
 
 Currently, the third article may be of interest.
 
 Jared
 
 On Thursday 26 December 2002 12:24, Richard Huntley wrote:
  Anyone have any useful scripts for doing this?
 
  TIA,
  Rich
 
 
 Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
 Content-Transfer-Encoding: 7bit
 Content-Description: 
 
 -- 
 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).
 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: Those Pesky Little Audit Files (ora_99999.aud)

They don't do a great job of monitoring as all they record is the fact
that someone logged in. But then the other auditing Oracle does (or did
in earlier versions, I haven't investigated it in 9i) didn't capture
much information either.

Since we used to automate, via cron, some of the startup/shutdown and
DBA functions, and we created an account within the database for the
oracle Unix account... the aud trace files never told us much of
anything.

I don't want to know only that someone tried to get in, I want to now
how he/she tried


--- Mogens_Nørgaard [EMAIL PROTECTED] wrote:
 They were put there in 7.1 in order to comply with some security 
 standard. And their purpose is exactly to prevent a dba from logging
 in 
 without being monitored. It's in the 7.1 new features manual, as far
 as 
 I remember.  That's also the version where it was suddenly not
 possible 
 for the poor deveopers to see their own tracefiles, except if they
 set 
 _trace_files_public=true.
 
 Mogens
 
 Rachel Carmichael wrote:
 
 IIRC, these files are generated whenever someone logs in as sysdba
 or
 internal. I don't know of any way to stop them.
 
 
 --- Kevin Lange [EMAIL PROTECTED] wrote:
   
 
 I thought I had these files stopped  but apparently not.
 
 Is there somone out there who can tell me how to stop the Audit
 files
 from
 appearing in the audit_file_dest ???   I thought if I set the
 audit_trail to
 false then these would stop as well ...   Apparently not.
 
 Anyone have an idea how to turn them off ??
 
 Thanks
 
 Kevin
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Kevin Lange
   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).
 
 
 
 
 
 __
 Do you Yahoo!?
 Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
 http://mailplus.yahoo.com
   
 
 
 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: Those Pesky Little Audit Files (ora_99999.aud)




O Oracle Guru's 
Please tell us, why _trace_files_public is *STILL* an underscore 
parameter??

Raj
__
Rajendra 
Jamadagni 
 MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot 
com
Any opinion expressed here is 
personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but 
having an opinion is an art!

  -Original Message-From: Mogens Nørgaard 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 2:09 
  AMTo: Multiple recipients of list ORACLE-LSubject: Re: 
  Those Pesky Little Audit Files (ora_9.aud)They were 
  put there in 7.1 in order to comply with some security standard. And their 
  purpose is exactly to prevent a dba from logging in without being monitored. 
  It's in the 7.1 new features manual, as far as I remember. That's also 
  the version where it was suddenly not possible for the poor deveopers to see 
  their own tracefiles, except if they set 
  _trace_files_public=true.Mogens
*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.*1

RE: Automatic backup on Oracle 9i

Title: RE: Automatic backup on Oracle 9i





To me Automatic Backup means the backup jobs/scripts written by resident script kiddies (AKA Unix Admins).


g
Raj
__
Rajendra Jamadagni  MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Tim Gorman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 26, 2002 10:34 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Automatic backup on Oracle 9i



Not familiar with this. Is this something in Oracle Enterprise Manager,
perhaps the backup manager forms?



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.*2

Re: Rebuilding Indexes...

2002-12-27 Thread Rick_Cale


If you know you have 3 duplicate records in the table then the PK must have
already been disabled so you have to rebuild anyway.  I do not see
where you had to disable in order to use the exception table. It was
already disabled therefore it probably not an app problem but a disable
constraint
problem unless direct load bypasses constraint checking which I am not
sure.

Rick



   
 
Rachel 
 
Carmichael   To: Multiple recipients of list ORACLE-L 
[EMAIL PROTECTED]
wisernet100@y   cc:   
 
ahoo.comSubject: Re: Rebuilding Indexes...
 
Sent by:   
 
[EMAIL PROTECTED] 
 
om 
 
   
 
   
 
12/27/2002 
 
07:43 AM   
 
Please respond 
 
to ORACLE-L
 
   
 
   
 




Here's a reason:

have you ever tried to find the three duplicate rows in a 12 million
row table without using the primary key constraint? I've had to disable
or drop the constraint in order to use the exceptions table. Once I do
that, even if I've built a separate index that enforces the primary key
constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow
the index to be rebuilt when I re-enable the primary key constraint, it
builds it in the default tablespace of the table owner, not where I
want it.

if anyone has a better way to fix this problem, I'm more than happy to
hear it! It's a data warehouse and the third party app has a bug we
can't find and on occasion sqlloads (via direct path) duplicate rows

Rachel

--- Jared Still [EMAIL PROTECTED] wrote:

 Though I have published a script for determining indexes that
 need to be rebuilt, and then rebuilding them,  I have to say that
 this is almost never necessary.

 Why are you rebuilding indexes?  About the only reason for ever
 doing so is that the BLEVEL = 5.

 goto asktom.oracle.com, and do a search on 'index rebuild'.

 Currently, the third article may be of interest.

 Jared

 On Thursday 26 December 2002 12:24, Richard Huntley wrote:
  Anyone have any useful scripts for doing this?
 
  TIA,
  Rich

 
 Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
 Content-Transfer-Encoding: 7bit
 Content-Description:
 
 --
 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).



__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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

RE: PMON seems to not close sessions in a timely manner eg Max

2002-12-27 Thread Bob Metelsky

 
  What do you get when run this on the server hosting Oracle:
 
  lsnrctl services
 
 
Waleed, thanks for your input. Here is what I have (below are my MTS
settings)

 MYDB   has 1 service handler(s)
   DEDICATED SERVER established:259 refused:1
 LOCAL SERVER
 MYDB   has 6 service handler(s)
   DEDICATED SERVER established:0 refused:0
 LOCAL SERVER
   DISPATCHER established:2 refused:0 current:2 max:4000 state:ready
 D004 machine: MYSERVER, pid: 683
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2276))
   DISPATCHER established:4 refused:0 current:0 max:4000 state:ready
 D003 machine: MYSERVER, pid: 636
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2275))
   DISPATCHER established:1 refused:0 current:1 max:4000 state:ready
 D002 machine: MYSERVER, pid: 534
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2274))
   DISPATCHER established:1 refused:0 current:1 max:4000 state:ready
 D001 machine: MYSERVER, pid: 301
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2273))
   DISPATCHER established:1 refused:0 current:0 max:4000 state:ready
 D000 machine: MYSERVER, pid: 668
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2272))
 DB2  has 1 service handler(s)
   DEDICATED SERVER established:0 refused:0
 LOCAL SERVER
 DB3  has 1 service handler(s)
   DEDICATED SERVER established:0 refused:0
 LOCAL SERVER

###
MTS_DISPATCHERS=(protocol=tcp)(dispatchers=5)(pool=on)(tick=1)
(connections=1000)(sessions=4000) 
MTS_MAX_DISPATCHERS=32
MTS_SERVERS=5
MTS_MAX_SERVERS=64
MTS_SERVICE=MYDB
MTS_LISTENER_ADDRESS =
(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=MYSERVER))

 
 Waleed, thanks for the reply here is what I have
 
 I have 26 sessions and 34 process, only one of which (my sqlplus
 session) that seems to indicate shared
 The rest seem to be remanents of the stored procedure.
 
 At the point of running this test, it was only myself 
 monitoring and the developer repeadatly running his app which 
  never ran the processses  37 in about 15 min
 
 How else can I analyse this?
 
 Im wondering why only one session is in shared mode and the 
 remainder are dedicated? And what mandates a shared 
 connection over a dedicated?
 
 My sql+ which initiated the shared session was simply 
 username/passwd@instance
 
 Thanks
 bob
 
 LOCALUSER@MYDB - select count(*) from v$session;
 
   COUNT(*)
 --
 26
 
 LOCALUSER@MYDB - select count(*) from v$process;
 
   COUNT(*)
 --
 37
 
 LOCALUSER@MYDB - select 
 decode(username,'SECUSER','LOCALUSER',NULL,'IS_NULL', 
 username), status,
   program, server from v$session;
 
 DECODE(US STATUS   PROGRAM
 SERVER
 - 
 
 -
 IS_NULL   ACTIVE   ORACLE.EXE
 DEDICATED
 IS_NULL   ACTIVE   ORACLE.EXE
 DEDICATED
 IS_NULL   ACTIVE   ORACLE.EXE
 DEDICATED
 IS_NULL   ACTIVE   ORACLE.EXE
 DEDICATED
 IS_NULL   ACTIVE   ORACLE.EXE
 DEDICATED
 IS_NULL   ACTIVE   ORACLE.EXE
 DEDICATED
 username  INACTIVE dbsnmp.exe
 DEDICATED
 LOCALUSER INACTIVE sqlplusw.exe
 NONE
 LOCALUSER INACTIVE sqlplusw.exe
 NONE
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 NONE
 LOCALUSER ACTIVE   sqlplusw.exe
 SHARED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 LOCALUSER INACTIVE
 DEDICATED
 
 26 rows selected.
 
 
  Do you know if the connections (sessions) created for the web
  app are shared or dedicated? If it's dedicated, did you try 
  to connect using sqlplus from that win2k server and other hosts?
  
  Trying to isolate the problem?
  
  Regards,
  
  Waleed
  
  
  -Original Message-
  Sent: Thursday, December 26, 2002 10:44 AM
  To: Multiple recipients of list ORACLE-L
  
  
  
   Does your app connect to the database using the listener 
 or directly 
   (BEQ) without specifying a connect string?
   
   It should connect using the listener and a connect string.
   
   Regards,
   
   Waleed
  
  
  Waleed
  
  Yes, it's a web application and uses a listener. The listener
  entry has not been modified. There was a vague reference in 
  the MTS docs that eluded to using a special connection 
  string. But I got the impression that you would use a special 
  connection string *if* you wanted the abillity to chooose 
  between direct connection or mts (shared).
  
  In my case Id simply like to have all connections to use MTS.
  
  Should I be using a special connection string or listener
  entry? If so where can I find some examples
  
  Here is my asp application

Re: Rebuilding Indexes...

2002-12-27 Thread Jack Silvey

Hey Rachel,

Consider using a non-unique index for your primary key constraint. If 
you prebuild it and then add the constraint, Oracle will not drop the 
index when you drop the PK constraint, and you can control the index 
build that a way (and build it in parallel to boot).

hth,

Jack




 Here's a reason:
 
 have you ever tried to find the three duplicate rows in a 12 million
 row table without using the primary key constraint? I've had to 
disable
 or drop the constraint in order to use the exceptions table. Once I do
 that, even if I've built a separate index that enforces the primary 
key
 constraint, Oracle drops the index. So I HAVE to rebuild it. If I 
allow
 the index to be rebuilt when I re-enable the primary key constraint, 
it
 builds it in the default tablespace of the table owner, not where I
 want it.
 
 if anyone has a better way to fix this problem, I'm more than happy to
 hear it! It's a data warehouse and the third party app has a bug we
 can't find and on occasion sqlloads (via direct path) duplicate rows
 
 Rachel
 
 --- Jared Still [EMAIL PROTECTED] wrote:
  
  Though I have published a script for determining indexes that
  need to be rebuilt, and then rebuilding them,  I have to say that
  this is almost never necessary.
  
  Why are you rebuilding indexes?  About the only reason for ever
  doing so is that the BLEVEL = 5.
  
  goto asktom.oracle.com, and do a search on 'index rebuild'.
  
  Currently, the third article may be of interest.
  
  Jared
  
  On Thursday 26 December 2002 12:24, Richard Huntley wrote:
   Anyone have any useful scripts for doing this?
  
   TIA,
   Rich
  
  
  Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
  Content-Transfer-Encoding: 7bit
  Content-Description: 
  
  -- 
  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).
  
 
 
 __
 Do you Yahoo!?
 Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
 http://mailplus.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).
 
 
 

Thanks,

Jack Silvey

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jack Silvey
  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).

Versioning the Database !

2002-12-27 Thread Rama, Shreekantha (CAP, CARD)

Hi, 

I want to version the Database for development, IT, QA and staging
environment. 
Can some one suggest different methods and best possible approach to
maintain the database. 

Database is in design stage  development has partially started.  We
are using MKS for versioning.

Regards
Shree
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rama, Shreekantha (CAP, CARD)
  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: Automatic backup on Oracle 9i

2002-12-27 Thread Stephen Lee

If this is rman backup, perhaps try granting sysdba to sys, or connecting to
target as sysdba?

 -Original Message-
 From: Sony kristanto [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, December 26, 2002 7:24 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Automatic backup on Oracle 9i

 Hi Listers,
 I'm new on Oracle Database 9i after I migrated from Oracle 8i.
 I try to use backup facility from Oracle 9i and I already follow the
 instructions how to activate the automatic backup but when I 
 see the status
 on history I get an error comment 'Failed'. I've try again 
 and again but the
 results are the same. Could someone out there tell me why it 
 can't runs. For
 your note I use 'SYS' as my user. I will really appreciate your help.

 Rgrds,

 Sony
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Sony kristanto
   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: Stephen Lee
  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: compile errors

2002-12-27 Thread Ehresmann, David

You are right.  I disabled the roles thru which the grants were made.  But
my schema owns the objects.  I have a userprivs script that shows my schema
owners privs.  The schema owner does not have any privs on the objects it
owns!  How can that be? And I tried granting to myself(the schema owner), as
you know you can't do that. What is the work around?

thanks

David Ehresmann.

-Original Message-
Sent: Thursday, December 26, 2002 7:19 PM
To: Multiple recipients of list ORACLE-L


Does the owner of the package have *direct* privs (not through a role) 
on the object in question?

 List,
 
 Can anybody tell me what is happening here.  I am constantly getting 
the
 PLS-00201 error when I try to compile.  It is looking inside the 
package at
 a procedure and saying the identifier must be declared.  I have gone 
over
 metalink docs and notes. I first compile the package spec and then the
 package body.  I get the following response:
 
 SQL alter package schema.p_messages compile package;
 
 Warning: Package altered with compilation errors.
 
 SQL show errors
 Errors for PACKAGE schema.P_MESSAGES:
 
 LINE/COL ERROR
 
  -

 
 193/5PL/SQL: Declaration ignored
 
 197/38   PLS-00201: identifier 'HSD_TYPES.T_RETURN_CODE' must be 
declared
 
 218/5PL/SQL: Declaration ignored
 
 219/34   PLS-00201: identifier 'HSD_TYPES.T_RETURN_CODE' must be 
declared
 
 
 
 SQL alter package schema.p_messages compile body;
 
 Warning: Package Body altered with compilation errors.
 
 SQL show errors
 Errors for PACKAGE BODY schema.P_MESSAGES:
 
 LINE/COL ERROR
 
  -

 
 0/0  PL/SQL: Compilation unit analysis terminated
 
 1/14 PLS-00905: object schema.P_MESSAGES is invalid
 
 1/14 PLS-00304: cannot compile body of 'P_MESSAGES' without its
 
  specification
 
  
 
 thanks,
 
 David Ehresmann 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Ehresmann, David
   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).
 
 
 

Thanks,

Jack Silvey

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jack Silvey
  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: Ehresmann, David
  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: Rebuilding Indexes...

2002-12-27 Thread Richard Huntley

Title: Rebuilding Indexes...



Thanks 
for the responses from all the great minds on this list! :)

-Original Message-From: Richard Huntley 
[mailto:[EMAIL PROTECTED]]Sent: Thursday, December 26, 2002 
3:24 PMTo: Multiple recipients of list ORACLE-LSubject: 
Rebuilding Indexes...
Anyone have any useful scripts for doing this? 

TIA, Rich

Re: join after saving rowid Value into a Field - For Design , Dev. Gurus

2002-12-27 Thread Stephane Paquette

You can use the rowid but do not keep it.
As a dev DBA I would not allow to store the rowid in a
table because its value is meaningless once you
export/import, ...


 --- VIVEK_SHARMA [EMAIL PROTECTED] a écrit :
 let us suppose there are two tables M and P. 
 both Contain the field emp_id. other columns may be
 different. 
  
  
 All records of M also Exist in P .Table M will have
 records in the range
 1-5 lakhs.
 P table will contain Additional Records such that
 the Total Number of
 Records in P is 15-20 times the number of records in
 M.
  
 one way to join the two tables is to say M.emp_id =
 P.emp_id. but
 because P has high number of records the select is
 slower.
  
 we found that select of a row from table P using
 rowid column was
 very QUICK . 
  
 Is it a Correct practice :-
  
 1) to Store the ROWID of Table P in M in a separate
 column (say
 P_rowid)
  
 2) Is it possible to do a Join like the follows :- 
  
 select field1, field2,... from M,P 
 where M.emd_id = '6223' 
 and like M.P_rowid = P.rowid
  
 this way we hoped to select from M table (which has
 less number of
 records) and do a rowid based select on P table,
 which we found out is
 not allowed by ORACLE. 
  
 we want a join because we want to create a view over
 table M and P.
  
 We do NOT want to use the following way :-
  
 select field1, field2 ..,P_rowid from M where emp_id
 = '6223' from a
 Cursor  passing it to the Query as follows :- 
  
 select * from P where rowid = P_rowid (selected
 earlier)
  
 Are there Some Standard Practices that Should be
 Followed during
 Designing Tables , Fields, SQL Writing ?
  
 Any Dos , Don'ts ?
  
  

=
Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données
Oracle and DB2 DBA, datawarehouse consultant
[EMAIL PROTECTED]

__
Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Stephane=20Paquette?=
  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: compile errors

2002-12-27 Thread Mercadante, Thomas F

David,

If the package is not too large, could you please show it (or the portions
of the package that are involved in the error) to us on the list so we can
see exactly what is going on?

We need to see where and how the object is being referenced.

thanks

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, December 27, 2002 9:49 AM
To: Multiple recipients of list ORACLE-L


You are right.  I disabled the roles thru which the grants were made.  But
my schema owns the objects.  I have a userprivs script that shows my schema
owners privs.  The schema owner does not have any privs on the objects it
owns!  How can that be? And I tried granting to myself(the schema owner), as
you know you can't do that. What is the work around?

thanks

David Ehresmann.

-Original Message-
Sent: Thursday, December 26, 2002 7:19 PM
To: Multiple recipients of list ORACLE-L


Does the owner of the package have *direct* privs (not through a role) 
on the object in question?

 List,
 
 Can anybody tell me what is happening here.  I am constantly getting 
the
 PLS-00201 error when I try to compile.  It is looking inside the 
package at
 a procedure and saying the identifier must be declared.  I have gone 
over
 metalink docs and notes. I first compile the package spec and then the
 package body.  I get the following response:
 
 SQL alter package schema.p_messages compile package;
 
 Warning: Package altered with compilation errors.
 
 SQL show errors
 Errors for PACKAGE schema.P_MESSAGES:
 
 LINE/COL ERROR
 
  -

 
 193/5PL/SQL: Declaration ignored
 
 197/38   PLS-00201: identifier 'HSD_TYPES.T_RETURN_CODE' must be 
declared
 
 218/5PL/SQL: Declaration ignored
 
 219/34   PLS-00201: identifier 'HSD_TYPES.T_RETURN_CODE' must be 
declared
 
 
 
 SQL alter package schema.p_messages compile body;
 
 Warning: Package Body altered with compilation errors.
 
 SQL show errors
 Errors for PACKAGE BODY schema.P_MESSAGES:
 
 LINE/COL ERROR
 
  -

 
 0/0  PL/SQL: Compilation unit analysis terminated
 
 1/14 PLS-00905: object schema.P_MESSAGES is invalid
 
 1/14 PLS-00304: cannot compile body of 'P_MESSAGES' without its
 
  specification
 
  
 
 thanks,
 
 David Ehresmann 
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Ehresmann, David
   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).
 
 
 

Thanks,

Jack Silvey

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jack Silvey
  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: Ehresmann, David
  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

RE: PMON seems to not close sessions in a timely manner eg Max

2002-12-27 Thread Khedr, Waleed

Try:

1) Force shared connections using  (SRVR=SHARED) in the tnsnames.ora.  
2) Change the service name for the MTS_service and restart te db and
listener. Make sure the service is registered with the listener. Add a new
entry pointing to the new service in tnsnames.ora and let you app use this
entry. I think the entry in tnsnames.ora will need to be like (service_name
= ) instead of  (sid = ).
3) sqlnet tracing may need to be done.

Regards,

Waleed

-Original Message-
Sent: Friday, December 27, 2002 8:49 AM
To: Multiple recipients of list ORACLE-L


 
  What do you get when run this on the server hosting Oracle:
 
  lsnrctl services
 
 
Waleed, thanks for your input. Here is what I have (below are my MTS
settings)

 MYDB   has 1 service handler(s)
   DEDICATED SERVER established:259 refused:1
 LOCAL SERVER
 MYDB   has 6 service handler(s)
   DEDICATED SERVER established:0 refused:0
 LOCAL SERVER
   DISPATCHER established:2 refused:0 current:2 max:4000 state:ready
 D004 machine: MYSERVER, pid: 683
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2276))
   DISPATCHER established:4 refused:0 current:0 max:4000 state:ready
 D003 machine: MYSERVER, pid: 636
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2275))
   DISPATCHER established:1 refused:0 current:1 max:4000 state:ready
 D002 machine: MYSERVER, pid: 534
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2274))
   DISPATCHER established:1 refused:0 current:1 max:4000 state:ready
 D001 machine: MYSERVER, pid: 301
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2273))
   DISPATCHER established:1 refused:0 current:0 max:4000 state:ready
 D000 machine: MYSERVER, pid: 668
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2272))
 DB2  has 1 service handler(s)
   DEDICATED SERVER established:0 refused:0
 LOCAL SERVER
 DB3  has 1 service handler(s)
   DEDICATED SERVER established:0 refused:0
 LOCAL SERVER

###
MTS_DISPATCHERS=(protocol=tcp)(dispatchers=5)(pool=on)(tick=1)
(connections=1000)(sessions=4000) 
MTS_MAX_DISPATCHERS=32
MTS_SERVERS=5
MTS_MAX_SERVERS=64
MTS_SERVICE=MYDB
MTS_LISTENER_ADDRESS =
(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=MYSERVER))

-- 
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: Versioning the Database !

2002-12-27 Thread Hemant K Chitale


Versioning the database ?
Take a backup of the database on a seperate tape each day !

What components of the database do you want to version ?  Table definitions ?
View definitions ? Packages/Procedures/Triggers ?
Code Objects should be versioned, but data objects [Tables/Indexes/Sequences]
would generally not vary once the design is done, save for a few 
changes/additions/enhancements.
Hemant

At 06:28 AM 27-12-02 -0800, you wrote:
Hi,

I want to version the Database for development, IT, QA and staging
environment.
Can some one suggest different methods and best possible approach to
maintain the database.

Database is in design stage  development has partially started.  We
are using MKS for versioning.

Regards
Shree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rama, Shreekantha (CAP, CARD)
  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
My web site page 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: Those Pesky Little Audit Files (ora_99999.aud)

2002-12-27 Thread Kevin Lange

Thanks.   Guess its clean-up job time.

-Original Message-
Sent: Thursday, December 26, 2002 7:59 PM
To: Multiple recipients of list ORACLE-L


IIRC, these files are generated whenever someone logs in as sysdba or
internal. I don't know of any way to stop them.


--- Kevin Lange [EMAIL PROTECTED] wrote:
 I thought I had these files stopped  but apparently not.
 
 Is there somone out there who can tell me how to stop the Audit files
 from
 appearing in the audit_file_dest ???   I thought if I set the
 audit_trail to
 false then these would stop as well ...   Apparently not.
 
 Anyone have an idea how to turn them off ??
 
 Thanks
 
 Kevin
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Kevin Lange
   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).
 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: Kevin Lange
  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).

Oracle.exe , CPU running at near 85%

2002-12-27 Thread Rick_Cale

Hi,

Oracle 8.1.6 on NT 4.0

Oracle.exe is running at about 85% CPU utilization.  What can I check to
see why that is the case?

Thanks
Rick

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

Re: join after saving rowid Value into a Field - For Design , Dev. Gurus

2002-12-27 Thread tim

Developers can also use the approach that Oracle uses with
UROWID values, which are stored in secondary indexes on IOTs
(i.e. replacing ROWIDs used in normal indexes).

Store the ROWID as well as the PK/UK column values.  Use the
following algorithm to retrieve in future:

   1. Retrieve the PK/UK values by ROWID
   2. Compare retrieved PK/UK values to those stored
   3. If PK/UK values do not match, then retrieve again by
PK/UK

The upsides and downsides should be pretty obvious, but it
is certainly safe...

 
 You can use the rowid but do not keep it.
 As a dev DBA I would not allow to store the rowid in a
 table because its value is meaningless once you
 export/import, ...
 
 
  --- VIVEK_SHARMA [EMAIL PROTECTED] a écrit :
  let us suppose there are two tables M and P. 
  both Contain the field emp_id. other columns may be
  different. 
   
   
  All records of M also Exist in P .Table M will have
  records in the range
  1-5 lakhs.
  P table will contain Additional Records such that
  the Total Number of
  Records in P is 15-20 times the number of records in
  M.
   
  one way to join the two tables is to say M.emp_id   P.emp_id. but
  because P has high number of records the select is
  slower.
   
  we found that select of a row from table P using
  rowid column was
  very QUICK . 
   
  Is it a Correct practice :-
   
  1) to Store the ROWID of Table P in M in a separate
  column (say
  P_rowid)
   
  2) Is it possible to do a Join like the follows :- 
   
  select field1, field2,... from M,P 
  where M.emd_id   and like M.P_rowid
  this way we hoped to select from M table (which has
  less number of
  records) and do a rowid based select on P table,
  which we found out is
  not allowed by ORACLE. 
   
  we want a join because we want to create a view over
  table M and P.
   
  We do NOT want to use the following way :-
   
  select field1, field2 ..,P_rowid from M where emp_id
Cursor  passing it to the Query as follows :- 
   
  select * from P where rowid   earlier)
   
  Are there Some Standard Practices that Should be
  Followed during
  Designing Tables , Fields, SQL Writing ?
   
  Any Dos , Don'ts ?
   
   
 
  Stéphane Paquette
 DBA Oracle et DB2, consultant entrepôt de données
 Oracle and DB2 DBA, datawarehouse consultant
 [EMAIL PROTECTED]
 
 __
 Lèche-vitrine ou lèche-écran ?
 magasinage.yahoo.ca
 -- 
 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: 
  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: PMON seems to not close sessions in a timely manner eg Max

2002-12-27 Thread Jeremy Pulcifer

Title: Message









Tehe, don't worry, Bob, the
developers here work for me, so I can be as un-diplomatic as I wanna be.



I don't know how you would do it in
Micro$oft; perhaps some kind of component (.NET? DCOM?) could do this for them.



I can do it in Java and Perl. Can't
imagine that there isn't some mechanism out there for ASP/M$. 



-Original Message-
From: Bob Metelsky
[mailto:[EMAIL PROTECTED]] 
Sent: Thursday, December 26, 2002
8:14 AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: PMON seems to not
close sessions in a timely manner eg Max 





 From: Bob Metelsky [mailto:[EMAIL PROTECTED]]

 
 
 I have a developers stored
procedure making repeaded calls 
 (logons/request for data and
logoff) 
 Each call takes only seconds
but it seems like PMON dosnt close the 
 process in a timely manner,
leaving what look to be ghost 
 process. If a 
 few users hit the same app the
processes go to = 50 then I get the 
 dreaded ORA-nnn max
processes (50) reached 





1. 50 max processes? Damn, dude, you're choking that
thing to death. 

2. That is one of the sorriest excuses for a data
access methodology that exists. Tell them to start pooling their database
connections or you'll turn your max processes down even further.

Hummm I have to
be more diplomatic than that .. ;-)

They are usingthe
Oracle 8.17 provider with a connection string like so

Connect=Provider=OraOLEDB.Oracle;PLSQLRSet=1;Password=a;Persist
Security Info=True;User ID=a;Data Source=mydb

Jeremy, do you have any
examples of using pooled connections with active server pages? I'd like to be
able to at least point them in the right direction and also have the
information myself



many thanks

Bob

RE: Those Pesky Little Audit Files (ora_99999.aud)

2002-12-27 Thread Mercadante, Thomas F

that's what I do Kevin.  I have a cron job that cleans up all of the Oracle
log files.  These audit files, Listener logs, Alert Logs, Trace files etc.
I run it twice a month, deleting anything that is 30 days or older.  rename
alert logs and listener logs, rman's sbtio.log file so that they will be
deleted by a later run.

works for me.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, December 27, 2002 10:39 AM
To: Multiple recipients of list ORACLE-L


Thanks.   Guess its clean-up job time.

-Original Message-
Sent: Thursday, December 26, 2002 7:59 PM
To: Multiple recipients of list ORACLE-L


IIRC, these files are generated whenever someone logs in as sysdba or
internal. I don't know of any way to stop them.


--- Kevin Lange [EMAIL PROTECTED] wrote:
 I thought I had these files stopped  but apparently not.
 
 Is there somone out there who can tell me how to stop the Audit files
 from
 appearing in the audit_file_dest ???   I thought if I set the
 audit_trail to
 false then these would stop as well ...   Apparently not.
 
 Anyone have an idea how to turn them off ??
 
 Thanks
 
 Kevin
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Kevin Lange
   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).
 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: Kevin Lange
  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: Rebuilding Indexes...

2002-12-27 Thread Denny Koovakattu



  If you build a separate index to enforce the primary key, Oracle shouldn't 
drop it when you disable or drop the primary key.

Regards,
Denny

Quoting Rachel Carmichael [EMAIL PROTECTED]:

 Here's a reason:
 
 have you ever tried to find the three duplicate rows in a 12 million
 row table without using the primary key constraint? I've had to
 disable
 or drop the constraint in order to use the exceptions table. Once I do
 that, even if I've built a separate index that enforces the primary
 key
 constraint, Oracle drops the index. So I HAVE to rebuild it. If I
 allow
 the index to be rebuilt when I re-enable the primary key constraint,
 it
 builds it in the default tablespace of the table owner, not where I
 want it.
 
 if anyone has a better way to fix this problem, I'm more than happy to
 hear it! It's a data warehouse and the third party app has a bug we
 can't find and on occasion sqlloads (via direct path) duplicate rows
 
 Rachel
 
 --- Jared Still [EMAIL PROTECTED] wrote:
  
  Though I have published a script for determining indexes that
  need to be rebuilt, and then rebuilding them,  I have to say that
  this is almost never necessary.
  
  Why are you rebuilding indexes?  About the only reason for ever
  doing so is that the BLEVEL = 5.
  
  goto asktom.oracle.com, and do a search on 'index rebuild'.
  
  Currently, the third article may be of interest.
  
  Jared
  
  On Thursday 26 December 2002 12:24, Richard Huntley wrote:
   Anyone have any useful scripts for doing this?
  
   TIA,
   Rich
  
  
  Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
  Content-Transfer-Encoding: 7bit
  Content-Description: 
  
  -- 
  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).
  
 
 
 __
 Do you Yahoo!?
 Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
 http://mailplus.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: Denny Koovakattu
  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: PMON seems to not close sessions in a timely manner eg Max





And finally, although I hate asking the question: Why are you running MTS
in the first place? I'm not saying there aren't good reasons for it - I'm
just curious. Or to be "funny": I've solved many MTS-problems in my time
by turning it off. However, that might not be possible or sensible in all
cases.

Mogens

Khedr, Waleed wrote:

  Try:

1) Force shared connections using  (SRVR=SHARED) in the tnsnames.ora.  
2) Change the service name for the MTS_service and restart te db and
listener. Make sure the service is registered with the listener. Add a new
entry pointing to the new service in tnsnames.ora and let you app use this
entry. I think the entry in tnsnames.ora will need to be like (service_name
= ) instead of  (sid = ).
3) sqlnet tracing may need to be done.

Regards,

Waleed

-Original Message-
Sent: Friday, December 27, 2002 8:49 AM
To: Multiple recipients of list ORACLE-L


  
  
 What do you get when run this on the server hosting Oracle:

 lsnrctl services



  
  Waleed, thanks for your input. Here is what I have (below are my MTS
settings)

 MYDB   has 1 service handler(s)
   DEDICATED SERVER established:259 refused:1
 LOCAL SERVER
 MYDB   has 6 service handler(s)
   DEDICATED SERVER established:0 refused:0
 LOCAL SERVER
   DISPATCHER established:2 refused:0 current:2 max:4000 state:ready
 D004 machine: MYSERVER, pid: 683
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2276))
   DISPATCHER established:4 refused:0 current:0 max:4000 state:ready
 D003 machine: MYSERVER, pid: 636
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2275))
   DISPATCHER established:1 refused:0 current:1 max:4000 state:ready
 D002 machine: MYSERVER, pid: 534
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2274))
   DISPATCHER established:1 refused:0 current:1 max:4000 state:ready
 D001 machine: MYSERVER, pid: 301
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2273))
   DISPATCHER established:1 refused:0 current:0 max:4000 state:ready
 D000 machine: MYSERVER, pid: 668
 (ADDRESS=(PROTOCOL=tcp)(HOST=MYSERVER.MYDOMAIN.com)(PORT=2272))
 DB2  has 1 service handler(s)
   DEDICATED SERVER established:0 refused:0
 LOCAL SERVER
 DB3  has 1 service handler(s)
   DEDICATED SERVER established:0 refused:0
 LOCAL SERVER

###
MTS_DISPATCHERS="(protocol=tcp)(dispatchers=5)(pool=on)(tick=1)
(connections=1000)(sessions=4000)" 
MTS_MAX_DISPATCHERS=32
MTS_SERVERS=5
MTS_MAX_SERVERS=64
MTS_SERVICE=MYDB
MTS_LISTENER_ADDRESS =
"(ADDRESS=(PROTOCOL=TCP)(PORT=1521)(HOST=MYSERVER))"

Re: Rebuilding Indexes...

9.2.0.1 Solaris, and yes, it does drop it

I created a unique index in the primary key columns
I created the primary key constraint without specifying an index
I checked that the index existed, it did
I dropped the primary key constraint
I checked that the index existed, it didn't

try it I tried various combinations before posting this note


--- Denny Koovakattu [EMAIL PROTECTED] wrote:
 
 
   If you build a separate index to enforce the primary key, Oracle
 shouldn't 
 drop it when you disable or drop the primary key.
 
 Regards,
 Denny
 
 Quoting Rachel Carmichael [EMAIL PROTECTED]:
 
  Here's a reason:
  
  have you ever tried to find the three duplicate rows in a 12
 million
  row table without using the primary key constraint? I've had to
  disable
  or drop the constraint in order to use the exceptions table. Once I
 do
  that, even if I've built a separate index that enforces the primary
  key
  constraint, Oracle drops the index. So I HAVE to rebuild it. If I
  allow
  the index to be rebuilt when I re-enable the primary key
 constraint,
  it
  builds it in the default tablespace of the table owner, not where I
  want it.
  
  if anyone has a better way to fix this problem, I'm more than happy
 to
  hear it! It's a data warehouse and the third party app has a bug we
  can't find and on occasion sqlloads (via direct path) duplicate
 rows
  
  Rachel
  
  --- Jared Still [EMAIL PROTECTED] wrote:
   
   Though I have published a script for determining indexes that
   need to be rebuilt, and then rebuilding them,  I have to say that
   this is almost never necessary.
   
   Why are you rebuilding indexes?  About the only reason for ever
   doing so is that the BLEVEL = 5.
   
   goto asktom.oracle.com, and do a search on 'index rebuild'.
   
   Currently, the third article may be of interest.
   
   Jared
   
   On Thursday 26 December 2002 12:24, Richard Huntley wrote:
Anyone have any useful scripts for doing this?
   
TIA,
Rich
   
   
   Content-Type: text/html; charset=iso-8859-1; name=Attachment:
 1
   Content-Transfer-Encoding: 7bit
   Content-Description: 
   
   -- 
   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).
   
  
  
  __
  Do you Yahoo!?
  Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
  http://mailplus.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: Denny Koovakattu
   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).
 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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

Re: Those Pesky Little Audit Files (ora_99999.aud)





Yeah, it's a nuisance in most installations, but the idea is to be compliant
with some abbreviation_that_I'm_sure_Tim_can_remember security standard.
Give me a 7.1 doc site (if it exists) and I'll find the details. I failed
to find 7.1 doc on Google searches. Probably too much beer.

Mogens

Jamadagni, Rajendra wrote:
  
  
  
   
  
 
  O Oracle Guru's 
 
  Please tell us, why _trace_files_public is *STILL*
an underscore  parameter??
 
  
 
  Raj
 
  __
 
  Rajendra  Jamadagni
  MIS, ESPN Inc.
 
  Rajendra dot Jamadagni
at ESPN dot  com
 
  Any opinion expressed
here is  personal and doesn't reflect that of ESPN Inc. 
 
  QOTD: Any clod can have
facts, but  having an opinion is
an art!
 
 
-Original Message-
From: Mogens Nrgaard[mailto:[EMAIL PROTECTED]]
Sent: Friday, December 27, 2002 2:09AM
To: Multiple recipients of list ORACLE-L
Subject: Re:Those Pesky Little Audit Files (ora_9.aud)


They wereput there in 7.1 in order to comply with some security standard.
And theirpurpose is exactly to prevent a dba from logging in without
being monitored.It's in the 7.1 new features manual, as far as I remember.
That's alsothe version where it was suddenly not possible for the poor
deveopers to seetheir own tracefiles, except if they set_trace_files_public=true.

Mogens
  
  

*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.*1

RE: Those Pesky Little Audit Files (ora_99999.aud)

I have 71620 for DG/UX ... tell me what to look for
...

Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot
com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but
having an opinion is an art!

-Original Message-From: Mogens Nørgaard
[mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 11:49
AMTo: Multiple recipients of list ORACLE-LSubject: Re:
Those Pesky Little Audit Files (ora_9.aud)Yeah, it's
a nuisance in most installations, but the idea is to be compliant with some
abbreviation_that_I'm_sure_Tim_can_remember security standard. Give me
a 7.1 doc site (if it exists) and I'll find the details. I failed to find 7.1
doc on Google searches. Probably too much
beer.MogensJamadagni, Rajendra wrote:

O Oracle Guru's
Please tell us, why _trace_files_public is *STILL* an underscore
parameter??

Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN
dot com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts,
but having an opinion is an
art!

-Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]Sent:
Friday, December 27, 2002 2:09 AMTo: Multiple recipients of
list ORACLE-LSubject: Re: Those Pesky Little Audit Files
(ora_9.aud)They were put there in 7.1 in order to
comply with some security standard. And their purpose is exactly to
prevent a dba from logging in without being monitored. It's in the 7.1 new
features manual, as far as I remember. That's also the version where
it was suddenly not possible for the poor deveopers to see their own
tracefiles, except if they set
_trace_files_public=true.Mogens
*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.*1

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.*2

Re: Rebuilding Indexes...


Yes, but that's a special case.  You are not rebuilding
the index as part of some regular index maintenance.

Jared

On Friday 27 December 2002 04:43, Rachel Carmichael wrote:
 Here's a reason:

 have you ever tried to find the three duplicate rows in a 12 million
 row table without using the primary key constraint? I've had to disable
 or drop the constraint in order to use the exceptions table. Once I do
 that, even if I've built a separate index that enforces the primary key
 constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow
 the index to be rebuilt when I re-enable the primary key constraint, it
 builds it in the default tablespace of the table owner, not where I
 want it.

 if anyone has a better way to fix this problem, I'm more than happy to
 hear it! It's a data warehouse and the third party app has a bug we
 can't find and on occasion sqlloads (via direct path) duplicate rows

 Rachel

 --- Jared Still [EMAIL PROTECTED] wrote:
  Though I have published a script for determining indexes that
  need to be rebuilt, and then rebuilding them,  I have to say that
  this is almost never necessary.
 
  Why are you rebuilding indexes?  About the only reason for ever
  doing so is that the BLEVEL = 5.
 
  goto asktom.oracle.com, and do a search on 'index rebuild'.
 
  Currently, the third article may be of interest.
 
  Jared
 
  On Thursday 26 December 2002 12:24, Richard Huntley wrote:
   Anyone have any useful scripts for doing this?
  
   TIA,
   Rich
 
  
  Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
  Content-Transfer-Encoding: 7bit
  Content-Description:
  
  --
  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).

 __
 Do you Yahoo!?
 Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
 http://mailplus.yahoo.com
-- 
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).

Re: Rebuilding Indexes...

2002-12-27 Thread Denny Koovakattu



  I don't have access to 9.2.0.1 right now. But can you try creating a non-
unique index instead of the unique index. If you create a unique index, it gets 
dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it 
shouldn't get dropped.

Regards,
Denny

Quoting Rachel Carmichael [EMAIL PROTECTED]:

 9.2.0.1 Solaris, and yes, it does drop it
 
 I created a unique index in the primary key columns
 I created the primary key constraint without specifying an index
 I checked that the index existed, it did
 I dropped the primary key constraint
 I checked that the index existed, it didn't
 
 try it I tried various combinations before posting this note
 
 
 --- Denny Koovakattu [EMAIL PROTECTED] wrote:
  
  
If you build a separate index to enforce the primary key, Oracle
  shouldn't 
  drop it when you disable or drop the primary key.
  
  Regards,
  Denny
  
  Quoting Rachel Carmichael [EMAIL PROTECTED]:
  
   Here's a reason:
   
   have you ever tried to find the three duplicate rows in a 12
  million
   row table without using the primary key constraint? I've had to
   disable
   or drop the constraint in order to use the exceptions table. Once
 I
  do
   that, even if I've built a separate index that enforces the
 primary
   key
   constraint, Oracle drops the index. So I HAVE to rebuild it. If I
   allow
   the index to be rebuilt when I re-enable the primary key
  constraint,
   it
   builds it in the default tablespace of the table owner, not where
 I
   want it.
   
   if anyone has a better way to fix this problem, I'm more than
 happy
  to
   hear it! It's a data warehouse and the third party app has a bug
 we
   can't find and on occasion sqlloads (via direct path) duplicate
  rows
   
   Rachel
   
   --- Jared Still [EMAIL PROTECTED] wrote:

Though I have published a script for determining indexes that
need to be rebuilt, and then rebuilding them,  I have to say
 that
this is almost never necessary.

Why are you rebuilding indexes?  About the only reason for ever
doing so is that the BLEVEL = 5.

goto asktom.oracle.com, and do a search on 'index rebuild'.

Currently, the third article may be of interest.

Jared

On Thursday 26 December 2002 12:24, Richard Huntley wrote:
 Anyone have any useful scripts for doing this?

 TIA,
 Rich


Content-Type: text/html; charset=iso-8859-1; name=Attachment:
  1
Content-Transfer-Encoding: 7bit
Content-Description: 

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

   
   
   __
   Do you Yahoo!?
   Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
   http://mailplus.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: Denny Koovakattu
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).
  
 
 
 __
 Do you Yahoo!?
 Yahoo!

Re: Rebuilding Indexes...


Rick,

You're not considering a PK built with 'enable novalidate'.

Jared


On Friday 27 December 2002 05:38, [EMAIL PROTECTED] wrote:
 If you know you have 3 duplicate records in the table then the PK must have
 already been disabled so you have to rebuild anyway.  I do not see
 where you had to disable in order to use the exception table. It was
 already disabled therefore it probably not an app problem but a disable
 constraint
 problem unless direct load bypasses constraint checking which I am not
 sure.

 Rick




 Rachel
 Carmichael   To: Multiple recipients of
 list ORACLE-L [EMAIL PROTECTED] wisernet100@y   cc:
 ahoo.comSubject: Re: Rebuilding
 Indexes... Sent by:
 [EMAIL PROTECTED]
 om


 12/27/2002
 07:43 AM
 Please respond
 to ORACLE-L






 Here's a reason:

 have you ever tried to find the three duplicate rows in a 12 million
 row table without using the primary key constraint? I've had to disable
 or drop the constraint in order to use the exceptions table. Once I do
 that, even if I've built a separate index that enforces the primary key
 constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow
 the index to be rebuilt when I re-enable the primary key constraint, it
 builds it in the default tablespace of the table owner, not where I
 want it.

 if anyone has a better way to fix this problem, I'm more than happy to
 hear it! It's a data warehouse and the third party app has a bug we
 can't find and on occasion sqlloads (via direct path) duplicate rows

 Rachel

 --- Jared Still [EMAIL PROTECTED] wrote:
  Though I have published a script for determining indexes that
  need to be rebuilt, and then rebuilding them,  I have to say that
  this is almost never necessary.
 
  Why are you rebuilding indexes?  About the only reason for ever
  doing so is that the BLEVEL = 5.
 
  goto asktom.oracle.com, and do a search on 'index rebuild'.
 
  Currently, the third article may be of interest.
 
  Jared
 
  On Thursday 26 December 2002 12:24, Richard Huntley wrote:
   Anyone have any useful scripts for doing this?
  
   TIA,
   Rich
 
  
  Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
  Content-Transfer-Encoding: 7bit
  Content-Description:
  
  --
  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).

 __
 Do you Yahoo!?
 Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
 http://mailplus.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: 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).

Re: Rebuilding Indexes...


Yeah, it sure does, if the index is unique.

Try out this test:

drop table y;

create table y(y number);
create unique index ypkidx on y(y);
alter table y add constraint ypk primary key(y);
alter table y drop primary key;

select table_name, index_name
from user_indexes
where index_name = 'YPKIDX'
/


drop table y;

create table y(y number);
create index ypkidx on y(y);
alter table y add constraint ypk primary key(y);
alter table y drop primary key;

select table_name, index_name
from user_indexes
where index_name = 'YPKIDX'
/


Notice that the non unique index will still be available, as Jack
has already pointed out.

Don't know why the behaviors are different.

Jared


On Friday 27 December 2002 08:14, Denny Koovakattu wrote:
   If you build a separate index to enforce the primary key, Oracle
 shouldn't drop it when you disable or drop the primary key.

 Regards,
 Denny

 Quoting Rachel Carmichael [EMAIL PROTECTED]:
  Here's a reason:
 
  have you ever tried to find the three duplicate rows in a 12 million
  row table without using the primary key constraint? I've had to
  disable
  or drop the constraint in order to use the exceptions table. Once I do
  that, even if I've built a separate index that enforces the primary
  key
  constraint, Oracle drops the index. So I HAVE to rebuild it. If I
  allow
  the index to be rebuilt when I re-enable the primary key constraint,
  it
  builds it in the default tablespace of the table owner, not where I
  want it.
 
  if anyone has a better way to fix this problem, I'm more than happy to
  hear it! It's a data warehouse and the third party app has a bug we
  can't find and on occasion sqlloads (via direct path) duplicate rows
 
  Rachel
 
  --- Jared Still [EMAIL PROTECTED] wrote:
   Though I have published a script for determining indexes that
   need to be rebuilt, and then rebuilding them,  I have to say that
   this is almost never necessary.
  
   Why are you rebuilding indexes?  About the only reason for ever
   doing so is that the BLEVEL = 5.
  
   goto asktom.oracle.com, and do a search on 'index rebuild'.
  
   Currently, the third article may be of interest.
  
   Jared
  
   On Thursday 26 December 2002 12:24, Richard Huntley wrote:
Anyone have any useful scripts for doing this?
   
TIA,
Rich
  
   
   Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
   Content-Transfer-Encoding: 7bit
   Content-Description:
   
   --
   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).
 
  __
  Do you Yahoo!?
  Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
  http://mailplus.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: 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).

RE: Those Pesky Little Audit Files (ora_99999.aud)

2002-12-27 Thread Orr, Steve

Title: RE: Those Pesky Little Audit Files (ora_9.aud)





Yupp, I do the same thing. I figure if there's a problem documented somewhere in those files and I haven't responded to them in 30-60 days then its too old to worry about anyway. Sometimes OWS wants an alert log which goes back to the beginning of time but I just tell 'em to get real. I just up'd the listerner log renaming routine to run on a daily basis because it was too big too view. Now the average size of the listener.julianday file is 20-50MB. The process of cleaning up oracle log and trace files is like a pooper-scooper at the end of the rodeo parade. 


Steve Orr
Bozeman, Montana




-Original Message-
From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 27, 2002 9:05 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Those Pesky Little Audit Files (ora_9.aud)



that's what I do Kevin. I have a cron job that cleans up all of the Oracle
log files. These audit files, Listener logs, Alert Logs, Trace files etc.
I run it twice a month, deleting anything that is 30 days or older. rename
alert logs and listener logs, rman's sbtio.log file so that they will be
deleted by a later run.


works for me.


Tom Mercadante
Oracle Certified Professional



-Original Message-
Sent: Friday, December 27, 2002 10:39 AM
To: Multiple recipients of list ORACLE-L



Thanks. Guess its clean-up job time.


-Original Message-
Sent: Thursday, December 26, 2002 7:59 PM
To: Multiple recipients of list ORACLE-L



IIRC, these files are generated whenever someone logs in as sysdba or
internal. I don't know of any way to stop them.



--- Kevin Lange [EMAIL PROTECTED] wrote:
 I thought I had these files stopped  but apparently not.
 
 Is there somone out there who can tell me how to stop the Audit files
 from
 appearing in the audit_file_dest ??? I thought if I set the
 audit_trail to
 false then these would stop as well ... Apparently not.
 
 Anyone have an idea how to turn them off ??
 
 Thanks
 
 Kevin
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Kevin Lange
 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).
 



__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: Kevin Lange
 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: Versioning the Database !

2002-12-27 Thread ora ak

I generally follow as a practice to keep a variable v_version at package level or any script level . ( i have only packages ) and this v_version is nothing but $header$ in mks . This way I can always run a query to find out object versions in db . This is REALLY helpful specially when code is wrapped in production .

-oramagic .

Hemant K Chitale [EMAIL PROTECTED] wrote:
Versioning the database ?Take a backup of the database on a seperate tape each day !What components of the database do you want to version ? Table definitions ?View definitions ? Packages/Procedures/Triggers ?Code Objects should be versioned, but data objects [Tables/Indexes/Sequences]would generally not vary once the design is done, save for a few changes/additions/enhancements.HemantAt 06:28 AM 27-12-02 -0800, you wrote:Hi, I want to version the Database for development, IT, QA and stagingenvironment. Can some one suggest different methods and best possible approach tomaintain the database. Database is in design stage development has partially started. Weare using MKS for versioning.RegardsShree--Please see the official ORACLE-L !
FAQ: http://www.orafaq.net--Author: Rama, Shreekantha (CAP, CARD) INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).Hemant K ChitaleMy web site page is : http://hkchital.tripod.com-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Hemant K ChitaleINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 h!
ttp://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

RE: Row Migration

2002-12-27 Thread Nick Wagner




We've 
done a few tests here with chained vs. unchained rows, and the impact is 
anywhere from 50-200% overhead. So if it took about 10 seconds to do 
a query it will now take 15 to 30 seconds. It seamed to depend most 
on which rows we were returning... not hitting the chained rows as much helped 
speed it up. 

For 
each row operation, Oracle must read the block that contains the data, and the 
last piece of information in each block contains a null/not null rowid pointer 
to the next row piece. In a spanned row (one inserted that is too big for 
a single DB_BLOCK) the pointer (usually)points to the next physical block 
in the DB, and it goes pretty fast. In a chained row (one where someone 
has done an update, and the new information put into the row does not fit into 
the rest of the block -- which sounds like your case) the pointer (usually) 
points to a block at the end of the physical table in the DB file that contains 
the rest of the information. And it goes very slowly. 
Chaining can really grow to be progressively worse, if you continually update a 
column who spans the two blocks, oracle will not update the first block or the 
last block and instead create another new block at the end of the table for 
those new characters. So a read of that column now takes in 3 blocks, 
potentially spanned over the entire datafile. 

In 
Oracle 9i we've seen some really strange behavior too... when doing an import, 
or direct load Oracle will actually chain a row inside of a block, and none of 
the analyze for chained row commands will pick it up.It still causes 
the slow down, but you cannot fix it. 

It's 
actually been a while since I've really been able to look at this stuff, so if 
anyone has any clarifications or things they want to add, please do so. 


Nick


-Original Message-From: Larry Elkins 
[mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 3:19 
AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row 
Migration
Well, 
yes, Iwould agree with that ;-) 

What 
we are trying to determine here in this particular case is how much or what 
percentage of the slowdown in the process is due to the migration of rows. We 
aren't ready (until we do some testing) tomake a blanket statement that 
row migration *alone* is the cause of the significant slowdown. In other words, 
I'm not willing to make a statement to the powers that be that simply increasing 
the pctfree is going to make things normal again until we have a chance to do 
some more detailed monitoring and testing.

Regards,Larry G. 
Elkins[EMAIL PROTECTED]214.954.1781 

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar NSent: 
  Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Re: Row Migration
  yes, row migration will 
  degrade the performance..

Re: Rebuilding Indexes...

2002-12-27 Thread Jack Silvey

Rachel,

Try a pre-created non-unique index. This should remain after the 
constraint it dropped, and can be used to enforce the primary key 
constraint (not to mention be created in parallel nologging mode.)

hth,

Jack

 9.2.0.1 Solaris, and yes, it does drop it
 
 I created a unique index in the primary key columns
 I created the primary key constraint without specifying an index
 I checked that the index existed, it did
 I dropped the primary key constraint
 I checked that the index existed, it didn't
 
 try it I tried various combinations before posting this note
 
 
 --- Denny Koovakattu [EMAIL PROTECTED] wrote:
  
  
If you build a separate index to enforce the primary key, Oracle
  shouldn't 
  drop it when you disable or drop the primary key.
  
  Regards,
  Denny
  
  Quoting Rachel Carmichael [EMAIL PROTECTED]:
  
   Here's a reason:
   
   have you ever tried to find the three duplicate rows in a 12
  million
   row table without using the primary key constraint? I've had to
   disable
   or drop the constraint in order to use the exceptions table. Once 
I
  do
   that, even if I've built a separate index that enforces the 
primary
   key
   constraint, Oracle drops the index. So I HAVE to rebuild it. If I
   allow
   the index to be rebuilt when I re-enable the primary key
  constraint,
   it
   builds it in the default tablespace of the table owner, not where 
I
   want it.
   
   if anyone has a better way to fix this problem, I'm more than 
happy
  to
   hear it! It's a data warehouse and the third party app has a bug 
we
   can't find and on occasion sqlloads (via direct path) duplicate
  rows
   
   Rachel
   
   --- Jared Still [EMAIL PROTECTED] wrote:

Though I have published a script for determining indexes that
need to be rebuilt, and then rebuilding them,  I have to say 
that
this is almost never necessary.

Why are you rebuilding indexes?  About the only reason for ever
doing so is that the BLEVEL = 5.

goto asktom.oracle.com, and do a search on 'index rebuild'.

Currently, the third article may be of interest.

Jared

On Thursday 26 December 2002 12:24, Richard Huntley wrote:
 Anyone have any useful scripts for doing this?

 TIA,
 Rich


Content-Type: text/html; charset=iso-8859-1; name=Attachment:
  1
Content-Transfer-Encoding: 7bit
Content-Description: 

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

   
   
   __
   Do you Yahoo!?
   Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
   http://mailplus.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: Denny Koovakattu
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).
  
 
 
 __
 Do you Yahoo!?
 Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
 http://mailplus.yahoo.com
 -- 
 Please see the official

RE: Row Migration

2002-12-27 Thread John Kanagaraj

Larry,

Don't want to preach to the Guru, but have you checked the values for 'table
fetch continued row'? 

StatisticTotal   per Secondper Trans
-   
table fetch by rowid   577,820,727 40,129.2 61,248.8
table fetch continued row  137,202  9.5 14.5

This when coming out of V$SESSTAT could give a good indication of number of
fetches by migrated as well as chained rows for that session. You could also
look at V$SESSION.MAX_WAIT for 'db file sequential read' events...

Let us know what you find!
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

What would you see if you were allowed to look back at your life at the end
of your journey in this earth?

** The opinions and statements above are entirely my own and not those of my
employer or clients **


 -Original Message-
 From: Larry Elkins [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, December 26, 2002 4:09 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Row Migration
 
 
 Listers,
 
 8.1.7.4 64 Bit Solaris
 
 Does row migration utilize DB File Sequential Reads on the 
 table? Off the
 top of my head I would expect so, but I've never tested 
 something like that
 before.
 
 Trying to figure out if row migration is the cause of the 
 slowdown in a
 package (well, it's probably slowing it down, just trying to gauge the
 impact). PctFree is 10, and new feeds contain lots of 
 elements that had been
 empty before. As a result, a very large number of rows are 
 being updated
 with the new info being applied, effectively doubling the row 
 length. Would
 certainly expect row migration to occur. When running, 
 execution time has
 quadrupled, and we see significant waits on DB File 
 Sequential Reads, with
 the file/block values and dba_extents indicating the table, 
 not an index.
 The working idea at this point is that all those DB File 
 Sequential Read
 waits on the table are possibly related to rows being migrated. Anyone
 tested for this?
 
 We will be building a test case on Friday. One with PctFree 10 and the
 columns being updated having nulls. Will gather the waits, 
 before and after
 sesstat's, analyze list chained rows, both before and after, 
 total blocks,
 rows per block, etc. Then rebuild the test having a PCTFREE 
 of 50 and do the
 same thing. Some wildcards -- with the blocks less tightly 
 packed, we will
 have to visit nearly double the number of blocks (maybe offset by
 migration), contention, and various other things to take into 
 account. But
 the main thing we are focusing in on is if we continue to see 
 the db file
 sequential read waits on the table. I guess the fact that we 
 are seeing
 waits is indicative of some I/O contention, but trying to 
 determine if, and
 how much, of that I/O is due to row migration, in which case a larger
 PCTFREE could provide some more immediate relief. No FK/PK 
 stuff, unique
 index is there, but it should resolve uniqueness using the 
 index, not the
 table. Maybe have left some things out. This came up a few 
 days ago, but
 just really started thinking about it and digging into it. And the end
 result is we don't want migrated rows, just looking to see if the row
 migration is the primary cause of the performance downturn.
 
 Regards,
 
 Larry G. Elkins
 [EMAIL PROTECTED]
 214.954.1781
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Larry Elkins
   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: John Kanagaraj
  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).

Deleting some//all the child records from the child tables and th e corresponding records in the parent table(s)

2002-12-27 Thread Krishnaswamy, Ranganath


The other day you sent me the query to find the direct and indirect 
relationship between tables.   In thesame way is it possible to delete 
all/few lower level records based on the column value of the parent table?  
If so, could you please send me the SQL queries for the same?   

Please note that I cannot enforce the 'ON DELETE
CASCADE' rule on the foreign key constraints.  Any help in this regard is
very much appreciated.

Thanks and Regards,

Ranganath




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Krishnaswamy, Ranganath
  INET: [EMAIL PROTECTED] (by way of Jared Still 
[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: Those Pesky Little Audit Files (ora_99999.aud)

2002-12-27 Thread Jonathan Lewis


Probably because changing it from it's default
value of FALSE introduces a potential security
hole - trace files may be dumped at any time,
and may contain information that is deemed to
be confidential.
.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

England__January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 27 December 2002 13:50


O Oracle Guru's
Please tell us, why _trace_files_public is *STILL* an underscore
parameter??

Raj


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  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: Those Pesky Little Audit Files (ora_99999.aud)


My guess would be that since it is a security risk, it's probably
not a good a idea to make it a supported parameter.

Jared

On Friday 27 December 2002 05:18, Jamadagni, Rajendra wrote:
 O Oracle Guru's
 Please tell us, why _trace_files_public is *STILL* an underscore
 parameter??

 Raj
 __

 Rajendra Jamadagni  MIS, ESPN Inc.

 Rajendra dot Jamadagni at ESPN dot com

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


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

 -Original Message-
 Sent: Friday, December 27, 2002 2:09 AM
 To: Multiple recipients of list ORACLE-L


 They were put there in 7.1 in order to comply with some security standard.
 And their purpose is exactly to prevent a dba from logging in without being
 monitored. It's in the 7.1 new features manual, as far as I remember.
 That's also the version where it was suddenly not possible for the poor
 deveopers to see their own tracefiles, except if they set
 _trace_files_public=true.

 Mogens


Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
Content-Transfer-Encoding: quoted-printable
Content-Description: 



Content-Type: text/plain; charset=iso-8859-1; name=ESPN_Disclaimer.txt
Content-Transfer-Encoding: 7bit
Content-Description: 

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

LMT- Migrated tablespaces

2002-12-27 Thread Browett, Darren

I have crossposted this question on the Oracle-Apps list, I would like
to
get the opinion of this list as it is more of database issue as opposed
to apps.

The question is about LMT and extent management with regards to Oracle
11i.

When upgrading to 11i, it creates migrated LMTS as opposed to
uniform/system ones, and therefore do not conform to the rules 
of correct LMTS.

My understanding is, even though my tablespaces are LMT, the tables
still act like they are dictionary managed with regards to extent
growth.

According to How to stop defrag, and start living . for tables
under 160M I should have an extent size of 160k.

With that in mind, should I 

1) Create LMT Tablespaces with an extent size of 160k ? ( This is
ignored
   by the import, tables will be one extent big)
2) Reset the next_extent on my apps tables to 160k ? 
3) Set pctincrease to 0 ? ( I think this is a given )

Thanks

Darren



--
Darren Browett P.EngThis
message was transmitted
Data Administrator  using
100% recycled electrons 
Information and Communication Technology
City of Coquitlam 
P:(604)927 - 3614 
E:[EMAIL PROTECTED] 

--- 



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Browett, Darren
  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: Versioning the Database !

2002-12-27 Thread Rama, Shreekantha (CAP, CARD)


I am in a peculiar situation where the development  design is happening in
parellel. 
It would table definitions, table data (Reference Data), View definitions,
the design itself ( LDM).

It would be a situation, where there are different schema's need to be
maintained at different stages of the project (Devlp, IT, QA, Staging).
Since these activities would be parellel versioning would help.

Hence this versioning. 

Regards
Shree


-Original Message-
Sent: Friday, December 27, 2002 10:34 AM
To: Multiple recipients of list ORACLE-L



Versioning the database ?
Take a backup of the database on a seperate tape each day !

What components of the database do you want to version ?  Table definitions
?
View definitions ? Packages/Procedures/Triggers ?
Code Objects should be versioned, but data objects
[Tables/Indexes/Sequences]
would generally not vary once the design is done, save for a few 
changes/additions/enhancements.
Hemant

At 06:28 AM 27-12-02 -0800, you wrote:
Hi,

 I want to version the Database for development, IT, QA and staging
environment.
 Can some one suggest different methods and best possible approach
to
maintain the database.

 Database is in design stage  development has partially started.
We
are using MKS for versioning.

Regards
Shree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rama, Shreekantha (CAP, CARD)
   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
My web site page 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: Rama, Shreekantha (CAP, CARD)
  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: Those Pesky Little Audit Files (ora_99999.aud)

Title: RE: Those Pesky Little Audit Files (ora_9.aud)





Or you might have to do the cleanup sooner if you have 9202 on AIX 5.1 and you have external tables and you run into that (yet unknown) pmon memory leak (where it supposedly corrupts first 80 bytes of memory). When the instance finally crashed, among 540 trace and trw files, one tracefile was 1.3GB (no it is not a type) in size.

Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!


-Original Message-
From: Orr, Steve [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 27, 2002 1:10 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Those Pesky Little Audit Files (ora_9.aud)



Yupp, I do the same thing. I figure if there's a problem documented somewhere in those files and I haven't responded to them in 30-60 days then its too old to worry about anyway. Sometimes OWS wants an alert log which goes back to the beginning of time but I just tell 'em to get real. I just up'd the listerner log renaming routine to run on a daily basis because it was too big too view. Now the average size of the listener.julianday file is 20-50MB. The process of cleaning up oracle log and trace files is like a pooper-scooper at the end of the rodeo parade. 

Steve Orr 
Bozeman, Montana 



*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.*1

RE: Versioning the Database !

2002-12-27 Thread Kevin Lange

Used to use that method in a former company with our DB2 database.   We had
one DB with schemas of DBPROD, DBTEST, DBSTST, and DBRTST.   At various
testing stages we would move the objects to a different schema The
application had a variable for who owned the structure.   That way we could
be developing (DBTEST), in the development test phase (DBRTST), into user
acceptance testing (DBSTST), and into production (DBPROD) with different
versions all at the same time. 

I could see it happening with Oracle as well..but 

  why not just use different instances to house the different stages of
development.  That way you can have everything under the same schema and not
have to worry about any synonym or schema switching.

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Friday, December 27, 2002 1:44 PM
To: Multiple recipients of list ORACLE-L



I am in a peculiar situation where the development  design is happening in
parellel. 
It would table definitions, table data (Reference Data), View definitions,
the design itself ( LDM).

It would be a situation, where there are different schema's need to be
maintained at different stages of the project (Devlp, IT, QA, Staging).
Since these activities would be parellel versioning would help.

Hence this versioning. 

Regards
Shree


-Original Message-
Sent: Friday, December 27, 2002 10:34 AM
To: Multiple recipients of list ORACLE-L



Versioning the database ?
Take a backup of the database on a seperate tape each day !

What components of the database do you want to version ?  Table definitions
?
View definitions ? Packages/Procedures/Triggers ?
Code Objects should be versioned, but data objects
[Tables/Indexes/Sequences]
would generally not vary once the design is done, save for a few 
changes/additions/enhancements.
Hemant

At 06:28 AM 27-12-02 -0800, you wrote:
Hi,

 I want to version the Database for development, IT, QA and staging
environment.
 Can some one suggest different methods and best possible approach
to
maintain the database.

 Database is in design stage  development has partially started.
We
are using MKS for versioning.

Regards
Shree
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rama, Shreekantha (CAP, CARD)
   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
My web site page 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: Rama, Shreekantha (CAP, CARD)
  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: Kevin Lange
  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).

Distributed Option

2002-12-27 Thread Michael Kline

I've got a client that needs distributed
option installed on several databases,
versions 7.3.4, 8.0.5, and 8.1.7...
 
Problem may be I'm not sure we'll have all
the CD's as vendors of applications did 
most of the installs and we think we'll
find that they took the CD's with them.
After all, if it's up and running and
vendor supported(or was), why would the
client need the CD?
 
Anyhow, is it just a free option that will
need to be selected with the Oracle installer,
or is it an add on that one is supposed to 
contact Oracle on? Or perhaps it just involves
running one of those all but undocumented
packages.
 
There are many ways to work around this if
we have to, but would like to know what all
is involved if we can get the distributed
option installed on all of their databases.
 
It's on my Personal Oracle, but seems to 
be part of a general set of things that
get installed.
 
 
 
Kline's Consulting
Michael Alan Kline, Sr., Owner 
13308 Thornridge Court; Midlothian, VA 23112, USA. 
Work: 804-744-1545 Cell: 804-314-6262 
Pager: 877-705-1155 ICQ: 1009605, 975313 
[EMAIL PROTECTED]; [EMAIL PROTECTED]; 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Kline
  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: Row Migration

2002-12-27 Thread Jonathan Lewis


When you do your testing, don't forget to keep an
eye on the change in dependent logical I/O and latching.

Fetching a migrated row will require an extra buffer
visit to find the row data. This MAY turn into an
extra disk read but at the least it IS another
buffer visit, which means another hit on the
cache-buffers-chains latch, and may mean further
work done getting another buffered block to the
correct read-consistent state.

I think you'll have to model your test very carefully -
it wouldn't be too hard to produce two different models
with totally contradictory results - one based on the
migration going to a relatively nearby block, the other
based on the update and migration taking place in
a way that ensures maximum scatter of the migrated
row piece.

The former may hide I/O problems, the latter may exaggerate
the I/O problems and hide the latch issues; and in either
case you may fail to emulate the read-consistency issue
properly.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

England__January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 27 December 2002 12:25


Well, yes, I would agree with that ;-)

What we are trying to determine here in this particular case is how
much or
what percentage of the slowdown in the process is due to the
migration of
rows. We aren't ready (until we do some testing) to make a blanket
statement
that row migration *alone* is the cause of the significant slowdown.
In
other words, I'm not willing to make a statement to the powers that
be that
simply increasing the pctfree is going to make things normal again
until we
have a chance to do some more detailed monitoring and testing.

Regards,

Larry G. Elkins


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  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:RE: Deadlock

2002-12-27 Thread Jonathan Lewis


I think I'll resist the temptation to review
the entire trace file.  However, since this
is a v9 deadlock dump, I think you should
find that you have a complete processstate
dump after the initial deadlock graph.

Somewhere near the end of the dump you
should find the CURSOR section, which
should list all the current cursors for the
session.  Read through these, they may
give you a clue about the SQL that has
pushed the TM lock from a 3 to a 5 on
the problem table.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

England__January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 27 December 2002 00:15


Yes it is 9013. This is not an array based update. As per the trace
file
same statement is being executed by both sessions. I can directly
send you
the trace file if there is a need.

There are triggers on the tables, I'll look into parent table
activity. But
there are indexes on all foreign keys except one which corresponds to
a
static master table containing PO TYPES. That table is not being
updated.

How can I dig deeper into this issue.

Thanks
Shaleen


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  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: Distributed Option

2002-12-27 Thread DENNIS WILLIAMS

Michael
   By distributed, I assume you mean replication?
   From what I can tell, basic replication is included with Standard Edition
and advanced replication is included with Enterprise Edition. 
   I think you run a script, something like catrep.sql in rdbms/admin, so
you should be able to get the pieces installed.
   Replicating between different Oracle versions could be challenging, but
doable. Others on the list can probably offer specific advice on pitfalls.
   We haven't implemented replication here, just studied. From what I can
tell, planning for replication is everything. I have only been able to find
2 books on it. The prize is Marie Buretta's Database Replication.
http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613/sr%3D11-1
/ref%3Dsr%5F11%5F1/102-1511927-6720101
It really tells you everything you need as an organization to prepare for
replication. Replication takes a lot of administration so it should be a
gold mine since you are consulting.
   The other book is Oracle Distributed Systems by Charles Dye
http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613/sr%3D11-1
/ref%3Dsr%5F11%5F1/102-1511927-6720101


Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Friday, December 27, 2002 2:39 PM
To: Multiple recipients of list ORACLE-L


I've got a client that needs distributed
option installed on several databases,
versions 7.3.4, 8.0.5, and 8.1.7...
 
Problem may be I'm not sure we'll have all
the CD's as vendors of applications did 
most of the installs and we think we'll
find that they took the CD's with them.
After all, if it's up and running and
vendor supported(or was), why would the
client need the CD?
 
Anyhow, is it just a free option that will
need to be selected with the Oracle installer,
or is it an add on that one is supposed to 
contact Oracle on? Or perhaps it just involves
running one of those all but undocumented
packages.
 
There are many ways to work around this if
we have to, but would like to know what all
is involved if we can get the distributed
option installed on all of their databases.
 
It's on my Personal Oracle, but seems to 
be part of a general set of things that
get installed.
 
 
 
Kline's Consulting
Michael Alan Kline, Sr., Owner 
13308 Thornridge Court; Midlothian, VA 23112, USA. 
Work: 804-744-1545 Cell: 804-314-6262 
Pager: 877-705-1155 ICQ: 1009605, 975313 
[EMAIL PROTECTED]; [EMAIL PROTECTED]; 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Kline
  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: 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).

OT: Forms,Reports performance problem

2002-12-27 Thread Rick_Cale

Oracle 8.1.6 Win Nt
Has anyone experience/heard of performace problems after migrating from
forms 5 to forms 6.0.8.15 and from reports 2.5 to 3.0?

Thanks
Rick

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

Re: Those Pesky Little Audit Files (ora_99999.aud)

I've got hard-copy of the 7 docs if you can give me a clue where to
start searching for it...


--- Mogens_Nørgaard [EMAIL PROTECTED] wrote:
 Yeah, it's a nuisance in most installations, but the idea is to be 
 compliant with some abbreviation_that_I'm_sure_Tim_can_remember 
 security standard. Give me a 7.1 doc site (if it exists) and I'll
 find 
 the details. I failed to find 7.1 doc on Google searches. Probably
 too 
 much beer.
 
 Mogens
 
 Jamadagni, Rajendra wrote:
 
  O Oracle Guru's
  Please tell us, why _trace_files_public is *STILL* an underscore 
  parameter??
   
  Raj
  __
 
  Rajendra Jamadagni  MIS, ESPN Inc.
 
  Rajendra dot Jamadagni at ESPN dot com
 
  Any opinion expressed here is personal and doesn't reflect that of 
  ESPN Inc.
 
  QOTD: Any clod can have facts, but having an opinion is an art!
 
  -Original Message-
  From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]
  Sent: Friday, December 27, 2002 2:09 AM
  To: Multiple recipients of list ORACLE-L
  Subject: Re: Those Pesky Little Audit Files (ora_9.aud)
 
  They were put there in 7.1 in order to comply with some
 security
  standard. And their purpose is exactly to prevent a dba from
  logging in without being monitored. It's in the 7.1 new
 features
  manual, as far as I remember.  That's also the version where it
  was suddenly not possible for the poor deveopers to see their
 own
  tracefiles, except if they set _trace_files_public=true.
 
  Mogens
 


 

*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.*1
   
 
 
 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: Rebuilding Indexes...

Jared,

it was built with enable validate. Doesn't seem to matter if it's
validate or not.

Rachel
--- Jared Still [EMAIL PROTECTED] wrote:
 
 Rick,
 
 You're not considering a PK built with 'enable novalidate'.
 
 Jared
 
 
 On Friday 27 December 2002 05:38, [EMAIL PROTECTED] wrote:
  If you know you have 3 duplicate records in the table then the PK
 must have
  already been disabled so you have to rebuild anyway.  I do not see
  where you had to disable in order to use the exception table. It
 was
  already disabled therefore it probably not an app problem but a
 disable
  constraint
  problem unless direct load bypasses constraint checking which I am
 not
  sure.
 
  Rick
 
 
 
 
  Rachel
  Carmichael   To: Multiple
 recipients of
  list ORACLE-L [EMAIL PROTECTED] wisernet100@y   cc:
  ahoo.comSubject: Re:
 Rebuilding
  Indexes... Sent by:
  [EMAIL PROTECTED]
  om
 
 
  12/27/2002
  07:43 AM
  Please respond
  to ORACLE-L
 
 
 
 
 
 
  Here's a reason:
 
  have you ever tried to find the three duplicate rows in a 12
 million
  row table without using the primary key constraint? I've had to
 disable
  or drop the constraint in order to use the exceptions table. Once I
 do
  that, even if I've built a separate index that enforces the primary
 key
  constraint, Oracle drops the index. So I HAVE to rebuild it. If I
 allow
  the index to be rebuilt when I re-enable the primary key
 constraint, it
  builds it in the default tablespace of the table owner, not where I
  want it.
 
  if anyone has a better way to fix this problem, I'm more than happy
 to
  hear it! It's a data warehouse and the third party app has a bug we
  can't find and on occasion sqlloads (via direct path) duplicate
 rows
 
  Rachel
 
  --- Jared Still [EMAIL PROTECTED] wrote:
   Though I have published a script for determining indexes that
   need to be rebuilt, and then rebuilding them,  I have to say that
   this is almost never necessary.
  
   Why are you rebuilding indexes?  About the only reason for ever
   doing so is that the BLEVEL = 5.
  
   goto asktom.oracle.com, and do a search on 'index rebuild'.
  
   Currently, the third article may be of interest.
  
   Jared
  
   On Thursday 26 December 2002 12:24, Richard Huntley wrote:
Anyone have any useful scripts for doing this?
   
TIA,
Rich
  
   
   Content-Type: text/html; charset=iso-8859-1; name=Attachment:
 1
   Content-Transfer-Encoding: 7bit
   Content-Description:
   
   --
   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).
 
  __
  Do you Yahoo!?
  Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
  http://mailplus.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: 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).

Re: Rebuilding Indexes...

I know I have 3 duplicates because that's how many I deleted when I got
rid of them.

If you use direct=true on sqlloader, the primary key constraint is NOT
disabled even if the index partition is made unusable.

and we know it's an app problem. That's a given. The app on occasion
re-runs part of a load. What we don't know is why. As yet.


--- [EMAIL PROTECTED] wrote:
 
 If you know you have 3 duplicate records in the table then the PK
 must have
 already been disabled so you have to rebuild anyway.  I do not see
 where you had to disable in order to use the exception table. It was
 already disabled therefore it probably not an app problem but a
 disable
 constraint
 problem unless direct load bypasses constraint checking which I am
 not
 sure.
 
 Rick
 
 
 
  
   
 Rachel   
   
 Carmichael   To: Multiple recipients
 of list ORACLE-L [EMAIL PROTECTED]
 wisernet100@y   cc: 
   
 ahoo.comSubject: Re: Rebuilding
 Indexes... 
 Sent by: 
   
 [EMAIL PROTECTED]   
   
 om   
   
  
   
  
   
 12/27/2002   
   
 07:43 AM 
   
 Please respond   
   
 to ORACLE-L  
   
  
   
  
   
 
 
 
 
 Here's a reason:
 
 have you ever tried to find the three duplicate rows in a 12 million
 row table without using the primary key constraint? I've had to
 disable
 or drop the constraint in order to use the exceptions table. Once I
 do
 that, even if I've built a separate index that enforces the primary
 key
 constraint, Oracle drops the index. So I HAVE to rebuild it. If I
 allow
 the index to be rebuilt when I re-enable the primary key constraint,
 it
 builds it in the default tablespace of the table owner, not where I
 want it.
 
 if anyone has a better way to fix this problem, I'm more than happy
 to
 hear it! It's a data warehouse and the third party app has a bug we
 can't find and on occasion sqlloads (via direct path) duplicate rows
 
 Rachel
 
 --- Jared Still [EMAIL PROTECTED] wrote:
 
  Though I have published a script for determining indexes that
  need to be rebuilt, and then rebuilding them,  I have to say that
  this is almost never necessary.
 
  Why are you rebuilding indexes?  About the only reason for ever
  doing so is that the BLEVEL = 5.
 
  goto asktom.oracle.com, and do a search on 'index rebuild'.
 
  Currently, the third article may be of interest.
 
  Jared
 
  On Thursday 26 December 2002 12:24, Richard Huntley wrote:
   Anyone have any useful scripts for doing this?
  
   TIA,
   Rich
 
  
  Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1
  Content-Transfer-Encoding: 7bit
  Content-Description:
  
  --
  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).
 
 
 
 __
 Do you Yahoo!?
 Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
 http://mailplus.yahoo.com

Re: Rebuilding Indexes...

it'll have to wait until Monday, I'm not at work until then. I'll try
it with a non-unique then

Hey, if it works, it saves me tons of time, I learn something new and I
had fun developing the single SQL statement to rebuild the constraint
and index. Win-win 


Rachel

--- Denny Koovakattu [EMAIL PROTECTED] wrote:
 
 
   I don't have access to 9.2.0.1 right now. But can you try creating
 a non-
 unique index instead of the unique index. If you create a unique
 index, it gets 
 dropped. That's the behavior on 8.1.x also. But if it's a non-unique
 index, it 
 shouldn't get dropped.
 
 Regards,
 Denny
 
 Quoting Rachel Carmichael [EMAIL PROTECTED]:
 
  9.2.0.1 Solaris, and yes, it does drop it
  
  I created a unique index in the primary key columns
  I created the primary key constraint without specifying an index
  I checked that the index existed, it did
  I dropped the primary key constraint
  I checked that the index existed, it didn't
  
  try it I tried various combinations before posting this note
  
  
  --- Denny Koovakattu [EMAIL PROTECTED] wrote:
   
   
 If you build a separate index to enforce the primary key,
 Oracle
   shouldn't 
   drop it when you disable or drop the primary key.
   
   Regards,
   Denny
   
   Quoting Rachel Carmichael [EMAIL PROTECTED]:
   
Here's a reason:

have you ever tried to find the three duplicate rows in a 12
   million
row table without using the primary key constraint? I've had to
disable
or drop the constraint in order to use the exceptions table.
 Once
  I
   do
that, even if I've built a separate index that enforces the
  primary
key
constraint, Oracle drops the index. So I HAVE to rebuild it. If
 I
allow
the index to be rebuilt when I re-enable the primary key
   constraint,
it
builds it in the default tablespace of the table owner, not
 where
  I
want it.

if anyone has a better way to fix this problem, I'm more than
  happy
   to
hear it! It's a data warehouse and the third party app has a
 bug
  we
can't find and on occasion sqlloads (via direct path) duplicate
   rows

Rachel

--- Jared Still [EMAIL PROTECTED] wrote:
 
 Though I have published a script for determining indexes that
 need to be rebuilt, and then rebuilding them,  I have to say
  that
 this is almost never necessary.
 
 Why are you rebuilding indexes?  About the only reason for
 ever
 doing so is that the BLEVEL = 5.
 
 goto asktom.oracle.com, and do a search on 'index rebuild'.
 
 Currently, the third article may be of interest.
 
 Jared
 
 On Thursday 26 December 2002 12:24, Richard Huntley wrote:
  Anyone have any useful scripts for doing this?
 
  TIA,
  Rich
 
 
 Content-Type: text/html; charset=iso-8859-1;
 name=Attachment:
   1
 Content-Transfer-Encoding: 7bit
 Content-Description: 
 
 -- 
 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).
 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: Denny Koovakattu
 INET: [EMAIL PROTECTED]
   
   Fat City Network Services-- 858-538-5051
 http://www.fatcity.com
   San Diego, California-- Mailing list and web hosting
  services

RE: Row Migration

2002-12-27 Thread Nick Wagner

Title: RE: Row Migration





also, what version of Oracle and how many columns on the table? 


-Original Message-
From: Nick Wagner [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 27, 2002 9:39 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Row Migration



We've done a few tests here with chained vs. unchained rows, and the impact is anywhere from 50-200% overhead. So if it took about 10 seconds to do a query it will now take 15 to 30 seconds. It seamed to depend most on which rows we were returning... not hitting the chained rows as much helped speed it up. 

For each row operation, Oracle must read the block that contains the data, and the last piece of information in each block contains a null/not null rowid pointer to the next row piece. In a spanned row (one inserted that is too big for a single DB_BLOCK) the pointer (usually) points to the next physical block in the DB, and it goes pretty fast. In a chained row (one where someone has done an update, and the new information put into the row does not fit into the rest of the block -- which sounds like your case) the pointer (usually) points to a block at the end of the physical table in the DB file that contains the rest of the information. And it goes very slowly. Chaining can really grow to be progressively worse, if you continually update a column who spans the two blocks, oracle will not update the first block or the last block and instead create another new block at the end of the table for those new characters. So a read of that column now takes in 3 blocks, potentially spanned over the entire datafile. 

In Oracle 9i we've seen some really strange behavior too... when doing an import, or direct load Oracle will actually chain a row inside of a block, and none of the analyze for chained row commands will pick it up. It still causes the slow down, but you cannot fix it. 

It's actually been a while since I've really been able to look at this stuff, so if anyone has any clarifications or things they want to add, please do so. 

Nick



-Original Message-
From: Larry Elkins [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 27, 2002 3:19 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Row Migration



Well, yes, I would agree with that ;-) 


What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) to make a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing.

Regards,


Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar N
Sent: Friday, December 27, 2002 2:09 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Row Migration



yes, row migration will degrade the performance..

Re: Rebuilding Indexes...

As Denny also suggested. I'm gonna try that on Monday, on my sandbox
database. If this does work in 9i as well (and it should, I hope), I
can just rebuild the unusable partition and not the entire index. The
index build will only have to happen once. 


--- Jack Silvey [EMAIL PROTECTED] wrote:
 Hey Rachel,
 
 Consider using a non-unique index for your primary key constraint. If
 
 you prebuild it and then add the constraint, Oracle will not drop the
 
 index when you drop the PK constraint, and you can control the index 
 build that a way (and build it in parallel to boot).
 
 hth,
 
 Jack
 
 
 
 
  Here's a reason:
  
  have you ever tried to find the three duplicate rows in a 12
 million
  row table without using the primary key constraint? I've had to 
 disable
  or drop the constraint in order to use the exceptions table. Once I
 do
  that, even if I've built a separate index that enforces the primary
 
 key
  constraint, Oracle drops the index. So I HAVE to rebuild it. If I 
 allow
  the index to be rebuilt when I re-enable the primary key
 constraint, 
 it
  builds it in the default tablespace of the table owner, not where I
  want it.
  
  if anyone has a better way to fix this problem, I'm more than happy
 to
  hear it! It's a data warehouse and the third party app has a bug we
  can't find and on occasion sqlloads (via direct path) duplicate
 rows
  
  Rachel
  
  --- Jared Still [EMAIL PROTECTED] wrote:
   
   Though I have published a script for determining indexes that
   need to be rebuilt, and then rebuilding them,  I have to say that
   this is almost never necessary.
   
   Why are you rebuilding indexes?  About the only reason for ever
   doing so is that the BLEVEL = 5.
   
   goto asktom.oracle.com, and do a search on 'index rebuild'.
   
   Currently, the third article may be of interest.
   
   Jared
   
   On Thursday 26 December 2002 12:24, Richard Huntley wrote:
Anyone have any useful scripts for doing this?
   
TIA,
Rich
   
   
   Content-Type: text/html; charset=iso-8859-1; name=Attachment:
 1
   Content-Transfer-Encoding: 7bit
   Content-Description: 
   
   -- 
   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).
   
  
  
  __
  Do you Yahoo!?
  Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
  http://mailplus.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).
  
  
  
 
 Thanks,
 
 Jack Silvey
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Jack Silvey
   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).
 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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

Re: Rebuilding Indexes...

fair enough. I retract the example :)

--- Jared Still [EMAIL PROTECTED] wrote:
 
 Yes, but that's a special case.  You are not rebuilding
 the index as part of some regular index maintenance.
 
 Jared
 
 On Friday 27 December 2002 04:43, Rachel Carmichael wrote:
  Here's a reason:
 
  have you ever tried to find the three duplicate rows in a 12
 million
  row table without using the primary key constraint? I've had to
 disable
  or drop the constraint in order to use the exceptions table. Once I
 do
  that, even if I've built a separate index that enforces the primary
 key
  constraint, Oracle drops the index. So I HAVE to rebuild it. If I
 allow
  the index to be rebuilt when I re-enable the primary key
 constraint, it
  builds it in the default tablespace of the table owner, not where I
  want it.
 
  if anyone has a better way to fix this problem, I'm more than happy
 to
  hear it! It's a data warehouse and the third party app has a bug we
  can't find and on occasion sqlloads (via direct path) duplicate
 rows
 
  Rachel
 
  --- Jared Still [EMAIL PROTECTED] wrote:
   Though I have published a script for determining indexes that
   need to be rebuilt, and then rebuilding them,  I have to say that
   this is almost never necessary.
  
   Why are you rebuilding indexes?  About the only reason for ever
   doing so is that the BLEVEL = 5.
  
   goto asktom.oracle.com, and do a search on 'index rebuild'.
  
   Currently, the third article may be of interest.
  
   Jared
  
   On Thursday 26 December 2002 12:24, Richard Huntley wrote:
Anyone have any useful scripts for doing this?
   
TIA,
Rich
  
   
   Content-Type: text/html; charset=iso-8859-1; name=Attachment:
 1
   Content-Transfer-Encoding: 7bit
   Content-Description:
   
   --
   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).
 
  __
  Do you Yahoo!?
  Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
  http://mailplus.yahoo.com
 -- 
 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).
 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: Those Pesky Little Audit Files (ora_99999.aud)

2002-12-27 Thread Orr, Steve

Title: RE: Those Pesky Little Audit Files (ora_9.aud)

that
calls for a super-duper-pooper-scooper. :-)

-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002
1:09 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: Those Pesky Little Audit Files (ora_9.aud)
Or you might have to do the cleanup sooner if you have 9202 on
AIX 5.1 and you have external tables and you run into that (yet unknown) pmon
memory leak (where it supposedly corrupts first 80 bytes of memory). When the
instance finally crashed, among 540 trace and trw files, one tracefile was
1.3GB (no it is not a type) in size.
Raj __ Rajendra
Jamadagni
MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot
com Any opinion expressed here is personal and doesn't
reflect that of ESPN Inc. QOTD: Any clod can have
facts, but having an opinion is an art!
-Original Message- From: Orr,
Steve [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 27, 2002 1:10 PM To: Multiple recipients of list ORACLE-L Subject: RE: Those Pesky Little Audit Files (ora_9.aud)

Yupp, I do the same thing. I figure if there's a problem
documented somewhere in those files and I haven't responded to them in 30-60
days then its too old to worry about anyway. Sometimes OWS wants an alert log
which goes back to the beginning of time but I just tell 'em to get real. I
just up'd the listerner log renaming routine to run on a daily basis because
it was too big too view. Now the average size of the listener.julianday file
is 20-50MB. The process of cleaning up oracle log and trace files is like a
"pooper-scooper" at the end of the rodeo parade.
Steve Orr Bozeman, Montana

RE: Distributed Option

2002-12-27 Thread Michael Kline

I believe this is different than replication, though many of
the ideas and transactions would be the same.

In this particular case, they are going to allow Name and address
changes over the web. Those changes will cause updates to two some
what different customer files on two different applications on two
different other databases.

What happens now is when they tried to do the update over the
database link, and commit, they get distributed option not
installed.

This is further confused that one database has replication but
still gets an error message about distributed option not
installed. And the v$option shows distributed option as
false or what ever.

Connected to:
Oracle7 Server Release 7.3.4.0.1 - Production
With the parallel query option
PL/SQL Release 2.3.4.0.0 - Production

This is the sign on for one of the databases missing the option.

My personal oracle shows:

Connected to:
Personal Oracle7 Release 7.2.2.3.1 - Production
With the distributed and replication options
PL/SQL Release 2.2.2.3.1 - Production

Maks.

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of DENNIS
 WILLIAMS
 Sent: Friday, December 27, 2002 4:19 PM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: Distributed Option


 Michael
By distributed, I assume you mean replication?
From what I can tell, basic replication is included with
 Standard Edition
 and advanced replication is included with Enterprise Edition.
I think you run a script, something like catrep.sql in
 rdbms/admin, so
 you should be able to get the pieces installed.
Replicating between different Oracle versions could be challenging, but
 doable. Others on the list can probably offer specific advice on pitfalls.
We haven't implemented replication here, just studied. From what I can
 tell, planning for replication is everything. I have only been
 able to find
 2 books on it. The prize is Marie Buretta's Database Replication.
 http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613
 /sr%3D11-1
 /ref%3Dsr%5F11%5F1/102-1511927-6720101
 It really tells you everything you need as an organization to prepare for
 replication. Replication takes a lot of administration so it should be a
 gold mine since you are consulting.
The other book is Oracle Distributed Systems by Charles Dye
 http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613
 /sr%3D11-1
 /ref%3Dsr%5F11%5F1/102-1511927-6720101


 Dennis Williams
 DBA, 40%OCP
 Lifetouch, Inc.
 [EMAIL PROTECTED]

 -Original Message-
 Sent: Friday, December 27, 2002 2:39 PM
 To: Multiple recipients of list ORACLE-L


 I've got a client that needs distributed
 option installed on several databases,
 versions 7.3.4, 8.0.5, and 8.1.7...

 Problem may be I'm not sure we'll have all
 the CD's as vendors of applications did
 most of the installs and we think we'll
 find that they took the CD's with them.
 After all, if it's up and running and
 vendor supported(or was), why would the
 client need the CD?

 Anyhow, is it just a free option that will
 need to be selected with the Oracle installer,
 or is it an add on that one is supposed to
 contact Oracle on? Or perhaps it just involves
 running one of those all but undocumented
 packages.

 There are many ways to work around this if
 we have to, but would like to know what all
 is involved if we can get the distributed
 option installed on all of their databases.

 It's on my Personal Oracle, but seems to
 be part of a general set of things that
 get installed.



 Kline's Consulting
 Michael Alan Kline, Sr., Owner
 13308 Thornridge Court; Midlothian, VA 23112, USA.
 Work: 804-744-1545 Cell: 804-314-6262
 Pager: 877-705-1155 ICQ: 1009605, 975313
 [EMAIL PROTECTED]; [EMAIL PROTECTED];
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Michael Kline
   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: 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

RE: Row Migration




Thanks 
for those comments, but that's a little down the road for what I'm looking at 
right now -- trying to determine the overhead associated with updates and the 
update causing a row to migrate. We don't intend to let the chaining actually 
make it into the DM. But it's good to see someone put some numbers on it, and 
something I would be interested in repeating at some time in the future should 
migration/chaining occur in the target table.

Regards,Larry G. 
Elkins[EMAIL PROTECTED]214.954.1781 

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]On Behalf Of Nick WagnerSent: 
  Friday, December 27, 2002 11:39 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Row Migration
  We've done a few tests here with chained vs. unchained rows, and the 
  impact is anywhere from 50-200% overhead. So if it took about 10 
  seconds to do a query it will now take 15 to 30 seconds. It seamed 
  to depend most on which rows we were returning... not hitting the chained rows 
  as much helped speed it up. 
  
  For 
  each row operation, Oracle must read the block that contains the data, and the 
  last piece of information in each block contains a null/not null rowid pointer 
  to the next row piece. In a spanned row (one inserted that is too big 
  for a single DB_BLOCK) the pointer (usually)points to the next physical 
  block in the DB, and it goes pretty fast. In a chained row (one where 
  someone has done an update, and the new information put into the row does not 
  fit into the rest of the block -- which sounds like your case) the pointer 
  (usually) points to a block at the end of the physical table in the DB file 
  that contains the rest of the information. And it goes very 
  slowly. Chaining can really grow to be progressively worse, if you 
  continually update a column who spans the two blocks, oracle will not update 
  the first block or the last block and instead create another new block at the 
  end of the table for those new characters. So a read of that column now 
  takes in 3 blocks, potentially spanned over the entire datafile. 
  
  
  In 
  Oracle 9i we've seen some really strange behavior too... when doing an import, 
  or direct load Oracle will actually chain a row inside of a block, and none of 
  the analyze for chained row commands will pick it up.It still 
  causes the slow down, but you cannot fix it. 
  
  It's 
  actually been a while since I've really been able to look at this stuff, so if 
  anyone has any clarifications or things they want to add, please do so. 
  
  
  Nick
  
  
  -Original Message-From: Larry Elkins 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 3:19 
  AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Row Migration
  Well, yes, Iwould agree with that ;-) 
  
  
  What 
  we are trying to determine here in this particular case is how much or what 
  percentage of the slowdown in the process is due to the migration of rows. We 
  aren't ready (until we do some testing) tomake a blanket statement that 
  row migration *alone* is the cause of the significant slowdown. In other 
  words, I'm not willing to make a statement to the powers that be that simply 
  increasing the pctfree is going to make things normal again until we have a 
  chance to do some more detailed monitoring and testing.
  
  Regards,Larry G. 
  Elkins[EMAIL PROTECTED]214.954.1781 
  
-Original Message-From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar NSent: 
Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list 
ORACLE-LSubject: Re: Row Migration
yes, row migration 
will degrade the performance..

RE: Row Migration

So I'm doomed? ;-)

Ok, so how am I going to know which block it went to, the first step towards
seeing if it was relatively nearby or maximum scatter? I'm guessing I would
have to dump a block and look at the placeholder or stub in the original
location and see where it points (I'm assuming it has to)? Just conjecture
and the first thing I would think of since I can't think of any DD view or
X$ that would tell me where a row migrated from/to.

And I'm not so much concerned about the extra LIO's and latching at this
point since I'm focused on the impact of a row migrating during an update.
And don't think we will allow migrated rows in the table (though one might
make a case for eating a few migrated rows for the sake of a significantly
reduced number of blocks). But over time, this sort of update *will*
eventually happen to all the rows anyway, so we would be looking at the
higher number of blocks somewhere down the road. But it's all irrelevant now
anyway since both the staging table and it's real counterpart in the DM
were both re-orged with a pctfree of 40 (found that out this morning). I'll
still need to keep an eye on migrating rows, but I'm not going to allow a
handful of them make us go overboard on pctfree and wasting a lot of
space.

Not that I'm asking you to do our work, but curious what are the things and
considerations *you* would consider in building such a test case?

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan
 Lewis
 Sent: Friday, December 27, 2002 2:59 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: Row Migration



 When you do your testing, don't forget to keep an
 eye on the change in dependent logical I/O and latching.

 Fetching a migrated row will require an extra buffer
 visit to find the row data. This MAY turn into an
 extra disk read but at the least it IS another
 buffer visit, which means another hit on the
 cache-buffers-chains latch, and may mean further
 work done getting another buffered block to the
 correct read-consistent state.

 I think you'll have to model your test very carefully -
 it wouldn't be too hard to produce two different models
 with totally contradictory results - one based on the
 migration going to a relatively nearby block, the other
 based on the update and migration taking place in
 a way that ensures maximum scatter of the migrated
 row piece.

 The former may hide I/O problems, the latter may exaggerate
 the I/O problems and hide the latch issues; and in either
 case you may fail to emulate the read-consistency issue
 properly.


 Regards

 Jonathan Lewis
 http://www.jlcomp.demon.co.uk

 Coming soon a new one-day tutorial:
 Cost Based Optimisation
 (see http://www.jlcomp.demon.co.uk/tutorial.html )

 Next Seminar dates:
 (see http://www.jlcomp.demon.co.uk/seminar.html )


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  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: Row Migration

John, the $10 is on the way ;-)

Right now I'm looking at the impact of rows migrating due to updates
expanding the rows. So I was considering fetch row continued as opposed to
analyze .. list chained rows (my first thought) before and after the update.
To know how many rows migrated due to the updates, I could do a parallel fts
prior to the update and record this number (or insert all the stats into a
holding table). And then after the update do it again. The delta should give
me the number of rows that migrated, and would probably be much faster than
the analyze list chained rows (or a compute and looking at the chain_cnt)
since I could use parallelism. And then was definitely looking at using this
on both the staging version of the table and the production copy from a
query perspective.

But my entire test fell apart ;-) The table, both the staging and the real
in the DM, without my knowing it was going to occur, was rebuilt with a
pctfree of 40 overnight on Thursday. So I don't have a baseline to do a
before and after comparison to gauge the impact of the rows migrating during
the updates. It doesn't look like the process ran again after that. The
person who wrote it is on vacation, and the person watching it is off on
Fridays. And they are migrating that instance and domain to a new domain on
a new machine this weekend, so I don't really see anything happening with
this, at least not this weekend.

But I did do a quick and dirty test. Slammed 1,000,000 rows into a two
column table with pctfree of 0, with the second column null. Then updated
the second column and got a timing on it (all the rows migrated). Then
dropped, recreated, and repeated with a very high value (95) for pctfree.
The update finished 4 times faster. Didn't do any detailed analysis or stats
gathering -- just thought I would put together a quick and dirty. I would
still like to put together a test that more realistically mimics the real
case.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John
 Kanagaraj
 Sent: Friday, December 27, 2002 12:44 PM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: Row Migration


 Larry,

 Don't want to preach to the Guru, but have you checked the values
 for 'table
 fetch continued row'?

 StatisticTotal   per Second
  per Trans
 -  
 
 table fetch by rowid   577,820,727 40,129.2
   61,248.8
 table fetch continued row  137,202  9.5
   14.5

 This when coming out of V$SESSTAT could give a good indication of
 number of
 fetches by migrated as well as chained rows for that session. You
 could also
 look at V$SESSION.MAX_WAIT for 'db file sequential read' events...

 Let us know what you find!
 John Kanagaraj
 Oracle Applications DBA
 DBSoft Inc
 (W): 408-970-7002

 What would you see if you were allowed to look back at your life
 at the end
 of your journey in this earth?

 ** The opinions and statements above are entirely my own and not
 those of my
 employer or clients **

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  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: Automatic backup on Oracle 9i

2002-12-27 Thread Sony kristanto

Yes, that's right Jared, by doing this we can make schedule when we want to
backup our data onto hard disk or tape periodicaly (weekly or daily even
hour), thanks for your response and wishing you can help me to solve it.

 -Original Message-
 From: Jared Still [SMTP:[EMAIL PROTECTED]]
 Sent: Friday, December 27, 2002 10:06 AM
 To:   [EMAIL PROTECTED]; Sony kristanto
 Subject:  Re: Automatic backup on Oracle 9i

 Sony,

 What is an 'automatic' backup?

 Is this something supplied by that 'Oracle Enterprise Manager' thingy?

 Jared

 On Thursday 26 December 2002 17:23, Sony kristanto wrote:
  Hi Listers,
  I'm new on Oracle Database 9i after I migrated from Oracle 8i.
  I try to use backup facility from Oracle 9i and I already follow the
  instructions how to activate the automatic backup but when I see the
 status
  on history I get an error comment 'Failed'. I've try again and again but
  the results are the same. Could someone out there tell me why it can't
  runs. For your note I use 'SYS' as my user. I will really appreciate
 your
  help.

  Rgrds,

  Sony
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sony kristanto
  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).

8.1.7 with what version of Portal??

2002-12-27 Thread Oracle Developer

we have 8i (8.1.7.1) running at our shop and one of our developers wants to 
use WebDB (what I understand is now Portal).  in checking OTN and other 
places, I can't figure out what version of Portal (or WebDB) I should be 
installing, nor where I can get it.

can anyone tell me what version I should be trying to install, where I can 
get it and where any resources on installation and configuration might be 
located?

it is my understanding that Portal is a collection of packages that work 
with our existing database and doesn't need anything extra.  is that also 
truy?  aside from the raw 8i database, will I need to install anything else 
(yes, we have apache running ok)?

Deb
Carbide Systems Group




_
Protect your PC - get McAfee.com VirusScan Online 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Oracle Developer
 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: Automatic backup on Oracle 9i


Hmm

A  lot of folks on this liststudiously avoid OEM.   I know I do, 
and I'm not going to be much help on this.

Have you tried MetaLink?

Jared


On Friday 27 December 2002 17:11, Sony kristanto wrote:
 Yes, that's right Jared, by doing this we can make schedule when we want to
 backup our data onto hard disk or tape periodicaly (weekly or daily even
 hour), thanks for your response and wishing you can help me to solve it.

  -Original Message-
  From:   Jared Still [SMTP:[EMAIL PROTECTED]]
  Sent:   Friday, December 27, 2002 10:06 AM
  To: [EMAIL PROTECTED]; Sony kristanto
  Subject:Re: Automatic backup on Oracle 9i
 
 
  Sony,
 
  What is an 'automatic' backup?
 
  Is this something supplied by that 'Oracle Enterprise Manager' thingy?
 
  Jared
 
  On Thursday 26 December 2002 17:23, Sony kristanto wrote:
   Hi Listers,
   I'm new on Oracle Database 9i after I migrated from Oracle 8i.
   I try to use backup facility from Oracle 9i and I already follow the
   instructions how to activate the automatic backup but when I see the
 
  status
 
   on history I get an error comment 'Failed'. I've try again and again
   but the results are the same. Could someone out there tell me why it
   can't runs. For your note I use 'SYS' as my user. I will really
   appreciate
 
  your
 
   help.
  
   Rgrds,
  
   Sony
-- 
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).

Re: LMT- Migrated tablespaces


IIRC, a 160m table would be in an LMT with 4m extents.

The 3 extent sizes recommended in the paper are 128k,
4m and 128m. 

 1) Create LMT Tablespaces with an extent size of 160k ? ( This is
 ignored   by the import, tables will be one extent big)

Not so.  If you create an LMT of the correct name for imp to import
a table into, it will be created with the uniform size extents you 
specified at tablespace creation.

By 'correct name', I mean either a tablespace of the same name
as the one the table was exported from, or the owners default
tablespace is an LMT, and there is not a tablespace to match
the name of that in the import file.

 2) Reset the next_extent on my apps tables to 160k ?
 3) Set pctincrease to 0 ? ( I think this is a given )

Both of these are invalid on an LMT.

HTH,

Jared



On Friday 27 December 2002 10:13, Browett, Darren wrote:
 I have crossposted this question on the Oracle-Apps list, I would like
 to
 get the opinion of this list as it is more of database issue as opposed
 to apps.

 The question is about LMT and extent management with regards to Oracle
 11i.

 When upgrading to 11i, it creates migrated LMTS as opposed to
 uniform/system ones, and therefore do not conform to the rules
 of correct LMTS.

 My understanding is, even though my tablespaces are LMT, the tables
 still act like they are dictionary managed with regards to extent
 growth.

 According to How to stop defrag, and start living . for tables
 under 160M I should have an extent size of 160k.

 With that in mind, should I

 1) Create LMT Tablespaces with an extent size of 160k ? ( This is
 ignored
by the import, tables will be one extent big)
 2) Reset the next_extent on my apps tables to 160k ?
 3) Set pctincrease to 0 ? ( I think this is a given )

 Thanks

 Darren


 
 --
 Darren Browett P.Eng  This
 message was transmitted
 Data Administratorusing
 100% recycled electrons
 Information and Communication Technology
 City of Coquitlam
 P:(604)927 - 3614
 E:[EMAIL PROTECTED]
 
 ---
-- 
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).

Re: Row Migration


Geez, I didn't know you could do that.

Sheepishly,

Jared

On Friday 27 December 2002 03:38, Larry Elkins wrote:
 Someone asked in a back channel email if parallelism is used. The select
 portion of the update statement uses parallelism (though the updates
 themselves get serialized) through the use of an in-line join update (to
 avoid the second sub-query commonly used to constrain the rows being
 updated):

 Update (Select /*+ parallel hints */ 
 From   a,b
 Where  a.key = b.key)
 Set a.col1 = b.col1,
 a.col2 = b.col2
 .

 Regards,

 Larry G. Elkins
 [EMAIL PROTECTED]
 214.954.1781

  -Original Message-
  From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Larry
  Elkins
  Sent: Thursday, December 26, 2002 6:09 PM
  To: Multiple recipients of list ORACLE-L
  Subject: Row Migration
 
 
  Listers,
 
  8.1.7.4 64 Bit Solaris
 
  Does row migration utilize DB File Sequential Reads on the table? Off the
  top of my head I would expect so, but I've never tested something
  like that
  before.
-- 
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).

OAS

2002-12-27 Thread Sony kristanto

Does OAS already include in Oracle 9i ?
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sony kristanto
  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: Automatic backup on Oracle 9i

2002-12-27 Thread Sony kristanto

Jared,
Thanks Jared for your opinion, perhaps my explaination ain't quite right so
it looks like complicated but I will try to give detail explaination. By the
way what is MetaLink ?

Rgrds,

Sony


 -Original Message-
 From: Jared Still [SMTP:[EMAIL PROTECTED]]
 Sent: Saturday, December 28, 2002 9:40 AM
 To:   Sony kristanto; [EMAIL PROTECTED]
 Subject:  Re: Automatic backup on Oracle 9i
 
 
 Hmm
 
 A  lot of folks on this liststudiously avoid OEM.   I know I do, 
 and I'm not going to be much help on this.
 
 Have you tried MetaLink?
 
 Jared
 
 
 On Friday 27 December 2002 17:11, Sony kristanto wrote:
  Yes, that's right Jared, by doing this we can make schedule when we want
 to
  backup our data onto hard disk or tape periodicaly (weekly or daily even
  hour), thanks for your response and wishing you can help me to solve it.
 
   -Original Message-
   From: Jared Still [SMTP:[EMAIL PROTECTED]]
   Sent: Friday, December 27, 2002 10:06 AM
   To:   [EMAIL PROTECTED]; Sony kristanto
   Subject:  Re: Automatic backup on Oracle 9i
  
  
   Sony,
  
   What is an 'automatic' backup?
  
   Is this something supplied by that 'Oracle Enterprise Manager' thingy?
  
   Jared
  
   On Thursday 26 December 2002 17:23, Sony kristanto wrote:
Hi Listers,
I'm new on Oracle Database 9i after I migrated from Oracle 8i.
I try to use backup facility from Oracle 9i and I already follow the
instructions how to activate the automatic backup but when I see the
  
   status
  
on history I get an error comment 'Failed'. I've try again and again
but the results are the same. Could someone out there tell me why it
can't runs. For your note I use 'SYS' as my user. I will really
appreciate
  
   your
  
help.
   
Rgrds,
   
Sony
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sony kristanto
  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: Distributed Option

2002-12-27 Thread DENNIS WILLIAMS

Michael - Okay, this is a form of replication, known as synchronous
replication. That means that the updates occur synchronously, or within a
2-phase commit. This is implemented through database links. The drawback is
that the transaction is as slow as the slowest database. If one database is
unavailable, no transactions can complete. This can be okay for
non-mission-critical situations. Someone earlier (I'm sorry, I didn't keep
that message) pointed out that this may be an extra pay option back in
7.3.4. I think maybe you could query but to update you needed the extra
option. Details are available in the Oracle manuals, which you can view
online. I'm sorry, but I haven't worked on 7 in quite a few years. 
Based on your description of your situation, you may want to consider
letting the application server do the honors. A simple app server like
Tomcat can simply connect to each database. A more sophisticated app server
like Oracle9iAS or WebLogic is capable of maintaining separate message
queues, so you aren't limited by the slowest database and if a database is
down, can maintain the queue of messages and apply them when it becomes
available again. I'm speaking a little beyond my knowledge at this point,
but you get the general idea and if this sounds promising you can
investigate further with someone that actually knows what they are talking
about.

-Original Message-
Sent: Friday, December 27, 2002 5:14 PM
To: Multiple recipients of list ORACLE-L


I believe this is different than replication, though many of
the ideas and transactions would be the same.

In this particular case, they are going to allow Name and address
changes over the web. Those changes will cause updates to two some
what different customer files on two different applications on two
different other databases.

What happens now is when they tried to do the update over the
database link, and commit, they get distributed option not
installed.

This is further confused that one database has replication but
still gets an error message about distributed option not
installed. And the v$option shows distributed option as
false or what ever.

Connected to:
Oracle7 Server Release 7.3.4.0.1 - Production
With the parallel query option
PL/SQL Release 2.3.4.0.0 - Production

This is the sign on for one of the databases missing the option.

My personal oracle shows:

Connected to:
Personal Oracle7 Release 7.2.2.3.1 - Production
With the distributed and replication options
PL/SQL Release 2.2.2.3.1 - Production

Maks.

 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of DENNIS
 WILLIAMS
 Sent: Friday, December 27, 2002 4:19 PM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: Distributed Option


 Michael
By distributed, I assume you mean replication?
From what I can tell, basic replication is included with
 Standard Edition
 and advanced replication is included with Enterprise Edition.
I think you run a script, something like catrep.sql in
 rdbms/admin, so
 you should be able to get the pieces installed.
Replicating between different Oracle versions could be challenging, but
 doable. Others on the list can probably offer specific advice on pitfalls.
We haven't implemented replication here, just studied. From what I can
 tell, planning for replication is everything. I have only been
 able to find
 2 books on it. The prize is Marie Buretta's Database Replication.
 http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613
 /sr%3D11-1
 /ref%3Dsr%5F11%5F1/102-1511927-6720101
 It really tells you everything you need as an organization to prepare for
 replication. Replication takes a lot of administration so it should be a
 gold mine since you are consulting.
The other book is Oracle Distributed Systems by Charles Dye
 http://www.amazon.com/exec/obidos/ASIN/0471157546/qid%3D1041023613
 /sr%3D11-1
 /ref%3Dsr%5F11%5F1/102-1511927-6720101


 Dennis Williams
 DBA, 40%OCP
 Lifetouch, Inc.
 [EMAIL PROTECTED]

 -Original Message-
 Sent: Friday, December 27, 2002 2:39 PM
 To: Multiple recipients of list ORACLE-L


 I've got a client that needs distributed
 option installed on several databases,
 versions 7.3.4, 8.0.5, and 8.1.7...

 Problem may be I'm not sure we'll have all
 the CD's as vendors of applications did
 most of the installs and we think we'll
 find that they took the CD's with them.
 After all, if it's up and running and
 vendor supported(or was), why would the
 client need the CD?

 Anyhow, is it just a free option that will
 need to be selected with the Oracle installer,
 or is it an add on that one is supposed to
 contact Oracle on? Or perhaps it just involves
 running one of those all but undocumented
 packages.

 There are many ways to work around this if
 we have to, but would like to know what all
 is involved if we can get the distributed
 option installed on all of their databases.

 It's on my Personal Oracle, but seems to
 be part of a general

RE: Row Migration

2002-12-27 Thread Jeremy Pulcifer

Title: RE: Row Migration





Gaaa!! Neither did I!!!


(I've been looking for a better way to do that query for years...)


 -Original Message-
 From: Jared Still [mailto:[EMAIL PROTECTED]] 
 Sent: Friday, December 27, 2002 6:49 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: Row Migration
 
 
 
 Geez, I didn't know you could do that.
 
 Sheepishly,
 
 Jared
 
 On Friday 27 December 2002 03:38, Larry Elkins wrote:
  Someone asked in a back channel email if parallelism is used. The 
  select portion of the update statement uses parallelism (though the 
  updates themselves get serialized) through the use of an 
 in-line join 
  update (to avoid the second sub-query commonly used to 
 constrain the 
  rows being
  updated):
 
  Update (Select /*+ parallel hints */ 
  From a,b
  Where a.key = b.key)
  Set a.col1 = b.col1,
  a.col2 = b.col2
  .
 
  Regards,
 
  Larry G. Elkins
  [EMAIL PROTECTED]
  214.954.1781
 
   -Original Message-
   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf 
 Of Larry 
   Elkins
   Sent: Thursday, December 26, 2002 6:09 PM
   To: Multiple recipients of list ORACLE-L
   Subject: Row Migration
  
  
   Listers,
  
   8.1.7.4 64 Bit Solaris
  
   Does row migration utilize DB File Sequential Reads on the table? 
   Off the top of my head I would expect so, but I've never tested 
   something like that before.
 -- 
 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).

Re: Row Migration

don't feel too sheepish, I didn't know it either. Larry is the SQL guru
and I bow to his knowledge. and had already saved off this email as
this sort of update is something we do often and I ALWAYS have problems
figuring out the correct SQL :)

rachel
--- Jared Still [EMAIL PROTECTED] wrote:
 
 Geez, I didn't know you could do that.
 
 Sheepishly,
 
 Jared
 
 On Friday 27 December 2002 03:38, Larry Elkins wrote:
  Someone asked in a back channel email if parallelism is used. The
 select
  portion of the update statement uses parallelism (though the
 updates
  themselves get serialized) through the use of an in-line join
 update (to
  avoid the second sub-query commonly used to constrain the rows
 being
  updated):
 
  Update (Select /*+ parallel hints */ 
  From   a,b
  Where  a.key = b.key)
  Set a.col1 = b.col1,
  a.col2 = b.col2
  .
 
  Regards,
 
  Larry G. Elkins
  [EMAIL PROTECTED]
  214.954.1781
 
   -Original Message-
   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
 Larry
   Elkins
   Sent: Thursday, December 26, 2002 6:09 PM
   To: Multiple recipients of list ORACLE-L
   Subject: Row Migration
  
  
   Listers,
  
   8.1.7.4 64 Bit Solaris
  
   Does row migration utilize DB File Sequential Reads on the table?
 Off the
   top of my head I would expect so, but I've never tested something
   like that
   before.
 -- 
 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).
 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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: Those Pesky Little Audit Files (ora_99999.aud)

2002-12-27 Thread Deshpande, Kirti

Metalink Note #1022776.6 explains why.. :)

-
Kirti

-Original Message-From: Mogens Nørgaard
[mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 10:49
AMTo: Multiple recipients of list ORACLE-LSubject: Re:
Those Pesky Little Audit Files (ora_9.aud)Yeah, it's a
nuisance in most installations, but the idea is to be compliant with some
abbreviation_that_I'm_sure_Tim_can_remember security standard. Give me a
7.1 doc site (if it exists) and I'll find the details. I failed to find 7.1 doc
on Google searches. Probably too much beer.MogensJamadagni,
Rajendra wrote:

O Oracle Guru's
Please tell us, why _trace_files_public is *STILL* an underscore
parameter??

Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN
dot com
Any opinion expressed here is
personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts,
but having an opinion is an
art!

-Original Message-From: Mogens Nørgaard [mailto:[EMAIL PROTECTED]]Sent:
Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list
ORACLE-LSubject: Re: Those Pesky Little Audit Files
(ora_9.aud)They were put there in 7.1 in order to
comply with some security standard. And their purpose is exactly to prevent
a dba from logging in without being monitored. It's in the 7.1 new features
manual, as far as I remember. That's also the version where it was
suddenly not possible for the poor deveopers to see their own tracefiles,
except if they set _trace_files_public=true.Mogens
*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.*1

Re: Automatic backup on Oracle 9i


MetaLink is Oracle's support site.

metalink.oracle.com

No, I don't think your explanation is complicated, I just
don't use OEM.

I fired it up to take a look, but the backup portion requires
the OEM repository to be setup, so I didn't learn anything.

Yes, I *do* make backups, but use RMAN directly with 
Veritas NetBackup.

Jared

On Friday 27 December 2002 19:44, Sony kristanto wrote:
 Jared,
 Thanks Jared for your opinion, perhaps my explaination ain't quite right so
 it looks like complicated but I will try to give detail explaination. By
 the way what is MetaLink ?

 Rgrds,

 Sony

  -Original Message-
  From:   Jared Still [SMTP:[EMAIL PROTECTED]]
  Sent:   Saturday, December 28, 2002 9:40 AM
  To: Sony kristanto; [EMAIL PROTECTED]
  Subject:Re: Automatic backup on Oracle 9i
 
 
  Hmm
 
  A  lot of folks on this liststudiously avoid OEM.   I know I do,
  and I'm not going to be much help on this.
 
  Have you tried MetaLink?
 
  Jared
 
  On Friday 27 December 2002 17:11, Sony kristanto wrote:
   Yes, that's right Jared, by doing this we can make schedule when we
   want
 
  to
 
   backup our data onto hard disk or tape periodicaly (weekly or daily
   even hour), thanks for your response and wishing you can help me to
   solve it.
  
-Original Message-
From:   Jared Still [SMTP:[EMAIL PROTECTED]]
Sent:   Friday, December 27, 2002 10:06 AM
To: [EMAIL PROTECTED]; Sony kristanto
Subject:Re: Automatic backup on Oracle 9i
   
   
Sony,
   
What is an 'automatic' backup?
   
Is this something supplied by that 'Oracle Enterprise Manager'
thingy?
   
Jared
   
On Thursday 26 December 2002 17:23, Sony kristanto wrote:
 Hi Listers,
 I'm new on Oracle Database 9i after I migrated from Oracle 8i.
 I try to use backup facility from Oracle 9i and I already follow
 the instructions how to activate the automatic backup but when I
 see the
   
status
   
 on history I get an error comment 'Failed'. I've try again and
 again but the results are the same. Could someone out there tell me
 why it can't runs. For your note I use 'SYS' as my user. I will
 really appreciate
   
your
   
 help.

 Rgrds,

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

RE: Row Migration