As of Oracle9i, using locally managed tablespaces
instead of dictionary-managed tablespaces solves this problem when disk space is not the issue??”
the maximum number of extents in an LMT is unlimited.
Used and Free Space by Tablespace and Datafile
The following SQL*Plus script breaks down the space usage of each tablespace, which is further
broken down by datafile within each tablespace. This is a good way to see how space is used
and extended within each datafile of a tablespace, and it may be useful for load balancing when
you??™re not using ASM or other high-availability storage.
--
-- Free space within non-temporary datafiles, by tablespace.
--
-- No arguments.
-- 1024*1024*1000 = 1048576000 = 1GB to match OEM
--
column free_space_gb format 9999999.999
column allocated_gb format 9999999.999
column used_gb format 9999999.999
column tablespace format a12
column filename format a20
select ts.name tablespace, trim(substr(df.name,1,100)) filename,
df.bytes/1048576000 allocated_gb,
((df.bytes/1048576000) - nvl(sum(dfs.bytes)/1048576000,0)) used_gb,
nvl(sum(dfs.bytes)/1048576000,0) free_space_gb
from v$datafile df
join dba_free_space dfs on df.file# = dfs.file_id
join v$tablespace ts on df.ts# = ts.ts#
group by ts.name, dfs.file_id, df.name, df.file#, df.bytes
order by filename;
TABLESPACE FILENAME ALLOCATED_GB USED_GB FREE_SPACE_GB
------------ -------------------- ------------ ---------- -------------
DMARTS +DATA/dw/datafile/dm .
Pages:
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356