date:20030122

Dan,

If I may, essentially you are saying that changes to data dictionary tables 
have to be committed immediately regardless of the outcome of the 
transaction.

For instance in the following code, starting with an empty table t1

step 1: insert into table t1 values row1
step 2: create table t2
step 3: insert into table t1 values row2
step 4: rollback

At this point a select * from t1 will show only row1, since the ddl create 
table t2 has inserted a commit. However, the point is, my transaction should 
have been from step 1 through step 4, not fromn step 3 through 4. The DDL 
broke my txn at step 2 and another transaction started from there. The data 
dictionary tables were updated and they should be committed; but that commit 
could have been done via an "autonomous transaction", not in the same 
transaction the user issued.

The more I think about it, I see no point why a DDL should insert a commit. 
This is different from saying that DDL itself may issue a commit to its 
seprate transaction to update the catalog. Any thoughts on that?

Arup

From: "Fink, Dan" <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: simple question on DDL
Date: Wed, 22 Jan 2003 14:18:57 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by 
mc1-f3.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Wed, 22 Jan 
2003 15:13:04 -0800
Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
(8.9.3/8.9.3) with UUCP id PAA16552;Wed, 22 Jan 2003 15:11:42 -0800 (PST)
Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00537F3B; 
Wed, 22 Jan 2003 14:18:57 -0800
Message-ID: <[EMAIL PROTECTED]>
X-Comment: Oracle RDBMS Community Forum
X-Sender: "Fink, Dan" <[EMAIL PROTECTED]>
Sender: [EMAIL PROTECTED]
Errors-To: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
Precedence: bulk
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 22 Jan 2003 23:13:04.0174 (UTC) 
FILETIME=[D0E4CCE0:01C2C26B]

Don't forget that extent allocation also affects the extent map for the
segment and possibly the high water mark. The hwm can be set without
allocating another extent and allocation of an extent may not alter the hwm
(if you manually allocate an extent). If I deallocate space from an object,
I will alter the  rows in fet$ and uet$ but not update the hwm. Make sense?

As for the ATOMICITY of the transaction, this is usually used to describe
the changes to data of interest. I don't think it is used to describe any
underlying data dictionary changes. Thus the answer is Yes (for 99% of the
Oracle techies) and No (for the 1% of us who really like to know exactly
what is going on under the covers).

Thanks for a great question, it brought up a subject that I had never
thought about. Yee-Haw! I learned someting today!

Cheers,
Dan

-Original Message-
Sent: Wednesday, January 22, 2003 2:04 PM
To: Multiple recipients of list ORACLE-L



Thanks Dan. The gist of your response was that all changes to the data
dictionary are immediately commited. Seems to make sense to me. Maybe,
thats one reason why one cannot free space below the high water mark. Coz
changes to UET$ has been committed, even though the data was rolled back.

I sent an email to one of my senior DBA friends, posing the same question,
and he replied with a one liner "To make the transaction as ATOMIC as
possible - They either run completely, or not at all".  Now, does that mean
the Insert, update and delete statements are not ATOMIC? For on a rollback,
changes to the data dictionary are commited, whereas the data is rolled
back.

Thanks
Raj






"Fink, Dan"


ORACLE-L <[EMAIL PROTECTED]>
.com>cc:

Sent by: Subject: RE: simple question 
on
DDL
root@fatcity.

com





January 22,

2003 02:16 PM

Please

respond to

ORACLE-L









Take the case of an insert (we'll call tx1), where space allocation is
required. As you insert records, the table allocated additional extents
(updating fet$ (free extent table) and uet$ (used extent table) in the data
dictionary). These updates to the data dictionary are implicitly committed,
even if you issue an explicit rollback for the insert statement. Imagine if
the dd changes are not immediately committed. Let's say another tx (we'll
call tx2) needs to allocate an extent in the same datafile.  If fet$
contains only a single row for the file requested, then tx1 will have an
exclusive lock on the row. tx2 needs to also lock the row exclusively in
order to update it. Thus, tx2 would wait until tx1 has completed and
released the lock. In the meantime, any transaction that needs to allocate
a

Re: Sequences in 8.1.7 vs 9i

2003-01-22 Thread BigP

I have never seen () after nextval in 7.3 or 8i or 9i .

-Bp
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 1:48 PM


> Hello everyone.
>
>   This one stumps me and I'm wondering if it is a bug that was resolved in
> 9i.  Here is sample code.
>
> Create sequence a;
> Create table xxx(numtest   number,   testvalue varchar2(100));
>
> Inside PL/SQL block and from SQL*Plus Prompt;
>
> Insert into xxx(numbest, testvalue) values (a.nextval(), 'TEST');
>
> PL/SQL: ORA-02287: sequence number not allowed here
>
> Take out the () after the sequence name and all is well.  Anyone
experience
> something like this???
> Application (not my code) written in 8i but imported the database into 9i.
>
>
>
> Thank You
>
> Stephen P. Karniotis
> Product Architect
> Compuware Corporation
> Direct: (248) 865-4350
> Mobile: (248) 408-2918
> Email: [EMAIL PROTECTED]
> Web: www.compuware.com
>
>
>
>
> The contents of this e-mail are intended for the named addressee only. It
> contains information that may be confidential. Unless you are the named
> addressee or an authorized designee, you may not copy or use it, or
disclose
> it to anyone else. If you received it in error please notify us
immediately
> and then destroy it.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Karniotis, Stephen
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: BigP
  INET: [EMAIL PROTECTED]

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

2003-01-22 Thread BigP

it depends on how you are updating slave databases .
-bp

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 3:39 PM


> We have four machines setup as slave databases which get updated data from
> one Master database every 5 minutes.  The question is how do I know all
> slave machines get updated data completely from the master database,
another
> word is how do I know there is no missing data when slave machines
replicate
> from the master database?
>
> Thanks,
> David
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Nguyen, David M
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: BigP
  INET: [EMAIL PROTECTED]

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

Oracle does not guarantee that the job will start at the time specified; 
it seems
that it usually starts about 1 minute later, at least that's what I see.

This would account for the creep, as when the interval is evaluated, it is 
later
than you might expect.

Jared






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 01/22/2003 11:39 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re:RE: dbms_job - running jobs every 15 minutes



Actually, Interval is evaluated at the beginning of the job according to
the docs.

I've not seen anyone mention the real cause behind DBMS_Job "creep".  That
is the setting of Job_Queue_Interval which, by default, is 60 seconds.  So
your jobs will run 1 minute later each time unless you set Interval to
evaluate to an absolute.  If someone's set Job_Queue_Interval longer, the
"creep" will be longer as well.

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



  
  [EMAIL PROTECTED]  
  Sent by: To:   Multiple 
recipients of list ORACLE-L 
  [EMAIL PROTECTED]  <[EMAIL PROTECTED]> 
 
   cc:   
   Subject:  Re:RE: dbms_job - 
running jobs every 15 minutes 
  01/22/2003 10:34  
  AM  
  Please respond to  
  ORACLE-L  
  
  




One potential problem with DBMS_JOBS as is being discussed here is that
Oracle
computes the next_date at the end of the job.  They do that so that if a
job
runs longer than it's schedule interval the two invocations will not run
into
each other.  Now as discussed, if the job is scheduled to start at 9:00 AM
and
runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and
it
will creep 5 minutes every time.

Dick Goulet
--
Author:
  INET: [EMAIL PROTECTED]





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

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




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

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

RE: Slow SQL*Plus connect.

2003-01-22 Thread Johnston, Tim

Is otrace enabled?

Note: 1020763.6
Note:   45482.1

Tim

-Original Message-
Sent: Wednesday, January 22, 2003 5:09 AM
To: Multiple recipients of list ORACLE-L


Hi All,

We have experienced a *very* slow connect time to a 9.0.1 database via
SQL*Plus (and other apps as well) on a Win2K machine, and I was wondering if
anybody else had experienced these slow connection times as well? We have
also been asked lately by a number of customers about slow connection times,
and to this point haven't found a solution for either ourselves or our
contacts..

It's not a network issue as connection times take just as long "locally".
Connections can take up to around a minute (and the odd occasion a couple of
minutes). No MTS is in use.

OS's that I've heard about this on are Win2K, XP and NT so I'm also
wondering if it may be a Win32 issue.

Help!

Cheers :)

Mark

===
 Mark Leith | T: +44 (0)1905 330 281
 Sales & Marketing  | F: +44 (0)870 127 5283
 Cool Tools UK Ltd  | E: [EMAIL PROTECTED]
===
   http://www.cool-tools.co.uk
   Maximising throughput & performance

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

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

Vladamir,

This job runs pretty fast, so I don't worry about jobs running
into each other.  It's a level 0 statspack.snapshot, it has run 
407 seconds in ~ 700 executions.

Jared

Vladimir Begun <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/22/2003 11:34 AM
 Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re: dbms_job - running jobs every 15 minutes

[EMAIL PROTECTED] wrote:
>>Now as discussed, if the job is scheduled to start at 9:00 AM and
>>runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, 
> 
> and it
> 
>>will creep 5 minutes every time.
> 
> 
> No, as written, my jobs start on every quarter hour, regardless of 
> runtime.
> 
> e.g. 09:00, 09:15, 09:30, 09:45 ...

Jared, I wanted to ask this question before but just provided
a solution w/o talking too much :)

If it's regardless of runtime (it means potentially one job
can consume more than 15 minutes to get things done) is it
allowed to run jobs concurrently?

In case of positive asnwer, you need 4 jobs. Otherwise in case
of more than 15mins runtime you'll face slipped jobs.

Regards,
-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

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

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

Replication question

2003-01-22 Thread Nguyen, David M

We have four machines setup as slave databases which get updated data from
one Master database every 5 minutes.  The question is how do I know all
slave machines get updated data completely from the master database, another
word is how do I know there is no missing data when slave machines replicate
from the master database?

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

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

Hamid Alavi wrote:
> 
> DEAR LIST,
> How can I import from a dump file which has some partionioned table into
> another user with no partitioning.
> Thanks in Advance
> 
> Hamid Alavi
> 
> Office  :  818-737-0526
> Cell phone  :  818-416-5095

Create the tables first and IGNORE=Y
-- 
Regards,

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

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

And I believe Gaja would be right at the front of the line to agree with you
on that.

RF

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!

-Original Message-
Sent: Wednesday, January 22, 2003 3:10 PM
To: Multiple recipients of list ORACLE-L

Guang Mei wrote:
> 
> Hi,
> 
> According to book "Oracle Performance Tuning 101" (By Gaja K.
Vaidyanatha),
> page 316:
> 
> Oracle SGA Component Percent Memory Allocation
> 
> Database buffer cache ~ 80
> Shared Pool area  ~ 12
> ...
> 
> This means 80% of SGA should be allocated to "Database buffer cache". Now
I
> have an oracle 8173 db running on Sun 2.8:
> 
> SQL> select * from v$sga;
> NAME  VALUE
>  --
> Fixed Size73888
> Variable Size 278183936
> Database Buffers  40960
> Redo Buffers1040384
> 
> How would I know if my "Variable Size" is too large or not? Which tables
or
> views that I can query to find if my shared_pool_area is set properly?
BTW,
> the db performance has been fine so far for about a year.
> 
> TIA.
> 
> Guang Mei
> 

Whatever the quality of a book, never believe ratios. Sizes are HIGHLY
dependent on what you do, and how you do it. If performance is fine,
keep it as it is.

-- 
Regards,

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

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

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

RE: Global names

Can you still create DB links between two DBs with the same global name?
While we don't normally use DB links (too many links from development to
production or vice versa), I seem to remember that being an issue.  Then
again, my memory isn't the best...

Rich


Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 22, 2003 1:08 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Global names
> 
> 
> > It recently came to my attention that the DBA's where I work have 
> adopted a 
> > convention where the global_name of a database is the same for the 
> > production, test, and development instance of that database 
> (obviously, 
> > they've turned off global naming in the init.ora).  They've 
> also set up 
> the 
> 
> Oracle has stated for some time that global_names=true will 
> be required
> in future versions of Oracle, and recommend that that be done now.
> 
> Which obviously won't work if you have databases with the same global 
> name.
> 
> I personally have global_names=false, though our databases all have 
> different global names. 
> 
> Having databases with the same global name is rather 
> confusing.  I can't 
> say I've really thought through the ramifications other than that.
> 
> 
> > Our DBA argues that this configuration is strongly preferred by the 
> majority 
> > of developers since they don't have to make any changes to 
> their code 
> when they 
> > move from development to QA and to test. 
> 
> Junior developers? If the changing the database name requires code 
> changes, then
> the duhvelopers need some remedial education.
> 
> my $0.02
> 
> Jared
-- 
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).

RE: Global names

2003-01-22 Thread Bob Metelsky

The way I understood Gloal names is a global name will have a
.someserver.com

AFAIK... Global names is set/required IF you have your sqlnet.ora with a
line for
 GLOBAL_NAMES =.someserver.com

Then you can seperatly identify your instances from the different
servers they are running on

Sqlplus [EMAIL PROTECTED] or
Sqlplus [EMAIL PROTECTED]

In the tns entries below I just use an alias how eer this wont work with
a dblink, then you need to set the parameter in initSID.ora
[example of 2 of the same SIDs different servers]

LOCDB1=
  (DESCRIPTION =
(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.54.100.231)(PORT = 1521))
)
(CONNECT_DATA = (SID = LOCDB)(SRVR=SHARED))
  )

LOCDB2=
  (DESCRIPTION =
(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.54.100.232)(PORT = 1521))
)
(CONNECT_DATA = (SID = LOCDB)(SRVR=SHARED))
  )

The problem is if your like me and would like to know what db your on
(while in SQL+)
You would add a setting to glogin

-- set db prompt 
set termout off 
col x new_value y 
select rtrim(name,chr(0)) x from v$database; 
set sqlprompt '&y SQL> ' 
set termout on 


The problem is you still see the SID not the alias or global
name.someserver.com Its much easier for SOME dba's to use the same SID
but As the first fellow pointed out it can have its
drawbacks/devistating effects


The global_name in initSID.ora, afaik is for creating dblinks with the
same or different names
For ease of use I prefer  false, but none the less that is going away

My 2cts
bob


