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.

You could try using the DBMS_SPACE PL/SQL utility.

Can I use this by programming ?
I need this information inside my application.

Thanks for your help.

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;
/

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;
/

PL/SQL Documentation in:

http://download-west.oracle.com/docs...a89856/toc.htm

and:

http://download-west.oracle.com/docs...a89852/toc.htm

and:

http://download-west.oracle.com/docs...a90125/toc.htm