actual data in the database

[ayushsingh]

Hi,
How can I find the actual data is in the database, Including indexes.

Thanks in advance.

[jovery]

Do you mean objects? if so the following views will help

DBA_OBJECTS, DBA_SEGMENTS

Regards

[arjun]

can u be more specific on the term "actual data"?

[ayushsingh]

I mean I can have datafiles of 10GB but the data in it would be just 7GB . So how to find the amount of that data.

[james_cc]

YOu can query basically starting from datafile level to table/index level.

v$datafile ---> v$tablespace ---> user_tables(dba_tables) or dba_indexes

Then of course look at all data you want. Have fun.

[adewri]

Originally posted by ayushsingh
I mean I can have datafiles of 10GB but the data in it would be just 7GB . So how to find the amount of that data.

Well what i understand is that you want to see how much free space is available...

Use DBA_FREE_SPACE and dba_segments to find that out...

HTH

[peach_partner]

SELECT      tablespace_name TS, count('x') "NO_BlockID", sum(bytes)/1024/1024 "FreeSpaces"
FROM         dba_free_space
GROUP BY tablespace_name

[lezanic]

To have space use in your tablespaces :
set lines 250
set pages 200

SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes,0)) / a.bytes * 100, 0), '990.00') "Used %" ,
round(NVL((a.bytes - NVL(f.bytes,0))/1024/1024, 0),2) ||'/'||NVL(a.bytes/1024/1024, 0) "Used (M)"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+);

---------------------------------------
To Know space really used by a table (you may execute statistics)
select table_name, blocks*8 || ' Ko' "Used" , EMPTY_BLOCKS *8 ||' Ko' "Free" from user_tables;