from:"tim"

So, my intention to set P_A_T to 140G on a new datawarehouse is ill-advised?

I'm not kidding, by the way.  The Sun E15K belonging to the project I'm
currently working on (purportedly) has 160G of RAM.  It is still in the box,
so I'm not believing anything until I type prtconf...

I wasn't planning to use more than 10G or so for SGA, and that much only
because I can... wee-hah!...

Any thoughts?




on 1/21/04 3:14 PM, Jonathan Lewis at [EMAIL PROTECTED] wrote:

 
 A comment I picked up from Tom Kyte's
 Masterclass in Copenhagen last week was
 that there is an effective limit of 1GB to
 P_A_T - and although a single session is
 supposed to be allowed 5% of the P_A_T,
 you could get about 90MB.  So there are
 some funny things going on in that area
 which still need fixing.
 
 It's a bit tough for big systems, as I've
 found that the optimizer seems to be
 much smarter about memory user and
 access paths when P_A_T and W_S_P
 are set.
 
 What's the book about ?
 
 Regards
 
 Jonathan Lewis
 http://www.jlcomp.demon.co.uk
 
 The educated person is not the person
 who can answer the questions, but the
 person who can question the answers -- T. Schick Jr
 
 
 Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland
 
 
 One-day tutorials:
 http://www.jlcomp.demon.co.uk/tutorial.html
 
 
 Three-day seminar:
 see http://www.jlcomp.demon.co.uk/seminar.html
 UK___February
 
 
 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]
 Sent: Wednesday, January 21, 2004 7:44 PM
 
 
 Replies in line...
 
 - Kirti
 
 --- [EMAIL PROTECTED] wrote:
 Kirti, you're back!
 
 Thanks. Found some slack time from routine DBA work!
 
 
 Must have finished the book.  :)
 
 Not yet.. Its tough..
 
 
 
 
 Re the PGA problems, what was the value for 'over allocation count' in
 v$pgastat?
 
 Actually, I never bothered to look at v$pgastat. Should have.. and will,
 when we do some more
 testing next week..
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Has anyone done any scalability work on dbms_lock?

DBMS_LOCK manipulates the same enqueue mechanism that Oracle uses for just
about everything.  Same as every other type of lock shown in V$LOCK, just
type = UL.

Thus, any problems in DBMS_LOCK would be shared by just about every facet of
session-level concurrency in the RDBMS...



on 1/21/04 5:49 AM, Nuno Souto at [EMAIL PROTECTED] wrote:

 As in: does it present an inherent or hidden performance
 problem when a lot of sessions try to lock/release the same
 lock?  Or how many lock/release per second.  Or some other
 idea of how efficient it is?
 
 Need to use it in a design, but not sure of any potential
 performance hits or scalability issues.  Any ideas?
 
 TIA.
 Cheers
 Nuno Souto
 [EMAIL PROTECTED]

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Oracle 8.1.7 can only use the first 15th indexes?

Amen to that.  I had a table with about 40 indexes on v7.0.16.  I don't
think that it was possible that any of them could have been ignored,
because all of them were used.  I can't verify that, because this system was
born and died (subsequently cremated) over 10 years ago and I never thought
to check while it was breathing, but like I said, all 40 or so indexes were
absolutely necessary...

Redesign?  Well, according to the architect, this was the perfect design.
Over 150 logical entities were encapsulated within this single table, which
also happened to be the only table in the entire application (at least in
the beginning).

Appropriately enough, its name was DATA...


on 1/21/04 2:44 AM, Nuno Souto at [EMAIL PROTECTED] wrote:

 Let's be realistic:  any table with  15 indexes
 PROBABLY needs a little bit of a re-design
 exercise?  ;)
 
 Cheers
 Nuno Souto
 [EMAIL PROTECTED]
 - Original Message -
 snip
 (I assume the report intended to say the first 15
 indexes on a specific table, 'cos the data dictionary
 alone has rather more than 15 indexes).

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: data manipulation of a large unix file

If you're on 9i, external tables and pipelined table functions should be
useful...


on 1/22/04 7:59 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:

 We are getting a feed of an 800 MB file that will come in nightly. It needs to
 be loaded to the database. Per requirements, we have to add some data to the
 file before loading(its not negotiable).
 
 ksh eats up 24% of total CPU on a 4 CPU Solaris box. We cannot do this. I am
 not allowed to stream it with a named pipe. any other solutions?
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: how do I interpret this in bstat/estat

2004-01-20 Thread Tim Gorman

Gene,

This is the problem with high-level aggregate reports like
BSTAT/ESTAT and STATSPACK.  A possible problem is
highlighted, but there is no detail on the possible cause.

One way to get more info is monitor V$SESSION_EVENT view
searching for sessions with lots of waits on enqueue
wait-event:

  select sid, time_waited
  from   v$session_event
  where  event_name = 'enqueue'
  order by 2 desc;

When you find an active session that seems to fit, then find
out more about it, and most especially slap a level-8 SQL
trace on the session for a period of time.  Also, get more
information from V$SESSION to understand what program is
running, etc...

Very often, you find out that the situation is benign, for
example:

   * a single session is responsible for all enqueue
 timeouts; this session simply waits on a
 DBMS_PIPE for messages, but the sending
 application is not sending any messages, thus
 waiting application racks up lots of timeouts...

Hope this helps...

-Tim


 Hi.
 
 I am looking at the bstat/estat report and see a high
 number of enqueue timeouts in the statistics section
 of the report. How do I tackle that? In the Niemec's
 book he receoomends increasing the enqueue_resources
 parameter. Metalink says that these may be related to
 DISTRIBUTED_LOCK_TIMEOUT being exceeded in a
 distributed transaction. Comments about changing
 ENQUEUE_RESOURCES are ill-founded. But it doesn't make
 any recommendations. 
 So my question is whether anyone has any practical
 suggestions what I can do to address this issue. I am
 running Oracle 9203 (yes, I should be usuns the
 statspack, but I haven't switched to it yet). 
 
 TIA
 
 Gene
 
 
 __
 Do you Yahoo!?
 Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
 http://hotjobs.sweepstakes.yahoo.com/signingbonus
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Gurelei
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Reasonable layout for DSS

2004-01-19 Thread Tim Gorman

- volumes 2,3,4 (RAID1 = 132 Gb apiece):  redo, undo, table tablespaces,
  and busy index tablespaces

Yes, it can be argued that archived redo logfiles are busy, but their
impact on database performance is not as direct as the impact of the online
redo logfiles...

 
 Keith H.

Just some ideas -- hope it helps...

-Tim

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SLA/metrics for RFP for programming services

2004-01-19 Thread Tim Gorman

Title: Re: SLA/metrics for RFP for programming services



Jeff,

As youre aware, whatever metrics you choose will govern their behavior. Completed deliverables is a good start, but if youre looking for well-tuned deliverables you could add a tuning-review gate through which each deliverable has to pass? Failure to resolve any items raised by the tuning-review committee will mean non-completion of the deliverable. Resolution of items raised by the committee should include the contractor asserting the expected benefit of the item is not worth the cost/effort.

As a housepainting contractor years ago, I bid fixed-price and was paid on deliverables, with 33% paid on signing the contract, 33% paid at a specifed half-way point, and 33% upon completion. Nowadays, I only do a few fixed-price jobs, but they are often structured similarly. Your tuning-review gateway for each deliverable would be logical as the specified half-way point, perhaps? Just an idea...

There are ways to accomplish the letter of just about any metric but not the intent. So, getting too esoteric will likely lead to a distorted result. If contractor does not trust such a committee not to be fair, then perhaps there isnt enough trust (or desperation! :-) ) in the relationship to make it work anyway.

Hope this helps...

-Tim


on 1/18/04 11:04 AM, Thomas Jeff at [EMAIL PROTECTED] wrote:

Tim,
 
Thanks for the reply. We are thinking more along the lines of metrics pertaining
to identifying the required efficiency of the outsourced ETL task, e.g. time, resource 
utilization,throughput, etc, in essence some desired baseline resource profile. 
 
