Results 1 to 7 of 7

Thread: Used Disk Space

  1. #1
    Join Date
    Jan 2003
    Posts
    54

    Unanswered: Used Disk Space

    Is there a way to find out the disk space used by a list of records.

    Example : if I have different users recorded in the database,
    with data spread out in different tables, how could I know the space used
    from the database by a specific user ?

    Thanks for any idea, advise, help.
    Pierre.
    Pierre (Pl-Arts)

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

    Cool

    You could try using the DBMS_SPACE PL/SQL utility.

  3. #3
    Join Date
    Jan 2003
    Posts
    54
    Can I use this by programming ?
    I need this information inside my application.

    Thanks for your help.
    Pierre (Pl-Arts)

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

    Cool

    You could create a procedure like:


    Create Or Replace Procedure User_Space (Usr Varchar2)
    Is

    Cursor Tab_Csr (P_Owner Varchar2) Is
    Select Table_Name From All_Tables
    Where Owner = P_Owner;

    Cursor Idx_Csr (P_Owner Varchar2) Is
    Select Index_Name From All_Indexes
    Where Owner = P_Owner;

    Usr_Username Varchar2(32);
    Tot Number;
    Totb Number;
    Unu Number;
    Unub Number;
    Last_Used_File_Id Number;
    Last_Used_Block_Id Number;
    Last_Used_Block Number;
    Usr_Tab_Tot Number;
    Usr_Tab_Totb Number;
    Usr_Idx_Tot Number;
    Usr_Idx_Totb Number;

    Begin
    Usr_Username := UPPER(usr);
    Usr_Tab_Tot :=0;
    Usr_Tab_Totb :=0;
    For Tab In Tab_Csr(Usr_Username)
    Loop
    Dbms_Space.Unused_Space (
    Segment_Owner => Usr_Username
    , Segment_Name => Tab.Table_Name
    , Segment_Type => 'TABLE'
    , Total_Blocks => Tot
    , Total_Bytes => Totb
    , unused_blocks => Unu
    , unused_bytes => Unub
    , last_used_extent_file_id => Last_Used_File_Id
    , last_used_extent_block_id => Last_Used_Block_Id
    , last_used_block => Last_Used_Block
    );
    Usr_Tab_Tot := Usr_Tab_Tot + Tot;
    Usr_Tab_Totb := Usr_Tab_Totb + Totb;
    End Loop;
    Usr_Idx_Tot :=0;
    Usr_Idx_Totb :=0;
    For Idx In Idx_Csr(Usr_Username)
    Loop
    Dbms_Space.Unused_Space (
    Segment_Owner => Usr_Username
    , Segment_Name => Idx.Index_Name
    , Segment_Type => 'INDEX'
    , Total_Blocks => Tot
    , Total_Bytes => Totb
    , unused_blocks => Unu
    , unused_bytes => Unub
    , last_used_extent_file_id => Last_Used_File_Id
    , last_used_extent_block_id => Last_Used_Block_Id
    , last_used_block => Last_Used_Block
    );
    Usr_Idx_Tot := Usr_Idx_Tot + Tot;
    Usr_Idx_Totb := Usr_Idx_Totb + Totb;
    End Loop;
    Dbms_Output.Put_Line('*+ --------------------------------------');
    Dbms_Output.Put_Line('User '||Usr_Username||' Statistics:');
    Dbms_Output.Put_Line(' Blocks Used For Tables: '||Usr_Tab_Tot);
    Dbms_Output.Put_Line(' Bytes Used For Tables: '||Usr_Tab_Totb);
    Dbms_Output.Put_Line(' Blocks Used For Indexes: '||Usr_Idx_Tot);
    Dbms_Output.Put_Line(' Bytes Used For Indexes: '||Usr_Idx_Totb);
    End;
    /

  5. #5
    Join Date
    Jan 2003
    Posts
    54
    Thanks a lot,

    I am not familiar with this type of programming. (syntax)
    I use simple SQL queries, Javascript, ColdFusion, HTML.

    Is this a SQL procedure ?

    Where can I find a documentation to understand well the coding ?
    Pierre (Pl-Arts)

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Originally posted by Plarde
    Thanks a lot,

    I am not familiar with this type of programming. (syntax)
    I use simple SQL queries, Javascript, ColdFusion, HTML.

    Is this a SQL procedure ?

    Where can I find a documentation to understand well the coding ?
    This is a PL/SQL Stored procedure.
    In order for the procedure to return values to a Java program, you would have to add 'OUT' parameters to the procedure and remove the 'dbms_output' statements, something like this:

    Create Or Replace Procedure User_Space
    (Usr Varchar2
    ,P_Usr_Tab_Tot OUT Number
    ,P_Usr_Tab_Totb OUT Number
    ,P_Usr_Idx_Tot OUT Number
    ,P_Usr_Idx_Totb OUT Number)

    <<.. rest of code ..>>

    P_Usr_Tab_Tot := Usr_Tab_Tot;
    P_Usr_Tab_Totb := Usr_Tab_Totb;
    P_Usr_Idx_Tot := Usr_Idx_Tot ;
    P_Usr_Idx_Totb := Usr_Idx_Totb;

    End;
    /

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

Posting Permissions

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