> > It recently came to my attention that the DBA's where I work have
> adopted a 
> > convention where the global_name of a database is the same for the
> > production, test, and development instance of that database 
> (obviously, 
> > they've turned off global naming in the init.ora).  They've 
> also set up 
> the 
> 
> Oracle has stated for some time that global_names=true will 
> be required
> in future versions of Oracle, and recommend that that be done now.
> 
> Which obviously won't work if you have databases with the same global 
> name.
> 
> I personally have global_names=false, though our databases all have 
> different global names. 
> 
> Having databases with the same global name is rather 
> confusing.  I can't 
> say I've really thought through the ramifications other than that.
> 
> 
> > Our DBA argues that this configuration is strongly preferred by the 
> majority 
> > of developers since they don't have to make any changes to 
> their code 
> when they 
> > move from development to QA and to test. 
> 
> Junior developers? If the changing the database name requires code 
> changes, then
> the duhvelopers need some remedial education.
> 
> my $0.02
> 
> Jared
> 
> 
> -- 
> 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: Bob Metelsky
  INET: [EMAIL PROTECTED]

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

RE: simple question on DDL

2003-01-22 Thread Fink, Dan

Don't forget that extent allocation also affects the extent map for the
segment and possibly the high water mark. The hwm can be set without
allocating another extent and allocation of an extent may not alter the hwm
(if you manually allocate an extent). If I deallocate space from an object,
I will alter the  rows in fet$ and uet$ but not update the hwm. Make sense?

As for the ATOMICITY of the transaction, this is usually used to describe
the changes to data of interest. I don't think it is used to describe any
underlying data dictionary changes. Thus the answer is Yes (for 99% of the
Oracle techies) and No (for the 1% of us who really like to know exactly
what is going on under the covers).

Thanks for a great question, it brought up a subject that I had never
thought about. Yee-Haw! I learned someting today!

Cheers,
Dan 

-Original Message-
Sent: Wednesday, January 22, 2003 2:04 PM
To: Multiple recipients of list ORACLE-L



Thanks Dan. The gist of your response was that all changes to the data
dictionary are immediately commited. Seems to make sense to me. Maybe,
thats one reason why one cannot free space below the high water mark. Coz
changes to UET$ has been committed, even though the data was rolled back.

I sent an email to one of my senior DBA friends, posing the same question,
and he replied with a one liner "To make the transaction as ATOMIC as
possible - They either run completely, or not at all".  Now, does that mean
the Insert, update and delete statements are not ATOMIC? For on a rollback,
changes to the data dictionary are commited, whereas the data is rolled
back.

Thanks
Raj




 

"Fink, Dan"


.com>cc:

Sent by: Subject: RE: simple question on
DDL
root@fatcity.

com

 

 

January 22,

2003 02:16 PM

Please

respond to

ORACLE-L

 

 





Take the case of an insert (we'll call tx1), where space allocation is
required. As you insert records, the table allocated additional extents
(updating fet$ (free extent table) and uet$ (used extent table) in the data
dictionary). These updates to the data dictionary are implicitly committed,
even if you issue an explicit rollback for the insert statement. Imagine if
the dd changes are not immediately committed. Let's say another tx (we'll
call tx2) needs to allocate an extent in the same datafile.  If fet$
contains only a single row for the file requested, then tx1 will have an
exclusive lock on the row. tx2 needs to also lock the row exclusively in
order to update it. Thus, tx2 would wait until tx1 has completed and
released the lock. In the meantime, any transaction that needs to allocate
a
new extent in that file will have to wait...and wait...and wait.

A different strategy is to commit the changes to fet$ and uet$ immediately.
Then the next tx can access the row and grab space. While this could result
in an overallocation of space if the tx is rolled back, it does not block
other txs. If space was allocated to an object, and the tx failed, there is
a strong probability that this space will be used at some point in the
future.

It seems that the tradeoff here is that the access to the data dictionary
is
kept to a minimum duration at the expense of periodic space wastage
(initially).

As for the other data dictionary tables, it may be a case of read
consistency conflicts. If a long running transaction needs to access a
table
definition, but a previous transaction has updated the table definition,
but
not committed, which table definition do you use? There are some issues
with
definitions not being the same at the start of a transaction and at a later
point. As I recall, Oracle terminates the transaction if object definitions
change while the transaction is running.

All in all, it makes sense (at least to me) that changes to the data
dictionary are immediately committed. Otherwise, the performance and
integrity of the system would be comprimised.

Dan Fink

-Original Message-
Sent: Wednesday, January 22, 2003 10:01 AM
To: Multiple recipients of list ORACLE-L



That raises another doubt. For an simple insert statement, could also
update the UET$ or FET$ tables? So, if the purpose was to preserve all
changes to the data dictionary, What's different between OBJ$, COL$ and
these space management tables?

Thanks
Raj


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

Re: shared_pool_area size question

Guang Mei wrote:
> 
> Hi,
> 
> According to book "Oracle Performance Tuning 101" (By Gaja K. Vaidyanatha),
> page 316:
> 
> Oracle SGA Component Percent Memory Allocation
> 
> Database buffer cache ~ 80
> Shared Pool area  ~ 12
> ...
> 
> This means 80% of SGA should be allocated to "Database buffer cache". Now I
> have an oracle 8173 db running on Sun 2.8:
> 
> SQL> select * from v$sga;
> NAME  VALUE
>  --
> Fixed Size73888
> Variable Size 278183936
> Database Buffers  40960
> Redo Buffers1040384
> 
> How would I know if my "Variable Size" is too large or not? Which tables or
> views that I can query to find if my shared_pool_area is set properly? BTW,
> the db performance has been fine so far for about a year.
> 
> TIA.
> 
> Guang Mei
> 

Whatever the quality of a book, never believe ratios. Sizes are HIGHLY
dependent on what you do, and how you do it. If performance is fine,
keep it as it is.

-- 
Regards,

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

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

Sequences in 8.1.7 vs 9i

2003-01-22 Thread Karniotis, Stephen

Hello everyone. 

  This one stumps me and I'm wondering if it is a bug that was resolved in
9i.  Here is sample code.

Create sequence a;
Create table xxx(numtest   number,   testvalue varchar2(100));

Inside PL/SQL block and from SQL*Plus Prompt;

Insert into xxx(numbest, testvalue) values (a.nextval(), 'TEST');

PL/SQL: ORA-02287: sequence number not allowed here

Take out the () after the sequence name and all is well.  Anyone experience
something like this???
Application (not my code) written in 8i but imported the database into 9i.

 

Thank You

Stephen P. Karniotis
Product Architect
Compuware Corporation
Direct: (248) 865-4350
Mobile: (248) 408-2918
Email:  [EMAIL PROTECTED]
Web:www.compuware.com




The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it. 

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

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

Too good to pass up - Microsoft says "Don't trust Microsoft"

http://www.infoworld.com/articles/op/xml/02/12/09/021209opwinman.xml


-- 
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: IMPORT QUESTION

2003-01-22 Thread DENNIS WILLIAMS

Hamid - In the target schema, is the table already created? If it isn't, I
would recommend that you create it before you import the data. 
   Normally partitioned tables are quite large. Are you sure this import
will complete in a reasonable time frame?

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


-Original Message-
Sent: Wednesday, January 22, 2003 3:34 PM
To: Multiple recipients of list ORACLE-L


DEAR LIST,
How can I import from a dump file which has some partionioned table into
another user with no partitioning.
Thanks in Advance

Hamid Alavi

Office  :  818-737-0526
Cell phone  :  818-416-5095






=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


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

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

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

Re: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Vladimir Begun

Stephane Faroult wrote:

Vladimir (whose formula I am still trying to understand :-))...


TRUNC(SYSDATE) + (CEIL(TO_CHAR(SYSDATE, 'S') / 60 / :interval) / (24 
* 60 / :interval));

P.S.: could you please answer my question ('100% CPU utilization,
urgent') thread?
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

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

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

2003-01-22 Thread Thomas Day


Try:
compute sum of bytes on report
break on report
column name format a30 heading 'Memory Structure'
column bytes format 999,999,999,999 heading 'Bytes'
select * from v$sgastat
/


   

  "Guang Mei"  

  
  @hotmail.com>cc: 

  Sent by: rootSubject: shared_pool_area size question 

   

   

  01/22/2003 03:24 

  PM   

  Please respond   

  to ORACLE-L  

   

   





Hi,

According to book "Oracle Performance Tuning 101" (By Gaja K. Vaidyanatha),

page 316:

Oracle SGA Component Percent Memory Allocation

Database buffer cache ~ 80
Shared Pool area  ~ 12
...

This means 80% of SGA should be allocated to "Database buffer cache". Now I

have an oracle 8173 db running on Sun 2.8:

SQL> select * from v$sga;
NAME  VALUE
 --
Fixed Size73888
Variable Size 278183936
Database Buffers  40960
Redo Buffers1040384

How would I know if my "Variable Size" is too large or not? Which tables or

views that I can query to find if my shared_pool_area is set properly? BTW,

the db performance has been fine so far for about a year.

TIA.

Guang Mei













_
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail

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

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






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

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

2003-01-22 Thread Rajesh . Rao


Thanks Dan. The gist of your response was that all changes to the data
dictionary are immediately commited. Seems to make sense to me. Maybe,
thats one reason why one cannot free space below the high water mark. Coz
changes to UET$ has been committed, even though the data was rolled back.

I sent an email to one of my senior DBA friends, posing the same question,
and he replied with a one liner "To make the transaction as ATOMIC as
possible - They either run completely, or not at all".  Now, does that mean
the Insert, update and delete statements are not ATOMIC? For on a rollback,
changes to the data dictionary are commited, whereas the data is rolled
back.

Thanks
Raj




   
 
"Fink, Dan"
 

.com>cc:   
 
Sent by: Subject: RE: simple question on DDL   
 
root@fatcity.  
 
com
 
   
 
   
 
January 22,
 
2003 02:16 PM  
 
Please 
 
respond to 
 
ORACLE-L   
 
   
 
   
 




Take the case of an insert (we'll call tx1), where space allocation is
required. As you insert records, the table allocated additional extents
(updating fet$ (free extent table) and uet$ (used extent table) in the data
dictionary). These updates to the data dictionary are implicitly committed,
even if you issue an explicit rollback for the insert statement. Imagine if
the dd changes are not immediately committed. Let's say another tx (we'll
call tx2) needs to allocate an extent in the same datafile.  If fet$
contains only a single row for the file requested, then tx1 will have an
exclusive lock on the row. tx2 needs to also lock the row exclusively in
order to update it. Thus, tx2 would wait until tx1 has completed and
released the lock. In the meantime, any transaction that needs to allocate
a
new extent in that file will have to wait...and wait...and wait.

A different strategy is to commit the changes to fet$ and uet$ immediately.
Then the next tx can access the row and grab space. While this could result
in an overallocation of space if the tx is rolled back, it does not block
other txs. If space was allocated to an object, and the tx failed, there is
a strong probability that this space will be used at some point in the
future.

It seems that the tradeoff here is that the access to the data dictionary
is
kept to a minimum duration at the expense of periodic space wastage
(initially).

As for the other data dictionary tables, it may be a case of read
consistency conflicts. If a long running transaction needs to access a
table
definition, but a previous transaction has updated the table definition,
but
not committed, which table definition do you use? There are some issues
with
definitions not being the same at the start of a transaction and at a later
point. As I recall, Oracle terminates the transaction if object definitions
change while the transaction is running.

All in all, it makes sense (at least to me) that changes to the data
dictionary are immediately committed. Otherwise, the performance and
integrity of the system would be comprimised.

Dan Fink

-Original Message-
Sent: Wednesday, January 22, 2003 10:01 AM
To: Multiple recipients of list ORACLE-L



That raises another doubt. For an simple insert statement, could also
update the UET$ or FET$ tables? So, if the purpose was to preserve all
changes to the data dictionary, What's different between OBJ$, COL$ and
these space management tables?

Thanks
Raj


-- 
Please see the official ORACLE-L FAQ:

IMPORT QUESTION

2003-01-22 Thread Hamid Alavi

DEAR LIST,
How can I import from a dump file which has some partionioned table into
another user with no partitioning.
Thanks in Advance

Hamid Alavi

Office  :  818-737-0526
Cell phone  :  818-416-5095






=== Confidentiality Statement === 
The information contained in this message and any attachments is 
intended only for the use of the individual or entity to which it is 
addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL 
and exempt from disclosure under applicable law.  If you have received 
this message in error, you are prohibited from copying, distributing, or 
using the information.  Please contact the sender immediately by return 
e-mail and delete the original message from your system. 
= End Confidentiality Statement =  


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

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

chao_ping wrote:
> 
> hi, dba friends:
> Is there someone else who is also using tuxedo as middleware? In my system 
>we use tuxedo 7.1 on linux to connect to db server, but when I look at 
>v$session.program, it always show something like:
> 
>?  @app9 (TNS V1-V3)
>?  @app9 (TNS V1-V3)
>?  @app9 (TNS V1-V3)
>?  @app9 (TNS V1-V3)
>?  @app9 (TNS V1-V3)
>?  @app9 (TNS V1-V3)
>?  @app9 (TNS V1-V3)
>?  @app9 (TNS V1-V3)
>?  @app9 (TNS V1-V3)
>?  @app9 (TNS V1-V3)
> 
> So, i cannot findout which program is it, I have to telnet to middle ware 
>server to check via ps and process id get from v$session.
> IS there solution to this problem?
> Thanks.
> 
> 
> Regards
> zhu chao
> msn:[EMAIL PROTECTED]
> www.happyit.net
> www.cnoug.org(China Oracle User Group)
> 

Check the DBMS_APPLICATION_INFO package. I try to convince every
developer I bump into to use it.

-- 
Regards,

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

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

2003-01-22 Thread Ron Rogers

1980's???  Yes, but it works, ain't broke, reliable, KISS, and is easy
to implement.
 I use cron from my Linux box to connect to the production Oracle
database (OpenVMS) and do a RMAN backup with the catalog on a second
Oracle database (OpenVMS) server. Beats the heck out of trying to get
the backup scritp to rewrite its' self after each use. If either server
has problems there are entries in the log.
Ron

>>> [EMAIL PROTECTED] 01/22/03 10:09AM >>>
Cron? How 1980's :-))
 
RF
 

Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com 
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com! 

-Original Message-
Sent: Wednesday, January 22, 2003 7:19 AM
To: Multiple recipients of list ORACLE-L



I simplified it by using cron instead ...  

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

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


-Original Message- 
 ] 
Sent: Tuesday, January 21, 2003 7:24 PM 
To: Multiple recipients of list ORACLE-L 


Feeling particularly anal the other day,  I used the following 
specification to 
run statspack at the top of the hour, 15, 30 and 45 minutes after the 
hour. 

variable jobno number; 
variable instno number; 
begin 
select instance_number into :instno from v$instance; 
dbms_job.submit( 
:jobno 
, 'statspack.snap;' 
-- every 15 minutes at 00,15,30 and 45 
, trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * 
floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) 
, 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * 
floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' 
); 
commit; 
end; 
/ 

Seems to me that the time specs could be simplified a bit. 
Anyone care to give it a go?  :) 
Jared 

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

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

I like this solution.. Looking at the second one I proposed, I think
that it actually would end up just running once an hour at 15 after It's
amazing how things look a lot different at midnight and 2pm in the
afternoon.

Cheers!

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!



-Original Message-
Sent: Wednesday, January 22, 2003 1:39 PM
To: Multiple recipients of list ORACLE-L


The problem with that formula is that you'll get "creep" and eventually your
15-minute jobs are running 8 minutes late.

Try this one instead:

trunc(sysdate) + (trunc(to_number(sysdate - trunc(sysdate)) * (60/15*24)) /
(60/15*24)) + 1/(60/15*24)

Guaranteed to run every 15 on the 15.  At least I think so...  The algorithm
comes courtesy the TOAD group from Quest, via the TOAD mailling list, and us
here at QTI.

Enjoy!  :)
Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 22, 2003 12:35 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: dbms_job - running jobs every 15 minutes
> 
> 
> Thanks Robert, I like this. 
> 
> Simplified and still easy to read.
> 
> Jared
> 
> 
> 
> 
> 
> 
> "Robert Freeman" <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
>  01/21/2003 08:39 PM
>  Please respond to ORACLE-L
> 
>  
> To: Multiple recipients of list ORACLE-L 
> <[EMAIL PROTECTED]>
> cc: 
> Subject:RE: dbms_job - running jobs every 15 minutes
> 
> 
> How about this... this will start the job at the top of the 
> following hour
> and then schedule it every 15 minutes...
> 
> dbms_job.submit(:jobno,
> 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'),
> 'mm/dd/ hh24') 
> ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''),
> ''mm/dd/ hh24'') + 15/1440' );
-- 
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: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

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

shared_pool_area size question

2003-01-22 Thread Guang Mei

Hi,

According to book "Oracle Performance Tuning 101" (By Gaja K. Vaidyanatha), 
page 316:

Oracle SGA Component Percent Memory Allocation

Database buffer cache ~ 80
Shared Pool area  ~ 12
...

This means 80% of SGA should be allocated to "Database buffer cache". Now I 
have an oracle 8173 db running on Sun 2.8:

SQL> select * from v$sga;
NAME  VALUE
 --
Fixed Size73888
Variable Size 278183936
Database Buffers  40960
Redo Buffers1040384

How would I know if my "Variable Size" is too large or not? Which tables or 
views that I can query to find if my shared_pool_area is set properly? BTW, 
the db performance has been fine so far for about a year.

TIA.

Guang Mei













_
The new MSN 8: advanced junk mail protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

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

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

2003-01-22 Thread Gene Sais




If a job takes longer than the next scheduled time to execute then I see a 
problem.  Suppose a job runs every 15 mins but runtime is 30 mins, the 
number of jobs will increase and compete for the same resources.  
I always use cron (80's kinda of control), and the 1st thing I do is check if 
the job is running and if it is, I exit and issue a warning email.
Gene>>> [EMAIL PROTECTED] 01/22/03 02:34PM 
>>>[EMAIL PROTECTED] wrote:>>Now as discussed, if 
the job is scheduled to start at 9:00 AM and>>runbs for 5 minutes it's 
next_date for run #2 will be 9:20, not 9:15, > > and it> 
>>will creep 5 minutes every time.> > > No, as 
written, my jobs start on every quarter hour, regardless of > 
runtime.> > e.g. 09:00, 09:15, 09:30, 09:45 ...Jared, I 
wanted to ask this question before but just provideda solution w/o talking 
too much :)If it's regardless of runtime (it means potentially one 
jobcan consume more than 15 minutes to get things done) is itallowed to 
run jobs concurrently?In case of positive asnwer, you need 4 jobs. 
Otherwise in caseof more than 15mins runtime you'll face slipped 
jobs.Regards,-- Vladimir BegunThe statements and opinions 
expressed here are my own anddo not necessarily represent those of Oracle 
Corporation.-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: 
Vladimir Begun  INET: [EMAIL PROTECTED]Fat City Network 
Services    -- 858-538-5051 http://www.fatcity.comSan Diego, 
California    -- Mailing list and web 
hosting 
services-To 
REMOVE yourself from this mailing list, send an E-Mail messageto: 
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message 
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list 
you want to be removed from).  You mayalso send the HELP command for 
other information (like subscribing).

Re: simple question on DDL

2003-01-22 Thread Igor Neyman

Very good case described.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 2:16 PM


