Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Information on activity in Oracle's temporary segment under

RE: Information on activity in Oracle's temporary segment under

From: <melanie.root_at_us.pwcglobal.com>
Date: Tue, 23 May 2000 15:53:47 -0500
Message-Id: <10506.106444@fatcity.com>


--0__=m8Fb6I7n28QaudxNBICWJG0bYkinZwLlaptVW6ukee106hHJmElLLgLj Content-type: text/plain; charset=us-ascii Content-Disposition: inline

The value of 'TEMPORARY' is for the temporary tablespace. Many sites name their temporary tablespace TEMPORARY. Simply substitute the name of your instances temporary tablespace name and this script should work properly, even in version 7.x.

Melanie

mleith_at_bradmark.co.uk on 05/23/2000 10:43:36 AM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:
Subject: RE: Information on activity in Oracle's temporary segment under

--0__=m8Fb6I7n28QaudxNBICWJG0bYkinZwLlaptVW6ukee106hHJmElLLgLj

Content-type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-transfer-encoding: quoted-printable



I have tested this script, and it doesn't seem to work. First off, it i= s looking
for a contents column in DBA_TABLESPACES, with a value of 'TEMORARY', t= his may
be a column under version 8, but not on my 7.2.2 test instance (HP/UX).=  I
believe (correct me if I'm wrong) that there is no type 'TEMPORARY' of = segments
under version 7?

I am beggining to be resigned to the fact that I just can't get to the = info I
need, If anybody could show me the way down that yellow brick road I wo= uld very
much appreciate it. I am looking for all users running sorts in a tempo= rary
tablespace, with the number of extents that they are explicitly using. = Any
ideas? Does anybody know if this is possible/impossible under version 7= ?

TIA Mark

-----Original Message-----
From: MIME :Colin.Shaw_at_phs.com Sent: 22 May 2000 21:57 To: ORACLE-L_at_fatcity.com
Subject: RE: Information on activity in Oracle's temporary segment und= er

You can find the original script in MetaLink:

Note: 40758.1 TFTS: Script to find who owns TEMP Segments in V7.

-----Original Message-----
Sent: Thursday, May 18, 2000 10:33 AM
To: Multiple recipients of list ORACLE-L under
O

Remco,

I don't know if you sent the e-mail to the list by mistake or not, but = would
you mind forwarding me a copy of that script?

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin & Operations | Admin. et Exploit. des syst=E8mes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique Maritimes Regi=
on, DFO
| R=E9gion des Maritimes, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca> -----Original Message-----

     From:     Daemen, Remco [SMTP:R.Daemen_at_arbeidsbureau.nl]
     Sent:     Thursday, May 18, 2000 1:12 PM
     To:  Multiple recipients of list ORACLE-L
     Subject:  RE: Information on activity in Oracle's temporary
segment under O

     Mark,

     I rewrote the script n40758 a little. Major breakthru was the alteration of

     the constant size_int to 1. After that it worked like a charm. I hope it

     also works for you. Don't forget: the script should be run as user=

SYS !      Good hunting.

     Remco

      <<n40758_new.txt>>      > ----------

> From: mleith_at_bradmark.co.uk[SMTP:mleith_at_bradmark.co.uk]
> Sent: woensdag 17 mei 2000 11:44
> To: Multiple recipients of list ORACLE-L
> Subject: Information on activity in Oracle's temporary
segment under
> Oracle 7
> > <<File: N40758.TXT>><<File: N1069041.TXT>>
> Hi there,
> > I am running 7.3.4.4, with a temporary tablespace (TEMP) of=
 500M,
marked

> as
> type 'temporary'. I believe that this causes there to be a singl=
e

> temporary segment in the
> tablespace, shared by all users, which grows and shrinks accordi=
ng
to

> demand.
> > I occasionally see ORA-1652 errors in the alert log, such a=
s
'ORA-1652:
> unable to extend temp segment by 63 in tablespace TEMP'. If I
> catch these quickly enough, I can see the state of the temporary=

segment

> using the V$SORT_SEGMENT view, as shown in the below example;
> > TABLESPACE_NAME SEGMENT_FILE SEGMENT_BLOCK
EXTENT_SIZE
> CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS
> ------------------------------- ------------ -------------


> ------------- ------------- ------------ ------------
> USED_BLOCKS FREE_EXTENTS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS
> FREED_EXTENTS FREE_REQUESTS MAX_SIZE MAX_BLOCKS MAX_USED_SIZE
> ----------- ------------ ----------- ------------- -----------
> ------------- ------------- ---------- ---------- -------------
> MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS
> --------------- ------------- ---------------
> TEMP 3 62271
63

> 2 978 63569 16
> 1040 962 62529 978 3188
> 0 0 978 63569 978
> 63569 412 26780
> > This example was taken while the system was quiet, but show=
s that
there

> were
> two users using the segment, and were using 14 extents (978 -
962). What I

> cannot tell from this view is, a) who the two users are, and b)
how many

> extents each is using.
> > Occasionally, someone will report experiencing the problem,=
 we
will ask

> them
> to repeat the action, and invariably, it will work the second ti=
me
around.

> > What I suspect is happening when I see the ORA-1652 is that=
 one
user is

> running a large (and probably invalid) query which is taking up
the lion's

> share of the temporary segment, and then some other 'innocent'
user is

> running a genuine query which is requiring a sort, and failing
because it

> is
> exhausting the remaining space in the segment.
> > In the absence of any visibility of what is going on in the=
re, I
don't

> know
> who I should go and take a big stick to!
> > Interestingly, there is a view provided in Oracle8 called
V$SORT_USAGE
> which
> would do the job, but this doesn't help me on Oracle7.
> > I also attach a couple of documents I received from Oracle =
when I
raised

> this as a TAR. The first (n40758) is, I think, not exactly
pertinent to my

> situation, and the second talks about 'dumping a systemstate'.
> > <<n40758.txt>> <<n1069041.txt>> What I would like is a)
visibility of

> who is doing what with regard to sorts
> in the temporary segment, and b) some means of monitoring this
usage and

> raising an alert when some condition is met (e.g. user using mor=
e
than nn

> extents, or something similar).
> > Your mission, should you choose to accept it.......!
> > TIA
> > Mark
> > > << File: n40758_new.txt >> -- Author: Boivin, Patrice=
 J
  INET: BoivinP_at_mar.dfo-mpo.gc.ca

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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

 << File: ENVELOPE.TXT >> --
Author:
  INET: mleith_at_bradmark.co.uk

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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

      The information transmitted is intended only for the person or en= tity to

      which it is addressed and may contain confidential and/or privile= ged

      material. Any review, retransmission, dissemination or other use=  of, or

      taking of any action in reliance upon, this information by person= Received on Tue May 23 2000 - 15:53:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US