date:20020125


Jacques are you sure? It would, of course, mean actually READING
the manual

Rachel
--- Jacques Kilchoer <[EMAIL PROTECTED]> wrote:
> Oracle SQL has a handy-dandy command called INSERT that allows you to
> put
> data into a table. I think INSERT is documented in the SQL manual.
> 
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> > 
> > Yes But I want thatthat number is inserted into the table.
> > 
> > [EMAIL PROTECTED]@fatcity.com den 2002-01-25 10:39 PST
> > 
> > Use SQL%ROWCOUNT.
> > 
> > BEGIN
> >  insert into table1
> >  select * from table2;
> >  dbms_output.put_line(SQL%ROWCOUNT);
> > END;
> > /
> > 
> > Executing this PL/SQL block should display you the number of 
> > rows that were
> > inserted into table1.
> 


__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

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

RE: SMON - Does it cause a degrade?


Cary Millsap, in his 'Myths About Extents' presentation advocates turning
off the auto-coalescing by SMON, stating that the auto-coalesce feature does
more harm than good.  
He had presented this at OAUG Database SIG meeting in Hawaii (held in Oct,
2000). 

- Kirti 


-Original Message-
Sent: Friday, January 25, 2002 6:16 PM
To: Multiple recipients of list ORACLE-L


Why the heck would you set pctincrease to anything but 0 at the
tablespace level. All you need is one table, created without storage
parameters and you are fragmented.

Try "Stop Defragmenting and Start Living"... what you want to do is
exactly what your DBA said, with the addition of either local
management (in which case, pctincrease is moot) or at least "minimum
extent" on the tablespace. Create all tables in the tablespace with NO
storage clause, let it default to the tablespace's storage parameters.

This does several things:  

a) you don't have to worry about storage parameters when creating
tables
b) all extents will be the same size or a multiple of each other -- so
NO fragmentation

Then you don't have to worry about the silly batch job either and can
go on and do something much more interesting.


--- [EMAIL PROTECTED] wrote:
> Hey Fellas,
> 
> I have an application DBA who insists that the pctincrease at the
> data
> tablespace should be set to 0 so that SMON does not coalesce the
> tablespace. He says coalesce will be performed by using a scheduled
> batch
> job written for that purpose. He states that having SMON to perform
> an
> coalesce of the tablespace could cause an performance degrade??? I
> have
> never heard of such a thing, but then I dont wanna argue with him.
> He's got
> wrinkles on his face, and grey hair ;-)
> 
> My argument would be, go back to the drawing board, get your tables
> sized
> properly, if you anticipate fragmentation. And SMON does not cause a
> performance degrade? It wakes up every 5 minutes, does hold ST
> enqueue
> locks if a tablespace needs coalescing, but it does not cause a
> performance
> degrade? Or does it???
> 
> Now, can I have a definitive word on this? Any sites, white papers,
> to
> refer to that says so, or to the contrary. I need to convince the
> higher
> ups.
> 
> Raj
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

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

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

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

Re: SMON - Does it cause a degrade?

Okie. To be more specific. This is a siebel application running against a
7.3.4 database. So the 8i features for space management are out of the
question. Second, there are large tables with varied values for initial and
next extent in the tablespace. There are also going to be temporary tables
created and dropped during a data load. I know for sure this tablespace is
going to be badly fragmented. Hence, I was suggesting that pctincrease be
set to 1 at the tablespace level so that SMON could coalesce the adjacent
free extents. But the other side was of the opinion that SMON could cause a
performance degrade. I needed to confirm this.

The answer that I was looking for was: Is SMON resource intensive? Melissa
directed me to a note on Metalink which said they could be. Could hog the
CPU, and hold ST enqueue locks on the data dictionary space transaction
tables. The bosses always want to see it on paper, saying Oracle says so.

And to update you guys further, I had my say. Got two tablespaces. One were
all extents are going to be of 128M and another where they will be 256M.
Chosen to be multiples of block_size*db_multiblock_read_count. And a
pctincrease of 0 at the tablespace level, which obviusly I dont mind now.

Thanks everybody. Now gotto go and do something interesting ;-)

Raj

Rachel Carmichael <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002
07:16:06 PM

Please respond to [EMAIL PROTECTED]

Sent by:  [EMAIL PROTECTED]

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

Why the heck would you set pctincrease to anything but 0 at the
tablespace level. All you need is one table, created without storage
parameters and you are fragmented.

Try "Stop Defragmenting and Start Living"... what you want to do is
exactly what your DBA said, with the addition of either local
management (in which case, pctincrease is moot) or at least "minimum
extent" on the tablespace. Create all tables in the tablespace with NO
storage clause, let it default to the tablespace's storage parameters.

This does several things:

a) you don't have to worry about storage parameters when creating
tables
b) all extents will be the same size or a multiple of each other -- so
NO fragmentation

Then you don't have to worry about the silly batch job either and can
go on and do something much more interesting.

--- [EMAIL PROTECTED] wrote:
> Hey Fellas,
>
> I have an application DBA who insists that the pctincrease at the
> data
> tablespace should be set to 0 so that SMON does not coalesce the
> tablespace. He says coalesce will be performed by using a scheduled
> batch
> job written for that purpose. He states that having SMON to perform
> an
> coalesce of the tablespace could cause an performance degrade??? I
> have
> never heard of such a thing, but then I dont wanna argue with him.
> He's got
> wrinkles on his face, and grey hair ;-)
>
> My argument would be, go back to the drawing board, get your tables
> sized
> properly, if you anticipate fragmentation. And SMON does not cause a
> performance degrade? It wakes up every 5 minutes, does hold ST
> enqueue
> locks if a tablespace needs coalescing, but it does not cause a
> performance
> degrade? Or does it???
>
> Now, can I have a definitive word on this? Any sites, white papers,
> to
> refer to that says so, or to the contrary. I need to convince the
> higher
> ups.
>
> Raj
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

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

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

Fat City Network Services-- (858) 538-5051

archive: reboot netware


**
*UNTESTED, WAS REPORTED TO REBOOT NETWARE*
**
/* original email: 31-Jan-96 [EMAIL PROTECTED] */
/* oracle 7.1.4.3.2, pl/sql 2.1.4.3.1*/
declare
 type tab_t is table of date index by binary_integer;
 t tab_t;
begin
 t(1) := sysdate;
 t := t;
 dbms_output.put_line(to_char(t(1));
end;
/
---end---

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

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

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

RE: ORA-04031 with Oracle 8.1.7.0.0 on HP-UX 11.0. URGENT


You may be hitting a bug# 1397603 and may have to apply the 8.1.7.1 or
8.1.7.2 patch set.
Pl search Metalink for this bug number. 

- Kirti 
 

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Friday, January 25, 2002 9:25 AM
To: Multiple recipients of list ORACLE-L


Getting:
"ORA-04031: unable to allocate 4256 bytes of shared memory ("shared pool",
"unknown object", "sga heap", "library cache")"
and
"ORA-00604: error occurred at recursive SQL level 1"
over and over in the alert log.

This is a "suddenly" event on a production database that had been
operational at 8.1.6 for a year, and is now at 8.1.7 for a month.  Running
SilverStream as a front-end, which has about 70 dedicated connections in
it's pool (SilverStream does MTS on its own). 

SilverStream users are unable to login.
I'm unable to login "sqlplus sys/manager", getting end-of-communication
channel.
I'm unable  to login "sqlplus internal", getting already logged in.
I'm able  to login "svrmgrl", but most commands end the session with a "not
connected".
  "startup" gives the expected already started, shutdown first.

My questions are (yeah, I know, clairvoiance):
  - Could anyone who experienced this share solution(s)?
  - Will a "shutdown immediate" just hang?
  - Will a "shutdown abort" cause harm? (Nothing but once-a-day full
export.)
  - What would you do?



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kirsch, Walter J (Northrop Grumman)
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: Re: Number_of_rows

2002-01-25 Thread Jacques Kilchoer

Title: RE: Re: Number_of_rows





Oracle SQL has a handy-dandy command called INSERT that allows you to put data into a table. I think INSERT is documented in the SQL manual.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> 
> Yes But I want thatthat number is inserted into the table.
> 
> [EMAIL PROTECTED]@fatcity.com den 2002-01-25 10:39 PST
> 
> Use SQL%ROWCOUNT.
> 
> BEGIN
>  insert into table1
>  select * from table2;
>  dbms_output.put_line(SQL%ROWCOUNT);
> END;
> /
> 
> Executing this PL/SQL block should display you the number of 
> rows that were
> inserted into table1.

RE: How can i receive name of the running procedure

2002-01-25 Thread Jacques Kilchoer

Title: RE: How can i receive name of the running procedure





> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> 
> How can I select the name of the procedure, which is running.?
> I mean I am running a procedure and I want the name of the 
> pocedure to be inserted in a table.
>  Please help me with a simple pl/sql script on this.



Take a look at Steve Adam's website for the consulting company Ixora:
http://www.ixora.com.au/


There is a script to show executing procedures. Unfortunately it's not "simple".

RE: SMON - Does it cause a degrade?


Here is a link to an excellent article on SMON by Jonathan Lewis... 
http://www.jlcomp.demon.co.uk/smon_i.html

- Kirti 


-Original Message-
Sent: Friday, January 25, 2002 5:51 PM
To: Multiple recipients of list ORACLE-L


Since SMON will coalesce anyway if needed when a request
for extents is made, what's the point in setting PCTFREE to
a value >0 to force periodic coalesces?

An explanation of what takes place during a request for extents
is in the concepts manual.

Jared





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/25/02 01:50 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:SMON - Does it cause a degrade?


Hey Fellas,

I have an application DBA who insists that the pctincrease at the data
tablespace should be set to 0 so that SMON does not coalesce the
tablespace. He says coalesce will be performed by using a scheduled batch
job written for that purpose. He states that having SMON to perform an
coalesce of the tablespace could cause an performance degrade??? I have
never heard of such a thing, but then I dont wanna argue with him. He's 
got
wrinkles on his face, and grey hair ;-)

My argument would be, go back to the drawing board, get your tables sized
properly, if you anticipate fragmentation. And SMON does not cause a
performance degrade? It wakes up every 5 minutes, does hold ST enqueue
locks if a tablespace needs coalescing, but it does not cause a 
performance
degrade? Or does it???

Now, can I have a definitive word on this? Any sites, white papers, to
refer to that says so, or to the contrary. I need to convince the higher
ups.

Raj

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

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

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



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

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

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

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

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

RE: Session_wait

2002-01-25 Thread John Kanagaraj


Sergey,

My $0.02 below: (comments below and only for relevant stats, some of which
have been re-arranged):

NAME  VALUE
  -
opened cursors cumulative  1072
opened cursors current   11

* Significant number of cursors closed in a short time, probably due to a
*lot* of recursive SQL. This is borne out by the ratio of 'user calls' to
'recursive calls' below. Keep in mind that index block splits are recursive,
so if you are inserting, updating or deleting data that re-arranges indexes,
this could be explained away.

user calls  341
recursive calls  198492
recursive cpu usage4089
CPU used by this session 696253

* Recursive CPU usage is insignificant compared to other CPU usage, and
that's another indication that recursive SQL is not the problem (if this is
against indexes).

enqueue requests  11878
enqueue releases  11876

* You don't seem to have enqueue waits, so you didn't get into a lock wait
situation.

session logical reads  77233609
= db block gets + consistent gets
db block gets   2642119
consistent gets74591490
physical reads   148822
db block changes3005410
consistent changes  141
redo entries1506007
redo size 1.184E+09
= approx 1.1 Gb!!!
physical writes2728
physical writes non checkpoint 2728

* You performed 148822 physical reads, but 77233609 logical reads!! This,
along with the fact that you are performing a large number of consistent
gets rather than db block gets implies that are reading and updating the
same blocks repeatedly in a loop. This is most certainly due to inefficient
and incorrect use of SQL. Tuning is imperative, and improvement will be
measured in quantums rather than deltas. The number of writes is negligible
compared to reads, supporting the above observation. The redo is based on
the number of db block changes - if the same block is changed multiple
times, the amount of redo goes up accordingly. This too supports the
observation. However, if you have a large number of indexes that change (as
a result of updates), this could also produce the same phenomenon - large
number of logical reads and same-block updates (and recursive SQL). 

physical reads direct  3594
physical writes direct 2728

* Do you have Quick I/O (or raw) on some files? 

table scans (short tables)   210918
table scans (long tables)36
table scan rows gotten798264962
table scan blocks gotten   71788386
table fetch by rowid1074164
table fetch continued row12

* You seem to be performing almost all your reads via table scans of small
tables (20 blocks or lesser). In this case, you aren't performing Indexed
reads, which *may* be good (if these are small tables). However, it looks
like you are scanning short tables repeatedly (as supported by observations
above).

leaf node splits   4018
branch node splits9

* This definitely points to Index block manipulation as a result of updates
or inserts. 

Having said all that above, I hazard the following guesses:

* You are inefficiently scanning small tables, while continually updating a
limited set of blocks repeatedly. This points to ineffecient coding. SQL
Tuning will go a long way!
* You are probably updating columns (or inserting rows) that support
indexes. You should look at whether these indexes are really required. If
indexes are not used (as in many full table scans), why are they present?

Perform a trace and TKPROF it. If you post the output, we could help
further. My feeling is that 'Database' or 'Instance' level tuning via init
parameters isn't going to get you muc

Re: SMON - Does it cause a degrade?


oh Jared darling, you did it. you forced me to correct that typo!

not PCTFREE dear, PCTINCREASE. PCTFREE won't force anything to coalesce
ever.

sigh, and here I thought I was gonna have an easy evening :)


--- [EMAIL PROTECTED] wrote:
> Since SMON will coalesce anyway if needed when a request
> for extents is made, what's the point in setting PCTFREE to
> a value >0 to force periodic coalesces?
> 
> An explanation of what takes place during a request for extents
> is in the concepts manual.
> 
> Jared
> 
> 
> 
> 
> 
> [EMAIL PROTECTED]
> Sent by: [EMAIL PROTECTED]
> 01/25/02 01:50 PM
> Please respond to ORACLE-L
> 
>  
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc: 
> Subject:SMON - Does it cause a degrade?
> 
> 
> Hey Fellas,
> 
> I have an application DBA who insists that the pctincrease at the
> data
> tablespace should be set to 0 so that SMON does not coalesce the
> tablespace. He says coalesce will be performed by using a scheduled
> batch
> job written for that purpose. He states that having SMON to perform
> an
> coalesce of the tablespace could cause an performance degrade??? I
> have
> never heard of such a thing, but then I dont wanna argue with him.
> He's 
> got
> wrinkles on his face, and grey hair ;-)
> 
> My argument would be, go back to the drawing board, get your tables
> sized
> properly, if you anticipate fragmentation. And SMON does not cause a
> performance degrade? It wakes up every 5 minutes, does hold ST
> enqueue
> locks if a tablespace needs coalescing, but it does not cause a 
> performance
> degrade? Or does it???
> 
> Now, can I have a definitive word on this? Any sites, white papers,
> to
> refer to that says so, or to the contrary. I need to convince the
> higher
> ups.
> 
> Raj
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

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

Re: SMON - Does it cause a degrade?

Why the heck would you set pctincrease to anything but 0 at the
tablespace level. All you need is one table, created without storage
parameters and you are fragmented.

Try "Stop Defragmenting and Start Living"... what you want to do is
exactly what your DBA said, with the addition of either local
management (in which case, pctincrease is moot) or at least "minimum
extent" on the tablespace. Create all tables in the tablespace with NO
storage clause, let it default to the tablespace's storage parameters.

This does several things:  

a) you don't have to worry about storage parameters when creating
tables
b) all extents will be the same size or a multiple of each other -- so
NO fragmentation

Then you don't have to worry about the silly batch job either and can
go on and do something much more interesting.

--- [EMAIL PROTECTED] wrote:
> Hey Fellas,
> 
> I have an application DBA who insists that the pctincrease at the
> data
> tablespace should be set to 0 so that SMON does not coalesce the
> tablespace. He says coalesce will be performed by using a scheduled
> batch
> job written for that purpose. He states that having SMON to perform
> an
> coalesce of the tablespace could cause an performance degrade??? I
> have
> never heard of such a thing, but then I dont wanna argue with him.
> He's got
> wrinkles on his face, and grey hair ;-)
> 
> My argument would be, go back to the drawing board, get your tables
> sized
> properly, if you anticipate fragmentation. And SMON does not cause a
> performance degrade? It wakes up every 5 minutes, does hold ST
> enqueue
> locks if a tablespace needs coalescing, but it does not cause a
> performance
> degrade? Or does it???
> 
> Now, can I have a definitive word on this? Any sites, white papers,
> to
> refer to that says so, or to the contrary. I need to convince the
> higher
> ups.
> 
> Raj
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

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

RE: ORA-01555 Mystery (Help)


IMO, FTS would be less costly(in terms of resources and may be time) and
will take care of dealyed block clean out issue, unless computed stats are
specifically needed.  

- Kirti  


-Original Message-
Sent: Friday, January 25, 2002 4:05 PM
To: Multiple recipients of list ORACLE-L


I don't have a definitive answer for that.

My guess would be that 'compute' would be required
so that all blocks are touched.

Another  way of dealing with delayed block cleanouts
is to do a 'select * from table;'.As long as you are 
going to touch every block anyway, you might as
well compute the stats.

But now I'm speculating.  :)

Jared





Walter K <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/25/02 11:20 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: ORA-01555 Mystery (Help)


Jared, would you elaborate more on this? Does this
need to be a 'compute' or can it be an 'estimate' on
the analyze?

I read the info on Steve's site as suggested by Barb
and it sounds like block cleanout may be the issue but
I'm still trying to digest the concept/issue as it
relates to my circumstance.

For the others that have contributed to the thread,
yes, the table is definitely locked in exclusive mode
(via a different session) before the SELECT is
performed and the lock is not released until the
following day. I too was suspicious that the lock was
accidentally being released.

-w




--- [EMAIL PROTECTED] wrote:
> Delayed block cleanouts can still cause the
> ORA-1555, even
> after locking the table in exlusive mode.
> 
> That's the purpose of the analyze, to force the
> block cleanouts.
> 
> Jared
> 
> 
> 
> 
> 
> 
> Paul Baumgartel <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 01/25/02 09:30 AM
> Please respond to ORACLE-L
> 
> 
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc: 
> Subject:RE: ORA-01555 Mystery (Help)
> 
> 
> Sure, but the original post concerns a *query*, not
> a transaction, and
> before running the query, the user locked the
> queried table in
> exclusive mode, to ensure that no other session
> could write to the
> queried table.   How do we account for the query's
> need to read from
> rollback? 
> 
> 
> --- "Baker, Barbara"
> <[EMAIL PROTECTED]> wrote:
> > 
> > I have a batch job that does this consistently. 
> It's the only job in
> > the
> > database; it sets the transaction to a hugh
> rollback segment.  And it
> > eats
> > its own tail.
> > 
> > Depending on how the job is written, it may need a
> read consistent
> > view
> > itself (as opposed to some other query in the
> database needing that
> > read
> > consistent view.)In that case, it may well go
> try to read its own
> > rollback segment, only to find that it's been
> overwritten.  (Oddly
> > enough,
> > even when there's plenty of space to extend the
> rollback, Oracle will
> > decide
> > to overwrite the original rollback segments rather
> than extend if it
> > thinks
> > it doesn't need those segments any more.)
> > 
> > I'd strongly suggest you get the stuff from Steve
> Adams' ixora site
> > that
> > places an uncommitted transaction in your rollback
> segments for the
> > length
> > of the run.This will guarantee that the
> rollback segments don't
> > get
> > overwritten.
> > Good luck!
> > 
> > Barb
> > 
> > > --
> > > From:Walter
> K[SMTP:[EMAIL PROTECTED]]
> > > Reply To:[EMAIL PROTECTED]
> > > Sent:Friday, January 25, 2002
> 9:15 AM
> > > To:  Multiple recipients of list
> ORACLE-L
> > > Subject: ORA-01555 Mystery (Help)
> > > 
> > > Hi,
> > > 
> > > A user in our data warehousing group is running
> into
> > > the old ORA-01555 (snapshot too old) error every
> time
> > > she runs a massive (20 million rows) select
> against
> > > one table via a view. I confirmed that the view
> only
> > > translates to the one table.
> > > 
> > > The user swears that no one would be making any
> > > updates/deletes to the table she is selecting
> from. I
> > > suggested she lock the table in exclusive mode,
> prior
> > > to running her massive select to guarantee no
> one else
> > > could change the data in the table and cause the
> > > triggering of the 1555 error. Locking the table
> was a
> > > viable option because it's a staging table in
> the
> > > warehouse itself. She locked the table in
> exclusive
> > > mode last night and it locked; fired off her
> query,
> > > and it failed 5 hours later with the 1555 error
> again.
> > > 
> > > I'm stumped on this. I just don't see how this
> is
> > > possible. Any suggestions?
> > > 
> > > Thanks!!!
> > > -w
> > > 
> > >
> __
> .
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  F

Re: ORA-01555 Mystery (Help)


I'm too tired to bug you tonight


--- [EMAIL PROTECTED] wrote:
> I was going to write this myself, but this explanation fron 
> MetaLink Note 45895.1 means I can just cut and paste, and
> Rachel won't get after me for typos.  :)
> 
> Jared
>  
>   Delayed block cleanout on old committed updates.  An update
> operation 
>   completes and commits; the updated blocks are not touched again
> until a 
>   long-running query begins.  Delayed Block Cleanout (DBC) has never
> been 
>   done on the blocks.  This can result in a scenario which happens
> only 
>   under specific circumstances in VLDB, causing ORA-01555 errors when
> NO 
>   updates or inserts are being committed on the same blocks a query
> is 
>   retrieving. 
>  
>   All of the following must be true for an ORA-01555 to occur in this
> 
> case: 
>  
>   (i) An update completes and commits and the blocks are not 
>   touched again until... 
>  
>   (ii) A long query begins against the previously updated blocks.
> 
>  
>   (iii) During the query, a considerable amount of DML takes
> place, 
>   though not on the previously updated blocks which the query is 
>   currently fetching. 
>  
>   (iv) Under condition (iii) there is so much DML relative to 
> available 
>   rollback space that the rollback segment used in the first
> update 
>   wraps around, probably several times. 
>  
>   (v) Under condition (iv), the commit SCN of the first update is
> 
>   cycled out of the rollback segment. 
>  
>   (vi) Under condition (iv) the lowest SCN in the rollback
> segment is 
>   pushed higher than the read consistent SCN in the query. 
>  
>   (Note:  The read consistent SCN is what the query uses to
> construct 
>a read consistent view.  Any block which has an SCN higher
> than 
> this
>was obviously updated after the query started and requires 
> rollback). 
>  
>   The above conditions imply that when a query reaches a block that
> has 
> been 
>   updated but not cleaned out, the query quickly learns that the
> update 
>   committed, and accordingly cleans out the block.  But because the
> update 
> 
>   SCN is no longer in the rollback segment (condition (v)), the query
> 
> doesn't
>   know WHEN the update committed.  This is important because if the
> commit 
> 
>   happened before the query began, the current value in the block can
> be 
> used 
>   by the query; but if the commit happened after, the old value must
> be 
> fetched
>   from the rollback segment. Now, because the rollback segment
> wrapped in 
> (iv),
>   we know that the update SCN can't be higher than the lowest SCN in
> the 
>   rollback segment, which gives us a nice upper bound.  If we only
> knew 
> that 
>   the read consistent SCN was higher than this upper bound, we would
> know 
> that
>   the update committed before the query started.  But we don't know
> this 
>   because of condition (vi), so we can't even accurately "estimate"
> the 
> update
>   SCN.  Hence, we get an ORA-01555. 
> 
> 
> 
> 
> 
> Stephane Faroult <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 01/25/02 10:39 AM
> Please respond to ORACLE-L
> 
>  
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc: 
> Subject:Re: ORA-01555 Mystery (Help)
> 
> 
> I was almost ready to subscribe to the idea of delayed cleanout, but
> I
> cannot understand why really. The necessity for reading a block from
> the
> rollback segments comes from encountering during the course of the
> SELECT a block the SCN of which is higher than the SCN when the query
> started. I have of course no certainty about it, but it would be
> logical
> to expect the block's SCN to be properly set irrespectively of the
> clean-out being immediate or delayed. In other words, even if a
> SELECT
> physically writes blocks, it should not have anything to do with
> rollback segments anyway.
> I share Mladen's opinion, somebody must be economical with the truth
> somewhere, and you should check V$ACCESS, V$SESSION and V$LOCK. Are
> you
> really sure that the code contains no 'just in case' commit ou
> rollback
> which would release the lock? And by the way, 5 hours look to me like
> an
> awfully long time, even for a 20 million row mega-select of death.
> 
> [EMAIL PROTECTED] wrote:
> > 
> > Precisely the point I was trying to make, when I put the question
> if it 
> was
> > a normal select, or if it was within a PL/SQL block?  The myth is
> that
> > snapshot too old happens only when some other transaction was in
> the
> > process of performing an DML on a table, when you did a select on
> it. It
> > can happen for other reasons too. Search on Metalink for "Delayed
> block
> > cleanouts" and "fetch across commits".
> > 
> > Raj
> > 
> > "Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Net

RE: SMON - Does it cause a degrade?



Thanks Melissa. I was wrong, I guess. For this is what I found on Metalink.

1. Because SMON acquires the Space Transaction (ST) enqueue in exclusive mode, other 
processes requiring the enqueue will be
blocked. This is typically manifested by multiple   errors.

2. SMON sits in a very tight loop while coalescing, and consumes close to 100% CPU. If 
the system is CPU-bound, the run queue will
increase as other processes try to get onto CPU.

Regards
Raj






"Godlewski, Melissa" <[EMAIL PROTECTED]>@fatcity.com on
01/25/2002 05:20:20 PM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


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




Check out meta link note:61997.1  SMON - Temporary Segment Cleanup and Free
Space Coalescing.  For one explanation.

-Original Message-
Sent: Friday, January 25, 2002 4:50 PM
To: Multiple recipients of list ORACLE-L

Hey Fellas,

I have an application DBA who insists that the pctincrease at the data
tablespace should be set to 0 so that SMON does not coalesce the
tablespace. He says coalesce will be performed by using a scheduled batch
job written for that purpose. He states that having SMON to perform an
coalesce of the tablespace could cause an performance degrade??? I have
never heard of such a thing, but then I dont wanna argue with him. He's got
wrinkles on his face, and grey hair ;-)

