Home » RDBMS Server » Server Administration » reduce the size of the table file (oracle 9.2.0.7.0)
reduce the size of the table file [message #431221] Mon, 16 November 2009 04:13 Go to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
I use below command to increase the size of table/file .
SQL> select FILE_NAME from dba_data_files where TABLESPACE_NAME='RCA_CFM_PROP_DATA';

FILE_NAME
--------------------------------------------------------------------------------
/gxsdb/database/oradata4/RCA/RCA_REF_CFM_PROP_DATA.dbf

alter database datafile '/gxsdb/database/oradata4/RCA/RCA_REF_CFM_PROP_DATA.dbf' resize 1M;


After this , I want to reduce back the size of the table again in order to save the space ???
It is already alter for a while .
How can I fall back ???
Any suggestion ??
Re: reduce the size of the table file [message #431225 is a reply to message #431221] Mon, 16 November 2009 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to reduce back the size of the table

Table or tablespace?

You cannot remove a file from your tablespace (in your version).

Regards
Michel
Re: reduce the size of the table file [message #431233 is a reply to message #431225] Mon, 16 November 2009 04:34 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
the procedure I use is increase the size of tablespace.
So, now I want to reduce back the tablespace size .....
Re: reduce the size of the table file [message #431234 is a reply to message #431233] Mon, 16 November 2009 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the same command.

Regards
Michel
Re: reduce the size of the table file [message #431235 is a reply to message #431234] Mon, 16 November 2009 04:41 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Will the new add space occupy the data and cannot reduce the size ?? any impact to the tablespace ????
Re: reduce the size of the table file [message #431245 is a reply to message #431235] Mon, 16 November 2009 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot release space that have been allocated to objects.
If the new space you added has not been occupied then you can release it otherwise the command will return an error.
Just try it.

Regards
Michel
Re: reduce the size of the table file [message #434133 is a reply to message #431221] Mon, 07 December 2009 06:12 Go to previous message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
Before reducing the size the things taken into consideration is
The total size of object on the top of this tablespace.

That information you will get it from dba_segments;

select sum(bytes/1024/1024/1024) "In GB" from dba_segments where TABLESPACE_NAME='t';

if this is 2 gb and you want to reduce the datafile size that is more near to 2 gb.

then you can try with below solutions

(Applicable only if downtime can be tolerable or in weekend)
create another tablespace t2 move all the data to that
tablespace. Resize the old tablespace's data file and move
back the objects again to t tablespace.

Previous Topic: drop database in oracle 10g
Next Topic: Moving Indexes ... ???
Goto Forum:
  


Current Time: Sun Sep 25 23:13:49 CDT 2016

Total time taken to generate the page: 0.09613 seconds