Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Red face Unanswered: Help on oracle tablespace report.

    Need help here. Sample report from oracle below.

    Tablespace Report for Database IMDB on Host ZZZ01
    ================================================== =====
    ------------------------------------------------------
    Tablespace Name EFAULT_UNDO_TBS
    Allocated Space for tablespace (MB) :198
    Free Space for tablespace (MB) :197
    Used (MB) :1
    % Free space in tablespace :19
    ------------------------------------------------------
    Tablespace Name :WAKINDEXES
    Allocated Space for tablespace (MB) :1000
    Free Space for tablespace (MB) :931
    Used (MB) :69
    % Free space in tablespace :23
    ------------------------------------------------------

    I wanna grep for the line "% Free space in tablespace" and report if it's less than 20. Ok that's easy but i need the report to be like one below. Just need the Host, Database name and Tablespace name.

    Tablespace Report for Database IMDB on Host ZZZ01
    ================================================== =====
    Tablespace Name EFAULT_UNDO_TBS
    % Free space in tablespace :19

  2. #2
    Join Date
    Oct 2004
    Posts
    4
    Is capital D being replaced by a biggie smile?

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Why not just run an SQL script like this one:
    Code:
    column "TOTAL MB" format 9999,999,999.9
    column "TOTAL USED" format 9999,999,999.9
    column "TOTAL FREE" format 9999,999,999.9
    column "PCT FREE" format 999.9
    set feedback on;
    
    Select B.Tablespace_Name, B.Total/1048576 "Total Mb",
           Nvl((B.Total-A.Total_Free)/1048576, 100) "Total Used",
           Nvl(A.Total_Free/1048576, 0) "Total Free",
           Nvl((A.Total_Free/B.Total) * 100, 0) "Pct Free"
    From (
            Select Tablespace_Name, Sum(Bytes) Total_Free
            From Sys.Dba_Free_Space
            Group By Tablespace_Name ) A
        , (
            Select Tablespace_Name, Sum(Bytes) Total
            From Sys.Dba_Data_Files
            Group By Tablespace_Name ) B
    Where A.Tablespace_Name(+) = B.Tablespace_Name
    Order By 5,1;

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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