My argument would be, go back to the drawing board, get your tables sized
properly, if you anticipate fragmentation. And SMON does not cause a
performance degrade? It wakes up every 5 minutes, does hold ST enqueue
locks if a tablespace needs coalescing, but it does not cause a performance
degrade? Or does it???

Now, can I have a definitive word on this? Any sites, white papers, to
refer to that says so, or to the contrary. I need to convince the higher
ups.

Raj

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

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

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




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

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

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

Re: SMON - Does it cause a degrade?


Since SMON will coalesce anyway if needed when a request
for extents is made, what's the point in setting PCTFREE to
a value >0 to force periodic coalesces?

An explanation of what takes place during a request for extents
is in the concepts manual.

Jared





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/25/02 01:50 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:SMON - Does it cause a degrade?


Hey Fellas,

I have an application DBA who insists that the pctincrease at the data
tablespace should be set to 0 so that SMON does not coalesce the
tablespace. He says coalesce will be performed by using a scheduled batch
job written for that purpose. He states that having SMON to perform an
coalesce of the tablespace could cause an performance degrade??? I have
never heard of such a thing, but then I dont wanna argue with him. He's 
got
wrinkles on his face, and grey hair ;-)

My argument would be, go back to the drawing board, get your tables sized
properly, if you anticipate fragmentation. And SMON does not cause a
performance degrade? It wakes up every 5 minutes, does hold ST enqueue
locks if a tablespace needs coalescing, but it does not cause a 
performance
degrade? Or does it???

Now, can I have a definitive word on this? Any sites, white papers, to
refer to that says so, or to the contrary. I need to convince the higher
ups.

Raj

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

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

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



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

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

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

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Nick Wagner

Title: RE: ORA-01555 Mystery (Help)





would "Set transaction read only" help here?


-Original Message-
From: Baker, Barbara [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 25, 2002 2:35 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: ORA-01555 Mystery (Help)




I think you might have to throw a full hint in there to insure you're really
touching all the blocks (select /*+ FULL*/ count(*) from table_name).


But Jared's correct.  If you're gonna go to that much trouble, might as well
compute statistics.




> --
> From:     [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]]
> Reply To:     [EMAIL PROTECTED]
> Sent:     Friday, January 25, 2002 3:05 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  RE: ORA-01555 Mystery (Help)
> 
> I don't have a definitive answer for that.
> 
> My guess would be that 'compute' would be required
> so that all blocks are touched.
> 
> Another  way of dealing with delayed block cleanouts
> is to do a 'select * from table;'.    As long as you are 
> going to touch every block anyway, you might as
> well compute the stats.
> 
> But now I'm speculating.  :)
> 
> Jared
> 
> 
> 
> 
> 
> Walter K <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 01/25/02 11:20 AM
> Please respond to ORACLE-L
> 
>  
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc: 
> Subject:    RE: ORA-01555 Mystery (Help)
> 
> 
> Jared, would you elaborate more on this? Does this
> need to be a 'compute' or can it be an 'estimate' on
> the analyze?
> 
> I read the info on Steve's site as suggested by Barb
> and it sounds like block cleanout may be the issue but
> I'm still trying to digest the concept/issue as it
> relates to my circumstance.
> 
> For the others that have contributed to the thread,
> yes, the table is definitely locked in exclusive mode
> (via a different session) before the SELECT is
> performed and the lock is not released until the
> following day. I too was suspicious that the lock was
> accidentally being released.
> 
> -w
> 
> 
> 
> 
> --- [EMAIL PROTECTED] wrote:
> > Delayed block cleanouts can still cause the
> > ORA-1555, even
> > after locking the table in exlusive mode.
> > 
> > That's the purpose of the analyze, to force the
> > block cleanouts.
> > 
> > Jared
> > 
> > 
> > 
> > 
> > 
> > 
> > Paul Baumgartel <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 01/25/02 09:30 AM
> > Please respond to ORACLE-L
> > 
> > 
> > To: Multiple recipients of list ORACLE-L
> > <[EMAIL PROTECTED]>
> > cc: 
> > Subject:    RE: ORA-01555 Mystery (Help)
> > 
> > 
> > Sure, but the original post concerns a *query*, not
> > a transaction, and
> > before running the query, the user locked the
> > queried table in
> > exclusive mode, to ensure that no other session
> > could write to the
> > queried table.   How do we account for the query's
> > need to read from
> > rollback? 
> > 
> > 
> > --- "Baker, Barbara"
> > <[EMAIL PROTECTED]> wrote:
> > > 
> > > I have a batch job that does this consistently. 
> > It's the only job in
> > > the
> > > database; it sets the transaction to a hugh
> > rollback segment.  And it
> > > eats
> > > its own tail.
> > > 
> > > Depending on how the job is written, it may need a
> > read consistent
> > > view
> > > itself (as opposed to some other query in the
> > database needing that
> > > read
> > > consistent view.)    In that case, it may well go
> > try to read its own
> > > rollback segment, only to find that it's been
> > overwritten.  (Oddly
> > > enough,
> > > even when there's plenty of space to extend the
> > rollback, Oracle will
> > > decide
> > > to overwrite the original rollback segments rather
> > than extend if it
> > > thinks
> > > it doesn't need those segments any more.)
> > > 
> > > I'd strongly suggest you get the stuff from Steve
> > Adams' ixora site
> > > that
> > > places an uncommitted transaction in your rollback
> > segments for the
> > > length
> > > of the run.    This will guarantee that the
> > rollback segments don't
> > > get
> > > overwritten.
> > > Good luck!
> > > 
> > > Barb
> > > 
> > > > --
> > > > From:    Walter
> > K[SMTP:[EMAIL PROTECTED]]
> > > > Reply To:    [EMAIL PROTECTED]
> > > > Sent:    Friday, January 25, 2002
> > 9:15 AM
> > > > To:  Multiple recipients of list
> > ORACLE-L
> > > > Subject: ORA-01555 Mystery (Help)
> > > > 
> > > > Hi,
> > > > 
> > > > A user in our data warehousing group is running
> > into
> > > > the old ORA-01555 (snapshot too old) error every
> > time
> > > > she runs a massive (20 million rows) select
> > against
> > > > one table via a view. I confirmed that the view
> > only
> > > > translates to the one table.
> > > > 
> > > > The user swears that no one would be making any
> > > > updates/deletes to the table she is selecting
> > from. I
> > > > suggested she lock the table in exclusive mode,
>

RE: session_cached_cursosrs

2002-01-25 Thread Khedr, Waleed


Modify init.ora + restart the DB

-Original Message-
Sent: Friday, January 25, 2002 5:56 PM
To: Multiple recipients of list ORACLE-L



Hi

how do I set session_cached_cursors for the whole 9i instance and not for a
particular session?

I tried :

alter system set session_cached_cursors=150 scope=spfile

...but I ended up with ora-02096 - "parameter not modifiable with this
option"


thanx,

Marin


"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "




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

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

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

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

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

RE: How can i receive name of the running procedure

2002-01-25 Thread Aponte, Tony

Title: RE: How can i receive name of the running procedure






Take a look at DBMS_UTILITY.FORMAT_CALL_STACK.  You can parse out the caller from the return value.  Here's a short example of an anonymous block:

DECLARE

stack_info VARCHAR2(4096);

BEGIN

stack_info:=DBMS_UTILITY.FORMAT_CALL_STACK;

DBMS_OUTPUT.PUT_LINE(stack_info);

END;



- PL/SQL Call Stack -

  object  line  object

  handle    number  name

c1b59734 4  anonymous block



HTH

Tony Aponte


-Original Message-

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]

Sent: Friday, January 25, 2002 1:07 PM

To: Multiple recipients of list ORACLE-L

Subject: How can i receive name of the running procedure



Hallo,


How can I select the name of the procedure, which is running.?

I mean I am running a procedure and I want the name of the pocedure to be inserted in a table.

 Please help me with a simple pl/sql script on this.


Thanks in advance



Roland S


-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

RE: ORA-01555 Mystery (Help)


Jared: 
The table was analyzed (via estimate) Wednesday night.
I don't know if it was before or after Wednesday
night's attempt at the extract but if the blocks are
getting cleaned out via the 'analyze..estimate' then
delayed block cleanout should definitely not have been
an issue for Thursday night's attempt.

Barb:
I understand what Mladen is saying but it shouldn't be
an issue because the table has been locked in
exclusive mode prior to the running of the query and
the lock took. So, even if some rogue process out
there was attempting to change data in the table after
the query started, it shouldn't matter because the
rogue process(es) would get stuck waiting on the table
lock.

I was lucky that the query is being re-run today while
I am still at the office so I looked at the DB
activity and nothing is happening in the rollbacks, as
I expected to see. Discrete transactions aren't an
issue as the warehouse developers don't know about
them. I also recreated all of the rollback segments
for grins. 

We'll see what happens tonight. Unfortunately, if it
works tonight I still won't really know why. :(

-w

--- "Baker, Barbara"
<[EMAIL PROTECTED]> wrote:
> No wonder you're mystified.  This doesn't make
> sense.
> I can understand how you might have had a problem
> Tues night, but Wed night
> you should have sailed.
> 
> Here's one more thing to add to your 'bag of
> tricks':  try running this
> query (in batch every 15 minutes or so, if you can)
> to see what user(s) are
> accessing which rollbacks at any given time. 
> Probably won't help (unless
> Mladen is right, and someone is not coming clean
> with the complete truth).
> But it can't hurt.
> 
> select TO_CHAR(SYSDATE,'DD-MON-:HH24:MI:SS'),
>osuser o,   username u,
>segment_name s, substr(sa.sql_text,1,500) txt
> from v$session s,
>  v$transaction t,
>  dba_rollback_segs r,
>  v$sqlarea sa
> where s.taddr=t.addr
> and   t.xidusn=r.segment_id(+)
> and   s.sql_address=sa.address(+)
> /
> 
> 
> 
> > --
> > From:   Walter K[SMTP:[EMAIL PROTECTED]]
> > Reply To:   [EMAIL PROTECTED]
> > Sent:   Friday, January 25, 2002 12:30 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject:RE: ORA-01555 Mystery (Help)
> > 
> > Another fact, that should be mentioned, is that
> the
> > table in question was built (loaded) two days ago.
> The
> > nightly ETL processes for the warehouse are pretty
> > substantial and the likelyhood of a block not
> getting
> > cleaned/flushed out for a couple days should be
> nil.
> > 
> > To summarize:
> > 
> > 1. Tuesday Night: 
> > -truncate/load table 'A' (24 million rows)
> > -Perform massive select from 'A', fails 5 hours
> later
> > with 1555. NO DML BEING PERFORMED AGAINST 'A' BY
> ANY
> > OTHER SESSION
> > 2. Wednesday Night: 
> > -Perform massive select against 'A', fails 5 hours
> > later with ORA-1555. NO DML BEING PERFORMED
> AGAINST
> > 'A' BY ANY OTHER SESSION
> > 3. Thursday night: 
> > -'lock table A in exclusive mode;' via session 123
> > -perform massive select against 'A', fails 5 hours
> > later with ORA-1555 via session 124. NO DML BEING
> > PERFORMED AGAINST 'A' BY ANY OTHER SESSION
> > -session 123 still has exclusive lock on table 'A'
> the
> > following morning
> > 4. Friday morning:
> > -Walter is stumped but still trying to figure out
> a
> > solution! :)
> > 
> > -w
> > 
> > 
> > --- [EMAIL PROTECTED] wrote:
> > > Delayed block cleanouts can still cause the
> > > ORA-1555, even
> > > after locking the table in exlusive mode.
> > > 
> > > That's the purpose of the analyze, to force the
> > > block cleanouts.
> > > 
> > > Jared
> > > 
> > > 
> > > 
> > > 
> > > 
> > > 
> > > Paul Baumgartel <[EMAIL PROTECTED]>
> > > Sent by: [EMAIL PROTECTED]
> > > 01/25/02 09:30 AM
> > > Please respond to ORACLE-L
> > > 
> > >  
> > > To: Multiple recipients of list
> ORACLE-L
> > > <[EMAIL PROTECTED]>
> > > cc: 
> > > Subject:RE: ORA-01555 Mystery
> (Help)
> > > 
> > > 
> > > Sure, but the original post concerns a *query*,
> not
> > > a transaction, and
> > > before running the query, the user locked the
> > > queried table in
> > > exclusive mode, to ensure that no other session
> > > could write to the
> > > queried table.   How do we account for the
> query's
> > > need to read from
> > > rollback? 
> > > 
> > > 
> > > --- "Baker, Barbara"
> > > <[EMAIL PROTECTED]> wrote:
> > > > 
> > > > I have a batch job that does this
> consistently. 
> > > It's the only job in
> > > > the
> > > > database; it sets the transaction to a hugh
> > > rollback segment.  And it
> > > > eats
> > > > its own tail.
> > > > 
> > > > Depending on how the job is written, it may
> need a
> > > read consistent
> > > > view
> > > > itself (as opposed to some other query in the
> > > database needing that
> > > > read
> > > > consistent view.)In that case, it may well
> go
> > > try to read its own
> > > > rollback segment, only to find that it's b

session_cached_cursosrs

2002-01-25 Thread Marin Dimitrov



Hi

how do I set session_cached_cursors for the whole 9i instance and not for a
particular session?

I tried :

alter system set session_cached_cursors=150 scope=spfile

...but I ended up with ora-02096 - "parameter not modifiable with this
option"


thanx,

Marin


"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "




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

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

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

RE: Session_wait

2002-01-25 Thread Babich , Sergey


Hi, Cherie,
Sorry for the delayed reply. Yes, I already did, and analyzed the table,
too. But, logically, will it do any good given that one of the columns in
join condition has  NULLs only, the other being everything but NULL?
Anyway, I'll test it again. Thanks for your reply.
Best & have a great weekend.
Sergey


 -Original Message-
Sent:   Friday, January 25, 2002 2:44 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Session_wait


Sergey,

Have you considered adding an index to that queried column in table B?
Many third-party vendors allow the DBA to add indexes even when they
won't allow them to alter the code.

Something to consider.

Cherie Machler
Oracle DBA
Gelco Information Network


 

"Babich ,

Sergey"  To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>  
   Subject: RE: Session_wait

Sent by:

[EMAIL PROTECTED]

om

 

 

01/25/02 12:31

PM

Please respond

to ORACLE-L

 

 





Hi, Ross,
This has been running for about 4 hours now. I got an SQL trace file, and
looked at the execution plans. So here's the deal. There's an interesting
join condition "...where A.col1=B.col1.". However, A has a few hundred
distinct values in that column, none of them being NULL, and B, which has a
few hundred thousand rows, has ALL NULLs in the corresponding column, and
that column is not indexed, too. That's the query where it sits for a
couple
of hours. Guess what the optimizer is doing (8i)? I think internal effects
are secondary in this scenario. It is the production (including the
database) designed by the company named DELTEK, so nobody can change the
code. Anyway, I reported my findings...
Thank you very much for your help, it's always appreciated.
Best,
Sergey

 -Original Message-
Sent:   Friday, January 25, 2002 12:56 PM
To:Multiple recipients of list ORACLE-L

This is a busy little beaver...how long you say this runs?

opened cursors cumulative  1072
session logical reads  77233609
db block gets   2642119
consistent gets74591490
physical reads   148822
db block changes3005410
consistent changes  141
no work - consistent read gets 72058049
table scans (short tables)   210918
table scans (long tables)36
table scan rows gotten798264962
table scan blocks gotten   71788386
table fetch by rowid1074164
leaf node splits   4018
execute count 74445
bytes sent via SQL*Net to client  59650
bytes received via SQL*Net from client84233
SQL*Net roundtrips to/from client   342
buffer is not pinned count 73513922

I see some updates (or inserts) and heavy reads...at first glance,
though, you don't appear to be I/O throttled but likely have inefficiencies
in buffer cache. Pin some small tables? Reexamine access paths for
fts, even if on "small" tables (generally defined to be around
5% in blocks of buffer cache size?)and consider seeking out
and destroying  nested loops joins in favor of hash joins. There's
more CPU, but less buffer cache splashing arounddon't forget to
review init.ora settings for hash, buffer pools, and query planning.
(...be happy to do a flyover of those as well, if you like.)

That's about all i can get in a one minute glance, but there are alot of
people on the list who'll see more. Look to them for longer posts with
more explanation.

Good Luck!

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

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

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

RE: ORA-01555 Mystery (Help)



I think you might have to throw a full hint in there to insure you're really
touching all the blocks (select /*+ FULL*/ count(*) from table_name).

But Jared's correct.  If you're gonna go to that much trouble, might as well
compute statistics.



> --
> From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, January 25, 2002 3:05 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  RE: ORA-01555 Mystery (Help)
> 
> I don't have a definitive answer for that.
> 
> My guess would be that 'compute' would be required
> so that all blocks are touched.
> 
> Another  way of dealing with delayed block cleanouts
> is to do a 'select * from table;'.As long as you are 
> going to touch every block anyway, you might as
> well compute the stats.
> 
> But now I'm speculating.  :)
> 
> Jared
> 
> 
> 
> 
> 
> Walter K <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 01/25/02 11:20 AM
> Please respond to ORACLE-L
> 
>  
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc: 
> Subject:RE: ORA-01555 Mystery (Help)
> 
> 
> Jared, would you elaborate more on this? Does this
> need to be a 'compute' or can it be an 'estimate' on
> the analyze?
> 
> I read the info on Steve's site as suggested by Barb
> and it sounds like block cleanout may be the issue but
> I'm still trying to digest the concept/issue as it
> relates to my circumstance.
> 
> For the others that have contributed to the thread,
> yes, the table is definitely locked in exclusive mode
> (via a different session) before the SELECT is
> performed and the lock is not released until the
> following day. I too was suspicious that the lock was
> accidentally being released.
> 
> -w
> 
> 
> 
> 
> --- [EMAIL PROTECTED] wrote:
> > Delayed block cleanouts can still cause the
> > ORA-1555, even
> > after locking the table in exlusive mode.
> > 
> > That's the purpose of the analyze, to force the
> > block cleanouts.
> > 
> > Jared
> > 
> > 
> > 
> > 
> > 
> > 
> > Paul Baumgartel <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 01/25/02 09:30 AM
> > Please respond to ORACLE-L
> > 
> > 
> > To: Multiple recipients of list ORACLE-L
> > <[EMAIL PROTECTED]>
> > cc: 
> > Subject:RE: ORA-01555 Mystery (Help)
> > 
> > 
> > Sure, but the original post concerns a *query*, not
> > a transaction, and
> > before running the query, the user locked the
> > queried table in
> > exclusive mode, to ensure that no other session
> > could write to the
> > queried table.   How do we account for the query's
> > need to read from
> > rollback? 
> > 
> > 
> > --- "Baker, Barbara"
> > <[EMAIL PROTECTED]> wrote:
> > > 
> > > I have a batch job that does this consistently. 
> > It's the only job in
> > > the
> > > database; it sets the transaction to a hugh
> > rollback segment.  And it
> > > eats
> > > its own tail.
> > > 
> > > Depending on how the job is written, it may need a
> > read consistent
> > > view
> > > itself (as opposed to some other query in the
> > database needing that
> > > read
> > > consistent view.)In that case, it may well go
> > try to read its own
> > > rollback segment, only to find that it's been
> > overwritten.  (Oddly
> > > enough,
> > > even when there's plenty of space to extend the
> > rollback, Oracle will
> > > decide
> > > to overwrite the original rollback segments rather
> > than extend if it
> > > thinks
> > > it doesn't need those segments any more.)
> > > 
> > > I'd strongly suggest you get the stuff from Steve
> > Adams' ixora site
> > > that
> > > places an uncommitted transaction in your rollback
> > segments for the
> > > length
> > > of the run.This will guarantee that the
> > rollback segments don't
> > > get
> > > overwritten.
> > > Good luck!
> > > 
> > > Barb
> > > 
> > > > --
> > > > From:Walter
> > K[SMTP:[EMAIL PROTECTED]]
> > > > Reply To:[EMAIL PROTECTED]
> > > > Sent:Friday, January 25, 2002
> > 9:15 AM
> > > > To:  Multiple recipients of list
> > ORACLE-L
> > > > Subject: ORA-01555 Mystery (Help)
> > > > 
> > > > Hi,
> > > > 
> > > > A user in our data warehousing group is running
> > into
> > > > the old ORA-01555 (snapshot too old) error every
> > time
> > > > she runs a massive (20 million rows) select
> > against
> > > > one table via a view. I confirmed that the view
> > only
> > > > translates to the one table.
> > > > 
> > > > The user swears that no one would be making any
> > > > updates/deletes to the table she is selecting
> > from. I
> > > > suggested she lock the table in exclusive mode,
> > prior
> > > > to running her massive select to guarantee no
> > one else
> > > > could change the data in the table and cause the
> > > > triggering of the 1555 error. Locking the table
> > was a
> > > > viable option because it's a staging table in
> > the
> > > > warehouse itself

RE: ORA-01555 Mystery (Help)


No wonder you're mystified.  This doesn't make sense.
I can understand how you might have had a problem Tues night, but Wed night
you should have sailed.

Here's one more thing to add to your 'bag of tricks':  try running this
query (in batch every 15 minutes or so, if you can) to see what user(s) are
accessing which rollbacks at any given time.  Probably won't help (unless
Mladen is right, and someone is not coming clean with the complete truth).
But it can't hurt.

select TO_CHAR(SYSDATE,'DD-MON-:HH24:MI:SS'),
   osuser o,   username u,
   segment_name s, substr(sa.sql_text,1,500) txt
from v$session s,
 v$transaction t,
 dba_rollback_segs r,
 v$sqlarea sa
where s.taddr=t.addr
and   t.xidusn=r.segment_id(+)
and   s.sql_address=sa.address(+)
/



> --
> From: Walter K[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, January 25, 2002 12:30 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  RE: ORA-01555 Mystery (Help)
> 
> Another fact, that should be mentioned, is that the
> table in question was built (loaded) two days ago. The
> nightly ETL processes for the warehouse are pretty
> substantial and the likelyhood of a block not getting
> cleaned/flushed out for a couple days should be nil.
> 
> To summarize:
> 
> 1. Tuesday Night: 
> -truncate/load table 'A' (24 million rows)
> -Perform massive select from 'A', fails 5 hours later
> with 1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY
> OTHER SESSION
> 2. Wednesday Night: 
> -Perform massive select against 'A', fails 5 hours
> later with ORA-1555. NO DML BEING PERFORMED AGAINST
> 'A' BY ANY OTHER SESSION
> 3. Thursday night: 
> -'lock table A in exclusive mode;' via session 123
> -perform massive select against 'A', fails 5 hours
> later with ORA-1555 via session 124. NO DML BEING
> PERFORMED AGAINST 'A' BY ANY OTHER SESSION
> -session 123 still has exclusive lock on table 'A' the
> following morning
> 4. Friday morning:
> -Walter is stumped but still trying to figure out a
> solution! :)
> 
> -w
> 
> 
> --- [EMAIL PROTECTED] wrote:
> > Delayed block cleanouts can still cause the
> > ORA-1555, even
> > after locking the table in exlusive mode.
> > 
> > That's the purpose of the analyze, to force the
> > block cleanouts.
> > 
> > Jared
> > 
> > 
> > 
> > 
> > 
> > 
> > Paul Baumgartel <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 01/25/02 09:30 AM
> > Please respond to ORACLE-L
> > 
> >  
> > To: Multiple recipients of list ORACLE-L
> > <[EMAIL PROTECTED]>
> > cc: 
> > Subject:RE: ORA-01555 Mystery (Help)
> > 
> > 
> > Sure, but the original post concerns a *query*, not
> > a transaction, and
> > before running the query, the user locked the
> > queried table in
> > exclusive mode, to ensure that no other session
> > could write to the
> > queried table.   How do we account for the query's
> > need to read from
> > rollback? 
> > 
> > 
> > --- "Baker, Barbara"
> > <[EMAIL PROTECTED]> wrote:
> > > 
> > > I have a batch job that does this consistently. 
> > It's the only job in
> > > the
> > > database; it sets the transaction to a hugh
> > rollback segment.  And it
> > > eats
> > > its own tail.
> > > 
> > > Depending on how the job is written, it may need a
> > read consistent
> > > view
> > > itself (as opposed to some other query in the
> > database needing that
> > > read
> > > consistent view.)In that case, it may well go
> > try to read its own
> > > rollback segment, only to find that it's been
> > overwritten.  (Oddly
> > > enough,
> > > even when there's plenty of space to extend the
> > rollback, Oracle will
> > > decide
> > > to overwrite the original rollback segments rather
> > than extend if it
> > > thinks
> > > it doesn't need those segments any more.)
> > > 
> > > I'd strongly suggest you get the stuff from Steve
> > Adams' ixora site
> > > that
> > > places an uncommitted transaction in your rollback
> > segments for the
> > > length
> > > of the run.This will guarantee that the
> > rollback segments don't
> > > get
> > > overwritten.
> > > Good luck!
> > > 
> > > Barb
> > > 
> > > > --
> > > > From:Walter
> > K[SMTP:[EMAIL PROTECTED]]
> > > > Reply To:[EMAIL PROTECTED]
> > > > Sent:Friday, January 25, 2002
> > 9:15 AM
> > > > To:  Multiple recipients of list
> > ORACLE-L
> > > > Subject: ORA-01555 Mystery (Help)
> > > > 
> > > > Hi,
> > > > 
> > > > A user in our data warehousing group is running
> > into
> > > > the old ORA-01555 (snapshot too old) error every
> > time
> > > > she runs a massive (20 million rows) select
> > against
> > > > one table via a view. I confirmed that the view
> > only
> > > > translates to the one table.
> > > > 
> > > > The user swears that no one would be making any
> > > > updates/deletes to the table she is selecting
> > from. I
> > > > suggested she lock the table in exclusive mode,
> >

Re:RE: RE: ORA-01555 Mystery (Help)

2002-01-25 Thread dgoulet


HUMMM, Sounds just like a certain PeopleSoft Cobol process we have.  Am there,
doing that, and just as frustrated.  Damned third party software vendors!!  If
only they would admit that these errors can occur.

Dick Goulet

Reply Separator
Author: "Baker; Barbara" <[EMAIL PROTECTED]>
Date:   1/25/2002 10:39 AM


> Dick:
> This makes the assumption that Walter can get to the code, find out what
> it's doing, and make modifications.  (In our case, we can't.  The code is
> vendor-supplied, unchangeable, and is written in Cobol).
> 
> It also sounds like this might be happening in the middle of the night.
> I'd guess there's a limit to how much information Walter can gather about
> what happened 5 hours into the job at 3:00 am
> 
> If he's desperate to get the data loaded and he can't change the sql, then
> his options are limited.
> 
> I believe all the possible causes for 1555 errors have been listed in this
> thread.  Hopefully he can identify which is causing the grief and find a
> resolution.
> 
> Barb
> 
> 
>   --
>   From:   [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]]
>   Sent:   Friday, January 25, 2002 11:09 AM
>   To: Baker; Barbara; Multiple recipients of list ORACLE-L
>   Subject:Re:RE: ORA-01555 Mystery (Help)
> 
>   Barb,
> 
>   I've tried Steve's idea in the past and although it sorta fixed
> the problem
>   with the large batch job, it created problems elsewhere.  It also
> did not
>   totally fix the problem when other applications updated parts of the
> table(s)
>   and committed their transaction.  Many folks believe that by
> allocating a large
>   rollback segment to their session they have fixed the problem.
> Wrong, this
>   particular issue can be caused by your own application plus anyone
> else who is
>   using the database and other rollback segments.
> 
>   The real issue here is to either find out who or what is
> updating the
>   underlying table or else speeding up the process.  There were two
> points that I
>   found easy to implement that fixed 90% of our errors.
> 
>   1) Don't commit across a cursor.  In this scenario look for
> cases where your
>   pulling data from a table, updating that table, and then continuing
> to read data
>   from the cursor.  This one will pop a 1555 very regularly since the
> cursor
>   depends on a read consistent view, but you just released the
> rollback segments.
> 
>   2) Use an order or group by in the select statement.  This one
> sounds odd,
>   but it does work.  By placing either an order by or group by clause
> in the
>   select statement you force Oracle to read all of the data at one
> time, place it
>   in a temp segment, and then hand it over.  The end result is that
> when the first
>   row of data appears in your application you no longer need any
> rollback to
>   create a read consistent view.  If your just pulling from the table,
> then Oracle
>   hands over a row as it satisfies the query criteria.  OH, did you
> just update
>   and commit a change?  Well that is NOT going to be included in your
> result set
>   since it is already locked in concrete.
> 
>   Try one of these & see if it fixes your problem.
> 
>   Dick Goulet
> 
>   Reply Separator
>   Subject:RE: ORA-01555 Mystery (Help)
>   Author: "Baker; Barbara" <[EMAIL PROTECTED]>
>   Date:   1/25/2002 8:52 AM
> 
> 
>   I have a batch job that does this consistently.  It's the only job
> in the
>   database; it sets the transaction to a hugh rollback segment.  And
> it eats
>   its own tail.
> 
>   Depending on how the job is written, it may need a read consistent
> view
>   itself (as opposed to some other query in the database needing that
> read
>   consistent view.)In that case, it may well go try to read its
> own
>   rollback segment, only to find that it's been overwritten.  (Oddly
> enough,
>   even when there's plenty of space to extend the rollback, Oracle
> will decide
>   to overwrite the original rollback segments rather than extend if it
> thinks
>   it doesn't need those segments any more.)
> 
>   I'd strongly suggest you get the stuff from Steve Adams' ixora site
> that
>   places an uncommitted transaction in your rollback segments for the
> length
>   of the run.This will guarantee that the rollback segments don't
> get
>   overwritten.
>   Good luck!
> 
>   Barb
> 
>   > --
>   > From: Walter K[SMTP:[EMAIL PROTECTED]]
>   > Reply To: [EMAIL PROTECTED]
>   > Sent: Friday, January 25, 2002 9:15 AM
>   > To:   Multiple recipients of list ORACLE-L
>   > Subject:  ORA-01555 Mystery (Help)
>   > 
>   > Hi,
>   > 
>   > A user in our data warehousing group i

