?
Solved

how to decrease datafile

Posted on 2001-08-06
6
Medium Priority
?
2,391 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:konektor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Expert Comment

by:fva
ID: 6355195
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
 
LVL 3

Expert Comment

by:myerpz
ID: 6355196
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
 
LVL 3

Accepted Solution

by:
p_yaroslav earned 400 total points
ID: 6355201
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 4

Expert Comment

by:fva
ID: 6355204
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
 

Expert Comment

by:gbauman
ID: 6423333
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
 
LVL 9

Author Comment

by:konektor
ID: 6746570
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

800 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