We are new to writing up RFPs at this level of granularity with respect to services, so 
if this all rather unreasonable, in yours (or anyone's) opinion, I'd like to know. It's
not just a matter of defining that the deliverables are to be completed during X number of 
days for $ cost, but we are thinking that we need to identify some metrics that would help 
us to specify some acceptable performance criteria, against which we would monitor when 
performing some form of acceptance testing. It does us no good if the contracted ETL
task capitalizes the box (like the runaway PL/SQL program mentioned by Ryan) and takes 
3 days to complete. 
 

 

-Original Message-
From: Tim Gorman [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 16, 2004 10:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: SLA/metrics for RFP for programming services

How about specific deliverables within a specific period of time for a total amount not-to-exceed? Cant think of any other metrics that matter...


on 1/16/04 2:19 PM, Thomas Jeff at [EMAIL PROTECTED] wrote:


If you were to write up a RFP for programming services, what kind, if any, metrics 
would you include to provide some measurements by which performance of the contract 
can be assessed? The tasks will typically be writing up ETL runs for our DW. 

CPU, LIO, wait events, etc? 

Thanks! 


 
Jeffery D Thomas 
DBA 
Thomson Information Services 
Thomson, Inc. 

Email: [EMAIL PROTECTED] 

Indy DBA Master Documentation available at: 
http://gkmqp.tce.com/tis_dba http://gkmqp.tce.com/tis_dba

Re: What to look for in STATSPACK report

2004-01-19 Thread Tim Gorman

Helmut,

Register with http://www.oraperf.com; and run those STATSPACK reports
through the YAPP analyzer, which will reformat them in such a way that they
make sense.

All of the ratio stuff on the STATSPACK report is ignored by the YAPP
analyzer, and instead the reformatting looks at things from the standpoint
of response-time analysis, as described in the white papers at
http://www.oraperf.com/whitepapers.html;.

Yes, I know OraPerf is now owned by Veritas and the real URLs are different,
but it'll always be just good old oraperf.com hopefully, no matter who
Anjo works for...  :-)

Hope this helps...

-Tim


on 1/18/04 11:24 PM, Daiminger, Helmut at [EMAIL PROTECTED] wrote:

 Hi!
 
 We want to introduce a performance monitoring policy here. We are using the
 STATSPACK utility.
 
 What are sections in statspack reports to look for? What are threshold
 numbers for these values?
 
 Does anybody have any power points or papers about it?
 
 This is 9.2 on HP-UX.
 
 Thanks,
 Helmut
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: any student versions of 'business objects' ab initio or

2004-01-17 Thread Tim Gorman

Title: Re: any student versions of 'business objects' ab initio or informatica?



Probably best to contact them directly?

I suspect that Ab Initio in particular will be out of reach for this purpose. My understanding is that they choose their customers based on their capability to deliver a data warehouse succesfully. Just a rumor, but it makes a certain amount of sense if you want to streamline your post-sales support costs. I understand that they are more selective of potential customers than the prospective customers are of ETL tools, actually deciding not to sell to certain prospects.

Talk about business intelligence!

If this is true, then I would suspect that student discounts are not an option. Of course, it might just be a rumor...



on 1/17/04 9:44 AM, Ryan at [EMAIL PROTECTED] wrote:

Something affordable. These are hot right now and I'd like to learn how to use them. I cant find anything on the web.

Re: SQL server and JOB

2004-01-16 Thread Tim Gorman

First, you may have to quit your job in Oracle...


on 1/16/04 5:59 AM, [EMAIL PROTECTED] at
[EMAIL PROTECTED] wrote:

 Hi , 
 How can i create a job in sql server ?
 
 Rgds.
 Arslan.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: SLA/metrics for RFP for programming services

2004-01-16 Thread Tim Gorman

Title: Re: SLA/metrics for RFP for programming services



How about specific deliverables within a specific period of time for a total amount not-to-exceed? Cant think of any other metrics that matter...


on 1/16/04 2:19 PM, Thomas Jeff at [EMAIL PROTECTED] wrote:

If you were to write up a RFP for programming services, what kind, if any, metrics 
would you include to provide some measurements by which performance of the contract 
can be assessed? The tasks will typically be writing up ETL runs for our DW. 

CPU, LIO, wait events, etc? 

Thanks! 


 
Jeffery D Thomas 
DBA 
Thomson Information Services 
Thomson, Inc. 

Email: [EMAIL PROTECTED] 

Indy DBA Master Documentation available at: 
http://gkmqp.tce.com/tis_dba http://gkmqp.tce.com/tis_dba

Re: Partitioning question (duplicate?)

2004-01-14 Thread Tim Gorman

Dan,

Good question, but unless I'm misinterpreting the results, the answer is
no...

SQL show release
release 902000100
SQL create table test
  2  (a date, b number, c number)
  3  partition by list (to_char(a, 'MON'))
  4  (partition pJAN values ('JAN')),
  5  (partition pFEB values ('FEB'))
  6  (partition pMAR values ('MAR'))
  7  (partition pAPR values ('APR'))
  8  (partition pMAY values ('MAY'));

partition by list (to_char(a, 'MON'))
  *
ERROR at line 3:
ORA-00907: missing right parenthesis

..seems to clearly be interpreting the phrase to_char as a column name...

Hope this helps...

-Tim

on 1/14/04 3:24 PM, Daniel Fink at [EMAIL PROTECTED] wrote:

 Pardon if this is a duplicate, but the original has not shown up
 on the list after 3 hours...
 
 Is it possible in 9.2 to partition on a function?
 
 I have a table with a date column and I would like to partition
 by month, regardless of the year. For example, data from January
 2003 or January 2004 would go into the same partition. Any
 sneaky ideas on how to accomplish this without changing the data
 structures.
 
 Daniel Fink

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Problem with archive log when testing recovery..., urgent

2004-01-12 Thread Tim Gorman

Are you certain that SHUTDOWN IMMEDIATE succeeded?  There are lots of bugs
in MetaLink where it hangs or fails with ORA-00600...


on 1/12/04 7:04 AM, Wendry at [EMAIL PROTECTED] wrote:

 Thank you for your reply,
 
 Yes, you're quite right, I think the archive next to the cold backup is
 corrupted. The hot backup is always succeeded. But I wonder why is my
 cold backup is largely end up with internal error or corrupted archive
 log files. 
 
 All I do for closed  backup is shutdown immediate, copy all
 controlfiles, datafiles and redo log files to backup destination, and
 then reopen the database. Is there any steps that I left which cause the
 archive log to be corrupted? Thank you in advance.
 
 Regards,
 
 Wendry.
 
 
 -Original Message-
 Sent: Monday, January 12, 2004 12:36 PM
 To: Wendry
 
 In which order did you perform the backups ?
 
 Say you do the cold backup, and the next archive is corrupted.
 
 You then do a hot backup. Restore the cold backup and the
 archive needs to be applied.
 
 Restore the hot backup, and it is after the corrupted archive.
 
 Does the corruption occur with an archive used by the cold
 backup, but not the hot backup ?
 
 Cheers
 
 GJC
 
 The fifty dwarves were reduced to eight,
 before anyone suspected hungry.
 
 __
 Gary Colbran
 
 System/Database Administrator
 Telkom SA 
 55 Oak Avenue
 Centurian
 South Africa
 Ph: 012-680 1315.
 Ph: 082-786 6592.
 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
 mailto:[EMAIL PROTECTED]
 
 **Disclaimer**
 **
 
 Information contained in this E-MAIL being proprietary to Telkom SA
 and is 
 'privileged' and 'confidential' and intended for use only by the
 individual
 or entity to which it is addressed. You are notified that any use,
 copying 
 or dissemination of the information contained in the E-MAIL in any
 manner 
 whatsoever is strictly prohibited.
 
 **
 *
 
 
 
 -Original Message-
 Sent: 16 January 2004 03:16
 To: LazyDBA.com Discussion
 
 
 
 
 Hi all,
 
 I have done closed and open backup on my database. Later on I try to
 test
 my
 backup. So I try the closed backup that I've taken. The backup
 database can
 be opened succesfully. But when I try to recover database using
 backup
 controlfile, the archive logs giving me internal error (sometimes
 after
 applying 1st to 5th archive log), sometimes it gave me notification
 that
 the
 archive logs is corrupted.
 
 Strangely when I test my open backup, and recover it using the same
 set of
 archive logs, the process went smoothly. So what is the real problem
 here,
 I
 really don't have any idea. Is there somebody have the same
 experience? Now
 I'm in doubt of planning my backup schedule, please help... Thanks a
 lot.
 
 Regards,
 
 Wendry.
 
 
 
 
 Get today's cartoon: http://www.LazyDBA.com
 Please don't reply to RTFM questions
 Oracle documentation is here: http://tahiti.oracle.com
 To unsubscribe: send a blank email to
 [EMAIL PROTECTED]
 To subscribe:   send a blank email to [EMAIL PROTECTED]
 By using this list you agree to these
 terms:http://www.lazydba.com/legal.html

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Should we stop analyzing?

2004-01-11 Thread Tim Gorman

Don,

Comments inline...

 Yes!  IME, there ARE still problems in the CBO, especially with complex
 subqueries.
 I have more than a dozen systems where management insists on staying with
 the RBO!

[TG]: With all due respect, what does management know about this stuff
anyway?  They do not work with it, they do not research it, and they do not
understand the issues if technical people do not research, understand, and
inform them.

Management makes decisions based on information provided.  That is their
job.  Bad information, bad decisions.

 Every time we collect deep stats and histogram and switch optimizer_mode,
 hundreds of statements generate poor plans.

[TG]: Please, let's talk specific examples, not generalities.  This list
resolves specific examples almost every week, and never (in my recollection)
has a resolution involved going to RBO.  Someone please correct me if I've
mis-spoken.  To verify, some enterprising soul may choose to review the list
archives going back over two years, which are available on
http://www.orafaq.com;.

First of all, besides statistics, there are some init.ora parameters
(besides OPTIMIZER_MODE) to be set appropriately, such as
OPTIMIZER_INDEX_CACHING.

 It would cost these clients many thousands of dollars to have adjusted these
 plans, and management says If it ain't broke, why fix it.

[TG]: No doubt any application transitioning from RBO to CBO needs to be
tested thoroughly.

But how about the success stories of the CBO?  How about all of the queries
that were impossible to fix under the RBO but now magically performed well
after implementing CBO, and how about the dozens of options for fixing bad
situations using the myriad options available with the CBO?  Function-based
indexes?  Materialized views and query rewrite?  Etc, etc, etc...

Personally, I can't understand why anyone would continue to bleed money away
using the RBO.  Certainly, legacy software that requires RBO should continue
to use it until end-of-life.  But advocating a return to the RBO for new
applications is not rational.

Again, please let's discuss specifics...

 
 We need look no further than Oracle Applications to see this issue.
 Oracle made a big-deal about going to the CBO in 11i, yet when we look at
 the SQL, a significant number of statement employ the rule hint!
 Connect-the-dots and you can guess why the RBO IS NOT being removed from
 Oracle10g. . . .

[TG]: I can't even spell 10g, so I'll take your word for it...

The OraApps 11i assertion did not sound right, so to verify I queried both
the V$SQLAREA view as well as the STATSPACK repository (i.e. STATS$SQLTEXT)
on a rather busy OraApps 11.5.8 system running Financials, ERP, HR/Payroll,
Order Entry, and Inventory.  The STATSPACK repository is only holding 14
days worth of data;  I keep it purged pretty tight to keep it below 1Gb in
size...

In both V$SQLAREA and STATS$SQLTEXT, I found only nine (9) and eight (8) SQL
statements, respectivley, using the RULE hint, all of which were querying
the data dictionary objects only.

8-9 is not what I would call a significant number, not when V$SQLAREA has
over 50,000 distinct SQL statements and STATS$SQLTEXT has almost 6,400
distinct SQL statements.

Here is the query and results from the STATSPACK repository:

SQL break on hash_value
SQL select hash_value, sql_text from stats$sqltext
  2  where upper(text_subset) like '%/*+%RULE%*/%'
  3  order by hash_value, piece;

HASH_VALUE SQL_TEXT
-- 
 296554613 Select /*+ RULE */ * FROM SYS.ALL_SYNONYMS WHERE ((OWNER = :own)
OR (TABLE_OWNER = :own and OWNER = 'PUBLIC')) and db_link is nu
   ll  and TABLE_NAME =  'OE_SOLD_TO_ORGS_V' ORDER BY SYNONYM_NAME
 476032654 SELECT /*+ rule  */O.SUBNAME PART_NAME,O.OBJ# OBJ_NUM   FROM SYS
   .USER$ U,SYS.OBJ$ O  WHERE U.NAME = :b1  AND O.OWNER# = U.USER#
AND O.NAME = :b2  AND O.TYPE# = 19 ORDER BY PART_NAME
 529775420 SELECT /*+ rule  */C.NAME COL_NAME,C.TYPE# COL_TYPE,C.CHARSETFOR
   M COL_CSF,C.DEFAULT$ COL_DEF,C.PROPERTY COL_PROP,C.COL# COL_UNUM
   ,C.INTCOL# COL_INUM   FROM SYS.USER$ U,SYS.OBJ$ O,SYS.COL$ C  WH
   ERE U.NAME = :b1  AND O.OWNER# = U.USER#  AND O.TYPE# = 2  AND O
   .NAME = :b2  AND O.OBJ# = C.OBJ#
 531307833 Select /*+ RULE */ t.*, o.status validity from SYS.ALL_TRIGGERS
   t, SYS.ALL_OBJECTS o where t.owner = o.owner and t.trigger_name
   = o.object_name and o.object_type = 'TRIGGER' and o. OWNER = :ow
   n AND ( t.table_name =  'OE_SOLD_TO_ORGS_V'  OR o.ob
   ject_name =  'OE_SOLD_TO_ORGS_V' ) ORDER BY t.TRIGGER_NAME
 787810128 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
   estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
   density, col#, spare1, spare2, avgcln from hist_head$ where obj#
   =:1 and intcol#=:2
2014200833 select /*+ RULE

Re: pga workarea and ora-04030

2004-01-10 Thread Tim Gorman

 := 'start loop';
   for i in 1..psize loop
 uitleg := 'insert i= ' || i;
 assarray(i) := i;
 /*
 uitleg := 'insert i2= ' || i;
 assarray2(i) := i;
 */
   end loop;
   EXCEPTION
 WHEN OTHERS THEN
   dbms_output.enable(2);
   dbms_output.put_line(' Exception raised ' || uitleg );
 end;
 end;
 -Oorspronkelijk bericht-
 Van: Jonathan Lewis [mailto:[EMAIL PROTECTED]
 Verzonden: dinsdag 6 januari 2004 16:49
 Aan: Multiple recipients of list ORACLE-L
 Onderwerp: Re: pga workarea and ora-04030
 
 
 The workarea_policy stuff does not apply
 to things like pl/sql tables, only to tuneable
 memory.  Given that you don't have the
 problem when you disable p_a_t and w_p,
 it may be that there is some buggy event
 occurring where the workarea_policy code
 is being infringed by an abuse of pga memory.
 
 You could try setting up test cases where
 you use a pl/sql loop to build a pl/sql table.
 Make it a procedure with an input parameter
 that is the table size, and see how big the table
 has to before the procedure crashes.  Fiddle
 with the p_a_t, and w_p (they can be set
 separately) to see if the crash point moves.
 
 This may give you (or Oracle Corp) some clues.
 
 
 
 --
 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).

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: What is the fastest way to dump oracle data into a human

2004-01-10 Thread Tim Gorman

Options #1 (Perl) and #2 (PRO*C) would be fastest and easiest.  The PRO*C
demo programs provide a decent start, for option #2.  Option #3 (OCI) would
be not faster than PRO*C and, due to the increased complexity of OCI, a more
problematic approach.

SQL*Plus is the easiest method to implement by far, but the SQL*Plus program
introduces a surprising amount of overhead processing, making it much
slower than Perl or PRO*C, but much faster than UTL_FILE.  If you are not in
a big time crunch but want decent performance, use SQL*Plus.  If you are in
a time crunch and need the best performance, use Perl or PRO*C.


on 1/10/04 6:09 PM, Guang Mei at [EMAIL PROTECTED] wrote:

 Hi:
 
 I have a program (running on oracle 8173 server) that writes 48 Millions
 lines of data into various text files . The selected data is from various
 tables and I have the query pretty much optimized. Now I am trying to find
 the fastest way to dump the selected data into a text file on the same
 oracle server. The program (written as a pl/sql package) now works
 something like this:
 
 str varchar2(32767) := '';
 NL  char(1) := chr(10);  -- new line character
 begin
  fpn := utl_file.fopen(directory, filename, 'w', 32767);
  for x in cur1 loop
str := str || x.str || NL;   -- keep building the str
if (length (str)  31000 ) then
   str := substr (str,1, length(str) -1 );
   utl_file.put_line(fpn, str);
   str := '';
end if;
  end loop;
 
  -- dump the last part:
  str := substr (str,1, length(str) -1 );
  utl_file.put_line(fpn, str);
  utl_file.fflush(fpn);
  utl_file.fclose(fpn);
 end ;
 
 
 The above code works perfect fine now. But I am wondering if there is
 another way that could increase the writing siginificantly faster. This
 porgram does not have to be in pl/sql.  I can think of a couple of
 potential approaches:
 
 1. Write a perl program, basically using perl's DBI/DBD to select the data
 from the database, then calling perl's print to write data into a file.
 I have not tested this and don't know if it is faster that
 utl_file.put_line.
 
 2. Write a C program, using ProC to talk to DB, then use C's fopen and
 fwrite(?) to dump data into text file.
 
 3.  Write a C program,using OCI to talk to DB, then use C's fopen and
 fwrite(?) to dump data into text file.
 
 I don't have direct experience with ProC and OCI, so I don't know how
 faster (or any) it would be  by doing Option 2 or 3 above. Does anyone
 know if I would see siginificant performance boost by using C? Any other
 suggestions?
 
 TIA.
 
 Guang
 
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Backups in a DW Environment

2004-01-09 Thread Tim Gorman

Mohammed,

Comments inline...

on 1/9/04 2:24 PM, mkb at [EMAIL PROTECTED] wrote:

 Have a question on backups in a DW environment.
 
 Our DW is somewhat small at the moment but projected
 to grow.  I seem to be having a hard time trying to
 convince the sys admin that I don't want archive
 logging turned on.  To me, it does'nt make much sense.

On the contrary, not using archivelog mode is what makes less sense, thus
justifying more careful consideration and justification.  Archivelogging is
the industry standard and makes complete sense in all but a few extreme
cases.

Have you considered what archive logging actually provides for you, and what
is necessary to engineer the same effects on your own?  Think it through...

 
 He's proposed using EMC BCV's which I've agreed to
 (and also sounds like a good idea) but also wants to
 turn on archiving.  My thinking is why turn on
 archiving if I can restore my DB from last night's
 BCV's and then bring it up to date by re-loading any
 data that was loaded after the BCV split.

The rebuild-then-reload method seems to make sense on paper, but it is the
cause of extreme difficultly in actual practice.  If you have not yet
already implemented a very mature change-management procedure, to record all
changes in the database, complete with all of the security to prevent it
being bypassed, then you are in for a rough time.

Robust change-management and ironclad security always makes sense, but the
extra insurance of being able to recover every change using archivelogging
makes sense also.

Also, on the topic of BCV splits, one of the problems of using BCV
splits (or file-system snapshots or similar snapshot schemes) is that,
while it makes backups very easy, it does not make recovery any easier.
This type of backup-centric thinking is very seductive.

What is the purpose of the whole exercise?  Taking backups?  Or being
recoverable?

Oracle Recovery Manager (RMAN) is not named Backup Manager for a reason.
RMAN is recovery-centric.  It seems more complex on the backup end of
things (it isn't), but it is undeniably easier on the recovery side of
things.  Try to work RMAN into your strategy at all times.  It is worth the
extra consideration.

 
 Our system is not 24x7 so we can shutdown before the
 BCV split.  Also, it's not directly accessed by users
 for ad-hoc queries.  Automated processes access the
 database and build cubes using Cognos tools.  Users
 access these and not the DB directly.

Any data warehouse that is shutdown, even for a few minutes, just to take a
backup, has been engineered to fail.  People keep data warehouses busy on a
24x7 basis just like any other system.

 
 So, again I don't see the need for archive logging.
 
 Any thoughts?
 
 mohammed

Hope this helps...

-Tim

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Re: Difference on ArchiveLog (I'm rewriting the question)

2004-01-08 Thread Tim Gorman

Mauricio,

Variances of 1G of redo generation (i.e. 4G one day, 2G
another day, 3G another day) are not indicative of anything
unusual.  I've seen systems that generate 4T of redo one
day, 6T of redo another day, and then only 0.5T of redo the
following day.  All without changing the size of the online
redo log files...  :-)  Oh yes, the size of the particular
database involved was about 6T, so there is also little
correlation between the size and number of datafiles in the
database and the volume of redo generation.

More likely than not, your hypothesis that changes in online
redo log size are the cause of (or even factors in) these
variations in redo generation is just plain incorrect.  It
is much more likely that the real cause is simply variations
in application workload and activity.

You may have already done this, but I think you might want
to summarize the volume of redo archived over longer periods
of time using:

select trunc(first_time), sum(blocks*block_size)/1048576
mb
from v$archived_log group by trunc(first_time)

I imagine you'll find that such variations occur all the
time, completely unrelated to the size of the online redo
log files or changes made to those sizes.  If you have any
job-execution records, you might find a closer correlation
between certain batch jobs and the volume of redo generated,
for example.

Don't look for bugs in the database or the operating system
when application user behavior is a much more probable cause
of the effect you are seeing.

Hope this helps...

-Tim



 Hello everybody thank for your answers, 
  
 the size I'm talking about is summing up real sizes of
 archivelogs files, and I had each configuration of redo
 logs for one week, and the first one was for many months. 
  
 There was not any change on database objects and the
 database is small, the summing up real sizes of datafiles
 is 13G. 
  
 I think It not should be happennig, because the archive
 generation shouldn't be dependent on redolog size. But in
 this case yes (Could be an Oracle 9i bug?) .
  
 Regards 
  
 Mauricio Vélez
 
 
 Tanel Poder [EMAIL PROTECTED] wrote:
 Is your system overloaded e.g. there is a continuous queue
 of transactions waiting? In that case, with bigger
 redologs, full checkpoints happen less frequently,
 allowing database to work faster, thus generating more
 redo.
  
 But, othervise, the archive generation shouldn't be
 dependent on redolog size.
  
 How are you measuring your archive size, just counting
 number of files/entries from v$archived_log or summing up
 real sizes of archivelogs? (´these may differ
noticeably,
 especially when frequent manual logswitches occur or
 archive_lag_target is set).
  
 Tanel.
  
 - Original Message - 
 To: Multiple recipients of list ORACLE-L 
 Sent: Wednesday, January 07, 2004 10:49 PM
 
 
 
 Hello Everybody
 
  
 
 Im rewriting the question, 
 
  
 
 Some days ago the database I work on had 3 logfiles that
 sized 100M and the database was generating 4G of archive
 daily. 
 I changed the  size to 20M and the database began to
 generate 2G of archive daily, then I changed to 50M and It
 began to generate 3G of archive daily. 
  
 
 I think Its not logical that archive size change.
 
  
 
 The database I'm working on is oracle 9i and I'is on
 Windows NT. 
  
 
 Regards 
 
 Mauricio Vélez
 
  
 
 
 
 -
 Do you Yahoo!?
 Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
 
 -
 Do you Yahoo!?
 Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: ORA-3113 errors after switching W2K server to new network

2004-01-08 Thread Tim Onions

Paul

Sounds suspiciously like an issue we had when se set up our latest system -
something in the firewall or network set-up has a TCP time-out in it. TCP
(or the network or something I'm no network expert) drops the connection
after 60 minutes resulting in client and Oracle server no longer being able
to talk to each other (3113 etc). We get no logs either.

Sorry I can't actually help here but this error we see is nothing to do with
Oracle, Net8 etc and everything to do with the network/firewall. We have not
solved it yet but luckily our software is configurable and can
disconnect/reconnect every 30 minutes so does not fall fowl of the issue.

T¬
_
Tim Onions
Head of Oracle Development
Speech Machines (A MedQuist Company)
...the speech-to-data Application Service Provider
Tel: +44.1684.312364
http://www.speechmachines.com



-Original Message-
Sent: 08 January 2004 15:39
To: Multiple recipients of list ORACLE-L


Hi everybody,

one of our Win2K servers, running Oracle 8.1.7, has recently been
switched from the University's old network to our shiny new network. All
the DNS entries have been successfully switched, everybody can still
connect to the databases, and all appeared well. Until our users started
reporting ORA-3113 errors (end of file on communiation channel). In
every case, this happens after the user's connection has been inactive
for somewhat over an hour. There are no messages in the database alert
log, no trace files or dump files in the bdump, cdump or udump
locations, and no messages in the listener log or in the sqlnet log. But
it happens with alarming frequency, and only since the switch to the new
network. The network guy who switched the server to the new network says
the only change was to replace the server's previous network card with a
new gigabit ethernet card. In the absence of any kind of server-side
error messages or trace files, I'm more than somewhat stumped. None of
the possible explanations I've found in MetaLink seem to apply in this
case.

Has anyone any ideas? Not only possible solutions, but ideas for further
inquiries, or further questions would be welcome!

Best regards,

Paul Vincent
DBA
University of Central England
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Paul Vincent
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Onions
  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: Verisign and MetaLink

2004-01-07 Thread Tim Onions

the ntoe I read talked about Oracle Wallets and SSL within Java

_
Tim Onions
Head of Oracle Development
Speech Machines (A MedQuist Company)
...the speech-to-data Application Service Provider
Tel: +44.1684.312364
http://www.speechmachines.com



-Original Message-
Sent: 07 January 2004 15:34
To: Multiple recipients of list ORACLE-L


So I got two e-mails today from Oracle Support warning about Verisign Certs
expiring today and to check MetaLink (thanks for the advanced warning guys).
I've been trying for over an hour now and am getting no joy.  Anyone know
what the Verisign Certs are used for in Oracle products?  I don't think we
have any of those products, but...

TIA,
Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Onions
  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: table reorganizations

2004-01-07 Thread Tim Gorman

I guess some of the folks on the list are in a playful
mood today...

The need for a table reorganization depends on how it is
used.

The query you cite might be illuminating if the table in
question is mostly accessed by full table scans, as it
seems to identify tables with large gaps due to deletions.
 During an FTS, these gaps would still be traversed,
resulting in what might turn out to be excessive I/O
required to accomplish the task.

However, if the table in question is commonly accessed via
indexed lookups or scans, then reorganizing these tables to
close these gaps might well be a complete waste of time. 
The reason being that the table access by ROWID action
that is the last step of table access via indexes does not
scan emptied blocks.  It directly addresses populated blocks
in the table only.  Thus, accessing rows in a table that is
99.99% empty takes no longer than accessing rows in a table
that is 100% full, using this access method.  Of course, if
someone wants to throw clustering factor in, then that
assertion starts to get a little squishy, but the fact
remains that the effort expended in reorging the table
clearly does not provide anything near an adequate return
on investment.

So, the knowledge of how the table is accessed is clearly
part of the answer.  This puts the equation beyond the scope
of a simple query on the data dictionary, although I'm
pretty sure that the V$SEGMENT_STATISTICS view in Oracle9i
could provide some of the insight into the usage of the
table.

So, if the table in question is typically accessed via full
table scan, the query you cited is useful.  If the table in
question is rarely (if ever) accessed via full table scan
(or shouldn't be), then the query you cited should at least
be changed to indicate a much much much larger blkdiff
constant.  Indeed, table reorgs in such circumstances wouuld
help so rarely that they aren't really worth worrying about.

Hope this helps...

-Tim


 I'm surprised at these responses.  I'm asking what sql
 statement most people use to identify tables that need
 reorganization because of holes.
 
 We had an Oracle consultant here and he uses 
 
 Select table_name,
 blocks-((num_rows*avg_row_len/block_size)*(1+(pct_free/1
 00))) blkdiff From dba_tables
 Where blkdiff  100;
 
 To determine reorganization need.
 
 What sql statement is used by others?
 
 Jolene
 
 -Original Message-
 Sent: Wednesday, January 07, 2004 2:25 PM
 To: Multiple recipients of list ORACLE-L
 
 
 I usually recommend Gospel by Jonathan for its
 completeness and a wide range of subjects. The book you
 mentioned is great  for beginner as well. As for the
 number 42, I'll continue using  it until this Saturday
 (1/10/2004) when it will become 43. Inflation is not as
 big as you think. PS:
 ---
 I was born on 1/10/1961, and that makes January 10th so
 special. I don't have to work on that great day, mostly
 because it's Saturday. 
 
 On 01/07/2004 03:09:53 PM, Thater, William wrote:
  Mladen Gogala  scribbled on the wall in glitter crayon:
  
   Lemme guess: you just started on your new job as a
   DBA? You are  another person to which can only
 wholeheartedly recommend Jonathan's 
   book. As for your questions, the answer is 42.
  
  actually, if she's just starting out, i'd recommend
  Marlene, Rachel  and Jim's book first, then Jonathan's.
  
  and are you sure it's not 57 now due to inflation?
  
  --
  Bill Shrek Thater ORACLE DBA  
  I'm going to work my ticket if I can... -- Gilwell
  song [EMAIL PROTECTED]
 
 --
   --
  Perfection of means and confusion of ends seem to
 characterize our age. -
  Albert Einstein
  -- 
  Please see the official ORACLE-L FAQ:
  http://www.orafaq.net -- 
  Author: Thater, William
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051
  http://www.fatcity.com San Diego, California--
  Mailing list and web hosting services
 --
  --- To REMOVE yourself from this mailing list,
  send an E-Mail message to: [EMAIL PROTECTED] (note
  EXACT spelling of 'ListGuru') and in  the message BODY,
  include a line containing: UNSUB ORACLE-L (or the  name
 of mailing list you want to be removed from).  You may
  also send  the HELP command for other information (like
  subscribing). 
 
 --
 Mladen Gogala
 Oracle DBA
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Mladen Gogala
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name

Re: DBA tasks

2004-01-07 Thread Tim Gorman

James Koopmann is presenting two sessions at the Rocky Mtn Oracle Users
Group (RMOUG) Training Days on 11-12 Feb in Denver.

Sorry for the plug, but it's a cool article, he's giving two cool
presentations (see http://www.rmoug.org), and he's a cool person!  Oh yeah,
it'll be a cool conference too!

..especially for those going skiing/boarding after the conference...



on 1/6/04 6:34 PM, Jared Still at [EMAIL PROTECTED] wrote:

 
 What does a DBA do?
 
 What should a DBA do?
 
 Interesting short list, or maybe not so short.
 
 http://dbasupport.com/oracle/ora9i/resolutions.shtml
 
 
 
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: HOTSOS Conference

2004-01-06 Thread Johnston, Tim

I'll be there...  A get together would be great...

Tim 

-Original Message-
Sent: Monday, January 05, 2004 8:59 PM
To: Multiple recipients of list ORACLE-L



While perusing the HOTSOS site, I noticed that the deadline for the
discounted registration for the HOTSOS conferences ends after tomorrow. If
you're thinking of going, you may want to check it out.

Along those same lines, how many listers will be there?

We could get together on Tuesday evening for dinner/drinks if any are
interested.  

Possibly some of you with Dallas connection can recommend a suitable
location.

Jared




--
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: Johnston, Tim
  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: Job question

2004-01-04 Thread Tim Gorman

Sounds like you may have answered your own question -- a worthless stock
price for a telecom is not a good sign.

Constantly advertising for a position like a DBA doesn't necessarily mean
that they can't keep people.  Just as likely that they have not yet hired
their first DBA -- possibly using contractors who refuse to hire in?
Happens a lot...



on 1/4/04 10:54 AM, Don at [EMAIL PROTECTED] wrote:

 Metro One Telecommunications (Beaverton, Oregon) keeps advertising for an
 Oracle DBA.
 
 Any idea what is going on there that they can't seem to hang on to
 folks?  I see that their stock price has has dropped to almost worthless.
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: stress testing

2004-01-03 Thread Tim Gorman

Henry,

I use the attached shell script to gather and store VMSTAT information in a
custom table within the PERFSTAT schema (i.e. schema belonging to
STATSPACK).  Allows for some nice reporting over time, rather than
anecdotal here-and-there observations.  Should work OK on Solaris, HP, and
Linux.

Be aware:  the script expects to use the standard oraenv and dbhome
scripts to set up the Oracle environment variables, and expects a hidden
file in the $HOME directory of the owner's UNIX account for storing Oracle
passwords, and also has optional functionality to email/page in the event of
trouble.

Hope this helps...

-Tim

on 1/2/04 11:54 AM, Poras, Henry R. at [EMAIL PROTECTED] wrote:

 We are planning on running some stress tests on a PeopleSoft/Oracle/Solaris
 system starting next week (using LoadRunner). I have never gone through a
 formalized stress test before (most of my stress is brought about informally).
 So far I am planning to gather statspack information, and periodically get
 vmstat from the OS. Is there anything else that I should collect? Thanks for
 the
 help.
 
 Henry



sp_vmstat.sh
Description: Binary data

Re: 10g new features question for beta testers

2003-12-27 Thread Tim Gorman

The credit for that goes to Gary Dodge.  It has been his email signature for
better than 10 years...

Building tomorrow's legacy systems today - one crisis at a time

Gene Fosnight (a successfully retired Oracle consultant) had an email
signature that was, if anything, even better:

Look, listen, and learn, for an original mistake is as rare as an original
idea.



on 12/22/03 5:34 PM, Mogens Nørgaard at [EMAIL PROTECTED] wrote:

 Imagine the banner text: Miracle A/S. The Legacy Support of Tomorrow.
 Filling the Gap (jeans) like nobody else.
 
 Thanks to Tim Gorman for inspiration. I don't recall the text completely
 anymore, but he used to have this one about Building tomorrow's legacy
 systems - one crisis at a time. Or something to that effect.
 
 Mogens
 
 Pete Sharman wrote:
 
 But I thought this was the perfect opportunity for Miracle to fill any
 perceived gap in support?  :)
 
 Pete
 
 Controlling developers is like herding cats.
 
 Kevin Loney, Oracle DBA Handbook
 
 Oh no, it's not.  It's much harder than that!
 
 Bruce Pihlamae, long-term Oracle DBA
 
 
 -Original Message-
 Mogens Nørgaard
 Sent: Sunday, December 21, 2003 10:14 PM
 To: Multiple recipients of list ORACLE-L
 
 It's the Best of Breed versus One Vendor debate, and there are pros and
 cons galore.
 
 The perfect scenario, of course, is when they combine, so one vendor
 delivers the best of everything. That's what we have with Microsoft,
 isn't it? ;-) : Office stuff, OS, Database, ERP, CRM, video player, what
 
 have you...
 
 Then on the Support side of things, it's indeed good to be able to call
 One Vendor Only... if that vendor is good at Support. If he isn't, you
 might be better off if you have more than one option for calling.
 
 Mogens
 
 Pete Sharman wrote:
 
  
 
 Just a couple of comments on this which hopefully won't go down the
 Marketing track too far.  :)
 
 1.  I'm pretty sure Steve Adams agrees with you, since he co-presented
 on ASM at OracleWorld in San Fran.  Not sure if he monitors this group
 actively or not, but I believe the presentation he did is loaded with
 all the other OracleWorld 2003 presentations so you can see what he
 said.
 
 2.  One point which makes a lot of sense to me, and it happens in a
 variety of places in 10g such as ASM and the RAC clusterware.  If you
 have one vendor to raise an issue with (not that you'd need to do that
 with Oracle of course!), it's a lot easier to get an answer without the
 finger pointing that can go on between vendors.  Take the clusterware
 example - if you run into a problem running RAC on Sun with the Sun
 Cluster technology and Veritas owning the disk side, who you gonna

 
 call?
  
 
 GhostBusters, maybe!  But if you're running RAC on Sun with Oracle's
 clusterware and ASM, it's a lot easier to determine who to call.
 
 Pete
 
 Controlling developers is like herding cats.
 
 Kevin Loney, Oracle DBA Handbook
 
 Oh no, it's not.  It's much harder than that!
 
 Bruce Pihlamae, long-term Oracle DBA
 
 
 -Original Message-
 Connor McDonald
 Sent: Saturday, December 20, 2003 2:34 AM
 To: Multiple recipients of list ORACLE-L
 
 As with anything I suppose, if a single vendor can be
 in control of more of the stack between application
 and physical server structure then there is a greater
 opportunity for benefits.  For example, ASM offers the
 ability to add disks to a stripe without needing to
 redistribute(reload) the entire stripeset.
 
 A (bug-free) ASM product looks very very impressive to
 me.  Time will tell how close Oracle are to achieving
 it.
 
 hth
 connor
 
 --- [EMAIL PROTECTED] wrote:  no ASMs are
 considerably different. Its supposed to
 
 

 
 manage everything. You dont give it a file, you give
 it entire disks and oracle does everything. Sets up
 files, manages, I/O, everything.
 
 you only look at the tablespace level. you dont even
 install any software on it. If your on SAN, you dont
 install SAN software on it.
   
 
  
 
 From: Goulet, Dick [EMAIL PROTECTED]
 Date: 2003/12/19 Fri AM 09:14:27 EST
 To: Multiple recipients of list ORACLE-L
 
 

 
 [EMAIL PROTECTED]
   
 
  
 
 Subject: RE: 10g new features question for beta
 
 

 
 testers
   
 
  
 
 That is not exactly a new feature.  Oracle 9i has
 
 

 
 Oracle Managed Files where you give it a directory
 and then just build tablespaces.  The database picks
 the filenames for you.  Now mind you it does work,
 but I'll be damned if I use it in anything other
 than a development environment.  For some reason
 Oracle has never gotten over that DUMB SAME (Stripe
 And Mirror Everything) idea.  The concept is great
 in theory, but in practice it's absolutely abysmal
 at best.
   
 
  
 
 Dick Goulet
 Senior Oracle DBA
 Oracle Certified 8i DBA
 
 -Original Message-
 Sent: Friday, December 19, 2003 8:24 AM
 To: Multiple recipients of list ORACLE-L
 
 
 I saw a presentation from Oracle on 10g new

Re: standby database configuration in 9iR2 SE ?

2003-12-27 Thread Tim Gorman

JP, Juan, and Prem,

Prem is correct.  Standby is certainly a feature of SE, just not the
managed recovery and SQL*Net log shipping parts, which only come with
EE.  Essentially, Standby Database features in SE is just like Standby
Database features from 7.3 through 8.0.

I have some shell scripts for log shipping and log apply (executed via
cron) that I wrote for SE on Sun Solaris some years ago.  Attachments to
this list get stripped off, so email me offline if you'd like 'em.  No
warranty, no guarantees -- just a starting point...

Hope this helps...

-Tim


on 12/16/03 1:04 AM, Prem Khanna J at [EMAIL PROTECTED] wrote:

 No Juan  . SE also has the feature but Managed Recovery
 is not possible as in EE .
 
 A metalink doc says :
 quote
 Basic Standby Database  is a feature of SE .
 that is it Allows the DBA to manually clone a database,
 and to copy and to apply log files to the standby
 /quote
 
 anyway i'm looking for manual recovery which is possible in SE.
 
 Regards,
 Jp.
 
 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
 
 Standby is only for Enterprise Ed.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Little competition

Title: Re: Little competition



 I can cut 45 minutes off my load times by shrinking the data file?

And how much overhead gets added to DML statements as blocks madly shift on and off the freelists with each operation? Priorities, priorities, priorities...

If youre using 9i or above, the table COMPRESS feature might be a more effective mechanism?



on 12/11/03 5:44 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:

hopefully i wont sound like a complete idiot, but what is wrong with wanting to be able to handle your own pctfree and pctused. Ok oracle handles the next and initial extent sizes...(which causes fragmentation). 

I use transportable tablespaces and in order to increase the time it takes to copy these datafiles, I use pctused 99 and pctfree 1 in order to compact the tables. I can cut 45 minutes off my load times by shrinking the data file? 
 
 From: Richard Foote [EMAIL PROTECTED]
 Date: 2003/12/11 Thu AM 06:39:26 EST
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Subject: Little competition
 
 Little competition for you all :)
 
 It's a two part question:
 a.. What's wrong with the following piece of expert analysis ?
 b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ?
 
 
 Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance.
 
 SQL create table
 2 test_table
 3 (c1 number)
 4 tablespace
 5 asm_test
 6 storage
 7 ( pctfree 20 pctused 30 )
 8 ;
 
 ( pctfree 20 pctused 30 )
 *
 ERROR at line 7:
 ORA-02143: invalid STORAGE option
 
 However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings
 
 
 
 You've gotta love it !!
 
 Sorry no clues 
 
 Cheers ;)
 
 Richard
 
 

Little competition for you all :)
 
