Solved

how to decrease datafile

Posted on 2001-08-06
6
2,378 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 200 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

749 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