> Take the case of an insert (we'll call tx1), where space allocation is
> required. As you insert records, the table allocated additional extents
> (updating fet$ (free extent table) and uet$ (used extent table) in the
data
> dictionary). These updates to the data dictionary are implicitly
committed,
> even if you issue an explicit rollback for the insert statement. Imagine
if
> the dd changes are not immediately committed. Let's say another tx (we'll
> call tx2) needs to allocate an extent in the same datafile.  If fet$
> contains only a single row for the file requested, then tx1 will have an
> exclusive lock on the row. tx2 needs to also lock the row exclusively in
> order to update it. Thus, tx2 would wait until tx1 has completed and
> released the lock. In the meantime, any transaction that needs to allocate
a
> new extent in that file will have to wait...and wait...and wait.
>
> A different strategy is to commit the changes to fet$ and uet$
immediately.
> Then the next tx can access the row and grab space. While this could
result
> in an overallocation of space if the tx is rolled back, it does not block
> other txs. If space was allocated to an object, and the tx failed, there
is
> a strong probability that this space will be used at some point in the
> future.
>
> It seems that the tradeoff here is that the access to the data dictionary
is
> kept to a minimum duration at the expense of periodic space wastage
> (initially).
>
> As for the other data dictionary tables, it may be a case of read
> consistency conflicts. If a long running transaction needs to access a
table
> definition, but a previous transaction has updated the table definition,
but
> not committed, which table definition do you use? There are some issues
with
> definitions not being the same at the start of a transaction and at a
later
> point. As I recall, Oracle terminates the transaction if object
definitions
> change while the transaction is running.
>
> All in all, it makes sense (at least to me) that changes to the data
> dictionary are immediately committed. Otherwise, the performance and
> integrity of the system would be comprimised.
>
> Dan Fink
>
> -Original Message-
> Sent: Wednesday, January 22, 2003 10:01 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> That raises another doubt. For an simple insert statement, could also
> update the UET$ or FET$ tables? So, if the purpose was to preserve all
> changes to the data dictionary, What's different between OBJ$, COL$ and
> these space management tables?
>
> Thanks
> Raj
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Fink, Dan
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

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

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

RE: dbms_job - running jobs every 15 minutes

The problem with that formula is that you'll get "creep" and eventually your
15-minute jobs are running 8 minutes late.

Try this one instead:

trunc(sysdate) + (trunc(to_number(sysdate - trunc(sysdate)) * (60/15*24)) /
(60/15*24)) + 1/(60/15*24)

Guaranteed to run every 15 on the 15.  At least I think so...  The algorithm
comes courtesy the TOAD group from Quest, via the TOAD mailling list, and us
here at QTI.

Enjoy!  :)
Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 22, 2003 12:35 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: dbms_job - running jobs every 15 minutes
> 
> 
> Thanks Robert, I like this. 
> 
> Simplified and still easy to read.
> 
> Jared
> 
> 
> 
> 
> 
> 
> "Robert Freeman" <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
>  01/21/2003 08:39 PM
>  Please respond to ORACLE-L
> 
>  
> To: Multiple recipients of list ORACLE-L 
> <[EMAIL PROTECTED]>
> cc: 
> Subject:RE: dbms_job - running jobs every 15 minutes
> 
> 
> How about this... this will start the job at the top of the 
> following hour
> and then schedule it every 15 minutes...
> 
> dbms_job.submit(:jobno,
> 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'),
> 'mm/dd/ hh24') 
> ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''),
> ''mm/dd/ hh24'') + 15/1440' );
-- 
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).

Re:RE: dbms_job - running jobs every 15 minutes

2003-01-22 Thread JApplewhite


Actually, Interval is evaluated at the beginning of the job according to
the docs.

I've not seen anyone mention the real cause behind DBMS_Job "creep".  That
is the setting of Job_Queue_Interval which, by default, is 60 seconds.  So
your jobs will run 1 minute later each time unless you set Interval to
evaluate to an absolute.  If someone's set Job_Queue_Interval longer, the
"creep" will be longer as well.

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



   

  [EMAIL PROTECTED] 

  Sent by: To:   Multiple recipients of list 
ORACLE-L  
  [EMAIL PROTECTED]  <[EMAIL PROTECTED]> 

   cc: 

   Subject:  Re:RE: dbms_job - running 
jobs every 15 minutes   
  01/22/2003 10:34 

  AM   

  Please respond to

  ORACLE-L 

   

   





One potential problem with DBMS_JOBS as is being discussed here is that
Oracle
computes the next_date at the end of the job.  They do that so that if a
job
runs longer than it's schedule interval the two invocations will not run
into
each other.  Now as discussed, if the job is scheduled to start at 9:00 AM
and
runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and
it
will creep 5 minutes every time.

Dick Goulet
--
Author:
  INET: [EMAIL PROTECTED]





-- 
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: dbms_job - running jobs every 15 minutes

As an aside, if you want something to run twice a day at the same time (in
my case 11:50 AM/PM), this seems to work well for an interval:

trunc(sysdate) + (decode(to_char(sysdate,'AM'),'AM',1,2)*12+(11+(50/60)))/24

Enjoy!  :)

Rich


Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA


> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, January 21, 2003 6:24 PM
> To: Multiple recipients of list ORACLE-L
> Subject: dbms_job - running jobs every 15 minutes
> 
> 
> Feeling particularly anal the other day,  I used the following 
> specification to
> run statspack at the top of the hour, 15, 30 and 45 minutes after the 
> hour.
> 
> variable jobno number;
> variable instno number;
> begin
> select instance_number into :instno from v$instance;
> dbms_job.submit(
> :jobno
> , 'statspack.snap;'
> -- every 15 minutes at 00,15,30 and 45
> , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * 
> floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 ))
> , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * 
> floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))'
> );
> commit;
> end;
> /
> 
> 
> Seems to me that the time specs could be simplified a bit.
> 
> Anyone care to give it a go?  :)
> 
> Jared
-- 
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).

Re: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Vladimir Begun

[EMAIL PROTECTED] wrote:

Now as discussed, if the job is scheduled to start at 9:00 AM and
runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, 

and it


will creep 5 minutes every time.



No, as written, my jobs start on every quarter hour, regardless of 
runtime.

e.g. 09:00, 09:15, 09:30, 09:45 ...

Jared, I wanted to ask this question before but just provided
a solution w/o talking too much :)

If it's regardless of runtime (it means potentially one job
can consume more than 15 minutes to get things done) is it
allowed to run jobs concurrently?

In case of positive asnwer, you need 4 jobs. Otherwise in case
of more than 15mins runtime you'll face slipped jobs.

Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

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

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

Thanks! Works better than the first suggestion.

RF

Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (It's everywhere that I am!)
Author of several books you can find on Amazon.com!


-Original Message-
Sent: Wednesday, January 22, 2003 12:35 PM
To: Multiple recipients of list ORACLE-L


Thanks Robert, I like this. 

Simplified and still easy to read.

Jared






"Robert Freeman" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/21/2003 08:39 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: dbms_job - running jobs every 15 minutes


How about this... this will start the job at the top of the following hour
and then schedule it every 15 minutes...

dbms_job.submit(:jobno,
'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'),
'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''),
''mm/dd/ hh24'') + 15/1440' );


-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, January 21, 2003 6:24 PM
To: Multiple recipients of list ORACLE-L


Feeling particularly anal the other day,  I used the following
specification to
run statspack at the top of the hour, 15, 30 and 45 minutes after the
hour.

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(
:jobno
, 'statspack.snap;'
-- every 15 minutes at 00,15,30 and 45
, trunc(sysdate,'hh24') +  ( ( 15 + ( 15 *
floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 ))
, 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 *
floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))'
);
commit;
end;
/


Seems to me that the time specs could be simplified a bit.

Anyone care to give it a go?  :)

Jared

--
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: Robert Freeman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

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

Parallel sessions

Ok, back on this topic again I would *like* to write a query that jaunts
through v$sql and collects and condenses related parallel queries with the
slave processes for all the SQL stored in that view. I'm not seeing any way
to do this for inactive queries... It's fairly easy to identify slave
processes, but it's much harder to group them correctly. Anyone want to
share some thoughts with me on this??

RF

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

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

Re: AW: Oracle Financials lists

2003-01-22 Thread M Rafiq

Date: Wed, 22 Jan 2003 09:01:20 -0800

Yes Ron, there is.
For ORACLE FINANCIALS  go to
http://www.rchath.com/mail_list.htm
or
http://www.oaug.org/public/oaugnet/oaugnet.html
and follow the instructions.


For other Oracle Products:
http://www.odtug.com/subscrib.htm
http://www.doug.org/list.server.html

HTH. Milen


-Ursprüngliche Nachricht-
Von: Smith, Ron L. [mailto:[EMAIL PROTECTED]]

Is there a forum for Oracle Financials questions?  I am doing backup support
on Oracle Financials and I don't know anything about the product.  The /tmp
file on Unix has filled with a couple of very large temp files.  I need a
way to tie the files back to a process and hopefully a client.  Can anyone
help?

Thanks!
R.Smith



_
Add photos to your e-mail with MSN 8. Get 2 months FREE*.  
http://join.msn.com/?page=features/featuredemail

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

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

2003-01-22 Thread Fink, Dan

Take the case of an insert (we'll call tx1), where space allocation is
required. As you insert records, the table allocated additional extents
(updating fet$ (free extent table) and uet$ (used extent table) in the data
dictionary). These updates to the data dictionary are implicitly committed,
even if you issue an explicit rollback for the insert statement. Imagine if
the dd changes are not immediately committed. Let's say another tx (we'll
call tx2) needs to allocate an extent in the same datafile.  If fet$
contains only a single row for the file requested, then tx1 will have an
exclusive lock on the row. tx2 needs to also lock the row exclusively in
order to update it. Thus, tx2 would wait until tx1 has completed and
released the lock. In the meantime, any transaction that needs to allocate a
new extent in that file will have to wait...and wait...and wait. 

A different strategy is to commit the changes to fet$ and uet$ immediately.
Then the next tx can access the row and grab space. While this could result
in an overallocation of space if the tx is rolled back, it does not block
other txs. If space was allocated to an object, and the tx failed, there is
a strong probability that this space will be used at some point in the
future. 

It seems that the tradeoff here is that the access to the data dictionary is
kept to a minimum duration at the expense of periodic space wastage
(initially).

As for the other data dictionary tables, it may be a case of read
consistency conflicts. If a long running transaction needs to access a table
definition, but a previous transaction has updated the table definition, but
not committed, which table definition do you use? There are some issues with
definitions not being the same at the start of a transaction and at a later
point. As I recall, Oracle terminates the transaction if object definitions
change while the transaction is running. 

All in all, it makes sense (at least to me) that changes to the data
dictionary are immediately committed. Otherwise, the performance and
integrity of the system would be comprimised.

Dan Fink

-Original Message-
Sent: Wednesday, January 22, 2003 10:01 AM
To: Multiple recipients of list ORACLE-L



That raises another doubt. For an simple insert statement, could also
update the UET$ or FET$ tables? So, if the purpose was to preserve all
changes to the data dictionary, What's different between OBJ$, COL$ and
these space management tables?

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

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

Very good point, Raj. I didn't wonder just for the sake it; there was meat
to it ;)

Now that this has been raised, any ideas, anybody?

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 12:01 PM


>
> That raises another doubt. For an simple insert statement, could also
> update the UET$ or FET$ tables? So, if the purpose was to preserve all
> changes to the data dictionary, What's different between OBJ$, COL$ and
> these space management tables?
>
> Thanks
> Raj
>
>
>
>
>
> "Arup Nanda"
> 
> tmail.com>   cc:
> Sent by: Subject: Re: simple question
on DDL
> root@fatcity.
> com
>
>
> January 22,
> 2003 09:58 AM
> Please
> respond to
> ORACLE-L
>
>
>
>
>
>
> My guess will be to preserve the changes to the data dictionary, which are
> just Oracle tables anyway. When you create a table, a record goes to TAB$,
> SEG$ and so on and so forth. Unless there is a commit these information is
> not visible.
>
> But now that you asked, I wnder why the same objective couldn't have been
> done through autonomous transactions.
>
> Arup
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, January 22, 2003 6:33 AM
>
>
> > Hi friends
> >
> > Why DDL statements performs auto commit ? What is the exact reason
behind
> > that one?
> > Anyone can share his/her opinions!!
> >
> > Thanks & regards
> > BanarasiBabu
> > --
>
>
> --
> 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: Arup Nanda
  INET: [EMAIL PROTECTED]

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

RE: senior oracle dba

2003-01-22 Thread DENNIS WILLIAMS

Bp, 
   Often where the term "Senior DBA" comes up is with company HR
departments. It isn't usual for we DBAs to greet each other with "Hi, I'm a
senior DBA". I agree with Mark's comments, but just wanted to point out this
other aspect to the issue. Sometimes HR people use this as a method for
categorizing people in terms of making sense out of salaries. HR criteria
may have nothing to do with what you are thinking of. Personally I often
can't understand HR criteria.

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


-Original Message-
Sent: Wednesday, January 22, 2003 12:05 PM
To: Multiple recipients of list ORACLE-L


Mark ,
Thanks for the reply . I think learning is a never ending process ,
specially in IT industry where new version of software is out before 30%
adapts the previous version .But is there a line which can be drawn ? No one
knows everything . But experience tells where to look for proper things and
what plannings and cautions to be taken before adapting any change .

-Bp

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, January 21, 2003 3:39 PM


> Bp,
>
> All I know is that I'm not a senior DBA.   I think you'll know if you
> really are or not.  Have you worked with clustered databases, different
> types of partitioning, different os / io / backup architectures, rman,
> fgac, capacity planning, tuning, etc?  There's a million things you may or
> may not have had exposure to, and while there isn't a magical number where
> you qualify I think it's where you have experienced a lot.
>
> I've worked with people who have been working with Oracle for ~15 years
and
> their understanding of the product, and history of experiences, makes me
> realise how "junior" I am.
>
> Are you asking because you are considering applying for a job or perhaps
> looking for a change to your position description?
>
>
>
>
>
> "BigP"
> 
> tmail.com>cc:
> Sent by:  Subject: senior oracle
dba
> [EMAIL PROTECTED]
>
>
> 22/01/2003 10:05
> Please respond to
> ORACLE-L
>
>
>
>
>
>
> How does one qualify for senior oracel dba. Do you guys have any
> questionare which I can ask myself .
>
> -Bp
>
>
>
<<>>

>Privileged/Confidential information may be contained in this message.
>   If you are not the addressee indicated in this message
>(or responsible for delivery of the message to such person),
> you may not copy or deliver this message to anyone.
> In such case, you should destroy this message and kindly notify the sender
>by reply e-mail or by telephone on (61 3) 9612-6999.
>Please advise immediately if you or your employer does not consent to
> Internet e-mail for messages of this kind.
> Opinions, conclusions and other information in this message
>   that do not relate to the official business of
>  Transurban City Link Ltd
>  shall be understood as neither given nor endorsed by it.
>
<<<>

>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mark 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: BigP
  INET: [EMAIL PROTECTED]

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

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

Re: Global names

> It recently came to my attention that the DBA's where I work have 
adopted a 
> convention where the global_name of a database is the same for the 
> production, test, and development instance of that database (obviously, 
> they've turned off global naming in the init.ora).  They've also set up 
the 

Oracle has stated for some time that global_names=true will be required
in future versions of Oracle, and recommend that that be done now.

Which obviously won't work if you have databases with the same global 
name.

I personally have global_names=false, though our databases all have 
different global names. 

Having databases with the same global name is rather confusing.  I can't 
say I've really thought through the ramifications other than that.


> Our DBA argues that this configuration is strongly preferred by the 
majority 
> of developers since they don't have to make any changes to their code 
when they 
> move from development to QA and to test. 

Junior developers? If the changing the database name requires code 
changes, then
the duhvelopers need some remedial education.

my $0.02

Jared


-- 
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: quest SharePlex

2003-01-22 Thread Jonathan Lewis


Nick,

Thanks for the notes.
I've often wondered how Shareplex
did its thing.


Just for kicks - have you tried writing
a collision handler for streams ?

Regards

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

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

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

England__January 21/23
USA_(CA, TX)_August


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





-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 22 January 2003 18:09


>Would the same thing work if you shut down the
>Shareplex processes after the row had chained,
>and restarted them before you updated the chained
>piece ?
>-- Yes
>
>And does Shareplex guarantee that it will
>never report a 1555 error regardless of how long it
>is shut down ?
>-- No, if there are massive amounts of transactions, we can still
blow
>rollback segments, nothing will happen to the source DB instance, but
it
>will effect replication.  Part of the implementation goes through
checking
>the RBS to make sure that we can handle the volume and the amount of
>activity you are generating.  We have had customers who have shut
down
>SharePlex for 2-3 hours, and when it comes up, replicates everything
during
>those 2-3 hours just fine...  even if it has been moved into the
archive
>logs.
>
>yes, the supplemental logging is not the greatest thing Oracle ever
did, but
>it was probably one of the easiest ways to implement it.   Just for
kicks...
>do a couple small batch jobs (maybe 2-3 million row changes) and see
how
>long it takes, and how much redo it generates.  Then do the same
thing with
>Streams replicating that job...  how long does it take, and how much
redo is
>generated.   Streams uses the supplemental logging, and their capture
>process reads from the redo logs, then puts the transaction right
back into
>the database (in Advanced Queues) which generates more logs, then
they
>dequeue the operation (generating more logs again) to post it to the
target
>machine.
>
>
>


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

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

Re:RE: dbms_job - running jobs every 15 minutes

> Now as discussed, if the job is scheduled to start at 9:00 AM and
> runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, 
and it
> will creep 5 minutes every time.

No, as written, my jobs start on every quarter hour, regardless of 
runtime.

e.g. 09:00, 09:15, 09:30, 09:45 ...

Jared







[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 01/22/2003 08:34 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re:RE: dbms_job - running jobs every 15 minutes


One potential problem with DBMS_JOBS as is being discussed here is that 
Oracle
computes the next_date at the end of the job.  They do that so that if a 
job
runs longer than it's schedule interval the two invocations will not run 
into
each other.  Now as discussed, if the job is scheduled to start at 9:00 AM 
and
runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and 
it
will creep 5 minutes every time.

Dick Goulet

Reply Separator
Author: Freeman Robert - IL <[EMAIL PROTECTED]>
Date:   1/22/2003 7:09 AM

Cron? How 1980's :-))
 
RF
 

Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com! 

-Original Message-
Sent: Wednesday, January 22, 2003 7:19 AM
To: Multiple recipients of list ORACLE-L



I simplified it by using cron instead ...  

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

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


-Original Message- 
 ] 
Sent: Tuesday, January 21, 2003 7:24 PM 
To: Multiple recipients of list ORACLE-L 


Feeling particularly anal the other day,  I used the following 
specification to 
run statspack at the top of the hour, 15, 30 and 45 minutes after the 
hour. 

variable jobno number; 
variable instno number; 
begin 
select instance_number into :instno from v$instance; 
dbms_job.submit( 
:jobno 
, 'statspack.snap;' 
-- every 15 minutes at 00,15,30 and 45 
, trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * 
floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) 
, 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * 
floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' 
); 
commit; 
end; 
/ 

