how to decrease datafile

HI all,
I have problem with decreasing size of datafile. It was filled up to 96 % so I moved largerest tables to another tablespace, but datafile for original tablespace did not reduce it's allocated space. If i had tried 'ALTER DATABASE DATAFILE /.../filename RESIZE xxx M' it allowed me reduce only a few space. I thing it is because of internal fragmentation in the tablespace. How to make the datafile become smaller.
I cannot create new datafile with new tablespace, move all tables to new tablespace and drop original datafile, because database in archivelog mode and I cannot change it.
thanks for help.
m.
LVL 9
konektorAsked:
Who is Participating?
 
p_yaroslavConnect With a Mentor Commented:
Hello!

Try:

1.export all objects, wich relate to datafile.
2. drop this objects;
3. alter tablespace TS coalesce (for merging tablespace extents)
4. import objects;


Also you may sequentially recreate objects in tablespace:
1.See tablespace map and find last object (at the end datafile) (if need i can post script for this operation).
2. Create this object in another tablespace (Create table T1 as select * from T2).
3. Drop object in initial tablespace.
4. alter tablespace TS coalesce
5. reduce datafile size
6. create table T2 as select * from T1
7. drop table T1

etc.......

Best regards!
Yaroslav.
0
 
fvaCommented:
The usual answer to this is:
Make a full backup, recreate your DB from scratch and reload data.

On 8i I do the following:
List the extents and the free space from that tablespace:
select segment_name,block_id,blocks from dba_extents where tablespace_name='YOURTABLESPACE'
union
select '<free>',block_id,blocks from dba_free_space where tablespace_name='YOURTABLESPACE'
order by block_id desc

You'll get an image of the organization within the tablespace.

Now the goal is to "move" the offending extents downwards. Look for the highest placed tables and issue:
alter table <the_table> move tablespace yourtablespace

If there is a free block lower, the table will usually be moved lower in the space map.

For indexes you issue:

alter index <the_index> rebuild tablespace yourtablespace

Check with the first query if you succeeded to move lower the extents.
Iterate the process.

After you are finished and you have created enough free space at the top, resize the datafile accordingly.

Note that "alter table ... move" invalidates all indices on that table and that you'll have to rebuild them after you finish resizing.

Good luck,
F.

0
 
myerpzCommented:
you could try coalescing the tablespace :-
    ALTER TABLESPACE tblsp_name COALESCE;

find out more about this command at http://technet.oracle.com/doc/server.804/a58225/ch4a.htm#1986899


hope this helps


0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
fvaCommented:
P.S. I presumed a single datafile per tablespace. If this is not your case, you'll have to make some adjustments to the process (probably to select by file no).
You might also want to run
"alter tablespace .... coalesce"
after moving, to force coalescing the free space without waiting for the background process to do it automatically.

F.
0
 
gbaumanCommented:
If you choose to drop all the tables and/or
indexes and them import these objects back
into the database.  

Avoid user complaints, do this on a weekend.

The status of other database objects may become invalid.  
You must recompile the codes, rebuild webdb context indexes, etc.

Thirdly, remember to disable the constraints and
triggers while importing the data.  And later
enable the constraints, triggers, etc.

0
 
konektorAuthor Commented:
i had to export DB, recreate temp tablespace, drop user, drop tablespaces, create tablespaces with uniform extents, import without data, move tables to suitable tablespaces, import data without indexes, create indexes, drop or recalculate statistics.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.