How to reduce the size of tablespace files that are clearly not fully used?

Posted on 2007-04-02
Last Modified: 2008-03-10
I'm working with a test database on my laptop and I had to abort a couple of large inserts and index builds.  A few of my DBF files are very large.  In particular,

TEMP01.DBF - 8Gigs+
UNDOTBS01.DBF - 5.5Gigs
USERS01.DBF - 3.7Gigs

I don't have exactly which tables and indexes are in which tablespaces, but I know I'm not using that much space.  Is there any way to reduce the sizes of those files?

Thanks buckets
Question by:DalTXColtsFan

Assisted Solution

gvsbnarayana earned 60 total points
ID: 18842550
  You can find out the exact space used by each of the table/index by querying dba_extents view. Sometimes, because of the way the index/table is fragmented accross the datafile, you may not be able to resize the datafile to the size of the segment.
The following query may show you the smallest data file size possible on for each of the datafile and you can re-size to the smallest size.
column value new_val blksize
select value from v$parameter where name = 'db_block_size'

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)


Author Comment

ID: 18842745
Thanks gvs this helped.

3 questions:

1.  My Undo tablespace (UNDOTBS010.dbf) is maxed out at about 5.4 Gigs (5330 blocks at 8 megs each block) and according to your query it's maxed out and can't be reduced.   Does that mean I'm completely stuck?  There's no setting I can adjust to make that file smaller?

2.  My TEMP01.DBF file did not even appear in that list, and it's about 8 Gigs all by itself.

3.  For the ones that your query reported *can* be reduced, how do I do it?  Is it a SQL statement?  A few of them had trimmable space.

LVL 28

Accepted Solution

Naveen Kumar earned 70 total points
ID: 18842780
I dont think you can reduce the size of the database tablespace files. Add a new tablespace and a data file with the space you want to allocate ( say 2 GB is enough for you ) and then drop these tablespaces from oracle database and then remove those files which belong to those tablespaces from the OS.

Just verify once all the objects present in the tablespace which you are going to drop because if you drop by mistake you cannot get it back.

select tablespace_name, segment_name, bytes/1024/1024 size_in_mb
from dba_segments
where tablespace_name in ('USERS01','TEMP01');

select * from dba_data_files
where tablespace_name in ('USERS01','TEMP01');  -- this is to see which files are for data tablespace which are used for storing database data

select * from dba_temp_files
where tablespace_name in ('USERS01','TEMP01'); -- this is to see which files are for TEMP tablespaces

If at all you dont want to drop existing tablespaces and create new ones, then identify unwanted tables/indexes which are sitting in the tablespaces which you wanted to cleanup and then drop them if tthey are not needed. This will get you free space without any addition of new space.

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


Author Comment

ID: 18842999
Don't know if this helps, but I ran the query

select tablespace_name, segment_name, bytes/1024/1024 size_in_mb
from dba_segments order by tablespace_name

And saw that there was one segment in the tablespace UNDOTBS1 that is over 5 Gigs all by itself:  The segment name is _SYSSMU3$.  The SEGMENT_TYPE is TYPE2 UNDO.

No other segment associated with that tablespace is even ten percent as big.

This mean anything?

Assisted Solution

harry_hendrata earned 70 total points
ID: 18843140
you may want to refer to this link on how to reduce datafile; however if the datafile is fragmented, you may need export the table, and drop the table and resize the datafile, then import back the data.


Author Comment

ID: 18843905
Just to update on the progress I’ve made, I found out that at least for the file TEMP01.DBF I can do this:

ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;

It seems, and someone please correct me if I’m wrong, that nothing is actually “stored” in the TEMP tablespace.  It’s basically an extra paging file used by Oracle for HASH joins, sorts, and whatever it needs extra memory for.  Also correct me if I’m wrong but it seems like a higher SORT_AREA_SIZE initialization parameter value will decrease the use of this temp tablespace and increase query speed (hence the reason for having a ton of RAM on the database server).

So this one has been resized, but will probably grow right back to the size it was next time I try to join a 24,000,000 row table to a 22,000 row table.  As a matter of fact, I probably hosed myself because the next time I run a query that big it will probably be slower because Oracle has to increase the size of this file before it can even RUN the bloody thing!

The tablespace USERS had a table with 46,000,000 rows in it that I have truncated, so now there’s about 3 Gigs of space in it that I’m not using.  The code behind this link (thanks Harry):  Had the answer.

I ran the query ALTER DATABASE DATAFILE 'C:\oracle\product\10.2.0\oradata\ikb20063\USERS01.DBF' RESIZE 60M; and it WORKED.  Again, I probably hosed myself because when I go to re-insert all of that data it’ll be slower, but hey, I’ll take a hosing in the name of a learning experience!

The tablespace UNDOTBS1 has 15 segments, and one of these segments is over 5 Gigs all by itself.  None of the others are anywhere NEAR that big.  I *would* like to know how to reduce the size of this one if possible because I’ve learned (the HARD way!) to, when the business logic permits, do a commit every 10,000 or so rows because if you try to add or update 46,000,000 rows in one fell swoop the whole kit and caboodle gets written to the undo tablespace which exponentially increases execution time.  I’m sure that the one 5-Gig segment is from when I tried to create 46,000,000 rows without doing a commit until the end.  I aborted the process before it finished too.

I'm going to go ahead and close this question, but if anyone happens to know how to delete the ONE segment in the UNDO tablespace, I'd appreciate the info!

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question