date:20020514


Simon - My apologies. Tim's response was for the other problem that is
getting a lot of responses. In trying to read the postings as well as get
some work done here, I confused the two.
I was thinking that the response said that the loader first loads
into the TEMPORARY segment, then merged with the table segment. I confused
the two since they both involved "temp".
I don't have a specific solution to your problem, but my survival
instincts as a crusty old DBA (but still employed) say "don't make your TEMP
tablespace autoextend". It just feels like you are trolling for trouble. I
am a big fan of autoextend and have it on all my datafiles except for a
couple, and guess what -- TEMP is not on autoextend for me. Maybe once you
lick this one, you can give talks worldwide on either a) why you should
never let TEMP autoextend or b) why you're missing the best part of Oracle
by not letting your TEMP autoextend. I promise I'll attend your talk.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Tuesday, May 14, 2002 4:13 PM
To: Multiple recipients of list ORACLE-L


Dennis,
I haven't seen Tim's posting !!? as regards my woos

-Original Message-
Sent: Tuesday, May 14, 2002 10:13 PM
To: Multiple recipients of list ORACLE-L


Simon - I think Tim has given you a good answer to your TEMP tablespace
growth. I just wanted to warn you to be cautious about extremely large sizes
of SORT_AREA_SIZE. This is a per-process setting, which means that if you
have several processes, each process may acquire a lot of memory and the
overall system memory may be overallocated. I speak from experience, bad
experience.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Tuesday, May 14, 2002 1:14 PM
To: Multiple recipients of list ORACLE-L


Forgot to tell you that myparameter for sort_area_size= apx. 8GB

-Original Message-
Sent: Tuesday, May 14, 2002 8:02 PM
To: '[EMAIL PROTECTED]'


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up
disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but
can't
get further.
Any help will be appreciated.

---
CSW
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Simon Waibale
  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: DENNIS WILLIAMS
  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: Simon Waibale
  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: DENNIS WILLIAMS
  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

RE: Explain THIS plan. SOLVED

2002-05-14 Thread Kirsch, Walter J (Northrop Grumman)



select /*+ rule */ ...

cut the execution time to nil.

Thanks to Charlie Mengler for help and Tim Gorman for a note in his "i.sql"
that solved the problem.

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 14, 2002 3:09 PM
To: Multiple recipients of list ORACLE-L



2-cpu, 220mhz, 32-bit HPUX 11.0.  Oracle 8.1.7.0.0

Could someone explain what's going on here?

This SQL takes no time at all :

select
  substr(username , 1, 12)  "User"
, substr(lock_type, 1, 18)  "Lock Type"
, substr(mode_held, 1, 18)  "Mode Held"
  from sys.dba_lock a
 , v$sessionb
 where /*lock_type not in ('Media Recovery','Redo Thread')
   and*/ a.session_id = b.sid;

with explain plan:

SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=1 Bytes=148)
  NESTED LOOPS (Cost=70 Card=1 Bytes=148)
NESTED LOOPS (Cost=60 Card=1 Bytes=141)
  NESTED LOOPS (Cost=20 Card=1 Bytes=107)
FIXED TABLE (FULL) OF X$KSUSE (Cost=10 Card=1 Bytes=30)
FIXED TABLE (FIXED INDEX #1) OF X$KSUSE (Cost=10 Card=1
Bytes=77)
  VIEW OF GV$_LOCK
UNION-ALL
  VIEW OF GV$_LOCK1 (Cost=20 Card=2 Bytes=162)
UNION-ALL
  FIXED TABLE (FULL) OF X$KDNSSF (Cost=10 Card=1
Bytes=94)
  FIXED TABLE (FULL) OF X$KSQEQ (Cost=10 Card=1
Bytes=94)
  FIXED TABLE (FULL) OF X$KTADM (Cost=10 Card=1 Bytes=94)
  FIXED TABLE (FULL) OF X$KTCXB (Cost=10 Card=1 Bytes=94)
FIXED TABLE (FIXED INDEX #1) OF X$KSQRS (Cost=10 Card=100
Bytes=700)

whereas an uncommented predicate consumes 40 minutes of CPU (sez TOP) with
this explain plan:

SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=1 Bytes=148)
  NESTED LOOPS (Cost=70 Card=1 Bytes=148)
NESTED LOOPS (Cost=30 Card=1 Bytes=114)
  MERGE JOIN (CARTESIAN) (Cost=20 Card=1 Bytes=37)
FIXED TABLE (FULL) OF X$KSUSE (Cost=10 Card=1 Bytes=30)
SORT (JOIN) (Cost=10 Card=1 Bytes=7)
  FIXED TABLE (FULL) OF X$KSQRS (Cost=10 Card=1 Bytes=7)
  FIXED TABLE (FIXED INDEX #1) OF X$KSUSE (Cost=10 Card=1
Bytes=77)
VIEW OF GV$_LOCK
  UNION-ALL
VIEW OF GV$_LOCK1 (Cost=20 Card=2 Bytes=162)
  UNION-ALL
FIXED TABLE (FULL) OF X$KDNSSF (Cost=10 Card=1 Bytes=94)
FIXED TABLE (FULL) OF X$KSQEQ (Cost=10 Card=1 Bytes=94)
FIXED TABLE (FULL) OF X$KTADM (Cost=10 Card=1 Bytes=94)
FIXED TABLE (FULL) OF X$KTCXB (Cost=10 Card=1 Bytes=94)

If it jams, force it. If it breaks, it needed replacing anyway. --John F
Duval

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

Re: list of events

2002-05-14 Thread Suzy Vordos



$ORACLE_HOME/rdbms/mesg/oraus.msg does list events, for example:

10046, 0, "enable SQL statement timing"
// *Cause:
// *Action:

Most events are in the range of 1 to 10999.

Suzy


[EMAIL PROTECTED] wrote:
> 
> actually that's the list of error messages, not events
> 
> and it's not under the admin directory in any case the path to the error
> messages is:
> 
> $ORACLE_HOME/rdbms/mesg/roaus.msg
> 
> |+--->
> ||   |
> ||   |
> ||  Rajendra.Jamadagn|
> ||  [EMAIL PROTECTED]   |
> ||   |
> ||  05/14/2002 04:23 |
> ||  PM   |
> ||  Please respond to|
> ||  ORACLE-L |
> ||   |
> |+--->
>   >|
>   ||
>   |   To: [EMAIL PROTECTED] |
>   |   cc: (bcc: Rachel Carmichael) |
>   |   Subject: RE: list of events  |
>   >|
> 
> $ORACLE_HOME/rdbms/admin/mesg/oraus.msg
> 
> Raj
> __
> 
> Rajendra Jamadagni  MIS, ESPN Inc.
> 
> Rajendra dot Jamadagni at ESPN dot com
> 
> Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
> 
> QOTD: Any clod can have facts, but having an opinion is an art!
> 
>  -Original Message-
>  From: Babu Nagarajan [mailto:[EMAIL PROTECTED]]
>  Sent: Tuesday, May 14, 2002 3:48 PM
>  To: Multiple recipients of list ORACLE-L
>  Subject: list of events
> 
>  All
> 
>  I know this has been mentioned on this list before but I forget...
> 
>  Which is the script in $ORACLE_HOME/rdbms/admin folder that contains the
>  list of all events?
> 
>  TIA
> 
>  Babu(See attached file: ESPN_Disclaimer.txt)
> 
>   
>  Name: ESPN_Disclaimer.txt
>ESPN_Disclaimer.txt   Type: Plain Text (text/plain)
>  Encoding: base64
>   Description: Text - character set unknown
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  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: TEMP tablespace growing abnormally

2002-05-14 Thread Rodrigues, Bryan


Simon,

If auto extend is on the tablespace and there is a runaway or resource
intensive process that is using up  a great amount of temp space, Oracle
will keep expanding the size of the tablespace until you have run out of
space on the disk.

Bryan

-Original Message-
Sent: Tuesday, May 14, 2002 5:08 PM
To: Multiple recipients of list ORACLE-L


Yes,
But this shouldn't warrant 'indefinite extension' of the TB ?!?

-Original Message-
Sent: Tuesday, May 14, 2002 9:39 PM
To: Multiple recipients of list ORACLE-L


Do you have auto extend on the temp tablespace?

Bryan

-Original Message-
Sent: Tuesday, May 14, 2002 2:10 PM
To: Multiple recipients of list ORACLE-L


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up
disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but
can't
get further.
Any help will be appreciated.

---
CSW
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Simon Waibale
  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: Rodrigues, Bryan
  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: Simon Waibale
  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: Rodrigues, Bryan
  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: Zero-term'd machine in V$SESSION from Winders

2002-05-14 Thread Jay Mehta


Rich,

We do have Win clients, and V$SESSION does show proper values for machine
and terminal. Are these sessions for background processes and/or slave
processes? 

Jay

-Original Message-
Sent: Tuesday, May 14, 2002 4:23 PM
To: Multiple recipients of list ORACLE-L


