Increase the Size of Tablespace March 31, 2007
Posted by Muhammad Habib in DBA.trackback
If you want to increase the size of tablespace, its so simple. You can do this by enterprise manager console. Increase the size of datafile for particular tablespace.
OR
For Example
ALTER DATABASE
DATAFILE ‘/u03/oradata/ userdata02. dbf’
RESIZE 200M;
If you don’t have any free space on that partition of disk then you can add another datafile on the other partition for particular tablespace.
For example
ALTER TABLESPACE app_data
ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’
SIZE 200M;
Now you can insert data with in this tablespace.
We run a Oracle 10g database in a datawarehouse enviroment but the problem is that I keep getting a message in my alert logs stating that tablespace only has 0 megabytes free space. This is occuring in a number of tablespaces. If I attempt to edit the tablespace I see that for example the tablespace is 4,099MB in size but only 1MB is used. What could be the problem and how could I solve this i.e make that message go away. Please note this includes but is not restricted to sort tablespaces.
Have you checked all your table spaces? I mean to say that sometimes it happens that to some table we have assigned different table space than schema’s table space. Select the table space of each table in query and then check.
Hope it will be helpful.
Best Regards
Habib
this was awesome for help!
Thanks Jenn b
Habib you are GREAT!!!
Thanks for the help!
i m using 10g R2 database, i want ot configure another database for replication purpose, Pl. tel me how i configure database for this purpose..
Assalam o alaikum Muhammad Habib,
Its very useful.
Thanks for sharing the knowledge.
hey i am using oracle 10g enterprise edition can anyone tell me how to increase the size of tablespace through command prompt.
i tried the above but failed my datafile name was “himansu.dat”.
thank you
On an Oracle 10gR2 database, when you run the SQL statement to increase the tablespace and add new datafile, if we were also to add the option “AUTOEXTEND”, does this extend the tablespace by:
1) adding a new datafile in the same sequence
2) increase the size of the datafile which we just added to allow for more size in the tablespace?
The reason why I ask this is because I’m concerned on the maximum size a datafile should be given. How would having a datafile of 2GB in size compare to a datafile of a lesser amount in performance?
Thank you.
whenever i am trying to add new datafiles its showing error in adding datafile. so how can i increase the size without increaseing the size of exising datafile …….
Nice work…keep it up..!
My system is the oracle server, which developers are using the server.
system doesn’t have such a space to add new data. Even i deleted some user from database i didnt get any disk space after done it. Please any one suggest me how to increase my disk space to mail id manikandan264@gmail.com. Thanks in advance
Hi,
We are forgotting about RECYCLEBIN new feature of Oracle 10g. If this option is available then you should need to PURGE recyclebin after dropping such objects. Otherwise space doesn’t re-claimed back even dropping objects.
Thanks and regards,
Gitesh
http://www.dbametrix.com
Just wanted to say Thanks! Habib!.. You are providing a great service here.
Please answer to 9th comment..
hey, spring is cooming! good post there, tnx for mhabib.wordpress.com
we face a lot of performance issue at the time of monthly loading due to which even the files are that are received and loaded on daily basis takes much longer time to load ,will invoking multiple DB writer help to fasten the process of loading or is there any other method to increase the performance.currently we are using about 5 CPU server.
Thanks for the help.
really its very usefull
Hi,
When we are using Oracle Enterprise Manager for adding new datafile, kindly take care of name and extension of datafile. Because if we don’t provide any extension then OEM create datafile without any extension or kept .ORA extension.
I Hibib
i created tablespace for PSINDEX table and here is the example but too small.
CREATE TABLESPACE PSINDEX DATAFILE ‘d:\app\mwahid\oradata\PSHRDMO1\psindex.dbf’ SIZE 2500M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/
and now i am trying to increase the the tables pace
1* ALTER TABLESPACE PSINDEX ADD DATAFILE ‘d:\app\mwahid\oradata\PSHRDMO1\psindex.dbf’ SIZE 7500M
SQL> /
ALTER TABLESPACE PSINDEX ADD DATAFILE ‘d:\app\mwahid\oradata\PSHRDMO1\psindex.dbf’ SIZE 7500M
*
ERROR at line 1:
ORA-01537: cannot add file ‘d:\app\mwahid\oradata\PSHRDMO1\psindex.dbf’ – file
already part of database
im stuck
please tell what to do thanks
hallo dear in which platform ur running ur database
..and give command as resize in place of size
Thanks! sorted out my tablespace problem
Awesome.. did the trick for me..
Thanks very much Habib Sahib
Thanks Rupinder
Regards
Habib
Is it important to take the table space offline before executing ‘datafile re-size ‘query.
If a user table space size increase unexpected,what is the reason for that.
Eg. suppose we have a table space DTA. At evening near about 6PM it has free space 25% and used 75%. But at night near about 11PM or 12AM it has used 100%. It doesn’t happen before that day.
Kindly tell me reason for that.
very helpful. thanks. bookmarked
good morning .can you please tell me if there is a way to creat a tablespace but without precise the size (illimit tablespace ).i am working in data warehouse in oracle 11g where the data is very large .
i have tape this query
CREATE TABLESPACE tbs DATAFILE ‘F:\tbsf.dbf’ SIZE 500M AUTOEXTEND ON MAXSIZE 1000M
but for me i need million of records in my data warehouse and all what i got when i use this is 5000 records
thanks for reading my msg
the size of USERS tablespace is 11GB and which is auto extend still i want to increase the size of tablespace can i increase it ..?
I m using oracle 10g, Toad.
Hi,
I’m running oracle 10g on suse linux and one tablespace is full, maxsize is reached. there are 4 datafiles inside and all have reached their maximum size (32g). if i add new datafile to the tablespace, does it solve the problem ? i mean the database will use this data file automatically or i have to configure something else so that the database will add data to this file.
thanks in advance.