RE: SMON - Does it cause a degrade?

2002-01-25 Thread Godlewski, Melissa

Title: RE: SMON - Does it cause a degrade?





Check out meta link note:61997.1  SMON - Temporary Segment Cleanup and Free Space Coalescing.  For one explanation.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday, January 25, 2002 4:50 PM
To: Multiple recipients of list ORACLE-L
Subject: SMON - Does it cause a degrade?



Hey Fellas,


I have an application DBA who insists that the pctincrease at the data
tablespace should be set to 0 so that SMON does not coalesce the
tablespace. He says coalesce will be performed by using a scheduled batch
job written for that purpose. He states that having SMON to perform an
coalesce of the tablespace could cause an performance degrade??? I have
never heard of such a thing, but then I dont wanna argue with him. He's got
wrinkles on his face, and grey hair ;-)


My argument would be, go back to the drawing board, get your tables sized
properly, if you anticipate fragmentation. And SMON does not cause a
performance degrade? It wakes up every 5 minutes, does hold ST enqueue
locks if a tablespace needs coalescing, but it does not cause a performance
degrade? Or does it???


Now, can I have a definitive word on this? Any sites, white papers, to
refer to that says so, or to the contrary. I need to convince the higher
ups.


Raj


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


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

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

RE: Standby database question

2002-01-25 Thread Jeremiah Wilton


On Fri, 25 Jan 2002, Molina, Gerardo wrote:

> There is one last, but important step.
> 
> You need to recreate standby control file...

Why do you have to do that?  It doesn't say to do that in the
documentation.  The new datafiles are reflected in the standby
controlfile through normal recovery and by issuing the 'alter database
create datafile' command.

There is no need to re-dump and copy a new standby controlfile, and
definitely no need to shut any database down.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Fri, 25 Jan 2002, Molina, Gerardo wrote:

> on primary:
> 
> alter database create standby controlfile as '';
> 
> ftp this new file to standby
> 
> on standby:
> 
> shutdown immediate
> 
> copy new control file to appropriate locations with correct file name.
> 
> startup nomount
> 
> alter database mount standby database
> 
> -Original Message-
> Sent: Friday, January 25, 2002 12:01 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> On Fri, 25 Jan 2002, [EMAIL PROTECTED] wrote:
> 
> > One of the co-workers has a hot standby database.  Logs are applied
> > at some interval.  He has to add a tablespace.  What is necessay to
> > make standby database aware of this?
> 
> This is clearly documented in the Oracle8i Standby Database Concepts
> and Administration Manual.
> 
> http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/
> a76995/standbys.htm#27363
> 
> In short, you just add the tablespace to the primary, wait for the
> standby to fail with ORA-01157, then issue the following command on
> the standby:
> 
> SQL> alter database create datafile '' as '';
> 
> Where foo is the location of the datafile on the primary, and bar is
> the location on the standby (usually the same).
> 
> If you create a tablespace with several datafiles, you will have to
> issue this command a few times after recovering the standby and
> waiting for the ORA-01157 each time.
> 
> Don't fall into the trap some people do where they think they have to
> copy the new file over to the standby every time they create a
> datafile.

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

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

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

RE: ORA-01555 Mystery (Help)


I don't have a definitive answer for that.

My guess would be that 'compute' would be required
so that all blocks are touched.

Another  way of dealing with delayed block cleanouts
is to do a 'select * from table;'.As long as you are 
going to touch every block anyway, you might as
well compute the stats.

But now I'm speculating.  :)

Jared





Walter K <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/25/02 11:20 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: ORA-01555 Mystery (Help)


Jared, would you elaborate more on this? Does this
need to be a 'compute' or can it be an 'estimate' on
the analyze?

I read the info on Steve's site as suggested by Barb
and it sounds like block cleanout may be the issue but
I'm still trying to digest the concept/issue as it
relates to my circumstance.

For the others that have contributed to the thread,
yes, the table is definitely locked in exclusive mode
(via a different session) before the SELECT is
performed and the lock is not released until the
following day. I too was suspicious that the lock was
accidentally being released.

-w




--- [EMAIL PROTECTED] wrote:
> Delayed block cleanouts can still cause the
> ORA-1555, even
> after locking the table in exlusive mode.
> 
> That's the purpose of the analyze, to force the
> block cleanouts.
> 
> Jared
> 
> 
> 
> 
> 
> 
> Paul Baumgartel <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 01/25/02 09:30 AM
> Please respond to ORACLE-L
> 
> 
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc: 
> Subject:RE: ORA-01555 Mystery (Help)
> 
> 
> Sure, but the original post concerns a *query*, not
> a transaction, and
> before running the query, the user locked the
> queried table in
> exclusive mode, to ensure that no other session
> could write to the
> queried table.   How do we account for the query's
> need to read from
> rollback? 
> 
> 
> --- "Baker, Barbara"
> <[EMAIL PROTECTED]> wrote:
> > 
> > I have a batch job that does this consistently. 
> It's the only job in
> > the
> > database; it sets the transaction to a hugh
> rollback segment.  And it
> > eats
> > its own tail.
> > 
> > Depending on how the job is written, it may need a
> read consistent
> > view
> > itself (as opposed to some other query in the
> database needing that
> > read
> > consistent view.)In that case, it may well go
> try to read its own
> > rollback segment, only to find that it's been
> overwritten.  (Oddly
> > enough,
> > even when there's plenty of space to extend the
> rollback, Oracle will
> > decide
> > to overwrite the original rollback segments rather
> than extend if it
> > thinks
> > it doesn't need those segments any more.)
> > 
> > I'd strongly suggest you get the stuff from Steve
> Adams' ixora site
> > that
> > places an uncommitted transaction in your rollback
> segments for the
> > length
> > of the run.This will guarantee that the
> rollback segments don't
> > get
> > overwritten.
> > Good luck!
> > 
> > Barb
> > 
> > > --
> > > From:Walter
> K[SMTP:[EMAIL PROTECTED]]
> > > Reply To:[EMAIL PROTECTED]
> > > Sent:Friday, January 25, 2002
> 9:15 AM
> > > To:  Multiple recipients of list
> ORACLE-L
> > > Subject: ORA-01555 Mystery (Help)
> > > 
> > > Hi,
> > > 
> > > A user in our data warehousing group is running
> into
> > > the old ORA-01555 (snapshot too old) error every
> time
> > > she runs a massive (20 million rows) select
> against
> > > one table via a view. I confirmed that the view
> only
> > > translates to the one table.
> > > 
> > > The user swears that no one would be making any
> > > updates/deletes to the table she is selecting
> from. I
> > > suggested she lock the table in exclusive mode,
> prior
> > > to running her massive select to guarantee no
> one else
> > > could change the data in the table and cause the
> > > triggering of the 1555 error. Locking the table
> was a
> > > viable option because it's a staging table in
> the
> > > warehouse itself. She locked the table in
> exclusive
> > > mode last night and it locked; fired off her
> query,
> > > and it failed 5 hours later with the 1555 error
> again.
> > > 
> > > I'm stumped on this. I just don't see how this
> is
> > > possible. Any suggestions?
> > > 
> > > Thanks!!!
> > > -w
> > > 
> > >
> __
> .
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListG

SMON - Does it cause a degrade?


Hey Fellas,

I have an application DBA who insists that the pctincrease at the data
tablespace should be set to 0 so that SMON does not coalesce the
tablespace. He says coalesce will be performed by using a scheduled batch
job written for that purpose. He states that having SMON to perform an
coalesce of the tablespace could cause an performance degrade??? I have
never heard of such a thing, but then I dont wanna argue with him. He's got
wrinkles on his face, and grey hair ;-)

My argument would be, go back to the drawing board, get your tables sized
properly, if you anticipate fragmentation. And SMON does not cause a
performance degrade? It wakes up every 5 minutes, does hold ST enqueue
locks if a tablespace needs coalescing, but it does not cause a performance
degrade? Or does it???

Now, can I have a definitive word on this? Any sites, white papers, to
refer to that says so, or to the contrary. I need to convince the higher
ups.

Raj

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

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

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

RE: Standby database question

2002-01-25 Thread Molina, Gerardo

There is one last, but important step.

You need to recreate standby control file...

on primary:

alter database create standby controlfile as '';

ftp this new file to standby

on standby:

shutdown immediate

copy new control file to appropriate locations with correct file name.

startup nomount

alter database mount standby database

HTH,
Gerardo

-Original Message-
Sent: Friday, January 25, 2002 12:01 PM
To: Multiple recipients of list ORACLE-L

On Fri, 25 Jan 2002, [EMAIL PROTECTED] wrote:

> One of the co-workers has a hot standby database.  Logs are applied
> at some interval.  He has to add a tablespace.  What is necessay to
> make standby database aware of this?

This is clearly documented in the Oracle8i Standby Database Concepts
and Administration Manual.

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/
a76995/standbys.htm#27363

In short, you just add the tablespace to the primary, wait for the
standby to fail with ORA-01157, then issue the following command on
the standby:

SQL> alter database create datafile '' as '';

Where foo is the location of the datafile on the primary, and bar is
the location on the standby (usually the same).

If you create a tablespace with several datafiles, you will have to
issue this command a few times after recovering the standby and
waiting for the ORA-01157 each time.

Don't fall into the trap some people do where they think they have to
copy the new file over to the standby every time they create a
datafile.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

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

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

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

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

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

Re: Backup Strategy


That's fine.  I'm in the good mood, TGIF...
and I just resolved another 'customer issue' (has nothing to do with
backup/recovery :)
Hopefully, it's the last one for this week.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, January 25, 2002 3:10 PM


> Ditto.
>
> Tim,
>
> I think we should save all these emails, so when Igor posts a message
asking
> for help on recovering a database that won't recover for some reason
then...
>
> Sorry, Igor I couldn't resist.
>
> Chris
>
> -Original Message-
> Sent: Friday, January 25, 2002 2:39 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Igor,
>
> I think your missing the point...  You state...
>
> "But, I'll take my chances, because as I said before, MetaLink
Note:139327.1
> didn't convince me at all that "OCOPY" is any better than regular "NT
Copy"
> command."
>
> But, I assume that the opposite is also true?  "NT Copy" isn't any better
> then "OCOPY"?  My assumption is that they are functionality equivalent and
> perform about the same?  But, the big difference is that "OCOPY" is the
> supported way to perform backups on NT while "NT Copy" is not...  So why
> "take a chance" when it doesn't gain you any benefit?  If "NT Copy" is
> significantly better then "OCOPY" for some reason then let me know...
Then
> maybe you have a risk/reward argument that I can understand...  If not,
why
> gamble for zero gain?
>
> Tim
>
> -Original Message-
> Sent: Friday, January 25, 2002 2:07 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Well, I know what you mean.
> But, I'll take my chances, because as I said before, MetaLink
Note:139327.1
> didn't convince me at all that "OCOPY" is any better than regular "NT
Copy"
> command.
>
> Besides, I am keeping two generations of backup (the latest and the one
> prior to that), so if anything goes wrong with the latest backup image of
> the db file, I can always recover, using older backup and archived
RedoLogs
> from both backups.
>
> Also, our customers wouldn't wait for Oracle support , and having two
> generations of backups, I can resolve potential issues much faster than
...
>
> Igor Neyman, OCP DBA
> [EMAIL PROTECTED]
>
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, January 25, 2002 12:30 PM
>
>
> > Igor,
> >
> > That sounds good, but what is Oracle Support going to say when you call
> them
> > for support on a database recovery and you mention that you used NT
COPY??
> > Your hosed, if there attitude is you should have used OCOPY so we can't
> help
> > you.
> >
> > Chris
> >
> > -Original Message-
> > Sent: Friday, January 25, 2002 11:26 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Ok, I guess, I owe some explanation here, since I've got a lot of
spanking
> > (replies, some rather sarcastic) regarding this issue.
> > My original note comes from my "real life" experience, so I'm still
> standing
> > behind it.
> > Sorry, it'll be kind of long, but if you are really interested...
> >
> > Couple years ago, when we were preparing first release of our product, I
> > read of course about "NT Copy" versus "Oracle Ocopy".
> > Still I decided to test it, because not always I trust what I read, and
I
> > like to get proof myself.
> > Testing of online ("hot") backup/recovery scenario showed, that using
"NT
> > Copy" command in backup scripts is perfectly fine, when creating backup
> set
> > of files on the disk.  And there is no problem restoring from this
backup.
> > Now this "disk backup" set of files could be saved on tape, using
NTBACKUP
> > (that's the one, that really can not copy file, if it's opened by some
> other
> > program.  But that's not the case with prepared in advance "disk
backup").
> > "NT Copy" has no problems copying files opened already by Oracle, and
> backup
> > is consistent, as long of course as I am using "alter tablespace 
> > begin backup" before copying relevant files and "alter tablespace 
> end
> > backup" after finishing files copy.
> > So, those scripts (using "NT Copy") were put into production, and now
have
> > been used for more than two years on more than hundred
installations/sites
> > (the number keeps growing).
> > From time to time, our field engineers are bringing back to me sets of
> > online (can not use "cold" backup - our systems should run 24*7, I'm not
> > saying they are, but we are trying to minimize downtime) backed up files
> (db
> > files and archived RedoLog files), and I recover them with no problem
(we
> > need this, to test how the upgrade to next release of our product will
run
> > against "real" customers data).
> >
> > Now, about MetaLink Note:139327.1
> > It says:
> > 
> > Ocopy  opens the file using CreateFile() with the FILE_SHARE_READ and
> > FILE_SHARE_WRITE  flags. This allows writing to continue while we take
the
> > backup.  Inconsistencies in the backup are rep

(Fwd) Re: ORACLE-L Digest -- Volume 2002, Number 025




--- Forwarded message follows ---
Date sent:  Fri, 25 Jan 2002 12:38:47 -0800 
(PST)
Number 025
To: [EMAIL PROTECTED]
Copies to:  [EMAIL PROTECTED]


Could one of you forward this to your list?  I'm not a 
s*u*b*s*c*r*i*b*e*r.

Also, I'm very curious if Igor can recover from his 
COPY hot 
backups, and if so, whether Oracle Support might 
actually be
wrong.  

If nothing else, one might want to use OCOPY in order
to get Oracle support in the case something goes 
wrong.  I 
doubt they'd be able to help if one used an 
unsupported backup
approach...

Thanks.
 -Tom

--- "Thomas B. Cox" <[EMAIL PROTECTED]> wrote:
> 
> I gave the right advice for the wrong reasons.  My bad. I'll revise
> the next release of the paper and credit Igor -- thanks.
> 
> Here's the word from Oracle Support:
> 
> 
> Doc ID:  Note:139327.1 
> 
> The Differences between Windows NT COPY and Oracle OCOPY When Doing
> Backups:
>
==
==
> 
> When doing an online backup, should you use the Windows NT COPY
> command, or the Oracle OCOPY command?
> 
> While doing online backups you should use OCOPY, or Oracle7 EBU, or
> Oracle8 
> (and later) RMAN.  With the OCOPY command you could copy to a backup
> directory 
> on the hard drive but cannot use OCOPY to copy a file to tape. The
> other option
> if you do not want to use ocopy to perform your backup as this does
> require a 
> lot of disk space is EBU/RMAN that comes with Oracle. Depending on
> your
> Oracle 
> version, the distribution includes a utility called EBU (Oracle7) or
> RMAN 
> (Oracle8 and later) that can be used for online recovery as well. You
> will need
> to use a media management product to move the data from RMAN to tape.
> Legato 
> Storage Manager is provided however there are other products that are
> supported
> to be used with this tool.
> 
> To backup you will need to use the utility delivered by Oracle, the
> ocopy 
> command. Utilities like the NT commands copy, xcopy CANNOT be used to
> back up. 
> The Windows NT feature to be aware of is that NT Backup does not
> allow
> files in
> use to be copied, so you must use the OCOPY utility that Oracle
> provides to 
> copy the open database files to another disk location. Since OCOPY
> cannot copy 
> files directly to tape, you will then need to use NT Backup or copy
> or
> a 
> similar utility to copy the files to tape, as required. 
> 
> OCOPY allows writing to continue while the backup is running. The NT
> COPY is a 
> closed copy and the files may be marked either as "fuzzy" or
> "corrupt."
>  Ocopy 
> opens the file using CreateFile() with the FILE_SHARE_READ and
> FILE_SHARE_WRITE 
> flags. This allows writing to continue while we take the backup.  
> Inconsistencies in the backup are repaired by applying archived redo
> during 
> recovery. The 'copy' command from NT doesn't use these flags since it
> wants to 
> prevent writes to the file while the copy is taking place. 
> 
> REFERENCES
>   [NOTE:41946.1] NT Online Backups 
>   Oracle Backup and Recovery Guide
> 
> 
> --- "Eric D. Pierce" <[EMAIL PROTECTED]> wrote:
> > fyi: 
> > 
> > On 25 Jan 2002 at 1:05, Oracle RDBMS Community Forum 
> > <[EMAIL PROTECTED]> wrote:
> > 
> > 
> > > 
> > > --
> > > 
> > >  From: "Igor Neyman" <[EMAIL PROTECTED]>
> > >  Date: Thu, 24 Jan 2002 16:14:25 -0500
> > >  Subject: Re: Backup Strategy
> > > 
> > > I took a quick look at this paper, and found right away, that
> it's
> > not
> > > very accurate, at least in one issue. i.e., it states : < quote>
> > The
> > > Windows NT command COPY can be used to create a cold backup of a
> > database.
> > > It cannot be used to make a hot backup. Attempting to perform a
> hot
> > backup
> > > with COPY will usually result in an error message being generated
> > as the
> > > COPY command fails - during a hot backup the database is running
> > and thus
> > > the database files are locked by the Oracle database process, and
> > COPY
> > > cannot work on a file that is so locked. < /quote>
> > > 
> > > Wrong. NT 'COPY' has no problems copying 'opened' oracle db
> files.
> > > I'm using it in 'hot backup' scripts on many dozens systems, and
> it
> > works
> > > fine.
> > > 
> > > Don't know about the accuracy of the rest of the paper, didn't
> have
> > time
> > > to read it all.
> > > 
> > > Igor Neyman, OCP DBA
> > > [EMAIL PROTECTED]
> > > 
> > > 
> > > - Original Message -
> > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > > Sent: Thursday, January 24, 2002 7:35 AM
> > > 
> > > 
> > > > Hi All,
> > > >
> > > >  http://www.geocities.com/tbcox23/
> > > >
> > > > Go here and get the paper.
> > > >
> > > > Regards
> > > > Venkat
> > > > --
> > > 
> > 
> > 
> 
> 
> =
> Thomas B. Cox "Saepe in errore sed numquam in dubito"
> [EMAIL PROTECTED]   http://www.geocities.com/tbcox23/
> 
> "The whole aim of practical politi

Re: v$session question

2002-01-25 Thread Catherine LeBlanc

Yes, that will do it, but module does not have ifrun60, it has the actual 
Oracle form name that the user is running.
Joe, I have a script to show all active connections and all that stuff if 
you want it.

Catherine LeBlanc
DBA, Bates College, Lewiston, ME

At 07:36 AM 1/25/02 -0800, you wrote:

>Hi,
>
>Try module in v$session (just a guess)
>
>Jack
>
>
>Joe LaCascio <[EMAIL PROTECTED]>@fatcity.com on 25-01-2002 15:35:24
>
>Please respond to [EMAIL PROTECTED]
>
>Sent by:  [EMAIL PROTECTED]
>
>
>To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL)
>
>
>In the past, I was running Oracle 8.1.5 and the clients were running
>Oracle Forms 4.5.  When I queried v$session and looked at the program
>field  I could see what clients where running f45run32.exe.
>
>Now we are on Oracle 8.1.6 and the clients are running Forms60.  When I
>now query v$session the program filed is null?
>
>What view could I query in 8.1.6 to see which users are running
>ifrun60.exe?
>
>Thanks,
>Joe

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

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

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

RE: Databases on Solaris: Online Forum Jan. 22-28

2002-01-25 Thread Eswar the MAD


Hi,

I dont know abt the book, but i agree with Jerad that there traffic is low, 
and the ans are pointers to the book (another marketing stratagy or what 
ever it is :D). But for my questions he ans with out the book (may be its 
not in the book :D)

Regards

OraEtM!


>From: "James McCann" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: Databases on Solaris: Online Forum Jan. 22-28
>Date: Fri, 25 Jan 2002 02:45:22 -0800
>
>Does anyone know if the book is any good? I'm thinking about getting it,
>
>Thanks,
>
>Jim
>
>-Original Message-
>[EMAIL PROTECTED]
>Sent: 24 January 2002 23:25
>To: Multiple recipients of list ORACLE-L
>
>
>That could have something to do with the questions not being pointed
>enough.
>
>Could be they're pointless?
>
>I was going to provide a couple of examples, but it seems there is too
>much
>traffic to this site for me to get back on right now.
>
>Jared
>
>
>
>
>
>
>"Jesse, Rich" <[EMAIL PROTECTED]>
>Sent by: [EMAIL PROTECTED]
>01/24/02 01:45 PM
>Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L 
><[EMAIL PROTECTED]>
> cc:
> Subject:RE: Databases on Solaris: Online Forum Jan. 22-28
>
>
>Hmmm...many (most?  all?) of the answers given in the forum however, seem
>to
>be pointers to chapters in Mr. Packer's book.
>
>Just an observation.
>
>:)
>
>Rich Jesse   System/Database Administrator
>[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI
>USA
>
>
>-Original Message-
>Sent: Thursday, January 24, 2002 1:49 PM
>To: Multiple recipients of list ORACLE-L
>
>
>
>This list pays for itself once again.
>
>Jared is charging the rest of you too, right?
>
>Steve
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: James McCann
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).




_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.

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

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

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

RE: Backup Strategy

2002-01-25 Thread Grabowy, Chris


Ditto.

Tim,

I think we should save all these emails, so when Igor posts a message asking
for help on recovering a database that won't recover for some reason then...

Sorry, Igor I couldn't resist.

Chris

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


Igor,

I think your missing the point...  You state...

"But, I'll take my chances, because as I said before, MetaLink Note:139327.1
didn't convince me at all that "OCOPY" is any better than regular "NT Copy"
command."

But, I assume that the opposite is also true?  "NT Copy" isn't any better
then "OCOPY"?  My assumption is that they are functionality equivalent and
perform about the same?  But, the big difference is that "OCOPY" is the
supported way to perform backups on NT while "NT Copy" is not...  So why
"take a chance" when it doesn't gain you any benefit?  If "NT Copy" is
significantly better then "OCOPY" for some reason then let me know...  Then
maybe you have a risk/reward argument that I can understand...  If not, why
gamble for zero gain?