It's a two part question:
 What's wrong with the following piece of expert analysis ? 
 Which well know Oracle Guru published this (and continues to display it on his web-page) ?
 

Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre using automatic space management. This is a serious limitation because Oracle9i cant know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance.
 
SQL create table
2 test_table
3 (c1 number)
4 tablespace
5 asm_test
6 storage
7 ( pctfree 20 pctused 30 )
8 ;

( pctfree 20 pctused 30 )
*
ERROR at line 7:
ORA-02143: invalid STORAGE option

However, heres an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings
 


You've gotta love it !!
 
Sorry no clues 
 
Cheers ;)
 
Richard

Re: Code Conversion from MSSQL into Oracle

Title: Re: Code Conversion from MSSQL into Oracle



10 years ago or so, I wrote a 105 line script for the UNIX sed (a.k.a. stream editor) command to convert Teradata BTEQ scripts into Oracle SQL*Plus. Painful, yet thrilling, and it took only about a day of concentration with the OReilly Awk and Sed book at hand.

Ill bet you can write something (whether in sed or awk or Perl or Java) faster than it takes you find a TransactSQL-to-PL/SQL converter...? And if you dont know Perl (probably the best choice) yet, this exercise could be the opportunity to put a huge new skill into the old skillset...

Just a thought...



on 12/11/03 4:49 AM, VIVEK_SHARMA at [EMAIL PROTECTED] wrote:

 

Are there any TOOLs for converting Sample Code (like the following) from MSSQL into Oracle?

 

SAMPLE :-



DECLARE @entity_id char(32), @branch_id char(9)

 

DECLARE cur_temp_GEMT CURSOR FOR select

 branch_id,entity_id from GEMT where other_party_name='' and entity_type='D'

 

OPEN cur_temp_GEMT 

 

FETCH NEXT FROM cur_temp_GEMT INTO

 @branch_id,@entity_id

 

WHILE @@FETCH_STATUS = 0

 

BEGIN

 

 

UPDATE GEMT SET other_party_name=(SELECT name from GEAT where branch_id [EMAIL PROTECTED] and [EMAIL PROTECTED] and addr_type='1' and entity_type='D') where [EMAIL PROTECTED] and branch_id = @branch_id

 

FETCH NEXT FROM cur_temp_GEMT INTO

 @branch_id,

 @entity_id

 

 

END

 

CLOSE cur_temp_GEMT

DEALLOCATE cur_temp_GEMT

Re: Little competition

Title: Re: Little competition

Neither the PCTFREE and PCTUSED clauses go inside the STORAGE clause. They are independent of it. That is why the error was thrown, not because PCTFREE is invalid with ASSM...

Essentially, an erroneous interpretation of the error message.

If it was really going to prove his point, the CREATE syntax in the article should have read instead:

create table test_table (c1 number)
tablespace test_assm
pctfree 20 pctused 30;

It succeeds, by the way...

on 12/11/03 6:14 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:

#1. these instances are still on 8i. We are supposed to go to 9i, but its not my call

#2. its read only for the users. We do batch loads at night and I did not notice any slow down in the loads. I run statspack regularly. no problem. Just gotta do an alter table move periodically when we get too much row migration, but I can do that over the weekend.

Depends on your situation. There are cases for dense blocks
and there are cases where you dont want to do this.

again, what is so bad with what burleson said about the pctfree and pctused?

From: Tim Gorman [EMAIL PROTECTED]
Date: 2003/12/11 Thu AM 07:59:25 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Little competition

I can cut 45 minutes off my load times by shrinking the data file?

And how much overhead gets added to DML statements as blocks madly shift on
and off the freelists with each operation? Priorities, priorities,
priorities...

If youre using 9i or above, the table COMPRESS feature might be a more
effective mechanism?

on 12/11/03 5:44 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:

hopefully i wont sound like a complete idiot, but what is wrong with wanting
to be able to handle your own pctfree and pctused. Ok oracle handles the next
and initial extent sizes...(which causes fragmentation).

I use transportable tablespaces and in order to increase the time it takes to
copy these datafiles, I use pctused 99 and pctfree 1 in order to compact the
tables. I can cut 45 minutes off my load times by shrinking the data file?

From: Richard Foote [EMAIL PROTECTED]
Date: 2003/12/11 Thu AM 06:39:26 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Little competition

Little competition for you all :)

It's a two part question:
a.. What's wrong with the following piece of expert analysis ?
b.. Which well know Oracle Guru published this (and continues to
display it on his web-page) ?

Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if
you're using automatic space management. This is a serious limitation because
Oracle9i can't know in advance about the amount of VARCHAR expansion in a
table row, leading to excessive row chaining and poor access performance.

SQL create table
2 test_table
3 (c1 number)
4 tablespace
5 asm_test
6 storage
7 ( pctfree 20 pctused 30 )
8 ;

( pctfree 20 pctused 30 )
*
ERROR at line 7:
ORA-02143: invalid STORAGE option

However, here's an important point. While Oracle9i rejects the PCTFREE and
PCTUSED parameters with locally managed tablespaces with automatic space
management, it does allow you to enter invalid settings for NEXT and
FREELISTS settings

You've gotta love it !!

Sorry no clues

Cheers ;)

Richard

Little competition for you all :)

It's a two part question:
* What's wrong with the following piece of expert analysis ?
* Which well know Oracle Guru published this (and continues to display
it on his web-page) ?

Sadly, Oracle9i doesnt allow you to specify the value for PCTFREE if youre
using automatic space management. This is a serious limitation because
Oracle9i cant know in advance about the amount of VARCHAR expansion in a
table row, leading to excessive row chaining and poor access performance.

SQL create table
2 test_table
3 (c1 number)
4 tablespace
5 asm_test
6 storage
7 ( pctfree 20 pctused 30 )
8 ;

( pctfree 20 pctused 30 )
*
ERROR at line 7:
ORA-02143: invalid STORAGE option

However, heres an important point. While Oracle9i rejects the PCTFREE and
PCTUSED parameters with locally managed tablespaces with automatic space
management, it does allow you to enter invalid settings for NEXT and FREELISTS
settings

You've gotta love it !!

Sorry no clues

Cheers ;)

Richard

I can cut 45 minutes off my load times by shrinking the data file?

And how much overhead gets added to DML statements as blocks madly shift on and off the freelists with each operation? Priorities, priorities, priorities...

If youre using 9i or above, the table COMPRESS feature might be a more effective mechanism?

on 12/11/03 5:44 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:

hopefully i wont sound like a complete idiot

RE: SQL Area Reloads

OK, fair enough.

Something to consider:  the concept of reload probably
also includes the concept of initial/first load.  I don't
know whether this is the case for certain, but one way to
check might be to query the number of items in the V$SQLAREA
(i.e. select count(*) from v$sqlarea).  After all, each of
those entries had to be loaded (a.k.a. reloaded) at least
once, I think...



 Shared pool is free all the time.
 There are no DDLs executed, no analyze or no package
 compiles still there are SQL AREA reloads.
 The database is on 9i RAC. The reloads are appearing on
 RAC instances. 
 -Original Message-
 Sent: Wednesday, December 10, 2003 11:05 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Sure, it's free now.  But has that always been the case?
 
 
 on 12/10/03 3:14 PM, Ashish Sahasrabudhe at
 [EMAIL PROTECTED] wrote:
 
 
 
 The statspack report is showing 10,684 reload for SQL AREA
 with 2.2% misses. 
 
 ==
 == == 
 Library Cache Activity for DB: MAIN  Instance: MAIN 
 Snaps: 18089 -18090  -Pct Misses  should be very low 
 
 Get  PctPinPct
   Invali- 
 Namespace   Requests  Miss Requests Miss  
   Reloads dations 
 ---  -- -- --
 --  
 BODY  12,1860.0 12,1860.0 
 0 0 
 CLUSTER   950.0 100.0 
 0 0 
 INDEX4660.04610.0 
 0 0 
 SQL AREA  44,1420.1500,9872.2 
10,684 0 
 TABLE/PROCEDURE  208,4980.1584,2190.0 
 1 0 
 TRIGGER2,7460.0  2,7460.0 
 0 0 
 ==
 == == 
 
 The 250 MB of shared pool is free. Why would it cause
 reloads if there is so much 
 free space available? 
 
 Any help is appreciated. 
 
 Ashish 
 
 
 
 
 
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Windows clustering???

This is a small, static, but fiercely loyal group of VMS users for HP to
milk.  OpenVMS is money in the bank for HP, as it was for Compaq and DEC.
Even the bean-counters understand the insanity of shutting off a guaranteed,
endless, and highly-profitable revenue stream upon which they do not have to
expend any marketing or sales resources...

Officially, they currently promise OpenVMS support through 2012 or
thereabouts, I believe.

In Colorado Springs (at the DEC-then-Compaq-then-HP hosting center), there
is a VMS cluster hosting an Rdb-based application that has been continuously
available for the past 11-12 years.  Zero downtime for the application.
Absolutely stunning.



on 12/10/03 2:14 PM, Tanel Poder at [EMAIL PROTECTED] wrote:

 Maybe you all already know that, but HP is planning to support OpenVMS on
 their Itanium servers :)
 
 Tanel.
 
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Tuesday, December 09, 2003 10:14 PM
 
 
 I'm guessing they're not running Oracle on this VMS cluster.  I really
 liked
 the part about the most difficult part was explaining to managers why it
 was unnecessary to shut systems down, even during the physical
 relocation.
 
 http://www.theinquirer.net/?article=13002
 
 Imagine if DEC had any marketing...
 
 Rich
 
 Rich Jesse   System/Database Administrator
 [EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Jesse, Rich
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: char is going away?

VARCHAR2 started with Oracle7 v7.0.x (circa 1992-ish).  Prior to that,
Oracle6 had just VARCHAR with the exact same semantics/definition/behavior
as current VARCHAR2.

At the time, the explanation was that the ANSI SQL definition of VARCHAR
was slightly different from Oracle's existing implementation, so they
renamed theirs to VARCHAR2 in case in future they ever decided to offer the
ANSI definition as well...


on 12/10/03 2:39 PM, Tanel Poder at [EMAIL PROTECTED] wrote:

 I remember Oracle saying that char was going away - about 6 years ago.
 That's when they created varchar and varchar2.
 
 Is this a new rumor?
 
 VARCHAR3 type coming?
 
 Tanel.
 
 
 
 Tom Mercadante
 Oracle Certified Professional
 
 
 -Original Message-
 Sent: Wednesday, December 10, 2003 11:09 AM
 To: Multiple recipients of list ORACLE-L
 
 
 Hi all:
 
 Someone told me that Oracle is planning to retire char
 variable and therefore they need to be replaced by
 varchar2. Has anyone heard anything about it?
 
 thanks
 
 Gene
 
 __
 Do you Yahoo!?
 Free Pop-Up Blocker - Get it now
 http://companion.yahoo.com/
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Gene Gurevich
   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).
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: 24 x 7 x 365

Title: Re: 24 x 7 x 365



As I mentioned a few minutes ago in another thread, there is an application using Oracle Rdb on an HP OpenVMS cluster located at HP in Colorado Springs that has been up and available continuously for the past 11-12 years.


on 12/10/03 2:49 PM, Goulet, Dick at [EMAIL PROTECTED] wrote:

True 24x365 is just about impossible. No if, ands. or buts about it. Why is because of the number of factors outside your control that affect system availability. Sure your web sever and database are up 24x365, but your ISP has 1 hour down time each month for maintenance. OOPS!! from a customer point of view your NOT 24x365. Also that fiber optic cable running out of your building to the phone pole is available for some heavy equipment operator to slice through while working on the sewer system, OOPS!! So you can't have 24x365 no matter what. What you should do is try to adapt the changes you need into the downtime that's imposed on you. That's what we do  it works very nicely.
 
Besides being completely redundant is extremely expensive, like two of everything, including building, air-conditioning, fiber optic cables, etc.
 
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA 

