add datafile to tablespace

Toad for Oracle
1

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

www.dcnr.state.pa.us

3

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.

4

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;

:slight_smile:

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]

5

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

6

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

7

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.

8

is allowing you to "use"

Hmm... methinks allowing should be in quotes too :slight_smile:

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.

9

…until v11 of course J

10

And the database browser too is part of the DBA module J

11

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

12

If you do auto extend make sure also to use bigfile tablespaces so that you
don’t run into an OS limits ….

13

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! :slight_smile:

--
Cheers,
Norm. [TeamT]

14

On 15/07/11 17:46, Norman Dunbar wrote:

No! No! No! No! No! :slight_smile:

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

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

PL/SQL is not permitted, neither is any other external language, only
straight SQL.

Have it on my desk, Monday morning first thing! :wink:

--
Cheers,
Norm. [TeamT]

15

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.

16

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! :wink:

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]