OraFAQ Forum: Server Administration » Is tablespace size required to increase

Home » RDBMS Server » Server Administration » Is tablespace size required to increase (Oracle, 10.2.0.2.0,Unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is tablespace size required to increase [message #532022]

Sat, 19 November 2011 11:20

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Hi,

I found some of the tablesapces whose used% exceeded 80%.Based on this output i thought of increasing the datafile.But there are many tablespaces of DATA1 whose autoextensible column some are set to NO, and some are set to YES. I believe we dont need to bother about the tablespace whose Autoextensible column set to YES as Oracle will automatically manage until the Maxsize of it. But some tablesapce of DATA1 are set to NO. So what to do in such case? Should i recommend to add size for this DATA1 tablespace whose autoextensible are set to YES/NO or leave it as it is.

Please see the column below.

Quote:

FILE_ID TABLESPACE_NAME SIZE(M) USED(M) FREE(M) MAX(M) AUTOEXTENSIBLE % USED
59 DATA1 32767 32741.13 25.75 32767 YES 100
60 DATA1 32767 32741.69 25.19 32767 YES 100
61 DATA1 32767 32740 26.88 32767 YES 100
76 DATA1 32700 32193.31 506.56 0 NO 98
77 DATA1 32700 30858.5 1841.38 0 NO 94
78 DATA1 32700 29476.56 3223.31 0 NO 90

Your input is appreciated.

Thanks

Report message to a moderator

Re: Is tablespace size required to increase [message #532023 is a reply to message #532022]

Sat, 19 November 2011 11:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what problem are you trying to solve?
How will independent observer conclude the problem has been solved?

Report message to a moderator

Re: Is tablespace size required to increase [message #532025 is a reply to message #532022]

Sat, 19 November 2011 11:28

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

You seem to be using the terms TABLESPACE and DATAFILE interchangeably. They are not the same thing. If you go back to your post, and consider for each occurance of these words which one you actially mean, I think the answer will become obvious.

Report message to a moderator

Re: Is tablespace size required to increase [message #532026 is a reply to message #532023]

Sat, 19 November 2011 11:30

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

I want to know whether i can increase the size of DATA1 tablesapce whose autoextesible column set to YES/No.

Thanks

Report message to a moderator

Re: Is tablespace size required to increase [message #532028 is a reply to message #532026]

Sat, 19 November 2011 11:35

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

As I said, you are confusing tablespaces with datafiles. Autoextend is not an attribute of a tablespace, it is an attribute of a datafile. You need to re-state your problem using the correct terms.

Report message to a moderator

Re: Is tablespace size required to increase [message #532029 is a reply to message #532028]

Sat, 19 November 2011 11:46

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

John,

As per the tablespace output and your inputs, i would say the file_id 76,77,78 datafiles
of tablesapces DATA1 whose autoextensible column set to NO has to be increased .

Am i correct ?

Thanks

Report message to a moderator

Re: Is tablespace size required to increase [message #532031 is a reply to message #532022]

Sat, 19 November 2011 11:56

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel

[Updated on: Sat, 19 November 2011 11:56]

Report message to a moderator

Re: Is tablespace size required to increase [message #532032 is a reply to message #532031]

Sat, 19 November 2011 11:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>As per the tablespace output and your inputs, i would say the file_id 76,77,78 datafiles
>of tablesapces DATA1 whose autoextensible column set to NO has to be increased .

>Am i correct ?
No, not correct.

Other data files can accept new data.

Report message to a moderator

Re: Is tablespace size required to increase [message #532034 is a reply to message #532029]

Sat, 19 November 2011 12:47

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

OK, you do seem to have worked out the difference between tablespaces and datafiles. But now, think about what you are trying to achieve. You seem to think that "only" 20% unused space is too little. If so, you need to increase the size of one or more datafiles. Do you think it matters which datafile(s) you resize?

Report message to a moderator

Re: Is tablespace size required to increase [message #532042 is a reply to message #532032]

Sat, 19 November 2011 23:31

Database admin
Messages: 365
Registered: September 2006
Location: india

Senior Member

Thank you Swan and John for your answers.

Report message to a moderator

Re: Is tablespace size required to increase [message #532117 is a reply to message #532042]

Sun, 20 November 2011 20:56

andy huang
Messages: 498
Registered: July 2011

Senior Member

hi,
The autoextend attribute of datafile is YES and NO, which is the command use in product application? which has advantage?

Report message to a moderator

Re: Is tablespace size required to increase [message #532118 is a reply to message #532117]

Sun, 20 November 2011 21:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ALTER command

Report message to a moderator

Re: Is tablespace size required to increase [message #532122 is a reply to message #532118]

Sun, 20 November 2011 21:39

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks BlackSwan,
Wich is the most use in product, YES or NO about autoextend attribute?

Report message to a moderator

Re: Is tablespace size required to increase [message #532124 is a reply to message #532122]

Sun, 20 November 2011 21:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Wich is the most use in product, YES or NO about autoextend attribute?
Answer depends upon shop/DB management philosophy.
Each has benefits/costs, pros/cons, & advantages/disadvantages.

AUTOEXTEND=Y can result in infrequent occurrence of rogue SQL filling up disk volume & crashing whole application or server?
AUTOEXTEND=N can result in more frequent transaction errors after datafile gets full that impacts fewer folks.

Pick your poison.

Report message to a moderator

Re: Is tablespace size required to increase [message #532183 is a reply to message #532124]

Mon, 21 November 2011 04:09

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks BlackSwan,
If the autoextend is set to Y,then the size of datafile will increase illimitably,does it will case performance issue when access the datafile?

Report message to a moderator

Re: Is tablespace size required to increase [message #532184 is a reply to message #532183]

Mon, 21 November 2011 04:25

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

If the autoextend is set to Y,then the size of datafile will increase illimitably

You can give a limit using MAXSIZE.

Quote:

it will case performance issue when access the datafile?

No.

Regards
Michel

Report message to a moderator

Re: Is tablespace size required to increase [message #532187 is a reply to message #532183]

Mon, 21 November 2011 04:34

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

In the original post, the file MAXSIZE is given as about 32G. If OP is using 8K blocks, 32G is the maximum file size possible. So the only option is to add more datafiles.
With regard to performance, when using autoextend it is vital to set the NEXT attribute to a sensible figure. I usually set it to 1G. The default NEXT is tiny, and your database will be crippled by zillions of resizing operations.

Report message to a moderator

Re: Is tablespace size required to increase [message #532298 is a reply to message #532187]

Mon, 21 November 2011 19:00

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks!

Report message to a moderator

Re: Is tablespace size required to increase [message #532299 is a reply to message #532298]

Mon, 21 November 2011 19:13

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks Michel,
Can you explain the flowing your answer?
Quote:

it will case performance issue when access the datafile?
No.

If the autoextend is set to Y and MAXVAULE is set to 32G,there is a big size datafile,and the size of the datafile is 32G;if the autoextend is set to N,and the big datafile break into 4 datafiles,each size of the smaller datafile is 8G,I think it will decrease I/O when access the 4 smaller datafiles.

Report message to a moderator

Re: Is tablespace size required to increase [message #532309 is a reply to message #532299]

Mon, 21 November 2011 23:12

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I think it will decrease I/O when access the 4 smaller datafiles.

Why do you think that? Explain.

Regards
Michel

Report message to a moderator

Re: Is tablespace size required to increase [message #532352 is a reply to message #532309]

Tue, 22 November 2011 01:46

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks Michel,
I do not know what detail clearly ,but i think if many sessiones access the one big datafile,it will increase I/O.
and many sessiones access 4 smaller datafile,it will be blance the I/O.

Report message to a moderator

Re: Is tablespace size required to increase [message #532354 is a reply to message #532352]

Tue, 22 November 2011 02:10

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

But there should be a reason that makes you think that.

Regards
Michel

Report message to a moderator

Re: Is tablespace size required to increase [message #532358 is a reply to message #532352]

Tue, 22 November 2011 02:46

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

To paraphrase Black Swan, "Are there fewer calories in your pizza if it is cut into 8 slices, rather than 12?"

Report message to a moderator

Re: Is tablespace size required to increase [message #532395 is a reply to message #532358]

Tue, 22 November 2011 04:55

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks for your answer!
In my product system,all datafiles were set to N by no autoextend.

Report message to a moderator

Re: Is tablespace size required to increase [message #532781 is a reply to message #532022]

Thu, 24 November 2011 11:34

emergingdba
Messages: 13
Registered: April 2011
Location: bangalore

Junior Member

@balaji14 - I dont know if your issue is resolved. Thought this might help.

Few things u need to know
1. Tablespaces are made up of one or more datafiles
2. Tablespace is a logical structure and datafiles are part of the physical structure.

Tablespace management is one of the important dba tasks. It is important to monitor the tablespace growth. When tablespaces begin to fill (as it is in your case), there are 2 options available to manage the growth.
1. Resize the existing datafile (if space is available in the mount point) so that there is sufficient space available in the tablespace.

ALTER DATABASE DATAFILE '<datafile_name>' RESIZE <final size of datafile>;

OR

2. Add another datafile to the tablespace which will again add suffcient space to tablespace structure.

ALTER TABLESPACE <tbs_name> ADD DATAFILE '<datafile_name>' SIZE <size of datafile>;

And coming to autoextensible option - if this is set to YES then the datafile will auto extend (until maxsize if specified or until the mount point is full)

If it is set to NO, the DBA intervention is required to add more space to the datafile.

Usually it is a standard practice to set autoextensible to one value (eiter YES or NO ) for all the datafiles. If a datafile is added manually then the default value for autoextensible is NO. Check and confirm if datafiles need to be autoextensible or not. Accordingly it can be changed as

ALTER DATABASE DATAFILE '<datafile_name>' AUTOEXTEND <ON|OFF>;

Report message to a moderator

Re: Is tablespace size required to increase [message #532861 is a reply to message #532781]

Fri, 25 November 2011 03:55

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks!

Report message to a moderator

Previous Topic:	What can be the maximum size of Data file
Next Topic:	ORA-04à30 (split from Table space with different block size inside same database)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 07:28:01 CDT 2024