Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    84

    Unanswered: Locally Managed Temporary Tablespace in 9i

    Hi,
    I wish to know how I can get information on the %age usage of temporary tablespace(Locally managed and hence tempfiles and not datafiles) in Oracle 9i.
    Other than v$sort_usage and v$sort_segment is there any other view that can help me?
    Thanks.

  2. #2
    Join Date
    Sep 2003
    Posts
    156

    Re: Locally Managed Temporary Tablespace in 9i

    Originally posted by preetikate
    Hi,
    I wish to know how I can get information on the %age usage of temporary tablespace(Locally managed and hence tempfiles and not datafiles) in Oracle 9i.
    Other than v$sort_usage and v$sort_segment is there any other view that can help me?
    Thanks.
    Do you use any GUIs like TOAD?
    rgs,

    Ghostman

  3. #3
    Join Date
    Jan 2004
    Posts
    84
    I use OEM to see teh space usage . But I want to have some shell script that will alert on alarming temprary tablespace usage(locally managed ones).

  4. #4
    Join Date
    Jan 2004
    Posts
    84
    waiting for suggestions!!

  5. #5
    Join Date
    Jan 2004
    Posts
    370
    Preetikate,

    Have you checked out dbms_space.space_usage() ?

    I've not used it myself but it apparently reports on space usage within locally managed tablespaces.

  6. #6
    Join Date
    Jan 2004
    Posts
    84
    Thanks ,
    but when I describe the package I see the following
    SQL> DESC SYS.DBMS_SPACE.SPACE_USAGE;
    Parameter Type Mode Default?
    ------------------ -------- ---- --------
    SEGMENT_OWNER VARCHAR2 IN
    SEGMENT_NAME VARCHAR2 IN
    SEGMENT_TYPE VARCHAR2 IN
    UNFORMATTED_BLOCKS NUMBER OUT
    UNFORMATTED_BYTES NUMBER OUT
    FS1_BLOCKS NUMBER OUT
    FS1_BYTES NUMBER OUT
    FS2_BLOCKS NUMBER OUT
    FS2_BYTES NUMBER OUT
    FS3_BLOCKS NUMBER OUT
    FS3_BYTES NUMBER OUT
    FS4_BLOCKS NUMBER OUT
    FS4_BYTES NUMBER OUT
    FULL_BLOCKS NUMBER OUT
    FULL_BYTES NUMBER OUT
    PARTITION_NAME VARCHAR2 IN Y

    How do I find out about temporary tablespace from this?

  7. #7
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    You can check the following views for more information..

    V$TEMP_EXTENT_MAP :
    This view displays the status of each unit for all LOCALLY MANAGED temporary tablespaces.

    V$TEMP_SPACE_HEADER:
    This view displays aggregate information per file per LOCALLY MANAGED
    temporary tablespace regarding how much space is currently being used and how much is free as identified in the space header.

    V$TEMPSEG_USAGE
    This view describes temporary segment usage.


    See the 9i database reference guide in otn to know more about these views and other views related to temporary tablespaces.
    Regards
    Suneel

Posting Permissions

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