Seems to me that the time specs could be simplified a bit. 
Anyone care to give it a go?  :) 
Jared 





RE: dbms_job - running jobs every 15 minutes



Cron? 
How 1980's :-))
 
RF
 
Robert G. FreemanTechnical 
Management

ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell 
(it's 
everywhere that I am!)Author of several books you can find on 
Amazon.com! 

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 
22, 
  2003 7:19 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: dbms_job - running jobs every 15 
  minutes
  I simplified it by using cron instead ... 
 

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

  QOTD: Any clod can have facts, but having an 
opinion 
  is an art! 
  -Original Message- From: 

  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]";>mailto:[EMAIL PROTECTED]] 
  Sent: Tuesday, January 21, 2003 7:24 PM 
To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes 
  Feeling particularly anal the other day,  I used 
the 
  following specification to run

  statspack at the top of the hour, 15, 30 and 45 minutes after the 
  hour. 
  variable jobno number; variable 
instno

  number; begin     select instance_number 
into 
  :instno from v$instance;     
dbms_job.submit( 
  
;    
  :jobno 
;    
  , 'statspack.snap;' 
;    
  -- every 15 minutes at 00,15,30 and 45 
;    
  , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 
)) 
  
;    
  , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 
))'

      ); 
      
commit; 
  end; / 
  Seems to me that the time specs could be simplified a 
  bit. Anyone care to give it a go?  
:) 
  Jared 

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

RE: RE: dbms_job - running jobs every 15 minutes

Hmm... I had only given the code a cursory examination.

Good thing I didn't use it.  :)

Jared






Freeman Robert - IL <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/22/2003 10:19 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: RE: dbms_job - running jobs every 15 minutes


Yea... I realized that after I sent the email, so I submitted my second
suggestion...
Thanks though for your thoughts!!

Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (It's everywhere that I am!)
Author of several books you can find on Amazon.com!


-Original Message-
Sent: Wednesday, January 22, 2003 2:45 AM
To: Multiple recipients of list ORACLE-L


>
>How about
>
>dbms_job.submit(:jobno, 'statspack.snap',
>sysdate+n/1440, 'sysdate
>15/1440');
>
>where n= a number of minutes to the nearest 15
>minutes. So if it's 14:25
>then it would
>be sysdate+5/1440.
>
>since you only need to do this one time, just make
>sure that sysdate + n =
>0, 15, 30 or 45
>after the hour... :-) Of course, if you want to
>automate the thing, then
>build this around a PL/SQL procedure...that
>calculates the value of n.
>Not elegant, but I think that when
>someone looks at DBA_JOBS they are not going to ask
>what the $*#(@( you were
>trying to do..
>I subscribe to the KISS philosophy...
>
>:-)
>
>RF
>
>
>-Original Message-
>[EMAIL PROTECTED]
>Sent: Tuesday, January 21, 2003 6:24 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Feeling particularly anal the other day,  I used
>the following
>specification to
>run statspack at the top of the hour, 15, 30 and 45
>minutes after the
>hour.
>
>variable jobno number;
>variable instno number;
>begin
> select instance_number into :instno from
>v$instance;
> dbms_job.submit(
> :jobno
> , 'statspack.snap;'
> -- every 15 minutes at 00,15,30 and
>45
> , trunc(sysdate,'hh24') +  ( ( 15 +
>( 15 *
>floor(to_number(to_char(sysdate,'mi')) / 15))) / (
>24 * 60 ))
> , 'trunc(sysdate,''hh24'') +  ( (
>15 + ( 15 *
>floor(to_number(to_char(sysdate,''mi'')) / 15))) /
>( 24 * 60 ))'
> );
> commit;
>end;
>/
>
>
>Seems to me that the time specs could be simplified
>a bit.
>
>Anyone care to give it a go?  :)
>
>Jared
>
>--
>Please see the official ORACLE-L FAQ:
>http://www.orafaq.net


Robert,

   I am afraid that you will soon run into the 'slipping job' syndrom.
Without any 'trunc' (or anything functionally similar), 'sysdate' in the
interval happens to be the date when the job started - which may be up to
one minute (usually) the time when you asked it to start. Means that you 
can
easily slip by four minutes every hour.
I agree with adding 15/1440 (one day = 1440mn), but your base time musn't 
be
'sysdate' but sysdate rounded to the nearest quarter of an hour. 
Considering
that a quarter of an hour is a 96th (24 * 4) of a day you have several 
more
or less complicated ways to do it. Vladimir (whose formula I am still 
trying
to understand :-)) took the seconds since midnight, you can also do
something such as
 [today 00:00] trunc(sysdate)
 + [current time rounded to the latest quarter of an hour] floor((sysdate 
-
trunc(sysdate))* 96) / 96
 + 15/1440

HTH,

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

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

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




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

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

RE: dbms_job - running jobs every 15 minutes

Thanks Robert, I like this. 

Simplified and still easy to read.

Jared






"Robert Freeman" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/21/2003 08:39 PM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: dbms_job - running jobs every 15 minutes


How about this... this will start the job at the top of the following hour
and then schedule it every 15 minutes...

dbms_job.submit(:jobno,
'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'),
'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''),
''mm/dd/ hh24'') + 15/1440' );


-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, January 21, 2003 6:24 PM
To: Multiple recipients of list ORACLE-L


Feeling particularly anal the other day,  I used the following
specification to
run statspack at the top of the hour, 15, 30 and 45 minutes after the
hour.

variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(
:jobno
, 'statspack.snap;'
-- every 15 minutes at 00,15,30 and 45
, trunc(sysdate,'hh24') +  ( ( 15 + ( 15 *
floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 ))
, 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 *
floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))'
);
commit;
end;
/


Seems to me that the time specs could be simplified a bit.

Anyone care to give it a go?  :)

Jared

--
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: Robert Freeman
  INET: [EMAIL PROTECTED]

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

Well, I did consider that using 4 jobs , but my overdeveloped sense of 
aesthetics and professional hubris (that one mostly) required that it be 
done with a single job.  ;)

Lots of good suggestions here.

Seems like everyone is always up for a puzzle.  :)

Jared






"Arup Nanda" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 01/22/2003 06:54 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re: dbms_job - running jobs every 15 minutes


That's exactly what I do. Phew! I thought I was the only one ;)

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 8:03 AM


> Personally, I tend to just submit four jobs:  one at the top of hour, 
one
at
> 15 past, one at 30 past, and the fourth at 45 past.  To alter the
frequency,
> just "break" or remove one or more of the jobs.  Falls into the category
of
> "not elegant, but it works"...
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, January 21, 2003 9:39 PM
>
>
> > How about this... this will start the job at the top of the following
hour
> > and then schedule it every 15 minutes...
> >
> > dbms_job.submit(:jobno,
> > 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'),
> > 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/
hh24''),
> > ''mm/dd/ hh24'') + 15/1440' );
> >
> >
> > -Original Message-
> > [EMAIL PROTECTED]
> > Sent: Tuesday, January 21, 2003 6:24 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Feeling particularly anal the other day,  I used the following
> > specification to
> > run statspack at the top of the hour, 15, 30 and 45 minutes after the
> > hour.
> >
> > variable jobno number;
> > variable instno number;
> > begin
> > select instance_number into :instno from v$instance;
> > dbms_job.submit(
> > :jobno
> > , 'statspack.snap;'
> > -- every 15 minutes at 00,15,30 and 45
> > , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 *
> > floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 ))
> > , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 *
> > floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))'
> > );
> > commit;
> > end;
> > /
> >
> >
> > Seems to me that the time specs could be simplified a bit.
> >
> > Anyone care to give it a go?  :)
> >
> > Jared
> >
> > --
> > 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: Robert Freeman
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (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).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

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

RE: RE: dbms_job - running jobs every 15 minutes

Yea... I realized that after I sent the email, so I submitted my second
suggestion...
Thanks though for your thoughts!!

Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (It's everywhere that I am!)
Author of several books you can find on Amazon.com!


-Original Message-
Sent: Wednesday, January 22, 2003 2:45 AM
To: Multiple recipients of list ORACLE-L


>
>How about
>
>dbms_job.submit(:jobno, 'statspack.snap',
>sysdate+n/1440, 'sysdate
>15/1440');
>
>where n= a number of minutes to the nearest 15
>minutes. So if it's 14:25
>then it would
>be sysdate+5/1440.
>
>since you only need to do this one time, just make
>sure that sysdate + n =
>0, 15, 30 or 45
>after the hour... :-) Of course, if you want to
>automate the thing, then
>build this around a PL/SQL procedure...that
>calculates the value of n.
>Not elegant, but I think that when
>someone looks at DBA_JOBS they are not going to ask
>what the $*#(@( you were
>trying to do..
>I subscribe to the KISS philosophy...
>
>:-)
>
>RF
>
>
>-Original Message-
>[EMAIL PROTECTED]
>Sent: Tuesday, January 21, 2003 6:24 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Feeling particularly anal the other day,  I used
>the following
>specification to
>run statspack at the top of the hour, 15, 30 and 45
>minutes after the
>hour.
>
>variable jobno number;
>variable instno number;
>begin
>select instance_number into :instno from
>v$instance;
>dbms_job.submit(
>:jobno
>, 'statspack.snap;'
>-- every 15 minutes at 00,15,30 and
>45
>, trunc(sysdate,'hh24') +  ( ( 15 +
>( 15 *
>floor(to_number(to_char(sysdate,'mi')) / 15))) / (
>24 * 60 ))
>, 'trunc(sysdate,''hh24'') +  ( (
>15 + ( 15 *
>floor(to_number(to_char(sysdate,''mi'')) / 15))) /
>( 24 * 60 ))'
>);
>commit;
>end;
>/
>
>
>Seems to me that the time specs could be simplified
>a bit.
>
>Anyone care to give it a go?  :)
>
>Jared
>
>--
>Please see the official ORACLE-L FAQ:
>http://www.orafaq.net


Robert,

   I am afraid that you will soon run into the 'slipping job' syndrom.
Without any 'trunc' (or anything functionally similar), 'sysdate' in the
interval happens to be the date when the job started - which may be up to
one minute (usually) the time when you asked it to start. Means that you can
easily slip by four minutes every hour.
I agree with adding 15/1440 (one day = 1440mn), but your base time musn't be
'sysdate' but sysdate rounded to the nearest quarter of an hour. Considering
that a quarter of an hour is a 96th (24 * 4) of a day you have several more
or less complicated ways to do it. Vladimir (whose formula I am still trying
to understand :-)) took the seconds since midnight, you can also do
something such as
 [today 00:00] trunc(sysdate)
 + [current time rounded to the latest quarter of an hour] floor((sysdate -
trunc(sysdate))* 96) / 96
 + 15/1440

HTH,

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

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

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

Re[2]: dbms_job - running jobs every 15 minutes

2003-01-22 Thread dgoulet

Vladimir,

I beg to disagree.  The reason is that we used a number of jobs where the
interval was defined as "sysdate+1" and the job routinely ran for ~30 minutes
every day.  The result was that the job migrated over the course of a week by
3.5 hours so that instead of running at 6AM as scheduled on Monday it was
running at 9:30 AM on the next Monday.  I filed an iTar on the subject which
resulted in OTS providing what I stated.  Interval is evaluated at the end of
the job, not the start.

Dick Goulet

Reply Separator
Author: Vladimir Begun <[EMAIL PROTECTED]>
Date:   1/22/2003 9:40 AM

[EMAIL PROTECTED] wrote:
> One potential problem with DBMS_JOBS as is being discussed here is that Oracle
> computes the next_date at the end of the job.  They do that so that if a job

-- INTERVAL is a date function, evaluated immediately before the job starts
-- executing...

> runs longer than it's schedule interval the two invocations will not run into
> each other.  Now as discussed, if the job is scheduled to start at 9:00 AM and
> runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it
> will creep 5 minutes every time.

Regards,
-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

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

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

tuxedo connect to oracle: v$session.program =?

2003-01-22 Thread chao_ping

hi, dba friends:
Is there someone else who is also using tuxedo as middleware? In my system we 
use tuxedo 7.1 on linux to connect to db server, but when I look at v$session.program, 
it always show something like:

   ?  @app9 (TNS V1-V3)
   ?  @app9 (TNS V1-V3)
   ?  @app9 (TNS V1-V3)
   ?  @app9 (TNS V1-V3)
   ?  @app9 (TNS V1-V3)
   ?  @app9 (TNS V1-V3)
   ?  @app9 (TNS V1-V3)
   ?  @app9 (TNS V1-V3)
   ?  @app9 (TNS V1-V3)
   ?  @app9 (TNS V1-V3)

So, i cannot findout which program is it, I have to telnet to middle ware 
server to check via ps and process id get from v$session.
IS there solution to this problem?
Thanks.




Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(China Oracle User Group)


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

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

To answer Arn's question, no, I'm not currently using it. However I can see
some potential in the product.

Rich, Workspace management allows you to "version" a table. You can create
multiple "versions" of the same table (workspaces), and different people can
work in different workspaces and multiple people can work in the same
workspace. Change made in one workspace can not be seen in another
workspace. 

As an example say that I have a nightly update program create a workspace on
my tables and perform less than atomic level updates (e.g. commits within
PL/SQL loops for example) while the data to the user would look perfectly
consistent. When the update is done, all I need do is merge the workspace
with the live workspace.

As you might expect, there are space considerations to using workspace, to
weigh against the benefits. I don't recall if it's a 1:1 space requirement
or not.

RF

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!



-Original Message-
Sent: Wednesday, January 22, 2003 11:20 AM
To: Multiple recipients of list ORACLE-L


Better yet -- WHAT is Workspace Management?

"Additional training for Oracle 9i"  -- Goals for 2003  :)