Tim

-Original Message-
Sent: Friday, January 25, 2002 2:07 PM
To: Multiple recipients of list ORACLE-L


Well, I know what you mean.
But, I'll take my chances, because as I said before, MetaLink Note:139327.1
didn't convince me at all that "OCOPY" is any better than regular "NT Copy"
command.

Besides, I am keeping two generations of backup (the latest and the one
prior to that), so if anything goes wrong with the latest backup image of
the db file, I can always recover, using older backup and archived RedoLogs
from both backups.

Also, our customers wouldn't wait for Oracle support , and having two
generations of backups, I can resolve potential issues much faster than ...

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, January 25, 2002 12:30 PM


> Igor,
>
> That sounds good, but what is Oracle Support going to say when you call
them
> for support on a database recovery and you mention that you used NT COPY??
> Your hosed, if there attitude is you should have used OCOPY so we can't
help
> you.
>
> Chris
>
> -Original Message-
> Sent: Friday, January 25, 2002 11:26 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Ok, I guess, I owe some explanation here, since I've got a lot of spanking
> (replies, some rather sarcastic) regarding this issue.
> My original note comes from my "real life" experience, so I'm still
standing
> behind it.
> Sorry, it'll be kind of long, but if you are really interested...
>
> Couple years ago, when we were preparing first release of our product, I
> read of course about "NT Copy" versus "Oracle Ocopy".
> Still I decided to test it, because not always I trust what I read, and I
> like to get proof myself.
> Testing of online ("hot") backup/recovery scenario showed, that using "NT
> Copy" command in backup scripts is perfectly fine, when creating backup
set
> of files on the disk.  And there is no problem restoring from this backup.
> Now this "disk backup" set of files could be saved on tape, using NTBACKUP
> (that's the one, that really can not copy file, if it's opened by some
other
> program.  But that's not the case with prepared in advance "disk backup").
> "NT Copy" has no problems copying files opened already by Oracle, and
backup
> is consistent, as long of course as I am using "alter tablespace 
> begin backup" before copying relevant files and "alter tablespace 
end
> backup" after finishing files copy.
> So, those scripts (using "NT Copy") were put into production, and now have
> been used for more than two years on more than hundred installations/sites
> (the number keeps growing).
> From time to time, our field engineers are bringing back to me sets of
> online (can not use "cold" backup - our systems should run 24*7, I'm not
> saying they are, but we are trying to minimize downtime) backed up files
(db
> files and archived RedoLog files), and I recover them with no problem (we
> need this, to test how the upgrade to next release of our product will run
> against "real" customers data).
>
> Now, about MetaLink Note:139327.1
> It says:
> 
> Ocopy  opens the file using CreateFile() with the FILE_SHARE_READ and
> FILE_SHARE_WRITE  flags. This allows writing to continue while we take the
> backup.  Inconsistencies in the backup are repaired by applying archived
> redo  during  recovery. The 'copy' command from NT doesn't use these flags
> since it  wants to  prevent writes to the file while the copy is taking
> place.
> 
>
> I don't think, it's very accurate, and here is why:
> When during online backup I run "NT copy" against db file, the file is
> already opened by Oracle (at moment, when I "open" the database).
> So, even if "NT copy" opens file without FILE_SHARE_READ and
> FILE_SHARE_WRITE  flags, all it means is that "Subsequent open operations
on
> the object will fail" (quote from NT do

Re: Standby database question

2002-01-25 Thread Jeremiah Wilton

On Fri, 25 Jan 2002, [EMAIL PROTECTED] wrote:

> One of the co-workers has a hot standby database. Logs are applied
> at some interval. He has to add a tablespace. What is necessay to
> make standby database aware of this?

This is clearly documented in the Oracle8i Standby Database Concepts
and Administration Manual.

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76995/standbys.htm#27363

In short, you just add the tablespace to the primary, wait for the
standby to fail with ORA-01157, then issue the following command on
the standby:

SQL> alter database create datafile '' as '';

Where foo is the location of the datafile on the primary, and bar is
the location on the standby (usually the same).

If you create a tablespace with several datafiles, you will have to
issue this command a few times after recovering the standby and
waiting for the ORA-01157 each time.

Don't fall into the trap some people do where they think they have to
copy the new file over to the standby every time they create a
datafile.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

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

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

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

RE: Session_wait

2002-01-25 Thread Cherie_Machler



Sergey,

Have you considered adding an index to that queried column in table B?
Many third-party vendors allow the DBA to add indexes even when they
won't allow them to alter the code.

Something to consider.

Cherie Machler
Oracle DBA
Gelco Information Network


   
   
"Babich ,  
   
Sergey"  To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
   Subject: RE: Session_wait 
   
Sent by:   
   
[EMAIL PROTECTED] 
   
om 
   
   
   
   
   
01/25/02 12:31 
   
PM 
   
Please respond 
   
to ORACLE-L
   
   
   
   
   




Hi, Ross,
This has been running for about 4 hours now. I got an SQL trace file, and
looked at the execution plans. So here's the deal. There's an interesting
join condition "...where A.col1=B.col1.". However, A has a few hundred
distinct values in that column, none of them being NULL, and B, which has a
few hundred thousand rows, has ALL NULLs in the corresponding column, and
that column is not indexed, too. That's the query where it sits for a
couple
of hours. Guess what the optimizer is doing (8i)? I think internal effects
are secondary in this scenario. It is the production (including the
database) designed by the company named DELTEK, so nobody can change the
code. Anyway, I reported my findings...
Thank you very much for your help, it's always appreciated.
Best,
Sergey

 -Original Message-
Sent:   Friday, January 25, 2002 12:56 PM
To:Multiple recipients of list ORACLE-L

This is a busy little beaver...how long you say this runs?

opened cursors cumulative  1072
session logical reads  77233609
db block gets   2642119
consistent gets74591490
physical reads   148822
db block changes3005410
consistent changes  141
no work - consistent read gets 72058049
table scans (short tables)   210918
table scans (long tables)36
table scan rows gotten798264962
table scan blocks gotten   71788386
table fetch by rowid1074164
leaf node splits   4018
execute count 74445
bytes sent via SQL*Net to client  59650
bytes received via SQL*Net from client84233
SQL*Net roundtrips to/from client   342
buffer is not pinned count 73513922

I see some updates (or inserts) and heavy reads...at first glance,
though, you don't appear to be I/O throttled but likely have inefficiencies
in buffer cache. Pin some small tables? Reexamine access paths for
fts, even if on "small" tables (generally defined to be around
5% in blocks of buffer cache size?)and consider seeking out
and destroying  nested loops joins in favor of hash joins. There's
more CPU, but less buffer cache splashing arounddon't forget to
review init.ora settings for hash, buffer pools, and query planning.
(...be happy to do a flyover of those as

Re: ORACLE-L Digest -- Volume 2002, Number 025

Thomas:

Here is the person to credit:

  "Igor Neyman" <[EMAIL PROTECTED]>

btw, any advice on libertarians to support in the 
election for california governor?

thanks,
ep

On 25 Jan 2002 at 11:12, Thomas B. Cox 
<[EMAIL PROTECTED]> wrote:

Date sent:  Fri, 25 Jan 2002 11:12:11 -0800 (PST)
Number 025
To: [EMAIL PROTECTED]
Copies to:  [EMAIL PROTECTED]

> 
> I gave the right advice for the wrong reasons.  My bad. I'll revise the
> next release of the paper and credit you -- thanks.
> 
> Here's the word from Oracle Support:
> 
> 
> Doc ID:  Note:139327.1 
> 
> The Differences between Windows NT COPY and Oracle OCOPY When Doing
> Backups:

...

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

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

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

Re: performance problem with partitioned table query.


Strange, I'd expect, that dropping 12 partitions should speed up the query.

Still partitioning helps only if column, used for partitioning, is specified
as one your search criteria, or if you do full table scan in parallel, or in
maintenance when you can quickly drop a partition instead of deleting rows.
Otherwise, it can only slow down your retrievals.
Why did you partition your table at all?
And, why did you partition by this particular column "poid_id0"?

Igor Neyman, OCP DBA
[EMAIL PROTECTED]


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, January 24, 2002 3:20 PM


> Thank you Igor. But only 1 of the 14 partitions contains data during all
the tests. Why should the extra 13 empty partitions slows down the query? I
also tried to drop 12 of the empty partitions. Results didn't
change. -Jessica
>
> -Original Message-
> Sent: Thursday, January 24, 2002 5:37 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Jessica,
>
> It looks like your query has to deal with all 14 partitions, because the
> column 'poid_id0', which your table partitioned on, is not in 'where'
> clause.
> That's why Oracle can not eliminate other (not populated) 13 partitions.
>
> Igor Neyman, OCP DBA
> [EMAIL PROTECTED]
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, January 23, 2002 6:15 PM
>
>
> > Oracle 8.1.7.0.0
> >
> > table event_t range partitioned by column poid_id0. only 1 partition
> called p_1 out of the 14 contains data. A query on event_t became
> significantly slow after rows increase:
> >
> > select   poid_DB, poid_ID0, poid_TYPE, poid_REV, start_t, end_t,
sys_descr
> > from  event_t
> > where event_t.end_t >= :1 and event_t.end_t < :2 and
> >   event_t.poid_TYPE like :3 and (event_t.account_obj_ID0 = :4 and
> >   event_t.account_obj_DB = 1 ) order by event_t.end_t desc
> >
> > Rows Execution Plan
> > ---  ---
> >   0  SELECT STATEMENT   GOAL: CHOOSE
> >   0   SORT (ORDER BY)
> >   0PARTITION RANGE (ALL) PARTITION: START=1 STOP=14
> >   0 TABLE ACCESS   GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF
> > 'EVENT_T' PARTITION: START=1 STOP=14
> >   0  INDEX   GOAL: ANALYZED (RANGE SCAN) OF
> >'I_EVENT__ACCTOBJ_END_T' (NON-UNIQUE) PARTITION: START=1
> >  STOP=14
> >
> > Index I_EVENT__ACCTOBJ_END_T was created on event_t ( account_obj_id0,
> end_t ) using LOCAL.
> > Other 2 columns involved in the where clause have either only one
distinct
> value or a few. So are not indexed.
> > column account_obj_id0 has 1 million unique values in event_t and remain
> unchanged during the tests. when rows insert, average rows per
> account_obj_id0 value increase as well.
> >
> > Trace shows always the same execution plan but elapsed time increased
> enormously!
> > I did 2 rounds of tests, every round I dropped and recreated event_t
> empty:
> >
> > In test round 1:
> > 1.) inserted 1 million rows into event_t with same end_t value. Query
> returned:
> > call count   cpuelapsed   disk  querycurrent
> rows
>
 --- --   -- -- -- --  
> --
> > Parse   23  0.02   0.09  0  0  0
> 0
> > Execute156  0.02   0.29  0  0  0
> 0
> > Fetch  156  0.14   1.09  8   2698  0
> 195
>
 --- --   -- -- -- --  
> --
> > total  335  0.18   1.47  8   2698  0
> 195
> >
> > 2.) inserted ANOTHER 1.5 million rows into event_t with 10,000+
different
> end_t values. Query returned:
> > Parse   36  0.00   0.04  0  0  0
> 0
> > Execute118  0.01   0.01  0  0  0
> 0
> > Fetch  118  0.61  86.71   1385   5045  0
> 587
>
 --- --   -- -- -- --  
> --
> > total  272  0.62  86.76   1385   5045  0
> 587
> >
> > In test round 2:
> > 1.) inserted 1 million rows into event_t with same end_t value. Query
> returned as round1 step 1.)
> >
> > 2.) inserted ANOTHER 5 million rows into event_t with ANOTHER end_t
value.
> Query returned:
> > Parse   40  0.00   0.11  0  0  0
> 0
> > Execute139  0.02   0.12  0  0  0
> 0
> > Fetch  139  0.25   4.66303   2868  0
> 761
>
 --- --   -- -- -- --  
> --
> > total  318  0.27   4.89303   2868  0
> 761
> >
> > 3.) inserted ANOTHER 2 million rows into event_t with 12,000+ different
> end_t values. Query returned:
> > Parse   34  0.01   0

Re: ORA-01555 Mystery (Help)

I was going to write this myself, but this explanation fron 
MetaLink Note 45895.1 means I can just cut and paste, and
Rachel won't get after me for typos.  :)

Jared

  Delayed block cleanout on old committed updates.  An update operation 
  completes and commits; the updated blocks are not touched again until a 
  long-running query begins.  Delayed Block Cleanout (DBC) has never been 
  done on the blocks.  This can result in a scenario which happens only 
  under specific circumstances in VLDB, causing ORA-01555 errors when NO 
  updates or inserts are being committed on the same blocks a query is 
  retrieving. 

  All of the following must be true for an ORA-01555 to occur in this 
case: 

  (i) An update completes and commits and the blocks are not 
  touched again until... 

  (ii) A long query begins against the previously updated blocks. 

  (iii) During the query, a considerable amount of DML takes place, 
  though not on the previously updated blocks which the query is 
  currently fetching. 

  (iv) Under condition (iii) there is so much DML relative to 
available 
  rollback space that the rollback segment used in the first update 
  wraps around, probably several times. 

  (v) Under condition (iv), the commit SCN of the first update is 
  cycled out of the rollback segment. 

  (vi) Under condition (iv) the lowest SCN in the rollback segment is 
  pushed higher than the read consistent SCN in the query. 

  (Note:  The read consistent SCN is what the query uses to construct 
   a read consistent view.  Any block which has an SCN higher than 
this
   was obviously updated after the query started and requires 
rollback). 

  The above conditions imply that when a query reaches a block that has 
been 
  updated but not cleaned out, the query quickly learns that the update 
  committed, and accordingly cleans out the block.  But because the update 

  SCN is no longer in the rollback segment (condition (v)), the query 
doesn't
  know WHEN the update committed.  This is important because if the commit 

  happened before the query began, the current value in the block can be 
used 
  by the query; but if the commit happened after, the old value must be 
fetched
  from the rollback segment. Now, because the rollback segment wrapped in 
(iv),
  we know that the update SCN can't be higher than the lowest SCN in the 
  rollback segment, which gives us a nice upper bound.  If we only knew 
that 
  the read consistent SCN was higher than this upper bound, we would know 
that
  the update committed before the query started.  But we don't know this 
  because of condition (vi), so we can't even accurately "estimate" the 
update
  SCN.  Hence, we get an ORA-01555. 

Stephane Faroult <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/25/02 10:39 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Re: ORA-01555 Mystery (Help)

I was almost ready to subscribe to the idea of delayed cleanout, but I
cannot understand why really. The necessity for reading a block from the
rollback segments comes from encountering during the course of the
SELECT a block the SCN of which is higher than the SCN when the query
started. I have of course no certainty about it, but it would be logical
to expect the block's SCN to be properly set irrespectively of the
clean-out being immediate or delayed. In other words, even if a SELECT
physically writes blocks, it should not have anything to do with
rollback segments anyway.
I share Mladen's opinion, somebody must be economical with the truth
somewhere, and you should check V$ACCESS, V$SESSION and V$LOCK. Are you
really sure that the code contains no 'just in case' commit ou rollback
which would release the lock? And by the way, 5 hours look to me like an
awfully long time, even for a 20 million row mega-select of death.

[EMAIL PROTECTED] wrote:
> 
> Precisely the point I was trying to make, when I put the question if it 
was
> a normal select, or if it was within a PL/SQL block?  The myth is that
> snapshot too old happens only when some other transaction was in the
> process of performing an DML on a table, when you did a select on it. It
> can happen for other reasons too. Search on Metalink for "Delayed block
> cleanouts" and "fetch across commits".
> 
> Raj
> 
> "Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(

RE: How to use a package variable in pkg1 inside of package pkg2

2002-01-25 Thread Steve McClure


I use this techinique when I build PL/SQL applications that span packages.
I almost always create a global package with nothing but the specification
filled with variables.  Usually these variables are established upon entry
to the app, and are applicable for the length of the run.  As for
guaranteeing the value to be what I expect, the fact that the package
variables are session specific takes care of that nicely.

In regards to the original post,  The specification of a package is public,
the body is private to the package itself.  If you want something to be
available outside of a package, it needs to be declared in the
specification.

Steve

-Original Message-
Thomas F
Sent: Friday, January 25, 2002 9:01 AM
To: Multiple recipients of list ORACLE-L


I'm not sure why you want to do this.  Why not have the package that you
call return the value back to the calling package.  I would not guarantee
that the value you expect to be stored in the variable would exist when you
think it will be there.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, January 25, 2002 10:31 AM
To: Multiple recipients of list ORACLE-L
pkg2



You have to declare this variable in package1 specification

create or replace package pkg1 is
end;
/

create or replace package pkg2 is
  procedure showvar;
end;
/

create or replace package pkg2 body is
  procedure showvar
  begin
dbms_output.put_line(pkg1.v_var);
  end;
end;
/

exec pkg2.showvar
in SQLPlus prompt should do that

Gints Plivna
IT Sistçmas, Meríeïa 13, LV1050 Rîga
http://www.itsystems.lv/gints/





Rick_Cale@team

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

[EMAIL PROTECTED]   Subject: How to use a package
variable in pkg1 inside of package pkg2
om





2002.01.25

16:35

Please respond

to ORACLE-L









Hi All,

I have a package pkg1 that has a variable var 1 declared

Inside of pkg2 I want to use pkg1.var1. I always get PLS201 identifier
pkg1.var1 must be declared.
What do I need to do to correct.

Pkg1 compiles fine.

Thanks
Rick


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

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

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




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

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

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

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

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

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

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

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

RE: ORA-01555 Mystery (Help)


Another fact, that should be mentioned, is that the
table in question was built (loaded) two days ago. The
nightly ETL processes for the warehouse are pretty
substantial and the likelyhood of a block not getting
cleaned/flushed out for a couple days should be nil.

To summarize:

1. Tuesday Night: 
-truncate/load table 'A' (24 million rows)
-Perform massive select from 'A', fails 5 hours later
with 1555. NO DML BEING PERFORMED AGAINST 'A' BY ANY
OTHER SESSION
2. Wednesday Night: 
-Perform massive select against 'A', fails 5 hours
later with ORA-1555. NO DML BEING PERFORMED AGAINST
'A' BY ANY OTHER SESSION
3. Thursday night: 
-'lock table A in exclusive mode;' via session 123
-perform massive select against 'A', fails 5 hours
later with ORA-1555 via session 124. NO DML BEING
PERFORMED AGAINST 'A' BY ANY OTHER SESSION
-session 123 still has exclusive lock on table 'A' the
following morning
4. Friday morning:
-Walter is stumped but still trying to figure out a
solution! :)

-w


