jump to navigation

Increase the Size of Tablespace March 31, 2007

Posted by Muhammad Habib in DBA.

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.


For Example

DATAFILE ‘/u03/oradata/ userdata02. dbf’

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

ADD DATAFILE ‘/u01/oradata/ userdata03. dbf’
SIZE 200M;

Now you can insert data with in this tablespace.



1. Russell - August 6, 2007

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.

2. Muhammad Habib - August 8, 2007

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

3. jenn b - September 18, 2007

this was awesome for help!

4. Muhammad Habib - September 19, 2007

Thanks Jenn b

5. zja - April 28, 2008

Habib you are GREAT!!!
Thanks for the help!

6. S. SADAQAT ALI - August 27, 2008

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..

7. Mohammed Ayyub - September 5, 2008

Assalam o alaikum Muhammad Habib,

Its very useful.
Thanks for sharing the knowledge.

8. himansu sekhar das - October 23, 2008

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

9. Serkan Cetin - November 20, 2008

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.

10. Rakesh - March 4, 2009

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 …….

11. Raghu - July 20, 2009

Nice work…keep it up..!

12. Manikandan - October 20, 2009

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

13. dbametrix - October 21, 2009


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,

14. Masud - October 23, 2009

Just wanted to say Thanks! Habib!.. You are providing a great service here.

15. Chandu - January 6, 2010

Please answer to 9th comment..

16. soma uk - March 2, 2010

hey, spring is cooming! good post there, tnx for mhabib.wordpress.com

17. vishnu - July 4, 2010

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.

18. Waleed - July 12, 2010

Thanks for the help.
really its very usefull

19. Jack Nicholson - August 5, 2010


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.

20. Muhammad Afzal Wahid - November 16, 2010

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

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

21. naj - November 21, 2010

hallo dear in which platform ur running ur database
..and give command as resize in place of size

22. Bill - December 2, 2010

Thanks! sorted out my tablespace problem 😀

23. Rupinder Singh - March 6, 2011

Awesome.. did the trick for me..

Thanks very much Habib Sahib

24. Habib - March 7, 2011

Thanks Rupinder



25. dlna - May 12, 2011

Is it important to take the table space offline before executing ‘datafile re-size ‘query.

26. suman - July 28, 2011

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.

27. felipe1982 - December 13, 2011

very helpful. thanks. bookmarked

28. djanahana - January 7, 2012

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


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

29. sachin - March 22, 2012

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.

30. Halim - May 24, 2012


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.

31. mohan - March 9, 2013

thanks i solve my table spaces problems

32. stock options explained example - March 27, 2013

fantastic points altogether, you simply received a brand new reader.
What may you suggest about your publish that you just made a few days
ago? Any certain?

33. pouran - April 3, 2013

create a script give me alert when the tablespace is full

34. {Panic Attacks - April 14, 2013

A person essentially lend a hand to make seriously posts I might state.
This is the first time I frequented your web page and up to now?
I amazed with the analysis you made to make this particular publish extraordinary.
Magnificent process!

35. anyoption scam - April 16, 2013

Great goods from you, man. I have understand
your stuff previous to and you are just too magnificent.

I really like what you have acquired here, really like what you’re saying and the way in which you say it. You make it entertaining and you still take care of to keep it sensible. I can not wait to read far more from you. This is really a great site.

36. Low T Symptoms - May 10, 2013

Hmm it seems like your site ate my first comment (it was super long)
so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog.

I as well am an aspiring blog writer but I’m still new to everything. Do you have any points for inexperienced blog writers? I’d really
appreciate it.

37. Top Online Casinos - May 11, 2013

wonderful points altogether, you simply received a emblem new
reader. What might you suggest about your put
up that you made a few days ago? Any sure?

38. forex steam - June 1, 2013

Hello it’s me, I am also visiting this website on a regular basis, this website is actually fastidious and the people are in fact sharing nice thoughts.

39. she mal - June 21, 2013

Hmm is anyone else experiencing problems with the images
on this blog loading? I’m trying to figure out if its a problem on my end or if it’s the blog.
Any feed-back would be greatly appreciated.

40. miami club casino - June 21, 2013

If you would like do this out and about, make use of one of many a few techniques many of us talked about to gain access to
the casino on the internet on the ease and comfort in your home.
The way to handicap School Hoops NCAA Walk Mayhem
Event Model

41. Manhattan Slots - June 29, 2013

And as well as the truth that online Manhattan Slots format regulations and
gives much better payment proportions which can be a lot more gambler helpful in comparison with
area primarily based Manhattan Slots. EcoCard is
easy and also protected make use of and is particularly recognised by a range of
Western banking companies.

42. sumair - July 9, 2013

hi habib,
i am trying to import a database dump into an another database but after importing the dump i find many objects are missing.
Dump which is i am importing is all fine but still i am facing the same issue.

43. youtube to mp3 - August 10, 2013

I believe that is among the so much significant info for me.
And i am satisfied reading your article. But want to
observation on some normal things, The web site style is wonderful,
the articles is really great : D. Good activity, cheers

44. Bryant - March 11, 2014

Good information, simple and useful. Thanks.

45. swordfish - June 15, 2014

if i add one more datafile to tablespace will application can read data from previeous data file automatically or should do anything on application (like pointing to second datafile ) im new oracle please help

46. http://www.youtube.com/watch?v=gf7arw-q8v0 - September 12, 2014

If you are going for best contents like I do, just go to see this web page daily since it gives quality contents,

47. Gemma - October 9, 2014

You share interesting things here. I think that your website can go viral easily, but you must give
it initial boost and i know how to do it, just search in google (with quotes) for – “mundillo traffic increase make your website go viral”

48. Sharief - February 18, 2016

Hello Habib,
In my prod env when I am trying to load history data I am always getting “Unable to extend temp segments” error and my job fails… I use to contact DBA team to increase the temp space.. this is how the process is going on and eating valuable time. I requested DBA team to automate this process, but DBA said it requires Manual intervention. Can’t we come up with automated unix/perl script which will check available DB files and extend the temp space automatically. Please help.

49. Bablu Gope - August 11, 2016

Thanks buddy for your great help

50. internet business income generator - January 9, 2017

internet business income generator

Increase the Size of Tablespace | Habib's Oracle World

51. buy instagram - January 12, 2017

buy instagram

Increase the Size of Tablespace | Habib's Oracle World

52. info viral - April 20, 2017

I got this web site from my buddy who told me on the topic of this site and now this time I
am visiting this web page and reading very informative articles at this time.

53. Prasanth - May 3, 2017


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: