DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: URGENT - How to clear out temp segments?

  1. #1
    Obn our system we have a temp segment 3GB(!) in size.
    How do I clear it out? How do I find which GIT is doing this to the system?

    Help! Help!

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    alter tablespace TEMP default storage (pctincrease 1);
    alter tablespace TEMP default storage (pctincrease 0);
    alter tablespace TEMP coalesce;

  3. #3
    Hi there,
    no good im afraid...
    My best guess is that the tansaction is still active so it wont clear. How can I trace back from the temp segment to the 'owner'/instigator? OWNER in dba_segement shows SYS as its a temp segment....

    The phones are ringing.....

    Bob

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    yes, it must be active (or rolling back) ... I guess you are in 8.x, so you can try :

    select username, sid, serial#, contents
    from v$session, v$sort_usage
    where v$session.saddr = v$sort_usage.session_addr

    which will return all users and their SID that are issuing a sort ...

  5. #5
    Fraid not ... this one is a 7.3.4...
    any further ideas? We have added a further datafile to the temp tablespace ...will keep an eye and see if that fills as well..

    -B

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    If you use 8i, make tempfiles in temporary tablespaces. Here is a sample:

    CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/dev/vx/rdsk/datadg/temp_1'
    SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;




  7. #7
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    ok, here is the article about it in 7, but I have tested it several times without having really interesting results :

    Creator of Sort Segment in Oracle 7
    -----------------------------------
    In Oracle 7, it is not so simple. The information is not stored
    in a queryable data dictionary view.

    You can find all SIDs which are using the TEMPORARY Tablespace datafiles
    use the following query:

    SELECT sid,event
    FROM v$session_wait
    WHERE p1 in (:temp_file#)
    AND event like 'db file%';

    [:temp_file# = list of all files comprising the TEMP TABLESPACE]

    Note: important events are 'db file scattered read'
    or 'db file sequential read'

    To find the exact username, you have to dump a systemstate which then
    has to be searched to find the temporary/sort segment and then the
    username.

    To dump a systemstate:

    - CONNECT SYSTEM/MANAGER

    - ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10'
    This will produce trace file in your user_dump_dest directory.

    - Load this trace file to any editor and SEARCH for SORT SEGMENT HANDLE.

    The SORT SEGMENT HANDLE will have been created by a PROCESS, and the
    PROCESS will have a username.

    You'll see something like (this systemstate was taken from Oracle 7.3):

    PROCESS 10:
    ----------------------------------------
    SO: 1e070c8, type: 1, owner: 0, flag: INIT/-/-/0x00
    (process) Oracle pid=10, calls cur/top: 1e25770/1e25770, flag: (0) -
    int error: 0, call error: 0, sess error: 0
    (latch info) wait_event=0 bits=0
    O/S info: user: Administrator, term: VKOPYRA, ospid: 00122
    OSD pid info: pid: 122
    ----------------------------------------
    SO: 1e117a8, type: 3, owner: 1e070c8, flag: ........
    (session) trans: 1e69434, creator: 1e070c8, .........
    oct: 9, prv: 0, user: 10/SCOTT <-- user name is here
    O/S info: user: Administrator, term: .....
    program:
    waiting for 'db file scattered read' seq=378 wait_time=0
    file#=2, block#=28e, blocks=8
    ----------------------------------------
    SO: 1e88cc4, type: 13, owner: 1e117a8, flag: INIT/-/-/0x00
    SORT SEGMENT HANDLE: tsn=3 incr=607 extents=35 <-----------
    (enqueue) TS-04000002-00000000
    lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
    res:1e37888, prv: 1e2ddd4, sess: 1e117a8, proc: 1e070c8
    (enqueue) ST-00000000-00000000


    As a result we can see that user SCOTT was doing something that
    required creating of temporary segment.

    Which users are performing Sorts
    --------------------------------
    Often it becomes necessary to identify the users performing sorts. This
    helps identify the particular sessions that may be issuing resource-intensive
    sorts. You can also determine whether an in-memory or disk sort is being
    performed. The following query lists the users performing sorts a well as
    the type of sort:

    ------cut ----- cut ----- cut ----- cut ----- cut ---- cut -------

    REM #*****************************************************
    REM #File Name: sort.sql
    REM #
    REM #Purpose: Report Sort Statistics
    REM#
    REM #*****************************************************

    SELECT substr(vs.username,1,20) "db user",
    substr(vs.osuser,1,20) "os user",
    substr(vsn.name,1,20) "Type of Sort",
    vss.value
    FROM v$session vs,
    v$sesstat vss,
    v$statname vsn
    WHERE (vss.statistic#=vsn.statistic#) AND
    (vs.sid = vss.sid) AND
    (vsn.name like '%sort%')
    ORDER BY 2,3;
    ------cut ----- cut ----- cut ----- cut ----- cut ---- cut -------

    Example:
    db user os user Type of Sort VALUE
    -------------------- --------------- -------------------- ----------
    SYS kevin sorts (disk) 4
    SYS kevin sorts (memory) 12
    SYS kevin sorts (rows) 9936


    Search Words:
    =============
    TEMP PERMANENT V$SESSION V$SORT_USAGE SORT USER USERID USERNAME

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width