--- [EMAIL PROTECTED] wrote:
> Delayed block cleanouts can still cause the
> ORA-1555, even
> after locking the table in exlusive mode.
> 
> That's the purpose of the analyze, to force the
> block cleanouts.
> 
> Jared
> 
> 
> 
> 
> 
> 
> Paul Baumgartel <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 01/25/02 09:30 AM
> Please respond to ORACLE-L
> 
>  
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc: 
> Subject:RE: ORA-01555 Mystery (Help)
> 
> 
> Sure, but the original post concerns a *query*, not
> a transaction, and
> before running the query, the user locked the
> queried table in
> exclusive mode, to ensure that no other session
> could write to the
> queried table.   How do we account for the query's
> need to read from
> rollback? 
> 
> 
> --- "Baker, Barbara"
> <[EMAIL PROTECTED]> wrote:
> > 
> > I have a batch job that does this consistently. 
> It's the only job in
> > the
> > database; it sets the transaction to a hugh
> rollback segment.  And it
> > eats
> > its own tail.
> > 
> > Depending on how the job is written, it may need a
> read consistent
> > view
> > itself (as opposed to some other query in the
> database needing that
> > read
> > consistent view.)In that case, it may well go
> try to read its own
> > rollback segment, only to find that it's been
> overwritten.  (Oddly
> > enough,
> > even when there's plenty of space to extend the
> rollback, Oracle will
> > decide
> > to overwrite the original rollback segments rather
> than extend if it
> > thinks
> > it doesn't need those segments any more.)
> > 
> > I'd strongly suggest you get the stuff from Steve
> Adams' ixora site
> > that
> > places an uncommitted transaction in your rollback
> segments for the
> > length
> > of the run.This will guarantee that the
> rollback segments don't
> > get
> > overwritten.
> > Good luck!
> > 
> > Barb
> > 
> > > --
> > > From:Walter
> K[SMTP:[EMAIL PROTECTED]]
> > > Reply To:[EMAIL PROTECTED]
> > > Sent:Friday, January 25, 2002
> 9:15 AM
> > > To:  Multiple recipients of list
> ORACLE-L
> > > Subject: ORA-01555 Mystery (Help)
> > > 
> > > Hi,
> > > 
> > > A user in our data warehousing group is running
> into
> > > the old ORA-01555 (snapshot too old) error every
> time
> > > she runs a massive (20 million rows) select
> against
> > > one table via a view. I confirmed that the view
> only
> > > translates to the one table.
> > > 
> > > The user swears that no one would be making any
> > > updates/deletes to the table she is selecting
> from. I
> > > suggested she lock the table in exclusive mode,
> prior
> > > to running her massive select to guarantee no
> one else
> > > could change the data in the table and cause the
> > > triggering of the 1555 error. Locking the table
> was a
> > > viable option because it's a staging table in
> the
> > > warehouse itself. She locked the table in
> exclusive
> > > mode last night and it locked; fired off her
> query,
> > > and it failed 5 hours later with the 1555 error
> again.
> > > 
> > > I'm stumped on this. I just don't see how this
> is
> > > possible. Any suggestions?
> > > 
> > > Thanks!!!
> > > -w
> > > 
> > >
> __
> .
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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

RE: RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Jeremiah Wilton

On Fri, 25 Jan 2002, Kathy Duret wrote:

> How about doing a set transaction to a large rollback before running
> this query if the analyze doesn't resolve the problem.

That will have no effect.

http://www.speakeasy.org/~jwilton/oracle/snapshot-too-old.html

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

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

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

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

RE: Backup Strategy

2002-01-25 Thread Johnston, Tim


Igor,

I think your missing the point...  You state...

"But, I'll take my chances, because as I said before, MetaLink Note:139327.1
didn't convince me at all that "OCOPY" is any better than regular "NT Copy"
command."

But, I assume that the opposite is also true?  "NT Copy" isn't any better
then "OCOPY"?  My assumption is that they are functionality equivalent and
perform about the same?  But, the big difference is that "OCOPY" is the
supported way to perform backups on NT while "NT Copy" is not...  So why
"take a chance" when it doesn't gain you any benefit?  If "NT Copy" is
significantly better then "OCOPY" for some reason then let me know...  Then
maybe you have a risk/reward argument that I can understand...  If not, why
gamble for zero gain?

Tim

-Original Message-
Sent: Friday, January 25, 2002 2:07 PM
To: Multiple recipients of list ORACLE-L


Well, I know what you mean.
But, I'll take my chances, because as I said before, MetaLink Note:139327.1
didn't convince me at all that "OCOPY" is any better than regular "NT Copy"
command.

Besides, I am keeping two generations of backup (the latest and the one
prior to that), so if anything goes wrong with the latest backup image of
the db file, I can always recover, using older backup and archived RedoLogs
from both backups.

Also, our customers wouldn't wait for Oracle support , and having two
generations of backups, I can resolve potential issues much faster than ...

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, January 25, 2002 12:30 PM


> Igor,
>
> That sounds good, but what is Oracle Support going to say when you call
them
> for support on a database recovery and you mention that you used NT COPY??
> Your hosed, if there attitude is you should have used OCOPY so we can't
help
> you.
>
> Chris
>
> -Original Message-
> Sent: Friday, January 25, 2002 11:26 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Ok, I guess, I owe some explanation here, since I've got a lot of spanking
> (replies, some rather sarcastic) regarding this issue.
> My original note comes from my "real life" experience, so I'm still
standing
> behind it.
> Sorry, it'll be kind of long, but if you are really interested...
>
> Couple years ago, when we were preparing first release of our product, I
> read of course about "NT Copy" versus "Oracle Ocopy".
> Still I decided to test it, because not always I trust what I read, and I
> like to get proof myself.
> Testing of online ("hot") backup/recovery scenario showed, that using "NT
> Copy" command in backup scripts is perfectly fine, when creating backup
set
> of files on the disk.  And there is no problem restoring from this backup.
> Now this "disk backup" set of files could be saved on tape, using NTBACKUP
> (that's the one, that really can not copy file, if it's opened by some
other
> program.  But that's not the case with prepared in advance "disk backup").
> "NT Copy" has no problems copying files opened already by Oracle, and
backup
> is consistent, as long of course as I am using "alter tablespace 
> begin backup" before copying relevant files and "alter tablespace 
end
> backup" after finishing files copy.
> So, those scripts (using "NT Copy") were put into production, and now have
> been used for more than two years on more than hundred installations/sites
> (the number keeps growing).
> From time to time, our field engineers are bringing back to me sets of
> online (can not use "cold" backup - our systems should run 24*7, I'm not
> saying they are, but we are trying to minimize downtime) backed up files
(db
> files and archived RedoLog files), and I recover them with no problem (we
> need this, to test how the upgrade to next release of our product will run
> against "real" customers data).
>
> Now, about MetaLink Note:139327.1
> It says:
> 
> Ocopy  opens the file using CreateFile() with the FILE_SHARE_READ and
> FILE_SHARE_WRITE  flags. This allows writing to continue while we take the
> backup.  Inconsistencies in the backup are repaired by applying archived
> redo  during  recovery. The 'copy' command from NT doesn't use these flags
> since it  wants to  prevent writes to the file while the copy is taking
> place.
> 
>
> I don't think, it's very accurate, and here is why:
> When during online backup I run "NT copy" against db file, the file is
> already opened by Oracle (at moment, when I "open" the database).
> So, even if "NT copy" opens file without FILE_SHARE_READ and
> FILE_SHARE_WRITE  flags, all it means is that "Subsequent open operations
on
> the object will fail" (quote from NT docs).  I want you to notice, it says
> "Subsequent open operations" not "Subsequent write/read operations".  So,
> all it does is prohibiting some other program/process from "opening" the
> file.  But Oracle, as I mentioned, has this file already opened, and it is
> perfectly capable of reading/writing this file.
> O

Re: Backup Strategy (NT)

(fwding in case TBC's "cc:" doesn't make it to the list.)

--- Forwarded message follows ---
Date sent:  Fri, 25 Jan 2002 11:12:11 -0800 (PST)
025
To: [EMAIL PROTECTED]
Copies to:  [EMAIL PROTECTED]

I gave the right advice for the wrong reasons.  My bad. I'll 
revise the next release of the paper and credit you -- thanks.  

Here's the word from Oracle Support:

Doc ID:  Note:139327.1 

The Differences between Windows NT COPY and Oracle OCOPY When 
Doing Backups:  

==
==

When doing an online backup, should you use the Windows NT 
COPY
command, or the Oracle OCOPY command?

While doing online backups you should use OCOPY, or Oracle7
 EBU, or Oracle8 (and later) RMAN.  With the OCOPY command you
 could copy to a backup directory on the hard drive but cannot
 use OCOPY to copy a file to tape. The other option if you do
 not want to use ocopy to perform your backup as this does
 require a lot of disk space is EBU/RMAN that comes with
 Oracle. Depending on your Oracle version, the distribution
 includes a utility called EBU (Oracle7) or RMAN (Oracle8 and
 later) that can be used for online recovery as well. You will
 need to use a media management product to move the data from
 RMAN to tape. Legato Storage Manager is provided however
 there are other products that are supported to be used with
 this tool.  

To backup you will need to use the utility delivered by
 Oracle, the ocopy command. Utilities like the NT commands
 copy, xcopy CANNOT be used to back up. The Windows NT feature
 to be aware of is that NT Backup does not allow files in use
 to be copied, so you must use the OCOPY utility that Oracle
 provides to copy the open database files to another disk
 location. Since OCOPY cannot copy files directly to tape, you
 will then need to use NT Backup or copy or a similar utility
 to copy the files to tape, as required.  

OCOPY allows writing to continue while the backup is running.
 The NT COPY is a closed copy and the files may be marked
 either as "fuzzy" or "corrupt." Ocopy opens the file using
 CreateFile() with the FILE_SHARE_READ and FILE_SHARE_WRITE
 flags. This allows writing to continue while we take the
 backup.  Inconsistencies in the backup are repaired by
 applying archived redo during recovery. The 'copy' command
 from NT doesn't use these flags since it wants to prevent
 writes to the file while the copy is taking place.  

REFERENCES
  [NOTE:41946.1] NT Online Backups 
  Oracle Backup and Recovery Guide

--- "Eric D. Pierce" <[EMAIL PROTECTED]> wrote:
> fyi: 
> 
> On 25 Jan 2002 at 1:05, Oracle RDBMS Community Forum 
> <[EMAIL PROTECTED]> wrote:
> 
> 
> > 
> > --
> > 
> >  From: "Igor Neyman" <[EMAIL PROTECTED]>
> >  Date: Thu, 24 Jan 2002 16:14:25 -0500
> >  Subject: Re: Backup Strategy
> > 
> > I took a quick look at this paper, and found right away, that it's
> not
> > very accurate, at least in one issue. i.e., it states : < quote>
> The
> > Windows NT command COPY can be used to create a cold backup of a
> database.
> > It cannot be used to make a hot backup. Attempting to perform a hot
> backup
> > with COPY will usually result in an error message being generated
> as the
> > COPY command fails - during a hot backup the database is running
> and thus
> > the database files are locked by the Oracle database process, and
> COPY
> > cannot work on a file that is so locked. < /quote>
> > 
> > Wrong. NT 'COPY' has no problems copying 'opened' oracle db files.
> > I'm using it in 'hot backup' scripts on many dozens systems, and it
> works
> > fine.
> > 
> > Don't know about the accuracy of the rest of the paper, didn't have
> time
> > to read it all.
> > 
> > Igor Neyman, OCP DBA
> > [EMAIL PROTECTED]
> > 
> > 
> > - Original Message -
> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > Sent: Thursday, January 24, 2002 7:35 AM
> > 
> > 
> > > Hi All,
> > >
> > >  http://www.geocities.com/tbcox23/
> > >
> > > Go here and get the paper.
> > >
> > > Regards
> > > Venkat
> > > --
> > 
> 
> 

=
Thomas B. Cox "Saepe in errore sed numquam in dubito"
[EMAIL PROTECTED]   http://www.geocities.com/tbcox23/

"The whole aim of practical politics is to keep the 
populace alarmed (and hence clamorous to be led to 
safety) by menacing it with an endless series of 
hobgoblins, all of them imaginary." --H.L. Mencken

--- End of forwarded message ---
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eric D. Pierce
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BO

RE: Mirroring REDO logs to an nfs drive


If the SA's would soft-mount the drives instead of hard-mounting 
them, they wouldn't have to reboot.

Jared





"Browett, Darren" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/25/02 10:31 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: Mirroring REDO logs to an nfs drive


I have experienced problems in the past when using NFS drives to dump
large amounts of data.  No sure if having the redo logs there will cause 
the
same problem.

I am running a tru64 4.0f environment, and what I was doing ( needed the
disk
space at the time) was export my production databases to a NFS mounted
drive. 

>From what I understand, due to the amount of data being dumped to the NFS
drive, 
I basically over-saturated the  connection, which caused the "automount"
daemon on 
my other systems to drop the NFS drive periodically and without warning. 

To fix the problem was to simply stop the export processes going to the 
NFS
drives (which were running
every 2nd night) and reboot the NFS master.

Darren

-Original Message-
Sent: January 24, 2002 11:49 AM
To: Multiple recipients of list ORACLE-L


Our site is preparing to fail over to our backup server.  We need to do
maintenance on our production server, and will be running on the backup 
for
about 24 hours.  One issue I brought up was that our backup server is not
equiped with mirrored drives, thus there was the possibility that a drive
failure could destroy an online redo log.  On our production box the logs
are not software mirrored, because of the physical mirroring in our drive
cabinet.

As a result I was told to multiplex the redo logs once we had failed over 
to
the backup server.  Furthermore I would add the new members to an nfs 
drive,
so that even a pesky controller couldn't foil our mirrored log files.

I have some questions about this.  First, am I just looking for problems 
by
doing this?  I would appreciate any tips or warnings on this subject.
Secondly, researching this topic made me curious as to my DB's settings 
for
MAXLOGFILES and MAXLOGMEMBERS.  Where can I find these parameters?  I was
sure I would find them in v$parameter, but they were not there.

Thanks for any response,
Steve McClure

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

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

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

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

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



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

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

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

RE: RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Kathy Duret


How about doing a set transaction to a large rollback before running this query if the 
analyze doesn't resolve the problem.  

Kathy

-Original Message-
Sent: Friday, January 25, 2002 10:40 AM
To: Multiple recipients of list ORACLE-L



> Dick:
> This makes the assumption that Walter can get to the code, find out what
> it's doing, and make modifications.  (In our case, we can't.  The code is
> vendor-supplied, unchangeable, and is written in Cobol).
> 
> It also sounds like this might be happening in the middle of the night.
> I'd guess there's a limit to how much information Walter can gather about
> what happened 5 hours into the job at 3:00 am
> 
> If he's desperate to get the data loaded and he can't change the sql, then
> his options are limited.
> 
> I believe all the possible causes for 1555 errors have been listed in this
> thread.  Hopefully he can identify which is causing the grief and find a
> resolution.
> 
> Barb
> 
> 
>   --
>   From:   [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]]
>   Sent:   Friday, January 25, 2002 11:09 AM
>   To: Baker; Barbara; Multiple recipients of list ORACLE-L
>   Subject:Re:RE: ORA-01555 Mystery (Help)
> 
>   Barb,
> 
>   I've tried Steve's idea in the past and although it sorta fixed
> the problem
>   with the large batch job, it created problems elsewhere.  It also
> did not
>   totally fix the problem when other applications updated parts of the
> table(s)
>   and committed their transaction.  Many folks believe that by
> allocating a large
>   rollback segment to their session they have fixed the problem.
> Wrong, this
>   particular issue can be caused by your own application plus anyone
> else who is
>   using the database and other rollback segments.
> 
>   The real issue here is to either find out who or what is
> updating the
>   underlying table or else speeding up the process.  There were two
> points that I
>   found easy to implement that fixed 90% of our errors.
> 
>   1) Don't commit across a cursor.  In this scenario look for
> cases where your
>   pulling data from a table, updating that table, and then continuing
> to read data
>   from the cursor.  This one will pop a 1555 very regularly since the
> cursor
>   depends on a read consistent view, but you just released the
> rollback segments.
> 
>   2) Use an order or group by in the select statement.  This one
> sounds odd,
>   but it does work.  By placing either an order by or group by clause
> in the
>   select statement you force Oracle to read all of the data at one
> time, place it
>   in a temp segment, and then hand it over.  The end result is that
> when the first
>   row of data appears in your application you no longer need any
> rollback to
>   create a read consistent view.  If your just pulling from the table,
> then Oracle
>   hands over a row as it satisfies the query criteria.  OH, did you
> just update
>   and commit a change?  Well that is NOT going to be included in your
> result set
>   since it is already locked in concrete.
> 
>   Try one of these & see if it fixes your problem.
> 
>   Dick Goulet
> 
>   Reply Separator
>   Subject:RE: ORA-01555 Mystery (Help)
>   Author: "Baker; Barbara" <[EMAIL PROTECTED]>
>   Date:   1/25/2002 8:52 AM
> 
> 
>   I have a batch job that does this consistently.  It's the only job
> in the
>   database; it sets the transaction to a hugh rollback segment.  And
> it eats
>   its own tail.
> 
>   Depending on how the job is written, it may need a read consistent
> view
>   itself (as opposed to some other query in the database needing that
> read
>   consistent view.)In that case, it may well go try to read its
> own
>   rollback segment, only to find that it's been overwritten.  (Oddly
> enough,
>   even when there's plenty of space to extend the rollback, Oracle
> will decide
>   to overwrite the original rollback segments rather than extend if it
> thinks
>   it doesn't need those segments any more.)
> 
>   I'd strongly suggest you get the stuff from Steve Adams' ixora site
> that
>   places an uncommitted transaction in your rollback segments for the
> length
>   of the run.This will guarantee that the rollback segments don't
> get
>   overwritten.
>   Good luck!
> 
>   Barb
> 
>   > --
>   > From: Walter K[SMTP:[EMAIL PROTECTED]]
>   > Reply To: [EMAIL PROTECTED]
>   > Sent: Friday, January 25, 2002 9:15 AM
>   > To:   Multiple recipients of list ORACLE-L
>   > Subject:  ORA-01555 Mystery (Help)
>   > 
>   > Hi,
>   > 
>   > A user in our data warehousing group is running into
>   > the old ORA-01555 (snapshot too old) error every time
>   > she runs a massive (20

Re: Backup Strategy


Well, I know what you mean.
But, I'll take my chances, because as I said before, MetaLink Note:139327.1
didn't convince me at all that "OCOPY" is any better than regular "NT Copy"
command.

Besides, I am keeping two generations of backup (the latest and the one
prior to that), so if anything goes wrong with the latest backup image of
the db file, I can always recover, using older backup and archived RedoLogs
from both backups.

Also, our customers wouldn't wait for Oracle support , and having two
generations of backups, I can resolve potential issues much faster than ...

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, January 25, 2002 12:30 PM


> Igor,
>
> That sounds good, but what is Oracle Support going to say when you call
them
> for support on a database recovery and you mention that you used NT COPY??
> Your hosed, if there attitude is you should have used OCOPY so we can't
help
> you.
>
> Chris
>
> -Original Message-
> Sent: Friday, January 25, 2002 11:26 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Ok, I guess, I owe some explanation here, since I've got a lot of spanking
> (replies, some rather sarcastic) regarding this issue.
> My original note comes from my "real life" experience, so I'm still
standing
> behind it.
> Sorry, it'll be kind of long, but if you are really interested...
>
> Couple years ago, when we were preparing first release of our product, I
> read of course about "NT Copy" versus "Oracle Ocopy".
> Still I decided to test it, because not always I trust what I read, and I
> like to get proof myself.
> Testing of online ("hot") backup/recovery scenario showed, that using "NT
> Copy" command in backup scripts is perfectly fine, when creating backup
set
> of files on the disk.  And there is no problem restoring from this backup.
> Now this "disk backup" set of files could be saved on tape, using NTBACKUP
> (that's the one, that really can not copy file, if it's opened by some
other
> program.  But that's not the case with prepared in advance "disk backup").
> "NT Copy" has no problems copying files opened already by Oracle, and
backup
> is consistent, as long of course as I am using "alter tablespace 
> begin backup" before copying relevant files and "alter tablespace 
end
> backup" after finishing files copy.
> So, those scripts (using "NT Copy") were put into production, and now have
> been used for more than two years on more than hundred installations/sites
> (the number keeps growing).
> From time to time, our field engineers are bringing back to me sets of
> online (can not use "cold" backup - our systems should run 24*7, I'm not
> saying they are, but we are trying to minimize downtime) backed up files
(db
> files and archived RedoLog files), and I recover them with no problem (we
> need this, to test how the upgrade to next release of our product will run
> against "real" customers data).
>
> Now, about MetaLink Note:139327.1
> It says:
> 
> Ocopy  opens the file using CreateFile() with the FILE_SHARE_READ and
> FILE_SHARE_WRITE  flags. This allows writing to continue while we take the
> backup.  Inconsistencies in the backup are repaired by applying archived
> redo  during  recovery. The 'copy' command from NT doesn't use these flags
> since it  wants to  prevent writes to the file while the copy is taking
> place.
> 
>
> I don't think, it's very accurate, and here is why:
> When during online backup I run "NT copy" against db file, the file is
> already opened by Oracle (at moment, when I "open" the database).
> So, even if "NT copy" opens file without FILE_SHARE_READ and
> FILE_SHARE_WRITE  flags, all it means is that "Subsequent open operations
on
> the object will fail" (quote from NT docs).  I want you to notice, it says
> "Subsequent open operations" not "Subsequent write/read operations".  So,
> all it does is prohibiting some other program/process from "opening" the
> file.  But Oracle, as I mentioned, has this file already opened, and it is
> perfectly capable of reading/writing this file.
> Of course, the image of the saved file will be "fuzzy", and that's why
when
> recovering from online backup we are applying archived RedoLog files
(which
> getting written much more intensely during online backup).
>
> As for Peter McLarty note, that  he "never knew that NT copy could manage
> keeping the CSN number in sync",
> well it ("NT Copy") does not have to (neither does "Oracle Ocopy") keep
CSN
> number in sync.
> Oracle updates file header with checkpoint SCN, when we issue "alter
> tablespace  begin backup".  Then until "alter tablespace  end
> backup", file header will cease updating.  And SCN, written in the
beginning
> provides the info, which archived RedoLog files should be used for
recovery.
>
> Now, please correct me, if I'm wrong.
>
> Igor Neyman, OCP DBA
> [EMAIL PROTECTED]
>
>
> - Original Message -
> To: <[EMAIL PROTECTED]>
> Cc: <[EM

RE: ORA-01555 Mystery (Help)


Jared, would you elaborate more on this? Does this
need to be a 'compute' or can it be an 'estimate' on
the analyze?

I read the info on Steve's site as suggested by Barb
and it sounds like block cleanout may be the issue but
I'm still trying to digest the concept/issue as it
relates to my circumstance.

For the others that have contributed to the thread,
yes, the table is definitely locked in exclusive mode
(via a different session) before the SELECT is
performed and the lock is not released until the
following day. I too was suspicious that the lock was
accidentally being released.

-w




--- [EMAIL PROTECTED] wrote:
> Delayed block cleanouts can still cause the
> ORA-1555, even
> after locking the table in exlusive mode.
> 
> That's the purpose of the analyze, to force the
> block cleanouts.
> 
> Jared
> 
> 
> 
> 
> 
> 
> Paul Baumgartel <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 01/25/02 09:30 AM
> Please respond to ORACLE-L
> 
>  
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc: 
> Subject:RE: ORA-01555 Mystery (Help)
> 
> 
> Sure, but the original post concerns a *query*, not
> a transaction, and
> before running the query, the user locked the
> queried table in
> exclusive mode, to ensure that no other session
> could write to the
> queried table.   How do we account for the query's
> need to read from
> rollback? 
> 
> 
> --- "Baker, Barbara"
> <[EMAIL PROTECTED]> wrote:
> > 
> > I have a batch job that does this consistently. 
> It's the only job in
> > the
> > database; it sets the transaction to a hugh
> rollback segment.  And it
> > eats
> > its own tail.
> > 
> > Depending on how the job is written, it may need a
> read consistent
> > view
> > itself (as opposed to some other query in the
> database needing that
> > read
> > consistent view.)In that case, it may well go
> try to read its own
> > rollback segment, only to find that it's been
> overwritten.  (Oddly
> > enough,
> > even when there's plenty of space to extend the
> rollback, Oracle will
> > decide
> > to overwrite the original rollback segments rather
> than extend if it
> > thinks
> > it doesn't need those segments any more.)
> > 
> > I'd strongly suggest you get the stuff from Steve
> Adams' ixora site
> > that
> > places an uncommitted transaction in your rollback
> segments for the
> > length
> > of the run.This will guarantee that the
> rollback segments don't
> > get
> > overwritten.
> > Good luck!
> > 
> > Barb
> > 
> > > --
> > > From:Walter
> K[SMTP:[EMAIL PROTECTED]]
> > > Reply To:[EMAIL PROTECTED]
> > > Sent:Friday, January 25, 2002
> 9:15 AM
> > > To:  Multiple recipients of list
> ORACLE-L
> > > Subject: ORA-01555 Mystery (Help)
> > > 
> > > Hi,
> > > 
> > > A user in our data warehousing group is running
> into
> > > the old ORA-01555 (snapshot too old) error every
> time
> > > she runs a massive (20 million rows) select
> against
> > > one table via a view. I confirmed that the view
> only
> > > translates to the one table.
> > > 
> > > The user swears that no one would be making any
> > > updates/deletes to the table she is selecting
> from. I
> > > suggested she lock the table in exclusive mode,
> prior
> > > to running her massive select to guarantee no
> one else
> > > could change the data in the table and cause the
> > > triggering of the 1555 error. Locking the table
> was a
> > > viable option because it's a staging table in
> the
> > > warehouse itself. She locked the table in
> exclusive
> > > mode last night and it locked; fired off her
> query,
> > > and it failed 5 hours later with the 1555 error
> again.
> > > 
> > > I'm stumped on this. I just don't see how this
> is
> > > possible. Any suggestions?
> > > 
> > > Thanks!!!
> > > -w
> > > 
> > >
> __
> .
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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


__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walter K
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet a

OraJava function vs. procedure

2002-01-25 Thread Jesse, Rich


So, there I am.  8.1.7.2 with JVM loaded in Oracle.  I need to be able to
access the Unix shell from within a procedure, so naturally, I plagiarize
and modify a very simple Java class from somewhere in Metalink:

--- Java code start

import java.lang.Runtime;
import java.lang.Process;
import java.io.IOException;
import java.lang.InterruptedException;

class QT_Exec_OS {

public static int main(String args[]) {

int retval = 0;

try {
String  ftpCommand;
ftpCommand = "/usr/bin/ls " + args[0];

Process p = Runtime.getRuntime().exec(ftpCommand);

try {
p.waitFor();
} catch (InterruptedException intexc) {
retval = 700;
}

retval = p.exitValue();

} catch (IOException e) {
e.printStackTrace();
retval = 701;
}
return retval;
   }
}

--- Java code end

And then, the PL/SQL wrapper:

--- PL/SQL code start

CREATE OR REPLACE PROCEDURE qt_rjtest (S1 IN VARCHAR2)
AS LANGUAGE JAVA
name 'QT_Exec_OS.main(java.lang.String[])';
/

--- PL/SQL code end

This works fine, but I'm not sure why.  According to Metalink, I should be
getting a PLS-311 error because the Java code is returning a value.  H.
But when I try to create a PL/SQL function to make use of the Java code's
return value:

--- PL/SQL code start

CREATE OR REPLACE FUNCTION qt_rjtest_f (S1 IN VARCHAR2)
RETURN NUMBER
AS LANGUAGE JAVA
name 'QT_Exec_OS.main(java.lang.String[]) return int';
/

--- PL/SQL code end

...I get the PLS-311 "the declaration of
"QT_Exec_OS.main(java.lang.String[]) return int" is incomplete or malformed"
error.

So, I'm guessing that the Java doesn't actually return a value, but I can't
figure out why.

Anyone?

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

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

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

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

Standby database question

2002-01-25 Thread Rick_Cale



Hi DBAs,

One of the co-workers has a hot standby database.  Logs are applied at some
interval.  He has to add
a tablespace.  What is necessay to make standby database aware of this?

Thanks
Rick


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

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

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

Ang: Re: Number_of_rows

2002-01-25 Thread Roland . Skoldblom



Yes But I want thatthat number is inserted into the table.







[EMAIL PROTECTED]@fatcity.com den 2002-01-25 10:39 PST

Sänd svar till [EMAIL PROTECTED]

Sänt av:  [EMAIL PROTECTED]


Till: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Kopia:


Use SQL%ROWCOUNT.

BEGIN
 insert into table1
 select * from table2;
 dbms_output.put_line(SQL%ROWCOUNT);
END;
/

Executing this PL/SQL block should display you the number of rows that were
inserted into table1.

Raj





[EMAIL PROTECTED]@fatcity.com on 01/25/2002 01:20:40 PM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


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


Hallo all  you gurus,

How can I write in the pl/sql code if I want to insert in a table the
number of rows that are inserted in the select statement in the procedure?
Give me a good example, please.

Thanks in advance



Roland S

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

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

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



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

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

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









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

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

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

Re: ORACLE-L Digest -- Volume 2002, Number 025


Yes, and if one buys enterprise version with a special 
support plan, there is an option for an onsite 
proctologist.

ORACLE-L Digest -- Volume 2002, Number 025
> --
> 
>  From: "Loughmiller, Greg" <[EMAIL PROTECTED]>
>  Date: Thu, 24 Jan 2002 14:55:44 -0500
>  Subject: RE: SCOTT/TIGER
> 
> And there is documentation that comes with Oracle?   
> 


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

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

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

RE: ORA-01555 Mystery (Help)


Delayed block cleanouts can still cause the ORA-1555, even
after locking the table in exlusive mode.

That's the purpose of the analyze, to force the block cleanouts.

Jared






Paul Baumgartel <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/25/02 09:30 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:RE: ORA-01555 Mystery (Help)


Sure, but the original post concerns a *query*, not a transaction, and
before running the query, the user locked the queried table in
exclusive mode, to ensure that no other session could write to the
queried table.   How do we account for the query's need to read from
rollback? 


--- "Baker, Barbara" <[EMAIL PROTECTED]> wrote:
> 
> I have a batch job that does this consistently.  It's the only job in
> the
> database; it sets the transaction to a hugh rollback segment.  And it
> eats
> its own tail.
> 
> Depending on how the job is written, it may need a read consistent
> view
> itself (as opposed to some other query in the database needing that
> read
> consistent view.)In that case, it may well go try to read its own
> rollback segment, only to find that it's been overwritten.  (Oddly
> enough,
> even when there's plenty of space to extend the rollback, Oracle will
> decide
> to overwrite the original rollback segments rather than extend if it
> thinks
> it doesn't need those segments any more.)
> 
> I'd strongly suggest you get the stuff from Steve Adams' ixora site
> that
> places an uncommitted transaction in your rollback segments for the
> length
> of the run.This will guarantee that the rollback segments don't
> get
> overwritten.
> Good luck!
> 
> Barb
> 
> > --
> > From:Walter K[SMTP:[EMAIL PROTECTED]]
> > Reply To:[EMAIL PROTECTED]
> > Sent:Friday, January 25, 2002 9:15 AM
> > To:  Multiple recipients of list ORACLE-L
> > Subject: ORA-01555 Mystery (Help)
> > 
> > Hi,
> > 
> > A user in our data warehousing group is running into
> > the old ORA-01555 (snapshot too old) error every time
> > she runs a massive (20 million rows) select against
> > one table via a view. I confirmed that the view only
> > translates to the one table.
> > 
> > The user swears that no one would be making any
> > updates/deletes to the table she is selecting from. I
> > suggested she lock the table in exclusive mode, prior
> > to running her massive select to guarantee no one else
> > could change the data in the table and cause the
> > triggering of the 1555 error. Locking the table was a
> > viable option because it's a staging table in the
> > warehouse itself. She locked the table in exclusive
> > mode last night and it locked; fired off her query,
> > and it failed 5 hours later with the 1555 error again.
> > 
> > I'm stumped on this. I just don't see how this is
> > possible. Any suggestions?
> > 
> > Thanks!!!
> > -w
> > 
> > __
.



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

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

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

Re: Number_of_rows


RTFM on SQL%ROWCOUNT  and %ROWCOUNT

Jared

Sorry, all exampled out today.





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/25/02 10:20 AM
Please respond to ORACLE-L

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


Hallo all  you gurus,

How can I write in the pl/sql code if I want to insert in a table the 
number of rows that are inserted in the select statement in the procedure?
Give me a good example, please.

Thanks in advance



Roland S

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

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

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



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

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

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

RE: RE: ORA-01555 Mystery (Help)



> Dick:
> This makes the assumption that Walter can get to the code, find out what
> it's doing, and make modifications.  (In our case, we can't.  The code is
> vendor-supplied, unchangeable, and is written in Cobol).
> 
> It also sounds like this might be happening in the middle of the night.
> I'd guess there's a limit to how much information Walter can gather about
> what happened 5 hours into the job at 3:00 am
> 
> If he's desperate to get the data loaded and he can't change the sql, then
> his options are limited.
> 
> I believe all the possible causes for 1555 errors have been listed in this
> thread.  Hopefully he can identify which is causing the grief and find a
> resolution.
> 
> Barb
> 
> 
>   --
>   From:   [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]]
>   Sent:   Friday, January 25, 2002 11:09 AM
>   To: Baker; Barbara; Multiple recipients of list ORACLE-L
>   Subject:Re:RE: ORA-01555 Mystery (Help)
> 
>   Barb,
> 
>   I've tried Steve's idea in the past and although it sorta fixed
> the problem
>   with the large batch job, it created problems elsewhere.  It also
> did not
>   totally fix the problem when other applications updated parts of the
> table(s)
>   and committed their transaction.  Many folks believe that by
> allocating a large
>   rollback segment to their session they have fixed the problem.
> Wrong, this
>   particular issue can be caused by your own application plus anyone
> else who is
>   using the database and other rollback segments.
> 
>   The real issue here is to either find out who or what is
> updating the
>   underlying table or else speeding up the process.  There were two
> points that I
>   found easy to implement that fixed 90% of our errors.
> 
>   1) Don't commit across a cursor.  In this scenario look for
> cases where your
>   pulling data from a table, updating that table, and then continuing
> to read data
>   from the cursor.  This one will pop a 1555 very regularly since the
> cursor
>   depends on a read consistent view, but you just released the
> rollback segments.
> 
>   2) Use an order or group by in the select statement.  This one
> sounds odd,
>   but it does work.  By placing either an order by or group by clause
> in the
>   select statement you force Oracle to read all of the data at one
> time, place it
>   in a temp segment, and then hand it over.  The end result is that
> when the first
>   row of data appears in your application you no longer need any
> rollback to
>   create a read consistent view.  If your just pulling from the table,
> then Oracle
>   hands over a row as it satisfies the query criteria.  OH, did you
> just update
>   and commit a change?  Well that is NOT going to be included in your
> result set
>   since it is already locked in concrete.
> 
>   Try one of these & see if it fixes your problem.
> 
>   Dick Goulet
> 
>   Reply Separator
>   Subject:RE: ORA-01555 Mystery (Help)
>   Author: "Baker; Barbara" <[EMAIL PROTECTED]>
>   Date:   1/25/2002 8:52 AM
> 
> 
>   I have a batch job that does this consistently.  It's the only job
> in the
>   database; it sets the transaction to a hugh rollback segment.  And
> it eats
>   its own tail.
> 
>   Depending on how the job is written, it may need a read consistent
> view
>   itself (as opposed to some other query in the database needing that
> read
>   consistent view.)In that case, it may well go try to read its
> own
>   rollback segment, only to find that it's been overwritten.  (Oddly
> enough,
>   even when there's plenty of space to extend the rollback, Oracle
> will decide
>   to overwrite the original rollback segments rather than extend if it
> thinks
>   it doesn't need those segments any more.)
> 
>   I'd strongly suggest you get the stuff from Steve Adams' ixora site
> that
>   places an uncommitted transaction in your rollback segments for the
> length
>   of the run.This will guarantee that the rollback segments don't
> get
>   overwritten.
>   Good luck!
> 
>   Barb
> 
>   > --
>   > From: Walter K[SMTP:[EMAIL PROTECTED]]
>   > Reply To: [EMAIL PROTECTED]
>   > Sent: Friday, January 25, 2002 9:15 AM
>   > To:   Multiple recipients of list ORACLE-L
>   > Subject:  ORA-01555 Mystery (Help)
>   > 
>   > Hi,
>   > 
>   > A user in our data warehousing group is running into
>   > the old ORA-01555 (snapshot too old) error every time
>   > she runs a massive (20 million rows) select against
>   > one table via a view. I confirmed that the view only
>   > translates to the one table.
>   > 
>   > The user swears that no one would be making any
>   > updates/deletes to the table she i

