DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: finding space used by user level?

  1. #1
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    182

    finding space used by user level?

    How to find out space used by each and every user without analyzing schema or database
    J Gangadhar

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    And the quiz goes on... and on.. and on...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    Analyzing the statistics does not increase the size of the database objects or database except that this would add or update some records into data dictionary objects.U can use the below command to find the size of database object owned by each user or schema.

    select sum(bytes)/1024/1024 from user_segments; --for the user loged in.

    select sum(bytes)/1024/1024 from dba_segments where owner='USERNAME';

    regards
    anandkl
    anandkl

  4. #4
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    182
    Anand i agree you

    but the query which you have mentioned shows total space occupied by each schema ,
    out of which i want only used space from total space
    J Gangadhar

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Why don't you want to analyze?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    defined "used space".
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by marist89
    defined "used space".
    It's the 1's - the 0's can be used for something else.

  8. #8
    Join Date
    Jan 2001
    Posts
    3,134
    1) Measure current space used and free.
    2) Drop user in question.
    3) Look at new freespace.




    MH
    I remember when this place was cool.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try this:
    Code:
    set serveroutput on size 1000000
    DECLARE
       used_blocks       NUMBER ;
       STMT              VARCHAR2(500);
    BEGIN
       DBMS_OUTPUT.PUT_LINE
         (RPAD('TABLE NAME',30)||' TYPE  TOTAL BLOCKS  USED BLOCKS FREE BLOCKS');
       FOR CREC IN
          (SELECT segment_name , segment_type, blocks
             FROM user_segments
             where segment_type in ('TABLE') )
       LOOP
          STMT := 'select /*+ parallel(a,8) */'||
                  'count(distinct(dbms_rowid.rowid_block_number(rowid))) from '||
                  crec.segment_name ||' a';
          EXECUTE IMMEDIATE STMT INTO USED_BLOCKS ;
          DBMS_OUTPUT.PUT_LINE(RPAD(crec.segment_name,30)||' '||
                      rpad(crec.segment_type,6)||' '||
                      rpad(TO_CHAR(crec.blocks),13)||' '||
                      rpad(TO_CHAR(used_blocks),13)||' '||
                      rpad(TO_CHAR(crec.blocks - used_blocks),13) );
       END LOOP;
    END;
    /
    The above SQL code gives nearest used space with respect to blocks.

    You can change the code to get avg row len, and total number of rows.
    Multiplication of these 2 will give you approximate space usage by a table.
    As I said earlier, there is no SQL statement available to calculate exact space used by a table. If some one says he has, then I can prove how it is wrong.

    You must learn how to live in approximation.

    Tamil

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