How to increase default table space size of a custom Oracle?

Asked By 270 points N/A Posted on -
qa-featured

Hi,

I have a user created in Oracle by the name "joseph". The initial table space was named as "tspjoseph" which used a data file named "josephtsp.dbf". Unfortunately, I set the size to 1GB which is not full filling the requirement. My whole software is up and the database is also filled with important data. Is there any way I can increase it, without affecting the current data?

SHARE
Best Answer by technical007
Best Answer
Best Answer
Answered By 0 points N/A #91953

How to increase default table space size of a custom Oracle?

qa-featured

First of all, commit all your required changes and take a backup if possible. Connect to sys as sysdba. You can set the datafile to be auto expandable, In order to do so issue something like:

alter database datafile 'josephtsp.dbf' autoextend on maxsize 2024M; 

This shall increase the capacity of josephtsp to 2GB.

Answered By 270 points N/A #91955

How to increase default table space size of a custom Oracle?

qa-featured

Wow! that was easy! Thanks a lot. But I realized one more problem. What if my hard disk partition reaches limit? Is it possible to create a new data file separately but linked to the same table space?

Answered By 0 points N/A #91956

How to increase default table space size of a custom Oracle?

qa-featured

Yes it is. You just need to add another datafile to the current tablespace using something like :

ALTER TABLESPACE <tablespacename>

ADD DATAFILE  '<name of datafile>.dbf '

SIZE <size you want>M;

Answered By 270 points N/A #91957

How to increase default table space size of a custom Oracle?

qa-featured

I am using this:

ALTER TABLESPACE tspjoseph ADD DATAFILE "d:josephtsp2.dbf" SIZE 1000M

which is giving the error:

ERROR at line 1:
ORA-02236: invalid file name

Answered By 0 points N/A #91958

How to increase default table space size of a custom Oracle?

qa-featured

It will be a single quotation.

ALTER TABLESPACE TSPJOSEPH ADD DATAFILE 'D:josephtsp2.dbf' SIZE 1024M;

Answered By 270 points N/A #91959

How to increase default table space size of a custom Oracle?

qa-featured

Thanks a lot Tech. That was amazing.

Related Questions