> -Original Message-
> From: Arn Klammer [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, January 21, 2003 12:54 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Anyone storing their documents in the database with
> 
> 
> Robert, 
> 
> Does this mean you're using it?  If so, could you describe 
> your use, and any caveats?
> 
> >>> [EMAIL PROTECTED] 21/1/2003 15:28:47 >>>
> This message has been scanned by MAILSweeper.
> 
> 
> Workspace management - cool feature!
> 
> RF
-- 
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: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

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

Re: senior oracle dba

2003-01-22 Thread BigP

Mark ,
Thanks for the reply . I think learning is a never ending process ,
specially in IT industry where new version of software is out before 30%
adapts the previous version .But is there a line which can be drawn ? No one
knows everything . But experience tells where to look for proper things and
what plannings and cautions to be taken before adapting any change .

-Bp

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, January 21, 2003 3:39 PM


> Bp,
>
> All I know is that I'm not a senior DBA.   I think you'll know if you
> really are or not.  Have you worked with clustered databases, different
> types of partitioning, different os / io / backup architectures, rman,
> fgac, capacity planning, tuning, etc?  There's a million things you may or
> may not have had exposure to, and while there isn't a magical number where
> you qualify I think it's where you have experienced a lot.
>
> I've worked with people who have been working with Oracle for ~15 years
and
> their understanding of the product, and history of experiences, makes me
> realise how "junior" I am.
>
> Are you asking because you are considering applying for a job or perhaps
> looking for a change to your position description?
>
>
>
>
>
> "BigP"
> 
> tmail.com>cc:
> Sent by:  Subject: senior oracle
dba
> [EMAIL PROTECTED]
>
>
> 22/01/2003 10:05
> Please respond to
> ORACLE-L
>
>
>
>
>
>
> How does one qualify for senior oracel dba. Do you guys have any
> questionare which I can ask myself .
>
> -Bp
>
>
>
<<>>

>Privileged/Confidential information may be contained in this message.
>   If you are not the addressee indicated in this message
>(or responsible for delivery of the message to such person),
> you may not copy or deliver this message to anyone.
> In such case, you should destroy this message and kindly notify the sender
>by reply e-mail or by telephone on (61 3) 9612-6999.
>Please advise immediately if you or your employer does not consent to
> Internet e-mail for messages of this kind.
> Opinions, conclusions and other information in this message
>   that do not relate to the official business of
>  Transurban City Link Ltd
>  shall be understood as neither given nor endorsed by it.
>
<<<>

>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mark 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: BigP
  INET: [EMAIL PROTECTED]

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

Consistency is the key too imagine what would happen if I dropped a
column or changed it's definition, while a SQL statement or PL/SQL package
was executing. The data that was updated before the change may well be very
different in nature than the data after the change

Borrowing from Ghostbusters if I may:

Dr. DBA: This database is about to face a disaster of biblical proportions. 
Hapless Manager: What do you mean, "biblical?" 
Sr. DBA Lead: We mean real wrath-of-God type stuff. Plagues, darkness-- 
Another DBA: The dead rising from the grave! 
Sr. DBA: Forty years of darkness! Earthquakes, volcanoes-- 
Dr. DBA: Riots in the streets, dogs and cats living together, mass hysteria
Manager: So, it's Ok to cut the DBA budget then?

RF

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!



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


Well look at it this way, besides creating/modifying/dropping the object
that you want your also performing a number of updated/inserts/deletes from
the
data dictionary.  Those data dictionary tables are just that, tables.  Now
imagine what a mess would be created if you performed a DDL statement and
then
rolled back the data dictionary updates?  These MUST be completed as a
single
autonomous transaction, so Oracle does you a favor and performs an implicit
commit.  Same thing with any other DBMS I've been associated with.

Dick Goulet

Reply Separator
Author: BanarasiBabu Tippa <[EMAIL PROTECTED]>
Date:   1/22/2003 3:33 AM

Hi friends

Why DDL statements performs auto commit ? What is the exact reason behind
that one?
Anyone can share his/her opinions!!

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

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

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

RE: filemon

Don't forget the other great tools from SysInternals either, like "Regmon"
and "procexpnt".  Great tools that helped me find that my Opera problem
("File not found" errors causing program to crash) is actually MS's really
really cool proxy software.  Crap.  All MS is crap.  Crap, crap, crap.



Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA

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


This may be a useful utility for those of you working with Oracle and NT.
Found it while trying to diagnose issues on an AIX box which also has a
program called filemon.  
 
http://www.sysinternals.com/ntw2k/source/filemon.shtml
-- 
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).

Re: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Vladimir Begun

[EMAIL PROTECTED] wrote:

One potential problem with DBMS_JOBS as is being discussed here is that Oracle
computes the next_date at the end of the job.  They do that so that if a job


-- INTERVAL is a date function, evaluated immediately before the job starts
-- executing...


runs longer than it's schedule interval the two invocations will not run into
each other.  Now as discussed, if the job is scheduled to start at 9:00 AM and
runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it
will creep 5 minutes every time.


Regards,
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

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

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

2003-01-22 Thread Nick Wagner

Title: RE: quest SharePlex





Would the same thing work if you shut down the
Shareplex processes after the row had chained,
and restarted them before you updated the chained
piece ? 
-- Yes


And does Shareplex guarantee that it will
never report a 1555 error regardless of how long it
is shut down ?
-- No, if there are massive amounts of transactions, we can still blow rollback segments, nothing will happen to the source DB instance, but it will effect replication.  Part of the implementation goes through checking the RBS to make sure that we can handle the volume and the amount of activity you are generating.  We have had customers who have shut down SharePlex for 2-3 hours, and when it comes up, replicates everything during those 2-3 hours just fine...  even if it has been moved into the archive logs.  

yes, the supplemental logging is not the greatest thing Oracle ever did, but it was probably one of the easiest ways to implement it.   Just for kicks...  do a couple small batch jobs (maybe 2-3 million row changes) and see how long it takes, and how much redo it generates.  Then do the same thing with Streams replicating that job...  how long does it take, and how much redo is generated.   Streams uses the supplemental logging, and their capture process reads from the redo logs, then puts the transaction right back into the database (in Advanced Queues) which generates more logs, then they dequeue the operation (generating more logs again) to post it to the target machine.   




-Original Message-
From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 22, 2003 12:34 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: quest SharePlex




Very cute - this tends to suggest that Shareplex is
spotting the appearance of chains in the log and
storing the list of rowids.


Would the same thing work if you shut down the
Shareplex processes after the row had chained,
and restarted them before you updated the chained
piece ?  And does Shareplex guarantee that it will
never report a 1555 error regardless of how long it
is shut down ?


Your comment about supplemental logging is
interesting - to me, one of the issues with using
the official method for logical standby is that you
have to have supplemental logging switched on
at the database level. This means, as you have
obviously spotted, that tables without primary
keys get whole rows copied into the log. Worse
still, because supplemental logging is effected
through the UNDO, global temporary tables have
an extra impact on the stream too.



Regards


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


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


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


England__January 21/23
USA_(CA, TX)_August



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






-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 21 January 2003 19:01



>This is actually part of the 'magic' of SharePlex.  The way we obtain
the PK
>information from the database if the PK was not modified is very
tricky.
>
>1) shutdown SharePlex (stop all processes on the source machine, so
>SharePlex is not even up and running)
>2) insert a row.
>3) update that row to cause chaining.
>4) update the row again in the chained piece and don't modify the PK.
>5) delete the row
>6) start SharePlex back up.
>
>Did everything replicate successfully?  Yep.  :)
>




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

AW: Oracle Financials - Help!

2003-01-22 Thread Kulev, Milen

Yes Ron, there is.
For ORACLE FINANCIALS  go to 
http://www.rchath.com/mail_list.htm
or
http://www.oaug.org/public/oaugnet/oaugnet.html
and follow the instructions.


For other Oracle Products:
http://www.odtug.com/subscrib.htm
http://www.doug.org/list.server.html

HTH. Milen 


-Ursprüngliche Nachricht-
Von: Smith, Ron L. [mailto:[EMAIL PROTECTED]]
Gesendet: Mittwoch, 22. Januar 2003 15:44
An: Multiple recipients of list ORACLE-L
Betreff: Oracle Financials - Help!


Is there a forum for Oracle Financials questions?  I am doing backup support
on Oracle Financials and I don't know anything about the product.  The /tmp
file on Unix has filled with a couple of very large temp files.  I need a
way to tie the files back to a process and hopefully a client.  Can anyone
help?

Thanks!
R.Smith
If you are not the intended recipient of this e-mail message, any use,
distribution or copying of the message is prohibited.  Please let me know
immediately by return e-mail if you have received this message by mistake,
then delete the e-mail message. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Smith, Ron L.
  INET: [EMAIL PROTECTED]

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

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

Title: RE: dbms_job - running jobs every 15 minutes



LOL. You are right about the job_scheduler in early 
9i.
Had 
all sorts of problems when I first started moving stuff to
9.0.1 
and 9.0.2 and Oracle was no help figuring out what the
problem was.
 
I use 
cron all the time, but I love the job scheduler for things
like 
starting parallel PL/SQL threads, etc It's just so easy! 
;-)
 
Hey, I 
*LIKE* retro, listen to the 80's station all the time!
 
RF
 
Robert G. FreemanTechnical Management 
ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's 
everywhere that I am!)Author of several books you can find on 
Amazon.com! 

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 
  2003 9:49 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: dbms_job - running jobs every 15 
  minutes
  Robert,
   
  I have solid reasons not to trust dbms_job ... it didn't work reliably 
  in 901x. Call me retro ... but "cron" rocks ...
   
  8:)
  Raj
  __
  Rajendra 
  Jamadagni  
      MIS, ESPN Inc.
  Rajendra dot Jamadagni at ESPN 
  dot com
  Any opinion expressed here is 
  personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, 
  but having an opinion is an 
  art!
  
-Original Message-From: Freeman Robert - IL 
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 
AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
dbms_job - running jobs every 15 minutes
Cron? How 1980's :-))
 
RF

RE: dbms_job - running jobs every 15 minutes

One thing I've learned Arup, there are 15,000 ways of doing the same thing,
and a good many of those are as good as the other. Cron, dbms_job, at,
whatever works for you!!

Rf

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!



-Original Message-
Sent: Wednesday, January 22, 2003 8:55 AM
To: Multiple recipients of list ORACLE-L


That's exactly what I do. Phew! I thought I was the only one ;)

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 8:03 AM


> Personally, I tend to just submit four jobs:  one at the top of hour, one
at
> 15 past, one at 30 past, and the fourth at 45 past.  To alter the
frequency,
> just "break" or remove one or more of the jobs.  Falls into the category
of
> "not elegant, but it works"...
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, January 21, 2003 9:39 PM
>
>
> > How about this... this will start the job at the top of the following
hour
> > and then schedule it every 15 minutes...
> >
> > dbms_job.submit(:jobno,
> > 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'),
> > 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/
hh24''),
> > ''mm/dd/ hh24'') + 15/1440' );
> >
> >
> > -Original Message-
> > [EMAIL PROTECTED]
> > Sent: Tuesday, January 21, 2003 6:24 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Feeling particularly anal the other day,  I used the following
> > specification to
> > run statspack at the top of the hour, 15, 30 and 45 minutes after the
> > hour.
> >
> > variable jobno number;
> > variable instno number;
> > begin
> > select instance_number into :instno from v$instance;
> > dbms_job.submit(
> > :jobno
> > , 'statspack.snap;'
> > -- every 15 minutes at 00,15,30 and 45
> > , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 *
> > floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 ))
> > , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 *
> > floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))'
> > );
> > commit;
> > end;
> > /
> >
> >
> > Seems to me that the time specs could be simplified a bit.
> >
> > Anyone care to give it a go?  :)
> >
> > Jared
> >
> > --
> > 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: Robert Freeman
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (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).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

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

RE: create tablespace problem

2003-01-22 Thread Hately, Mike (NESL-IT)


Where's the challenge when you have the facts?

=)

Mike

-Original Message-
Sent: 22 January 2003 15:05
To: Multiple recipients of list ORACLE-L



Well while we are ranting how about always including the version of the
database, and the platform you are running on too!


RF

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!



**
 
The information contained in this e-mail is confidential and 
intended only for the use of the addressee. If the reader of 
this message is not the addressee, you are hereby notified 
that you have received this e-mail in error and you must not 
copy, disseminate, distribute, use or take any action as a 
result of the information contained in it.

If you have received this e-mail in error, please notify 
[EMAIL PROTECTED] (UK 01384 275454) and delete it 
immediately from your system.

Neither Npower nor any of the other companies in the 
Innogy group from whom this e-mail originates accept any 
responsibility for losses or damage as a result of any viruses 
and it is your responsibility to check attachments (if any) for 
viruses.
Npower Limited
Registered office: Windmill Hill Business Park, Whitehill 
Way, Swindon SN5 6PB. Registered in England and Wales: 
number 3653277
This e-mail may be sent on behalf of a member of the Innogy 
group of companies.
**

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

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

Title: RE: dbms_job - running jobs every 15 minutes



LOL.
 
Ok, I 
confess, my name's Robert and I'm a CRON user
 
Rf
 
Robert G. FreemanTechnical Management 
ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's 
everywhere that I am!)Author of several books you can find on 
Amazon.com! 

  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 
  10:49 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: dbms_job - running jobs every 15 minutes
  Cron?  How RELIABLE !!
  
-Original Message-From: Freeman Robert - IL 
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 
AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
dbms_job - running jobs every 15 minutes
Cron? How 1980's :-))
 
RF
 
Robert G. FreemanTechnical 
Management ConsultantTUSC - The Oracle Experts 
www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of 
several books you can find on Amazon.com! 

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 
  22, 2003 7:19 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: dbms_job - running jobs every 15 
  minutes
  I simplified it by using cron instead ...  
  
  Raj __ 
  Rajendra Jamadagni  
      MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
  opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, but having an 
  opinion is an art! 
  -Original Message- From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
  Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes 
  Feeling particularly anal the other day,  I used the 
  following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after 
  the hour. 
  variable jobno number; variable 
  instno number; begin     select instance_number 
  into :instno from v$instance;     dbms_job.submit( 
      
  :jobno     
  , 'statspack.snap;'     
  -- every 15 minutes at 00,15,30 and 45     
  , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 
  ))     
  , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 
  ))'     
  );     
  commit; end; / 
  Seems to me that the time specs could be simplified a 
  bit. Anyone care to give it a go?  :) 
  Jared

Re: simple question on DDL

2003-01-22 Thread Rajesh . Rao


That raises another doubt. For an simple insert statement, could also
update the UET$ or FET$ tables? So, if the purpose was to preserve all
changes to the data dictionary, What's different between OBJ$, COL$ and
these space management tables?

Thanks
Raj




   
 
"Arup Nanda"   
 

tmail.com>   cc:   
 
Sent by: Subject: Re: simple question on DDL   
 
root@fatcity.  
 
com
 
   
 
   
 
January 22,
 
2003 09:58 AM  
 
Please 
 
respond to 
 
ORACLE-L   
 
   
 
   
 




My guess will be to preserve the changes to the data dictionary, which are
just Oracle tables anyway. When you create a table, a record goes to TAB$,
SEG$ and so on and so forth. Unless there is a commit these information is
not visible.

But now that you asked, I wnder why the same objective couldn't have been
done through autonomous transactions.

Arup

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 6:33 AM


> Hi friends
>
> Why DDL statements performs auto commit ? What is the exact reason behind
> that one?
> Anyone can share his/her opinions!!
>
> Thanks & regards
> BanarasiBabu
> --


-- 
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: Anyone storing their documents in the database with

Better yet -- WHAT is Workspace Management?

"Additional training for Oracle 9i"  -- Goals for 2003  :)

> -Original Message-
> From: Arn Klammer [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, January 21, 2003 12:54 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Anyone storing their documents in the database with
> 
> 
> Robert, 
> 
> Does this mean you're using it?  If so, could you describe 
> your use, and any caveats?
> 
> >>> [EMAIL PROTECTED] 21/1/2003 15:28:47 >>>
> This message has been scanned by MAILSweeper.
> 
> 
> Workspace management - cool feature!
> 
> RF
-- 
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).

RE: Slow SQL*Plus connect.

2003-01-22 Thread Sunil_Nookala

How big is the listener log file?? do you truncate/rename it on regular
basis??
just a thought.

Sunil Nookala
Dell Corp.



-Original Message-
Sent: Wednesday, January 22, 2003 4:09 AM
To: Multiple recipients of list ORACLE-L


Hi All,

We have experienced a *very* slow connect time to a 9.0.1 database via
SQL*Plus (and other apps as well) on a Win2K machine, and I was wondering if
anybody else had experienced these slow connection times as well? We have
also been asked lately by a number of customers about slow connection times,
and to this point haven't found a solution for either ourselves or our
contacts..

It's not a network issue as connection times take just as long "locally".
Connections can take up to around a minute (and the odd occasion a couple of
minutes). No MTS is in use.

OS's that I've heard about this on are Win2K, XP and NT so I'm also
wondering if it may be a Win32 issue.

Help!

Cheers :)

Mark

===
 Mark Leith | T: +44 (0)1905 330 281
 Sales & Marketing  | F: +44 (0)870 127 5283
 Cool Tools UK Ltd  | E: [EMAIL PROTECTED]
===
   http://www.cool-tools.co.uk
   Maximising throughput & performance

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

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

2003-01-22 Thread Koivu, Lisa

Title: RE: dbms_job - running jobs every 15 minutes



Cron?  How RELIABLE !!

  -Original Message-From: Freeman Robert - IL 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 
  AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  dbms_job - running jobs every 15 minutes
  Cron? How 1980's :-))
   
  RF
   
  Robert G. FreemanTechnical 
  Management ConsultantTUSC - The Oracle Experts 
  www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of 
  several books you can find on Amazon.com! 
  
-Original Message-From: Jamadagni, Rajendra 
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 
2003 7:19 AMTo: Multiple recipients of list 
ORACLE-LSubject: RE: dbms_job - running jobs every 15 
minutes
I simplified it by using cron instead ...  