So, there I am, creating a "special" kill user script (don't ask) for one of
our instances, 8.1.6.0.0 on Solaris.  The problem is that some of the output
rows were getting truncated on the output.  Here's the proc I had started:

CREATE OR REPLACE PROCEDURE Euthanize AS

v_printline VARCHAR2(140);

CURSOR c_sessions IS
SELECT vs.username, vs.osuser, 
--
--REPLACE(vs.machine,CHR(0),NULL) "MACHINE",
vs.machine,
--
, vs.logon_time, vs.last_call_et, vp.SPID 
FROM v$session vs, v$process vp
WHERE vs.username IS NOT NULL
AND vs.paddr = vp.addr
ORDER BY vs.last_call_et DESC;

BEGIN

FOR rsess IN c_sessions LOOP
v_printline := rsess.username||'|'||
rsess.osuser||'|'||rsess.machine||'|'||
rsess.logontime||'|'||rsess.idletime||'|'||
rsess.spid||'|'||rsess.logon_time;
dbms_output.put_line(v_printline);
END LOOP;

END Euthanize;

After compiling this, I called it from SQL*Plus using "execute euthanize;".

I noticed that all the DBMS_OUTPUT lines that were truncated were sessions
from Windohs workstations.  Examining a SELECT DUMP(machine) FROM V$SESSION
showed that all of the MACHINE columns from Windohs sessions were
zero-terminated.  No other client (Solaris) was.

Since the output from DBMS_OUTPUT is being prematurely truncated by this
zero-term'd field, the workaround is to either move the problem field to the
end of the output line, or to use the REPLACE function, as I've commented
out in the above code.  The latter is necessary if there is more than one
bastardized field like this or if the order of the columns in the output is
important.

Can anyone reproduce this?  The particular clients I found with the problem
are 8.1.7.

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



This electronic message contains information from CTIS, Inc., which 
may be company sensitive, proprietary, privileged or otherwise protected 
from disclosure. The information is intended to be used solely by the 
recipients named above. If you are not an intended recipient, be aware 
that any review, disclosure, copying, distribution or use of this 
transmission or its contents is prohibited.  If you have received this 
transmission in error, please notify us immediately at [EMAIL PROTECTED] 




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jay Mehta
  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: list of events

2002-05-14 Thread Jamadagni, Rajendra


Rachel,

Actually, error messages between 1 and 10999 are events (i.e. 10046,
10053 etc). In 9i there are some new events in the 297xx range as well.

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

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


-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 14, 2002 4:53 PM
To: Multiple recipients of list ORACLE-L



actually that's the list of error messages, not events

and it's not under the admin directory in any case the path to the error
messages is:

$ORACLE_HOME/rdbms/mesg/roaus.msg



*2

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

*2

no of open cursor growing

2002-05-14 Thread BigP




Hi List ,
Since last few days i m finding that number of open 
cursors are growing in our database . After examining v$open_cursor view I see 
that there are lot of  sql statements like 'select some_seq.nextval from 
dual ' .  My question is when does oracle closes these kind of open cursors 
or will it keep them open . So that it can find them if it executes same sql 
stmt next time .
Similerly there are lots of delete from ... and 
update some_table ... kind of cursors . When does these gets  released . 
Does it mean that some connections are not doing commit or rollback at then end 
leaving these cursors .
 
Thanks for help .
Bp

RE: TEMP tablespace growing abnormally -Sort Area


Dennis,
I haven't seen Tim's posting !!? as regards my woos

-Original Message-
Sent: Tuesday, May 14, 2002 10:13 PM
To: Multiple recipients of list ORACLE-L


Simon - I think Tim has given you a good answer to your TEMP tablespace
growth. I just wanted to warn you to be cautious about extremely large sizes
of SORT_AREA_SIZE. This is a per-process setting, which means that if you
have several processes, each process may acquire a lot of memory and the
overall system memory may be overallocated. I speak from experience, bad
experience.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Tuesday, May 14, 2002 1:14 PM
To: Multiple recipients of list ORACLE-L


Forgot to tell you that myparameter for sort_area_size= apx. 8GB

-Original Message-
Sent: Tuesday, May 14, 2002 8:02 PM
To: '[EMAIL PROTECTED]'


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up
disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but
can't
get further.
Any help will be appreciated.

---
CSW
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Simon Waibale
  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: DENNIS WILLIAMS
  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: Simon Waibale
  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: extents allocation in parallel load

2002-05-14 Thread Gurelei


Tim, 

SQL> select TABLESPACE_NAME, INITIAL_EXTENT/1024,
NEXT_EXTENT/1024, PCT_INCREASE
  2  from dba_tablespaces
  3  where tablespace_name like 'TEMP%'
  4  or tablespace_name = 'TREPD01';

TABLESPACE_NAMEINITIAL_EXTENT/1024
NEXT_EXTENT/1024 PCT_INCREASE
-- ---
 
TREPD01 40
  400
TEMP011024
10240

TREPD01 is where the tables are located, TTEMP01 is
the temp tablespace.

Gene
--- Tim Gorman <[EMAIL PROTECTED]> wrote:
> What does "SELECT * FROM DBA_TABLESPACES" show for
> the tablespace involved?
> 
> - Original Message - 
> To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> Sent: Tuesday, May 14, 2002 12:53 PM
> 
> 
> > Tim,
> > 
> > Thanks for the explanation. The table I'm looking
> at
> > has an initial size 32K while the new extents are
> all
> > 16K. Why would this happen?
> > 
> > thanks
> > 
> > Gene
> > --- Tim Gorman <[EMAIL PROTECTED]> wrote:
> > > SQL*Loader in direct-parallel mode (i.e.
> > > DIRECT=TRUE, PARALLEL=TRUE) first
> > > loads into a TEMPORARY segment.  After the load
> > > completes, then the
> > > TEMPORARY segment is "merged" with the table
> > > segment.  All of the "direct"
> > > (a.k.a. "append") operations work this way when
> > > executed in parallel (i.e.
> > > INSERT /*+ APPEND PARALLEL */, CREATE INDEX ...
> > > PARALLEL, CREATE TABLE ...
> > > PARALLEL ... AS SELECT, etc)...
> > > 
> > > Only SQL*Loader's direct-nonparallel mode (i.e.
> > > DIRECT=TRUE, PARALLEL=FALSE)
> > > loads into space on the table segment above the
> > > current "high-water mark".
> > > That expected scenario is probably the reason
> why
> > > you expect all new extents
> > > to be sized according to NEXT...
> > > 
> > > So, the newly added extents were probably
> "INITIAL"
> > > extents for the original
> > > TEMPORARY segments, before they were merged into
> the
> > > table segment, perhaps?
> > > 
> > > - Original Message -
> > > To: "Multiple recipients of list ORACLE-L"
> > > <[EMAIL PROTECTED]>
> > > Sent: Tuesday, May 14, 2002 10:33 AM
> > > 
> > > 
> > > > Hi,
> > > >
> > > > I'm confused by the way some of my extents are
> > > > allocated in Oracle. I've talked to Oracle
> reps, I
> > > > thing I've posted here before and jsut when I
> > > thought
> > > > I got an understanring - ooops, everything is
> > > gone. So
> > > > I'll give it another try. I have a table with
> > > initial
> > > > set to 32K and next set to 1M. This table is
> being
> > > > loaded on a daily basis via Ab initio. The
> load is
> > > > done by SQL*loader in direct mode in parallel
> (I
> > > > believe it is 8 ways). Now all the segments
> that
> > > have
> > > > been created for this table (besides the 1st
> one)
> > > are
> > > > 16K. Where does this size come from? I thought
> it
> > > > should use the NEXT parameter for the new
> extents,
> > > but
> > > > it doesn't. Can someone give me some pointers?
> > > >
> > > > thanks
> > > >
> > > > Gene
> > > >
> > > >
> __
> > > > Do You Yahoo!?
> > > > LAUNCH - Your Yahoo! Music Experience
> > > > http://launch.yahoo.com
> > > > --
> > > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > > --
> > > > Author: Gurelei
> > > >   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: Tim Gorman
> > >   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: TEMP tablespace growing abnormally -Sort Area


TEMPORARY

-Original Message-
Sent: Tuesday, May 14, 2002 9:39 PM
To: Multiple recipients of list ORACLE-L


Is your TEMP tablespace set to TEMPORARY or PERMANENT?



-Original Message-
Sent: Tuesday, May 14, 2002 11:14 AM
To: Multiple recipients of list ORACLE-L


Forgot to tell you that myparameter for sort_area_size= apx. 8GB

-Original Message-
Sent: Tuesday, May 14, 2002 8:02 PM
To: '[EMAIL PROTECTED]'


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up
disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but
can't
get further.
Any help will be appreciated.

---
CSW
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Simon Waibale
  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: Wong, Bing
  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: Simon Waibale
  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: list of events

2002-05-14 Thread Rachel_Carmichael




actually that's the list of error messages, not events

and it's not under the admin directory in any case the path to the error
messages is:

$ORACLE_HOME/rdbms/mesg/roaus.msg




|+--->
||   |
||   |
||  Rajendra.Jamadagn|
||  [EMAIL PROTECTED]   |
||   |
||  05/14/2002 04:23 |
||  PM   |
||  Please respond to|
||  ORACLE-L |
||   |
|+--->
  >|
  ||
  |   To: [EMAIL PROTECTED] |
  |   cc: (bcc: Rachel Carmichael) |
  |   Subject: RE: list of events  |
  >|




$ORACLE_HOME/rdbms/admin/mesg/oraus.msg

Raj
__


Rajendra Jamadagni  MIS, ESPN Inc.


Rajendra dot Jamadagni at ESPN dot com


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


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


 -Original Message-
 From: Babu Nagarajan [mailto:[EMAIL PROTECTED]]
 Sent: Tuesday, May 14, 2002 3:48 PM
 To: Multiple recipients of list ORACLE-L
 Subject: list of events

 All

 I know this has been mentioned on this list before but I forget...

 Which is the script in $ORACLE_HOME/rdbms/admin folder that contains the
 list of all events?

 TIA

 Babu(See attached file: ESPN_Disclaimer.txt)




ESPN_Disclaimer.txt
Description: Text - character set unknown

RE: TEMP tablespace growing abnormally


Yes,
But this shouldn't warrant 'indefinite extension' of the TB ?!?

-Original Message-
Sent: Tuesday, May 14, 2002 9:39 PM
To: Multiple recipients of list ORACLE-L


Do you have auto extend on the temp tablespace?

Bryan

-Original Message-
Sent: Tuesday, May 14, 2002 2:10 PM
To: Multiple recipients of list ORACLE-L


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up
disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but
can't
get further.
Any help will be appreciated.

---
CSW
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Simon Waibale
  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: Rodrigues, Bryan
  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: Simon Waibale
  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-OT] News.

2002-05-14 Thread Eric D. Pierce

just as I suspected, once you factor out the legacy sphincter 
installations, Oracle has the biggest relational "O ring".

On 14 May 2002 at 15:13, Henry Poras  wrote:

> Tom Kyte posted this url as a reply on one of the newsgroups.
> 
> http://biz.yahoo.com/prnews/020508/sfw038_1.html

...

>   Gartner: IBM Steals Database Crown from Oracle
> 
>   IBM has dislodged Oracle Corp. from the top of the market for database
>   management software, helped along by its billion-dollar purchase of Informix
>   Corp., according to new figures being released today by Stamford,
>   Conn.-based Gartner Inc.

...

-- 
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: [oracle-l-OT] News.

2002-05-14 Thread Henry Poras

Tom Kyte posted this url as a reply on one of the
newsgroups.
http://biz.yahoo.com/prnews/020508/sfw038_1.html
Henry

- Original Message -
From:
Mark
Leith
To: Oracle-L-Ot
Cc: Oracle-L
Sent: Tuesday, May 14, 2002 10:04
AM
Subject: [oracle-l-OT] News.
Gartner: IBM Steals Database Crown from OracleIBM
has dislodged Oracle Corp. from the top of the market for
databasemanagement software, helped along by its billion-dollar purchase
of InformixCorp., according to new figures being released today by
Stamford,Conn.-based Gartner Inc.In 2001, IBM and Westborough,
Mass.-based Informix captured a combined 34.6%of worldwide new license
revenue from database management systems, bestingOracle's 32%, Gartner
said. In 2000, the totals were 33.7% for IBM-Informixand 34.1% for
Oracle.The market as a whole generated $8.8 billion in revenue,
growing just 1.4%from the previous year. In contrast, revenue climbed a
brisk 10% from 1999to 2000, Gartner said.Most database management
systems vendors saw single-digit or negativegrowth, although Microsoft
Corp. was a notable exception, Gartner said. Thesoftware giant saw revenue
from new database management systems licensesclimb 17.8%, putting it in
third place behind Oracle with 16.3% of themarket. Sybase Inc. was a
distant fourth with 2.6%.For more on this storyhttp://computerworld.com/databasetopics/data/software/story/0%2C10801%2C70903%2C00.html?nlid=AM===Mark
Leith
| T: +44 (0)1905 330 281Sales &
Marketing | F: +44 (0)870 127 5283Cool Tools
UK Ltd | E:
[EMAIL PROTECTED]===
http://www.cool-tools.co.uk
Maximising throughput & performance

Yahoo! Groups
Sponsor

Height:
4567 ft
01234567891011 in

Weight:

Sex:
F
M

To
talk about oracle database issues: subscribe to
[EMAIL PROTECTED]To do the offtopic(OT) thing. post here
:)To unsubscribe from this group, send an email
to:[EMAIL PROTECTED]Your
use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

RE: {9i New Features: Online Reorg or DBMS_REDEFINITION Package}

2002-05-14 Thread Grabowy, Chris




And constraintsand DBAs can revoke/grant 
other schemas objects

  -Original Message-From: Toepke, Kevin M 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 3:48 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  {9i New Features: Online Reorg or DBMS_REDEFINITION 
  Package}
  A quick followup to this...
   
  I've done some testing of this package and 
  concur with Joe. Its kewl. Outside of renaming a column, it can be used to 
  quickly partition a non-partitioned table. Its much faster and easier than 
  using exchange partition.
   
  The 9iR2 new features whitepaper hints at 
  a native rename column command.
   
  Caver
  
-Original Message-From: JOE TESTA 
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 2:58 
PMTo: Multiple recipients of list ORACLE-LSubject: {9i 
New Features: Online Reorg or DBMS_REDEFINITION 
Package}
Welcome to the next installment of 9i New Features, today's topic is 
Online changes of objects, specifically we'll cover the new package called 
DBMS_REDEFINITION.
 
The spec for this package is located where all of the other package 
sources are:
 
ORACLE_HOME/rdbms/admin.  The file is dbmshord.sql
 
So what does this package give us?  Well it gives the capability 
to do online reorganization of a table.  Ok so now if you're not 
confused, you should be :)
In easy to understand terms, in the past when you wanted to move a 
table to a new tablespace, drop a column, add a column, change a column 
datatype, it require a exclusive lock on the table during the 
operation(which if it was a large table could lock it up for a long 
time).  Well that is no longer the case, those kinds of changes can be 
done while DML is still being applied to the object.  
 
Let's take for an example something that all of us have been asking for 
YEARS, the rename of a column.
 
Look at this code, I've included comments within it so its pretty much 
self-explanatory and you can run it against your 9i database to see what if 
it really works.
 
--   BEGINNING OF SCRIPT 
---
 
set serveroutput on size 10;
 
-- let's drop a couple of tables so if we re-run we won't get 
errorsdrop table sales;drop table sales_temp;
 
-- create a new table, handful of columns with the last one named 
incorrectly.
 
create table sales(sales_id number not null, sales_amount 
number(10,2) not null, salesman_id number(5) not 
null, tax_amount number(5,2) not null, bad_column_name 
varchar2(20) not null);
 
-- add a PK since for online reorg it's required
 
alter table sales add primary key(sales_id)-
 
