Home » SQL & PL/SQL » SQL & PL/SQL » RE: Tablespace usage
RE: Tablespace usage [message #19630] Mon, 01 April 2002 23:37 Go to next message
dhnish
Messages: 26
Registered: March 2002
Junior Member
Hi

Just created a datafile having initial size of 128M, next 128M , maxsize 1920M ( autoextend on )

When I query from dba_data_files ,for this particular datafile its 134217728 bytes and blocks=65536
BUT when i query using the following script:

select a.file_name,sum(a.bytes)bytes_Used,sum(b.BYTES) bytes_allocated from dba_extents b,dba_data_files a
where a.TABLESPACE_NAME=b.TABLESPACE_NAME group by a.file_name;

which is suppose to give tablespace usage it shows for this particular filename its bytes used is 4,147,998,883,840
and bytes allocated is 17,358,411,776

Is this correct? Am I using the correct script?
Since this datafile was just created , the would not be any extents yet thus dba_extents should not have any details pertaining to this file.

Pls help.
Thank you
Re: RE: Tablespace usage [message #19638 is a reply to message #19630] Tue, 02 April 2002 04:21 Go to previous message
Cindy
Messages: 88
Registered: November 1999
Member
Try this:

CREATE OR REPLACE view bytes_free AS
(SELECT tablespace_name, sum(bytes) free_bytes
FROM user_free_space
GROUP BY tablespace_name)
/

CREATE OR REPLACE view bytes_used AS
(SELECT tablespace_name, sum(bytes) used_bytes
FROM user_segments
GROUP BY tablespace_name)
/

/home/hnj0205> more dais_ts_bytes.sql
column tablespace format a20
column free_ratio format 99,999.99
column total_bytes format 9,999,999,999,999
column bytes_used format 9,999,999,999,999
column bytes_free format 9,999,999,999,999
select f.tablespace_name tablespace,
nvl(u.used_bytes,0) + nvl(f.free_bytes,0) total_bytes,
nvl(u.used_bytes,0) bytes_used,
nvl(f.free_bytes,0) bytes_free,
((nvl(f.free_bytes,0) / (nvl(u.used_bytes,0) + nvl(f.free_bytes,0))) * 100 )
free_ratio
from owner_name.bytes_used u, owner_name.bytes_free f
where u.tablespace_name(+) = f.tablespace_name
/

P.S. To get answers in megabytes. Take bytes divided by megabytes (1048576)
Previous Topic: UTL_FILE permission
Next Topic: Re: connecting oracle 8i with d2k
Goto Forum:
  


Current Time: Fri Mar 29 10:10:00 CDT 2024