Raj __ 
Rajendra Jamadagni  
    MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion 
is an art! 
-Original Message- From: 
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes 
Feeling particularly anal the other day,  I used the 
following specification to run statspack at the top of the hour, 15, 30 and 45 minutes after the 
hour. 
variable jobno number; variable 
instno number; begin     select instance_number 
into :instno from v$instance;     dbms_job.submit( 
    
:jobno     
, 'statspack.snap;'     
-- every 15 minutes at 00,15,30 and 45     
, trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) 
    
, 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 
))'     
);     
commit; end; / 
Seems to me that the time specs could be simplified a 
bit. Anyone care to give it a go?  :) 
Jared

Re:simple question on DDL

2003-01-22 Thread dgoulet

Well look at it this way, besides creating/modifying/dropping the object
that you want your also performing a number of updated/inserts/deletes from the
data dictionary.  Those data dictionary tables are just that, tables.  Now
imagine what a mess would be created if you performed a DDL statement and then
rolled back the data dictionary updates?  These MUST be completed as a single
autonomous transaction, so Oracle does you a favor and performs an implicit
commit.  Same thing with any other DBMS I've been associated with.

Dick Goulet

Reply Separator
Author: BanarasiBabu Tippa <[EMAIL PROTECTED]>
Date:   1/22/2003 3:33 AM

Hi friends

Why DDL statements performs auto commit ? What is the exact reason behind
that one?
Anyone can share his/her opinions!!

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

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

2003-01-22 Thread Jonathan Lewis


One reason for seeing more db_block_gets
than you expect on a full segment scan
is that every extent map block (of which
the segment header block is just one)
is accessed differently.  If you have small
extents and large objects, you may have
overshoot the maximum extents allowed
in the segment header block - so the 12
db_block_gets could indicate 2 extra
map blocks.

NOTE - Oracle 9 simply does not do
4 CU gets on map blocks.

8.14 CU gets per map block
9.22 CR gets per map block
9.02 gets, but can't remember if they are CU or CR

Also - bigger tables => more map blocks may have been used


Regards

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

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

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

England__January 21/23
USA_(CA, TX)_August


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





-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 22 January 2003 15:47
version


Jonathan Lewis,
Thanks very much for your reply. Yes, in oracle 8i on sun solaris, the
db block gets of fulltable scan is always 4block, no matter what the
size of the table is. But in my tests in linux/817, it always show
12(8170 and 8172).I run the full scan again and again, but the value
does not change.
And another problem is why in oracle9i, there is no db_block_gets when
doing full segment scan.I checked asktom.oracle.com, but unable to
find the answer. And i also tested 9.0.1 on sun solaris , when very
small, db_block_gets is 6,while larger tables(12K records from
dba_tables), db_block_gets is 12.
Scanning the segment header caused the current mode read,is it because
of scanning the buffer pool head and getting the cache buffer chains
latch in exclusive mode,and the buffer head will be moved the other
end lru list? why scanning the other data block does not cause the
current read? Sorry, I asked to much, and maybe they are meanless, but
if possible, I still want to know.

Sony kristanto, what kind of parameters do you think will affect the
db_block_gets? I will post them.
Thanks.



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

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

Re:RE: dbms_job - running jobs every 15 minutes

2003-01-22 Thread dgoulet

One potential problem with DBMS_JOBS as is being discussed here is that Oracle
computes the next_date at the end of the job.  They do that so that if a job
runs longer than it's schedule interval the two invocations will not run into
each other.  Now as discussed, if the job is scheduled to start at 9:00 AM and
runbs for 5 minutes it's next_date for run #2 will be 9:20, not 9:15, and it
will creep 5 minutes every time.

Dick Goulet

Reply Separator
Author: Freeman Robert - IL <[EMAIL PROTECTED]>
Date:   1/22/2003 7:09 AM

Cron? How 1980's :-))
 
RF
 

Robert G. Freeman
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com! 

-Original Message-
Sent: Wednesday, January 22, 2003 7:19 AM
To: Multiple recipients of list ORACLE-L



I simplified it by using cron instead ...  

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

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


-Original Message- 
 ] 
Sent: Tuesday, January 21, 2003 7:24 PM 
To: Multiple recipients of list ORACLE-L 


Feeling particularly anal the other day,  I used the following 
specification to 
run statspack at the top of the hour, 15, 30 and 45 minutes after the 
hour. 

variable jobno number; 
variable instno number; 
begin 
select instance_number into :instno from v$instance; 
dbms_job.submit( 
:jobno 
, 'statspack.snap;' 
-- every 15 minutes at 00,15,30 and 45 
, trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * 
floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) 
, 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * 
floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' 
); 
commit; 
end; 
/ 

Seems to me that the time specs could be simplified a bit. 
Anyone care to give it a go?  :) 
Jared 





RE: dbms_job - running jobs every 15 minutes



Cron? 
How 1980's :-))
 
RF
 
Robert G. FreemanTechnical Management

ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's 
everywhere that I am!)Author of several books you can find on 
Amazon.com! 

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 
  2003 7:19 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: dbms_job - running jobs every 15 
  minutes
  I simplified it by using cron instead ...  

  Raj __ Rajendra Jamadagni  
      MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
  opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, but having an opinion 
  is an art! 
  -Original Message- From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]";>mailto:[EMAIL PROTECTED]] 
  Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes 
  Feeling particularly anal the other day,  I used the 
  following specification to run

  statspack at the top of the hour, 15, 30 and 45 minutes after the 
  hour. 
  variable jobno number; variable instno

  number; begin     select instance_number into 
  :instno from v$instance;     dbms_job.submit( 
  
;    
  :jobno 
;    
  , 'statspack.snap;' 
;    
  -- every 15 minutes at 00,15,30 and 45 
;    
  , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) 
  
;    
  , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))'

      ); 
      commit; 
  end; / 
  Seems to me that the time specs could be simplified a 
  bit. Anyone care to give it a go?  :) 
  Jared 

-- 
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: off topic: iostat -x in linux

2003-01-22 Thread Gogala, Mladen

The best you can get on linux is sar -d.

> -Original Message-
> From: chao_ping [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 22, 2003 2:39 AM
> To: Multiple recipients of list ORACLE-L
> Subject: off topic: iostat -x in linux
> 
> 
> oracledba??
>   dba friends, do you know how to measure the io capacity 
> usage(%busy) in linux server, like solaris:
> 
>extended device statistics
> devicer/s  w/s   kr/s   kw/s wait actv  svc_t  %w  %b 
> sd6   0.0  0.00.00.0  0.0  0.00.0   0   0 
> sd9   0.0  0.00.00.0  0.0  0.00.0   0   0 
> sd10  0.0  0.00.00.0  0.0  0.00.0   0   0 
> ssd2  0.0 10.00.0   22.0  0.0  0.01.8   0   2 
> ssd3  0.0  0.00.00.0  0.0  0.00.0   0   0 
> ssd4 51.0  0.0  408.00.0  0.0  0.5   10.1   0  41 
> ssd5  0.0  0.00.00.0  0.0  0.00.0   0   0 
> nfs1  0.0  0.00.00.0  0.0  0.00.0   0   0 
> ^C
> 
>   In linux, it seems difficult, I have tried linux iostat 
> and sysstat-iostat, non of them seems to work properly with 
> util column in iostat report.  Though no activity on the 
> server at all, iostat always report utilization of the 
> partitions 100%  Why?
> I also searched google and found the same question asked 
> by others, but nobody replied. 
> 
>   Anyone have the same experience? How did you measure 
> the load on the io subsystem in your linux server then? IO 
> wait is still 0%, but io capacity usage is 100%. Confusing...
> 
> 
> avg-cpu: %user %nice %sys %iowait %idle 
> 3.00 0.00 0.00 0.00 97.00 
> 
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s 
> avgrq-sz avgqu-sz await svctm %util 
> /dev/sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 
> 42941682.96 0.00 0.00 100.00 
> /dev/sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 
> 42949662.96 0.00 0.00 100.00 
> /dev/sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10.00 
> 0.00 0.00 100.00 
> /dev/sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 
> 42949652.96 0.00 0.00 100.00 
> /dev/sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 
> 0.00 0.00 0.00 
> /dev/sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 
> 0.00 0.00 0.00 
> /dev/sda7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 
> 42948232.96 0.00 0.00 100.00 
> 
> avg-cpu: %user %nice %sys %iowait %idle 
> 2.00 0.00 0.50 0.00 97.50 
> 
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s 
> avgrq-sz avgqu-sz await svctm %util 
> /dev/sda 0.00 2.00 1.00 4.00 8.00 48.00 4.00 24.00 11.20 
> 42941683.06 2.00 200.00 100.00 
> /dev/sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 
> 42949662.96 0.00 0.00 100.00 
> /dev/sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 10.00 
> 0.00 0.00 100.00 
> /dev/sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 
> 42949652.96 0.00 0.00 100.00 
> /dev/sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 
> 0.00 0.00 0.00 
> /dev/sda6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 
> 0.00 0.00 0.00 
> /dev/sda7 0.00 2.00 1.00 4.00 8.00 48.00 4.00 24.00 11.20 
> 42948233.06 2.00 200.00 100.00 
> 
>   
> 
> Regards
> zhu chao
> msn:[EMAIL PROTECTED]
> www.happyit.net
> www.cnoug.org(China Oracle User Group)
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: chao_ping
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: Gogala, Mladen
  INET: [EMAIL PROTECTED]

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

Shared Pool - How much free memory?

2003-01-22 Thread Rajesh . Rao

I have this application which issues a lot of dynamic SQL's with literals,
for each customer who logs in. And on querying v$sgastat, I see 21Mb of
free memory. Does shared_pool_reserved_size show up as "free memory" in
v$sgastat? If not, I might have some further questions?

Thanks
Raj

-- 
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: dbms_job - running jobs every 15 minutes

2003-01-22 Thread Jamadagni, Rajendra

Title: RE: dbms_job - running jobs every 15 minutes



Robert,
 
I have solid reasons not to trust dbms_job ... it didn't work reliably in 
901x. Call me retro ... but "cron" rocks ...
 
8:)
Raj
__
Rajendra 
Jamadagni  
    MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot 
com
Any opinion expressed here is 
personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but 
having an opinion is an art!

  -Original Message-From: Freeman Robert - IL 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 10:10 
  AMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  dbms_job - running jobs every 15 minutes
  Cron? How 1980's :-))
   
  RF
This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

Re: create tablespace problem


And when your problem is finally resolved, even if it's as magical as the
passage of time, please... please... let the us know that. This is a
educational excercise for all concerned and everyone is interested in the
analysis and the final outcome. Don't you think you owe that much to the
people who mulled over your problem and offered suggestions?


Feeling a little better!


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 8:18 AM


> 
> I have a problem understanding why anyone would ask a question and then
not
> display the exact error number and error message text they are seeing.
> There are thousands of error messages in Oracle and the platforms it runs
> upon and the APIs it supports, and the text associated with the number is
> practically useless, due to differences in local language, problems with
> recollection, sloppy wording, etc.
>
> For heavens sake people:  please include the error number too!  Not just
> your vague recollection of the wording of the message, not just some
edited
> version of the error text, not just the code throwing the error.  The
> number, the number, the blasted number!!!  It's the only sure way to
> precisely identify what has happened, and people just throw it away...
> 
>
> ...breathe...breeaatthhhe..oom.ooommmAh!
> Better now?  Nopeoo.ah-om.
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, January 21, 2003 11:53 PM
>
>
> > I'm have problem when execute this script, the error is show by oracle
is
> > "error at line 1
> > ORA-...:TSelCZ_Data already exists;
> >
> >
> > -- create tablespace
> >
> > CREATE TABLESPACE TSelCZ_Data DATAFILE
'd:\orant\database\tselcz1dbCZ.ora'
> > SIZE 150M;
> > ALTER TABLESPACE "TSELCZ_DATA" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
> > /
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: pomi
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (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).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

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

RE: dbms_job - running jobs every 15 minutes

Title: RE: dbms_job - running jobs every 15 minutes



Cron? 
How 1980's :-))
 
RF
 
Robert G. FreemanTechnical Management 
ConsultantTUSC - The Oracle Experts www.tusc.com904.708.5076 Cell (it's 
everywhere that I am!)Author of several books you can find on 
Amazon.com! 

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 
  2003 7:19 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: dbms_job - running jobs every 15 
  minutes
  I simplified it by using cron instead ...  

  Raj __ Rajendra Jamadagni  
      MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
  opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, but having an opinion 
  is an art! 
  -Original Message- From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
  Sent: Tuesday, January 21, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Subject: dbms_job - running jobs every 15 minutes 
  Feeling particularly anal the other day,  I used the 
  following specification to run 
  statspack at the top of the hour, 15, 30 and 45 minutes after the 
  hour. 
  variable jobno number; variable instno 
  number; begin     select instance_number into 
  :instno from v$instance;     dbms_job.submit( 
      
  :jobno     
  , 'statspack.snap;'     
  -- every 15 minutes at 00,15,30 and 45     
  , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 )) 
      
  , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))' 
      ); 
      commit; 
  end; / 
  Seems to me that the time specs could be simplified a 
  bit. Anyone care to give it a go?  :) 
  Jared

Re: create tablespace problem

Pomi,

This file alrerady exists; and so is the error. You have two options.

(1) Just add a clause REUSE after the datafile name. Oracle will reuse the
existing file. Not recomended. You might accidentally overwrite a good file.
(2) Find out the datafile does not belong to the database
select name from v$datafile where name = 'd:\orant\database\tselcz1dbCZ.ora'
; should not return any row and delete the file in the OS. Then create the
tablespace.

I assume you had this tablespace before and then dropped it later. If you
have Oracle9i, you would do a DROP TABLESPACE INCLUDING CONTENTS AND
DATAFILE to drop the tablespace as well as the file.

HTH.

Arup

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 1:53 AM


> I'm have problem when execute this script, the error is show by oracle is
> "error at line 1
> ORA-...:TSelCZ_Data already exists;
>
>
> -- create tablespace
>
> CREATE TABLESPACE TSelCZ_Data DATAFILE 'd:\orant\database\tselcz1dbCZ.ora'
> SIZE 150M;
> ALTER TABLESPACE "TSELCZ_DATA" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
> /
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: pomi
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

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

RE: create tablespace problem


Well while we are ranting how about always including the version of the
database, and the platform you are running on too!


RF