-- insert some data
insert into sales values(1,20,4,5.70,'bogus');insert into sales 
values(2,30,6,6.70,'no way');insert into sales values(3,40,7,7.70,'XX 
way');insert into sales values(4,50,8,8.70,'YY way');insert into 
sales values(5,60,9,9.70,'ZZ way');insert into sales 
values(6,70,1,0.70,'AA way');insert into sales values(7,80,2,1.70,'BB 
way');insert into sales values(8,90,3,2.70,'CC way');insert into 
sales values(9,10,4,3.70,'DD way');insert into sales 
values(10,25,5,4.70,'EE way');
 
-- commit the data
 
commit;
 
-- run the proc to see if this table can be reorganized online, if 
we get an error,--    then its not possible, otherwise 
we're good to go.
 
execute dbms_redefinition.can_redef_table(USER,'SALES');
 
-- we must create the temp table for this reorg to happen manually, 
either with a --    create table statement or via a 
create table as select(no rows please to be copied)--  this 
exercise is going to be to do a rename on the column, so we need to 
create--   the table making sure we have the new column 
name
 
 
 
create table sales_temp(sales_id number not 
null, sales_amount number(10,2) not null, salesman_id 
number(5) not null, tax_amount number(5,2) not 
null, good_column_name varchar2(20) not null);
 
-- have to have a PK on the temp table also.
 
alter table sales_temp add primary key(sales_id);
 
-- lets desc the original to see what it looks like before
 
prompt this is the sales desc before the changedesc sales;
 
-- ok lets fire up the redefinition, the parms are(SCHEMA, OLD_TBL, 
TEMP_TBL,--    then column mapping, notice we're mapping 
bad_column_name to good_column_name).
 
execute 
dbms_redefinition.start_redef_table(USER,'SALES','SALES_TEMP',  
-'sales_id sales_id, sales_amount sales_amount, salesman_id salesman_id, 
-tax_amount tax_amount, bad_column_name good_column_name');
 
-- at this point its YOUR responsiblity to put the triggers, other 
FK constraints(disabled)--  an

Zero-term'd machine in V$SESSION from Winders

2002-05-14 Thread Jesse, Rich


So, there I am, creating a "special" kill user script (don't ask) for one of
our instances, 8.1.6.0.0 on Solaris.  The problem is that some of the output
rows were getting truncated on the output.  Here's the proc I had started:

CREATE OR REPLACE PROCEDURE Euthanize AS

v_printline VARCHAR2(140);

CURSOR c_sessions IS
SELECT vs.username, vs.osuser, 
--
--REPLACE(vs.machine,CHR(0),NULL) "MACHINE",
vs.machine,
--
, vs.logon_time, vs.last_call_et, vp.SPID 
FROM v$session vs, v$process vp
WHERE vs.username IS NOT NULL
AND vs.paddr = vp.addr
ORDER BY vs.last_call_et DESC;

BEGIN

FOR rsess IN c_sessions LOOP
v_printline := rsess.username||'|'||
rsess.osuser||'|'||rsess.machine||'|'||
rsess.logontime||'|'||rsess.idletime||'|'||
rsess.spid||'|'||rsess.logon_time;
dbms_output.put_line(v_printline);
END LOOP;

END Euthanize;

After compiling this, I called it from SQL*Plus using "execute euthanize;".

I noticed that all the DBMS_OUTPUT lines that were truncated were sessions
from Windohs workstations.  Examining a SELECT DUMP(machine) FROM V$SESSION
showed that all of the MACHINE columns from Windohs sessions were
zero-terminated.  No other client (Solaris) was.

Since the output from DBMS_OUTPUT is being prematurely truncated by this
zero-term'd field, the workaround is to either move the problem field to the
end of the output line, or to use the REPLACE function, as I've commented
out in the above code.  The latter is necessary if there is more than one
bastardized field like this or if the order of the columns in the output is
important.

Can anyone reproduce this?  The particular clients I found with the problem
are 8.1.7.

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

RE: [oracle-l-OT] News.

2002-05-14 Thread Mohan, Ross

Eric, 

Are you saying the legacy installations are crap?

(Some of my favorite stovepipe database systems acheive
their obfuscation by virtue of being routed through 
mainframe 'oracle image' rdbms-like emulations.)

-- Larry "Roland" Ellison

-Original Message-
Sent: Tuesday, May 14, 2002 3:39 PM
To: [EMAIL PROTECTED]
Cc: Oracle-L

just as I suspected, once you factor out the legacy sphincter 
installations, Oracle has the biggest relational "O ring".

On 14 May 2002 at 15:13, Henry Poras  wrote:

> Tom Kyte posted this url as a reply on one of the newsgroups.
> 
> http://biz.yahoo.com/prnews/020508/sfw038_1.html

...

>   Gartner: IBM Steals Database Crown from Oracle
> 
>   IBM has dislodged Oracle Corp. from the top of the market for database
>   management software, helped along by its billion-dollar purchase of
Informix
>   Corp., according to new figures being released today by Stamford,
>   Conn.-based Gartner Inc.

...

 Yahoo! Groups Sponsor -~-->
Tied to your PC? Cut Loose and
Stay connected with Yahoo! Mobile
http://us.click.yahoo.com/QBCcSD/o1CEAA/sXBHAA/o7folB/TM
-~->

To talk about oracle database issues:  subscribe to [EMAIL PROTECTED]

To do the offtopic(OT) thing. post here :)

To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 

-- 
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: list of events

2002-05-14 Thread Jamadagni, Rajendra




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

  -Original Message-From: Babu Nagarajan 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 3:48 
  PMTo: Multiple recipients of list ORACLE-LSubject: list 
  of events
  All
   
  I know this has been 
  mentioned on this list before but I forget... 
   
  Which is the script in 
  $ORACLE_HOME/rdbms/admin folder that contains the list of all 
  events?
   
  TIA
   
  Babu

***1

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

***1

list of events

2002-05-14 Thread Babu Nagarajan




All
 
I know this has been 
mentioned on this list before but I forget... 
 
Which is the script in 
$ORACLE_HOME/rdbms/admin folder that contains the list of all 
events?
 
TIA
 
Babu

Re: Explain THIS plan.

2002-05-14 Thread Stephane Faroult


"Kirsch, Walter J (Northrop Grumman)" wrote:
> 
> 2-cpu, 220mhz, 32-bit HPUX 11.0.  Oracle 8.1.7.0.0
> 
> Could someone explain what's going on here?
> 
> This SQL takes no time at all :
> 
> select
>   substr(username , 1, 12)  "User"
> , substr(lock_type, 1, 18)  "Lock Type"
> , substr(mode_held, 1, 18)  "Mode Held"
>   from sys.dba_lock a
>  , v$sessionb
>  where /*lock_type not in ('Media Recovery','Redo Thread')
>and*/ a.session_id = b.sid;
> 
> with explain plan:
> 
> SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=1 Bytes=148)
>   NESTED LOOPS (Cost=70 Card=1 Bytes=148)
> NESTED LOOPS (Cost=60 Card=1 Bytes=141)
>   NESTED LOOPS (Cost=20 Card=1 Bytes=107)
> FIXED TABLE (FULL) OF X$KSUSE (Cost=10 Card=1 Bytes=30)
> FIXED TABLE (FIXED INDEX #1) OF X$KSUSE (Cost=10 Card=1
> Bytes=77)
>   VIEW OF GV$_LOCK
> UNION-ALL
>   VIEW OF GV$_LOCK1 (Cost=20 Card=2 Bytes=162)
> UNION-ALL
>   FIXED TABLE (FULL) OF X$KDNSSF (Cost=10 Card=1
> Bytes=94)
>   FIXED TABLE (FULL) OF X$KSQEQ (Cost=10 Card=1
> Bytes=94)
>   FIXED TABLE (FULL) OF X$KTADM (Cost=10 Card=1 Bytes=94)
>   FIXED TABLE (FULL) OF X$KTCXB (Cost=10 Card=1 Bytes=94)
> FIXED TABLE (FIXED INDEX #1) OF X$KSQRS (Cost=10 Card=100
> Bytes=700)
> 
> whereas an uncommented predicate consumes 40 minutes of CPU (sez TOP) with
> this explain plan:
> 
> SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=1 Bytes=148)
>   NESTED LOOPS (Cost=70 Card=1 Bytes=148)
> NESTED LOOPS (Cost=30 Card=1 Bytes=114)
>   MERGE JOIN (CARTESIAN) (Cost=20 Card=1 Bytes=37)
> FIXED TABLE (FULL) OF X$KSUSE (Cost=10 Card=1 Bytes=30)
> SORT (JOIN) (Cost=10 Card=1 Bytes=7)
>   FIXED TABLE (FULL) OF X$KSQRS (Cost=10 Card=1 Bytes=7)
>   FIXED TABLE (FIXED INDEX #1) OF X$KSUSE (Cost=10 Card=1
> Bytes=77)
> VIEW OF GV$_LOCK
>   UNION-ALL
> VIEW OF GV$_LOCK1 (Cost=20 Card=2 Bytes=162)
>   UNION-ALL
> FIXED TABLE (FULL) OF X$KDNSSF (Cost=10 Card=1 Bytes=94)
> FIXED TABLE (FULL) OF X$KSQEQ (Cost=10 Card=1 Bytes=94)
> FIXED TABLE (FULL) OF X$KTADM (Cost=10 Card=1 Bytes=94)
> FIXED TABLE (FULL) OF X$KTCXB (Cost=10 Card=1 Bytes=94)
> 
> If it jams, force it. If it breaks, it needed replacing anyway. --John F
> Duval
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Kirsch, Walter J (Northrop Grumman)
>   INET: [EMAIL PROTECTED]
> 

Walter,

  Keep in mind that X$ tables (which underlay the V$ views) are
relational representations of memory structures ''SGA-as-tables'). In
two words : no statistics. In one acronym : RBO. NOT IN is to the RBO a
strong 'full scan' signal. Add to this the absence of any join condition
on 'a' and 'b' (noticed the cartesian merge ?) and anything can happen.
-- 
Regards,

Stephane Faroult
Oriole Software
-- 
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).

Re: extents allocation in parallel load

2002-05-14 Thread Tim Gorman


What does "SELECT * FROM DBA_TABLESPACES" show for the tablespace involved?

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, May 14, 2002 12:53 PM


> Tim,
> 
> Thanks for the explanation. The table I'm looking at
> has an initial size 32K while the new extents are all
> 16K. Why would this happen?
> 
> thanks
> 
> Gene
> --- Tim Gorman <[EMAIL PROTECTED]> wrote:
> > SQL*Loader in direct-parallel mode (i.e.
> > DIRECT=TRUE, PARALLEL=TRUE) first
> > loads into a TEMPORARY segment.  After the load
> > completes, then the
> > TEMPORARY segment is "merged" with the table
> > segment.  All of the "direct"
> > (a.k.a. "append") operations work this way when
> > executed in parallel (i.e.
> > INSERT /*+ APPEND PARALLEL */, CREATE INDEX ...
> > PARALLEL, CREATE TABLE ...
> > PARALLEL ... AS SELECT, etc)...
> > 
> > Only SQL*Loader's direct-nonparallel mode (i.e.
> > DIRECT=TRUE, PARALLEL=FALSE)
> > loads into space on the table segment above the
> > current "high-water mark".
> > That expected scenario is probably the reason why
> > you expect all new extents
> > to be sized according to NEXT...
> > 
> > So, the newly added extents were probably "INITIAL"
> > extents for the original
> > TEMPORARY segments, before they were merged into the
> > table segment, perhaps?
> > 
> > - Original Message -
> > To: "Multiple recipients of list ORACLE-L"
> > <[EMAIL PROTECTED]>
> > Sent: Tuesday, May 14, 2002 10:33 AM
> > 
> > 
> > > Hi,
> > >
> > > I'm confused by the way some of my extents are
> > > allocated in Oracle. I've talked to Oracle reps, I
> > > thing I've posted here before and jsut when I
> > thought
> > > I got an understanring - ooops, everything is
> > gone. So
> > > I'll give it another try. I have a table with
> > initial
> > > set to 32K and next set to 1M. This table is being
> > > loaded on a daily basis via Ab initio. The load is
> > > done by SQL*loader in direct mode in parallel (I
> > > believe it is 8 ways). Now all the segments that
> > have
> > > been created for this table (besides the 1st one)
> > are
> > > 16K. Where does this size come from? I thought it
> > > should use the NEXT parameter for the new extents,
> > but
> > > it doesn't. Can someone give me some pointers?
> > >
> > > thanks
> > >
> > > Gene
> > >
> > > __
> > > Do You Yahoo!?
> > > LAUNCH - Your Yahoo! Music Experience
> > > http://launch.yahoo.com
> > > --
> > > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > > --
> > > Author: Gurelei
> > >   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: Tim Gorman
> >   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!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Gurelei
>   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: Tim Gorman
  INET: [EMAIL PROTECTED]

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