Re: Number_of_rows



Use SQL%ROWCOUNT.

BEGIN
 insert into table1
 select * from table2;
 dbms_output.put_line(SQL%ROWCOUNT);
END;
/

Executing this PL/SQL block should display you the number of rows that were
inserted into table1.

Raj





[EMAIL PROTECTED]@fatcity.com on 01/25/2002 01:20:40 PM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


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


Hallo all  you gurus,

How can I write in the pl/sql code if I want to insert in a table the
number of rows that are inserted in the select statement in the procedure?
Give me a good example, please.

Thanks in advance



Roland S

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

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

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



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

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

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

Re: ORA-01555 Mystery (Help)

2002-01-25 Thread Stephane Faroult


I was almost ready to subscribe to the idea of delayed cleanout, but I
cannot understand why really. The necessity for reading a block from the
rollback segments comes from encountering during the course of the
SELECT a block the SCN of which is higher than the SCN when the query
started. I have of course no certainty about it, but it would be logical
to expect the block's SCN to be properly set irrespectively of the
clean-out being immediate or delayed. In other words, even if a SELECT
physically writes blocks, it should not have anything to do with
rollback segments anyway.
I share Mladen's opinion, somebody must be economical with the truth
somewhere, and you should check V$ACCESS, V$SESSION and V$LOCK. Are you
really sure that the code contains no 'just in case' commit ou rollback
which would release the lock? And by the way, 5 hours look to me like an
awfully long time, even for a 20 million row mega-select of death.

[EMAIL PROTECTED] wrote:
> 
> Precisely the point I was trying to make, when I put the question if it was
> a normal select, or if it was within a PL/SQL block?  The myth is that
> snapshot too old happens only when some other transaction was in the
> process of performing an DML on a table, when you did a select on it. It
> can happen for other reasons too. Search on Metalink for "Delayed block
> cleanouts" and "fetch across commits".
> 
> Raj
> 
> "Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on
> 01/25/2002 11:52:05 AM
> 
> Please respond to [EMAIL PROTECTED]
> 
> Sent by:  [EMAIL PROTECTED]
> 
> To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> cc:
> 
> I have a batch job that does this consistently.  It's the only job in the
> database; it sets the transaction to a hugh rollback segment.  And it eats
> its own tail.
> 
> Depending on how the job is written, it may need a read consistent view
> itself (as opposed to some other query in the database needing that read
> consistent view.)In that case, it may well go try to read its own
> rollback segment, only to find that it's been overwritten.  (Oddly enough,
> even when there's plenty of space to extend the rollback, Oracle will
> decide
> to overwrite the original rollback segments rather than extend if it thinks
> it doesn't need those segments any more.)
> 
> I'd strongly suggest you get the stuff from Steve Adams' ixora site that
> places an uncommitted transaction in your rollback segments for the length
> of the run.This will guarantee that the rollback segments don't get
> overwritten.
> Good luck!
> 
> Barb
> 
> > --
> > From:   Walter K[SMTP:[EMAIL PROTECTED]]
> > Reply To:[EMAIL PROTECTED]
> > Sent:   Friday, January 25, 2002 9:15 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: ORA-01555 Mystery (Help)
> >
> > Hi,
> >
> > A user in our data warehousing group is running into
> > the old ORA-01555 (snapshot too old) error every time
> > she runs a massive (20 million rows) select against
> > one table via a view. I confirmed that the view only
> > translates to the one table.
> >
> > The user swears that no one would be making any
> > updates/deletes to the table she is selecting from. I
> > suggested she lock the table in exclusive mode, prior
> > to running her massive select to guarantee no one else
> > could change the data in the table and cause the
> > triggering of the 1555 error. Locking the table was a
> > viable option because it's a staging table in the
> > warehouse itself. She locked the table in exclusive
> > mode last night and it locked; fired off her query,
> > and it failed 5 hours later with the 1555 error again.
> >
> > I'm stumped on this. I just don't see how this is
> > possible. Any suggestions?
> >
> > Thanks!!!
> > -w
> >
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

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

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

OT - this means ??? "MS Exchange runs on SQL Server"

There have been turf wars between "central" 
(mainframe/unix oriented) and "departmental" (NT 
oriented) SysAdmns here for 5+ years (administration 
promulgated a "decentralization" policy, and changed 
IT funding to support it). 

Now, some of the "departmental" SysAdmns are finally 
getting sick of some of the overhead involved in 
running the own little fiefdoms.

At the same time, "central" LAN gurus are taking on AD 
implementation.

With reference to the above, yesterday I heard a 
"central" SysAdmn/LAN guru saying:

"MS Exchange runs on SQL Server"

what does that mean?

Is the SQL Server that Exchange runs on pretty much 
the same as the off-the-shelf version that one would 
install for standard development purposes, or is it 
"pre-tuned", specially configured, etc?

thanks,
ep

ORACLE-L Digest -- Volume 2002, Number 025
> --
> 
>  From: bill thater <[EMAIL PROTECTED]>
>  Date: Thu, 24 Jan 2002 14:14:51 -0500
>  Subject: Re: Backup Strategy
> 
> [EMAIL PROTECTED] wrote:
> 
> >JoJo --
> >
> >Sure, but be aware that Unix abaci are better than NT abaci.
> >
> don't forget the VMS abaci.;-)
> 
> 

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

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

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

RE: ROLLBACK SEGMENT?



Jeremiah, it is.

Thanks, Kirit ;-)

Raj





"Deshpande, Kirti" <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002
12:55:27 PM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


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


Guys, in the next round, please correct Mr. Wilton's first name to -
Jeremiah.

Cut & paste is a wonderful thing ;-)


- Kirti


-Original Message-
Sent: Friday, January 25, 2002 10:35 AM
To: Multiple recipients of list ORACLE-L



John,

I DISAGREE. The gymnastics of assigning a large rollback segment to an
export could avoid the snapshot too old error.

I agree with Jeremy when he says export does not generate rollback. But I
was trying to impress upon him that still an export could end up with the
snapshot too old message, particularly if there are plenty of active DML
transactions happening while the export is in progress, or if the export
uses the consistent parameter, or the rollback segments are not properly
sized.

To stress my point further, Note:22836.1 on Metalink. I rest my case ;-)

Raj

orantdba <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002 08:20:25 AM
Please respond to [EMAIL PROTECTED]
Sent by:  [EMAIL PROTECTED]
To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:


Hi Raj,

Interesting that you first agree with Jeremy and then argue with him.  It
is precisely because
export does not generate rollback that the "gymnastics" of taking all of
the other rbs's offline
will not help anything.  It might make you feel better, however :-).

John

[EMAIL PROTECTED] wrote:




Export doesn't generate any rollback, right, so what is it supposed to
accomplish by doing this incantation?





Sorry to press the point, but could you elaborate on how that "COULD"
possibly make any difference for 'snapshot too old'?



For the same reason, any other transaction could end up with a snapshot too
old error. Export does not generate any rollback, but there could be users
performing DML operations on the table  that is being exported, and the
export needs to be redirected to read from the rollback segments. The
likelihood of the error being thrown up especially if one uses the
consistent parameter could be very high, if you dont have a large enough
rollback segment without an optimal clause.

Raj


Jeremiah Wilton <[EMAIL PROTECTED]>@fatcity.com on 01/23/2002 11:20:40
AM

Please respond to [EMAIL PROTECTED]
Sent by:  [EMAIL PROTECTED]
To:   Multiple recipien
ts of list ORACLE-L <[EMAIL PROTECTED]>
cc:

On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote:

H ... "COULD" help in avoiding snapshot too old errors.

Sorry to press the point, but could you elaborate on how that "COULD"
possibly make any difference for 'snapshot too old'?

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton


Jeremiah Wilton <[EMAIL PROTECTED]> wrote:

So what does it accomplish to "assign export [to] a particular
rollback segment?"

Export doesn't generate any rollback, right, so what is it supposed to
accomplish by doing this incantation?

On Tue, 22 Jan 2002, Jason Rowski wrote:

... you can use the following trick to assign export a
particular rollback segment -

1) Create a rollback segment tablespace with one large
segment and bring it online before export.
2) Offline all existing rollback segments.
3) Export the database
4) Offline the large tablespace created earlier.
5) Bring back the orginals rollback segments online.

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

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

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



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

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

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

RE: Mirroring REDO logs to an nfs drive

2002-01-25 Thread Browett, Darren


I have experienced problems in the past when using NFS drives to dump
large amounts of data.  No sure if having the redo logs there will cause the
same problem.

I am running a tru64 4.0f environment, and what I was doing ( needed the
disk
space at the time) was export my production databases to a NFS mounted
drive.  

>From what I understand, due to the amount of data being dumped to the NFS
drive, 
I basically over-saturated the  connection, which caused the "automount"
daemon on 
my other systems to drop the NFS drive periodically and without warning. 

To fix the problem was to simply stop the export processes going to the NFS
drives (which were running
every 2nd night) and reboot the NFS master.

Darren

-Original Message-
Sent: January 24, 2002 11:49 AM
To: Multiple recipients of list ORACLE-L


Our site is preparing to fail over to our backup server.  We need to do
maintenance on our production server, and will be running on the backup for
about 24 hours.  One issue I brought up was that our backup server is not
equiped with mirrored drives, thus there was the possibility that a drive
failure could destroy an online redo log.  On our production box the logs
are not software mirrored, because of the physical mirroring in our drive
cabinet.

As a result I was told to multiplex the redo logs once we had failed over to
the backup server.  Furthermore I would add the new members to an nfs drive,
so that even a pesky controller couldn't foil our mirrored log files.

I have some questions about this.  First, am I just looking for problems by
doing this?  I would appreciate any tips or warnings on this subject.
Secondly, researching this topic made me curious as to my DB's settings for
MAXLOGFILES and MAXLOGMEMBERS.  Where can I find these parameters?  I was
sure I would find them in v$parameter, but they were not there.

Thanks for any response,
Steve McClure

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

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

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

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

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

RE: Session_wait

2002-01-25 Thread Babich , Sergey


Hi, Ross,
This has been running for about 4 hours now. I got an SQL trace file, and
looked at the execution plans. So here's the deal. There's an interesting
join condition "...where A.col1=B.col1.". However, A has a few hundred
distinct values in that column, none of them being NULL, and B, which has a
few hundred thousand rows, has ALL NULLs in the corresponding column, and
that column is not indexed, too. That's the query where it sits for a couple
of hours. Guess what the optimizer is doing (8i)? I think internal effects
are secondary in this scenario. It is the production (including the
database) designed by the company named DELTEK, so nobody can change the
code. Anyway, I reported my findings...
Thank you very much for your help, it's always appreciated.
Best,
Sergey

 -Original Message-
Sent:   Friday, January 25, 2002 12:56 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Session_wait

This is a busy little beaver...how long you say this runs?

opened cursors cumulative  1072
session logical reads  77233609
db block gets   2642119
consistent gets74591490
physical reads   148822
db block changes3005410
consistent changes  141
no work - consistent read gets 72058049
table scans (short tables)   210918
table scans (long tables)36
table scan rows gotten798264962
table scan blocks gotten   71788386
table fetch by rowid1074164
leaf node splits   4018
execute count 74445
bytes sent via SQL*Net to client  59650
bytes received via SQL*Net from client84233
SQL*Net roundtrips to/from client   342
buffer is not pinned count 73513922

I see some updates (or inserts) and heavy reads...at first glance, 
though, you don't appear to be I/O throttled but likely have inefficiencies
in buffer cache. Pin some small tables? Reexamine access paths for
fts, even if on "small" tables (generally defined to be around
5% in blocks of buffer cache size?)and consider seeking out
and destroying  nested loops joins in favor of hash joins. There's
more CPU, but less buffer cache splashing arounddon't forget to
review init.ora settings for hash, buffer pools, and query planning. 
(...be happy to do a flyover of those as well, if you like.) 

That's about all i can get in a one minute glance, but there are alot of
people on the list who'll see more. Look to them for longer posts with
more explanation. 

Good Luck!

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

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

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

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

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

Number_of_rows

2002-01-25 Thread Roland . Skoldblom


Hallo all  you gurus,

How can I write in the pl/sql code if I want to insert in a table the number of rows 
that are inserted in the select statement in the procedure?
Give me a good example, please.

Thanks in advance



Roland S

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

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

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

Re: ROLLBACK SEGMENT?

2002-01-25 Thread orantdba


Sorry Jeremiah!

John

[EMAIL PROTECTED] wrote:

>Guys, in the next round, please correct Mr. Wilton's first name to -
>Jeremiah. 
>
>Cut & paste is a wonderful thing ;-)
> 
>
>- Kirti 
>
>
>-Original Message-
>Sent: Friday, January 25, 2002 10:35 AM
>To: Multiple recipients of list ORACLE-L
>
>
>
>John,
>
>I DISAGREE. The gymnastics of assigning a large rollback segment to an
>export could avoid the snapshot too old error.
>
>I agree with Jeremy when he says export does not generate rollback. But I
>was trying to impress upon him that still an export could end up with the
>snapshot too old message, particularly if there are plenty of active DML
>transactions happening while the export is in progress, or if the export
>uses the consistent parameter, or the rollback segments are not properly
>sized.
>
>To stress my point further, Note:22836.1 on Metalink. I rest my case ;-)
>
>Raj
>
>orantdba <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002 08:20:25 AM
>Please respond to [EMAIL PROTECTED]
>Sent by:  [EMAIL PROTECTED]
>To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>cc:
>
>
>Hi Raj,
>
>Interesting that you first agree with Jeremy and then argue with him.  It
>is precisely because
>export does not generate rollback that the "gymnastics" of taking all of
>the other rbs's offline
>will not help anything.  It might make you feel better, however :-).
>
>John
>
>[EMAIL PROTECTED] wrote:
>
>
>
>
>Export doesn't generate any rollback, right, so what is it supposed to
>accomplish by doing this incantation?
>
>
>
>
>
>Sorry to press the point, but could you elaborate on how that "COULD"
>possibly make any difference for 'snapshot too old'?
>
>
>
>For the same reason, any other transaction could end up with a snapshot too
>old error. Export does not generate any rollback, but there could be users
>performing DML operations on the table  that is being exported, and the
>export needs to be redirected to read from the rollback segments. The
>likelihood of the error being thrown up especially if one uses the
>consistent parameter could be very high, if you dont have a large enough
>rollback segment without an optimal clause.
>
>Raj
>
>
>Jeremiah Wilton <[EMAIL PROTECTED]>@fatcity.com on 01/23/2002 11:20:40
>AM
>
>Please respond to [EMAIL PROTECTED]
>Sent by:  [EMAIL PROTECTED]
>To:   Multiple recipien
>ts of list ORACLE-L <[EMAIL PROTECTED]>
>cc:
>
>On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote:
>
>H ... "COULD" help in avoiding snapshot too old errors.
>
>Sorry to press the point, but could you elaborate on how that "COULD"
>possibly make any difference for 'snapshot too old'?
>
>--
>Jeremiah Wilton
>http://www.speakeasy.net/~jwilton
>
>
>Jeremiah Wilton <[EMAIL PROTECTED]> wrote:
>
>So what does it accomplish to "assign export [to] a particular
>rollback segment?"
>
>Export doesn't generate any rollback, right, so what is it supposed to
>accomplish by doing this incantation?
>
>On Tue, 22 Jan 2002, Jason Rowski wrote:
>
>... you can use the following trick to assign export a
>particular rollback segment -
>
>1) Create a rollback segment tablespace with one large
>segment and bring it online before export.
>2) Offline all existing rollback segments.
>3) Export the database
>4) Offline the large tablespace created earlier.
>5) Bring back the orginals rollback segments online.
>


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

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

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

Re: Backup Stragedy


I already did that, about a year ago.

btw, there is a (very low-traffic) oracle-nt list:

http://groups.yahoo.com
-
http://groups.yahoo.com/group/oracle-on-nt


a collection links to this kind of stuff could be put 
there?

regards,
ep

ORACLE-L Digest -- Volume 2002, Number 025
> --
> 
>  From: "C.S.Venkata Subramanian" <[EMAIL PROTECTED]>
>  Date: Thu, 24 Jan 2002 18:04:02 +0530
>  Subject: Re: Backup Strategy
> 
> Hi All,
> 
>  http://www.geocities.com/tbcox23/
> 
> Go here and get the paper.
> 
> Regards
> Venkat
> --
> 


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

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

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

How can i receive name of the running procedure

2002-01-25 Thread Roland . Skoldblom


Hallo,

How can I select the name of the procedure, which is running.?
I mean I am running a procedure and I want the name of the pocedure to be inserted in 
a table.
 Please help me with a simple pl/sql script on this.

Thanks in advance


Roland S

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

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

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

RE: v$session question


What platform? 

On HP-UX 11.0 and Oracle 8.1.6.2.0 and w/ Clients on Win/NT 4 Workstations, 
here is what I get: (last few lines)

SQL> select username, program from v$session;
USERNAMEPROGRAM
--- ---
X468Y02 C:\orant\bin\ifrun60.exe
X0C0AJF C:\orant\bin\ifrun60.exe
X020C7P C:\orant\bin\ifrun60.exe
XFZGBMX C:\orant\bin\ifrun60.exe
X225D64 C:\orant\bin\ifrun60.exe
XDTF9GR C:\orant\bin\ifrun60.exe


- Kirti

-Original Message-
Sent: Friday, January 25, 2002 8:35 AM
To: Multiple recipients of list ORACLE-L



In the past, I was running Oracle 8.1.5 and the clients were running
Oracle Forms 4.5.  When I queried v$session and looked at the program
field  I could see what clients where running f45run32.exe.

Now we are on Oracle 8.1.6 and the clients are running Forms60.  When I
now query v$session the program filed is null?

What view could I query in 8.1.6 to see which users are running
ifrun60.exe?

Thanks,
Joe

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

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

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

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

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

RE: Session_wait

2002-01-25 Thread Mohan, Ross


This is a busy little beaver...how long you say this runs?

opened cursors cumulative  1072
session logical reads  77233609
db block gets   2642119
consistent gets74591490
physical reads   148822
db block changes3005410
consistent changes  141
no work - consistent read gets 72058049
table scans (short tables)   210918
table scans (long tables)36
table scan rows gotten798264962
table scan blocks gotten   71788386
table fetch by rowid1074164
leaf node splits   4018
execute count 74445
bytes sent via SQL*Net to client  59650
bytes received via SQL*Net from client84233
SQL*Net roundtrips to/from client   342
buffer is not pinned count 73513922

I see some updates (or inserts) and heavy reads...at first glance, 
though, you don't appear to be I/O throttled but likely have inefficiencies
in buffer cache. Pin some small tables? Reexamine access paths for
fts, even if on "small" tables (generally defined to be around
5% in blocks of buffer cache size?)and consider seeking out
and destroying  nested loops joins in favor of hash joins. There's
more CPU, but less buffer cache splashing arounddon't forget to
review init.ora settings for hash, buffer pools, and query planning. 
(...be happy to do a flyover of those as well, if you like.) 

That's about all i can get in a one minute glance, but there are alot of
people on the list who'll see more. Look to them for longer posts with
more explanation. 

Good Luck!

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

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

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

RE: Oracle DBAs Needed in Boston


Becauses, he needs ... HELP  ;-)

-Original Message-
Sent: Friday, January 25, 2002 10:55 AM
To: Multiple recipients of list ORACLE-L


I am picky perhaps, but why is there an apostrophe between "its" and "I.T.
staff"?

This is like store fronts that don't put apostrophes anywhere.

: )

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

-Original Message-
Sent:   Friday, January 25, 2002 12:40 PM
To: Multiple recipients of list ORACLE-L
Subject:Oracle DBAs Needed in Boston

This well known and highly respected client in Boston, Mass. needs an
Oracle DBA to join its' I.T.staff.
A great opportunity for the right candidate with ALL the required skills
listed below.


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

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

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

RE: ROLLBACK SEGMENT?


Guys, in the next round, please correct Mr. Wilton's first name to -
Jeremiah. 

Cut & paste is a wonderful thing ;-)
 

- Kirti 


-Original Message-
Sent: Friday, January 25, 2002 10:35 AM
To: Multiple recipients of list ORACLE-L



John,

I DISAGREE. The gymnastics of assigning a large rollback segment to an
export could avoid the snapshot too old error.

I agree with Jeremy when he says export does not generate rollback. But I
was trying to impress upon him that still an export could end up with the
snapshot too old message, particularly if there are plenty of active DML
transactions happening while the export is in progress, or if the export
uses the consistent parameter, or the rollback segments are not properly
sized.

To stress my point further, Note:22836.1 on Metalink. I rest my case ;-)

Raj

orantdba <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002 08:20:25 AM
Please respond to [EMAIL PROTECTED]
Sent by:  [EMAIL PROTECTED]
To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:


Hi Raj,

Interesting that you first agree with Jeremy and then argue with him.  It
is precisely because
export does not generate rollback that the "gymnastics" of taking all of
the other rbs's offline
will not help anything.  It might make you feel better, however :-).

John

[EMAIL PROTECTED] wrote:




Export doesn't generate any rollback, right, so what is it supposed to
accomplish by doing this incantation?





Sorry to press the point, but could you elaborate on how that "COULD"
possibly make any difference for 'snapshot too old'?



For the same reason, any other transaction could end up with a snapshot too
old error. Export does not generate any rollback, but there could be users
performing DML operations on the table  that is being exported, and the
export needs to be redirected to read from the rollback segments. The
likelihood of the error being thrown up especially if one uses the
consistent parameter could be very high, if you dont have a large enough
rollback segment without an optimal clause.

Raj


Jeremiah Wilton <[EMAIL PROTECTED]>@fatcity.com on 01/23/2002 11:20:40
AM

Please respond to [EMAIL PROTECTED]
Sent by:  [EMAIL PROTECTED]
To:   Multiple recipien
ts of list ORACLE-L <[EMAIL PROTECTED]>
cc:

On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote:

H ... "COULD" help in avoiding snapshot too old errors.

Sorry to press the point, but could you elaborate on how that "COULD"
possibly make any difference for 'snapshot too old'?

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton


Jeremiah Wilton <[EMAIL PROTECTED]> wrote:

So what does it accomplish to "assign export [to] a particular
rollback segment?"

Export doesn't generate any rollback, right, so what is it supposed to
accomplish by doing this incantation?

On Tue, 22 Jan 2002, Jason Rowski wrote:

... you can use the following trick to assign export a
particular rollback segment -

1) Create a rollback segment tablespace with one large
segment and bring it online before export.
2) Offline all existing rollback segments.
3) Export the database
4) Offline the large tablespace created earlier.
5) Bring back the orginals rollback segments online.

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

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

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

Re: Add mod_ldap to 9iAS Apache


Here's some places to start:

http://httpd.apache.org/docs/sitemap.html
http://www.kie.berkeley.edu/people/jmorrow/mod_ldap/

Jared





"James Howerton" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/25/02 08:55 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Add mod_ldap to 9iAS Apache


DBA's

Does anyone have instructions for re-compiling apache to include
mod_ldap? We need to integrate our existing iplanet ldap into 9iAS for
reports and portal. Metalink states re-compiling apache is not supported
and the only manuals I've found so far only address OID.

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

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

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



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

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

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

RE: Backup Strategy

2002-01-25 Thread Grabowy, Chris


Igor,

That sounds good, but what is Oracle Support going to say when you call them
for support on a database recovery and you mention that you used NT COPY??
Your hosed, if there attitude is you should have used OCOPY so we can't help
you.

