|
|
Re: Shrink tablespace [message #279175 is a reply to message #279174] |
Wed, 07 November 2007 04:23 |
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 #279240 is a reply to message #279177] |
Wed, 07 November 2007 08:07 |
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 #279283 is a reply to message #279274] |
Wed, 07 November 2007 10:40 |
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 #280803 is a reply to message #280801] |
Wed, 14 November 2007 15:19 |
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 |
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 |
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 |
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 |
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 #283796 is a reply to message #283780] |
Wed, 28 November 2007 03:06 |
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 |
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 |
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 #283825 is a reply to message #283780] |
Wed, 28 November 2007 04:22 |
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 #283833 is a reply to message #283780] |
Wed, 28 November 2007 04:37 |
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 |
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 #283868 is a reply to message #283833] |
Wed, 28 November 2007 05:48 |
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: Shrink tablespace [message #284034 is a reply to message #279173] |
Wed, 28 November 2007 10:53 |
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.
|
|
|
|
|
|
|
alter tablespace coalesce [message #284531 is a reply to message #279173] |
Fri, 30 November 2007 00:23 |
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.
|
|
|