RE: {9i New Features: Online Reorg or DBMS_REDEFINITION Package}

2002-05-14 Thread Toepke, Kevin M




A quick followup to this...
 
I've done some testing of this package and 
concur with Joe. Its kewl. Outside of renaming a column, it can be used to 
quickly partition a non-partitioned table. Its much faster and easier than using 
exchange partition.
 
The 9iR2 new features whitepaper hints at a 
native rename column command.
 
Caver

  -Original Message-From: JOE TESTA 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 2:58 
  PMTo: Multiple recipients of list ORACLE-LSubject: {9i 
  New Features: Online Reorg or DBMS_REDEFINITION Package}
  Welcome to the next installment of 9i New Features, today's topic is 
  Online changes of objects, specifically we'll cover the new package called 
  DBMS_REDEFINITION.
   
  The spec for this package is located where all of the other package 
  sources are:
   
  ORACLE_HOME/rdbms/admin.  The file is dbmshord.sql
   
  So what does this package give us?  Well it gives the capability to 
  do online reorganization of a table.  Ok so now if you're not confused, 
  you should be :)
  In easy to understand terms, in the past when you wanted to move a 
  table to a new tablespace, drop a column, add a column, change a column 
  datatype, it require a exclusive lock on the table during the operation(which 
  if it was a large table could lock it up for a long time).  Well that is 
  no longer the case, those kinds of changes can be done while DML is still 
  being applied to the object.  
   
  Let's take for an example something that all of us have been asking for 
  YEARS, the rename of a column.
   
  Look at this code, I've included comments within it so its pretty much 
  self-explanatory and you can run it against your 9i database to see what if it 
  really works.
   
  --   BEGINNING OF SCRIPT 
  ---
   
  set serveroutput on size 10;
   
  -- let's drop a couple of tables so if we re-run we won't get 
  errorsdrop table sales;drop table sales_temp;
   
  -- create a new table, handful of columns with the last one named 
  incorrectly.
   
  create table sales(sales_id number not null, sales_amount 
  number(10,2) not null, salesman_id number(5) not 
  null, tax_amount number(5,2) not null, bad_column_name 
  varchar2(20) not null);
   
  -- add a PK since for online reorg it's required
   
  alter table sales add primary key(sales_id)-
   
  -- insert some data
  insert into sales values(1,20,4,5.70,'bogus');insert into sales 
  values(2,30,6,6.70,'no way');insert into sales values(3,40,7,7.70,'XX 
  way');insert into sales values(4,50,8,8.70,'YY way');insert into sales 
  values(5,60,9,9.70,'ZZ way');insert into sales values(6,70,1,0.70,'AA 
  way');insert into sales values(7,80,2,1.70,'BB way');insert into sales 
  values(8,90,3,2.70,'CC way');insert into sales values(9,10,4,3.70,'DD 
  way');insert into sales values(10,25,5,4.70,'EE way');
   
  -- commit the data
   
  commit;
   
  -- run the proc to see if this table can be reorganized online, if we 
  get an error,--    then its not possible, otherwise we're 
  good to go.
   
  execute dbms_redefinition.can_redef_table(USER,'SALES');
   
  -- we must create the temp table for this reorg to happen manually, 
  either with a --    create table statement or via a create 
  table as select(no rows please to be copied)--  this exercise is 
  going to be to do a rename on the column, so we need to 
  create--   the table making sure we have the new column 
  name
   
   
   
  create table sales_temp(sales_id number not 
  null, sales_amount number(10,2) not null, salesman_id 
  number(5) not null, tax_amount number(5,2) not 
  null, good_column_name varchar2(20) not null);
   
  -- have to have a PK on the temp table also.
   
  alter table sales_temp add primary key(sales_id);
   
  -- lets desc the original to see what it looks like before
   
  prompt this is the sales desc before the changedesc sales;
   
  -- ok lets fire up the redefinition, the parms are(SCHEMA, OLD_TBL, 
  TEMP_TBL,--    then column mapping, notice we're mapping 
  bad_column_name to good_column_name).
   
  execute 
  dbms_redefinition.start_redef_table(USER,'SALES','SALES_TEMP',  
  -'sales_id sales_id, sales_amount sales_amount, salesman_id salesman_id, 
  -tax_amount tax_amount, bad_column_name good_column_name');
   
  -- at this point its YOUR responsiblity to put the triggers, other FK 
  constraints(disabled)--  and indexes on the "temp" table before 
  calling the next part to do the "switch".
   
  --  DO THE OTHER INDEXES, ETC HERE
   
  -- ok time to finish up
   
  execute 
  dbms_redefinition.finish_redef_table(USER,'SALES','SALES_TEMP');
   
  prompt this is the definition of sales AFTER the changedesc 
  sales;select * from sales;
   
  ---   END OF SCRIPT 
  ---
   
  So what you think, pretty kewl(geek kids spelling)?
   
  Check out the docs on dbms_redefinition for limitati

LogMiner - Null Username

2002-05-14 Thread Walter K


Does anyone know why USERNAME in V$LOGMNR_CONTENTS is 
not always populated? I have encountered numerous 
instances where a select like 'Select username, 
sql_redo from v$logmnr_contents' shows a username for 
some operations and no username for others. 

I have also noticed that DELETE operations always have 
a null Username.

Any ideas?

Thanks.
-w

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

RE: TEMP tablespace growing abnormally -Sort Area

2002-05-14 Thread Khedr, Waleed


I think you meant 8MB not 8GB, right?

Take a look at v$sort_usage and find these sessions that are filling the
TEMP TS.
>From there find the sql behind this temp space and try to figure out if it's
normal to allocate this space or there is a CARTESIAN join for example that
is giving you this problem.

It's not weired to fill a 32 GB temp TS in a data warehouse application.

Regards,
Waleed

-Original Message-
Sent: Tuesday, May 14, 2002 2:14 PM
To: Multiple recipients of list ORACLE-L


Forgot to tell you that myparameter for sort_area_size= apx. 8GB

-Original Message-
Sent: Tuesday, May 14, 2002 8:02 PM
To: '[EMAIL PROTECTED]'


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up
disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but
can't
get further.
Any help will be appreciated.

---
CSW
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Simon Waibale
  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: TEMP tablespace growing abnormally -Sort Area


Simon - I think Tim has given you a good answer to your TEMP tablespace
growth. I just wanted to warn you to be cautious about extremely large sizes
of SORT_AREA_SIZE. This is a per-process setting, which means that if you
have several processes, each process may acquire a lot of memory and the
overall system memory may be overallocated. I speak from experience, bad
experience.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Tuesday, May 14, 2002 1:14 PM
To: Multiple recipients of list ORACLE-L


Forgot to tell you that myparameter for sort_area_size= apx. 8GB

-Original Message-
Sent: Tuesday, May 14, 2002 8:02 PM
To: '[EMAIL PROTECTED]'


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up
disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but
can't
get further.
Any help will be appreciated.

---
CSW
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Simon Waibale
  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: DENNIS WILLIAMS
  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).

Explain THIS plan.

2002-05-14 Thread Kirsch, Walter J (Northrop Grumman)



2-cpu, 220mhz, 32-bit HPUX 11.0.  Oracle 8.1.7.0.0

Could someone explain what's going on here?

This SQL takes no time at all :

select
  substr(username , 1, 12)  "User"
, substr(lock_type, 1, 18)  "Lock Type"
, substr(mode_held, 1, 18)  "Mode Held"
  from sys.dba_lock a
 , v$sessionb
 where /*lock_type not in ('Media Recovery','Redo Thread')
   and*/ a.session_id = b.sid;

with explain plan:

SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=1 Bytes=148)
  NESTED LOOPS (Cost=70 Card=1 Bytes=148)
NESTED LOOPS (Cost=60 Card=1 Bytes=141)
  NESTED LOOPS (Cost=20 Card=1 Bytes=107)
FIXED TABLE (FULL) OF X$KSUSE (Cost=10 Card=1 Bytes=30)
FIXED TABLE (FIXED INDEX #1) OF X$KSUSE (Cost=10 Card=1
Bytes=77)
  VIEW OF GV$_LOCK
UNION-ALL
  VIEW OF GV$_LOCK1 (Cost=20 Card=2 Bytes=162)
UNION-ALL
  FIXED TABLE (FULL) OF X$KDNSSF (Cost=10 Card=1
Bytes=94)
  FIXED TABLE (FULL) OF X$KSQEQ (Cost=10 Card=1
Bytes=94)
  FIXED TABLE (FULL) OF X$KTADM (Cost=10 Card=1 Bytes=94)
  FIXED TABLE (FULL) OF X$KTCXB (Cost=10 Card=1 Bytes=94)
FIXED TABLE (FIXED INDEX #1) OF X$KSQRS (Cost=10 Card=100
Bytes=700)

whereas an uncommented predicate consumes 40 minutes of CPU (sez TOP) with
this explain plan:

SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=1 Bytes=148)
  NESTED LOOPS (Cost=70 Card=1 Bytes=148)
NESTED LOOPS (Cost=30 Card=1 Bytes=114)
  MERGE JOIN (CARTESIAN) (Cost=20 Card=1 Bytes=37)
FIXED TABLE (FULL) OF X$KSUSE (Cost=10 Card=1 Bytes=30)
SORT (JOIN) (Cost=10 Card=1 Bytes=7)
  FIXED TABLE (FULL) OF X$KSQRS (Cost=10 Card=1 Bytes=7)
  FIXED TABLE (FIXED INDEX #1) OF X$KSUSE (Cost=10 Card=1
Bytes=77)
VIEW OF GV$_LOCK
  UNION-ALL
VIEW OF GV$_LOCK1 (Cost=20 Card=2 Bytes=162)
  UNION-ALL
FIXED TABLE (FULL) OF X$KDNSSF (Cost=10 Card=1 Bytes=94)
FIXED TABLE (FULL) OF X$KSQEQ (Cost=10 Card=1 Bytes=94)
FIXED TABLE (FULL) OF X$KTADM (Cost=10 Card=1 Bytes=94)
FIXED TABLE (FULL) OF X$KTCXB (Cost=10 Card=1 Bytes=94)

If it jams, force it. If it breaks, it needed replacing anyway. --John F
Duval

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

{9i New Features: Online Reorg or DBMS_REDEFINITION Package}

2002-05-14 Thread JOE TESTA




Welcome to the next installment of 9i New Features, today's topic is Online 
changes of objects, specifically we'll cover the new package called 
DBMS_REDEFINITION.
 
The spec for this package is located where all of the other package sources 
are:
 
ORACLE_HOME/rdbms/admin.  The file is dbmshord.sql
 
So what does this package give us?  Well it gives the capability to do 
online reorganization of a table.  Ok so now if you're not confused, you 
should be :)
In easy to understand terms, in the past when you wanted to move a 
table to a new tablespace, drop a column, add a column, change a column 
datatype, it require a exclusive lock on the table during the operation(which if 
it was a large table could lock it up for a long time).  Well that is no 
longer the case, those kinds of changes can be done while DML is still being 
applied to the object.  
 
Let's take for an example something that all of us have been asking for 
YEARS, the rename of a column.
 
Look at this code, I've included comments within it so its pretty much 
self-explanatory and you can run it against your 9i database to see what if it 
really works.
 
--   BEGINNING OF SCRIPT 
---
 
set serveroutput on size 10;
 
-- let's drop a couple of tables so if we re-run we won't get 
errorsdrop table sales;drop table sales_temp;
 
-- create a new table, handful of columns with the last one named 
incorrectly.
 
create table sales(sales_id number not null, sales_amount 
number(10,2) not null, salesman_id number(5) not 
null, tax_amount number(5,2) not null, bad_column_name 
varchar2(20) not null);
 
-- add a PK since for online reorg it's required
 
alter table sales add primary key(sales_id)
 
