How to find out Tablespace full or not? - URGENT..
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to find out Tablespace full or not? - URGENT..

Hybrid View

  1. #1
    Join Date
    Jun 2002
    Posts
    88
    Hi,

    In which data dictionary view and dynamic performance view
    find out whether tablespace is full or not?

    In specific data dictionary view or dynamic performance view
    is there.

    Thanks,
    Iyyappan.M

  2. #2
    Join Date
    Jan 2002
    Posts
    48
    Hi, run this query. It will show tablespace, total mb, free mb, free % and largest segment size. Largest segment size will indicate how fragmented your tablespace is and how big the largest available chunk of space is.

    SELECT a.tablespace_name tablespace,
    ROUND(SUM(a.total1)/1024/1024, 1) "Total Mb",
    ROUND(SUM(a.total1)/1024/1024, 1)-ROUND(SUM(a.sum1)/1024/1024, 1) "Used Mb",
    ROUND(SUM(a.sum1)/1024/1024, 1) "Free Mb",
    ROUND(SUM(a.sum1)/1024/1024,1)*100/ROUND(SUM(a.total1)/1024/1024,1) "Free %",
    ROUND(SUM(a.maxb)/1024/1024, 1) "Largest Segment Size Mb"
    FROM (SELECT tablespace_name,
    0 total1,
    SUM(bytes) sum1,
    MAX(bytes) maxb,
    COUNT(bytes) cnt
    FROM dba_free_space
    GROUP BY tablespace_name
    UNION
    SELECT tablespace_name,
    SUM(bytes) total1,
    0,
    0,
    0
    FROM dba_data_files
    GROUP BY tablespace_name) a
    GROUP BY a.tablespace_name;

  3. #3
    Join Date
    Jun 2002
    Posts
    88

    How to find out tablespace is full or not? - Urgent(Temporary tablespace)

    Hi,

    How to find out the temporary tablespaces whether full or not?

    In v$temp_space_header are other views are there.

    Awaiting ur reply,

    Thanks,
    Iyyappan.M

  4. #4
    Join Date
    Feb 2002
    Posts
    267
    hi folks,

    there is an easy way to find out the free space and used space in a tablespace.

    query sm$ts_free to find out % free space in a tablespace
    sm$ts_used to find out % used space in a tablespace

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    sys@DSIM>select * from V$TEMP_EXTENT_POOL;
    
    TABLESPACE_NAME                   FILE_ID EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO
    ------------------------------ ---------- -------------- ------------ ------------- ----------- ------------ ---------- ------------
    TEMP                                    1          15999           28        255984         448   2097020928    3670016            1

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select * from v$sort_segment;

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