Chris

-Original Message-
Sent: Friday, January 25, 2002 11:26 AM
To: Multiple recipients of list ORACLE-L


Ok, I guess, I owe some explanation here, since I've got a lot of spanking
(replies, some rather sarcastic) regarding this issue.
My original note comes from my "real life" experience, so I'm still standing
behind it.
Sorry, it'll be kind of long, but if you are really interested...

Couple years ago, when we were preparing first release of our product, I
read of course about "NT Copy" versus "Oracle Ocopy".
Still I decided to test it, because not always I trust what I read, and I
like to get proof myself.
Testing of online ("hot") backup/recovery scenario showed, that using "NT
Copy" command in backup scripts is perfectly fine, when creating backup set
of files on the disk.  And there is no problem restoring from this backup.
Now this "disk backup" set of files could be saved on tape, using NTBACKUP
(that's the one, that really can not copy file, if it's opened by some other
program.  But that's not the case with prepared in advance "disk backup").
"NT Copy" has no problems copying files opened already by Oracle, and backup
is consistent, as long of course as I am using "alter tablespace 
begin backup" before copying relevant files and "alter tablespace  end
backup" after finishing files copy.
So, those scripts (using "NT Copy") were put into production, and now have
been used for more than two years on more than hundred installations/sites
(the number keeps growing).
>From time to time, our field engineers are bringing back to me sets of
online (can not use "cold" backup - our systems should run 24*7, I'm not
saying they are, but we are trying to minimize downtime) backed up files (db
files and archived RedoLog files), and I recover them with no problem (we
need this, to test how the upgrade to next release of our product will run
against "real" customers data).

Now, about MetaLink Note:139327.1
It says:

Ocopy  opens the file using CreateFile() with the FILE_SHARE_READ and
FILE_SHARE_WRITE  flags. This allows writing to continue while we take the
backup.  Inconsistencies in the backup are repaired by applying archived
redo  during  recovery. The 'copy' command from NT doesn't use these flags
since it  wants to  prevent writes to the file while the copy is taking
place.


I don't think, it's very accurate, and here is why:
When during online backup I run "NT copy" against db file, the file is
already opened by Oracle (at moment, when I "open" the database).
So, even if "NT copy" opens file without FILE_SHARE_READ and
FILE_SHARE_WRITE  flags, all it means is that "Subsequent open operations on
the object will fail" (quote from NT docs).  I want you to notice, it says
"Subsequent open operations" not "Subsequent write/read operations".  So,
all it does is prohibiting some other program/process from "opening" the
file.  But Oracle, as I mentioned, has this file already opened, and it is
perfectly capable of reading/writing this file.
Of course, the image of the saved file will be "fuzzy", and that's why when
recovering from online backup we are applying archived RedoLog files (which
getting written much more intensely during online backup).

As for Peter McLarty note, that  he "never knew that NT copy could manage
keeping the CSN number in sync",
well it ("NT Copy") does not have to (neither does "Oracle Ocopy") keep CSN
number in sync.
Oracle updates file header with checkpoint SCN, when we issue "alter
tablespace  begin backup".  Then until "alter tablespace  end
backup", file header will cease updating.  And SCN, written in the beginning
provides the info, which archived RedoLog files should be used for recovery.

Now, please correct me, if I'm wrong.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]


- Original Message -
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, January 24, 2002 6:47 PM


> > Wrong. NT 'COPY' has no problems copying 'opened' oracle db files.
> > I'm using it in 'hot backup' scripts on many dozens systems, and it
> works
> > fine.
>
> Igor, you sure about that?
>
> There are backup packages (such as Backup Exec ) that use their
> own file open copy program to avoid using copy.exe.
>
> Have you restored any of these backups made with copy?
>
> Jared
>
> Note:139327.1
> Subject:
>  Differences between Windows NT COPY and Oracle
>  OCOPY When Doing Backups
>
> Creation Date:03-APR-2001
> Last Revision Date:   04-DEC-2001
>
>  PURPOSE
>A comparison of the differences between the Windows NT "copy" commnad,
> and
>the Oracle "ocopy" command.  Which should be used during an online
> backup?
>
>  SCOPE & APPLICATION
>DBAs with databases on the Windows NT platform.
>
>  The Differences betw

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Paul Baumgartel


Sure, but the original post concerns a *query*, not a transaction, and
before running the query, the user locked the queried table in
exclusive mode, to ensure that no other session could write to the
queried table.   How do we account for the query's need to read from
rollback?  


--- "Baker, Barbara" <[EMAIL PROTECTED]> wrote:
> 
> I have a batch job that does this consistently.  It's the only job in
> the
> database; it sets the transaction to a hugh rollback segment.  And it
> eats
> its own tail.
> 
> Depending on how the job is written, it may need a read consistent
> view
> itself (as opposed to some other query in the database needing that
> read
> consistent view.)In that case, it may well go try to read its own
> rollback segment, only to find that it's been overwritten.  (Oddly
> enough,
> even when there's plenty of space to extend the rollback, Oracle will
> decide
> to overwrite the original rollback segments rather than extend if it
> thinks
> it doesn't need those segments any more.)
> 
> I'd strongly suggest you get the stuff from Steve Adams' ixora site
> that
> places an uncommitted transaction in your rollback segments for the
> length
> of the run.This will guarantee that the rollback segments don't
> get
> overwritten.
> Good luck!
> 
> Barb
> 
> > --
> > From:   Walter K[SMTP:[EMAIL PROTECTED]]
> > Reply To:   [EMAIL PROTECTED]
> > Sent:   Friday, January 25, 2002 9:15 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject:ORA-01555 Mystery (Help)
> > 
> > Hi,
> > 
> > A user in our data warehousing group is running into
> > the old ORA-01555 (snapshot too old) error every time
> > she runs a massive (20 million rows) select against
> > one table via a view. I confirmed that the view only
> > translates to the one table.
> > 
> > The user swears that no one would be making any
> > updates/deletes to the table she is selecting from. I
> > suggested she lock the table in exclusive mode, prior
> > to running her massive select to guarantee no one else
> > could change the data in the table and cause the
> > triggering of the 1555 error. Locking the table was a
> > viable option because it's a staging table in the
> > warehouse itself. She locked the table in exclusive
> > mode last night and it locked; fired off her query,
> > and it failed 5 hours later with the 1555 error again.
> > 
> > I'm stumped on this. I just don't see how this is
> > possible. Any suggestions?
> > 
> > Thanks!!!
> > -w
> > 
> > __
> > Do You Yahoo!?
> > Great stuff seeking new owners in Yahoo! Auctions! 
> > http://auctions.yahoo.com
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > -- 
> > Author: Walter K
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> > San Diego, California-- Public Internet access / Mailing
> Lists
> >
> 
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like
> subscribing).
> > 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Baker, Barbara
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

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

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

RE: Session_wait

2002-01-25 Thread Babich , Sergey


Eventually...

 real: 0
SQL> select 
  2  name, value 
  3  from 
  4  v$sesstat vs, v$statname sn 
  5  where 
  6  vs.statistic#=sn.statistic# and 
  7  value is NOT NULL and 
  8  value<>0 and 
  9  sid=11;

NAME  VALUE
  -
logons cumulative 1
logons current1
opened cursors cumulative  1072
opened cursors current   11
user commits 26
user calls  341
recursive calls  198492
recursive cpu usage4089
session logical reads  77233609
CPU used when call started   696253
CPU used by this session 696253
session connect time   20654909
process last non-idle time 20654909
session uga memory  1347972
session uga memory max  2872124
messages sent  2154
session pga memory  6752520
session pga memory max  6752520
enqueue requests  11878
enqueue releases  11876
total file opens 13
db block gets   2642119
consistent gets74591490
physical reads   148822
db block changes3005410
consistent changes  141
physical writes2728
physical writes non checkpoint 2728
change write time 29633
redo synch writes40
redo synch time 305
free buffer requested311344
dirty buffers inspected   63544
pinned buffers inspected  2
hot buffers moved to head of LRU  13576
free buffer inspected 63546
commit cleanout failures: block lost860
commit cleanout failures: callback failure   12
commit cleanouts  79286
commit cleanouts successfully completed   78414
CR blocks created   141
switch current to new buffer  54870
write clones created in foreground  198
prefetched blocks108149
physical reads direct  3594
physical writes direct 2728
calls to kcmgcs   56343
calls to kcmgas4130
calls to get snapshot scn: kcmgss 82845
redo entries1506007
redo size 1.184E+09
redo buffer allocation retries  886
redo log space requests   3
redo log space wait time122
redo ordering marks   4
data blocks consistent reads - undo records applied 141
no work - consistent read gets 72058049
cleanouts only - consistent read gets 31927
rollbacks only - consistent read gets   141
immediate (CURRENT) block cleanout applications   12274
immediate (CR) block cleanout applications31927

RE: Backup Strategy

2002-01-25 Thread Johnston, Tim


Hi Igor...

  I have a couple questions to you...  What do you think will happen the
first time you have ANY problem recovering a backup that was taken with the
COPY utility and you contact Oracle support...  Will Oracle support you or
immediately blame it on you backup method since it is documented that you
should not use it...


-Original Message-
Sent: Friday, January 25, 2002 11:26 AM
To: Multiple recipients of list ORACLE-L


Ok, I guess, I owe some explanation here, since I've got a lot of spanking
(replies, some rather sarcastic) regarding this issue.
My original note comes from my "real life" experience, so I'm still standing
behind it.
Sorry, it'll be kind of long, but if you are really interested...

Couple years ago, when we were preparing first release of our product, I
read of course about "NT Copy" versus "Oracle Ocopy".
Still I decided to test it, because not always I trust what I read, and I
like to get proof myself.
Testing of online ("hot") backup/recovery scenario showed, that using "NT
Copy" command in backup scripts is perfectly fine, when creating backup set
of files on the disk.  And there is no problem restoring from this backup.
Now this "disk backup" set of files could be saved on tape, using NTBACKUP
(that's the one, that really can not copy file, if it's opened by some other
program.  But that's not the case with prepared in advance "disk backup").
"NT Copy" has no problems copying files opened already by Oracle, and backup
is consistent, as long of course as I am using "alter tablespace 
begin backup" before copying relevant files and "alter tablespace  end
backup" after finishing files copy.
So, those scripts (using "NT Copy") were put into production, and now have
been used for more than two years on more than hundred installations/sites
(the number keeps growing).
>From time to time, our field engineers are bringing back to me sets of
online (can not use "cold" backup - our systems should run 24*7, I'm not
saying they are, but we are trying to minimize downtime) backed up files (db
files and archived RedoLog files), and I recover them with no problem (we
need this, to test how the upgrade to next release of our product will run
against "real" customers data).

Now, about MetaLink Note:139327.1
It says:

Ocopy  opens the file using CreateFile() with the FILE_SHARE_READ and
FILE_SHARE_WRITE  flags. This allows writing to continue while we take the
backup.  Inconsistencies in the backup are repaired by applying archived
redo  during  recovery. The 'copy' command from NT doesn't use these flags
since it  wants to  prevent writes to the file while the copy is taking
place.


I don't think, it's very accurate, and here is why:
When during online backup I run "NT copy" against db file, the file is
already opened by Oracle (at moment, when I "open" the database).
So, even if "NT copy" opens file without FILE_SHARE_READ and
FILE_SHARE_WRITE  flags, all it means is that "Subsequent open operations on
the object will fail" (quote from NT docs).  I want you to notice, it says
"Subsequent open operations" not "Subsequent write/read operations".  So,
all it does is prohibiting some other program/process from "opening" the
file.  But Oracle, as I mentioned, has this file already opened, and it is
perfectly capable of reading/writing this file.
Of course, the image of the saved file will be "fuzzy", and that's why when
recovering from online backup we are applying archived RedoLog files (which
getting written much more intensely during online backup).

As for Peter McLarty note, that  he "never knew that NT copy could manage
keeping the CSN number in sync",
well it ("NT Copy") does not have to (neither does "Oracle Ocopy") keep CSN
number in sync.
Oracle updates file header with checkpoint SCN, when we issue "alter
tablespace  begin backup".  Then until "alter tablespace  end
backup", file header will cease updating.  And SCN, written in the beginning
provides the info, which archived RedoLog files should be used for recovery.

Now, please correct me, if I'm wrong.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]


- Original Message -
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, January 24, 2002 6:47 PM


> > Wrong. NT 'COPY' has no problems copying 'opened' oracle db files.
> > I'm using it in 'hot backup' scripts on many dozens systems, and it
> works
> > fine.
>
> Igor, you sure about that?
>
> There are backup packages (such as Backup Exec ) that use their
> own file open copy program to avoid using copy.exe.
>
> Have you restored any of these backups made with copy?
>
> Jared
>
> Note:139327.1
> Subject:
>  Differences between Windows NT COPY and Oracle
>  OCOPY When Doing Backups
>
> Creation Date:03-APR-2001
> Last Revision Date:   04-DEC-2001
>
>  PURPOSE
>A comparison of the differences between the Windows NT "copy" commnad,
> and
>the Oracle "ocopy" command.  Which should be used during an online
> backup?
>
>  SCOPE & APPLIC

OT: RE: Oracle DBAs Needed in Boston

2002-01-25 Thread Paul Baumgartel


Not only shouldn't there be an apostophe, but it's in the wrong place. 
Geeze.  ;-)

--- "Boivin, Patrice J" <[EMAIL PROTECTED]> wrote:
> I am picky perhaps, but why is there an apostrophe between "its" and
> "I.T.
> staff"?
> 
> This is like store fronts that don't put apostrophes anywhere.
> 
> : )
> 
> Regards,
> Patrice Boivin
> Systems Analyst (Oracle Certified DBA)
> 
> -Original Message-
> Sent: Friday, January 25, 2002 12:40 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Oracle DBAs Needed in Boston
> 
> This well known and highly respected client in Boston, Mass. needs an
> Oracle DBA to join its' I.T.staff.
> A great opportunity for the right candidate with ALL the required
> skills
> listed below.
> 
> * Duties and Responsibilities
> Work with technical and application analysts to integrate project
> requirements into 
> efficient Oracle database designs/modifications in primarily 2-tier
> configurations. 
> Work with senior staff and management in database strategy decisions
> in a
> complex application 
> environment. 
> 
> * Requirements:
> -Candidates must have 5-7 years experience as an Oracle DBA
> -Must have managed Oracle in a production environment. 
> -A minimum of one year work experience with Oracle 8i. 
> -Proficient in use of Oracle advanced features such as MTS,
> Replication, and
> Partitioning. 
> -Extensive experience with performance tuning and troubleshooting, at
> the
> OS, database, 
>  and application levels. 
> -Candidate must be comfortable with a range of tools to support
> monitoring
> and tuning activities.
> -Develop clear well-written documentation. 
> -Proficient in developing a range of documentation from high level
> approach
> to detailed 
>  specifications. 
> -Knowledge of Veritas, OEM, and Oracle 9I a plus. 
> 
> U.S. citizenship or permanent residency is also required.
> 
> This position offers:
> * Opportunity to become a key member of the team
> * Base Salary -in the 85K range D.O.E.
> * Relocation Assistance
> 
> NO sub contracting positions available.
> *U.S. citizenship only
> PLEASE do not send your resume if you are not in the United States.
> 
> For  immediate consideration, please send your resume as an
> attachment to:
> OraStaff, Inc.
> Email: [EMAIL PROTECTED]
> Please use job code: One/Boston/DBA/DF
> 
> We pay referral fees.
> So please contact me if you know of anyone who would be
> qualified/interested
> in the
> posiition described above- if it is not a match for your skills.
> Thanks,
> OraStaff
> 
> 
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: OraStaff
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Boivin, Patrice J
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

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

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

RE: ORA-01555 Mystery (Help)



Precisely the point I was trying to make, when I put the question if it was
a normal select, or if it was within a PL/SQL block?  The myth is that
snapshot too old happens only when some other transaction was in the
process of performing an DML on a table, when you did a select on it. It
can happen for other reasons too. Search on Metalink for "Delayed block
cleanouts" and "fetch across commits".

Raj






"Baker, Barbara" <[EMAIL PROTECTED]>@fatcity.com on
01/25/2002 11:52:05 AM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


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



I have a batch job that does this consistently.  It's the only job in the
database; it sets the transaction to a hugh rollback segment.  And it eats
its own tail.

Depending on how the job is written, it may need a read consistent view
itself (as opposed to some other query in the database needing that read
consistent view.)In that case, it may well go try to read its own
rollback segment, only to find that it's been overwritten.  (Oddly enough,
even when there's plenty of space to extend the rollback, Oracle will
decide
to overwrite the original rollback segments rather than extend if it thinks
it doesn't need those segments any more.)

I'd strongly suggest you get the stuff from Steve Adams' ixora site that
places an uncommitted transaction in your rollback segments for the length
of the run.This will guarantee that the rollback segments don't get
overwritten.
Good luck!

Barb

> --
> From:   Walter K[SMTP:[EMAIL PROTECTED]]
> Reply To:[EMAIL PROTECTED]
> Sent:   Friday, January 25, 2002 9:15 AM
> To: Multiple recipients of list ORACLE-L
> Subject: ORA-01555 Mystery (Help)
>
> Hi,
>
> A user in our data warehousing group is running into
> the old ORA-01555 (snapshot too old) error every time
> she runs a massive (20 million rows) select against
> one table via a view. I confirmed that the view only
> translates to the one table.
>
> The user swears that no one would be making any
> updates/deletes to the table she is selecting from. I
> suggested she lock the table in exclusive mode, prior
> to running her massive select to guarantee no one else
> could change the data in the table and cause the
> triggering of the 1555 error. Locking the table was a
> viable option because it's a staging table in the
> warehouse itself. She locked the table in exclusive
> mode last night and it locked; fired off her query,
> and it failed 5 hours later with the 1555 error again.
>
> I'm stumped on this. I just don't see how this is
> possible. Any suggestions?
>
> Thanks!!!
> -w
>
> __
> Do You Yahoo!?
> Great stuff seeking new owners in Yahoo! Auctions!
> http://auctions.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Walter K
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
  INET: [EMAIL PROTECTED]

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

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



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

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

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

Re: ROLLBACK SEGMENT?

2002-01-25 Thread orantdba


HI Raj,

I hope you feel better :-).

John

[EMAIL PROTECTED] wrote:

>John,
>
>I DISAGREE. The gymnastics of assigning a large rollback segment to an
>export could avoid the snapshot too old error.
>
>I agree with Jeremy when he says export does not generate rollback. But I
>was trying to impress upon him that still an export could end up with the
>snapshot too old message, particularly if there are plenty of active DML
>transactions happening while the export is in progress, or if the export
>uses the consistent parameter, or the rollback segments are not properly
>sized.
>
>To stress my point further, Note:22836.1 on Metalink. I rest my case ;-)
>
>Raj
>
>
>
>
>
>orantdba <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002 08:20:25 AM
>
>Please respond to [EMAIL PROTECTED]
>
>
>
>Sent by:  [EMAIL PROTECTED]
>
>
>To:   Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>cc:
>
>
>Hi Raj,
>
>Interesting that you first agree with Jeremy and then argue with him.  It
>is precisely because
>export does not generate rollback that the "gymnastics" of taking all of
>the other rbs's offline
>will not help anything.  It might make you feel better, however :-).
>
>John
>
>[EMAIL PROTECTED] wrote:
>
>
>
>
>Export doesn't generate any rollback, right, so what is it supposed to
>accomplish by doing this incantation?
>
>
>
>
>
>Sorry to press the point, but could you elaborate on how that "COULD"
>possibly make any difference for 'snapshot too old'?
>
>
>
>For the same reason, any other transaction could end up with a snapshot too
>old error. Export does not generate any rollback, but there could be users
>performing DML operations on the table  that is being exported, and the
>export needs to be redirected to read from the rollback segments. The
>likelihood of the error being thrown up especially if one uses the
>consistent parameter could be very high, if you dont have a large enough
>rollback segment without an optimal clause.
>
>Raj
>
>
>
>
>
>Jeremiah Wilton <[EMAIL PROTECTED]>@fatcity.com on 01/23/2002 11:20:40
>AM
>
>Please respond to [EMAIL PROTECTED]
>
>
>
>Sent by:  [EMAIL PROTECTED]
>
>
>To:   Multiple recipien
>ts of list ORACLE-L <[EMAIL PROTECTED]>
>cc:
>
>
>On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote:
>
>
>
>H ... "COULD" help in avoiding snapshot too old errors.
>
>
>
>Sorry to press the point, but could you elaborate on how that "COULD"
>possibly make any difference for 'snapshot too old'?
>
>--
>Jeremiah Wilton
>http://www.speakeasy.net/~jwilton
>
>
>
>Jeremiah Wilton <[EMAIL PROTECTED]> wrote:
>
>So what does it accomplish to "assign export [to] a particular
>rollback segment?"
>
>Export doesn't generate any rollback, right, so what is it supposed to
>accomplish by doing this incantation?
>
>On Tue, 22 Jan 2002, Jason Rowski wrote:
>
>
>
>... you can use the following trick to assign export a
>particular rollback segment -
>
>1) Create a rollback segment tablespace with one large
>segment and bring it online before export.
>2) Offline all existing rollback segments.
>3) Export the database
>4) Offline the large tablespace created earlier.
>5) Bring back the orginals rollback segments online.
>
>
>--- Seema Singh <[EMAIL PROTECTED]> wrote:
>
>
>Can I use one rollback segment at time of export?Is
>yes,then
>SET TRANSACTION USE ROLLBACK SEGMENT
>rollbacksegmentname;
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Jeremiah Wilton
>INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>
>
>
>
>
>
>
>
>
>


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

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

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

Re: * Oracle DBAs Needed in Baton Rouge, Louisana

2002-01-25 Thread Paul Baumgartel

So, the client will only relocate people who have already decided to
move to Louisiana?  

--- OraStaff <[EMAIL PROTECTED]> wrote:
> We have a client requirement for several Oracle DBAs in Baton Rouge,
> Louisana.
> 
> *This company will provide relocation assistance, as long as there is
> a viable
> reason besides money for the candidate's desire to move there.

__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

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

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

RE: How to use a package variable in pkg1 inside of package pkg2

2002-01-25 Thread Mercadante, Thomas F


I'm not sure why you want to do this.  Why not have the package that you
call return the value back to the calling package.  I would not guarantee
that the value you expect to be stored in the variable would exist when you
think it will be there.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, January 25, 2002 10:31 AM
To: Multiple recipients of list ORACLE-L
pkg2



You have to declare this variable in package1 specification

create or replace package pkg1 is
end;
/

create or replace package pkg2 is
  procedure showvar;
end;
/

create or replace package pkg2 body is
  procedure showvar
  begin
dbms_output.put_line(pkg1.v_var);
  end;
end;
/

exec pkg2.showvar
in SQLPlus prompt should do that

Gints Plivna
IT Sistçmas, Meríeïa 13, LV1050 Rîga
http://www.itsystems.lv/gints/



 

Rick_Cale@team

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

[EMAIL PROTECTED]   Subject: How to use a package
variable in pkg1 inside of package pkg2  
om

 

 

2002.01.25

16:35

Please respond

to ORACLE-L

 

 





Hi All,

I have a package pkg1 that has a variable var 1 declared

Inside of pkg2 I want to use pkg1.var1. I always get PLS201 identifier
pkg1.var1 must be declared.
What do I need to do to correct.

Pkg1 compiles fine.

Thanks
Rick


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

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

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




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

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

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

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

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

Re: export the schema exclude two tables?

2002-01-25 Thread CC Harvest


Claim,Jared,Peter,Rajesh:
   Thanks for the reply. I just try a different
method.
exp the two tables to a dump, truncate the tables, exp
the rest to another dump. imp the two tables back to
the database, ship the secpdn dump to the customer.
The customer does not like that two tables, because
there are two big and the data is useless.

   Thanks all.

Chris

--- [EMAIL PROTECTED] wrote:
> 
> 
> I tried a different way once - with the PL/SQL
> extensions to export
> package. It isn't described in the docs, but there
> is a file (dbmsexp.sql)
> in rdbms/admin directory. Basically for each table
> that needs special
> treatment, you insert a row in sys.expact$ and
> identify the PL/SQL code you
> want to call before or after the table is exported.
> I had PL/SQL functions that renamed the tables
> (rename emp to a_emp). It
> sort of worked; exp renamed the tables before
> copying the rows and then
> raised an ORA-942 error because the table wasn't
> found and then continued
> with the next table in the schema. One problem was
> that the exp dmp file
> still contained the create table statement for the
> excluded tables. The
> solution to that was to pre-create dummy tables on
> the target system with
> the same names...
> It was a while ago maybe in Oracle7.3. Right now I
> can't remember why I did
> it. Is there a limit on the number of tables you can
> list for the exp
> tables parameter? Maybe the issue was to speed up
> the exp or limit the size
> of the exp dump file?
> 
> With a complete export, you can still create dummy
> tables on the target
> with the same names as the tables you want to
> exclude and run imp with
> ignore=n. This at least excludes certain tables from
> the imp (if not from
> the exp).
> 
> Chaim
> 
> 
> 
> 
> 
> 
> 
> [EMAIL PROTECTED]@fatcity.com on 01/24/2002
> 06:50:52 PM
> 
> Please respond to [EMAIL PROTECTED]
> 
> Sent by:[EMAIL PROTECTED]
> 
> 
> To:Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> 
> 
> 
> 
> Could we do it in 7.3, 8.0? ;-) Open the catexp.sql
> and modify some table
> creation scripts, possibly some exutab tables to say
> obj$.name !=  Table2>.
> 
> Hic !! Nooo. I did not say that ;-)
> 
> What I say is, include all the tablenames except the
> two that you do not
> need in your parfile.
> 
> Regards
> Raj
> 
> 
> 
> 
> 
> CC Harvest <[EMAIL PROTECTED]>@fatcity.com on
> 01/24/2002 04:55:22 PM
> 
> Please respond to [EMAIL PROTECTED]
> 
> 
> 
> Sent by:  [EMAIL PROTECTED]
> 
> 
> To:   Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> 
> 
> Anyone knows how to do it in Oracle8.1.7?
> 
> __
> Do You Yahoo!?
> Great stuff seeking new owners in Yahoo! Auctions!
> http://auctions.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: CC Harvest
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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

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

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


__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-

Re: ORA-01555 Mystery (Help)