-- insert some data
insert into sales values(1,20,4,5.70,'bogus');insert into sales 
values(2,30,6,6.70,'no way');insert into sales values(3,40,7,7.70,'XX 
way');insert into sales values(4,50,8,8.70,'YY way');insert into sales 
values(5,60,9,9.70,'ZZ way');insert into sales values(6,70,1,0.70,'AA 
way');insert into sales values(7,80,2,1.70,'BB way');insert into sales 
values(8,90,3,2.70,'CC way');insert into sales values(9,10,4,3.70,'DD 
way');insert into sales values(10,25,5,4.70,'EE way');
 
-- commit the data
 
commit;
 
-- run the proc to see if this table can be reorganized online, if we 
get an error,--    then its not possible, otherwise we're 
good to go.
 
execute dbms_redefinition.can_redef_table(USER,'SALES');
 
-- we must create the temp table for this reorg to happen manually, 
either with a --    create table statement or via a create 
table as select(no rows please to be copied)--  this exercise is going 
to be to do a rename on the column, so we need to create--   the 
table making sure we have the new column name
 
 
 
create table sales_temp(sales_id number not null, sales_amount 
number(10,2) not null, salesman_id number(5) not 
null, tax_amount number(5,2) not null, good_column_name 
varchar2(20) not null);
 
-- have to have a PK on the temp table also.
 
alter table sales_temp add primary key(sales_id);
 
-- lets desc the original to see what it looks like before
 
prompt this is the sales desc before the changedesc sales;
 
-- ok lets fire up the redefinition, the parms are(SCHEMA, OLD_TBL, 
TEMP_TBL,--    then column mapping, notice we're mapping 
bad_column_name to good_column_name).
 
execute 
dbms_redefinition.start_redef_table(USER,'SALES','SALES_TEMP',  
-'sales_id sales_id, sales_amount sales_amount, salesman_id salesman_id, 
-tax_amount tax_amount, bad_column_name good_column_name');
 
-- at this point its YOUR responsiblity to put the triggers, other FK 
constraints(disabled)--  and indexes on the "temp" table before calling 
the next part to do the "switch".
 
--  DO THE OTHER INDEXES, ETC HERE
 
-- ok time to finish up
 
execute 
dbms_redefinition.finish_redef_table(USER,'SALES','SALES_TEMP');
 
prompt this is the definition of sales AFTER the changedesc 
sales;select * from sales;
 
---   END OF SCRIPT 
---
 
So what you think, pretty kewl(geek kids spelling)?
 
Check out the docs on dbms_redefinition for limitations, etc, don't want to 
give you allthe answers.
 
 
Until next time all hate mail to /dev/null  ,  all good stuff to 
[EMAIL PROTECTED]
 
Joe
 
PS: I did receive a request for PGA* init.ora parm, its on the list to 
do.

Re: extents allocation in parallel load

2002-05-14 Thread Gurelei


Tim,

Thanks for the explanation. The table I'm looking at
has an initial size 32K while the new extents are all
16K. Why would this happen?

thanks

Gene
--- Tim Gorman <[EMAIL PROTECTED]> wrote:
> SQL*Loader in direct-parallel mode (i.e.
> DIRECT=TRUE, PARALLEL=TRUE) first
> loads into a TEMPORARY segment.  After the load
> completes, then the
> TEMPORARY segment is "merged" with the table
> segment.  All of the "direct"
> (a.k.a. "append") operations work this way when
> executed in parallel (i.e.
> INSERT /*+ APPEND PARALLEL */, CREATE INDEX ...
> PARALLEL, CREATE TABLE ...
> PARALLEL ... AS SELECT, etc)...
> 
> Only SQL*Loader's direct-nonparallel mode (i.e.
> DIRECT=TRUE, PARALLEL=FALSE)
> loads into space on the table segment above the
> current "high-water mark".
> That expected scenario is probably the reason why
> you expect all new extents
> to be sized according to NEXT...
> 
> So, the newly added extents were probably "INITIAL"
> extents for the original
> TEMPORARY segments, before they were merged into the
> table segment, perhaps?
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L"
> <[EMAIL PROTECTED]>
> Sent: Tuesday, May 14, 2002 10:33 AM
> 
> 
> > Hi,
> >
> > I'm confused by the way some of my extents are
> > allocated in Oracle. I've talked to Oracle reps, I
> > thing I've posted here before and jsut when I
> thought
> > I got an understanring - ooops, everything is
> gone. So
> > I'll give it another try. I have a table with
> initial
> > set to 32K and next set to 1M. This table is being
> > loaded on a daily basis via Ab initio. The load is
> > done by SQL*loader in direct mode in parallel (I
> > believe it is 8 ways). Now all the segments that
> have
> > been created for this table (besides the 1st one)
> are
> > 16K. Where does this size come from? I thought it
> > should use the NEXT parameter for the new extents,
> but
> > it doesn't. Can someone give me some pointers?
> >
> > thanks
> >
> > Gene
> >
> > __
> > Do You Yahoo!?
> > LAUNCH - Your Yahoo! Music Experience
> > http://launch.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Gurelei
> >   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: Tim Gorman
>   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!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gurelei
  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: News.

2002-05-14 Thread Steven Lembark




>> So how big a market share does DB2 itself command?  They don't say.
>>  Big
>> deal, so IBM-Informix have slightly more market share than Oracle.
>> If
>> Oracle had bought Informix the shoe would be on the other foot.
>> Again, big
>> deal!

What's news is that a few years ago many pundits had written
DB2 off for dead and decided that Oracle was The Thing. If
IBM is creeping up on Oracle it generally means that IBM has
reversed a significant trend -- and probably that Oracle has
blown a good thing.

The numbers are also interesting because new license revenue
is a good indicator of who is switching over or starting up
new systems with the product. Oracle's being behind in new
licensing bodes ill for them in the next few years as they
loose the ongoing licensing and support fees. It also hurts
when selling to suits because the product has less "momentum"
and IBM will certianly be telling everyone about how well they
have turned DB2 around.

It's also vindication of sorts for commercial linux vendors,
since IBM is now doing their best to hork linux on anyone
that'll listen and is doing a rather nice job of integrating
DB2 into linux platforms. This helps give the suits something
solid that runs on linux, backed by Big Blue and does their
jobs well. Uncle Larry, on the other hand, is mercurical
enough that noone really trusts his "vision" of linux taking
over the corporation; IBM the suits are willing to trust on
this one.

It's also good news for data warehousing in general, since
Red Brick may become a product again; which would be good for
linux since it tends to make a nice data warehousing platform
(on the right hardware). This will give people a choice, at
least, and save us from having Oracle shoved up our snoots
for tasks it does poorly just becuase it's Oracle.

I'm certianly not going to run around in the streets naked
celebrating over the news [for which my neighbors are lucky]
but it is worth noting and is probably pleasant news for most
of us.

--
Steven Lembark   2930 W. Palmer
Workhorse Computing   Chicago, IL 60647
+1 800 762 1582
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Lembark
  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: TEMP tablespace growing abnormally

2002-05-14 Thread Rodrigues, Bryan


Do you have auto extend on the temp tablespace?

Bryan

-Original Message-
Sent: Tuesday, May 14, 2002 2:10 PM
To: Multiple recipients of list ORACLE-L


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up
disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but
can't
get further.
Any help will be appreciated.

---
CSW
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Simon Waibale
  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: Rodrigues, Bryan
  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: TEMP tablespace growing abnormally -Sort Area

2002-05-14 Thread Wong, Bing


Is your TEMP tablespace set to TEMPORARY or PERMANENT?



-Original Message-
Sent: Tuesday, May 14, 2002 11:14 AM
To: Multiple recipients of list ORACLE-L


Forgot to tell you that myparameter for sort_area_size= apx. 8GB

-Original Message-
Sent: Tuesday, May 14, 2002 8:02 PM
To: '[EMAIL PROTECTED]'


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up
disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but
can't
get further.
Any help will be appreciated.

---
CSW
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Simon Waibale
  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: Wong, Bing
  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 version usage


Alex - There was an article about Oracle that came out recently in the
media. Toward the bottom it listed a percentage of production systems that
were using 9i. It was pretty small, as expected. I poked around a little,
but couldn't locate the article in the time I had available.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Monday, May 13, 2002 8:23 PM
To: Multiple recipients of list ORACLE-L


Maybe the question was not correctly formulated. Anybody has some knowledge
or maybe gess about percentages of 7.3, 8.0, 8.1 and 9 in production and
prognosys for a year. Maybe some studies were published?

Alex Hillman

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Alex
> Hillman
> Sent: Saturday, May 11, 2002 12:03 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Oracle version usage
>
>
> Anybody knows what percentage of production databases are 7.3, 8.0 and 8.1
> and prognosys in an year.
>
> Alex Hillman
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Alex Hillman
>   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: Alex Hillman
  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: DENNIS WILLIAMS
  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).

TEMP tablespace growing abnormally


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but can't
get further.
Any help will be appreciated.

---
CSW
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Simon Waibale
  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: extents allocation in parallel load

2002-05-14 Thread Tim Gorman


SQL*Loader in direct-parallel mode (i.e. DIRECT=TRUE, PARALLEL=TRUE) first
loads into a TEMPORARY segment.  After the load completes, then the
TEMPORARY segment is "merged" with the table segment.  All of the "direct"
(a.k.a. "append") operations work this way when executed in parallel (i.e.
INSERT /*+ APPEND PARALLEL */, CREATE INDEX ... PARALLEL, CREATE TABLE ...
PARALLEL ... AS SELECT, etc)...

Only SQL*Loader's direct-nonparallel mode (i.e. DIRECT=TRUE, PARALLEL=FALSE)
loads into space on the table segment above the current "high-water mark".
That expected scenario is probably the reason why you expect all new extents
to be sized according to NEXT...

So, the newly added extents were probably "INITIAL" extents for the original
TEMPORARY segments, before they were merged into the table segment, perhaps?

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, May 14, 2002 10:33 AM


> Hi,
>
> I'm confused by the way some of my extents are
> allocated in Oracle. I've talked to Oracle reps, I
> thing I've posted here before and jsut when I thought
> I got an understanring - ooops, everything is gone. So
> I'll give it another try. I have a table with initial
> set to 32K and next set to 1M. This table is being
> loaded on a daily basis via Ab initio. The load is
> done by SQL*loader in direct mode in parallel (I
> believe it is 8 ways). Now all the segments that have
> been created for this table (besides the 1st one) are
> 16K. Where does this size come from? I thought it
> should use the NEXT parameter for the new extents, but
> it doesn't. Can someone give me some pointers?
>
> thanks
>
> Gene
>
> __
> Do You Yahoo!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Gurelei
>   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: Tim Gorman
  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).

TEMP tablespace growing abnormally -Sort Area


Forgot to tell you that myparameter for sort_area_size= apx. 8GB

-Original Message-
Sent: Tuesday, May 14, 2002 8:02 PM
To: '[EMAIL PROTECTED]'


Hi all,
Having a bad start of week.
My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up disk).
DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running 
billing application for appx 200,000 customer base.
Initially set-up as OPS but undone though init.ora file still has
PARALEL_SERVER=TRUE, only one node is active.

I've seen a refernce to OPS in relation to de-allocation of extents but can't
get further.
Any help will be appreciated.

---
CSW
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Simon Waibale
  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: News.

2002-05-14 Thread Boivin, Patrice J


Esp. if Microsoft charges new licenses when people upgrade.

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

-Original Message-
Sent:   Tuesday, May 14, 2002 2:39 PM
To: Multiple recipients of list ORACLE-L
Subject:Re: News.

I think that the key words in the article are "worldwide new license
revenue" NOT market share...

-Joe

--- KENNETH JANUSZ <[EMAIL PROTECTED]> wrote:
> So how big a market share does DB2 itself command?  They don't say.
>  Big
> deal, so IBM-Informix have slightly more market share than Oracle. 
> If
> Oracle had bought Informix the shoe would be on the other foot. 
> Again, big
> deal!
> 
> Ken Janusz, CPIM
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, May 14, 2002 10:08 AM
> 
> 
> > Gartner: IBM Steals Database Crown from Oracle
> >
> > IBM has dislodged Oracle Corp. from the top of the market for
> database
> > management software, helped along by its billion-dollar purchase
> of
> Informix
> > Corp., according to new figures being released today by Stamford,
> > Conn.-based Gartner Inc.
> >
> > In 2001, IBM and Westborough, Mass.-based Informix captured a
> combined
> 34.6%
> > of worldwide new license revenue from database management
> systems, besting
> > Oracle's 32%, Gartner said. In 2000, the totals were 33.7% for
> IBM-Informix
> > and 34.1% for Oracle.
> >
> > The market as a whole generated $8.8 billion in revenue, growing
> just 1.4%
> > from the previous year. In contrast, revenue climbed a brisk 10%
> from 1999
> > to 2000, Gartner said.
> >
> > Most database management systems vendors saw single-digit or
> negative
> > growth, although Microsoft Corp. was a notable exception, Gartner
> said.
> The
> > software giant saw revenue from new database management systems
> licenses
> > climb 17.8%, putting it in third place behind Oracle with 16.3%
> of the
> > market. Sybase Inc. was a distant fourth with 2.6%.
> >
> > For more on this story
> >
>
http://computerworld.com/databasetopics/data/software/story/0%2C10801%2C7090
> > 3%2C00.html?nlid=AM


