RE: LMTs
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Mar 2008 13:31:04 -0400
Niall's concerns and preferences look pretty good to me.
-- Mark D Powell --
Phone (313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Niall Litchfield
Sent: Tuesday, March 11, 2008 11:35 AM
To: Mayen.Shah@xxxxxxxxxx
Cc: joe_dba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx;
oracle-l-bounce@xxxxxxxxxxxxx
Subject: Re: LMTs
Hi Shah,
I'm not convinced that I'm any more likely to run out of space
if my alert is configured for 95% space usage or space used being within
5% of a calculated max value. I entirely agree that the more databases
and the more tablespaces that you have the more difficult monitoring
becomes - and the more attractive automating space management becomes,
but I'm always going to have limits on
1. filesystem size
2. tape capacity
3. backup window duration
4. filesystem size on clone databases
5. filesystem size on standbys
I'm sure there are others, which is why my personal preference
is for fixed size datafiles and manual intervention. That said I only
have 29 Oracle databases to monitor - though that does include 8 apps
ones! I'm sure that others will have significantly more.
On Tue, Mar 11, 2008 at 12:32 PM, <Mayen.Shah@xxxxxxxxxx> wrote:
Niall,
Only problem in setting autoextensible to off is if you
run out space in middle of business day, some transactions will fail and
rest will be the history......
In large environment with many production database, with
many more tablespaces it becomes difficult to monitor and maintain
manually to make sure each tablespace has enough free space. I am using
all tablespace autoextensible with fixed upper limit. I also have set up
monitoring (patrol in my case) to send e-mail any time any tablespace is
autoextended and mobile alert when actual size is within 5% of maxsize.
If any tablespace has multiple datafile, I have kept only one datafile
as autoextensible.
Mayen Shah
"Niall Litchfield" <niall.litchfield@xxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
Mar 11 2008 05:37 AM
Please respond to
niall.litchfield@xxxxxxxxx
To
joe_dba@xxxxxxxxxxx
cc
oracle-l@xxxxxxxxxxxxx
Subject
Re: LMTs
On Mon, Mar 10, 2008 at 2:30 PM, Joe Smith
<joe_dba@xxxxxxxxxxx <mailto:joe_dba@xxxxxxxxxxx> > wrote:
CREATE TABLESPACE data
DATAFILE '/FS/data_s01.dbf' size 2000m autoextend on
next 1m maxsize 12000m,
'/FS/data_s02.dbf' size 2000m autoextend on
next 1m maxsize 12000m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
How do you control the size of LMTs.
If I remove the "autoextend on next 1m" I can't use the
"maxsize" keyword.
How do I restrict the size of the datafiles for LMTs
Hey Joe (always wanted to say that sorry)
You have a choice. Either you want the datafiles to grow
as needed and limit the total size to which they can grow - i.e to be
autoextensible - in which case it makes sense that you need both the
amount by which to grow each time and the absolute limit. Alternatively
you know how big you want them to be and you just specify the fixed size
for the datafile (no autoextension at all).
I happen to prefer the latter - not least because it
then becomes easy to tell when you are running out of space in a
tablespace (how much free space is left), whereas when the datafiles are
autoextensible it's very easily to miscalculate how full a tablespace
is. I also like to change control space operations because they have an
impact on clones, backups dataguard space requirements and so on. If you
do prefer to let Oracle handle the growth then I'd suggest a rather
larger next size than 1m. Once you get to 2gb of data every time you add
1mb more data you'll be growing the datafile which is a lot of growth
operations. You'll also likely cause more filesystem fragmentation -
though you might not care about that.
--
Niall Litchfield
Oracle DBA
http://www.orawin.info <http://www.orawin.info/>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
