Freeing up Disk space

[former_member204618]

Hi all,

I have just done an offline table reorg for all tablespaces using brtools and oracle 10g, with the intention of dropping some datafiles and allocating them elsewhere.

But even though I now have 90G free I am unable to remove any datafiles.

How do I remove these datafiles or resize them properly?  I've read countless postings but none of them result in the space saving I can see in dbacockpit.

Thanks

Craig

[fidel_vales · Accepted solution]

Hi,

the best would have been planne it properly.

You can only drop the last datafile of a tablespace if it is empty.

What you should have done is to create a new tablespace (smaller), do the reorganization (of all tables) drop the tablespace and rename the target one as the initial.

now, you are left only with resizing the datafiles (if you are lucky there is free space at the end)

[former_member204618 · Accepted solution]

Hi Fidel,

That doesn't really offer any help.  As I have said I do not have the file system space to create a new tablespace to reorg into otherwise I would have done that already.

There must be a way of doing this, if oracle knows that it's only using 46% then there must be a way of releasing the remainder of that space, without needing even more space to do it.

Cheers

Craig

[fidel_vales · Accepted solution]

Hi,

I'm sorry to bring the bad news, but there is no other way (or I will be very happy to be proved wrong) to recover that space.

Oracle does know it has free space, it will use that free space by further object growth.

Resume:

• You cannot drop a datafile unless it is empty (Dropping Datafiles) and (here It seems I was mistaken, thanks) it cannot be the first or only datafile.

• You cannot "empty" a datafile (at least easily as it would involve to export the objects that have a part on that datafile, drop those objects, drop the datafile and import them again)
• You can resize a datafile as mentioned but only if there is no object on the space to be freed (Managing Datafiles and Tempfiles)

For that reason I said that the best would have planned it previously

[former_member204618 · Accepted solution]

Thanks All,

Looks like I am in a chicken & egg situation.  Never mind I'll figure something out.

Cheers

Craig

[Reagan]

How do I remove these datafiles or resize them properly?

The suggestion form Yves is one option. The other options is that you will need to do a tablespace reorg using export import method. You basically export the tablespace and drop it and the recreate it. When you extend the tablespace you just need to add datafiles with a small size initially and with auto extend mode ON. You then start the import.

Regards

RB

[ACE-SAP]

Hello Craig,

As the segments within a tablespace are spread over all the datafiles a reorg will not free a complete datafile.

If you are wishing to recover some free space you can use the here under script to reduce the size of the datafiles.

It will recover all the free space over the datafile high water mark.

To keep some room for future tablespace/segement growth it could be clever to keep some freespace within some datafiles.

Best regards

821687 - FAQ: Space utilization and fragmentation in Oracle

If a tablespace requires a large amount of space because the data file is too large, you can reduce the size of the data file by using RESIZE:
ALTER DATABASE DATAFILE '<file>' RESIZE <new_size>;

Select 'ALTER database datafile ''' || File_name || ''' resize ' || smallest_Mb || ' M;' FROM (
  SELECT
       file_name as File_name,  ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) as smallest_Mb,  ceil( blocks*C.VALUE/1024/1024) as currsize_Mb,
       ceil( blocks*C.VALUE/1024/1024) - ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) as Shrink_Mb,
       a.autoextensible as Auto_extend , a.maxbytes/1024/1024 as Extend_max_MB
  FROM dba_data_files a,
       (select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b,
       (select value from v$parameter where name = 'db_block_size') C
  WHERE a.file_id = b.file_id(+) AND a.file_name in (select FILE_NAME from  dba_data_files)
) WHERE 1=1;

     

[former_member204618]

Hi Yves,

I ran your script but it produced the same as others I have ran.  It resizes data files as expected but only saves 3.8GB I have 90GB free!!!

Surely there must be a way of doing this without requiring even more file system space to do an export import method or a reorg into a new TableSpace!

The reason I am trying to do this is because in production I have 500GB free and need a way of releasing that space without requiring even more file system space.

Thanks

Craig