__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.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).
-- 
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: Trigger or ????

2002-05-14 Thread Burton, Laura L.

Title: Trigger or 









Nope.

 

We have a document number which consists
of a serial number, date, location, etc, etc.  The business rule is to have the serial
number start back at one each day.  

 

 

-Original Message-
From: Khedr, Waleed
[mailto:[EMAIL PROTECTED]] 
Sent: Tuesday, May 14, 2002 12:55
PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Trigger or 

 



Do you mind if I ask why
do you want to reset the sequence?





 





Regards,





   
Waleed

RE: Trigger or ????

2002-05-14 Thread Khedr, Waleed

Title: Trigger or 



Do you 
mind if I ask why do you want to reset the sequence?
 
Regards,
 
Waleed

  -Original Message-From: Burton, Laura L. 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 10:58 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  Trigger or 
  I have a need to reset a sequence number 
  at 00:01 
  everyday.  I thought about creating a trigger to check the time, but 
  thought that there might be a better way than checking the time every time a record is being added.  I also 
  thought about checking the max date on the table and comparing against the system 
  date.  When system date > max then reset the sequence 
  number.  I like this logic better and 
  thought of holes with using the time.  
  The only problem I have is that this 
  seems like a lot of overhead every time I add a record, which will be often.  Is a trigger the only method available to me?
  Thanks,
  Laura

Re: News.

2002-05-14 Thread Joe Raube


I think that the key words in the article are "worldwide new license
revenue" NOT market share...

-Joe

--- KENNETH JANUSZ <[EMAIL PROTECTED]> wrote:
> So how big a market share does DB2 itself command?  They don't say.
>  Big
> deal, so IBM-Informix have slightly more market share than Oracle. 
> If
> Oracle had bought Informix the shoe would be on the other foot. 
> Again, big
> deal!
> 
> Ken Janusz, CPIM
> 
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, May 14, 2002 10:08 AM
> 
> 
> > Gartner: IBM Steals Database Crown from Oracle
> >
> > IBM has dislodged Oracle Corp. from the top of the market for
> database
> > management software, helped along by its billion-dollar purchase
> of
> Informix
> > Corp., according to new figures being released today by Stamford,
> > Conn.-based Gartner Inc.
> >
> > In 2001, IBM and Westborough, Mass.-based Informix captured a
> combined
> 34.6%
> > of worldwide new license revenue from database management
> systems, besting
> > Oracle's 32%, Gartner said. In 2000, the totals were 33.7% for
> IBM-Informix
> > and 34.1% for Oracle.
> >
> > The market as a whole generated $8.8 billion in revenue, growing
> just 1.4%
> > from the previous year. In contrast, revenue climbed a brisk 10%
> from 1999
> > to 2000, Gartner said.
> >
> > Most database management systems vendors saw single-digit or
> negative
> > growth, although Microsoft Corp. was a notable exception, Gartner
> said.
> The
> > software giant saw revenue from new database management systems
> licenses
> > climb 17.8%, putting it in third place behind Oracle with 16.3%
> of the
> > market. Sybase Inc. was a distant fourth with 2.6%.
> >
> > For more on this story
> >
>
http://computerworld.com/databasetopics/data/software/story/0%2C10801%2C7090
> > 3%2C00.html?nlid=AM


__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.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).

Test - Please Ignore

2002-05-14 Thread Madhavan Amruthur


Test Mail
---

Madhavan Amruthur



Outgrown your current e-mail service?
Get a 25MB Inbox, POP3 Access, No Ads and No Taglines with LYCOS MAIL PLUS.
http://login.mail.lycos.com/brandPage.shtml?pageId=plus
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Madhavan Amruthur
  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: Trigger or ????

2002-05-14 Thread Mark Leith


How about using DBMS_JOB to do the reset for you?

HTH

Mark

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

-Original Message-
L.
Sent: 14 May 2002 15:58
To: Multiple recipients of list ORACLE-L


I have a need to reset a sequence number at 00:01 everyday.  I thought about
creating a trigger to check the time, but thought that there might be a
better way than checking the time every time a record is being added.  I
also thought about checking the max date on the table and comparing against
the system date.  When system date > max then reset the sequence number.  I
like this logic better and thought of holes with using the time.
The only problem I have is that this seems like a lot of overhead every time
I add a record, which will be often.  Is a trigger the only method available
to me?
Thanks,
Laura

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Leith
  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: Trigger or ????

2002-05-14 Thread Vergara, Michael (TEM)

Title: Trigger or 



Here's a thought...create a stored procedure and run it from 
the
DBMS_JOB scheduler.  Start the job at 23:55, and have it 
loop
internally (checking sysdate) until exactly the time you want, 

then reset the sequence.  Be sure to set the 
DBMS_JOB.NEXT
parameter so that it starts at exactly the same time every 
day.
 
Another idea is to run your reset script from 'cron' or 
'at'
and again let the system determine when exactly to fire 
the
reset.
 
Cheers,
Mike

  -Original Message-From: Burton, Laura L. 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 7:58 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  Trigger or 
  I have a need to reset a sequence number 
  at 00:01 
  everyday.  I thought about creating a trigger to check the time, but 
  thought that there might be a better way than checking the time every time a record is being added.  I also 
  thought about checking the max date on the table and comparing against the system 
  date.  When system date > max then reset the sequence 
  number.  I like this logic better and 
  thought of holes with using the time.  
  The only problem I have is that this 
  seems like a lot of overhead every time I add a record, which will be often.  Is a trigger the only method available to me?
  Thanks,
  Laura

RE: Trigger or ????

2002-05-14 Thread John Hallas

Title: Trigger or 









Why not set a dbms_job up to recreate the sequence
or even  a cronjob which is quite
easy to set to run at 00:01

 

John

 

-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Burton,
Laura L.
Sent: 14 May 2002 15:58
To: Multiple recipients of list
ORACLE-L
Subject: Trigger or 

 

I have a need to reset a
sequence number at 00:01 everyday.  I thought about creating a trigger to
check the time, but thought that there might be a better way than checking the
time every time a record is being added.  I also thought about checking
the max date on the table and comparing against the system date.  When system
date > max then
reset the sequence number.  I like this
logic better and thought of holes with using the time.  

The only problem I have
is that this seems like a lot of overhead every time I add a record,
which will be often.  Is a trigger the only
method available to me?

Thanks,

Laura

Re: News.

2002-05-14 Thread KENNETH JANUSZ


So how big a market share does DB2 itself command?  They don't say.  Big
deal, so IBM-Informix have slightly more market share than Oracle.  If
Oracle had bought Informix the shoe would be on the other foot.  Again, big
deal!

Ken Janusz, CPIM

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, May 14, 2002 10:08 AM


> Gartner: IBM Steals Database Crown from Oracle
>
> IBM has dislodged Oracle Corp. from the top of the market for database
> management software, helped along by its billion-dollar purchase of
Informix
> Corp., according to new figures being released today by Stamford,
> Conn.-based Gartner Inc.
>
> In 2001, IBM and Westborough, Mass.-based Informix captured a combined
34.6%
> of worldwide new license revenue from database management systems, besting
> Oracle's 32%, Gartner said. In 2000, the totals were 33.7% for
IBM-Informix
> and 34.1% for Oracle.
>
> The market as a whole generated $8.8 billion in revenue, growing just 1.4%
> from the previous year. In contrast, revenue climbed a brisk 10% from 1999
> to 2000, Gartner said.
>
> Most database management systems vendors saw single-digit or negative
> growth, although Microsoft Corp. was a notable exception, Gartner said.
The
> software giant saw revenue from new database management systems licenses
> climb 17.8%, putting it in third place behind Oracle with 16.3% of the
> market. Sybase Inc. was a distant fourth with 2.6%.
>
> For more on this story
>
http://computerworld.com/databasetopics/data/software/story/0%2C10801%2C7090
> 3%2C00.html?nlid=AM
>
> ===
>  Mark Leith | T: +44 (0)1905 330 281
>  Sales & Marketing  | F: +44 (0)870 127 5283
>  Cool Tools UK Ltd  | E: [EMAIL PROTECTED]
> ===
>http://www.cool-tools.co.uk
>Maximising throughput & performance
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mark Leith
>   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: KENNETH JANUSZ
  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).

Trigger or ????

2002-05-14 Thread Magaliff, Bill

Title: Trigger or 



try a 
cron job (if unix) or at job (if NT)

  -Original Message-From: Burton, Laura L. 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 10:58 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  Trigger or 
  I have a need to reset a sequence number 
  at 00:01 
  everyday.  I thought about creating a trigger to check the time, but 
  thought that there might be a better way than checking the time every time a record is being added.  I also 
  thought about checking the max date on the table and comparing against the system 
  date.  When system date > max then reset the sequence 
  number.  I like this logic better and 
  thought of holes with using the time.  
  The only problem I have is that this 
  seems like a lot of overhead every time I add a record, which will be often.  Is a trigger the only method available to me?
  Thanks,
  Laura

RE: Unix Script --- Archive Log Destination Issue

2002-05-14 Thread Brian_P_MacLean



I have found doing a "ps" command for your job name alone is just asking
for a world of pain, and very very unreliable for reasons that I will not
go into here.  I like to create a lock file as the following ksh example
illustrates:

JOB_LOCK="/tmp/${0##*/}"
if [ -f ${JOB_LOCK} ]
then
  cat ${JOB_LOCK} | read v_LOCK_PID
  echo "Found job lock pid ${v_LOCK_PID}"
  if ps -ef | grep " ${v_LOCK_PID} " | grep -v grep | grep ${0##*/}
>/dev/null
  then
echo "Job lock pid ${v_LOCK_PID} is running"
exit
  fi
  echo "Job lock pid ${v_LOCK_PID} not found, removing old job lock"
  rm ${JOB_LOCK}
fi
v_LOCK_PID=${$}
echo "${v_LOCK_PID}" >${JOB_LOCK}
echo "Created job file lock"
.
.the rest of your script goes here
.
rm ${JOB_LOCK}


PS: I'm looking for a new TV, think you could get me a deal at BestBuy with
your employee discount?

Brian P. MacLean
Oracle DBA, OCP8i



   
   
  "Reddy, Madhusudana" 
   
 
  estbuy.com> cc:  
   
  Sent by:Subject:  RE: Unix Script --- 
Archive Log Destination Issue 
  [EMAIL PROTECTED] 
   
   
   
   
   
  05/13/02 10:48 AM
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   




My mistake , this new script is only working sometimes. Which is scheduled
every hour , but not working at all the hour changes .

-Original Message-
Sent: Monday, May 13, 2002 11:28 AM
To: Multiple recipients of list ORACLE-L


Hello All,
I have a script, which cleans up the archive log destination by moving the
old archive logs to a retention area based on the thresholds
1. Percent of space utilization
2. greater than 2 days old.

The script was working fine , except for the reason , sometimes we see
multiple copies of the same. So I have added the following to code just to
stop multiple copies. But now the script is not running at all and we did
see more than 90% space utilization archive log destination.

Here is the added code:


# Exit If Already Running

function ExitIfAlreadyRunning
{
copies=`ps -ef | grep OraProcessArch | grep "$SID_NAME" | grep -v grep | wc
-l`
if [ $copies -gt 2 ]
then
   echo $(date)" Number Of Copies Running : $copies"
   exit 0
else
   return 0
fi
}

Any suggestions ???

I am not  so good in using semaphores and all , looking for a simple
solution if possible ..

Thanks in advance,
Madhu

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Reddy, Madhusudana
  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: Reddy, Madhusudana
  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 l

Re:Trigger or ????

2002-05-14 Thread dgoulet


Laura,

Look at using a DBMS_JOB.  They fire whenever you need them to.

Dick Goulet

Reply Separator
Author: "Burton; Laura L." <[EMAIL PROTECTED]>
Date:   5/14/2002 6:58 AM

I have a need to reset a sequence number at 00:01 everyday.  I thought about
creating a trigger to check the time, but thought that there might be a
better way than checking the time every time a record is being added.  I
also thought about checking the max date on the table and comparing against
the system date.  When system date > max then reset the sequence number.  I
like this logic better and thought of holes with using the time.  

The only problem I have is that this seems like a lot of overhead every time
I add a record, which will be often.  Is a trigger the only method available
to me?

Thanks,

Laura







Trigger or 



I have a need to reset a sequence number
at 00:01
everyday.  I thought about creating a trigger to check the time, but
thought that there might be a better way than checking the time every time a record is being added.  I also
thought about checking the max date on the table and comparing against the system
date.  When system date > max then reset the sequence
number.  I like this logic better and
thought of holes with using the time.  

The only problem I have is that this
seems like a lot of overhead every time I add a record, which will be often.  Is a trigger the only method available to me?

Thanks,

Laura



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

extents allocation in parallel load

2002-05-14 Thread Gurelei


Hi,

I'm confused by the way some of my extents are
allocated in Oracle. I've talked to Oracle reps, I
thing I've posted here before and jsut when I thought
I got an understanring - ooops, everything is gone. So
I'll give it another try. I have a table with initial
set to 32K and next set to 1M. This table is being
loaded on a daily basis via Ab initio. The load is
done by SQL*loader in direct mode in parallel (I
believe it is 8 ways). Now all the segments that have
been created for this table (besides the 1st one) are
16K. Where does this size come from? I thought it
should use the NEXT parameter for the new extents, but
it doesn't. Can someone give me some pointers?

thanks

Gene

__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gurelei
  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: Trigger or ????

2002-05-14 Thread Yexley Robert D Contr Det 1 AFRL/WSI

Title: Trigger or 



Create a stored 
procedure that resets your sequence number, and then run that procedure at the 
necessary time each night using dbms_job to schedule it.  Look into the 
Supplied Packages Reference for your version of the database for specifics on 
usage of the dbms_job package.
 
_YEX_
<)))><

  -Original Message-From: Burton, Laura L. 
  [mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 10:58 
  AMTo: Multiple recipients of list ORACLE-LSubject: 
  Trigger or 
  I have a need to reset a sequence number 
  at 00:01 
  everyday.  I thought about creating a trigger to check the time, but 
  thought that there might be a better way than checking the time every time a record is being added.  I also 
  thought about checking the max date on the table and comparing against the system 
  date.  When system date > max then reset the sequence 
  number.  I like this logic better and 
  thought of holes with using the time.  
  The only problem I have is that this 
  seems like a lot of overhead every time I add a record, which will be often.  Is a trigger the only method available to me?
  Thanks,
  Laura

Useful settings for login.sql

2002-05-14 Thread Bob Metelsky


All

Ive set my page size and buffer to 1000 so my scroll enables and
I can see all columns, the problem is when I describe tables the columns
are miles wide. Does anyone have a nice preset login.sql or glogin?? Im
shrt on time and need to get some nicely spooled files...

Thanks
bob

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bob Metelsky
  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).

