Help - Search - Members - Calendar
Full Version: Release tablespace unused space
Oracle DBA Forums > Oracle > Oracle Forum
paulogervasio
Hi

I have a problem...

I created a tablespace called my_ts:

CODE
CREATE TABLESPACE my_ts DATAFILE 'C:\Oracle\oradata\db\my_ts.dbf' SIZE 5M  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
ALTER DATABASE DATAFILE  'C:\Oracle\oradata\db\my_ts.dbf' AUTOEXTEND ON;


Its was sucessfully created and my_ts.dbf file has 5MB

charging with data...

CODE
create table big_table tablespace my_ts as select * from dba_objects;
select * from big_table;
begin
  for i in 1..10 loop
    insert into big_table select * from dba_objects;
  end loop;
end;



Now the my_ts.dbf file has 90MB

Now I want drop this table:
CODE
drop table big_table purge;


And my tablespace file still has 90MB.

I already tried to restart the database but doesn't works...

Someone knows how can I reduce the size to the original (5MB)?

Thnks.
Paulo.


burleson
Hi Paulo,

The initial size of a tablespace is the size of the underlying data file.

The initial size of a table is also governed by the MINEXTENTS parameter and the size of your data blocks db_block_size.

When you create a tablespace without the SIZE parameter, the system default data file size size is used.

If you want a tablespace to grow as tables grow, use the "autoextend on" option (recommended):

CODE
create tablespace
   myusers
datafile  
  ‘/u01/app/oracle/oradata/booktst_users_01.dbf’
size 50m
blocksize 32k
maxsize 100m
autoextend on;


Note that we used 50m to indicate that we wanted to create the tablespace 50 Megabytes in size. You can also use the K symbol for Kilobytes, and the G symbol for Gigabytes.

In this example, the "create tablespace" command creates a tablespace that will auto extend in increments of 10 megabytes until it reaches a maximum size of 100 megabytes. We recommend you use auto extend on all tablespaces for any production database.

Please read:

http://www.dba-oracle.com/t_shrink_tablespace.htm

*************************************
>> Now the my_ts.dbf file has 90MB

How are you measuring the space used?

A tablespace will only expand when you add a new datafile . . .

*************************************
>> Now I want drop this table:

And the extents remain! Oracle never shrinks un-used space within a table unless you free it up by "coalense" or "deallocate unused space" or "shrink space":

Please read:

http://www.dba-oracle.com/t_alter_table_sh...ace_command.htm

http://www.dba-oracle.com/t_coalesce_deall...nused_space.htm

But remember, the size of the underlying data file always remains dedicated to Oracle . . .
paulogervasio
I found the answer!
Itīs just to resize the tablespace to current space used!!!

with

CODE
select tablespace_name, bytes/(1024*1024) as total_size  from dba_data_files;


You get the total size from the tablespace 90M

and with

CODE
select tablespace_name, sum(bytes)/(1024*1024) as free_space  from dba_free_space group by tablespace_name;


you get the free space, that will be removed! 85M

Now itīs just substract the free_space total_from total_size and resize the tablespace.
we assume that the total used space is 15m, then:

CODE
ALTER database datafile '/my_orcla_dir/my_ts.dbf' RESIZE 5m;


And thatīs all!

If the new size does not support the existing data in the tablespace, the command will not work.

Thks.
burleson
Hi Paulo,

***********************************************
>> select tablespace_name, bytes/(1024*1024) as total_size from dba_data_files;

that doe not book rigth! It's only the size of the data files.

Don't yu ant total space used within the data files.

Look at he dbms_space package:

http://dba-oracle.com/googlesearchsite_pro...mp;q=dbms_space


************************************************
>> Itīs just to resize the tablespace to current space used!!!

Great! Interesting approach.

But when if the data files to small o keep (say only 5% it is is used) or too big at the filesystem level (only 1% of he filesystem is free)?

I thought that a coalesce did that.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.