-Original Message-
From: Tracy Rahmlow [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 10, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L
Subject: 24 x 7 x 365


Hello, 
Our company would like to know whether or not Oracle supports true 24x7x365 availability for an oltp database. We currently are using the 8.1.7 enterprise edition. Does an architecture exist whereby we can upgrade the database and/or operating system and not cause an outage? Will RAC solve this issue? Are there any other areas of concerns that I should be thinking about? For example, analyzing with the validate clause and its impacts on the transaction system. Thanks 

American Express made the following
annotations on 12/10/2003 09:41:15 AM
--
**

This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you.

**


==

Re: SQL Area Reloads

Title: Re: SQL Area Reloads



Sure, its free now. But has that always been the case?


on 12/10/03 3:14 PM, Ashish Sahasrabudhe at [EMAIL PROTECTED] wrote:

The statspack report is showing 10,684 reload for SQL AREA with 2.2% misses. 

== 
Library Cache Activity for DB: MAIN Instance: MAIN Snaps: 18089 -18090 
-Pct Misses should be very low 

 Get Pct Pin Pct Invali- 
Namespace Requests Miss Requests Miss Reloads dations 
---  -- -- -- --  
BODY 12,186 0.0 12,186 0.0 0 0 
CLUSTER 95 0.0 10 0.0 0 0 
INDEX 466 0.0 461 0.0 0 0 
SQL AREA 44,142 0.1 500,987 2.2 10,684 0 
TABLE/PROCEDURE 208,498 0.1 584,219 0.0 1 0 
TRIGGER 2,746 0.0 2,746 0.0 0 0 
== 

The 250 MB of shared pool is free. Why would it cause reloads if there is so much 
free space available? 

Any help is appreciated. 

Ashish

Re: Documenting databases

I apologize if someone already mentioned this (I've been busy so mostly
deleting traffic in order to keep from being overwhelmed!), but did anyone
mention the DBMS_METADATA package for this?

Looks pretty cool, mostly intended for generated XML output but also
generates DDL commands...



on 12/10/03 4:39 PM, [EMAIL PROTECTED] at
[EMAIL PROTECTED] wrote:

 
 
 
 
 Thanks for all the responses.  As I figured, everyone has a different
 method, but the common thread seems to be automation.  I'm not sure exactly
 what  approach I'm going to take yet, but I'll definitely try to automate
 it as much as possible...
 
 I'll add one of my own documentation tricks that might be useful for large
 sites.  I have a graphical diagram (created in Visio) that I keep up to
 date with our database servers.  Under each server is a list of databases
 running on it and other minor details (like Oracle release, versions of
 applications it supports, etc)..  Finally, I have lines drawn between the
 servers/databases documenting triggers that update remote databases,
 advanced replication/streams, automated export/imports, or other important
 interactions.  Especially if you have 50-100+ databases, it can be tough to
 remember where every database lives and what relationships exist between
 them.  It's also great to give to managers who love diagrams... :-)
 
 
 Thanks again,
 
 Alan
 
 
 Alan Aschenbrenner
 Oracle DBA
 IHS Group
 [EMAIL PROTECTED]
 
 
 
  
 Stephane
 Faroult To:   Multiple recipients of
 list ORACLE-L [EMAIL PROTECTED]
 [EMAIL PROTECTED]cc:
 orp.com Subject:  RE: Re: Documenting
 databases
 Sent by:
 [EMAIL PROTECTED]
 .com
  
  
 12/10/2003 06:29
 AM
 Please respond to
 ORACLE-L
  
  
 
 
 
 
 Alan,
 
 The IT departments of several sites, hitherto fairly independent, have
 all been brought under a single roof at one of my customers and as a result
 a lot of databases have fallen into the herd of databases we had to manage
 there.
 IMHO the key point to inventory is automation; if you don't automate, it
 will never stay up-to-date.
 First of all, get hold of some platform for scripting.
 VERY VERY SMALLI don't know perl,/VERY VERY SMALLMICROSCOPIC I don't
 even plan to learn it any soon/MICROSCOPIC and as I feel comfortable with
 ksh, sed, awk and the like I jumped on a Unix platform, but your choice may
 be different.
 The first challenge in our case was to build an inventory of databases
 (asking people is totally unreliable); I have used scripts from Tim Gorman
 which you will find on his site (http://www.evdbt.com) - from a security
 paper, which I have reworked to suit my case. The idea was to probe the
 network (fortunately all servers are supposed to follow a special address
 pattern) and check for listeners, and send the lsnrctl stat command. This
 helps you identify servers, listeners, and instances. A suitable schema was
 built into a database (Oracle, but see below) to store this; note that
 relationships are sometimes not very simple, since a same instance can be
 served by several listeners.
 Next step was to secure a foothold into each database to execute inventory
 queries (it has been a good opportunity to check security too).
 DBSNMP/DBSNMP is a good bet. Actually, we created a special MONITOR account
 on each database, with only the minimum rights required.
 Everyday a script runs, which checks V$DATABASE, V$INSTANCE, V$LICENCE,
 V$VERSION (the only place BTW when you find some indication about which OS
 you are running on), getting information and updating it if required.
 Storage is of course checked as well. Database links are collected too. We
 have a PHP application displaying all the information (with the refresh
 date), conveniently crossed (for instance, we list for each database the
 dblinks to the database as well as the dblinks from the database). We have
 some summary PDF reports (storage, databases per OS, per version, etc.)
 which are printed every week. We are also linking to a (static) inventory
 of applications.
 It's still work in progress. We have recently added a connection test
 every 15mn to check database availability (trying a non-existent user. If
 we don't get ORA-1917 we try to ping the server and tnsping the listener to
 pinpoint the reason for the problem - of course we skip the other databases
 on the server if we can't ping it) and compute some availability percentage
 figure. We also intend to collect some metrics at regular intervals to have
 an idea about the load.
 I have nothing against using Access to store the data; in fact, some of
 the ideas were borrowed from another customer where the repository

Re: Hide schema from other user.

Sure!  Create the following views while connected as schema A:

   create view dba_users
   as
   select * from sys.dba_users
   where username not in ('C','D');

   create view all_users
   as
   select * from sys.all_users
   where username not in ('C','D');

You might need to have SYS grant you explicit SELECT permissions on these
views -- usually they are granted through a role, which is insufficient for
creating compiled objects like views and procedures.

You might also have to create similar views named DBA/ALL_OBJECTS,
DBA/ALL_SEGMENTS, DBA/ALL_TABLES, etc in order to make the deception
complete...



on 12/10/03 7:49 PM, Kean Jacinta at [EMAIL PROTECTED] wrote:

 Hello : All
 
 I have a scenario here. I have a database name :
 CYBER.
 I am using oem to manage this dbase.In this database ,
 i have created 4 schema . which is schema A, schema B,
 schema C and schema D.
 
 When i login into the dbase using schema A user id
 throught oem , i can see all other 3 schema B,C and D.
 I need the system to only show schema A and B while
 schema C and D will be hidden .
 
 Can anyone tell me how can i achieved this ? or maybe
 this can't be done. I am not sure. pls guide me .
 
 Thank in advanced
 
 regards
 JKean
 
 
 
 
 
 __
 Do you Yahoo!?
 New Yahoo! Photos - easier uploading and sharing.
 http://photos.yahoo.com/

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: RAC and Forms6i

2003-12-01 Thread Tim Gorman

I've used Apps 11.5.8 on RAC, so I know the 8.0.6 stack will connect just
fine to 9.2.0, RAC or no RAC.

For your debugging, you are going to have to work your way into it, starting
from the most basic level.  Start from tnsping then sqlplus and work
your way up to the Forms and stuff, not the other way around.

Verify TNS listener connectivity via v806 version of tnsping first.  If
that doesn't work, then you have your problem at its most basic.  Next,
verify database connectivity via v806 version of sqlplus.  If that doesn't
work, then again you have your problem at its most basic.

With errors like 12203 (i.e. no listener found), you should be able to
debug from the tnsping and sqlplus level.  If those work and your forms
continue to get these errors, and since these tools and the forms are
supposed to use the same SQL*Net stack, then this would best be explained by
a forms configuration problem.



on 12/1/03 4:09 AM, Bruno Vanters at [EMAIL PROTECTED] wrote:

 Actually I am having a problem connecting from Forms or even from
 sqlplus 8.0.6 to RAC. Connection to RAC database or to single instance
 from RAC ends with generic windows error. Oracle trace shows succesful
 connection, folloved by errors 12560 and 12203. Oracle 9.2 clients are
 working with no problems.
 Search in Metalink revealed The Certification of Database 9.2 with
 Forms 6i is incorrect at the
 concept level since the RSFs on which Forms/Reports are based cannot
 be made to work with the Database 9.2 components. so I got confused...
 Could you offer some advice for solving this problem?
 
 K Gopalakrishnan wrote:
 
 Hi,
 
 RAC is just a database option. If RDBMS 9.2 is certified against a
 product means, 9.2 RAC is also certifid on that product. But all the
 RAC features may not work with the certified option.. (Is it too
 confusing?!(
 
 Okay.. The TAF feature in RAC (okay... it is not a RAC feature!!) will
 not work with forms.
 
 Is this what you are looking or something else?
 
 
 Regards,
 Gopal
 
 --- Bruno Vanters [EMAIL PROTECTED] wrote:
 
 Hi all,
 
 Oracle Forms  Reports patch 15 is certified with Oracle 9.2
 (according 
 to Metalink).
 Does that mean that Forms are certified against RAC too?
 Can anyone confirm that, having/knowing any working RAC9.2+Forms6i
 environments?
 
 Thanks,
 Bruno Vanters
 Junior Oracle DBA
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Bruno Vanters
  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).
 
 
 
 =
 Have a nice day !!
 
 Best Regards,
 K Gopalakrishnan,
 Bangalore, INDIA.

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: OS Level Defrag

2003-11-27 Thread Tim Gorman

Title: Re: OS Level Defrag

Ask him/her to demonstrate their point empirically and not speculate or cite hearsay. After all, they should not have any difficulty putting together a simple test case and then demonstrating how de-fragmentation aids performance.

Databases simply do not manipulate files very much, which is one reason why logical volumes (a.k.a. raw devices) are a viable option for databases. Databases do not add and drop files frequently. They do not grow often and rarely shrink. For the most part, database files are added only occasionally, rarely if ever removed, are large in size and are few in number. So, any test case the sysadmin puts together should emulate this behavior.

on 11/27/03 6:59 PM, Sujatha Madan at [EMAIL PROTECTED] wrote:

Hi,

Does anyone here do an O/S level defrag of their Oracle filesystems???

Background: (Tru64/8.1.7.4)

Sysadmin here were adamant that the Oracle domains were running out of extents and were highly fragmented (O/S level). DBA was adamant that the Oracle filesystems should not be defragmented. I lost the battle and the sysadmins are defragging the domains.

I now have a corruption on a table partition with 100 million plus rows on a 50G datafile. I am wondering if the defrag has caused this corruption.

The only way I can think of finding out is:

Finding the approx date of the corruption using the query
SELECT ROWID, LAST_COLUMN_OF_TABLE from TABLE_NAME(PARTITION);
(which will do the full tablescan row by row).

And then finding when the defrag utility was hitting the particular datafile that is corrupted.

But this reasoning is flawed ...

Does anyone have another method of trying to pinpoint if the O/S defrag caused the corruption

Regards,

Sujatha

Re: Re[2]: Deleting partitioned data

 |
 AN 
 --
 
 AN |   0 | SELECT STATEMENT   | | 1 |21 | 2 |
 |   |
 AN |   1 |  PARTITION RANGE SINGLE| |   |   |   |
 KEY |   KEY |
 AN |*  2 |   TABLE ACCESS FULL| SALES   | 1 |21 | 2 |
 KEY |   KEY |
 AN 
 --
 
 
 AN Predicate Information (identified by operation id):
 AN ---
 
 AN2 - filter(SALES.SALES_DT='14-nov-03')
 
 AN Note: cpu costing is off
 
 AN 15 rows selected.
 
 AN Well, what happened here? The optimizer couldn't decide the partition at
 the parse time, hence it shows KEY as the values of partition start and stop
 keys. This occurred since we specified where
 AN sales_date = '14-nov-03' as opposed to where sales_dt =
 to_date('14-nov-2003','dd-mon-'). The former is not in the same format as
 the partition definition, i.e.
 AN (to_date('14-nov-2003','dd-mon-')), the latter is; hence the
 optimizer made a smart choice. When the patterns mentioned in the query and
 the partition definition don't match, the optimizer
 AN can't decide at parse time which partition to use; it uses a KEY iterator.
 
 AN I am not sure if the facility provided by Oracle to query a partition
 directly is due to the above situation, but it helps there, nevertheless.
 
 AN HTH.
 
 AN Arup Nanda
 
 
 
 AN - Original Message -
 AN To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 AN Sent: Thursday, November 13, 2003 7:34 PM
 
 
 I'd like to ask a question. Consider the two statements
 below:
 
 DELETE
 FROM county PARTITION (michigan)
 WHERE county_name = 'Alger';
 
 DELETE
 FROM county
 WHERE county_name = 'Alger'
   AND state = 'MI';
 
 Is there ever a case where the first option is preferable?
 Is there ever a case where Oracle wouldn't be able to
 isolate the partition of interest simply by evaluating the
 conditions in the WHERE clause? There must be, else why
 would Oracle provide the syntax shown in the first
 statement? However, I'm having difficulty coming up with a
 good example of when that syntax makes sense. Can someone
 help me out here?
 
 Best regards,
 
 Jonathan Gennick --- Brighten the corner where you are
 http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
 
 Join the Oracle-article list and receive one
 article on Oracle technologies per month by
 email. To join, visit
 http://four.pairlist.net/mailman/listinfo/oracle-article,
 or send email to [EMAIL PROTECTED] and
 include the word subscribe in either the subject or body.
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Jonathan Gennick
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Bad performance because of wrong characterset?

Pure bvllsh1t.

Slap a sql trace on the process and find out what's actually going on.  Stop
the madness before some damager makes a stupid decision...

In the end, they might be right.  But isn't it better to be certain?



on 11/17/03 3:49 AM, Carel-Jan Engel at [EMAIL PROTECTED] wrote:

 Hi List,
 
 One of my customers gets HP Servicedesk 4.5 implemented. The database that
 has been created by on of the DBA's for the application is Oracle 8.1.7.4,
 on AIX 4.3.
 
 The database has characterset UTF8, which is the standard here.
 
 Now ServiceDesk has bad performance. HP claims that the characterset is
 wrong, and a new database has to be created with character set
 WE8ISO8859P15.
 
 I've never heard of bad performance due to differences in character sets,
 probably because of my lack of experience. Anyone familiar with this
 issue?
 
 TIA, Carel-Jan
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Oracle Version 8 as read only

You can't.

Even if you were to set all possible tablespaces to READ ONLY, you'll find
that it doesn't prevent folks from dropping objects.

All you can do is restrict people to schemas/accounts which do not own any
objects and do not have permissions to create any.  That way, you can
restrict them to CREATE SESSION, ALTER SESSION, and SELECT permissions only.


on 11/26/03 4:44 PM, Browett, Darren at [EMAIL PROTECTED] wrote:

 We are in the process of upgrading our peoplesoft app, using an oracle
 database of
 8.0.5 to 9i.  During the upgrade we still need access to the old 8.0.5
 database as readonly.
 
 I have read all the doc's but it appears I cannot open the database as
 read only.  Looks like
 I could if the version was 8i.
 
 Does anybody have any pointers on how I could accomplish this.
 
 Thanks
 
 Darren.
 
 --
 Darren Browett P.Eng This
 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: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Oracle Version 8 as read only

Consider also using ALTER USER xxx ACCOUNT LOCK instead of changing the
passwords...


on 11/26/03 6:49 PM, Browett, Darren at [EMAIL PROTECTED] wrote:

 Looks like that is what we will be doing, basically creating
 a generic user with read-only privileges.  All the current
 users will have their password changed so they cannot access the
 system.
 
 Darren
 
 -Original Message-
 Sent: Wednesday, November 26, 2003 4:04 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Darren,
 Couldn't you just open the database with a user that has all the
 necessary privileges revoked?
 
 -Original Message-
 Browett, Darren
 Sent: Wednesday, November 26, 2003 3:44 PM
 To: Multiple recipients of list ORACLE-L
 
 We are in the process of upgrading our peoplesoft app, using an oracle
 database of
 8.0.5 to 9i.  During the upgrade we still need access to the old 8.0.5
 database as readonly.
 
 I have read all the doc's but it appears I cannot open the database as
 read only.  Looks like
 I could if the version was 8i.
 
 Does anybody have any pointers on how I could accomplish this.
 
 Thanks
 
 Darren.
 
 --
 Darren Browett P.Eng This
 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: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: IOUG 2004

2003-11-25 Thread Tim Gorman

Good points, Tanel.  More to the point, forget about
formulating a backup strategy.  Focus on a recovery
strategy.


 RE: RE: Development vs. Production DBAIf I was doing a
 introductory administration session, I'd emphasize the
 right way of thinking the most. That is 
 1) Always make sure that you know what you're doing and
 what are the potential consequences of your doings 2) Do
 not do extensive maintenance tasks during peak hours (yes,
 for some people it is no problem to take down the database
 server during the most active usage period) 3) Every
 security hole will be used eventually 4) A backup strategy
 is only as good as it's corresponding recovery strategy 
 :)
 
 Btw, I'll be presenting 1-2 sessions there too, so see you
 there. 
 Tanel.
 
   - Original Message - 
   From: Boivin, Patrice J 
   To: Multiple recipients of list ORACLE-L 
   Sent: Tuesday, November 25, 2003 3:19 PM
   Subject: IOUG 2004
 
 
   I was informed last night that they accepted my seminar
 proposal, I will be doing an introductory session on how
 to administer Oracle databases. 
   Hope to see many of you in Toronto next April.
 
   In the meantime if there are questions you would like me
 to cover, send them on -- I will only have an hour but the
 more relevant the presentation can be, the better. 
   Patrice.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: 'internal' role and 9i

2003-11-18 Thread Tim Gorman

Barry,

Why make life difficult?  It's just a role, not a data
object referenced by applications (hopefully).  Change it's
name to something that is not a reserved word and move on.
 There is a list of reserved words in the SQL Language
reference.

Hope this helps...

-Tim


 Hello all,
 
 I'm attempting to import into 9.2.0.1.0 from 7.3.4 and I'm
 getting loads of the same error:
 
 IMP-00017: following statement failed with ORACLE error
 9275:
  GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL
 IMP-3: ORACLE error 9275 encountered
 ORA-09275: CONNECT INTERNAL is not a valid DBA connection
 
 I'm aware that connect internal does not exist in 9i, but
 'internal' is a role.
 
 So as a test I dropped the role, recreated it and then
 manually tried to grant it something - The same error
 occurred: 
 SQL select * from dba_roles where role like 'INTER%';
 
 ROLE   PASSWORD
 -- 
 INTERNAL   NO
 
 SQL
 SQL drop role internal;
 
 Role dropped.
 
 SQL create role internal;
 
 Role created.
 
 SQL GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL;
 GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL
 *
 ERROR at line 1:
 ORA-09275: CONNECT INTERNAL is not a valid DBA connection
 
 SQL
 
 This doesn't make any sense to me.  Can anybody help to
 shed any light on this??
 
 TIA for any response, they're much appreciated.
 
 Cheers,
 Barry.
 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Barry Deevey
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: 'internal' role and 9i

2003-11-18 Thread Tim Gorman

Oops!  blush  Thanks for the correction, Yong!


 Tim,
 
 I checked v$reserved_words. At least in 9.2.0.1, INTERNAL
 is not in there. Oracle should address this issue.
 
 When I trace the SQL GRANT SELECT ON SOMETABLE TO
 INTERNAL, it stops in parsing.
 
 Nonetheless, it's confusing to say the least to create a
 role called internal. 
 Yong Huang
 
 --- Tim Gorman [EMAIL PROTECTED] wrote:
  Barry,
  
  Why make life difficult?  It's just a role, not a data
  object referenced by applications (hopefully).  Change
  it's name to something that is not a reserved word and
   move on. There is a list of reserved words in the SQL
  Language reference.
  
  Hope this helps...
  
  -Tim
  
  
   Hello all,
   
   I'm attempting to import into 9.2.0.1.0 from 7.3.4 and
   I'm getting loads of the same error:
   
   IMP-00017: following statement failed with ORACLE
   error 9275:
GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL
   IMP-3: ORACLE error 9275 encountered
   ORA-09275: CONNECT INTERNAL is not a valid DBA
   connection 
   I'm aware that connect internal does not exist in 9i,
   but 'internal' is a role.
   
   So as a test I dropped the role, recreated it and then
   manually tried to grant it something - The same error
   occurred: 
   SQL select * from dba_roles where role like 'INTER%';
   
   ROLE   PASSWORD
   -- 
   INTERNAL   NO
   
   SQL
   SQL drop role internal;
   
   Role dropped.
   
   SQL create role internal;
   
   Role created.
   
   SQL GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL;
   GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL
   *
   ERROR at line 1:
   ORA-09275: CONNECT INTERNAL is not a valid DBA
   connection 
   SQL
   
   This doesn't make any sense to me.  Can anybody help
   to shed any light on this??
   
   TIA for any response, they're much appreciated.
   
   Cheers,
   Barry
 
 __
 Do you Yahoo!?
 Protect your identity with Yahoo! Mail AddressGuard
 http://antispam.yahoo.com/whatsnewfree
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Yong Huang
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Looking for help.

2003-11-12 Thread Johnston, Tim

Or...  Create a stored procedure that truncates the table...  Grant execute
on the procedure to the user...  The user executes the procedure and then
calls sqlldr...

Tim

-Original Message-
Sent: Wednesday, November 12, 2003 12:45 PM
To: Multiple recipients of list ORACLE-L

yeah but...

if you attempt (as I do) to isolate the schema owner from the users which
have select/insert/update/delete privileges, TRUNCATE won't work unless you
have granted DROP ANY TABLE (I *really* hate that that is
required) to the account which does the actual sql load. So I use REPLACE,
because then I only have to grant the delete priv on that table.


--- Yechiel Adar [EMAIL PROTECTED] wrote:
 There are two options to replace all data in the table: REPLACE and 
 TRUNCATE which are equivalent to truncate and delete sql statements. 
 If you have staging tables without RI or triggers then use truncate. 
 Using delete just takes a lot longer and use a lot more resources.
 
 We use TRUNCATE almost exclusively.
 
 Yechiel Adar
 Mehish
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Wednesday, November 12, 2003 3:44 PM
 
 
  Hi
 
  We do something similiar, but instead of deleting the tables
 beforehand, I
  just use the SQL*LOADER REPLACE option. No such problems as
 described in
 the
  original eMail occured so far. The platform is Oracle 9.2.0.3 on
 Win3k.
 
  Regards,
  Stefan
 
  -Ursprüngliche Nachricht-
  Von: Jonathan Gennick [mailto:[EMAIL PROTECTED]
  Gesendet: Mittwoch, 12. November 2003 14:34
  An: Multiple recipients of list ORACLE-L
  Betreff: Fwd: Looking for help.
 
 
  I don't usually forward my reader email to the list, but the 
  question below strikes me as rather interesting. In this case, 
  SQL*Loader appears to be causing all SQL statements that refer to 
  the table being loaded to be invalidated. Is this normal behavior? 
  Does anyone know why it might be the case?
 
  --
  Best regards,
 
  Jonathan Gennick --- Brighten the corner where you are 
  http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
 
  Join the Oracle-article list and receive one article on Oracle 
  technologies per month by email. To join, visit 
  http://four.pairlist.net/mailman/listinfo/oracle-article,
  or send email to [EMAIL PROTECTED] and include the 
  word subscribe in either the subject or body.
 
  Wednesday, November 12, 2003, 1:07:41 AM, [EMAIL PROTECTED] 
  ([EMAIL PROTECTED]) wrote:
  Hi Jonathan,
 
  I was unable to find the answers from your book SQL*Loader: The
 Definitive
  Guide and the web. I am running out of sources. I hope you can
 help me
 with
  the following questions.
 
  We are using Oracle 9i sqlldr, direct path to load data from
 external
 files
  into
  staging tables. After data is loaded, we invoked stored procedures
 to
  transform data and move them to the target tables. The steps are:
  1. delete all entries from 20 staging tables 2. invoke sqlldr 
  userid=dbimpl/dbimpl control=controlFile
 direct=true
 to
  load data to all 20 staging  tables
  3. invoke stored procedures to transform data from the staging
 tables to
 the
  final tables. Currently these stored procedures are standalone.
  4. invoke stored procedures to remove out-of-date entries from the
 final
  tables.
 
  I monitor invalidations column in v$sqlarea. Every time after sqlldr 
  is invoked for data loading (step 2), all the sql statements that 
  reference the staging tables are invalidated, including delete from 
  stageing_table sql statement. I setup a test and used a java 
  program to loop steps 1-4 every ~2 minutes. There were no other 
  activities in the database except data loading and transformation.
  After a couple days, I got the following error: ORA-04031:
  unable to allocate 4212 bytes of shared memory (shared 
  pool,unknown object,sga heap(1,0),stat array mem)
 
  The questions are:
  1. Do we need to delete entries in the staging table prior to
 loading.
 Will
  sqlldr remove the entires in the staging table first prior to
 loading?
  2. There are no changes in the stored procedures, how / why sqlldr
 would
  invalidate the sql statement in the stored procedures?
  3. The error ORA-04031 in this case, is it due to shared memory 
  fragmentation? I suspect that the culprint is invalidations. How do 
  invalidations cause shared memory fragmentation?
 
  I would appreciate if you can send me some pointers or suggestions.
 
  Thanks,
  KamYee
 
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Jonathan Gennick
INET: [EMAIL PROTECTED]
 
  Fat City Network Services-- 858-538-5051 http://www.fatcity.com
  San Diego, California-- Mailing list and web hosting
 services
 
 -
  To REMOVE yourself from this mailing list, send an E-Mail message
  to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
  the message BODY

Re: Multi-threaded server - will it help in this case

2003-11-12 Thread Tim Gorman

Title: Re: Multi-threaded server - will it help in this case



No, thanks for helping bust the myth. Im always so surprised by the gap between what I thought I said and what I apparently communicated... :-)

Still, it is an interesting distinction. A separate TNS listener certainly isnt required, but one of the two major scenarios that frequently call for MTS/SS (extremely frequent connect requests) might also benefit from multiple TNS Listeners or even listener load-balancing. I guess that is one reason that server-side listener load-balancing involves MTS/SS in the first place. Imagine that!

Thanks again!



on 11/11/03 11:59 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:


Thanks John, this is the way I have done it in the past. 

Tim, thanks for your clarification. Thought that was more or less what 
you meant, just wanted didn't want to see any myths started stating 
that MTS requires its own listener. ;) 

Jared 




John Kanagaraj [EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED]  11/11/2003 08:49 AM 
 Please respond to ORACLE-L 
 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 
 cc: 
Subject: RE: Multi-threaded server - will it help in this case 


Jared,

I don't think that is what Tim meant. You can use something akin to the
following:

For an MTS connection/client:

MYDB_MTS.MYCOMPANY.COM = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=MYHOST.MYCOMPANY.COM)(PORT=7505))(CONNECT_DATA=(SID=MYSID)))

For a dedicated connection/client:

MYDB_DEDICATED.MYCOMPANY.COM = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=MYHOST.MYCOMPANY.COM)(PORT=7505))(CONNECT_DATA=(SID=MYSID)(SERVER=DEDI
CATED)))

The only difference is in the TNS handles and the entry they point to which
differs in content. The SERVER=DEDICATED will bypass the MTS configured
default connection.