News.

2002-05-14 Thread Mark Leith


Gartner: IBM Steals Database Crown from Oracle

IBM has dislodged Oracle Corp. from the top of the market for database
management software, helped along by its billion-dollar purchase of Informix
Corp., according to new figures being released today by Stamford,
Conn.-based Gartner Inc.

In 2001, IBM and Westborough, Mass.-based Informix captured a combined 34.6%
of worldwide new license revenue from database management systems, besting
Oracle's 32%, Gartner said. In 2000, the totals were 33.7% for IBM-Informix
and 34.1% for Oracle.

The market as a whole generated $8.8 billion in revenue, growing just 1.4%
from the previous year. In contrast, revenue climbed a brisk 10% from 1999
to 2000, Gartner said.

Most database management systems vendors saw single-digit or negative
growth, although Microsoft Corp. was a notable exception, Gartner said. The
software giant saw revenue from new database management systems licenses
climb 17.8%, putting it in third place behind Oracle with 16.3% of the
market. Sybase Inc. was a distant fourth with 2.6%.

For more on this story
http://computerworld.com/databasetopics/data/software/story/0%2C10801%2C7090
3%2C00.html?nlid=AM

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


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Leith
  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).

Trigger or ????

2002-05-14 Thread Burton, Laura L.

Title: Trigger or 





I have a need to reset a sequence number at 00:01 everyday.  I thought about creating a trigger to check the time, but thought that there might be a better way than checking the time every time a record is being added.  I also thought about checking the max date on the table and comparing against the system date.  When system date > max then reset the sequence number.  I like this logic better and thought of holes with using the time.  

The only problem I have is that this seems like a lot of overhead every time I add a record, which will be often.  Is a trigger the only method available to me?

Thanks,

Laura

RE: Virtual Address IN Oracle Fail Safe.. Urgent

2002-05-14 Thread Grabowy, Chris


I helped with an OFS installation last year.  The virtual IP/hostname is the
one that you would use in tnsnames.ora.  MSCS and OFS handle translating
that into the real IP address of the node that currently has the database
files and instance.

Goodluck!!!  I had a lot of fun with that OFS stuff.  MSCS actually seemed
to work correctly.  Although we had not stressed it.

Chris

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 14, 2002 4:13 AM
To: Multiple recipients of list ORACLE-L


Hi 
Can Any one help me in How to know what ip address to be given in
configuring the Virtual Address in Fail safe. What should be the Host name
and IP address we have to provide in this. 


RGDS
Ayappan.S


This communication contains information, which is confidential and may also
be privileged. It is for the exclusive use of the intended recipient(s). If
you are not the intended recipient(s), please note that any distribution,
printing, copying or use of this communication or the information in it is
strictly prohibited. If you have received this communication in error,
please notify the sender immediately and then destroy any copies of it.

-- 
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: 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: Oracle version usage

2002-05-14 Thread Grabowy, Chris


Hey Alex,

How are things hanging down in our lovely captial?  BTW, are you still down
there?

I seem to remember reading about something like this either in the IOUG rag
or on the website.

Perhaps you would be willing to take a poll on the list?  If Jared doesn't
mind?  Everyone email you directly?  A few simple poll questions?

CYA

Chris

-Original Message-
Sent: Monday, May 13, 2002 9:23 PM
To: Multiple recipients of list ORACLE-L


Maybe the question was not correctly formulated. Anybody has some knowledge
or maybe gess about percentages of 7.3, 8.0, 8.1 and 9 in production and
prognosys for a year. Maybe some studies were published?

Alex Hillman

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Alex
> Hillman
> Sent: Saturday, May 11, 2002 12:03 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Oracle version usage
>
>
> Anybody knows what percentage of production databases are 7.3, 8.0 and 8.1
> and prognosys in an year.
>
> Alex Hillman
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Alex Hillman
>   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: Alex Hillman
  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).

Wait Stats Question

2002-05-14 Thread csp2201


Hi,

I have used the following sql query to get wait stats :

Select sid, event, p1, p2, p3, wait_time, Seconds_in_wait, state
from V$session_wait where sid  >6 and event not like '%SQL%' and event not like 
'%rdbms%'
order by 7
/

As a result of this query, I got the wait stats and I have found  explanation for all 
wait events except for the following.  Could you please help me understand as to what 
these events or where can I find the explanations for these events.

  SID EVENT  P1 P2 P3  WAIT_TIME 
SECONDS_IN_WAIT STATE  
- -- -- -- -- -- 
--- -- 
   22 PX Deq: Execution Msg   268566527  6  0  0   
   34 WAITING
   36 PX Deq: Execution Msg   268566527 18  0  0   
   34 WAITING
   63 PX Deq: Execution Msg   268566527 18  0  0   
   34 WAITING
   48 PX Deq Credit: send blkd268566527  1  0  0   
   38 WAITING


Thanks for your time.
CP


__
Your favorite stores, helpful shopping tools and great gift ideas. Experience the 
convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/

-- 
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: snapshot and advanced replication

2002-05-14 Thread Alexandre Gorbatchev


Hi,

For Oracle 9i (9.0.1):
"Updatable materialized view" is "Basic Replication" (available and included
in both EE and SE)
"Multi-master replication" is "Advanced Replication" (available and included
only in EE)

BTW, there are slightly different features included in SE for 8i and 9i.
(like AQ)
But these above I believe available in both.

HTH
Alexandre

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, May 14, 2002 4:18 PM


> Sarath - My understanding is that basic replication is included with
Oracle
> Standard Edition and advanced replication is included with Oracle
Enterprise
> Edition. The boundary between what is basic and what is advanced is not
made
> very clear, but I believe that updatable snapshots is part of basic
> replication. Clearly read-only snapshots are part of basic replication.
> Clearly, multi-master replication is part of advanced replication. If
anyone
> else has better information on which types of replication are available
with
> SE and EE, I would be interested in hearing that. I am getting most of
this
> information from the book Oracle Distributed Systems by Charles Dye. It is
> the only book I have located on Oracle replication.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Tuesday, May 14, 2002 8:13 AM
> To: Multiple recipients of list ORACLE-L
>
>
> i am installing Oracle 8.1.7. is the snapshot feature
> part of advanced replication? should i have to take
> the license for advanced replication to use this
> feature.
>
> Thanks
> Sarath
>
> __
> Do You Yahoo!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: sarath kumar
>   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: DENNIS WILLIAMS
>   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: Alexandre Gorbatchev
  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: Alternatives to Directly Attached Disk - NAS or SAN ?


Hi John !
Unfortunately , i can't add to the discussion yet, i juts know much less
than you do.
Therefore , i'd be interested in some URLs / papers on the matter.
Thanks a lot !





-Original Message-
Sent: Tue, May 14, 2002 3:18 PM
To: DBA Forum (E-mail); '[EMAIL PROTECTED]'


Andrey,

The vendors we're talking to are:-

o For DAS - we're putting a quote together ourselves.  No a "serious" option
but it gives a starting point and includes all options
o For SAN we're talking to Sun (T3s) and HP and IBM (in that order)
o For NAS we're talking to Net Apps and Auspex

Oracle certification gives some comfort (otherwise we could consider 100's
of other NAS suppliers).   I've now spent a day looking at the "NAS Vs SAN"
story, and found highly conflicting opinion of the type:-

o SAN is best for database use.
o NAS just doesn't cut it for RDBMS applications
o NAS using Direct Attached Storage (Software protocol) and dedicated
gigabyte connection using fibre channel make it fine for 1000 user database
applications
o NAS runs faster than directly attached disk

I'd be interested if you could add to the discussion.


John R

-Original Message-
Sent: 14 May 2002 10:50
To: LazyDBA.com Discussion

John , would you mind telling which NAS and SAN  solutions have you looked
at or been pointed to by the list,
and which approach / vendor / product is the leader so far ?


DBAndrey

* 03-9254520
* 058-548133
* mailto:[EMAIL PROTECTED]

 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Andrey Bronfin
  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: Who is eating up all the memory ?

2002-05-14 Thread Connor McDonald


Try v$sesstat (for uga/pga figures)

and don't forget that the SGA is probably included in
that 75% you're seeing

hth
connor

 --- Andrey Bronfin <[EMAIL PROTECTED]> wrote:
> dear gurus !
> It's Oracle 8.0.5 on NT.
> I see that 75% of the machine's physical memory and
> much of the virtual one
> is occupied by Oracle.exe process.
> And nothing outstanding runs in the instance at the
> moment.
> How can i see which session/process exactly is
> consuming how much memory
> within the instance.
> And how would i see this on UNIX ?
> 
> Cheers.
> 
> DBAndrey
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Andrey Bronfin
>   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). 

=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

"Some days you're the pigeon, some days you're the statue"

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  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: snapshot and advanced replication


Sarath - My understanding is that basic replication is included with Oracle
Standard Edition and advanced replication is included with Oracle Enterprise
Edition. The boundary between what is basic and what is advanced is not made
very clear, but I believe that updatable snapshots is part of basic
replication. Clearly read-only snapshots are part of basic replication.
Clearly, multi-master replication is part of advanced replication. If anyone
else has better information on which types of replication are available with
SE and EE, I would be interested in hearing that. I am getting most of this
information from the book Oracle Distributed Systems by Charles Dye. It is
the only book I have located on Oracle replication.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Tuesday, May 14, 2002 8:13 AM
To: Multiple recipients of list ORACLE-L


i am installing Oracle 8.1.7. is the snapshot feature
part of advanced replication? should i have to take
the license for advanced replication to use this
feature.

Thanks
Sarath

__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: sarath kumar
  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: DENNIS WILLIAMS
  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).

pl/sql embedded gateway

2002-05-14 Thread sarath kumar


where can i get info on pl/sql embedded gateway. can
someone point me to the links ,sites,docs etc.

thanks
Sarath.

__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: sarath kumar
  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).

snapshot and advanced replication

2002-05-14 Thread sarath kumar


i am installing Oracle 8.1.7. is the snapshot feature
part of advanced replication? should i have to take
the license for advanced replication to use this
feature.

Thanks
Sarath

__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: sarath kumar
  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: 1 Table and its Indexes occupying 30% space of the

2002-05-14 Thread Ron Rogers


Deepender,
 You did not say what the OS was. There are limits on the datafile