Robert G. Freeman 
Technical Management Consultant
TUSC - The Oracle Experts www.tusc.com
904.708.5076 Cell (it's everywhere that I am!)
Author of several books you can find on Amazon.com!



-Original Message-
Sent: Wednesday, January 22, 2003 7:19 AM
To: Multiple recipients of list ORACLE-L



I have a problem understanding why anyone would ask a question and then not
display the exact error number and error message text they are seeing.
There are thousands of error messages in Oracle and the platforms it runs
upon and the APIs it supports, and the text associated with the number is
practically useless, due to differences in local language, problems with
recollection, sloppy wording, etc.

For heavens sake people:  please include the error number too!  Not just
your vague recollection of the wording of the message, not just some edited
version of the error text, not just the code throwing the error.  The
number, the number, the blasted number!!!  It's the only sure way to
precisely identify what has happened, and people just throw it away...


...breathe...breeaatthhhe..oom.ooommmAh!
Better now?  Nopeoo.ah-om.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, January 21, 2003 11:53 PM


> I'm have problem when execute this script, the error is show by oracle is
> "error at line 1
> ORA-...:TSelCZ_Data already exists;
>
>
> -- create tablespace
>
> CREATE TABLESPACE TSelCZ_Data DATAFILE 'd:\orant\database\tselcz1dbCZ.ora'
> SIZE 150M;
> ALTER TABLESPACE "TSELCZ_DATA" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
> /
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: pomi
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Freeman Robert - IL
  INET: [EMAIL PROTECTED]

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

Re: simple question on DDL

My guess will be to preserve the changes to the data dictionary, which are
just Oracle tables anyway. When you create a table, a record goes to TAB$,
SEG$ and so on and so forth. Unless there is a commit these information is
not visible.

But now that you asked, I wnder why the same objective couldn't have been
done through autonomous transactions.

Arup

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 6:33 AM


> Hi friends
>
> Why DDL statements performs auto commit ? What is the exact reason behind
> that one?
> Anyone can share his/her opinions!!
>
> Thanks & regards
> BanarasiBabu
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: BanarasiBabu Tippa
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

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

Re: Oracle 9.2.0.2 performance problem

2003-01-22 Thread Hemant K Chitale


Presuming that PGA_AGGREGATE_TARGET of 500MB is sufficient
for your environment 
... it does look as if your PA-RISC processors are slower than the Intel ones
but ... I still wonder..
your statistics show that the number of block-gets for the FTS isn't very high
and cpu time is high.

If you can, try taking it out of automatic PGA management and set an
explicit SORT_AREA_SIZE [run the query with different SORT_AREA_SIZES].
Also watch the NEXT_EXTENT size of your Temporary Tablespace.
Hemant

At 03:13 AM 22-01-03 -0800, you wrote:


Hello

650 Mhz processors are HP pa RISC processors.
We use automatic PGA management. There are 500MB PGA.
sort_area_size have the dafault value because under automatic PGA
there is not used (or not?).

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, January 21, 2003 10:59 PM


> Hemant raises a valid point...
>
> You could just do a "select blah from dw.supuestos" in SQL*Plus (or some
> other client that retrieves all results without pausing) to get an idea of
> how much effort the group by is taking.
>
> My other question relates to the CPU's.  If I'm reading correctly the old
> server had 2 * 1.4GHz CPU's and the new server has 2 * 650MHz CPU's.  I
> realise the CPU's most likely aren't the same architecture (sorry - I know
> nothing about HP's range of machines) but you aren't comparing apples to
> apples are you?
>
> It sounds like you have changed Oracle version, CPU architecture, IO
> subsystem and OS all at the same time.  There is a multitude of
> possibilities.  Have you compared the init.ora files (Hemant's
> SORT_AREA_SIZE recommendation is a good one).
>
>
>
>
> Hemant K
> Chitale   To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
>  et.com.sg>Subject: Re: Oracle 9.2.0.2
performance problem
> Sent by:
> [EMAIL PROTECTED]
> m
>
>
> 22/01/2003
> 00:49
> Please respond
> to ORACLE-L
>
>
>
>
>
>
>
> Wonder if it is the SORT (for the GroupBy) taking time ?
> What is the SORT_AREA_SIZE and what are the INITIAL and NEXT
> extents of the user's temporary tablespace ?
> Are the tablespaces Locally-Managed and the temporary tablespace
> a TEMPORARY TABLESPACE with a TEMPFILE ?
>
> Hemant
>
> At 01:59 AM 20-01-03 -0800, you wrote:
>  Hello
>
>  We have an serious performance problem on a DSS db.
>  We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11
>
>  Oracle 9.2.0.2 tooks 30 min doing this query where an  Intel 2x1,4
Ghz
>  tooks 9 min only.
>
>  We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB),
>  fast i/O (EMC Clariom CX600)...
>  We try lost of parameters, but time is always the same.
>
>  Is there some bug in this release - platform ?
>  How can I get more data about this problem??
>
>  Thanks.
>
>  SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4,
> evpanc,evpgru,evpcli,evppai,evppro,evpume,
> to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA,
> sum(evppca) PPTO
>  FROM DW.SUPUESTOS
>  GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4,
> evpanc,evpgru,evpcli,evppai,evppro,evpume,
> evpano, evpmes
>
>  call count   cpuelapsed   disk  querycurrent
>  rows
>  --- --   -- -- -- --
>  --
>  Parse1  0.01   0.00  0  0  0
>  0
>  Execute  1  0.00   0.00  0  0  0
>  0
>  Fetch   445920   1748.651708.72   1554   1675 23
>  445919
>  --- --   -- -- -- --
>  --
>  total   445922   1748.661708.72   1554   1675 23
>  445919
>
>  Misses in library cache during parse: 1
>  Optimizer goal: CHOOSE
>  Parsing user id: 90 (recursive depth: 1)
>
>
>  Execution Plan
>  --
> 0  SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 Card=464215
Byt
>es=32495050)
>
> 10   SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050)
> 21 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162
Card=464215
> Bytes=32495050)
>
>  Statistics
>  --
>0  recursive calls
>   31  db block gets
> 1675  consistent gets
> 1577  physical reads
>0  redo size
>  9012743  bytes sent via SQL*Net to client
>   208363  bytes received via SQL*Net from client

Re: same sql: different db block gets in different oracle version

Cho,

This is not unusual. I suspect your db_cache_size (or db_block_buffers) in
9.2 is more than db_block_buffers in 8.1. When you do a FTS, all the blocks
are loaded into the buffers first time so 'db block gets' is a non zero
value (it's the physical IO). The next time you do this, the blocks are
found in the buffer already, so there is no physical IO, hence a zero value
for 'db block gets'. However, your logical reads (consistent gets) should be
the same and they are the same. In 817, run the query once again and see the
'db block gets' , it should be 0. If not then check if db_block_buffers is
less than db_cache_size (or db_block_buffers) in 9.2.

HTH.

Arup

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 1:53 AM


> Hi, dba friends:
>
> Look at the following test result, the first one is from oracle9.2, and
the second is from the oracle 817. The table is same, and as you see, the
execution path is the same.
> But there is difference in statistics: db_block_gets, in oracle92, it is
0, and in oracle817, it is not.
> And i noticed that in oracle8i, all sql that does only query with
execution path full scan of table/index,there will always be db_block_gets,
while in 9i, select won't make db_block_gets.
> As tom said, db_block_gets is increased when data is accessed for update,
how does this query generate this statistics?
>
>
> ORA92> select count(*) from abc;
>
>   COUNT(*)
> --
>  1
>
> Execution Plan
> --
>0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1)
>1 0 SORT (AGGREGATE)
>2 1 TABLE ACCESS (FULL) OF 'ABC' (Cost=6 Card=1)
>
> Statistics
> --
>   0 recursive calls
>   0 db block gets
>  27 consistent gets
>   0 physical reads
>   0 redo size
> 379 bytes sent via SQL*Net to client
> 503 bytes received via SQL*Net from client
>   2 SQL*Net roundtrips to/from client
>   0 sorts (memory)
>   0 sorts (disk)
>   1 rows processed
>
> ORA817> select count(*) from abc;
>
>   COUNT(*)
> --
>  1
>
> Elapsed: 00:00:00.26
>
> Execution Plan
> --
>0 SELECT STATEMENT Optimizer=CHOOSE
>1 0 SORT (AGGREGATE)
>2 1 TABLE ACCESS (FULL) OF 'ABC'
>
> Statistics
> --
>   0 recursive calls
>  12 db block gets
>  28 consistent gets
>  24 physical reads
>   0 redo size
> 367 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
>   2 SQL*Net roundtrips to/from client
>   0 sorts (memory)
>   0 sorts (disk)
>   1 rows processed
>
>
> Regards
> zhu chao
> msn:[EMAIL PROTECTED]
> www.happyit.net
> www.cnoug.org(China Oracle User Group)
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: chao_ping
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

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

Re: dbms_job - running jobs every 15 minutes

That's exactly what I do. Phew! I thought I was the only one ;)

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 8:03 AM


> Personally, I tend to just submit four jobs:  one at the top of hour, one
at
> 15 past, one at 30 past, and the fourth at 45 past.  To alter the
frequency,
> just "break" or remove one or more of the jobs.  Falls into the category
of
> "not elegant, but it works"...
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, January 21, 2003 9:39 PM
>
>
> > How about this... this will start the job at the top of the following
hour
> > and then schedule it every 15 minutes...
> >
> > dbms_job.submit(:jobno,
> > 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'),
> > 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/
hh24''),
> > ''mm/dd/ hh24'') + 15/1440' );
> >
> >
> > -Original Message-
> > [EMAIL PROTECTED]
> > Sent: Tuesday, January 21, 2003 6:24 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Feeling particularly anal the other day,  I used the following
> > specification to
> > run statspack at the top of the hour, 15, 30 and 45 minutes after the
> > hour.
> >
> > variable jobno number;
> > variable instno number;
> > begin
> > select instance_number into :instno from v$instance;
> > dbms_job.submit(
> > :jobno
> > , 'statspack.snap;'
> > -- every 15 minutes at 00,15,30 and 45
> > , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 *
> > floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 ))
> > , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 *
> > floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))'
> > );
> > commit;
> > end;
> > /
> >
> >
> > Seems to me that the time specs could be simplified a bit.
> >
> > Anyone care to give it a go?  :)
> >
> > Jared
> >
> > --
> > 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: Robert Freeman
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (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).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

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

Re: question about rman

2003-01-22 Thread Ruth Gramolini

Title: question about rman



I am jumping in late on this thread and may be way off the 
mark but I do all my backups to disk and put them on tape using the OS backup 
facility.  When recovery is needed I just put the backupsets back on the 
original disk.  Then rman isn't confused at all.  If omniback can put 
the files onto tape at a later time, you can still do backup whenever you 
want.  I work for the State of Vermont, and budget contraints force us to 
find the least expensive solution.  Rman, tape, and the OS backup software 
are the cheapest way and it works.  I have recovered many times using this 
method.  
 
Ruth

  - Original Message - 
  From: 
  Chris 
  Stephens 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Tuesday, January 21, 2003 3:04 
  PM
  Subject: RE: question about rman
  
  The 
  problem is that if we try and backup to tape, we have to wait in the 
  queue.  Depending on what is already there, we may have to wait a number 
  of hours before RMAN has the opportunity write data to the tape.  Believe 
  me, we have tried to devise ways to predict when we could actually write to 
  tape but the sys admin has stated over and over that he has no control over 
  when a tape is available due to the queueing.  In a cold backup this 
  implies unacceptable downtime.  In a hot backup it means that the 
  database will be backed up over a very long period of 
time.
   
  So 
  if we first backup to disk and then let omniback take the data to tape 
  whenever it gets around to it, we'll still be able to due a recovery through 
  RMAN if the necessary files are on tape (a place that RMAN is unaware 
  of).
   
  note: obviously we are going to have to test this but 
  i'd like to know that it is possible before i go through the trouble of 
  coordinating something with the sys admins.
   
  thank you.
   
   
  
-Original Message-From: Freeman Robert - IL 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 21, 2003 1:31 
PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
question about rman
I 
guess my first question is why not just backup to tape through the Omniback 
MML layer? Or, if you prefer to go to disk you can backup to disk with RMAN 
and then backup the backupset to tape through the MML with RMAN again. 
If you try to restore from an RMAN backup, and the required backup pieces 
are not there, RMAN will let you know.
 
HTH,
 
Robert
 
Robert G. FreemanTechnical 
Management ConsultantTUSC - The Oracle Experts 
www.tusc.com904.708.5076 Cell (it's everywhere that I am!)Author of 
several books you can find on Amazon.com! 

  -Original Message-From: Chris Stephens 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 21, 
  2003 10:45 AMTo: Multiple recipients of list 
  ORACLE-LSubject: question about rman
  I could test this myself but that would require 
  coordination with the system administrators and they always seem to think 
  their time is too important for testing. :)
  i would like to implement RMAN in and 8.1.7.2 oltp 
  environment and 9.2.0 warehouse environment.  We use Omniback as an 
  enterprise wide backup solution.  Don't know if this is version 
  specific so I thought I would mention.  Here's the 
  question
  Currently we are doing os level backups for both 
  environments.  We take the backup to disk and then omniback backs up 
  the mountpoints we write to.  A problem we have is that we are unable 
  to predict when omniback will get to our backup mountpoints
  due to the fact that there is a queue that serializes our 
  backups and other servers are place in that queue ahead of us.  This 
  is a factor in our warehouse environment as it makes the downtime of a 
  cold backup unpredictable.  In the oltp environment this makes the 
  length of backups unpredictable and GREATLY increases redo 
  generation.  The only option we have is to first backup to 
  disk.
  So here's my problem with RMAN...if we backup to disk 
  through rman and then omniback takes that data to tape and the backup 
  mountpoints are overwritten the very next time a backup is taken then 
  RMAN's ability to find the necessary files is compromised if the 
  redundancy is set to anything >1 (or whatever recovery window the 
  translates to).  So if I try and restore a database in either 
  environment and RMAN can't find the necesary file(s) will it return a 
  message asking where those files are?  ...at which point i would call 
  the sys admins to restore the particular file.  or would i be 
  hosed?
  thank you very much for any responses. (i plan on buying 9i rman soon but didn't want to wait on 
  this) 
  chris 
  (wishing for dedicated tape drives)

RE: Slow SQL*Plus connect.

2003-01-22 Thread Stephen Lee


If the box is swapping memory, connections can be slow since memory has to
allocated for the connection.  Just one possibility.

> -Original Message-
> 
> We have experienced a *very* slow connect time to a 9.0.1 database via
> SQL*Plus (and other apps as well) on a Win2K machine,
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  INET: [EMAIL PROTECTED]

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

Re: Re: same sql: different db block gets in different oracle version

2003-01-22 Thread chao_ping

Jonathan Lewis,
Thanks very much for your reply. Yes, in oracle 8i on sun solaris, the 
db block gets of fulltable scan is always 4block, no matter what the size of the table 
is. But in my tests in linux/817, it always show 12(8170 and 8172).I run the full scan 
again and again, but the value does not change.
And another problem is why in oracle9i, there is no db_block_gets when 
doing full segment scan.I checked asktom.oracle.com, but unable to find the answer. 
And i also tested 9.0.1 on sun solaris , when very small, db_block_gets is 6,while 
larger tables(12K records from dba_tables), db_block_gets is 12.  
Scanning the segment header caused the current mode read,is it because 
of scanning the buffer pool head and getting the cache buffer chains latch in 
exclusive mode,and the buffer head will be moved the other end lru list? why scanning 
the other data block does not cause the current read? Sorry, I asked to much, and 
maybe they are meanless, but if possible, I still want to know. 

Sony kristanto, what kind of parameters do you think will affect the 
db_block_gets? I will post them.
Thanks.




Regards
zhu chao
msn:[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(China Oracle User Group)

=== 2003-01-22 00:33:00 ,you wrote£º===

>Tablescans in Oracle 8 start with 4 current
>gets (db block gets) on the segment header
>block - in Oracle 9 tablescans start with
>2 consistent gets on the segment header.
>
>I can't tell you why you have 12 current gets
>rather than 4 - but if the tests aren't identical,
>the physical reads and extra current gets may
>be related to delayed cleanout on a first read.
>
>
>
>Regards
>
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Coming soon a new one-day tutorial:
>Cost Based Optimisation
>(see http://www.jlcomp.demon.co.uk/tutorial.html )
>
>Next Seminar dates:
>(see http://www.jlcomp.demon.co.uk/seminar.html )
>
>England__January 21/23
>USA_(CA, TX)_August
>
>
>The Co-operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
>-Original Message-
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Date: 22 January 2003 07:42
>
>


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

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

2003-01-22 Thread Mark Leith

Hi Sean

The following site has *some* info about your problem, though the "fix"
isn't too great an explanation, and you also mentioned that you had no
errors in your alert log:

http://www.orafaq.net/archive/comp.databases.oracle.server/2002/03/03/137294
.htm

M.

-Original Message-
Sean
Sent: 22 January 2003 12:27
To: Multiple recipients of list ORACLE-L


Recently we've had three of our 8.1.7 databases on W2K servers crash with
messages similar to below in relevant alert logs:

ORA-00202: controlfile: ''
ORA-27091: skgfqio: unable to queue I/O
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 1453) Insufficient quota to complete the requested service.

Instance was terminated by CKPT.

I've had a good trawl through Metalink and basically the postings suggest
it's down to one of following:
[1] Quota management enabled on disks.  [We don't have!]
[2] Possibly running low on physical and/or virtual memory.  Nothing written
to event logs on servers to indicate this was the case.

If anyone else out there experienced the same problem AND managed to
eliminate it I would appreciate your feedback.  Heck, I'm open to
suggestions on troubleshooting this one.

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



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

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

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

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

2003-01-22 Thread Markus Reger

hi
what's your exact question?
you mean autocommit like in database applications?
what tool are you using to observe or redard to this phenomenon?

the erd-demon has to send some info via rs232 to make the amplifier -called booster - 
work. no info implies no current on the tracks. kind of answwer you want?

kr mr



>>> [EMAIL PROTECTED] 01/22/03 13:03 PM >>>
Hi friends

Why DDL statements performs auto commit ? What is the exact reason behind
that one?
Anyone can share his/her opinions!!

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

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

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

Oracle Financials - Help!

2003-01-22 Thread Smith, Ron L.

Is there a forum for Oracle Financials questions?  I am doing backup support
on Oracle Financials and I don't know anything about the product.  The /tmp
file on Unix has filled with a couple of very large temp files.  I need a
way to tie the files back to a process and hopefully a client.  Can anyone
help?

Thanks!
R.Smith
If you are not the intended recipient of this e-mail message, any use,
distribution or copying of the message is prohibited.  Please let me know
immediately by return e-mail if you have received this message by mistake,
then delete the e-mail message. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Smith, Ron L.
  INET: [EMAIL PROTECTED]

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

2003-01-22 Thread Jamadagni, Rajendra

Title: RE: dbms_job - running jobs every 15 minutes





I simplified it by using cron instead ... 


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



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 21, 2003 7:24 PM
To: Multiple recipients of list ORACLE-L
Subject: dbms_job - running jobs every 15 minutes



Feeling particularly anal the other day,  I used the following 
specification to
run statspack at the top of the hour, 15, 30 and 45 minutes after the 
hour.


variable jobno number;
variable instno number;
begin
    select instance_number into :instno from v$instance;
    dbms_job.submit(
    :jobno
    , 'statspack.snap;'
    -- every 15 minutes at 00,15,30 and 45
    , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 * 
floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 ))
    , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 * 
floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))'
    );
    commit;