You can do this via ONAMES too (and I know you use one!) - see
Note:1036577.6. Btw, I am currently in the UK helping with a Name Server
rollout..

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

Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-Original Message-
From: Jared Still [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 11, 2003 7:29 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Multi-threaded server - will it help in this case


Tim,

This bit:

 accomodate this application. Please be aware that you can
 mix dedicated and MTS by setting up different TNS names on
 different ports for each, so it is not an all-or-nothing

seems to imply that MTS and Dedicated will each require their
own listener ( different ports). Been awhile since I messed 
with MTS, but I don't recall that as being necessary.

Is that what you meant?

Jared



On Tue, 2003-11-11 at 07:04, Tim Gorman wrote:
 Peter,
 
 MTS (or SS in 9i onwards) is an excellent choice to
 accomodate this application. Please be aware that you can
 mix dedicated and MTS by setting up different TNS names on
 different ports for each, so it is not an all-or-nothing
 situation. Most connections to the database outside of this
 CAE app will likely be better served with dedicated
 connections, so just dole out TNS names accordingly.
 
 Also, please be sure to estimate the size of your UGA by
 tracking values (i.e. name like '%uga%') in V$SESSTAT at
 peak periods then sizing the Large Pool to accomodate,
 before you enable MTS. Unless you're really constrained for
 memory, don't be shy about this; double the highest value
 you sum from V$SESSSTAT to be safe. After enabling MTS,
 monitor the value of free memory where POOL = 'large pool'
 in V$SGASTAT. If you've oversized, you can start backing
 down on LARGE_POOL_SIZE gently, if you need the memory
 elsewhere...
 
 Hope this helps...
 
 -Tim
 
  Environment: AIX 4.3
  Oracle 8.1.7
  
  The application is a CAE tool which stores metadata for
  a hierarchy of 3D engineering design models.
  When a user opens a model at a given level in the design,
  the application retrieves data about that model and all of
  the models below it in the design try. This often 
  involves as many as 100 or more models. 
  Unfortunately, the way the application is written, it
  opens a new connection to the database for each model. 
  Thus, in the process of retrieving
  metadata, it may open and close as many as 100 connections
  to the database. Obviously, this causes some performance
  problems, especially for remote users. The number of
  users when the system goes fully into production
  is going to be in the low 100's.
  
  The vendor is not interested in changing the way the
  software works. 
  Will use of the mult-threaded server improve performance
  in this situation, for
  example, by eliminating the overhead of starting a
  dedicated server for each connection?
  
  Thanks,
  Peter

Re: Multi-threaded server - will it help in this case

Peter,

MTS (or SS in 9i onwards) is an excellent choice to
accomodate this application.  Please be aware that you can
mix dedicated and MTS by setting up different TNS names on
different ports for each, so it is not an all-or-nothing
situation.  Most connections to the database outside of this
CAE app will likely be better served with dedicated
connections, so just dole out TNS names accordingly.

Also, please be sure to estimate the size of your UGA by
tracking values (i.e. name like '%uga%') in V$SESSTAT at
peak periods then sizing the Large Pool to accomodate,
before you enable MTS.  Unless you're really constrained for
memory, don't be shy about this;  double the highest value
you sum from V$SESSSTAT to be safe.  After enabling MTS,
monitor the value of free memory where POOL = 'large pool'
in V$SGASTAT.  If you've oversized, you can start backing
down on LARGE_POOL_SIZE gently, if you need the memory
elsewhere...

Hope this helps...

-Tim

 Environment:  AIX 4.3
 Oracle 8.1.7
 
 The application is a CAE tool which stores metadata for
 a hierarchy of 3D engineering design models.
 When a user opens a model at a given level in the design,
 the application retrieves data about that model and all of
 the models below it in the design try.  This often
 involves as many as 100 or more models. 
 Unfortunately, the way the application is written, it
 opens a new connection to the database for each model. 
 Thus, in the process of retrieving
 metadata, it may open and close as many as 100 connections
 to the database. Obviously, this causes some performance
 problems, especially for  remote users.  The number of
 users when the system goes fully into production
 is going to be in the low 100's.
 
 The vendor is not interested in changing the way the
 software works. 
 Will use of the mult-threaded server improve performance
 in this situation, for
 example, by eliminating the overhead of starting a
 dedicated server for each connection?
 
 Thanks,
 Peter Schauss
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: for years I have been using korn shell

PDKSH is also needed for Mac OS X, available on
http://www.osxgnu.org/software;...


 Bash is a very big shell which I typically don't need. I
 use korn shell which is available as pdksh package (see
 rpmfind.net). On 11/10/2003 04:54:25 PM, Jesse, Rich
  wrote: For Linux, I use bash.  For Unix (HP/Sun)
  accounts, I use Korn, where bash typically isn't
 available.  I like either, but am tending to like bash
  more for the non-vi command line editing that uses
  cursor keys (I've been told that this is set -o emacs
  in Korn, but it shore don't work like that on Korn-88).
  
  For one 3rd party install, I use csh because that's what
  they require.  And because of they way they have it
  setup, if I'm in Korn and execute their setup, it won't
  work.  I have to change to csh first, then execute their
  setup.  Just poorly written, IMHO. 
  My $.02,
  Rich
  
  Rich Jesse   System/Database
  Administrator [EMAIL PROTECTED] 
  Quad/Tech Inc, Sussex, WI USA 
   -Original Message-
   From: [EMAIL PROTECTED]
   [mailto:[EMAIL PROTECTED]
   Sent: Monday, November 10, 2003 3:09 PM
   To: Multiple recipients of list ORACLE-L
   Subject: RE: for years I have been using korn shell
   
   
   I have been working with Oracle on Unix - various
   platforms  since Version 6.X.
   For the first time a vendor has sent us an install
   that  installs under the C-shell.
   Up until this point I have always worked on and
   installed  under the korn shell.
   
   This introduces a different shell environment in our
   current  environment.  I find this really annoying. 
   However, I cannot  find anything that stipulates which
   shell environment to use. 
   -What is everyone else using out there?
   -Do you think I should make an issue of this one?
   
   
   Thanks,
   Paula
  -- 
  Please see the official ORACLE-L FAQ:
  http://www.orafaq.net -- 
  Author: Jesse, Rich
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051
  http://www.fatcity.com San Diego, California--
  Mailing list and web hosting services
 --
  --- To REMOVE yourself from this mailing list,
  send an E-Mail message to: [EMAIL PROTECTED] (note
  EXACT spelling of 'ListGuru') and in the message BODY,
  include a line containing: UNSUB ORACLE-L (or the name
  of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like
  subscribing). 
 
 Mladen Gogala
 Oracle DBA
 
 
 
 Note:
 This message is for the named person's use only.  It may
 contain confidential, proprietary or legally privileged
 information.  No confidentiality or privilege is waived or
 lost by any mistransmission.  If you receive this message
 in error, please immediately delete it and all copies of
 it from your system, destroy any hard copies of it and
 notify the sender.  You must not, directly or indirectly,
 use, disclose, distribute, print, or copy any part of this
 message if you are not the intended recipient. Wang
 Trading LLC and any of its subsidiaries each reserve the
 right to monitor all e-mail communications through its
 networks. Any views expressed in this message are those of
 the individual sender, except where the message states
 otherwise and the sender is authorized to state them to be
 the views of any such entity. 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Mladen Gogala
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Multi-threaded server - will it help in this case

Nope.  They can use the same listener and I'm pretty sure
they can even use the same port, but if you did the latter,
you'd just want to differentiate in the TNSNAMES definition
with the (SERVER=SHARED) or (SERVER=DEDICATED) clause. 
However, I haven't done this last thing lately and as I
write, I'm not completely certain that it works and I'm not
interested enough to test it out...  :-)

However, I _was_ implying that it would be _easier_ to
separate the different traffic to different ports, just to
be a little more certain that you are getting what you are
looking for when you connect.  You can always check after
the fact using:

   select server from v$session where audsid =
userenv('SESSIONID')

Of course, if your reason for using MTS involves extremely
frequent/rapid connection attempts (that cannot be corrected
by fixing the application), then dedicating a separate TNS
Listener to that traffic might be the better part of
discretion.  Old CGI-style web applications, where each
click spawned a new shell process with a new connection to
the database, are a prime example.  Switching from CGI to
modern app-servers with pooled servlet connections fixes
that problem.  Weirdo CAE apps that treat database
connections like cursors are another problem... :-)


 Tim,
 
 This bit:
 
  accomodate this application.  Please be aware that you
  can mix dedicated and MTS by setting up different TNS
  names on different ports for each, so it is not an
 all-or-nothing 
 seems to imply that MTS and Dedicated will each require
 their own listener ( different ports).  Been awhile since
 I messed  with MTS, but I don't recall that as being
 necessary. 
 Is that what you meant?
 
 Jared
 
 
 
 On Tue, 2003-11-11 at 07:04, Tim Gorman wrote:
  Peter,
  
  MTS (or SS in 9i onwards) is an excellent choice to
  accomodate this application.  Please be aware that you
  can mix dedicated and MTS by setting up different TNS
  names on different ports for each, so it is not an
  all-or-nothing situation.  Most connections to the
  database outside of this CAE app will likely be better
  served with dedicated connections, so just dole out TNS
  names accordingly. 
  Also, please be sure to estimate the size of your UGA by
  tracking values (i.e. name like '%uga%') in V$SESSTAT at
  peak periods then sizing the Large Pool to accomodate,
  before you enable MTS.  Unless you're really constrained
  for memory, don't be shy about this;  double the highest
  value you sum from V$SESSSTAT to be safe.  After
  enabling MTS, monitor the value of free memory where
  POOL = 'large pool' in V$SGASTAT.  If you've oversized,
  you can start backing down on LARGE_POOL_SIZE gently, if
  you need the memory elsewhere...
  
  Hope this helps...
  
  -Tim
  
   Environment:  AIX 4.3
   Oracle 8.1.7
   
   The application is a CAE tool which stores metadata
   for a hierarchy of 3D engineering design models.
   When a user opens a model at a given level in the
   design, the application retrieves data about that
   model and all of the models below it in the design
   try.  This often involves as many as 100 or more
   models.  Unfortunately, the way the application is
   written, it opens a new connection to the database for
   each model.  Thus, in the process of retrieving
   metadata, it may open and close as many as 100
   connections to the database. Obviously, this causes
   some performance problems, especially for  remote
   users.  The number of users when the system goes fully
   into production is going to be in the low 100's.
   
   The vendor is not interested in changing the way the
   software works. 
   Will use of the mult-threaded server improve
   performance in this situation, for
   example, by eliminating the overhead of starting a
   dedicated server for each connection?
   
   Thanks,
   Peter Schauss
  -- 
  Please see the official ORACLE-L FAQ:
  http://www.orafaq.net -- 
  Author: Tim Gorman
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051
  http://www.fatcity.com San Diego, California--
  Mailing list and web hosting services
 --
  --- To REMOVE yourself from this mailing list,
  send an E-Mail message to: [EMAIL PROTECTED] (note
  EXACT spelling of 'ListGuru') and in the message BODY,
  include a line containing: UNSUB ORACLE-L (or the name
  of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like
  subscribing). 
 
 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: 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

Re: var source_data varchar2(12)

Maryann,

SOURCE_DATA is a SQL*Plus variable, declared using the VAR
(a.k.a. VARIABLE) command.  It can be referenced inside the
PL/SQL block and then used by SQL*Plus commands (such as
PRINT) or SQL commands (such as SELECT) outside of the
block.

My guess is that it is being used for communicating data
values into or out of the PL/SQL block.

Hope this helps...

-Tim

 My original posting had a few lines truncated, so here I
 go again: 
 I'm looking at a PL/SQL script that goes like this
 
 -- Header
 var Source_Data  VARCHAR2(12)
 
 DECLARE
 Num1   NUMBER;
 
 BEGIN
   Source_Data := '1';
 
 What's the purpose of having a VAR statement in front or
 Before of the Declare section? Is it used for
 bind-variables, is that what it is? 
 thx
 maa 
 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Maryann Atkinson
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: file sizes over 32GB

2003-11-07 Thread Tim Gorman

Ryan,

Oracle can certainly transport more than one datafile at a
time.  I'm not sure what you mean by the datafiles need to
be 'atomic' to be transported, but it is certainly a
limitation of the application logic, not Oracle.  You could
transport every single PERMANENT tablespace in a database
(except SYSTEM) in a single TTS operation, regardless of how
many datafiles were involved in each tablespace or in the
database.  Perhaps the person working on the application has
gotten off on the wrong foot and needs to add some
real-world considerations to it?

With regards to datafile sizes, just because you can create
ultra-large datafiles doesn't mean you should.  Doing so
means letting yourself in for some really painful downstream
after-effects, I think.  My personal preference is a max
datafile size of 2G, 4G, or 8G regardless of 32-bit, 64-bit,
or large files capabilities.  YMMV...

For reasons why, think about it from the backup/restore
perspective.  Which database can be backed up or restored
faster:  one with 100 2Gb datafiles or one with 2 100Gb
datafiles?  Datafile management is just like extent
management.  As Roger Waters said, All in all, they're all
just bricks in the wall...  :-)

Just my $0.02...

-Tim

 One of the guys here did some research and found that
 files over 32GB can cause data dictionary corruption.
 anyone have problems with this? we are using an automated
 transportable tablespace process with alot of logic and
 between many instances and servers. 
 we would prefer not to complicate the logic by having to
 introduce additional tablespaces to transport(cant do
 multiple datafiles in one tablespace because the datafiles
 need to be atomic to be transported).  
 so anyone use datafiles larger than 32GBs. What happened?
 I know most of you dont, but we are in a unique situation.
 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: [EMAIL PROTECTED]
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: This is just wrong

2003-11-06 Thread Tim Gorman

I doubt it too.  I don¹t see MAC as a serious server, and it would probably
be a mistake for Apple to go in that direction...

However, I hope that Oracle releases a 10g ³developer¹s release² for OS X,
so I can keep up by having it on my laptop.  What they did with 9.2 is just
fine with me...


on 11/6/03 12:09 AM, Paul Drake at [EMAIL PROTECTED] wrote:

 Tim,
  
  Oracle has even produced a developer's release of Oracle9.2 for Mac
  OS X, downloadable from OTN.
  
 that was released in Sept 2002.
 releases of 8.1.6 and 8.1.7 were supposed to be released for production usage,
 according to articles floated by Bob Shrimpi. Those never happened.
 What do you think are the chances of a 9.2 production release for Mac OS X?
 Oracle doesn't even support FreeBSD. One can run the Linux release of Oracle
 Server with Linux compatibility loaded.
  
 Do you really think that they would support Mac OS X?
  
 In my opinion, no. Not last september, not this september, not next september.
 never.
 Who really wants to run the server software on a Tier 3 platform, anyways?
  
 Paul
 
 
 
 Tim Gorman [EMAIL PROTECTED] wrote:
 I live on a Mac laptop. I chose to do so because there are only three
 possible alternatives for laptop (or PC) users:
 
 - Windows
 - anti-Windows (i.e. Linux)
 - the middle-ground (i.e. Mac)
 
 The Mac is mature (20 years) and has applications, such as Microsoft Office,
 Netscape, etc. I have no quarrel with MS about their applications (they
 only suck at operating systems) -- it is what they do best and I think it is
 nonsense to learn a different word-processor other than Word, a different
 spreadsheet other than Excel, a different presentation tool other than
 PowerPoint. On Linux, you have no choice. On Mac, you get all those things
 because MS has always had a soft spot for the Mac (let's hope it continues).
 
 Oracle has even produced a developer's release of Oracle9.2 for Mac OS X,
 downloadable from OTN.
 
 Now, the folks at! Apple are nonetheless surprisingly arrogant about their
 place in the world -- v10.2 of Mac OS X still leaves a bit to be desired
 when you are trying to live in a world of Windows. C'mon folks: you have
 only 3% of the worldwide market -- let's start acting like it! Nobody cares
 that you have better technology longer -- you have to work with Windows, not
 the other way around!
 
 Some vendors (i.e. Digital cameras, printer drivers, etc) simply refuse to
 port their stuff to Mac OS X. If you are trying to use Oracle Apps R10.7
 NCA or R11.0, you cannot use the Applet Viewer or the Jinitiator, though I
 haven't had problems with the newer 11i versions. Most surprising of all,
 X-Windows is a johnny-come-lately to Mac OS X; when I started with Mac
 earlier this year, you had to jump through 20 hoops to get something
 installed. Now, X-Darwin is an easy install.
 
 But the nicest thing is having UNIX (i.e. FreeBSD) underneath. Finally, 15
 years afte! r I drooled over the ATT 3B1 (i.e. the UNIX PC), I've got
 UNIX-to-go and I'm not having to fight with Windows people for everything
 about everything.
 
 
 
 on 11/4/03 7:44 PM, Mladen Gogala at [EMAIL PROTECTED] wrote:
 
  Jonathan, you're a very smart guy and a very nice one as well but I cannot
  make sense of this clarification of yours. Would you care to explain it a
 bit?
  What confuses me is that you agree that one version of Unix (Linux) is not
  appropriate for a home user, but then, in the same message, recommend OS
 X,
  which is essentially a version of BSD Unix. Was that a joke? Are you
  moonlighting as an Apple salesman?
  
  On 2003.11.04 20:29, Jonathan Gennick wrote:
  Tuesday, November 4, 2003, 7:19:25 PM, Joe Testa ([EMAIL PROTECTED])
 wrote:
  JT Redhat recommending windoze for desktop.
  
  JT http://zdnet.com.com/2100-1104_2-5101690.html
  I find that assessment reasonable. It's no slam against
  Linux, just a recognition that for perhaps the vast majority
  of non-business users (i.e., home users) that Windows is a
  more appropriate choice. Actually, given the number of
  support calls I get from friends running Windows, OS X
  might actually be the *best* choice for such people.
  
  Best regards,
  
  Jonathan Gennick --- Brighten the corner where you are
  http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
  
  Join the Oracle-article list and receive one
  article on Oracle technologies per month by
  email. To join, visit
  http://four.pairlist.net/mailman/listinfo/oracle-article,
  
  or send email to [EMAIL PROTECTED] and
  include the word subscribe in either the subject or body.
  
  --
  Please see the official ORACLE-L FAQ: http://www.orafaq.net
  --
  Author: Jonathan Gennick
  INET: [EMAIL PROTECTED]
  
  Fat City Network Services -- 858-538-5051 http://www.fatcity.com
  San Diego, California -- Mailing list and web hosting services
  -
  To REMOVE yourself from this mailing list, send an E-Mail message

RE: shareplex: datatype unsupported

2003-11-05 Thread Tim Onions

That goes for Shareplex too (sorry to state the obvious). I've been
seriously bitten in recent weeks by problems with their stuff too.

_
Tim Onions
Head of Oracle Development
Speech Machines (A MedQuist Company)
...the speech-to-data Application Service Provider
Tel: +44.1684.312364
http://www.speechmachines.com



-Original Message-
Sent: 05 November 2003 14:59
To: Multiple recipients of list ORACLE-L


This isn't a direct answer to your question, but make sure you test
logical standby thoroughly--I had to abandon the idea of using it due
to serious bugs in the apply process, and due to seriously poor
performance of the apply process.


--- elain he [EMAIL PROTECTED] wrote:
 Hi,
 We are evaluating using either Oracle logical standby or Quest
 Shareplex 
 replication for reporting purposes. It appears that there are quite a
 few 
 datatypes not supported by Logical standby. Anyone knows what
 datatypes are 
 not supported by shareplex replication? Tried looking up at quest
 website 
 but could not find any documentation.
 
 Quest claimed that shareplex can replicate database of different
 versions, 
 for eg from 9i to 8i as long as the 9i new features are not being
 utilized. 
 Anyone has any experience with that?
 
 Thanks.
 
 elain
 
 _
 MSN Messenger with backgrounds, emoticons and more. 
 http://www.msnmessenger-download.com/tracking/cdp_customize
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: elain he
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).


=
Paul Baumgartel
Transcentive, Inc.
www.transcentive.com

__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Onions
  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: This is just wrong

2003-11-05 Thread Tim Gorman

I live on a Mac laptop.  I chose to do so because there are only three
possible alternatives for laptop (or PC) users:

  - Windows
  - anti-Windows (i.e. Linux)
  - the middle-ground (i.e. Mac)

