Tablespaces en datafiles

[230173]

Hi, I'm trying to get some information regarding free space on a AIX unix server.
I'm fairly new at this so don't laugh!!!
Tablespaces are just logical groups of datafiles.ok?
So if i take a look at the datafiles i see a size.
This size is the maximum size?
How do i know how much of this size is used?

 

[nugpot]

REM set pause off
REM set pages 0
column file_id format 999
column tablespace_name format a12
column file_name format a30
column bytes format 999

SELECT f.file_id,
d.tablespace_name,
d.file_name,
round((d.bytes/1048576),2) MB,
round(((d.bytes - sum(nvl(f.bytes,0))) / d.bytes) * 100,2) pct_used
FROM sys.dba_data_files d, sys.dba_free_space f
WHERE d.file_id = f.file_id(+)
AND d.tablespace_name in (SELECT distinct tablespace_name
FROM dba_segments)
GROUP BY d.tablespace_name, d.file_name,f.file_id, d.bytes

 

[lweng]

Query dba_free_space will give you how much free space in each tablespace. This is more meaningful than knowing how much space used in the datafile. If the tablespace does not have enough free space, the segments in the tablespace will fail to extend extents.

 

[rcurva]

Here's another query:

SELECT a.tablespace_name,
SUM(a.tots) Total_Size,
SUM(a.sumb) Total_Free,
SUM(a.sumb) * 100/SUM(a.tots) Pct_Free,
SUM(a.largest) Max_Free,
SUM(a.chunks) Chunks_Free
FROM (SELECT tablespace_name, 0 tots, SUM(bytes) sumb,
MAX(bytes) largest, COUNT(*) chunks
FROM dba_free_space
GROUP by tablespace_name
UNION
SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0
FROM dba_data_files
GROUP by tablespace_name) a
GROUP by a.tablespace_name

The max_free here is the largest contiguous space that can be allocated for an extent. If your tables next extents are larger that this value then your table/s will not extend. Which is a problem if you have a long running process involved.

 

[230173]

rcurva,

i'm trying to run this sql statement but this is the error i get:

an INTO clause is expected in this SELECT statement

 

[230173]

ignore the last message.
Everything works perfect.
Thanks a lot.