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

Asked By Joseph King 270 points N/A Posted on - 05/12/2011

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?

• Status: Open
• Question Views: 2081
• Answer Count: 6
• Vote Up 27 Vote Down

• Answer Accepted: Yes
• Question Category: Oracle Database

Best Answer by technical007

Go To Solution

Best Answer

Best Answer

Answered By technical007 0 points N/A #91953

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

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.

About technical007

Questions
0

Answers
14

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/12/2011
• Question Category: Oracle Database

Answered By Joseph King 270 points N/A #91955

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

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?

About Joseph King

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/12/2011
• Question Category: Oracle Database

Answered By technical007 0 points N/A #91956

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

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;

About technical007

Questions
0

Answers
14

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/12/2011
• Question Category: Oracle Database

Answered By Joseph King 270 points N/A #91957

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

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

About Joseph King

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/12/2011
• Question Category: Oracle Database

Answered By technical007 0 points N/A #91958

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

It will be a single quotation.

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

About technical007

Questions
0

Answers
14

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Oracle Database

Answered By Joseph King 270 points N/A #91959

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

Thanks a lot Tech. That was amazing.

About Joseph King

Questions
1

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/13/2011
• Question Category: Oracle Database