sizes on different OS's that could cause you problems. Watch out for the
MAXDATAFILES parameter reaching it's max limit and then you will not be
able to add another datafile. You will still be able to RESIZE them to
the max allowed by the OS.
Ron
ROR mª¿ªm

>>> [EMAIL PROTECTED] 05/13/02 04:13PM >>>
By the time you have to worry, you will have retired from the job ;-)



[EMAIL PROTECTED] wrote:

> Hi All,
>
> I have a table in my database ( size 70GB ) with size of 7GB and it
has 13
>
> indexes on it. 5 Indexes are of the size 2 GB and rest are around
>
> 500-800Mb.
>
> Total size of the table and indexes in total is 21GB.
>
> The table size is increasing everyday and every 10-12 days I have to
add a
>
> datafile for Index and data tablespace.
>
> I just want to know the precautions I need to take while maintaining
this
>
> table. Is oracle 7.3 having restriction on the maximum size of the
table.
>
> Kindly give your valuable suggestions.
>
> Thanks in advance,
>
> Deepender Gupta
>
> The New Power Company
> 1 Manhattanville Road
> Purchase, NY 10577
>
> --
> 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: Anjo Kolk
  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: Ron Rogers
  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: Staffware on Oracle - deletes are hanging...

2002-05-14 Thread Hately Mike


Hi,
I think you're misunderstanding the way these things work. Truncate is
always fairly fast because it does very little work in most cases, simply
resetting the table's HWM and releasing some extents back into FET$.
A delete can have considerable overheads and can bottleneck in many places.
Such things as integrity constraint checking, index maintenance and poor
redo log optimisation can all slow down the operation. 
You should watch for wait events during the delete to see where the database
is actually waiting. Also check the alert log file for messages indicating
that you're waiting to checkpoint.

Regards,
Mike Hately

-Original Message-
Sent: 14 May 2002 13:23
To: Multiple recipients of list ORACLE-L


Hi all.

Anyone that is running Staffware out there on Oracle 8.1.7 - please help me
out here.
This is urgent.
We are trying to delete 70 000 rows from a table - via Staffware, so that we
can do a restore - via Staffware.
This seems to take over 10 hours, and still does not seem to complete.
If I do a truncate table it takes a few seconds, but via Staffware it just
hangs...

Any idea's?

Thanks

Clint


 

 

This email and any attached to it are confidential and intended only for the
individual or 
entity to which it is addressed.  If you are not the intended recipient,
please let us know 
by telephoning or emailing the sender.  You should also delete the email and
any attachment 
from your systems and should not copy the email or any attachment or
disclose their content 
to any other person or entity.  The views expressed here are not necessarily
those of 
Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. 
Churchill Insurance Group plc.  Company Registration Number - 2280426.
England. 
Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1
1DP. 


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

Who is eating up all the memory ?


dear gurus !
It's Oracle 8.0.5 on NT.
I see that 75% of the machine's physical memory and much of the virtual one
is occupied by Oracle.exe process.
And nothing outstanding runs in the instance at the moment.
How can i see which session/process exactly is consuming how much memory
within the instance.
And how would i see this on UNIX ?

Cheers.

DBAndrey

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Andrey Bronfin
  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).

Staffware on Oracle - deletes are hanging...

2002-05-14 Thread Clinton Naude




Hi all.
 
Anyone that is running 
Staffware out there on Oracle 8.1.7 - please help me out 
here.
This is 
urgent.
We are trying to delete 70 000 
rows from a table - via Staffware, so that we can do a restore - via 
Staffware.
This seems to take over 10 
hours, and still does not seem to complete.
If I do a truncate table it 
takes a few seconds, but via Staffware it just hangs...
 
Any idea's?
 
Thanks
 
Clint
*   Clinton S. 
Naudé*   Head DBA 
Services *   Tel: 011 685 
4304*   Fax: 011 685 
4303*   Cell: 082 377 
1726*   E-mail: 
[EMAIL PROTECTED] Confidentiality 
Warning===The contents of this message and 
any attachments are intended solely for the addressee's use and may be legally 
privileged and/or confidential. If you are not the addressee indicated in this 
message, any retention, distribution, copying or use of this message is strictly 
prohibited. If you received this message in error, kindly notify the sender 
immediately by reply e-mail and then destroy the message and any copies thereof. 
The content and any views expressed therein are, unless otherwise stated, the 
views of the author and not those of the company or any of its management or 
directors.Whilst all reasonable steps are taken to ensure the accuracy 
and integrity of information transmitted, the company does not accept 
responsibility for any corruption of the information or data or breach of 
confidentiality as a result of electronic submission.When addressed to 
the Momentum Employee Benefits clients any opinion or advice contained in this 
e-mail is subject to the terms and conditions expressed in any applicable terms 
of business.


Currency.gif
Description: GIF image

OT - Teradata - THANX !


Thanks a lot to all who replied !


DBAndrey

* 03-9254520
* 058-548133
* mailto:[EMAIL PROTECTED]





-Original Message-
Sent: Thu, May 09, 2002 6:19 PM
To: Multiple recipients of list ORACLE-L


Andrey - I would try the dwlist. Here is enough to get signed up. A number
of Teradata sites participate on that list.

For help with list commands, send a message
to  with the
word "help" in the body of the message.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


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


Dear gurus , sorry for the off topic posting.
One of our customers wants to start using Teradata for some of their
products.
That means that I need to learn it, quickly.
I would like to ask for any inputs you can provide regarding Teradata - your
thoughts , impressions, URLs, any materials.

Thanks a lot in advance.

DBAndrey

* 03-9254520
* 058-548133
* mailto:[EMAIL PROTECTED]




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Andrey Bronfin
  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: DENNIS WILLIAMS
  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: Andrey Bronfin
  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: Telephone Alerts

2002-05-14 Thread Steve . Parker


If you are looking for a bigger solution, that as well as SMS, using the SendSMS package I mentioned last time, can also 
do voice alerts, voice XML,  paging, email etc, based on Scheduled database queries and/or Triggers,
then take a look at Categoric Xalerts.

This is a Java based system that runs on both NT/2000 and Solaris, that allows you to graphically build Alerts to many types of device
based on the recipient and time of day, it also allows people to subscribe to alerts via customisable web pages and 
to set how they want to receive the alerts.

There are other alerting packages around but this had the most features and was architecturally better than the other we were looking at.

http://www.categoric.com

Steve Parker
E Mail: [EMAIL PROTECTED]
         --







Robertson Lee - lerobe <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
13/05/2002 16:33
Please respond to ORACLE-L

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


We used to use telalert by Telamon (looks like they have been taken over
now). Seemed to do the job very nicely considering how many times I got
woken up in the wee small hours when I was in manufacturing. GRRR 

http://www.vytek.com/products/urgentmsg/telalert/index.asp


HTH

Lee


-Original Message-
Sent: 13 May 2002 15:48
To: Multiple recipients of list ORACLE-L


Simon,

Working with a tools company that provides a monitoring tool for Oracle - we
have had a lot of call for sending alerts to SMS. We've done a fair bit of
testing of various SMS providers in the UK so that we can try to recommend a
valid solution to our customers - that is able to alert a user with a
consistently good response time. The "norm" is to use an "SMS gateway",
where you send a standard email to a an SMS Gateway provider - who then
forward that email in SMS form to your mobile phone. We've tried various
people such as Genie (BT's) now called mmo2, http://www.uboot.com,
http://www.iobox.com etc. etc. all of which have had poor response times at
various times in the day due to user traffic..

There is also the possibility of getting tools that people have pre-built to
send SMS from a desktop - or server.. Many of these are pretty good in GUI
form, but when it comes to command line support, they are usually very
lacking to none - and let's face it if we wanted to use it, or if you wanted
to call it from a shell type script you *need* command line support.. And if
there is command line support - most of the time these are made by people in
other countries, and don't seem to support our UK service providers such as
Orange, One-2-One, and to a slightly lesser extent Vodafone.

If we are asked to provide SMS alerts for our tools, we now point our
customers to a company called Textforce (http://www.textforce.net) which is
actually supposed to be a targeted marketing type site for sending marketing
SMS messages. It's a "pay per message" provider - with each SMS being around
7-8 pence.. The only reason we actually recommend to our customers that they
use this site - is that throughout our entire testing, this site provided
response times (from the time the email was sent, to the time the SMS was
received) of between 30 seconds - 4 minutes consistently! Many of the other
providers, such as genie, or uboot et al have greatly differing response
times - from 20 seconds right up to 14 hours!

I'd still be interested though if anybody has any experience with server
based tools that are available (for things such as Sun, HP, AIX, NT/2000)
that do have command line support, and also support the major UK providers..

HTH

Mark

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

-Original Message-
Gorbatchev
Sent: 13 May 2002 14:48
To: Multiple recipients of list ORACLE-L


Some mobile network providers make use of email gateway for sms. In that
case you have some email address which is redirected by your provider to
your phone.

Alexandre
- Original Message -
To: Multiple recipients of list ORACLE-L
Sent: Monday, May 13, 2002 3:03 PM


We have some UNIX batch processes that send us e-mails when they have
finished.

Does anyone have any experience of sending a SMS message to a mobile phone?


Simon Fox
Room 221
Furlong House
Queens Drive
NOTTINGHAM
NG2 1AL
Phone   0115 957 6536
Fax     0115 957 6548



___
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
Sc

RE: Alternatives to Directly Attached Disk - NAS or SAN ?


John , would you mind telling which NAS and SAN  solutions have you looked
at or been pointed to by the list,
and which approach / vendor / product is the leader so far ?


DBAndrey

* 03-9254520
* 058-548133
* mailto:[EMAIL PROTECTED]





-Original Message-
Sent: Mon, May 13, 2002 6:12 PM
To: LazyDBA.com Discussion


Thanks,

The comment about SAN problems are consistent with what I've also heard -
SAN solutions tend to be complex, difficult to manage and are easy to screw
up in a right royal fashion.  NAS filers tend not to work as fast for DBMS
use (I've had two replies saying their NAS solution was replaced by a SAN
for performance), but they're very easy to run - very low maintenance.

I've also received details of a new protocol Direct Access File System
(DAFS) which produces performance on NAS similar to directly attached disk
mounted as a raw device.  If this is true (and there's very little
independent review evidence available), then even the performance becomes a
non-issue.

Thanks to all those who've commented - if anyone else has any experience
please chip in.


John R.

-Original Message-
Sent: 13 May 2002 16:12
To: LazyDBA.com Discussion

Hwell, sounds like first thing to consider, is avoiding
windows as the OS that manages and controls it

:-)

Kelly

On Mon, May 13, 2002 at 08:04:24AM -0700, after pounding the keys randomly,
MacGregor, Ian A. came up with
> I don't much about using SAN with Oracle.  However be careful how you
configure it.  We had "experts" set up  SAN for our Windows NT Central File
system.   A few months later, the system was brought down for a fairly minor
problem.  When restarted,  it up  it initiated chkdsk, a process which took
over three days to complete!  Users, unable to get to their files, were less
than impressed.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> [EMAIL PROTECTED]
>
> -Original Message-
> From: John Ryan [mailto:[EMAIL PROTECTED]]
> Sent: Monday, May 13, 2002 1:24 AM
> To: LazyDBA.com Discussion
> Subject: Alternatives to Directly Attached Disk - NAS or SAN ?
>
>
> DBAs,
>
> I'm in the lucky(?) position of deciding the storage strategy for a mid
> sized company (100 staff and growing), who're generating large data
volumes.
> Non-Oracle volumes have reached nearly 500Gb in 12 months, and a major
> Oracle based system has just come "on tap" and expected to generate 1Tb in
> 12 months, then perhaps the same again every year - or frighteningly even
> more!!!
>
> Directly Attached Disk is not a sensible way forward, so we're looking at
> Network Attached Storage (NAS) and Storage Area Network (SAN) solutions.
In
> short I'm finding the following:-
>
> o Both give high reliability and availability with RAID and dual redundant
> almost anything
> o Both can share space between a File Serving and Unix (ie. database)
> applications although the NAS shares files not just space
> o Both provide excellent management tools including "Snapshots" and fast
> restore
>
> But SANs are supposedly faster than NAS solutions for raw I/O and
therefore
> better for database applications.
>
> Has anyone any direct experience with SAN or NAS solutions?
>
> I'm also talking to vendors, but I wondered if there was any general
> consensus in the "real" world.
>
>


Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to [EMAIL PROTECTED]
To subscribe:   send a blank email to [EMAIL PROTECTED]
Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to [EMAIL PROTECTED]
To subscribe:   send a blank email to [EMAIL PROTECTED]
Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Andrey Bronfin
  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).

A trigger vs. "default value" in a table - SOLVED