finding space used by user level?

[ganga]

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

[jmodic]

And the quiz goes on... and on.. and on...

[anandkl]

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

[ganga]

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

[slimdave]

Why don't you want to analyze?

[marist89]

defined "used space".

[DaPi]

Originally posted by marist89
defined "used space".

It's the 1's - the 0's can be used for something else.

[Mr.Hanky]

1) Measure current space used and free.
2) Drop user in question.
3) Look at new freespace.




MH

[tamilselvan]

Try this:

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