Is it a simple select statement, or is it a cursor select in an PL/SQL
block? Does her transaction itself perform any DML on those tables?

Raj




Walter K <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002 11:15:26 AM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


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


Hi,

A user in our data warehousing group is running into
the old ORA-01555 (snapshot too old) error every time
she runs a massive (20 million rows) select against
one table via a view. I confirmed that the view only
translates to the one table.

The user swears that no one would be making any
updates/deletes to the table she is selecting from. I
suggested she lock the table in exclusive mode, prior
to running her massive select to guarantee no one else
could change the data in the table and cause the
triggering of the 1555 error. Locking the table was a
viable option because it's a staging table in the
warehouse itself. She locked the table in exclusive
mode last night and it locked; fired off her query,
and it failed 5 hours later with the 1555 error again.

I'm stumped on this. I just don't see how this is
possible. Any suggestions?

Thanks!!!
-w

__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
  INET: [EMAIL PROTECTED]

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

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



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

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

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

RE: Oracle DBAs Needed in Boston

2002-01-25 Thread Boivin, Patrice J


I am picky perhaps, but why is there an apostrophe between "its" and "I.T.
staff"?

This is like store fronts that don't put apostrophes anywhere.

: )

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

-Original Message-
Sent:   Friday, January 25, 2002 12:40 PM
To: Multiple recipients of list ORACLE-L
Subject:Oracle DBAs Needed in Boston

This well known and highly respected client in Boston, Mass. needs an
Oracle DBA to join its' I.T.staff.
A great opportunity for the right candidate with ALL the required skills
listed below.

* Duties and Responsibilities
Work with technical and application analysts to integrate project
requirements into 
efficient Oracle database designs/modifications in primarily 2-tier
configurations. 
Work with senior staff and management in database strategy decisions in a
complex application 
environment. 

* Requirements:
-Candidates must have 5-7 years experience as an Oracle DBA
-Must have managed Oracle in a production environment. 
-A minimum of one year work experience with Oracle 8i. 
-Proficient in use of Oracle advanced features such as MTS, Replication, and
Partitioning. 
-Extensive experience with performance tuning and troubleshooting, at the
OS, database, 
 and application levels. 
-Candidate must be comfortable with a range of tools to support monitoring
and tuning activities.
-Develop clear well-written documentation. 
-Proficient in developing a range of documentation from high level approach
to detailed 
 specifications. 
-Knowledge of Veritas, OEM, and Oracle 9I a plus. 

U.S. citizenship or permanent residency is also required.

This position offers:
* Opportunity to become a key member of the team
* Base Salary -in the 85K range D.O.E.
* Relocation Assistance

NO sub contracting positions available.
*U.S. citizenship only
PLEASE do not send your resume if you are not in the United States.

For  immediate consideration, please send your resume as an attachment to:
OraStaff, Inc.
Email: [EMAIL PROTECTED]
Please use job code: One/Boston/DBA/DF

We pay referral fees.
So please contact me if you know of anyone who would be qualified/interested
in the
posiition described above- if it is not a match for your skills.
Thanks,
OraStaff





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

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

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

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

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

RE: Yet again more Recovery Manager questions

2002-01-25 Thread Mercadante, Thomas F


Jack,

Generally, you can backup any database to a higher-level Rman repository by
using the Rman version of the database you are backing up.

So, in your case, use the 8.0.5 version of Rman, connecting to the 8.1.7
repository.

Optionally, you could create an Rman 805 catalog in an additional 805
database someplace - even on the same box as the 817 catalog.

I know there is a matrix someplace in Oracle land that shows what version of
Rman work in what version of the Rman catalog.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, January 25, 2002 10:36 AM
To: Multiple recipients of list ORACLE-L




Hi All,


I have a mixed environment 8.0.5 & 8.1.7 databases. I can not use the same
catalog for both versions. I tried creating a new catalog for my 8.0.5
databases (different owner) in the same 8.1.7. database that my other
catalog is in using the rman 805 executable. This also results in error
messages (about packages/procedures).

My Q: Do I really need a database matching the version of RMAN I'm using
and/or is there a way to use the 8.1.7 rman executable for 8.0.5 databases?
 (documentation tends to go that direction)


TIA

Jack

===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
===




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

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

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

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

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

RE: Oracle DBAs Needed in Boston

2002-01-25 Thread Mercadante, Thomas F


You're right - you're picky..

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Friday, January 25, 2002 11:55 AM
To: Multiple recipients of list ORACLE-L


I am picky perhaps, but why is there an apostrophe between "its" and "I.T.
staff"?

This is like store fronts that don't put apostrophes anywhere.

: )

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

-Original Message-
Sent:   Friday, January 25, 2002 12:40 PM
To: Multiple recipients of list ORACLE-L
Subject:Oracle DBAs Needed in Boston

This well known and highly respected client in Boston, Mass. needs an
Oracle DBA to join its' I.T.staff.
A great opportunity for the right candidate with ALL the required skills
listed below.

* Duties and Responsibilities
Work with technical and application analysts to integrate project
requirements into 
efficient Oracle database designs/modifications in primarily 2-tier
configurations. 
Work with senior staff and management in database strategy decisions in a
complex application 
environment. 

* Requirements:
-Candidates must have 5-7 years experience as an Oracle DBA
-Must have managed Oracle in a production environment. 
-A minimum of one year work experience with Oracle 8i. 
-Proficient in use of Oracle advanced features such as MTS, Replication, and
Partitioning. 
-Extensive experience with performance tuning and troubleshooting, at the
OS, database, 
 and application levels. 
-Candidate must be comfortable with a range of tools to support monitoring
and tuning activities.
-Develop clear well-written documentation. 
-Proficient in developing a range of documentation from high level approach
to detailed 
 specifications. 
-Knowledge of Veritas, OEM, and Oracle 9I a plus. 

U.S. citizenship or permanent residency is also required.

This position offers:
* Opportunity to become a key member of the team
* Base Salary -in the 85K range D.O.E.
* Relocation Assistance

NO sub contracting positions available.
*U.S. citizenship only
PLEASE do not send your resume if you are not in the United States.

For  immediate consideration, please send your resume as an attachment to:
OraStaff, Inc.
Email: [EMAIL PROTECTED]
Please use job code: One/Boston/DBA/DF

We pay referral fees.
So please contact me if you know of anyone who would be qualified/interested
in the
posiition described above- if it is not a match for your skills.
Thanks,
OraStaff





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

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

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

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

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

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

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

RE: ORA-01555 Mystery (Help)



I have a batch job that does this consistently.  It's the only job in the
database; it sets the transaction to a hugh rollback segment.  And it eats
its own tail.

Depending on how the job is written, it may need a read consistent view
itself (as opposed to some other query in the database needing that read
consistent view.)In that case, it may well go try to read its own
rollback segment, only to find that it's been overwritten.  (Oddly enough,
even when there's plenty of space to extend the rollback, Oracle will decide
to overwrite the original rollback segments rather than extend if it thinks
it doesn't need those segments any more.)

I'd strongly suggest you get the stuff from Steve Adams' ixora site that
places an uncommitted transaction in your rollback segments for the length
of the run.This will guarantee that the rollback segments don't get
overwritten.
Good luck!

Barb

> --
> From: Walter K[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, January 25, 2002 9:15 AM
> To:   Multiple recipients of list ORACLE-L
> Subject:  ORA-01555 Mystery (Help)
> 
> Hi,
> 
> A user in our data warehousing group is running into
> the old ORA-01555 (snapshot too old) error every time
> she runs a massive (20 million rows) select against
> one table via a view. I confirmed that the view only
> translates to the one table.
> 
> The user swears that no one would be making any
> updates/deletes to the table she is selecting from. I
> suggested she lock the table in exclusive mode, prior
> to running her massive select to guarantee no one else
> could change the data in the table and cause the
> triggering of the 1555 error. Locking the table was a
> viable option because it's a staging table in the
> warehouse itself. She locked the table in exclusive
> mode last night and it locked; fired off her query,
> and it failed 5 hours later with the 1555 error again.
> 
> I'm stumped on this. I just don't see how this is
> possible. Any suggestions?
> 
> Thanks!!!
> -w
> 
> __
> Do You Yahoo!?
> Great stuff seeking new owners in Yahoo! Auctions! 
> http://auctions.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Walter K
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Baker, Barbara
  INET: [EMAIL PROTECTED]

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

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

Add mod_ldap to 9iAS Apache

2002-01-25 Thread James Howerton


DBA's

Does anyone have instructions for re-compiling apache to include
mod_ldap? We need to integrate our existing iplanet ldap into 9iAS for
reports and portal. Metalink states re-compiling apache is not supported
and the only manuals I've found so far only address OID.

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

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

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

RE: Hmmmmm

2002-01-25 Thread Grabowy, Chris


Yes, this sounds like you need to use a sequence.  And you should seriously
consider using a sequence, since you might continue to run into
locking/performance issues trying to use a table.

Chris 

-Original Message-
Sent: Friday, January 25, 2002 10:00 AM
To: Multiple recipients of list ORACLE-L


There are more rows in that table.

It's one that we use for storing sequential numbers we use... like invoice
numbers, membership numbers etcetc

So we have to lock row when using it, so noone else uses the same number
when we add a new member or
create a new invoice...

But good advice tho, and I would have used lock on it if not for the fact
that we have to use row locking
on it...

/Stefan

> -Ursprungligt meddelande-
> Från: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
> Skickat: den 25 januari 2002 15:00
> Till: Multiple recipients of list ORACLE-L
> Ämne: Re: Hm
> 
> 
> is there only one row in that table?  if so, you can lock the table
> itself. 
> 
> try it without the nowait...
> 
> 
> 
> --- Stefan Jakobsson <[EMAIL PROTECTED]> wrote:
> > Having a small problem I need some help with...
> > 
> > The problem is trying to lock a row on a parameter table for an
> > application.
> > 
> > The thing we need to do is following:
> > 
> > Read the value in one row. LOCK that row for other users, increase
> > the value
> > with one
> > update the row with the new value and release if for access to other
> > users.
> > 
> > We are trying to use the following SQL statement.
> > 
> > SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT;
> > 
> > But the thing is that when we try, anyone can head on in and select
> > the same
> > value
> > and even make an update of that row...
> > 
> > What am I missing here?
> > 
> > Regards,
> > Stefan Jakobsson
> > Programmer
> > Arel-Data 
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > -- 
> > Author: Stefan Jakobsson
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> > San Diego, California-- Public Internet access / Mailing
> > Lists
> > 
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
> 
> 
> __
> Do You Yahoo!?
> Great stuff seeking new owners in Yahoo! Auctions! 
> http://auctions.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stefan Jakobsson
  INET: [EMAIL PROTECTED]

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

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

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

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

RE: ORA-01555 Mystery (Help)

2002-01-25 Thread Gogala, Mladen


Don't believe your users. Somebody is causing
oracle to read blocks in consistent mode, ie. reading them
from rollback segments. If the user is right, then try
locking the participating tables in the exclusive mode and see
who will complain. Alternatively, go to V$ACCESS table, see who is
accessing the table in question, and see who has a transaction lock
(v$lock, id1=object id).

-Original Message-
Sent: Friday, January 25, 2002 11:15 AM
To: Multiple recipients of list ORACLE-L


Hi,

A user in our data warehousing group is running into
the old ORA-01555 (snapshot too old) error every time
she runs a massive (20 million rows) select against
one table via a view. I confirmed that the view only
translates to the one table.

The user swears that no one would be making any
updates/deletes to the table she is selecting from. I
suggested she lock the table in exclusive mode, prior
to running her massive select to guarantee no one else
could change the data in the table and cause the
triggering of the 1555 error. Locking the table was a
viable option because it's a staging table in the
warehouse itself. She locked the table in exclusive
mode last night and it locked; fired off her query,
and it failed 5 hours later with the 1555 error again.

I'm stumped on this. I just don't see how this is
possible. Any suggestions?

Thanks!!!
-w

__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walter K
  INET: [EMAIL PROTECTED]

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

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

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

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

Re: ORA-01555 Mystery (Help)

2002-01-25 Thread Charlie Mengler


In Tom Kyte's book Expert 1-on-1
he says to ANALYZE the table BEFORE starting a big query.
Read Chapter 5, starting on page 185 for a complete explanation.

Walter K wrote:
> 
> Hi,
> 
> A user in our data warehousing group is running into
> the old ORA-01555 (snapshot too old) error every time
> she runs a massive (20 million rows) select against
> one table via a view. I confirmed that the view only
> translates to the one table.
> 
> The user swears that no one would be making any
> updates/deletes to the table she is selecting from. I
> suggested she lock the table in exclusive mode, prior
> to running her massive select to guarantee no one else
> could change the data in the table and cause the
> triggering of the 1555 error. Locking the table was a
> viable option because it's a staging table in the
> warehouse itself. She locked the table in exclusive
> mode last night and it locked; fired off her query,
> and it failed 5 hours later with the 1555 error again.
> 
> I'm stumped on this. I just don't see how this is
> possible. Any suggestions?
> 
> Thanks!!!
> -w
> 
> __
> Do You Yahoo!?
> Great stuff seeking new owners in Yahoo! Auctions!
> http://auctions.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Walter K
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

-- 
Charlie Mengler   Maintenance Warehouse  
[EMAIL PROTECTED]  10641 Scripps Summit Ct.
858-831-2229  San Diego, CA 92131
Our customers are part of our team. They're the test department!
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charlie Mengler
  INET: [EMAIL PROTECTED]

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

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

Re: Legato backup.

2002-01-25 Thread Joe Raube


RMAN will interface nicely with Legato.

-Joe

--- Jenner Mike <[EMAIL PROTECTED]> wrote:
> Hey, just to confirm what I think is obvious...
> For an oracle database to be backed up on NT with Legato: you need
> to
> either;
>  shutdown, cold backup via legato, startup, or
>  online backup mode and legato's "Oracle database" extension,
>  [or some sort of strategy involving oracle export].
> 
> And to verify... legato's "backup an in-use file" extension used to
> backup
> oracle database datafiles that have not been shutdown...   will not
> give a
> viable backup ??? 
> A department that has not paid for my section's DBA services has
> just phoned
> up with restore problems with a database backed up in this manner!
> 
> If someone could confirm the obvious, I would be grateful.
> 
> Regards,
> Mike.
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Jenner Mike
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
>

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


__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Raube
  INET: [EMAIL PROTECTED]

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

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

Oracle DBAs Needed in Boston

2002-01-25 Thread OraStaff


This well known and highly respected client in Boston, Mass. needs an
Oracle DBA to join its' I.T.staff.
A great opportunity for the right candidate with ALL the required skills
listed below.

* Duties and Responsibilities
Work with technical and application analysts to integrate project
requirements into 
efficient Oracle database designs/modifications in primarily 2-tier
configurations. 
Work with senior staff and management in database strategy decisions in a
complex application 
environment. 

* Requirements:
-Candidates must have 5-7 years experience as an Oracle DBA
-Must have managed Oracle in a production environment. 
-A minimum of one year work experience with Oracle 8i. 
-Proficient in use of Oracle advanced features such as MTS, Replication, and
Partitioning. 
-Extensive experience with performance tuning and troubleshooting, at the
OS, database, 
 and application levels. 
-Candidate must be comfortable with a range of tools to support monitoring
and tuning activities.
-Develop clear well-written documentation. 
-Proficient in developing a range of documentation from high level approach
to detailed 
 specifications. 
-Knowledge of Veritas, OEM, and Oracle 9I a plus. 

U.S. citizenship or permanent residency is also required.

This position offers:
* Opportunity to become a key member of the team
* Base Salary -in the 85K range D.O.E.
* Relocation Assistance

NO sub contracting positions available.
*U.S. citizenship only
PLEASE do not send your resume if you are not in the United States.

For  immediate consideration, please send your resume as an attachment to:
OraStaff, Inc.
Email: [EMAIL PROTECTED]
Please use job code: One/Boston/DBA/DF

We pay referral fees.
So please contact me if you know of anyone who would be qualified/interested
in the
posiition described above- if it is not a match for your skills.
Thanks,
OraStaff





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

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

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

Re: ROLLBACK SEGMENT?



John,

I DISAGREE. The gymnastics of assigning a large rollback segment to an
export could avoid the snapshot too old error.

I agree with Jeremy when he says export does not generate rollback. But I
was trying to impress upon him that still an export could end up with the
snapshot too old message, particularly if there are plenty of active DML
transactions happening while the export is in progress, or if the export
uses the consistent parameter, or the rollback segments are not properly
sized.

To stress my point further, Note:22836.1 on Metalink. I rest my case ;-)

Raj





orantdba <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002 08:20:25 AM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


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


Hi Raj,

Interesting that you first agree with Jeremy and then argue with him.  It
is precisely because
export does not generate rollback that the "gymnastics" of taking all of
the other rbs's offline
will not help anything.  It might make you feel better, however :-).

John

[EMAIL PROTECTED] wrote:




Export doesn't generate any rollback, right, so what is it supposed to
accomplish by doing this incantation?





Sorry to press the point, but could you elaborate on how that "COULD"
possibly make any difference for 'snapshot too old'?



For the same reason, any other transaction could end up with a snapshot too
old error. Export does not generate any rollback, but there could be users
performing DML operations on the table  that is being exported, and the
export needs to be redirected to read from the rollback segments. The
likelihood of the error being thrown up especially if one uses the
consistent parameter could be very high, if you dont have a large enough
rollback segment without an optimal clause.

Raj





Jeremiah Wilton <[EMAIL PROTECTED]>@fatcity.com on 01/23/2002 11:20:40
AM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


To:   Multiple recipien
ts of list ORACLE-L <[EMAIL PROTECTED]>
cc:


On Tue, 22 Jan 2002, [EMAIL PROTECTED] wrote:



H ... "COULD" help in avoiding snapshot too old errors.



Sorry to press the point, but could you elaborate on how that "COULD"
possibly make any difference for 'snapshot too old'?

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton



Jeremiah Wilton <[EMAIL PROTECTED]> wrote:

So what does it accomplish to "assign export [to] a particular
rollback segment?"

Export doesn't generate any rollback, right, so what is it supposed to
accomplish by doing this incantation?

On Tue, 22 Jan 2002, Jason Rowski wrote:



... you can use the following trick to assign export a
particular rollback segment -

1) Create a rollback segment tablespace with one large
segment and bring it online before export.
2) Offline all existing rollback segments.
3) Export the database
4) Offline the large tablespace created earlier.
5) Bring back the orginals rollback segments online.


--- Seema Singh <[EMAIL PROTECTED]> wrote:


Can I use one rollback segment at time of export?Is
yes,then
SET TRANSACTION USE ROLLBACK SEGMENT
rollbacksegmentname;



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

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

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










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

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

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

Re: Backup Strategy


Ok, I guess, I owe some explanation here, since I've got a lot of spanking
(replies, some rather sarcastic) regarding this issue.
My original note comes from my "real life" experience, so I'm still standing
behind it.
Sorry, it'll be kind of long, but if you are really interested...

Couple years ago, when we were preparing first release of our product, I
read of course about "NT Copy" versus "Oracle Ocopy".
Still I decided to test it, because not always I trust what I read, and I
like to get proof myself.
Testing of online ("hot") backup/recovery scenario showed, that using "NT
Copy" command in backup scripts is perfectly fine, when creating backup set
of files on the disk.  And there is no problem restoring from this backup.
Now this "disk backup" set of files could be saved on tape, using NTBACKUP
(that's the one, that really can not copy file, if it's opened by some other
program.  But that's not the case with prepared in advance "disk backup").
"NT Copy" has no problems copying files opened already by Oracle, and backup
is consistent, as long of course as I am using "alter tablespace 
begin backup" before copying relevant files and "alter tablespace  end
backup" after finishing files copy.
So, those scripts (using "NT Copy") were put into production, and now have
been used for more than two years on more than hundred installations/sites
(the number keeps growing).
>From time to time, our field engineers are bringing back to me sets of
online (can not use "cold" backup - our systems should run 24*7, I'm not
saying they are, but we are trying to minimize downtime) backed up files (db
files and archived RedoLog files), and I recover them with no problem (we
need this, to test how the upgrade to next release of our product will run
against "real" customers data).

Now, about MetaLink Note:139327.1
It says:

Ocopy  opens the file using CreateFile() with the FILE_SHARE_READ and
FILE_SHARE_WRITE  flags. This allows writing to continue while we take the
backup.  Inconsistencies in the backup are repaired by applying archived
redo  during  recovery. The 'copy' command from NT doesn't use these flags
since it  wants to  prevent writes to the file while the copy is taking
place.


I don't think, it's very accurate, and here is why:
When during online backup I run "NT copy" against db file, the file is
already opened by Oracle (at moment, when I "open" the database).
So, even if "NT copy" opens file without FILE_SHARE_READ and
FILE_SHARE_WRITE  flags, all it means is that "Subsequent open operations on
the object will fail" (quote from NT docs).  I want you to notice, it says
"Subsequent open operations" not "Subsequent write/read operations".  So,
all it does is prohibiting some other program/process from "opening" the
file.  But Oracle, as I mentioned, has this file already opened, and it is
perfectly capable of reading/writing this file.
Of course, the image of the saved file will be "fuzzy", and that's why when
recovering from online backup we are applying archived RedoLog files (which
getting written much more intensely during online backup).

As for Peter McLarty note, that  he "never knew that NT copy could manage
keeping the CSN number in sync",
well it ("NT Copy") does not have to (neither does "Oracle Ocopy") keep CSN
number in sync.
Oracle updates file header with checkpoint SCN, when we issue "alter
tablespace  begin backup".  Then until "alter tablespace  end
backup", file header will cease updating.  And SCN, written in the beginning
provides the info, which archived RedoLog files should be used for recovery.

Now, please correct me, if I'm wrong.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]


- Original Message -
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, January 24, 2002 6:47 PM


> > Wrong. NT 'COPY' has no problems copying 'opened' oracle db files.
> > I'm using it in 'hot backup' scripts on many dozens systems, and it
> works
> > fine.
>
> Igor, you sure about that?
>
> There are backup packages (such as Backup Exec ) that use their
> own file open copy program to avoid using copy.exe.
>
> Have you restored any of these backups made with copy?
>
> Jared
>
> Note:139327.1
> Subject:
>  Differences between Windows NT COPY and Oracle
>  OCOPY When Doing Backups
>
> Creation Date:03-APR-2001
> Last Revision Date:   04-DEC-2001
>
>  PURPOSE
>A comparison of the differences between the Windows NT "copy" commnad,
> and
>the Oracle "ocopy" command.  Which should be used during an online
> backup?
>
>  SCOPE & APPLICATION
>DBAs with databases on the Windows NT platform.
>
>  The Differences between Windows NT COPY and Oracle OCOPY When Doing
> Backups:
>
>

>
>  When doing an online backup, should you use the Windows NT COPY command,
> or the
>  Oracle OCOPY command?
>
>  While doing online backups you should use OCOPY, or Oracle7 EBU, or
> Oracle8
>  (and later) RMAN.  Wi

Re: export the schema exclude two tables?

2002-01-25 Thread Chaim . Katz




I tried a different way once - with the PL/SQL extensions to export
package. It isn't described in the docs, but there is a file (dbmsexp.sql)
in rdbms/admin directory. Basically for each table that needs special
treatment, you insert a row in sys.expact$ and identify the PL/SQL code you
want to call before or after the table is exported.
I had PL/SQL functions that renamed the tables (rename emp to a_emp). It
sort of worked; exp renamed the tables before copying the rows and then
raised an ORA-942 error because the table wasn't found and then continued
with the next table in the schema. One problem was that the exp dmp file
still contained the create table statement for the excluded tables. The
solution to that was to pre-create dummy tables on the target system with
the same names...
It was a while ago maybe in Oracle7.3. Right now I can't remember why I did
it. Is there a limit on the number of tables you can list for the exp
tables parameter? Maybe the issue was to speed up the exp or limit the size
of the exp dump file?

With a complete export, you can still create dummy tables on the target
with the same names as the tables you want to exclude and run imp with
ignore=n. This at least excludes certain tables from the imp (if not from
the exp).

Chaim







[EMAIL PROTECTED]@fatcity.com on 01/24/2002 06:50:52 PM

Please respond to [EMAIL PROTECTED]

Sent by:[EMAIL PROTECTED]


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




Could we do it in 7.3, 8.0? ;-) Open the catexp.sql and modify some table
creation scripts, possibly some exutab tables to say obj$.name != .

Hic !! Nooo. I did not say that ;-)

What I say is, include all the tablenames except the two that you do not
need in your parfile.

Regards
Raj





CC Harvest <[EMAIL PROTECTED]>@fatcity.com on 01/24/2002 04:55:22 PM

Please respond to [EMAIL PROTECTED]



Sent by:  [EMAIL PROTECTED]


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


Anyone knows how to do it in Oracle8.1.7?

__
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions!
http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: CC Harvest
  INET: [EMAIL PROTECTED]

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

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



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

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

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



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

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

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

RE: Hmmmmm

2002-01-25 Thread Aponte, Tony

Title: RE: Hm






What you are observing is the concurrency mechanism as implemented in the Oracle rdbms.  I couldn't explain it better in an email so I suggest you get a hold of Expert One-on-one Oracle by Thomas Kyte, chapter 3 on Locking and Concurrency.  Another thought I have is that it looks like you are trying to implement a sequence number.  If the application can tolerate gaps in the numbers then you can use an rdbms-managed sequence number and use NEXTVAL in place of the SELECT FOR UPDATE.

HTH.

Tony Aponte


-Original Message-

From: Stefan Jakobsson [mailto:[EMAIL PROTECTED]]

Sent: Friday, January 25, 2002 5:35 AM

To: Multiple recipients of list ORACLE-L

Subject: Hm



Having a small problem I need some help with...


The problem is trying to lock a row on a parameter table for an application.


The thing we need to do is following:


Read the value in one row. LOCK that row for other users, increase the value

with one

update the row with the new value and release if for access to other users.


We are trying to use the following SQL statement.


SELECT medlnr FROM parametrar FOR UPDATE OF medlnr NOWAIT;


But the thing is that when we try, anyone can head on in and select the same

value

and even make an update of that row...


What am I missing here?


Regards,

Stefan Jakobsson

Programmer

Arel-Data 

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: Stefan Jakobsson

  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California    -- Public Internet access / Mailing Lists



To REMOVE yourself from this mailing list, send an E-Mail message

to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

ORA-01555 Mystery (Help)