The Mac is mature (20 years) and has applications, such as Microsoft Office,
Netscape, etc.  I have no quarrel with MS about their applications (they
only suck at operating systems) -- it is what they do best and I think it is
nonsense to learn a different word-processor other than Word, a different
spreadsheet other than Excel, a different presentation tool other than
PowerPoint.  On Linux, you have no choice.  On Mac, you get all those things
because MS has always had a soft spot for the Mac (let's hope it continues).

Oracle has even produced a developer's release of Oracle9.2 for Mac OS X,
downloadable from OTN.

Now, the folks at Apple are nonetheless surprisingly arrogant about their
place in the world -- v10.2 of Mac OS X still leaves a bit to be desired
when you are trying to live in a world of Windows.  C'mon folks:  you have
only 3% of the worldwide market -- let's start acting like it!  Nobody cares
that you have better technology longer -- you have to work with Windows, not
the other way around!

Some vendors (i.e. Digital cameras, printer drivers, etc) simply refuse to
port their stuff to Mac OS X.  If you are trying to use Oracle Apps R10.7
NCA or R11.0, you cannot use the Applet Viewer or the Jinitiator, though I
haven't had problems with the newer 11i versions.  Most surprising of all,
X-Windows is a johnny-come-lately to Mac OS X;  when I started with Mac
earlier this year, you had to jump through 20 hoops to get something
installed.  Now, X-Darwin is an easy install.

But the nicest thing is having UNIX (i.e. FreeBSD) underneath.  Finally, 15
years after I drooled over the ATT 3B1 (i.e. the UNIX PC), I've got
UNIX-to-go and I'm not having to fight with Windows people for everything
about everything.



on 11/4/03 7:44 PM, Mladen Gogala at [EMAIL PROTECTED] wrote:

 Jonathan, you're a very smart guy and a very nice one as well but I cannot
 make sense of this clarification of yours. Would you care to explain it a bit?
 What confuses me is that you agree that one version of Unix (Linux) is not
 appropriate for a home user, but then, in the same message, recommend OS X,
 which is essentially a version of BSD  Unix. Was that a joke? Are you
 moonlighting as an Apple salesman?
 
 On 2003.11.04 20:29, Jonathan Gennick wrote:
 Tuesday, November 4, 2003, 7:19:25 PM, Joe Testa ([EMAIL PROTECTED]) wrote:
 JT Redhat recommending windoze for desktop.
 
 JT http://zdnet.com.com/2100-1104_2-5101690.html
 
 I find that assessment reasonable. It's no slam against
 Linux, just a recognition that for perhaps the vast majority
 of non-business users (i.e., home users) that Windows is a
 more appropriate choice. Actually, given the number of
 support calls I get from friends running Windows, OS X
 might actually be the *best* choice for such people.
 
 Best regards,
 
 Jonathan Gennick --- Brighten the corner where you are
 http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
 
 Join the Oracle-article list and receive one
 article on Oracle technologies per month by
 email. To join, visit
 http://four.pairlist.net/mailman/listinfo/oracle-article,
 
 or send email to [EMAIL PROTECTED] and
 include the word subscribe in either the subject or body.
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Jonathan Gennick
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: PCTFREE and PCTUSED

2003-11-04 Thread Tim Fleury

PCTUSED comes into play when rows are deleted from the block.  If enough
data is deleted from a block to cause the block to fall below 60% used
(PCTUSED), the block goes back on the freelist for subsequent
inserts/updates.

-Original Message-
Sent: Tuesday, November 04, 2003 3:09 PM
To: Multiple recipients of list ORACLE-L


Suppose I have the following settings which happen to be
the defaults as well:

PCTFREE 10
PCTUSED 40


I am trying to figure out what PCTUSED is really used for.
My book is telling me that is used so that Oracle knows
whether to keep a block in the free-list.

My point is this: If PCTFREE is 10%, that means the block can be up to
90% full, right?

Well, if the block happens to be 60% full at the moment, then Oracle
knows that this block is not full enough because 60 is less than 90, so
it can keep it in the free list. I dont see what PCTUSED is needed, it
kind of seems I can accomplish the same with just one parm, that being
PCTFREE.

But Oracle wouldnt have just put a parm there without any usage, so I
guess there's something I dont see...

Any ideas/examples? Any good reasoning anywhere?

Thanks,
maa

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Fleury
  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: PCTFREE and PCTUSED

2003-11-04 Thread Tim Fleury

To use your numbers, the block can fill to 90% (100-PCTFREE) at which
time it comes off the freelist.

If you delete rows until the block falls below 40% used (PCTUSED), the
block will go back on the freelist.

-Original Message-
Sent: Tuesday, November 04, 2003 3:09 PM
To: Multiple recipients of list ORACLE-L


Suppose I have the following settings which happen to be
the defaults as well:

PCTFREE 10
PCTUSED 40


I am trying to figure out what PCTUSED is really used for.
My book is telling me that is used so that Oracle knows
whether to keep a block in the free-list.

My point is this: If PCTFREE is 10%, that means the block can be up to
90% full, right?

Well, if the block happens to be 60% full at the moment, then Oracle
knows that this block is not full enough because 60 is less than 90, so
it can keep it in the free list. I dont see what PCTUSED is needed, it
kind of seems I can accomplish the same with just one parm, that being
PCTFREE.

But Oracle wouldnt have just put a parm there without any usage, so I
guess there's something I dont see...

Any ideas/examples? Any good reasoning anywhere?

Thanks,
maa

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Fleury
  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: Sequences in OPS/RAC

2003-11-03 Thread Tim Gorman

Sequences are mastered by the single SYS.SEQ$ table in each
database.  Cached or uncached, RAC or non-RAC, OPS or
non-OPS, sequence numbers generated by this mechanism are
unique across a database, not by instance.  Each instance
updates SEQ$ as individual numbers (noncached) or ranges of
numbers (cached) are reserved, and those updates are
controlled by the same synchronization mechanisms used by
OPS/RAC for all UPDATE statements.  This is precisely the
reason that setting CACHE on sequence numbers help
performance, as the number of updates to SEQ$ are reduced,
minimizing the bottleneck.

However, because of the simplicity of this caching
mechanism, sequence numbers are not guaranteed to be in
order (i.e. sequentially ascending) across multiple
instances.

Perhaps Mr. Stephens mis-spoke, confusing uniqueness for
ordering?  Or perhaps he is confusing distributed
databases for clustered databases?


 
 
 I have always been comfortable with the idea that
 Sequences continue to  guarantee
 uniqueness even in OPS / RAC environments.
 
 However, a recent Builder.Com article by Scott Stephens on
 the SYS_GUID  function has these lines :
 Sequence generator numbers are guaranteed to be unique
 only for a single  instance, which is unsuitable for use
 as a primary key in parallel or  remote environments,
 where a sequence in each environment might generate  the
 same number and result in conflicts. An identifier created
 by SYS_GUID  is guaranteed to be unique for each
 database. 
 Huh ?!  Do the lines mean that a single sequence can have
 duplicate values  in the two instances of an RAC cluster ?
 
 
 Hemant K Chitale
 Oracle 9i Database Administrator Certified Professional
 My personal web site is :  http://hkchital.tripod.com
 
 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Hemant K Chitale
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Re[2]: Sequences in OPS/RAC

2003-11-03 Thread tim

The problem is that the ORDER clause comes at the expense of
CACHE.  You can use SQL tracing to verify that each use of
the sequence causes an update of SYS.SEQ$ when ORDER is set,
effectively rendering the CACHE setting a no-op.  So,
especially in an OPS/RAC environment, the use of ORDERED
sequences, especially heavily used ORDERED sequences, comes
at a steep price.

Think about it:  is ORDERED *really* necessary?  In some
situations (i.e. check numbers), the ORDERED clause would be
necessary, but unless you are pumping out thousands of
checks an hour, perhaps a cached sequence shouldn't be used.
 But for system-generated keys, surrogate keys, etc, I don't
think the semantics of ORDERED are necessary at all.



 Hi,
 
 I have RAC and I always use ORDER when I create SEQUENCE. 
 The following information is from Oracle Manual: 
 ORDER is necessary only to guarantee ordered generation if
 you are using Oracle with Real Application Clusters. If
 you are using exclusive mode, sequence numbers are always
 generated in order. 
 Muqthar Ahmed
 
 -Original Message-
 Sent: Monday, November 03, 2003 12:04 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Hello Hemant,
 
 Monday, November 3, 2003, 11:29:26 AM, you wrote:
 HKC However, the Builder.Com article quite explicity
 asserts HKC Sequence generator numbers are guaranteed to
 be unique only for a single  HKC instance, which is
 unsuitable for use as a primary key in parallel or  HKC
 remote environments, where a sequence in each environment
 might generate  HKC the same number and result in
 conflicts 
 Can you point us to the article? My guess is that the
 author is not familiar with Oracle, and is basing the
 above statement on his experience with some other database
 (DB2 perhaps?). There is no problem with using sequence
 numbers in a RAC. No conflicts will occur. I've never
 heard of a problem in that regard.
 
 Best regards,
 
 Jonathan Gennick --- Brighten the corner where you are
 http://Gennick.com * 906.387.1698 *
 mailto:[EMAIL PROTECTED] 
 Join the Oracle-article list and receive one
 article on Oracle technologies per month by 
 email. To join, visit
 http://four.pairlist.net/mailman/listinfo/oracle-article, 
 or send email to [EMAIL PROTECTED] and 
 include the word subscribe in either the subject or
 body. 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Jonathan Gennick
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing). -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Muqthar Ahmed
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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: char(1) VS varchar2(1)

2003-11-03 Thread Tim Gorman

Yes!  RMOUG Training Days is going to be an incredible event this year!  A
2-day conference in Colorado during ski season with a lineup of speakers
that rivals the major international conferences, including keynotes by Bill
Inmon and Sue Cook!  We're going to have a blast;  I'm looking forward to
meeting you!


on 11/3/03 2:59 AM, Tanel Poder at [EMAIL PROTECTED] wrote:

 Hi!
 
 Trailing columns with NULL values do not occupy any space, not even a
 length
 byte.
 
 Yep, I missed this one. Also, when few trailing null columns aren't stored
 in a row, this particular rows column count is smaller as well, so the
 column count in physical table storage can vary...
 
 Non-trailing columns with NULL values have a constant value of 0xFF (255)
 in
 the length byte consuming just the one byte.
 
 Yep, and this means that even fixed-length CHAR datatypes don't consume any
 space except length byte if they contain nulls. I once saw a recommendation
 to store your data in char columns if you don't want to have to worry about
 row size changes and PCTFREE setting... which is mostly a stupid suggestion
 anyway and doesn't work in case of nulls either.
 
 Tanel.
 P.S. Tim, I assume that we'll meet at RMOUG training days this Feb?
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: char(1) VS varchar2(1)

2003-11-02 Thread Tim Gorman

Trailing columns with NULL values do not occupy any space, not even a length
byte.

Non-trailing columns with NULL values have a constant value of 0xFF (255) in
the length byte consuming just the one byte.

Column values with a length of 0-254 bytes have one length byte, and values
with a length greater than 254 bytes have 3 bytes, where the first byte is
the constant 0xFE (254) and the remaining 2 bytes actually have the length.

For example:

=
SQL create table xyz
  2  (
  3 c1 number,
  4 c2 number,
  5 c3 number,
  6 c4 number,
  7 c5 number
  8  ) tablespace tools;

Table created.

SQL insert into xyz values (1, null, 1, null, 99);

1 row created.

SQL insert into xyz values (2, null, 2, null, 99);
1 row created.

SQL select dump(c1) c1, dump(c2) c2, dump(c3) c3,
  2  dump(c4) c4, dump(c5) c5 from xyz;

C1  C2C3  C4C5
--- - --- -
-
Typ=2 Len=2: 193,2  NULL  Typ=2 Len=2: 193,2  NULL  Typ=2 Len=4:
195,100,100,100
Typ=2 Len=2: 193,3  NULL  Typ=2 Len=2: 193,3  NULL  Typ=2 Len=4:
195,100,100,100

SQL select file_id,block_id,blocks from dba_extents where
 2   segment_name='XYZ';

   FILE_ID   BLOCK_ID BLOCKS
-- -- --
 2   5857  8

SQL commit;

Commit complete.

SQL alter system checkpoint;

System altered.

=

OK, the checkpoint made sure that everything was flushed to the datafile.
Now, we can look at things using the UNIX od command:

$ dd if=/u01/oradata/PRD/tools_02.dbf bs=8192 skip=5858 count=1 | \
= od -x

000 0602008016e2002f5a390104
020 08090100217b002f5a03
040 90e4000203000008002f
060 048b01000040056416000002
100 
120 00010002
140 00161f801f6a1f6a00021f90
160 1f80
200 
*
0017720 2c010502
0017740 c103ff02c103ff04c36464642c010502
0017760 c102ff02c102ff04c36464645a390601
002

OK, now remember that data rows fill from the end of the block, working
backwards, not from the beginning.

So, at the end of the block, we see the 4-byte block tailer (5a39 0601).
Just before that, we see the first row:

2c 01 05 02 c1 02 ff 02 c1 02 ff 04 c3 64 64 64
   +++--++--+--+
row hdr  c1   c2 c3   c4 c5

Then, just prior to that, we see the second row:


2c 01 05 02 c1 03 ff 02 c1 03 ff 04 c3 64 64 64
   +++--++--+--+
row hdr  c1   c2 c3   c4 c5

The third byte (0x05) of each row indicates that five columns comprise the
row.

The numbers are represented in 100s-complement which is a form of base-100
arithmetic.  For column C1, the first byte (hex 0x02) is the length byte.
The next byte (hex 0xC1 or decimal 193) is both the sign and the exponent,
while the next byte (hex 02 again) is the mantissa or significant digits in
100s-complement.  To avoid a value of 0x0, they add one to the value, so the
value of 1 is represented as 0x02.

So in the first row, the value of 1 in column C1 is shown by the three
bytes 0x02c102.  The null value in column C2 is shown by the one byte 0xff.
The value of 1 in column C3 is shown by the three bytes 0x02c102.  The
null value in column C4 is shown by the one byte 0xff.  The value of
99 in column C5 is shown by the five bytes 0x04c3646464.

-Tim


on 11/2/03 4:44 PM, Tanel Poder at [EMAIL PROTECTED] wrote:

 Hi!
 
 Just for the record, every column in a table has a length byte (or three,
 depending on column size). This works so even in clusters, where rows are
 split vertically, but column structures remain the same.
 
 Tanel.
 
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Friday, October 31, 2003 7:04 PM
 
 
 I have believed for a while that a varchar2(1) would have included a
 'length byte', making it more wasteful of storage than a char(1) but in fact
 the two are strictly identical storage-wise :
 
 SQL create table t(c1 char(1),
   2 c2 varchar2(1));
 
 Table created.
 
 SQL insert into t values('A', 'B');
 
 1 row created.
 
 SQL select vsize(c1), dump(c1), vsize(c2), dump(c2)
   2  from T;
 
  VSIZE(C1)
 --
 DUMP(C1)
 --
 --
  VSIZE(C2)
 --
 DUMP(C2

Re: RMAN Error

2003-10-30 Thread Tim Gorman

Tamizh,

You are probably running out of space in the file-system you
were located within when you invoked RMAN.  It would be
better to specify a full path-name in the FORMAT= clause
(i.e. specify directory as well as filename), to make sure
that the file-system where you are creating the backupsets
is the one you intend.

Hope this helps...

-Tim


 Hi List,
 
 I am getting the following RMAN error. Any help would be
 really appreciated. 
 RMAN script:-
 ==
 RMAN replace script ts_system_backup
 {
 allocate channel c1 type disk;
 allocate channel c2 type disk;
 allocate channel c3 type disk;
 sql 'alter system switch logfile';
 resync catalog;
 backup tablespace system format='al_%d%t%p';
 }
 
 OUTPUT
 
 
 RMAN-03022: compiling command: backup
 RMAN-03023: executing command: backup
 RMAN-08008: channel c1: starting full datafile backupset
 RMAN-08502: set_count=7 set_stamp=508754890
 creation_time=30-OCT-03 RMAN-08010: channel c1: specifying
 datafile(s) in backupset RMAN-08522: input datafile
 fno=1 name=/dev/vx/rdsk/dbdg10/System.dbf RMAN-08011:
 including current controlfile in backupset RMAN-03026:
 error recovery releasing channel resources RMAN-08031:
 released channel: c1 RMAN-08031: released channel: c2
 RMAN-08031: released channel: c3
 RMAN-00571:
 ==
 = RMAN-00569: === ERROR MESSAGE STACK FOLLOWS
 === RMAN-00571:
 ==
 = RMAN-03015: error occurred in stored script
 ts_system_backup RMAN-03007: retryable error occurred
 during execution of command: backup RMAN-07004: unhandled
 exception during command execution on channel c1
 RMAN-10035: exception raised in RPC: ORA-19502: write
 error on file al_HSBC5087548901, blockno 2040833
 (blocksize=512) ORA-27063: skgfospo: number of bytes
 read/written is incorrect Additional information: 52736
 Additional information: 131072
 RMAN-10031: ORA-19624 occurred during call to
 DBMS_BACKUP_RESTORE.BACKUPPIECECREATE
  
 RMAN 
  
 Recovery Manager complete.
 
 Thanks
 -tamizh
 
 
 -- 
 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: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

10046 level 8 trace - help required with 'direct path write' wait

Gurus

I've applied many of the things I've learnt from this list over the years
and today I tried a 10046 trace for the first time on a reported slow
transaction. From what I can tell the biggest offender is a wait seemingly
associated with rollback (see below) called 'direct path write'. Is this
just a traditional wait for a row lock to be released or something more
sinister? Any help much appreciated. Also (daft question time) what units
are tim= in? (ie how many seconds between tim=131853898 and
tim=131853270).

This SE 8.1.7.4.12 on Windows 2000.

Thank you

T¬

PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270
hv=2073223040 ad='8e9a2080'
DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1
END OF STMT
PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270
WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0
EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270
XCTEND rlbk=0, rd_only=0
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1
WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1
WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1
WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1
...
WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7
WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1
WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7
WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1
WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3
FETCH #14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim=131853898
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Onions
  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: 10046 level 8 trace - help required with 'direct path write'

OK - so I'd not got round to reading a recent previous post on the same
issue, seems tim= is in hundredths of seconds for 8i. So I got that bit of
the answer. Still pondering on those 'direct path write' waits though.

_
Tim Onions
Head of Oracle Development
Speech Machines (A MedQuist Company)
...the speech-to-data Application Service Provider
Tel: +44.1684.312364
http://www.speechmachines.com



-Original Message-
Sent: 30 October 2003 14:44
To: Multiple recipients of list ORACLE-L
wait


Gurus

I've applied many of the things I've learnt from this list over the years
and today I tried a 10046 trace for the first time on a reported slow
transaction. From what I can tell the biggest offender is a wait seemingly
associated with rollback (see below) called 'direct path write'. Is this
just a traditional wait for a row lock to be released or something more
sinister? Any help much appreciated. Also (daft question time) what units
are tim= in? (ie how many seconds between tim=131853898 and
tim=131853270).

This SE 8.1.7.4.12 on Windows 2000.

Thank you

T¬

PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270
hv=2073223040 ad='8e9a2080'
DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1
END OF STMT
PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270
WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0
EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270
XCTEND rlbk=0, rd_only=0
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1
WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1
WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1
WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1
WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1
...
WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7
WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1
WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7
WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1
WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3
FETCH #14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim=131853898
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Onions
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Onions
  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: how to get rid of default

2003-10-30 Thread Tim Fleury

I believe your only option with the alter table command is to set it to
null (which it can't be on an insert that doesn't provide a value since
the column is set up as not null).

ALTER TABLE table_name
  MODIFY (column_name DEFAULT NULL);

-Original Message-
Sent: Thursday, October 30, 2003 9:50 AM
To: Multiple recipients of list ORACLE-L


Hi.

I have created a field in a table with a default
clause. - f1 number(1) not null default 1. How can I
get rid of the default now?

thanks

Gene

__
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Olga Gurevich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Fleury
  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: 10046 level 8 trace - help required with 'direct path

Paul

Oh well spotted, I'd completely missed that, cursor #14 is MUCH more complex
and would require a fair degree of sorts.

Thanks

T¬

_
Tim Onions
Head of Oracle Development
Speech Machines (A MedQuist Company)
..the speech-to-data Application Service Provider
Tel: +44.1684.312364
http://www.speechmachines.com



-Original Message-
Sent: 30 October 2003 19:04
To: Multiple recipients of list ORACLE-L


John/Tim,

The 'direct path read/write' are for cursor #14.  The delete is cursor #15.
Check the trace file for the preceding cursor #14.


Paul


-Original Message-
Sent: Thursday, October 30, 2003 1:40 PM
To: Multiple recipients of list ORACLE-L


Tim,

As you have seen, this is due to writes to and reads from the TEMPORARY
tablespace of that user. This could be due to both SORT segments
(SORT_AREA_SIZE overflow) as well as HASH segments due to HASH Joins going
to TEMP when they overflow HASH_AREA_SIZE. This can be seen from
V$SORT_USAGE.SEGTYPE. Since a DELETE should normally not generate sorting or
Hashing, I am assuming that either there are triggers that are forcing this
to occur, or this is a view and the INSTEAD OF is performing some
inefficient joins... 

Andy - just curious how a WHERE clause on a DELETE would generate Sort usage
(outside of that explained above)...

John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

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

-Original Message-
From: Yong Huang [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 30, 2003 9:10 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: 10046 level 8 trace - help required with 'direct path


Hi, Tim,

Assuming you don't have more than 1000 files, what's your 
db_files set to and
what's select file#, name from v$tempfile? If you do have more 
than 1026 files,
select file#, name from v$datafile.

Also show us select * from v$sort_usage if you can run that 
DELETE again.

XCTEND rlbk=0: your transaction end marker says it's not 
rolling back; i.e.
it's committing.

Yong Huang

--- Andy Rivenes [EMAIL PROTECTED] wrote:
 Looks sort spillage to disk due to the where clause.
 
 Andy Rivenes
 [EMAIL PROTECTED]
 
 At 06:44 AM 10/30/2003 -0800, Tim Onions wrote:
 Gurus
 
 I've applied many of the things I've learnt from this list 
over the years
 and today I tried a 10046 trace for the first time on a 
reported slow
 transaction. From what I can tell the biggest offender is a 
wait seemingly
 associated with rollback (see below) called 'direct path 
write'. Is this
 just a traditional wait for a row lock to be released or 
something more
 sinister? Any help much appreciated. Also (daft question 
time) what units
 are tim= in? (ie how many seconds between tim=131853898 and
 tim=131853270).
 
 This SE 8.1.7.4.12 on Windows 2000.
 
 Thank you
 
 T¬
 
 PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270
 hv=2073223040 ad='8e9a2080'
 DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1
 END OF STMT
 PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270
 WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0
 EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270
 XCTEND rlbk=0, rd_only=0
 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1
 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1
 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1
 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1
 WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1
 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1
 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1
 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1
 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1
 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1
 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1
 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1
 ...
 WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7
 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1
 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7
 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1
 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3
 FETCH 
#14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim=131853898
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Tim Onions

__
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yong Huang
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com

RE: 10046 level 8 trace - help required with 'direct path write'

Mladen

As my db_files parameter is set to 1024 then I take it that p1=1026 refers
to temp file #2 confirming what everybody is telling me its sorting to disk.

Thanks for your input, I now understand what it is I should be looking at.

T¬
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Onions
  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: 2G trace files

Set the dump file size to unlimited.

-Original Message-
Sent: Wednesday, October 29, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L


I'm tracing a session with 10046 event level 8.  Here's the method I
use:

sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE );
sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
  'timed_statistics', true);
/* Max dump file size is 2G */
sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
  'max_dump_file_size', 2147483647);
sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
 
If I set max_dump_file_size greater than 2G I get an error.  But with
trace level 8, I'm easily overrunning this limit.  How do you guys get
around this?

BTW - Just got Optimizing Oracle Performance last night and if I didn't
have to work so much I would have read it through by now.  Maybe I'll
take tomorrow off.

Richard Quintin, DBA 
Information Systems  Computing, DBMS 
Virginia Tech 
-- 
When the character of a man is not clear to you, look at his friends.
-- Japanese Proverb
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Fleury
  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: 2G trace files

