How to find out space used by each and every user without analyzing schema or database
Printable View
How to find out space used by each and every user without analyzing schema or database
And the quiz goes on... and on.. and on...
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
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
Why don't you want to analyze?
defined "used space".
It's the 1's - the 0's can be used for something else.Quote:
Originally posted by marist89
defined "used space".
1) Measure current space used and free.
2) Drop user in question.
3) Look at new freespace.
:D
MH
Try this:
The above SQL code gives nearest used space with respect to blocks.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;
/
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