Roman wrote:
> Am Fri, 09 Jul 2004 08:06:06 -0700 schrieb jose luis fernandez diaz:
>
>
>>I have a lot of space in data files binds to tablespace
>>"CUSTOM_TBS_ARTICULOS_IX_01", but it seems that Oracle doesn't see it.
>>What is the problem ?
>
>
> Well, there are 2 possibilities:
>
> 1. All the space is used by other objects
> 2. There is a lot of freespace but its fragmented and the extents don't
> fit in the empty gaps.
>
> If you are using DMTS you can try:
> alter tablespace <name> coalesce;
>
> When you have actually verified that there is a lot of freespace and its
> fragmented the only way to defragment it is a tablespace reorg.
>
> However storage is cheap and time usually short. therefore you might just
> whant ot add an other datafile?
>
> All the best,
>
> Roman
This query will give you a quick look at your tablespaces and how much
space you have available.
Good Luck,
aja
- = = = = c u t h e r e = = = = = =
SELECT tablespace_name,
ts_#,
num_files,
sum_free_mbytes,
count_blocks,
max_mbytes,
sum_alloc_mbytes,
DECODE(sum_alloc_mbytes,0,0,100 * sum_free_mbytes /
sum_alloc_mbytes ) AS pct_free
FROM (SELECT v.name AS tablespace_name,
ts# AS ts_#,
NVL(SUM(bytes)/1048576,0) AS sum_alloc_mbytes,
NVL(COUNT(file_name),0) AS num_files
FROM dba_data_files f,
v$tablespace v
WHERE v.name = f.tablespace_name (+)
GROUP
BY v.name,
ts#),
(SELECT v.name AS fs_ts_name,
ts#,
NVL(MAX(bytes)/1048576,0) AS max_mbytes,
NVL(COUNT(BLOCKS) ,0) AS count_blocks,
NVL(SUM(bytes)/1048576,0) AS sum_free_mbytes
FROM dba_free_space f,
v$tablespace v
WHERE v.name = f.tablespace_name(+)
GROUP
BY v.name,
ts#)
WHERE tablespace_name = fs_ts_name
ORDER
BY tablespace_name;
Received on Fri Jul 09 2004 - 14:12:10 CDT