end;
/


Seems to me that the time specs could be simplified a bit.
Anyone care to give it a go?  :)
Jared



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

Database Load & Server Specs

2003-01-22 Thread Hussain Ahmed Qadri

Title: Database Load & Server Specs

Hi all,
I have three questions, would appreciate if any of you can help me on that, its important because I need these answers to support my claim of our server being over loaded.

First is; is there query to find out the number of transactions at any time? V$transaction tells about the time the query has been running for, but how many transactions are running at peak time? Does the number of transaction have any bearing on what the server specification should be like?

Second; what should be the specifications of a server, based on the size of the database or load? Are there any norms for this, or a matrix, which would help me see the specs based on load, or if any of you can share your experience?

(Our environment is OLTP, round the clock, 24x7, size of the physical database is 15GB, and growing and the actual dump size is less though, around 5GB. We are using only one server, single processor 933MHz, 896MB ram, and 2 SCSI hard drives. Our application is module based, and since we made part of our last module online, the load has increased tremendously as it involves constant entry, reporting, and on screen viewing. I mean on WinNT, the CPU usage touches (for a few seconds thankfully) 100% every minute or two, which does make the system go a little slow every now and then.

If the configuration seems ok, which parameters can I consider revising/increasing to enhance performance?)

Third; since our is a critical hospital application, 24x7 environment, running on one server, with periodic Tape backups and one standby database, as our means to fall back on. Does that sound Ok? Or do we need a couple of servers, same specs, go for operating system clustering, with a storage unit housing a few Hard disks?

I know it's a very long query, but would really appreciate if I can have some guidelines.

Thanks and regards,

Hussain
DBA

SKMCH&RC

Re: Online recompilation of SP




Short answer is no.  Can't think of an 
environment where compiled objects can be recreated without quiescence, so its 
not just Oracle.
 
You should use PL/SQL packages instead of 
stored procedures or functions so that the "body" of the 
package (containing code) can be disassociated and recompiled 
separately from the "header" or "public specification" section of the 
package.  This way, you can recompile just the package body without 
cascading invalidations (due to the recompile) throughout other 
referencing stored objects (i.e. views, triggers, other packages, 
procedures, functions).  Only the header references the body, and it is not 
invalidated when the body is recompiled.  All other stored objects 
reference the header only, so they will be invalidated only if the header is 
recompiled, not the body...
 
In a compiled environment, the only thing you can 
do is minimize the period of quiescence (i.e. downtime) using redundancy.  
Two schemas in the same database, two databases within the same environment, 
etc...
 
Anyone know of a better way?

  - Original Message - 
  From: 
  [EMAIL PROTECTED] 
  
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Tuesday, January 21, 2003 11:28 
  PM
  Subject: Online recompilation of SP
  Dear all, Is there any means of changing the body of a SP and 
  recompiling it while there "could" 
  possibly be some sessions accessing 
  it. We have such a req in our appln wherein the recreation shouldn't affect ongoing applns that might access the same SP. T I A.Best 
RegardsJai

RE: There is a problem with your message - O_S - To Kirti

2003-01-22 Thread Deshpande, Kirti

Remove *all*  footers (telling you how to subscribe/unsubscribe) added by Oracle-l 
list server.
Sorry about that. 

- Kirti 

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 22, 2003 6:34 AM
To: Multiple recipients of list ORACLE-L
Deshpan


Hi Kirti,

I am unable to send a message to you privately.  Whenever I try to
send a message to you it bounces back with the below forwarded message.  How
do I send a message to you privately?  Please help.

Thanks and Regards,

Ranganath

> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, January 22, 2003 5:57 PM
> To:   Krishnaswamy, Ranganath
> Subject:  There is a problem with your message - O_S
> 
> Your internet message contains phrases commonly found in Unsolicited Bulk
> Emails and has been blocked.  If this is official Verizon Information
> Services business, please send a message to [EMAIL PROTECTED] to
> have it released for delivery. Thank you.
> 
> --- Summary of Original Message --
> 
>   From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
>   Date: Wed, 22 Jan 2003 17:51:20 +0530
>   Subj: RE: RMAN backup - basic Qs
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee.  Access to this message
by anyone else is unauthorised.  If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Krishnaswamy, Ranganath
  INET: [EMAIL PROTECTED]

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

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

Just curious: why are you using PGA_AGGREGATE_TARGET?  Are there any limits
on memory capacity that you are in danger of exceeding?  You have two CPUs
with 4Gb of RAM;  I imagine that you're not in any danger, but it pays to be
sure.  I can't think of the HP-UX equivalent to the Solaris "swap" command,
but I imagine that the answer is "glance" or "glanceplus"...

There are hundreds of over-configured servers which have entire SIMMs and
DIMMs that have never been visited, have never had voltage applied to them.
One place I've worked has 8Gb of RAM onboard its database server, but
"swap -s" (i.e. Solaris) has never shown more than 2.5G of virtual memory
used (i.e. allocated plus reserved)!  They are running 32-bit Oracle on
Solaris 2.8 (i.e. 64-bit capable), so they will probably never use all that
RAM anyway...

So, why use a feature like PGA_AGGREGATE_TARGET to place upper bounds on
memory utilization?  The reason I ask is that this mechanism must play hell
on the cost-based optimizer.  Imagine the CBO coming up with an execution
plan based on one memory model (i.e. this much memory to do in-memory sort,
that much memory to do in-memory hash join, etc) and then have subsequent
executions forced into unexpected memory models because automatic PGA memory
management decided to do so.  Obviously the impact of automatic PGA memory
management is something that affects the CBO something fierce, and I don't
think anyone really has a handle on the "how" or "why".

My advice is twofold:

* re-evaluate the reasons for using automatic PGA memory management
   in the first place
* pitch the automatic PGA memory management feature and then compare
   how things behave.

Hope this helps...

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 4:13 AM

>
> Hello
>
> 650 Mhz processors are HP pa RISC processors.
> We use automatic PGA management. There are 500MB PGA.
> sort_area_size have the dafault value because under automatic PGA
> there is not used (or not?).
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, January 21, 2003 10:59 PM
>
>
> > Hemant raises a valid point...
> >
> > You could just do a "select blah from dw.supuestos" in SQL*Plus (or some
> > other client that retrieves all results without pausing) to get an idea
of
> > how much effort the group by is taking.
> >
> > My other question relates to the CPU's.  If I'm reading correctly the
old
> > server had 2 * 1.4GHz CPU's and the new server has 2 * 650MHz CPU's.  I
> > realise the CPU's most likely aren't the same architecture (sorry - I
know
> > nothing about HP's range of machines) but you aren't comparing apples to
> > apples are you?
> >
> > It sounds like you have changed Oracle version, CPU architecture, IO
> > subsystem and OS all at the same time.  There is a multitude of
> > possibilities.  Have you compared the init.ora files (Hemant's
> > SORT_AREA_SIZE recommendation is a good one).
> >
> >
> >
> >
> > Hemant K
> > Chitale   To: Multiple recipients of
> list ORACLE-L <[EMAIL PROTECTED]>
> >  > et.com.sg>Subject: Re: Oracle
9.2.0.2
> performance problem
> > Sent by:
> > [EMAIL PROTECTED]
> > m
> >
> >
> > 22/01/2003
> > 00:49
> > Please respond
> > to ORACLE-L
> >
> >
> >
> >
> >
> >
> >
> > Wonder if it is the SORT (for the GroupBy) taking time ?
> > What is the SORT_AREA_SIZE and what are the INITIAL and NEXT
> > extents of the user's temporary tablespace ?
> > Are the tablespaces Locally-Managed and the temporary tablespace
> > a TEMPORARY TABLESPACE with a TEMPFILE ?
> >
> > Hemant
> >
> > At 01:59 AM 20-01-03 -0800, you wrote:
> >  Hello
> >
> >  We have an serious performance problem on a DSS db.
> >  We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11
> >
> >  Oracle 9.2.0.2 tooks 30 min doing this query where an  Intel 2x1,4
> Ghz
> >  tooks 9 min only.
> >
> >  We have in the HP losts of buffers(1,5GB), sga(200MB), pga(500MB),
> >  fast i/O (EMC Clariom CX600)...
> >  We try lost of parameters, but time is always the same.
> >
> >  Is there some bug in this release - platform ?
> >  How can I get more data about this problem??
> >
> >  Thanks.
> >
> >  SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4,
> > evpanc,evpgru,evpcli,evppai,evppro,evpume,
> > to_date(evpano||'-'||evpmes||'-'||'01','-MM-DD') FECHA,
> > sum(evppca) PPTO
> >  FROM DW.SUPUESTOS
> >  GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4,
> > evpanc,evpgru,evpcli,evppai,evppro,evpume,
> > evpano, evpmes
> >
> >  call cou

Re: create tablespace problem


I have a problem understanding why anyone would ask a question and then not
display the exact error number and error message text they are seeing.
There are thousands of error messages in Oracle and the platforms it runs
upon and the APIs it supports, and the text associated with the number is
practically useless, due to differences in local language, problems with
recollection, sloppy wording, etc.

For heavens sake people:  please include the error number too!  Not just
your vague recollection of the wording of the message, not just some edited
version of the error text, not just the code throwing the error.  The
number, the number, the blasted number!!!  It's the only sure way to
precisely identify what has happened, and people just throw it away...


...breathe...breeaatthhhe..oom.ooommmAh!
Better now?  Nopeoo.ah-om.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, January 21, 2003 11:53 PM


> I'm have problem when execute this script, the error is show by oracle is
> "error at line 1
> ORA-...:TSelCZ_Data already exists;
>
>
> -- create tablespace
>
> CREATE TABLESPACE TSelCZ_Data DATAFILE 'd:\orant\database\tselcz1dbCZ.ora'
> SIZE 150M;
> ALTER TABLESPACE "TSELCZ_DATA" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
> /
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: pomi
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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).

FW: There is a problem with your message - O_S - To Kirti Deshpan

2003-01-22 Thread Krishnaswamy, Ranganath

Hi Kirti,

I am unable to send a message to you privately.  Whenever I try to
send a message to you it bounces back with the below forwarded message.  How
do I send a message to you privately?  Please help.

Thanks and Regards,

Ranganath

> -Original Message-
> From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
> Sent: Wednesday, January 22, 2003 5:57 PM
> To:   Krishnaswamy, Ranganath
> Subject:  There is a problem with your message - O_S
> 
> Your internet message contains phrases commonly found in Unsolicited Bulk
> Emails and has been blocked.  If this is official Verizon Information
> Services business, please send a message to [EMAIL PROTECTED] to
> have it released for delivery. Thank you.
> 
> --- Summary of Original Message --
> 
>   From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
>   Date: Wed, 22 Jan 2003 17:51:20 +0530
>   Subj: RE: RMAN backup - basic Qs
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee.  Access to this message
by anyone else is unauthorised.  If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Krishnaswamy, Ranganath
  INET: [EMAIL PROTECTED]

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

2003-01-22 Thread Jamadagni, Rajendra




Your only choice (limited) is do not create standalone procedures ... 
package them. But Wait there is more 
 
no matter what you create ... procedure/function/package if it is being 
executed, you can't compile it. Who makes changes in live production environment 
(other than us) anyways ... ??
 
What you want to do is capture ORA-4068 and retry the code that raised 
the error. Trust me, it is not trivial, especially if it happens when you are in 
middle of a transaction.
 
Raj
__
Rajendra 
Jamadagni  
    MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot 
com
Any opinion expressed here is 
personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but 
having an opinion is an art!

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, January 22, 2003 1:29 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  Online recompilation of SPDear all, Is there any 
  means of changing the body of a SP and recompiling it while there "could" possibly be some sessions accessing it. We have such a req in our 
  appln wherein the recreation shouldn't 
  affect ongoing applns that might 
  access the same SP. T I 
  A.Best RegardsJai
*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

Re: dbms_job - running jobs every 15 minutes

Personally, I tend to just submit four jobs:  one at the top of hour, one at
15 past, one at 30 past, and the fourth at 45 past.  To alter the frequency,
just "break" or remove one or more of the jobs.  Falls into the category of
"not elegant, but it works"...

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, January 21, 2003 9:39 PM


> How about this... this will start the job at the top of the following hour
> and then schedule it every 15 minutes...
>
> dbms_job.submit(:jobno,
> 'statspack.snap;',to_date(to_char(sysdate+60/1440,'mm/dd/ hh24'),
> 'mm/dd/ hh24') ,'to_date(to_char(sysdate+60/1440,''mm/dd/ hh24''),
> ''mm/dd/ hh24'') + 15/1440' );
>
>
> -Original Message-
> [EMAIL PROTECTED]
> Sent: Tuesday, January 21, 2003 6:24 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Feeling particularly anal the other day,  I used the following
> specification to
> run statspack at the top of the hour, 15, 30 and 45 minutes after the
> hour.
>
> variable jobno number;
> variable instno number;
> begin
> select instance_number into :instno from v$instance;
> dbms_job.submit(
> :jobno
> , 'statspack.snap;'
> -- every 15 minutes at 00,15,30 and 45
> , trunc(sysdate,'hh24') +  ( ( 15 + ( 15 *
> floor(to_number(to_char(sysdate,'mi')) / 15))) / ( 24 * 60 ))
> , 'trunc(sysdate,''hh24'') +  ( ( 15 + ( 15 *
> floor(to_number(to_char(sysdate,''mi'')) / 15))) / ( 24 * 60 ))'
> );
> commit;
> end;
> /
>
>
> Seems to me that the time specs could be simplified a bit.
>
> Anyone care to give it a go?  :)
>
> Jared
>
> --
> 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: Robert Freeman
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (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: create tablespace problem

2003-01-22 Thread Rachel Carmichael

in 9i, you can remove the datafiles as well by using the clause "and
datafiles" on the drop tablespace command. 

Saves having to remember to go back and clean up after yourself.
Without that clause, the drop tablespace command works as it did in 8i

Rachel

--- Stephane Faroult <[EMAIL PROTECTED]> wrote:
> 
> >Pomin,
> >
> >Pls check this tablespace whether it's already
> >exist in your database.
> >Try this script (connect as sys):
> >
> >SQL > select * from dba_tablespaces where
> >tablespace_name = 'TSelCZ_Data';
> 
> Would be better in UPPERCASE. Besides, this is far from being enough.
> When you drop a tablespace, the files subside (Hmm, I think that it
> is not necessarily still true with Oracle9 ... need to RTFM) and
> should (assuming a version which etc. etc.) be removed by hand. File
> existence prevents you from creating a new one with the same name.
> The tablespace no longer appears in DBA_TABLESPACES (version
> dependent ?) but still lurks in sys.ts$.
>  Of course you can use REUSE but this is a great way to wipe out an
> existing, useful tablespace by mistake.
> 
> >> -Original Message-
> >> From: pomin [SMTP:[EMAIL PROTECTED]]
> >> Sent: Wednesday, January 22, 2003 1:54 PM
> >> To: Multiple recipients of list ORACLE-L
> >> Subject:create tablespace problem
> >> 
> >> I'm have problem when execute this script, the
> >error is show by oracle is 
> >> "error at line 1
> >> ORA-...:TSelCZ_Data already exists;
> >> 
> >> 
> >> -- create tablespace
> >> 
> >> CREATE TABLESPACE TSelCZ_Data DATAFILE
> >'d:\orant\database\tselcz1dbCZ.ora'
> >> 
> >> SIZE 150M;
> >> ALTER TABLESPACE "TSELCZ_DATA" DEFAULT STORAGE (
> >MAXEXTENTS UNLIMITED );
> >> /
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Stephane Faroul
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 


__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

Insufficient quota problem

2003-01-22 Thread O'Neill, Sean

Recently we've had three of our 8.1.7 databases on W2K servers crash with
messages similar to below in relevant alert logs:

ORA-00202: controlfile: ''
ORA-27091: skgfqio: unable to queue I/O
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 1453) Insufficient quota to complete the requested service.

Instance was terminated by CKPT.

I've had a good trawl through Metalink and basically the postings suggest
it's down to one of following:
[1] Quota management enabled on disks.  [We don't have!]
[2] Possibly running low on physical and/or virtual memory.  Nothing written
to event logs on servers to indicate this was the case.

If anyone else out there experienced the same problem AND managed to
eliminate it I would appreciate your feedback.  Heck, I'm open to
suggestions on troubleshooting this one. 

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

 

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

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

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

Uncle Larry's new pricing scheme.

2003-01-22 Thread Farnsworth, Dave

http://www.informationweek.com/story/IWK20030121S0001
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Farnsworth, Dave
  INET: [EMAIL PROTECTED]

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

simple question on DDL

2003-01-22 Thread BanarasiBabu Tippa

Hi friends

Why DDL statements performs auto commit ? What is the exact reason behind
that one?
Anyone can share his/her opinions!!

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

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