Home » RDBMS Server » Server Administration » Shrink tablespace and files (3 merged from same poster)
Shrink tablespace and files (3 merged from same poster) [message #279173] Wed, 07 November 2007 04:12 Go to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
How do i shrink a tablespace.
Re: Shrink tablespace [message #279174 is a reply to message #279173] Wed, 07 November 2007 04:20 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What do you mean by shrink tablespace?

first alter tablespace coalesce and then
You can resize data file in the tablespace by alter database ... resize

[Updated on: Wed, 07 November 2007 04:23]

Report message to a moderator

Re: Shrink tablespace [message #279175 is a reply to message #279174] Wed, 07 November 2007 04:23 Go to previous messageGo to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
i mean to reclaim some space. some tables were purged from this tablespace and we need to bring it to a smaller size.

Do you just need to do a resize of the datafiles in the tablespace?

some will resize to a smaller value other will not
Re: Shrink tablespace [message #279176 is a reply to message #279173] Wed, 07 November 2007 04:24 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

alter tablespace tablespace_name coalesce
Re: Shrink tablespace [message #279177 is a reply to message #279176] Wed, 07 November 2007 04:26 Go to previous messageGo to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
thanks, after coalesce then resize.
Re: Shrink tablespace [message #279240 is a reply to message #279177] Wed, 07 November 2007 08:07 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
You will not be able to shrink the datafile if you have objects at the end of it (you don't shrink tablespaces).
I don't see any reason to shrink a data file. what are you going to gain back, a few hundred megabytes? If you are that strapped for space, you have bigger problems.
Re: Shrink tablespace [message #279274 is a reply to message #279240] Wed, 07 November 2007 10:29 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Or you should first shrink the objects in the tablespace and then resize the datafile.
Re: Shrink tablespace [message #279283 is a reply to message #279274] Wed, 07 November 2007 10:40 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:


Or you should first shrink the objects in the tablespace and then resize the datafile.



Does anyone actually have a practical example for this?

I have a database with ~300GB on a storage cluster that is now 90% full, one tablespace in 50 datafiles.

I plan to delete about 30% of historical data in a few month.

I tried the procedure on a test environment with an

....
alter table xxx move;
....
alter index yyy rebuild;
....


For all tables and all indexes after the data was deleted.

But even after that the object's didn't move to the beginning of the datafiles, so I couldn't shrink them.

The way I did it the last couple of years was to re-create the db empty and do an export/import, which reduced the size by a little more than the amount of data that was deleted.

Are there any other more practical ways?

( Version 9.2.0.8.0 in my case )
Re: Shrink tablespace [message #280671 is a reply to message #279283] Wed, 14 November 2007 08:18 Go to previous messageGo to next message
alabaster_box
Messages: 4
Registered: October 2007
Junior Member
I think to shrink a tablespace,simply resize the datafiles concerned eg
alter database
datafile 'C:\oraData\TB02.dbf'
Resize 150M;
Re: Shrink tablespace [message #280677 is a reply to message #280671] Wed, 14 November 2007 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What if there is an object at the end of the file?

Regards
Michel
icon14.gif  Re: Shrink tablespace [message #280689 is a reply to message #279173] Wed, 14 November 2007 09:22 Go to previous messageGo to next message
alabaster_box
Messages: 4
Registered: October 2007
Junior Member
The resizing of the datafile simply frees up unused blocks.If automatic extension is enabled,then the datafle will eventually grow again as needed.
You're not in danger of loosing data due to the resizing of the datafiles.
The risk is in dropping datafiles-thats an operation i'd hardly carry out in a production system
Regards
Re: Shrink tablespace [message #280722 is a reply to message #280689] Wed, 14 November 2007 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not answer my question not OP problem.

Regards
Michel

Re: Shrink tablespace [message #280727 is a reply to message #280722] Wed, 14 November 2007 10:45 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
ALTER TABLESPACE my_tablespace SHRINK;


Will shrink a tablespace. This feature is only available in 10g. You also need to enable row movement:

ALTER TABLESPACE my_tablespace ENABLE ROW MOVEMENT;


There are ways to do it in 9i but it is a little more complicated.

Check out this link:
http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-09-10.8045550726

Also note that tablespaces need to use Automatic Segment Management vs. Manual management. Manual management is the default. You cannot switch between manual and automatic management (http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#ADMIN011 See the section "Specifying Segment Space Management in Locally Managed Tablespaces")

I'll be posting a message about how else to go about turning a manually managed tablespace into automatic management soon.

[Updated on: Wed, 14 November 2007 11:44]

Report message to a moderator

Re: Shrink tablespace [message #280750 is a reply to message #279173] Wed, 14 November 2007 11:42 Go to previous messageGo to next message
alabaster_box
Messages: 4
Registered: October 2007
Junior Member
thats great to know.I come from a 9i background.
Re: Shrink tablespace [message #280801 is a reply to message #280727] Wed, 14 November 2007 15:03 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
clintonf wrote on Wed, 14 November 2007 09:45

ALTER TABLESPACE my_tablespace SHRINK;


Will shrink a tablespace. This feature is only available in 10g. You also need to enable row movement:

ALTER TABLESPACE my_tablespace ENABLE ROW MOVEMENT;


There are ways to do it in 9i but it is a little more complicated.

Check out this link:
http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-09-10.8045550726

Also note that tablespaces need to use Automatic Segment Management vs. Manual management. Manual management is the default. You cannot switch between manual and automatic management (http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#ADMIN011 See the section "Specifying Segment Space Management in Locally Managed Tablespaces")

I'll be posting a message about how else to go about turning a manually managed tablespace into automatic management soon.




Is there any SHRINK commands for tablespace? Did you try these commands.
May be you meant TABLE LEVEL.

[Updated on: Wed, 14 November 2007 15:07]

Report message to a moderator

Re: Shrink tablespace [message #280803 is a reply to message #280801] Wed, 14 November 2007 15:19 Go to previous messageGo to next message
clintonf
Messages: 82
Registered: May 2006
Member
Ooops!

My mistake; shrinks are on the table, not the tablespace.

Thanks for pointing out the error!

I don't think there are any commands to shrink an entire tablespace.
resize data file [message #283780 is a reply to message #279173] Wed, 28 November 2007 02:37 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

I was rebuilding index of 3 GB and I was having free space of 2.5 GB on TEMP_LARGE tablespace in which index exists.

So I resized 1 datafile from 1000m to 1500m with hoping that I will reduce size after rebuilding index.

Then I rebuild index of 3 GB successfully and I get freespace of 3.3 GB in the TEMP_LARGE tablespace.

Then I tried to resize/reduce datafile size but it gives me an error
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


Please let me know how is it possible that even I have much free space then why I am not bale to reduce datafile size. Why I am getting this type of error.

Please tell me any solution.

Thanks in advance.
Re: resize data file [message #283781 is a reply to message #283780] Wed, 28 November 2007 02:42 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
select count(*)from dba_free_space where tablespace_name='tablespace_name';
if the value is high,try alter tablespace coalesce and then retry to resize it.
Re: resize data file [message #283784 is a reply to message #283780] Wed, 28 November 2007 02:47 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SELECT a.File_Name,
       a.Bytes File_Size_In_Bytes,
       (c.Block_Id + (c.Blocks - 1)) * &_Block_Size hwm_Bytes,
       a.Bytes - ((c.Block_Id + (c.Blocks - 1)) * &_Block_Size) savIng
FROM   dba_Data_Files a,
       (SELECT   File_Id,
                 MAX(Block_Id) Maximum
        FROM     dba_Extents
        GROUP BY File_Id) b,
       dba_Extents c
WHERE  a.File_Id = b.File_Id
       AND c.File_Id = b.File_Id
       AND c.Block_Id = b.Maximum
       AND c.TableSpace_Name = '&tbs_name'



through above query output you can reduce your datafile size.
if above query and total datafile size is not much difference you have to reorganize your tablespace.

[Updated on: Wed, 28 November 2007 03:17]

Report message to a moderator

Re: resize data file [message #283792 is a reply to message #283780] Wed, 28 November 2007 02:56 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks for reply.

As varu123 says.....
if the value is high,try alter tablespace coalesce and then retry to resize it. 


PLease tell me if value is high means how much value you are thinking.
I got the count(*) = 116 as a result.

And if i specify the
Alter tablespace tablespacename coalesce;

Then how much time it will take to perform?

And as i am working on the 24*7 production environment. Will it cause any side effects.

Thanks,

[Updated on: Wed, 28 November 2007 02:57]

Report message to a moderator

Re: resize data file [message #283795 is a reply to message #283792] Wed, 28 November 2007 03:01 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Your tablespace is "dictionary" or "locally" managed ?
Did you try what I said in my previous post.
Re: resize data file [message #283796 is a reply to message #283780] Wed, 28 November 2007 03:06 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
116 is too high a value.
How many datafiles do you have for that tablespace?
Alter tablespace coalesce provides a contiguous free space.
If your tablespace is LM then there could be no Fragmentation.
Re: resize data file [message #283799 is a reply to message #283795] Wed, 28 November 2007 03:13 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Mohammad Taj,

My tablespace is locally managed.
And tried that query but having doubt.
Please tell what size whould i specify for &_Block_Size parameter.
I am begginer in the DBA so sorry for any silly question.
Thanks,
Re: resize data file [message #283803 is a reply to message #283780] Wed, 28 November 2007 03:18 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi varu123,

In database there are 4 datafiles are of 9G, 9G, 8G, 1.5G

As you says what is LM?
If your tablespace is LM then there could be no Fragmentation.


Then how much time it will take to perform coalesce?

And as i am working on the 24*7 production environment. Will it cause any side effects.


Thanks,

Re: resize data file [message #283806 is a reply to message #283799] Wed, 28 November 2007 03:23 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

It is your database block size..
SQL> show block_size 

Re: resize data file [message #283808 is a reply to message #283796] Wed, 28 November 2007 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why 116 is too high?
Coalesce just coalesce contiguous free space, it does not change the minimum value for resize.
It is useless in LMT.

Regards
Michel
Re: resize data file [message #283809 is a reply to message #283803] Wed, 28 November 2007 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Replace &_block_size by a.bytes/a.blocks.
Files can have different block size.

Regards
Michel
Re: resize data file [message #283825 is a reply to message #283780] Wed, 28 November 2007 04:22 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Expert,
I tried with given query but it is running from 30 min.

Does it has any problem?

And also seen one solution on net.
Please suggest is it a right solution.
SQL> create tablespace tb_test
  2  datafile 'c:\temp\tb_test.dbf' size 100 K autoextend on;
Tablespace created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
    106496                                                                      
SQL> create table t_big tablespace tb_test as
  2  select a.* from all_objects a, dba_users for_cartesian;
Table created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
        34                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
  19963904Now we have datafile quite big. Let's drop table and shrink file: 
SQL> drop table t_big;
Table dropped.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
alter database datafile 'c:\temp\tb_test.dbf' resize 100 K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value 
So. even there is nothing reported in DBA_EXTENT, file cannot be shinked. Let's purge recyclebin and try again: 
SQL> purge recyclebin;
Recyclebin purged.
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
Database altered.


Thanks in advance.
Re: resize data file [message #283826 is a reply to message #283825] Wed, 28 November 2007 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

is it a right solution

It depends.
Do you want to keep the objects that are in the recycle bin or not?
Only you can answer this.

Regards
Michel
Re: resize data file [message #283833 is a reply to message #283780] Wed, 28 November 2007 04:37 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I tested with purge recyclebin;
But it doesn't work.

And regarding query i tested but it is still running .
Does any problem with this query?
SELECT a.File_Name,
       a.Bytes File_Size_In_Bytes,
       (c.Block_Id + (c.Blocks - 1)) * &_Block_Size hwm_Bytes,
       a.Bytes - ((c.Block_Id + (c.Blocks - 1)) * &_Block_Size) savIng
FROM   dba_Data_Files a,
       (SELECT   File_Id,
                 MAX(Block_Id) Maximum
        FROM     dba_Extents
        GROUP BY File_Id) b,
       dba_Extents c
WHERE  a.File_Id = b.File_Id
       AND c.File_Id = b.File_Id
       AND c.Block_Id = b.Maximum
       AND c.TableSpace_Name = '&tbs_name'



Or please suggest if any other solution?

Thanks in advance.
Re: resize data file [message #283835 is a reply to message #283780] Wed, 28 November 2007 04:45 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Michel,
Alter tablespace coalesce statement separates or rather put used and free space on one side.
If the tablespace is fragmented,would the high water mark value decrease/increase/noeffect after issuing alter tablespace colaesce statement.
Re: resize data file [message #283863 is a reply to message #283835] Wed, 28 November 2007 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Coalesce does not change used blocks/extents, so it does not change the HWM (as defined as the last used block).

Regards
Michel
Re: resize data file [message #283868 is a reply to message #283833] Wed, 28 November 2007 05:48 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

@OP,
above query is correct.
I already checked in my production database.
you should check v$session_longops view why query take too much time ???
SQL> show parameter block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL> SELECT a.File_Name,
  2         a.Bytes File_Size_In_Bytes,
  3         (c.Block_Id + (c.Blocks - 1)) * &_Block_Size hwm_Bytes,
  4         a.Bytes - ((c.Block_Id + (c.Blocks - 1)) * &_Block_Size) savIng
  5  FROM   dba_Data_Files a,
  6         (SELECT   File_Id,
  7                   MAX(Block_Id) Maximum
  8          FROM     dba_Extents
  9          GROUP BY File_Id) b,
 10         dba_Extents c
 11  WHERE  a.File_Id = b.File_Id
 12         AND c.File_Id = b.File_Id
 13         AND c.Block_Id = b.Maximum
 14         AND c.TableSpace_Name = '&tbs_name'
 15  /
Enter value for _block_size: 8192
old   3:        (c.Block_Id + (c.Blocks - 1)) * &_Block_Size hwm_Bytes,
new   3:        (c.Block_Id + (c.Blocks - 1)) * 8192 hwm_Bytes,
Enter value for _block_size: 8192
old   4:        a.Bytes - ((c.Block_Id + (c.Blocks - 1)) * &_Block_Size) savIng
new   4:        a.Bytes - ((c.Block_Id + (c.Blocks - 1)) * 8192) savIng
Enter value for tbs_name: HGC
old  14:        AND c.TableSpace_Name = '&tbs_name'
new  14:        AND c.TableSpace_Name = 'HGC'

FILE_NAME
--------------------------------------------------------------------------------

FILE_SIZE_IN_BYTES  HWM_BYTES     SAVING
------------------ ---------- ----------
E:\DATAFILES\HGC01.DBF
        1.5729E+10 3162570752 1.2566E+10


PS: you can also kill that session which query is running.
Re: resize data file [message #283873 is a reply to message #283868] Wed, 28 November 2007 05:52 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

@op,
YOu have also two option to reduce your tablespace size.

I
1. export tablespace
2. recreate tablespace
3. import tablespace

II
1. create new tablespace
2. move all table to new tablespace
3. rebuild all indexes to new tablespace
4. drop old tablespace
5. rename new tablespace name to old tablespace name.

NOTE: if any table have LONG datatype will not move.

Both is option but you first try on your test enviourment.
if above option is suitable for your then apply on production site.
Re: Shrink tablespace [message #284034 is a reply to message #279173] Wed, 28 November 2007 10:53 Go to previous messageGo to next message
zbob99
Messages: 1
Registered: November 2007
Junior Member
I think to shrink a tablespace,simply resize the datafiles concerned eg
alter database
datafile 'C:\oraData\TB02.dbf'
Resize 150M;
========

This was posted before and is correct.
Re: Shrink tablespace [message #284043 is a reply to message #284034] Wed, 28 November 2007 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not possible if you have segments at the end of the file.

Regards
Michel
Re: resize data file [message #284103 is a reply to message #283780] Wed, 28 November 2007 22:17 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I see in Tom Kyte's expert oracle,in there, he wrote export/import is no longer a reorganize method any more.

He suggested to use 2nd one as Mohammad Taj suggested.

He argued about availability and other features to use alter table move.
Re: resize data file [message #284138 is a reply to message #284103] Thu, 29 November 2007 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I fully agree with Tom for this if a prerequisite is satisfied: you have space enough to create the second tablespace.

Regards
Michel
Re: resize data file [message #284157 is a reply to message #284103] Thu, 29 November 2007 01:05 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

I also agree with Tom Kyte.
but i was just given possible suggestion.

Everything is depend on database enviourment.
as myself I used both method on my production database.(depends on tablespace size)
alter tablespace coalesce [message #284531 is a reply to message #279173] Fri, 30 November 2007 00:23 Go to previous messageGo to previous message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

I want to shrink tablespace.
So first i will use command
alter tablespace coalesce
Then resize the datafile.

Please answer following questions.
1. Is it right?
2. If i use alter tablespace coalesce how much time it will take to perform
3. If i use alter tablespace coalesce will it lock database objects inside that tablespace.
4. Does it will cause any other problem in production environment.

Thanks in advance.

Previous Topic: Confusing result of a query.
Next Topic: Alert_Log
Goto Forum:
  


Current Time: Thu Sep 19 14:36:09 CDT 2024