I believe that is a limitation with that procedure.

If you are tracing your own session, use

Alter session set max_dump_file_size=unlimited;

Otherwise set it at the system level during your trace

Alter system set max_dump_file_size=unlimited;

If necessary, reset it after your large trace has completed.

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


 If I set max_dump_file_size greater than 2G I get an error.

Perhaps I should be more specific:

sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
  'max_dump_file_size', 'unlimited');
gives:
ORA-06502: PL/SQL: numeric or value error: character to number
conversion error

sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
  'max_dump_file_size', 2147483647 + 1);
gives:
ORA-01426: numeric overflow


On Wed, 2003-10-29 at 14:24, Tim Fleury wrote:
 Set the dump file size to unlimited.
 
 -Original Message-
 Sent: Wednesday, October 29, 2003 10:49 AM
 To: Multiple recipients of list ORACLE-L
 
 
 I'm tracing a session with 10046 event level 8.  Here's the method I
 use:
 
 sys.dbms_system.set_sql_trace_in_session( p_sid, p_serial#, TRUE
);
 sys.dbms_system.set_bool_param_in_session(p_sid, p_serial#,
   'timed_statistics', true);
 /* Max dump file size is 2G */
 sys.dbms_system.set_int_param_in_session(p_sid, p_serial#,
   'max_dump_file_size', 2147483647);
 sys.dbms_system.set_ev(p_sid, p_serial#, 10046, p_level, '');
  
 If I set max_dump_file_size greater than 2G I get an error.  But with 
 trace level 8, I'm easily overrunning this limit.  How do you guys get

 around this?
 
 BTW - Just got Optimizing Oracle Performance last night and if I 
 didn't have to work so much I would have read it through by now.  
 Maybe I'll take tomorrow off.
 
 Richard Quintin, DBA
 Information Systems  Computing, DBMS 
 Virginia Tech 
 -- 
 When the character of a man is not clear to you, look at his
friends.
 -- Japanese Proverb
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Quintin, Richard
   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
Richard Quintin, DBA 
Information Systems  Computing, DBMS 
Virginia Tech 
-- 
[Long hair] is considered bohemian, which may be why I grew it, but I
keep it long because I love the way it feels, part cloak, part fan, part
mane, part security blanket. -- Marge Piercy
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Quintin, Richard
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Fleury
  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: ORA-4031 error help.

Make sure you are pinning your large and often executed packages,
triggers, procedures, etc in the shared pool (should be done at
startup).  That will help eliminate fragmentation.

select 'execute dbms_shared_pool.keep('||chr(39)||
owner||'.'||name||chr(39)||','||chr(39)||
 
decode(type,'TRIGGER','R','SEQUENCE','Q','P')||chr(39)||');'||chr(10)
  from v$db_object_cache
 where type in ('PACKAGE','PROCEDURE','TRIGGER','FUNCTION')
   and owner='SYS'
   and kept='NO'
 order by sharable_mem desc
/

This can be done for your application schema as well.

-Original Message-
[mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 29, 2003 11:39 AM
To: Multiple recipients of list ORACLE-L


Hello List, Need some help in resolving ORA-4031 error message. We are
using Lawson and for last few days users are getting ORA-4031 error 2-3
times a day in LAWSON log files but there is no error message in alert
log file or any trace file. Both shared pool and large pool is set to
1GB. Below is the current init.ora file. We are on Oracle 9202 and AIX
5.1, using MTS.

Thanks




# Miscellaneous
COMPATIBLE=9.2.0
DB_NAME=LAWSON
DB_FILES=1500
GLOBAL_NAMES=TRUE
DB_BLOCK_SIZE=8192
DB_CACHE_SIZE=1792M
DB_KEEP_CACHE_SIZE=16M
LARGE_POOL_SIZE=1024M
SHARED_POOL_SIZE=1024M
SGA_MAX_SIZE = 5G
DB_FILE_MULTIBLOCK_READ_COUNT=8 CONTROL_FILE_RECORD_KEEP_TIME=45
CURSOR_SHARING=SIMILAR OPEN_CURSORS=750 # From Lawson--Raised from 500
to 750 10/24/03
BACKGROUND_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/bdump
CORE_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/cdump
USER_DUMP_DEST=/appl/lawdb/oracle/admin/LAWSON/udump
TIMED_STATISTICS=TRUE
CONTROL_FILES=(/appl/lawdb/oracle/data/db01/LAWSON/contrl_LAWSON_01.ctl
,
 
/appl/lawdb/oracle/data/db02/LAWSON/contrl_LAWSON_02.ctl,
 
/appl/lawdb/oracle/data/db03/LAWSON/contrl_LAWSON_03.ctl,
 
/appl/lawdb/oracle/data/db04/LAWSON/contrl_LAWSON_04.ctl,
 
/appl/lawdb/oracle/data/db05/LAWSON/contrl_LAWSON_05.ctl)

# Archive LOG_ARCHIVE_DEST=/appl/lawdb/oracle/archive_logs/LAWSON/
LOG_ARCHIVE_DUPLEX_DEST=/appl/lawdb/oracle/archive_logs_2/LAWSON/
LOG_ARCHIVE_FORMAT=ARC_LAWSON_%S.%T
LOG_ARCHIVE_START=TRUE
# LOG_ARCHIVE_TRACE = 1

# Distributed, Replication and Snapshot
DB_DOMAIN=PHSOR.ORG

# Pools
JAVA_POOL_SIZE=0

# Processes and Sessions
# PROCESSES=800 Increased value per vendor JMK 6/09/03 PROCESSES=1000
SESSIONS=1140 ENQUEUE_RESOURCES=8000 TRANSACTION_AUDITING=FALSE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAST_START_MTTR_TARGET=1200
SORT_AREA_SIZE=0
HASH_AREA_SIZE=0
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=undo
UNDO_RETENTION = 10800
PGA_AGGREGATE_TARGET=1G
WORKAREA_SIZE_POLICY = AUTO
JOB_QUEUE_PROCESSES = 10
LOG_BUFFER = 8192000# To reduce 'log file parallel write' wait event
in v$system_event
CURSOR_SPACE_FOR_TIME   = TRUE
SERVICE_NAMES=lawson_ax3202a
LOCAL_LISTENER=lawson_ax3202a
# Network Registration
INSTANCE_NAME=LAWSON
DISK_ASYNCH_IO = FALSE
BACKUP_TAPE_IO_SLAVES=TRUE
PARALLEL_THREADS_PER_CPU = 6
PARALLEL_MAX_SERVERS = 6
PARALLEL_MIN_SERVERS = 1
DISPATCHERS=(ADDRESS=(PROTOCOL=TCP)(HOST=provicon)(PORT=5000))(DISPATCH
ERS=1)
MAX_DISPATCHERS = 3
SHARED_SERVERS = 10
MAX_SHARED_SERVERS = 50


DISCLAIMER:
This message is intended for the sole use of the individual to whom it
is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If you are
not the addressee you are hereby notified that you may not use, copy,
disclose, or distribute to anyone the message or any information
contained in the message. If you have received this message in error,
please immediately advise the sender by reply email and delete this
message.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: [EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Fleury
  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: 10046 trace data question

Title: Message



Refer 
to page 133 and 134 of Cary Millsap's book, Optimizing Oracle Performance. 
For his research server it is the number of elapsed microseconds since the Unix 
Epoch (00:00:00 UTC,1 January 1970).

  
  -Original Message-From: Jamadagni, 
  Rajendra [mailto:[EMAIL PROTECTED] Sent: Wednesday, 
  October 29, 2003 2:04 PMTo: Multiple recipients of list 
  ORACLE-LSubject: 10046 trace data question
  Does anyone know where tim= comes from? Is 
  it from a certain epoch? 
  e.g. PARSING IN CURSOR #15 len=6 dep=2 uid=5 oct=44 lid=5 
  tim=1042250821743271 hv=1053795750 ad='1eed99f0' COMMIT END OF STMT PARSE 
  #15:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1042250821743266 
  XCTEND rlbk=0, rd_only=1 EXEC 
  #15:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1042250821743458 
  = PARSING IN CURSOR #1 len=2882 dep=1 uid=5 oct=47 
  lid=5 tim=1042250821743528 hv=3326928535 ad='16ff4a88' 
  I am writing a program that takes a trace 
  file and reconstructs the whole trace against a timeline. My first run looks 
  like this ... As you can see, because this is first pass, I ma skipping a lot 
  of details. Those will eventually come in ... don't know how yet ... my 
  imagination is running wild.
  2003-10-27 
  09:27:21.465000 Session 
  Started. 2003-10-27 
  09:27:21.465000 
  PARSE 
  Cursor#15 [ 0 microseconds] 2003-10-27 
  09:27:21.465192 
  EXEC 
  Cursor#15 [ 192 microseconds] 2003-10-27 
  09:27:21.465259 
  EXEC 
  Cursor#1 [ 67 microseconds] 2003-10-27 
  09:27:21.466318 
  PARSE 
  Cursor#1 [ 1059 microseconds] 2003-10-27 
  09:27:21.466642 
  PARSE 
  Cursor#8 [ 324 microseconds] 2003-10-27 
  09:27:21.466721 
  EXEC 
  Cursor#8 [ 79 microseconds] 2003-10-27 
  09:27:21.467023 
  FETCH 
  Cursor#8 [ 302 microseconds] 2003-10-27 
  09:27:21.467099 
  PARSE 
  Cursor#9 [ 76 microseconds] 2003-10-27 
  09:27:21.469147 
  EXEC 
  Cursor#9 [ 2048 microseconds] 2003-10-27 
  09:27:21.469228 
  EXEC 
  Cursor#1 [ 81 microseconds] 2003-10-27 
  09:27:21.473288 
  PARSE 
  Cursor#1 [ 4060 microseconds] 
  although I am doing all calculations by 
  hand, it would be nice to know where tim= is coming from  
  any ideas? 
  If you are curious why I am doing this? We 
  get emails when users experience delays that are (or deemed) unacceptable. 
  Next day we take the trace file and try to look at it, but without a good 
  timeline it is difficult to find that a user did between 10:15am and 10:20am. 
  That's why I am writing this program. 
  Raj  
  Rajendra dot Jamadagni at nospamespn dot 
  com All Views expressed in this 
  email are strictly personal. QOTD: 
  Any clod can have facts, having an opinion is an art ! 
  **This 
  e-mail message is confidential, intended only for the named recipient(s) above 
  and may contain information that is privileged, attorney work product or 
  exempt from disclosure under applicable law. If you have received this message 
  in error, or are not the named recipient(s), please immediately notify 
  corporate MIS at (860) 766-2000 and delete this e-mail message from your 
  computer, Thank 
  you.**5

Re: dba interview questions

2003-10-28 Thread Tim Gorman

You're likely to get the kind of response my kids would give:

   Wasn't he with the Beatles or the Stones or some other *old*
band like that?

Hell, my son considers the Offspring to be over the hill and Linkin Park
as starting to lose it...

About 7 years ago, I interviewed someone who listed Phish as one of his
interests.  I asked him What is Phish? and then spelled it for him.  The
expressions that swept across his face in one second ran from frank
astonishment, to disbelief, to pity, to a carefully-composed poker face as
he answered, A musical group that I like.

Food for thought:  when I was a kid in the 70s, my father would play his
big band records and my brothers and I would roll our eyes and leave the
house.  Such lame, ancient music!  At the time, those recordings were 30-35
years old...

Um...

For a kid today, the Stones, the Beatles, Pink Floyd, Frank Zappa, Yes, and
Bowie are far more ancient.  Not just in years, but the years do add up...

..'scuse me, I think I hear a bottle of Metamucil calling...



on 10/28/03 4:59 AM, Richard Foote at [EMAIL PROTECTED] wrote:

 Only two questions are required to ensure you get an appropriate person for
 the job (any job):
 
 1) What do you think of David Bowie, is he brilliant or what ?
 
 and providing they answer the above question positively
 
 2) Are you any good ?
 
 Works every time ;)
 
 Richard
 - Original Message -
 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
 Sent: Tuesday, October 28, 2003 7:44 PM
 
 
 I ask things like tell me the thing you've done that you are most
 proud of and tell me your nightmare situation and how did you recover
 from it
 
 Ans: My worst nightmare, my date pick her nose infront of me, I call cab
 infront of her.
 
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Sinardy Xing
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: dba interview questions

2003-10-28 Thread Tim Gorman

Bumper sticker sighted just the other day:

  My karma ran over your dogma


 How about Dogma? That's a new movie and it is
 hillarious. Salma Hayek character was very impresive.
 
 
 On 10/28/2003 02:49:32 PM, Jesse, Rich wrote:
  Ghostbusters?
  When someone asks if you're a god, you say 'YES'!
  ...and the flowers are still standing.
  Tell him about the Twinkie, Egon.
  
  Joe vs. The Volcano?
  Not a nice place you have here, Joe.
  I know he can get the job, but can he do the job?
  
  The Hunt for Red October? 
  Be careful what you shoot at, Ryan.  Most things in
  here don't react well to bullets.
  Next time, Jack, write a [gosh darn] memo.
  I said speak your mind, Jack, but geezus.
  One ping only.
  Come on, Big D, fly!
  
  Princess Bride? 
  Have fun storming the castle!
  Did I make it clear that your job is at stake?
  Do you want me to send you back where you were --
  unemployed in Greenland?
  
  Simpsons?
  Your manager says for you to shut up.
  And the weak and nerdy are admired for their
  computer programming ability.  (OK, not a
  movie) 
  Young Frankenstein?
  Throw the third switch!  Not the THIRD switch!
  
  
  Rich Jesse   System/Database
  Administrator [EMAIL PROTECTED] 
  Quad/Tech Inc, Sussex, WI USA 
  
   -Original Message-
   From: Mladen Gogala [mailto:[EMAIL PROTECTED]
   Sent: Tuesday, October 28, 2003 12:44 PM
   To: Multiple recipients of list ORACLE-L
   Subject: Re: dba interview questions
   
   
   How about Life of Brian? That's even better then the
   Holy Grail. 
  -- 
  Please see the official ORACLE-L FAQ:
  http://www.orafaq.net -- 
  Author: Jesse, Rich
INET: [EMAIL PROTECTED]
  
  Fat City Network Services-- 858-538-5051
  http://www.fatcity.com San Diego, California--
  Mailing list and web hosting services
 --
  --- To REMOVE yourself from this mailing list,
  send an E-Mail message to: [EMAIL PROTECTED] (note
  EXACT spelling of 'ListGuru') and in the message BODY,
  include a line containing: UNSUB ORACLE-L (or the name
  of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like
  subscribing). 
 
 Mladen Gogala
 Oracle DBA
 
 
 
 Note:
 This message is for the named person's use only.  It may
 contain confidential, proprietary or legally privileged
 information.  No confidentiality or privilege is waived or
 lost by any mistransmission.  If you receive this message
 in error, please immediately delete it and all copies of
 it from your system, destroy any hard copies of it and
 notify the sender.  You must not, directly or indirectly,
 use, disclose, distribute, print, or copy any part of this
 message if you are not the intended recipient. Wang
 Trading LLC and any of its subsidiaries each reserve the
 right to monitor all e-mail communications through its
 networks. Any views expressed in this message are those of
 the individual sender, except where the message states
 otherwise and the sender is authorized to state them to be
 the views of any such entity. 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Mladen Gogala
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Coalescing tablespace

Ross,

Yup.  I don't recall ever excluding SYSTEM, though I generally work hard to
minimize I/O in that tablespace (i.e. make sure not being used as temporary,
no schemas except SYS, move AUD$ table to another TS if necessary, cache
sequences, etc).

Most folks put that statement into a loop, to go through all the
tablespaces, excluding tablespaces where COUNT(*) comes back as 0...

Good luck!

-Tim


on 10/26/03 9:54 PM, Ross Collado at [EMAIL PROTECTED] wrote:

 Thanks Tim.
 Yes, for some it is a blast from the past!  Unfortunately for me, for one of
 our systems still using this database, I am sort of stuck in the past.
 On the subject of coalescing, is the SYSTEM tablespace safe to be coalesced
 as well?  I was thinking of putting this SQL statement you've suggested in a
 loop for all tablespaces.
 Thanks again and to others for the prompt reply.
 Rgds,
 Ross
 
 -Original Message-
 From: Tim Gorman [mailto:[EMAIL PROTECTED]
 Sent: Monday, 27 October 2003 2:44 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Re: Coalescing tablespace
 
 
 Ross,
 
 Wow!  That's a blast from the past!  ALTER TABLESPACE ...
 COALESCE didn't
 arrive on the scene until v7.3, so prior to that version you had to do
 something like the following:
 
 alter session set events 'immediate trace name coalesce
 level NNN';
 
 where the value of the level NNN could be set one of two ways.
 
 First and simplest way to set NNN is to use the value of
 the column TS# in
 the table SYS.TS$ where NAME is the name of the tablespace.
 So, a query
 like:
 
 select ts# from sys.ts$ where name = 'TOOLS'
 
 would yield the NNN for the level in the ALTER SESSION
 statement, or:
 
 alter session set events 'immediate trace name coalesce level 23';
 
 if the TS# of the TOOLS tablespace was 23.
 
 However, setting the level this way would cause the ALTER
 SESSION command to
 only coalesce a certain, predefined number of extents and
 then stop.  At
 this time, 6-7 years after the last time I used it, I forget what the
 default was, but it was probably a very low number (maybe 1?)...
 
 So, if you wanted to specify to the ALTER SESSION command how
 many extents
 to coalesce before quitting, you had to specify the TS# number in the
 lower-order 16 bits of the NNN and the number of extents to
 coalesce in
 the higher-order 16 bits.  This implied an upper limit of
 65535 extents to
 be coalesced at a time.
 
 So, generating a full ALTER SESSION statement, complete with a
 fully-qualified LEVEL parameter, could be performed as follows:
 
 select   'alter session set events ' ||
  '''immediate trace name coalesce level ' ||
  to_char((65536*least(count(*),65535))+t.ts#)||
 from sys.fet$   a,
  sys.fet$   b,
  sys.ts$t
 wheret.name = 'tablespace-name'
 and  a.ts# = t.ts#
 and  a.ts# = b.ts#
 and  a.file# = b.file#
 and  (a.block# + a.length) = b.block#
 group by t.ts#;
 
 The purpose of the LEAST(COUNT(*),65535) phrase is to
 prevent an overflow,
 due to max of 16 bits in which to specify the count...
 
 Hope this helps...
 
 Thanks!
 
 -Tim
 
 
 
 on 10/26/03 6:04 PM, Ross Collado at [EMAIL PROTECTED] wrote:
 
 Hi,
 For those like me still working on an Oracle 716 (hold the
 laughs), how do
 we coalesce a tablespace?
 Rgds,
 Ross
 
 -- 
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 -- 
 Author: Tim Gorman
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: ORA-4031 errors no a high Load Database

Title: Re: ORA-4031 errors no a high Load Database



Vivek,

You are using MTS/SS; have you configured the Large Pool to accommodate all of the UGA structures? If you do not have the Large Pool configured from its default of 0, then all of the UGA (i.e. session global areas, shared amongst the dispatchers and shared servers) will be placed into the Shared Pool, which is not an appropriate place for them.

For sizing, you might consider querying V$SYSSTAT where NAME = session uga memory max, then add a fudge factor (i.e. double it, if possible?) depending on how confident you are that you captured peak activity.

If you have already configured the Large Pool and can confirm that the MTS/SS processes are placing their UGAs there (by querying V$SGASTAT), then your shared pool problems lie elsewhere. Please confirm that the ORA-4031 is mentioning the Shared Pool, not the Large Pool in that case, please...

If this doesnt help, could you query V$SGASTAT, order by POOL and BYTES, and post the output to your reply to this list? Since V$SGASTAT is a real-time view (i.e. reflective of the present point-in-time), it would be most useful if the query were performed as soon as possible after an ORA-04031 is received, but we understand that they are occurring intermittently and that may not be possible...

Thanks!

-Tim

on 10/27/03 4:59 AM, VIVEK_SHARMA at [EMAIL PROTECTED] wrote:

 

Intermittent ORA-4031 errors Out of shared Pool :-

 

Oracle ver 9203

Solaris 9

Concurrent Users = 6000

Shared Servers / MTS being used

Listeners = 4

Application using Bind Variables

Application = Banking - Hybrid in Nature 

Database size = 1 TB

m/c = SF15K

 

How can this issue be approached ?

Should we consider moving to Oracle 9204 / higher ?

 

 

large_pool_size big integer 2147483648

max_shared_servers integer 1000

mts_circuits integer 11000

mts_dispatchers string (address=(protocol=tcp)(host=1

 0.16.14.236))(listener=CONSOLD

 GLIST)(dispatchers=7), (addres

 s=(protocol=tcp)(host=10.16.14

 .236))(listener=OEMDGLIST)(dis

 patchers=7), (address=(protoco

 l=tcp)(host=10.16.14.236))(lis

 tener=BBYDGLIST6)(dispatchers=

 

 7), (address=(protocol=tcp)(ho

 st=10.16.14.236))(listener=BBY

 DGLIST2)(dispatchers=7), (addr

 ess=(protocol=tcp)(host=10.16.

 14.236))(listener=BBYDGLIST3)(

 dispatchers=7), (address=(prot

 ocol=tcp)(host=10.16.0.215))(l

 istener=BBYDGLIST4)(dispatcher

 s=7),

mts_listener_address string

mts_max_dispatchers integer 150

mts_max_servers integer 1000

mts_multiple_listeners boolean FALSE

 

mts_servers integer 300

mts_service string bby01

mts_sessions integer 10995

shared_pool_reserved_size big integer 367001600

shared_pool_size big integer 1056964608

 

 

Will provide any Data needed

 

Thanks

Re: What happened to Howard Rogers ?

Title: Re: What happened to Howard Rogers ?



That is quite standard. The meaning of full time employment does not admit the concept of working on my own time; mutually exclusive in many senses. In order to segregate your own time from that of a company, you have to be a temporary (i.e. contract) worker. Each designation (i.e. permanent full-time employee or temporary contract worker) has its own advantages and disadvantages. Everyone has a choice, but each choice buys the whole package. Its not an easy choice.


on 10/27/03 7:29 AM, Whittle Jerome Contr NCI at [EMAIL PROTECTED] wrote:

I once worked for an excellent company, Southwest Research Institute, in the USA. As part of my employment agreement, I had to sign a paper saying ANY patent I was granted belonged to the company. It didn't matter if it was on my own time or not anything the company was doing. Any was the operative word.

Jerry Whittle 
ASIFICS DBA 
NCI Information Systems Inc. 
[EMAIL PROTECTED] 
618-622-4145 

-Original Message- 
From:  Nuno Souto [SMTP:[EMAIL PROTECTED] 

It's not unique to Australia and yes, they make 
you sign a piece of paper to say anything you 
do between 9am and 5pm belongs to the company... 
Not just Oracle, BTW. Everyone does that. I wonder 
what that would do to those who claim 
one consulting gig = one book... 

Oh, while I'm here: did anyone suggest Howard 
had been sacked? I don't recall seeing that 
said anywhere here. There was a troll suggesting 
that in c.d.o.s., but that was just that: a troll. 

Cheers 
Nuno Souto 
[EMAIL PROTECTED]

Re: performance issue on select count(*)

Linda,

I am guessing that since your table is partitioned on an unspecified date
column, that the index on TID is either LOCAL or non-partitioned (i.e.
GLOBAL).

If it is LOCAL (you would have had to specify the keyword, as it is not the
default), then you will be performing indexed RANGE scans on each of the
partitions in the index.  Naturally, the more partitions there are, the
longer this may take, but probably not a great deal longer than if the index
was a GLOBAL non-partitioned index.

But regardless of the number of RANGE scans and the type of index it is, the
main question is whether TID is a good index to use in the first place.
This is a matter of data, purely the nature of the data.

You can diagnose this better using results from the following query:

SELECT  NUM_ROWS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
LAST_ANALYZED
FROMDBA_INDEXES
WHERE   INDEX_NAME = 'name-of-index';

Of particular interest are the values in AVG_LEAF_BLOCKS_PER_KEY and
AVG_DATA_BLOCKS_PER_KEY, as the cost-based optimizer uses these to calculate
the cost of an index RANGE scan (assuming that column-level statistics or
histograms have not been gathered).

If the values of these two columns are high, then the CBO will be hesitant
to use the index, and with good reason.  Thus, with the use of the index
rejected as an option, you'll of course have a FULL table scan on your
hands.

There is probably more to it, but this should be a start.  Feel free to post
the results of the query above to the list, if you wish...

Hope this helps...

-Tim


on 10/27/03 6:24 AM, Linda Wang at [EMAIL PROTECTED] wrote:

 Hi,
 I have an online application that does a  'select count(*)' on a few tables.
 The 'select counts' always runs slow (about 10secs) for the first time and
 then fast again ( 1sec) after subsequent accesses. The query runs slow
 again when the data is flushed out of the buffer cache.
 10046 trace shows that the query takes a long time whenever there are disk
 accesses to fetch the data (about 1000 8K) into db cache. It should not take
 that long to fetch 1000 8K blocks into the cache and I/O does not appear to
 be the problem.
 
 Anyone has any idea what the problem may be or how I can speed up my query?
 
 DB: 8.1.7.4
 query: select count(*) from tickets where tid='value1';
 where tickets has about 2 million records partition on a date field.
 and   tid is indexed.
 
 thanks.
 
 linda
 
 _
 Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet
 Service.  Try it FREE for one month!   http://join.msn.com/?page=dept/dialup

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Coalescing tablespace

Cary,

Under 7.1, 7.2, and 7.3, there were some very real situations where even the
most careful space management strategies came to naught.  Specifically, the
habit of parallel direct-path operations on tables and indexes to trim
the last extent to avoid leaving wasted space above the high-water mark in
the temporary segment before it was merged into the final segment.  Event
10901 could be set to disable this extent trimming and allow the space to
be wasted in order to maintain desired extent sizing.  The reason for this
trim operation was not to avoid wasting space per se, but rather to
resolve the dilemma of merging multiple segments into one, each with it's
own high-water mark to maintain.

The reason this became a problem was due to an apparent failure to coalesce
on subsequent CREATE;  it was not unusual for a series of DROPs followed by
CREATEs in 7.1 and 7.2 to result in an out-of-space message, even when
DBA_SEGMENTS clearly showed no segments in the tablespace in question.  A
coalesce operation using the fully-qualified coalesce event level that I
replied in an earlier email would fix the problem directly.

The 10901 event became available in 7.3.3 (due to problems documented in 7.1
and 7.2) and became largely useless in v8.x.  In v9.x, it is apparently a
no-op.

I agree that coalesce operations as a proactive database maintenance
measure are generally a waste of time, and are generally the product of the
fragmentation nonsense that has been flying around for over a decade.  It
didn't help that some large companies had de-frag products and thus had
financial incentive to propagate the myths of problems, including
performance problems (which is utter bunk).  Some years ago I put together a
paper on the myths surrounding extent management
(http://www.EvDBT.com/papers.htm)...

From 8i onwards, of course, the use of LMTs completely negates the
discussion altogether.

Thanks!

-Tim

on 10/27/03 7:49 AM, Cary Millsap at [EMAIL PROTECTED] wrote:

 It's a note created in response to hundreds of customers shouting about
 how VMS and DOS need defragmenting; therefore, Oracle must need
 defragmenting, too.
 
 There *was* a cluster bug way back in release 6 that caused CREATE
 statements to take a really long time if you ever let a tablespace get
 more than a certain number of freed extents in it. Oracle (Jonathan
 Klein) fixed it by 6.0.36. I described that problem to the list already,
 some months ago.
 
 This bug drove the perception that Oracle tablespaces needed periodic
 defragmentation. But not exactly. Unless you defragmented *before* so
 many extents were created, even defragmentation didn't do any good. If I
 recall correctly, the magic number was about 61 extents or something
 like that, with a 2KB Oracle database block size. So, if you got to 50
 free extents and coalesced them to 1, got to 50 again and coalesced
 again, and so on, then you'd be okay. But if you ever dropped a whole
 schema and produced 2,000 free extents (or even just 70), then no amount
 of defragmenting would help you, automatic or not. Let me repeat: THIS
 PROBLEM WAS FIXED FIFTEEN YEARS AGO.
 
 The bottom line is that manual coalescing just wastes time and system
 resources. SMON's automatic coalescing wastes even more. Always did;
 still does.
 
 
 Cary Millsap
 Hotsos Enterprises, Ltd.
 http://www.hotsos.com
 
 Upcoming events:
 - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
 - SQL Optimization 101: 12/8-12 Dallas
 - Hotsos Symposium 2004: March 7-10 Dallas
 - Visit www.hotsos.com for schedule details...
 
 
 -Original Message-
 Khedr, Waleed
 Sent: Monday, October 27, 2003 8:04 AM
 To: Multiple recipients of list ORACLE-L
 
 I always saw this note (and hated it):
 
 http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_d
 atab
 ase_id=NOTp_id=31116.1
 
 Hope it helps,
 
 Waleed
 
 -Original Message-
 Sent: Sunday, October 26, 2003 8:04 PM
 To: Multiple recipients of list ORACLE-L
 
 
 Hi,
 For those like me still working on an Oracle 716 (hold the laughs), how
 do
 we coalesce a tablespace?
 Rgds,
 Ross

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Coalescing tablespace

2003-10-26 Thread Tim Gorman

Ross,

Wow!  That's a blast from the past!  ALTER TABLESPACE ... COALESCE didn't
arrive on the scene until v7.3, so prior to that version you had to do
something like the following:

alter session set events 'immediate trace name coalesce level NNN';

where the value of the level NNN could be set one of two ways.

First and simplest way to set NNN is to use the value of the column TS# in
the table SYS.TS$ where NAME is the name of the tablespace.  So, a query
like:

select ts# from sys.ts$ where name = 'TOOLS'

would yield the NNN for the level in the ALTER SESSION statement, or:

alter session set events 'immediate trace name coalesce level 23';

if the TS# of the TOOLS tablespace was 23.

However, setting the level this way would cause the ALTER SESSION command to
only coalesce a certain, predefined number of extents and then stop.  At
this time, 6-7 years after the last time I used it, I forget what the
default was, but it was probably a very low number (maybe 1?)...

So, if you wanted to specify to the ALTER SESSION command how many extents
to coalesce before quitting, you had to specify the TS# number in the
lower-order 16 bits of the NNN and the number of extents to coalesce in
the higher-order 16 bits.  This implied an upper limit of 65535 extents to
be coalesced at a time.

So, generating a full ALTER SESSION statement, complete with a
fully-qualified LEVEL parameter, could be performed as follows:

select   'alter session set events ' ||
 '''immediate trace name coalesce level ' ||
 to_char((65536*least(count(*),65535))+t.ts#)||
from sys.fet$   a,
 sys.fet$   b,
 sys.ts$t
wheret.name = 'tablespace-name'
and  a.ts# = t.ts#
and  a.ts# = b.ts#
and  a.file# = b.file#
and  (a.block# + a.length) = b.block#
group by t.ts#;

The purpose of the LEAST(COUNT(*),65535) phrase is to prevent an overflow,
due to max of 16 bits in which to specify the count...

Hope this helps...

Thanks!

-Tim



on 10/26/03 6:04 PM, Ross Collado at [EMAIL PROTECTED] wrote:

 Hi,
 For those like me still working on an Oracle 716 (hold the laughs), how do
 we coalesce a tablespace?
 Rgds,
 Ross

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: RMAN Incremental

2003-10-24 Thread Tim Gorman

Title: Re: RMAN Incremental



Michael,

Another good treatment of the use of partitioning in DW to reduce backups is a paper by Jeff Maresh posted online at http://www.EvDBT.com/papers.htm, entitled Managing the Data Lifecycle.

-Tim

on 10/24/03 4:34 PM, Michael Kline at [EMAIL PROTECTED] wrote:

I'm working at a Data Warehouse and they are looking for backup possibilities... 
 
This is almost a TB, a baby, and it may be that RMAN with incremental could be a good solution.
 
If say for instance there is a single tablespace of some 100 gig and they add 200,000,000 records to one of the tables and we do an incremental backup. Is the whole tablespace slated for backup?
 
Also if this tablespace was lost, and we recover.. What happens during that process? Does RMAN basically have to filter through two complete copies of that tablespace or just once and then get changed blocks?
 
What solutions have some doing this found to be best practice?
 
Thanks.

Michael Kline, Principal Consultant
Business To Business Solutions, LLC
Richmond, VA
804-744-1545

RE: Re: What happened to Howard Rogers ?

2003-10-23 Thread tim

Because they said so.

Signed,

-Another former Oracle employee threatened by Oracle Legal
about info posted to the internet



 He had great stuff on his site. Why did Oracle make him
 take it down? His stuff his very readable and informative
 IMHO. 
 
 
 This e-mail, including attachments, may include
 confidential and/or proprietary information, and may be
 used only by the person or entity to which it is
 addressed. If the reader of this e-mail is not the
 intended recipient or his or her authorized agent, the
 reader is hereby notified that any dissemination,
 distribution or copying of this e-mail is prohibited. If
 you have received this e-mail in error, please notify the
 sender by replying to this message and delete this e-mail
 immediately. -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Michael Milligan
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
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: how is it possible

2003-10-23 Thread Tim Gorman

Likely there are non-printing characters in the name of
the file...

To see them, try ls -alt | od -c | more.  To view, rename,
or delete the file, use wildcards...


 This happening with me 3 rd time on this hp box . When I
 do ls -alt I can see a file in current directory but when
 I try to open it (vi/cat ) it says no such file or
 directory .  I am same user who created the file . ( no
 permission problem ) 
 Any idea ??
 
 -ak
 
 
 
 
 
 /home/ak/myscripts/shell_scr $ ls -alt
 total 4
 drwxrwxrwx   2 ak   dba 96 Oct 23 14:40 .
 -rwxrwxrwx   1 ak   dba412 Oct 23 14:40
 mon_scr drwxr-x---  10 ak   dba   1024 Oct 13
 16:07 .. 
  /home/ak/myscripts/shell_scr $ cat mon_scr
 cat: Cannot open mon_scr: No such file or directory
 
  /home/ak/myscripts/shell_scr $ cat ./mon_scr
 cat: Cannot open ./mon_scr: No such file or directory
 
 /home/ak/myscripts/shell_scr $
 
 /home/ak/shell_scr $ whoami
 ak
 
 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Boolean dates...

Jose Luis,

Is it possible that your boss actually means Julian date,
not boolean date?  If you speak the two phrase out loud,
there are enough similarities to allow mispronunciation or
misinterpretation?

I tried one of your date values and it didn't work out so
well, but if the format is _similar_ to the Julian format
employed by Oracle, you might have a better time searching
Google for julian date rather than boolean date.  Just
thinking out loud...

Here is what I got using one of the values you displayed:

SQL select to_date(728283,'J') from dual;

TO_DATE(728
---
06-DEC-2719

And here is the Julian date value for today...

SQL select to_char(sysdate,'J') from dual;

TO_CHAR
---
2452935

Hope this helps...

-Tim


 Hi to all!
 
 We have an old app that manages something that my boss
 calls: boolean dates.
 
 He told me that exists an algorithm that manages dates
 as a boolean format.
 
 We have several tables in this form:
 
 Note: The following table: PAAM 
 has the field BDATE defined as NUMBER.
 
 sql select bdate from paam
 sql where rownum  6
 
 BDATE
 --
 728464
 728434
 728403
 728495
 728283
 
 now, I need to convert that format to an
 'understandable' format to get the old data and old
 dates.
 
 I'm looking (google-ing) for that subject but, without
 luck.
 
 any ideas? help?, pls...
 
 Thanks in advance
 
 Regards!
 JL
 
 
 __
 Do you Yahoo!?
 The New Yahoo! Shopping - with improved product search
 http://shopping.yahoo.com
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Jose Luis Delgado
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: OT: How to call unix shell scripts from 'C'?

If you want the C program and the spawned shell script to
interact and communicate back and forth with each other,
then you'd have to use the pipe() system call to set up a
two-way interprocess-communication pipe in the C program,
then call fork() to spawn a new identical process
(including the IPC pipes), then finally exec() in the
child process to bring the image of the desired shell
running it's shell script in.  Of course, each port of C
has variations on those basic function call (i.e. exec()
can be execv(), execve(), execle(), etc).

If you're just going to have the C program spawn the shell
script that will operate independently of its parent, you
can just call the system() library call and be done with
it...

Hope this helps...

-Tim


 The unix and C forums are pretty inactive. Hope its ok to
 ask this here.  
 Anyone know how to do this? 
 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: [EMAIL PROTECTED]
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Cache a table

I have two favorite email signatures.  One is used by Gary Dodge who is
commencing his 68th quarter at Oracle Corporation.  It reads:

Building tomorrow's legacy systems today, one crisis at a time...

And the other is used by a gentleman named Gene Fosnight, formerly of Oracle
(now happily retired):

Look, listen, and learn, for an original mistake is as rare as an
original idea.



on 10/22/03 1:19 PM, Pete Sharman at [EMAIL PROTECTED] wrote:

 I think you have mentioned that once or twice.  :)
 
 Pete
 Controlling developers is like herding cats.
 Kevin Loney, Oracle DBA Handbook
 Oh no, it's not.  It's much harder than that!
 Bruce Pihlamae, long-term Oracle DBA
 
 
 
 -Original Message-
 Mladen Gogala
 Sent: Wednesday, October 22, 2003 8:40 AM
 To: Multiple recipients of list ORACLE-L
 
 
 Good idea. I located the meaning of TIM column (time to age buffer). BTW,
 did I tell you that I like your signature?
 
 On 10/21/2003 05:39:25 PM, Pete Sharman wrote:
 Have a look at Steve Adam's web site.  He probably knows more about
 it
 than
 just about anyone else I know (as usual!)
 
 Pete
 Controlling developers is like herding cats.
 Kevin Loney, Oracle DBA Handbook
 Oh no, it's not.  It's much harder than that!
 Bruce Pihlamae, long-term Oracle DBA
 
 
 
 -Original Message-
 Mladen Gogala
 Sent: Wednesday, October 22, 2003 7:14 AM
 To: Multiple recipients of list ORACLE-L
 
 
 Did anyone try to benchmark the touch count based algorithm against
 the old LRU list? LRU list had advantage of being intuitive, while
 touch
 count
 algorithm is depending on many parameters for which I don't exactly
 understand the impact. LRU list parameters  were
 essentially defining the desired length of the free buffers list,
 while the
 touch count parameters are all undocumented and are
 signifying the size of touch pool, the interval in which block has to
 
 be touched if the touch count is to increase, the required touch
 count
 
 to be moved to the hot pool and alike. Is it more efficient then the
 previous easy and understandable LRU lists or not? Touch counts are
 visible as TCH in X$BH. I still have no clue what TIM is.
 
 On 10/21/2003 04:39:33 PM, Wolfgang Breitling wrote:
 Before Oracle 8 and the new touch count algorithm the cache
 attribute
 made sense. If a small, frequently used table was read by a full
 scan, it would have been put at the end of the LRU chain eligible
 to
 
 be aged out immediately, quite possibly by itself if it consisted
 of
 
 more than ~ db_file_multiblock_read_count blocks, i.e. the 2nd or
 3rd
 full scan read would already override the previously read blocks.
 Marking the table as CACHEd prevented that.
 
 At 01:09 PM 10/21/2003, you wrote:
 I always wondered why Oracle thought this was a useful table
 attribute.
 
 My gut feeling is that it is an extra that does little.
 
 For example, say we want to keep a code table in memory because it
 is constantly being hit for column verifiction.  By definition, if
 a table is
 constantly being queried, it's segments will be in memory because
 they never
 age out.  That sounds like cacheing to me.
 
 And then I remember a specific piece of Oracle documentation
 saying
 that,
 even though we may mark a table to be cached, it *still* may be
 aged out if memory is needed for other data blocks.
 
 Like I said, sounds a little like here you have it, and here you
 don't.
 
 I'm sure that my impression is wrong and someone will correct me.
 
 But I
 doubt I will use the CACHE option anytime soon.
 
 Tom Mercadante
 Oracle Certified Professional
 
 Wolfgang Breitling
 Oracle7, 8, 8i, 9i OCP DBA
 Centrex Consulting Corporation
 http://www.centrexcc.com
 
 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.net
 --
 Author: Wolfgang Breitling
  INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting
 services
  
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L (or the
 name of mailing list you want to be removed from).  You may also
 send
 the HELP command for other information (like subscribing).
 Mladen Gogala
 Oracle DBA
 
 
 
 Note:
 This message is for the named person's use only.  It may contain
 confidential, proprietary or legally privileged information.  No
 confidentiality or privilege is waived or lost by any
 mistransmission.
  If
 you receive this message in error, please immediately delete it and
 all
 copies of it from your system, destroy any hard copies of it and
 notify the
 sender.  You must not, directly or indirectly, use, disclose,
 distribute,
 print, or copy any part of this message if you are not the intended
 recipient. Wang Trading LLC and any of its subsidiaries each reserve

Re: 10046 trace question

Title: Re: 10046 trace question

DBMS_SYSTEM.KSDWRT should do what you want. First parameter is a numeric value 1 or 2, second is a string. If 1, the string is written to a .trc file (which is what you want). If 2, then string is written to the alert log.

on 10/22/03 9:39 AM, Jamadagni, Rajendra at [EMAIL PROTECTED] wrote:

Hi all,

I am monitoring a production database and while we have performance issues looked at, I have 10046^8 running on all user session in this RAC db.

The scenario is as follows .. user logs in through a windows terminal server, opens multiple sessions (oracle forms) to connect to database. Whenever they see a performance issue (AKA slowness) they hit a button on their windows session, that sends an email to us informing that user experienced slowness at say 10am.

Now normally because users don't exit their session till COB, the trace files are still incomplete at the time when user reported slowness. While these trace files are useful to look at next day, there is no way (that I know of) to go into the trace file and answer questions like what was this user doing around 10am ... is there?

Also is there an easy way to put a marker in the trace file (something like dbms_system.ksdddt) that can be invoked preferable triggered from a script ... and then we can go back to trace file and find out what the session was doing by looking at trace lines around the marker?

I don't know if anyone has done this before, or I am really trying to offset the US debt by collecting pennies?

Any help in this regard is greatly appreciated.
Raj

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

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

Re: Boolean dates...

Title: Re: Boolean dates...



...and just by way of trivia, the Latin word kalends is the only word in that language to start with the letter K...


on 10/22/03 6:09 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:


The 15th of March. 

From http://www.infoplease.com/spot/ides1.html : 
Kalends (1st day of the month) Nones (the 7th day in March, May, July, and October; the 5th in the other months) 
Ides (the 15th day in March, May, July, and October; the 13th in the other months 

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager, [EMAIL PROTECTED])
[EMAIL PROTECTED]



Mladen Gogala [EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED] 10/22/2003 04:09 PM 
Please respond to ORACLE-L 
 
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 
 cc: 
Subject: Re: Boolean dates... 


When, exactly, were the Ides of March?
.

Re: using temp tables for staging databases?

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

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



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

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

Re: Cache a table

 ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).
 

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: object compilation scripts

Seema,

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

-Tim

 Hi,
 Can someone send all object compilation script?
 Thx
 -Seema
 
 __
 ___ Enjoy MSN 8 patented spam control and more with
 MSN 8 Dial-up Internet  Service.  Try it FREE for one
 month!   http://join.msn.com/?page=dept/dialup 
 -- 
 Please see the official ORACLE-L FAQ:
 http://www.orafaq.net -- 
 Author: Seema Singh
   INET: [EMAIL PROTECTED]
 
 Fat City Network Services-- 858-538-5051
 http://www.fatcity.com San Diego, California--
 Mailing list and web hosting services
 --
 --- To REMOVE yourself from this mailing list,
 send an E-Mail message to: [EMAIL PROTECTED] (note
 EXACT spelling of 'ListGuru') and in the message BODY,
 include a line containing: UNSUB ORACLE-L (or the name of
 mailing list you want to be removed from).  You may also
 send the HELP command for other information (like
 subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Cache a table