Solved

how to decrease datafile

Posted on 2001-08-06
6
2,367 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
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
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.

 
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

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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now