Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Red face Unanswered: temp tablespace is getting much to big

    Hello,

    Doe anyone know how to control the size of the temporary tablespace?
    It's getting particularly big in the next section:

    BEGIN
    -- Step 1 Remove following fields from filter(set SVFCDORDER to 0): datetime->time and long varchar

    DECLARE CURSOR curInvalidSVFCD IS SELECT a.sfierec sfierec, a.sfiename sfiename
    FROM ish.sfield a
    , ish.srecord b
    , DBA_TAB_COLUMNS c
    , ish.sviewfieldconfigdetail d
    WHERE b.srecdbd = ''UMM''
    AND b.srecname = a.sfierec
    AND a.sfierec = c.TABLE_NAME
    AND a.sfiename = c.COLUMN_NAME
    AND a.sfiename = d.svfcdfld
    AND a.sfierec = d.svfcdrec
    AND c.owner = ''ISH''
    AND (
    ( c.DATA_TYPE = ''DATE'' AND a.sfieinput = ''5'' )
    OR
    ( c.DATA_TYPE = ''CLOB'' )
    );
    BEGIN
    FOR recInvalidSVFCD IN curInvalidSVFCD LOOP
    UPDATE ish.SVIEWFIELDCONFIGDETAIL
    SET SVFCDORDER = 0
    WHERE SVFCDDBD = ''UMM''
    AND SVFCDREC = recInvalidSVFCD.sfierec
    AND SVFCDFLD = recInvalidSVFCD.sfiename
    ;
    END LOOP;
    END;

    DECLARE
    nCount NUMBER;
    sSVFCDREC DBA_TAB_COLUMNS.COLUMN_NAME%TYPE;
    sSVFCDID DBA_TAB_COLUMNS.COLUMN_NAME%TYPE;
    sSVFCDCOU DBA_TAB_COLUMNS.COLUMN_NAME%TYPE;
    CURSOR curOrderSVFCD IS SELECT SVFCDREC, SVFCDID, SVFCDCOU, SVFCDORDER
    FROM ISH.SVIEWFIELDCONFIGDETAIL
    WHERE SVFCDDBD = ''UMM'' AND NVL(SVFCDORDER,0) <> 0
    ORDER BY SVFCDDBD, SVFCDREC, SVFCDID, SVFCDCOU, SVFCDORDER, SVFCDFLD
    FOR UPDATE OF SVFCDORDER;
    BEGIN
    FOR recOrderSVFCD IN curOrderSVFCD LOOP
    IF ( sSVFCDREC <> recOrderSVFCD.SVFCDREC OR sSVFCDID <> recOrderSVFCD.SVFCDID OR sSVFCDCOU <> recOrderSVFCD.SVFCDCOU ) THEN
    nCount := 1;
    ELSE
    nCount := nCount + 1;
    END IF;

    UPDATE ish.SVIEWFIELDCONFIGDETAIL
    SET SVFCDORDER = nCount
    WHERE CURRENT OF curOrderSVFCD
    ;

    sSVFCDREC := recOrderSVFCD.SVFCDREC;
    sSVFCDID := recOrderSVFCD.SVFCDID;
    sSVFCDCOU := recOrderSVFCD.SVFCDCOU;
    END LOOP;
    END;


    -- Step 3 Modify default selection criteria if defselcrit is not allowed (RW = Rserved Word)
    -- CLOB - OBSOLETE
    -- DATE - DEFSELCRIT : EqualTo
    -- NUMBER(8/16/32,6) - DEFSELCRIT : EqualTo
    -- VARCHAR2 - DEFSELCRIT : LIKE
    -- BLOB - DEFSELCRIT : NotEmpty
    DECLARE
    NotSelected ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    StartsWith ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    StartsNotWith ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    RWLike ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    NotLike ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    EqualTo ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    GreaterThan ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    SmallerThan ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    GrThanOrEq ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    SmThanOrEq ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    EndsWith ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    EndsNotWith ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    NotEqualTo ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    RWBetween ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    BetweenAndIncluding ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    Empty ish.SFIELD.SFIEDEFSELCRIT%TYPE;
    NotEmpty ish.SFIELD.SFIEDEFSELCRIT%TYPE;

    CURSOR curDefSelCrit IS SELECT SFIEDBD, SFIEREC, SFIENAME, SFIEDEFSELCRIT, c.DATA_TYPE DATA_TYPE
    FROM ish.sfield a
    , ish.srecord b
    , DBA_TAB_COLUMNS c
    WHERE b.srecdbd = 'UMM'
    AND b.srecname = a.sfierec
    AND a.sfierec = c.TABLE_NAME
    AND a.sfiename = c.COLUMN_NAME
    AND c.owner = 'ISH';
    BEGIN
    NotSelected := 0; StartsWith := 1; StartsNotWith := 2; RWLike := 3;
    NotLike := 4; EqualTo := 5; GreaterThan := 6; SmallerThan := 7;
    GrThanOrEq := 8; SmThanOrEq := 9; EndsWith := 10; EndsNotWith := 11;
    NotEqualTo := 12; RWBetween := 13; BetweenAndIncluding := 14; Empty := 15;
    NotEmpty := 16;

    FOR recDefSelCrit IN curDefSelCrit LOOP
    -- VARCHAR2
    IF ( recDefSelCrit.DATA_TYPE = 'VARCHAR2'
    AND recDefSelCrit.SFIEDEFSELCRIT IN ( GreaterThan, SmallerThan, GrThanOrEq, SmThanOrEq )
    ) THEN

    UPDATE ish.SFIELD
    SET SFIEDEFSELCRIT = RWLike
    WHERE SFIEDBD = recDefSelCrit.SFIEDBD
    AND SFIEREC = recDefSelCrit.SFIEREC
    AND SFIENAME = recDefSelCrit.SFIENAME
    ;
    END IF;
    -- NUMBER
    IF ( recDefSelCrit.DATA_TYPE = 'NUMBER'
    AND recDefSelCrit.SFIEDEFSELCRIT IN ( StartsWith, StartsNotWith, RWLike, NotLike, EndsWith, EndsNotWith, Empty, NotEmpty )
    ) THEN

    UPDATE ish.SFIELD
    SET SFIEDEFSELCRIT = EqualTo
    WHERE SFIEDBD = recDefSelCrit.SFIEDBD
    AND SFIEREC = recDefSelCrit.SFIEREC
    AND SFIENAME = recDefSelCrit.SFIENAME
    ;
    END IF;
    IF ( recDefSelCrit.DATA_TYPE = 'DATE'
    AND recDefSelCrit.SFIEDEFSELCRIT IN ( StartsWith, StartsNotWith, RWLike, NotLike, EndsWith, EndsNotWith )
    ) THEN

    UPDATE ish.SFIELD
    SET SFIEDEFSELCRIT = EqualTo
    WHERE SFIEDBD = recDefSelCrit.SFIEDBD
    AND SFIEREC = recDefSelCrit.SFIEREC
    AND SFIENAME = recDefSelCrit.SFIENAME
    ;
    END IF;
    IF ( recDefSelCrit.DATA_TYPE = 'BLOB' AND recDefSelCrit.SFIEDEFSELCRIT NOT IN ( NotSelected, Empty, NotEmpty )
    ) THEN

    UPDATE ish.SFIELD
    SET SFIEDEFSELCRIT = NotEmpty
    WHERE SFIEDBD = recDefSelCrit.SFIEDBD
    AND SFIEREC = recDefSelCrit.SFIEREC
    AND SFIENAME = recDefSelCrit.SFIENAME
    ;
    END IF;
    END LOOP;
    END;
    END;
    /
    Thanx beforehand!

    greetz Coen Dunnink
    The Netherlands

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    On a normal operating system, temp would appear to be _always_ full and it will get as big as you want (i.e. you define its size and how it will grow -- you can constraint that if you want). They are many many things that go into temp (for example, sorts when they dont fit in the sort_area_size).

    How many rows are there in: ish.sviewfieldconfigdetail ? Cursor curOrderSVFCD is *probably* the culprit, since it is probably doing a sort which doesn't fit into memory and its spanning it to disk (to temp). I _dont_ see a need for this whole block anyway ? What happens when you do something like:
    Code:
    update ish.sviewfieldconfigdetail i
       set svfcorder = ( select count( * ) over( partition by svfcdrec, svfcdid, svfcdcou )
                           from ish.sviewfieldconfigdetail
                          where svfcddbd = 'UM'
                            and nvl( svfcdorder, 0 ) <> 0
                            and svfcdrec = i.svfcdrec
                            and svfcdid = i.svfcdid
                            and svfcdcou = i.svfcdcou
                            and svfcdorder = i.svfcorder )
    Your task is to test this, since I haven't (lack of table def, data, etc..). But I see that most of the operations you're doing in this code can be done in SQL directly (without sorting or whatsoever).

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    make sure to have autoextensible set to NO for the datafiles in TEMP since the tablespace space gets reused.
    Last edited by The_Duck; 02-21-06 at 13:55.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    If you fail to create a temp tablespace locally managed with tempfiles, vice datafiles, then SMON can fail to manage the size of temp segments. In 9i and higher, always use syntax "create temporary tablespace <name> tempfile .... I have not backwards tested this solution to 817 or earlier.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

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