# add datafile to tablespace **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [July 15, 2011, 9:49am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569 "2011-07-15T09:49:36Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [July 15, 2011, 9:49am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/1 "2011-07-15T09:49:36Z")
This week we had a tablespace datafile fill up. I went into Toad, went to Database =\> Administer =\> Tablespaces. I saw that both the offending tablespace’s 500MB datafiles were full. I really wanted to add a 3rd datafile to that tablespace but I could not easily see how to do that. In order to quickly get people working again, I changed the size of the 2nd datafile to 1000MB. That fixed the current issue. I then looked further into adding a 3rd datafile and found a push button way to do it. Today, another tablespace is filling up and to solve any problems before they come up, I want to add a datafile to that tablespace. The problem is that I cannot find that button in any menu. It seems it would be in the above menu but I try right-clicking on everything and searching the menus but to no avail. Can someone please point me in the right direction? Thanks, James W. Wolff Oracle DBA PA Department of Conservation and Natural Resources Bureau of Information Technology Services 400 Market Street | Harrisburg, PA 17105 Fax: 717.772.9107 E-mail: [c-jwolff@state.pa.us](mailto:c-jwolff@state.pa.us) [www.dcnr.state.pa.us](http://www.dcnr.state.pa.us) ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [July 15, 2011, 9:56am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/2 "2011-07-15T09:56:00Z")
---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [July 15, 2011, 9:57am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/3 "2011-07-15T09:57:14Z")
By the way, in the schema browser, there is an “Add datafile” button on the right hand side. You don’t have to do through the Alter Tablespace screen. ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [July 15, 2011, 10:21am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/4 "2011-07-15T10:21:43Z")
On 15/07/11 15:57, John Dorlon wrote: > By the way, in the schema browser, there is an “Add datafile” button on > the right hand side. You don’t have to do through the Alter Tablespace > screen. Sticks neck above parapet again - just back from holiday..... Alternatively, in the MOE there is an easy way to add a new datafile, plus, it works for all versions of Toad, regardless of the Oracle client in use: alter tablespace X add datafile 'full/path/to/datafile.dbf' size nnnn reuse; 🙂 Sorry, couldn't resist! However, in all seriousness, while Toad is by far the best tool for DBAs, Developers, Analysts etc, I think we should know the underlying commands for these things. Just in case the day ever comes when we have to fix a problem, on site somewhere, and Toad is not present or even, not allowed. When all you have is SQL\*Plus, it pays to know what the commands are that the GUI in Toad is allowing you to "use". Just a thought. -- Cheers, Norm. [TeamT] ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [July 15, 2011, 10:25am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/5 "2011-07-15T10:25:03Z")
John, I do not see an add datafile button in my schema browser. On the “right the hand side” of what screen? Do you mean database browser? We have not purchased the DBA module yet so I don’t have access to the Database Browser. James ![image001.jpeg](https://global.discourse-cdn.com/quest/original/1X/278be512a68050c664416c8c1dd90a338bc191af.jpeg) ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [July 15, 2011, 10:25am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/6 "2011-07-15T10:25:03Z")
John, I do not see an add datafile button in my schema browser. On the “right the hand side” of what screen? Do you mean database browser? We have not purchased the DBA module yet so I don’t have access to the Database Browser. James ![image001.jpeg](https://global.discourse-cdn.com/quest/original/1X/e8288ef7bf69daaab6959f752b25ca292478b911.jpeg) ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [July 15, 2011, 10:42am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/7 "2011-07-15T10:42:59Z")
Oh, well, if there is no DBA module then you won’t have access to tablespaces in the schema browser, so nevermind. I never said anything about the database browser. At least I didn’t mean to. ---
**Author:** ![Guest](https://avatars.discourse-cdn.com/v4/letter/g/8dc957/32.png) [@Guest](https://forums.toadworld.com/u/Guest)\ **Post date:** [July 15, 2011, 10:43am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/8 "2011-07-15T10:43:34Z")
> > is allowing you to "use" Hmm... methinks allowing should be in quotes too 🙂 In in total agreement with Norm. One should have an understanding of the basics in the event one doesn't have a particular tool handy. RAS This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply. ---
**Author:** ![HillbillyToad](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/hillbillytoad/32/190_2.png) [@HillbillyToad](https://forums.toadworld.com/u/HillbillyToad)\ **Post date:** [July 15, 2011, 10:47am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/9 "2011-07-15T10:47:03Z")
…until v11 of course J ---
**Author:** ![Bert\_Scalzo](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/bert_scalzo/32/274_2.png) [@Bert\_Scalzo](https://forums.toadworld.com/u/Bert_Scalzo)\ **Post date:** [July 15, 2011, 10:53am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/10 "2011-07-15T10:53:18Z")
And the database browser too is part of the DBA module J ---
**Author:** ![HillbillyToad](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/hillbillytoad/32/190_2.png) [@HillbillyToad](https://forums.toadworld.com/u/HillbillyToad)\ **Post date:** [July 15, 2011, 10:54am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/11 "2011-07-15T10:54:40Z")
Or bypass the entire problem and setup your tablespaces to AUTOEXTEND. Of course you’ll still need to monitor available disk space and consider adding a limit… Jeff ---
**Author:** ![Bert\_Scalzo](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/bert_scalzo/32/274_2.png) [@Bert\_Scalzo](https://forums.toadworld.com/u/Bert_Scalzo)\ **Post date:** [July 15, 2011, 10:56am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/12 "2011-07-15T10:56:50Z")
If you do auto extend make sure also to use bigfile tablespaces so that you don’t run into an OS limits …. ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [July 15, 2011, 11:46am UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/13 "2011-07-15T11:46:49Z")
On 15/07/11 16:54, Jeff Smith wrote: > Or bypass the entire problem and setup your tablespaces to AUTOEXTEND. No! No! No! No! No! 🙂 -- Cheers, Norm. [TeamT] ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [July 15, 2011, 12:00pm UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/14 "2011-07-15T12:00:56Z")
On 15/07/11 17:46, Norman Dunbar wrote: > No! No! No! No! No! 🙂 I shall explain, perhaps it would make more sense. Consider monitoring a tablespace, you wish to write a script that will alert you at say 80% with a warning, and a critical at 90%. Sounds easy. Ok, consider that your tablespace may have multiple files. Still easy. Add in the fact that the tablespace may be in AUTOEXTEND mode. Not quite so easy now is it? 80% or 90% or what figure do we consider now? Considet too the maximum size allowed, it may be an explicit limit or UNLIMITED. This too should be considered as part of the query. Still think it's simple? Add in the fact that some files making up the tablespace are in AUTOEXTEND while some others are not. Getting harder to write that one SQL statement isn't it? Of course, we only wish to monitor tablespaces with PERMANENT contents, so the query is getting more difficult now. However, consider those huge tablespaces where a 10% free space is still potentially gigabytes in size, so in those tablespaces, we only want to be alerted with a critical alert when the free space drops below a certain number of megabytes. Still think it's easy? And we don't want to monitor TEMP or UNDO as we monitor those separately. Did I mention that there are times when the actual size of the data file, in AUTOEXTEND mode, is actually BIGGER that the maximum size specified? Factor that in to the query! I wrote the above SQL query for Nagios monitoring of my databases, and I really don't advise it! And finally, how exactly do you factor in the case where the tablespace is allowed to grow to a given size (or UNLIMITED) yet the actual disc(s) it is sitting on have insufficient space to allow it to grow to the allowed maximum size? AUTOEXTEND is "useful" when you have an overnight job running that "might" use up all the space, so you don't want the on-call DBA called out, so that's the time when AUTOEXTEND is helpful. Other than that, the DBA should know how much space is left. Other opinions are available, as ever. 🙂 For homework, I want you all to write the SQL query required to monitor a tablespace given all of the above - except the fact that you can't (couldn't at the time) extract the physical disc size left on the devices. 🙂 PL/SQL is not permitted, neither is any other external language, only straight SQL. Have it on my desk, Monday morning first thing! 😉 -- Cheers, Norm. [TeamT] ---
**Author:** ![HillbillyToad](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/hillbillytoad/32/190_2.png) [@HillbillyToad](https://forums.toadworld.com/u/HillbillyToad)\ **Post date:** [July 15, 2011, 12:46pm UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/15 "2011-07-15T12:46:29Z")
Norm, do you use ASM? Curious if this makes things any easier for you if so. A lot of DBAs I talk to don’t have to worry so much about space anymore. The SAN folks give them space, and if they need more, they just go get it. If my database is growing by X% YOY, then I just let the tablespaces grow accordingly. ---
**Author:** ![NormTeamT](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/normteamt/32/53_2.png) [@NormTeamT](https://forums.toadworld.com/u/NormTeamT)\ **Post date:** [July 15, 2011, 3:17pm UTC](https://forums.toadworld.com/t/add-datafile-to-tablespace/10569/16 "2011-07-15T15:17:35Z")
Hi Jeff, On 15/07/11 18:46, Jeff Smith wrote: > Norm, do you use ASM? > ASM? Not yet. We're only just migrating to 11g. Still got lots on 7.3.4 > though! 😉 > Curious if this makes things any easier for you if > so. A lot of DBAs I talk to don’t have to worry so much about space > anymore. The SAN folks give them space, and if they need more, they just > go get it. > We have to tell the storage people how much we need, and they allocate a > lump for us on the SAM. That is mounted on the server/cluster and we use > it accordingly. We are not allowed to fill it up or request "too much" > space. > If my database is growing by X% YOY, then I just let the tablespaces > grow accordingly. > In an ideal world, we'd do the same. But we have to account for every byte! -- Cheers, Norm. [TeamT]