At my previous company, I managed an OPS database ver. 8.1.7.1
with ALL tablespaces locally managed (auto allocate) and on the raw
devices.There were no problems, if we disregard the famous Philamae
comparison.
On Thu, 2003-10-02 at 15:14, Louis Avrami wrote:
Hi all,
I just inherited an
Jared
I'm with you on this one. I switched our production tablespaces (except
system) to autoextend several years ago and couldn't be happier. I used to
scrupulously check the free space in tablespaces, but over the years, being
a solo DBA, as more instances were added, this took longer and
of pctincrease should I set? What about the other settings?
Just curious.
Ryan
From: BALA,PRAKASH (HP-USA,ex1) [EMAIL PROTECTED]
Date: 2003/03/21 Fri PM 12:54:41 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Locally Managed Tablespaces
Once you set to uniform extents
: BALA,PRAKASH (HP-USA,ex1) [EMAIL PROTECTED]
Date: 2003/03/21 Fri PM 12:54:41 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Locally Managed Tablespaces
Once you set to uniform extents, pctincrease will default to 0.
Most Oracle gurus advise to just use uniform
AUTOEXTEND can be abused for sure, but it can also
be a big time saver.
Say you want to load 100 gigabytes of data, and you have
5 disks to spread it out on. You opt for 5 files of 4 gig each
on each disk.
That gives you 20 files to create in your tablespace. Creating
100 gig of datafiles
PCTINCREASE is not relevant if you are using locally managed
tablespaces.
The DBA did tell you correctly, but he/she meant for dictionary managed
tablespaces, not LMT. In 8i, the SYSTEM tablespace still has to be
dictionary managed, while in 9i the default is now LMT
--- [EMAIL PROTECTED]
RG
Here is a Web page that explains it pretty well.
http://www.samoratech.com/TopicOfInterest/swLMT.htm
You need to decide whether you want your data file to autoextend. I can't
recall if that is the default or not.
You can't set pctincrease for the tablespace and if you think about
Once you set to uniform extents, pctincrease will default to 0.
Most Oracle gurus advise to just use uniform extents for all situations.
-Original Message-
Sent: Friday, March 21, 2003 10:59
To: Multiple recipients of list ORACLE-L
This is probably pretty basic, so please keep in mind
]
Subject: RE: Locally Managed Tablespaces
Once you set to uniform extents, pctincrease will default to 0.
Most Oracle gurus advise to just use uniform extents for all situations.
-Original Message-
Sent: Friday, March 21, 2003 10:59
To: Multiple recipients of list ORACLE-L
Title: RE: RE: Locally Managed Tablespaces
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
thanks in case I happen to work on a 7.3 database
what kind of pctincrease should I set? What about the other
settings? Just curious.
The current thinking
of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Locally Managed Tablespaces
Once you set to uniform extents, pctincrease will default to 0.
Most Oracle gurus advise to just use uniform extents for all
situations.
-Original Message-
Sent: Friday, March 21, 2003 10:59
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: RE: Locally Managed Tablespaces
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
thanks in case I happen to work on a 7.3
For 7.3 it is also important to set MINIMUM EXTENT
for the tablespace to match the initial and next - then
every extent has to be at worst a multiple of the minimum
extent size whatever a rogue user does.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
For one-day tutorials:
(see
Title: RE: RE: Locally Managed Tablespaces
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
I think my 'reasonable' # of extents must be higher than
yours Jacques. :)
I agree that having a datafile autoextend is better than having a job die. On the other
must be higher than yours Jacques. :)
Jared
Jacques Kilchoer [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/21/2003 11:00 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: RE: Locally
Title: RE: RE: Locally Managed Tablespaces
I think minimum extent was a new parameter in 8.0.
-Original Message-
From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
For 7.3 it is also important to set MINIMUM EXTENT
for the tablespace to match the initial and next - then
every
I knew I'd forget something :) it's been a while since I worked on
7.3
thanks!
--- Jonathan Lewis [EMAIL PROTECTED] wrote:
For 7.3 it is also important to set MINIMUM EXTENT
for the tablespace to match the initial and next - then
every extent has to be at worst a multiple of the minimum
Title: RE: RE: Locally Managed Tablespaces
Well, my first suggestion would be to buy a software package from a reputable software company that lets you predict object growth and an estimate of when your tablespace will be full. Contact me for more details. :)
But seriously, you can write
One can 'plug' in a DMT via TTS. but can not make it writable... ;)
- Kirti
-Original Message-
Sent: Thursday, June 13, 2002 12:39 PM
To: Multiple recipients of list ORACLE-L
I would treat dmt's as obsolete. (In 9.2, all
tablespaces default to locally managed, and
furthermore,
I am using 9.0.1 - can the system tablespace be setup as lmt? Or is that new in 9.2?
Michele
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California--
only in 9.2 ... that's the default.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but
That is new in 9.2.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 14, 2002 2:04 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Locally Managed Tablespaces
I am using 9.0.1 - can the system tablespace be setup as lmt
9.2
[EMAIL PROTECTED] wrote:
I am using 9.0.1 - can the system tablespace be setup as lmt? Or is that new in 9.2?
Michele
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joe Testa
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
only in 9.2 can system be an lmt
--- [EMAIL PROTECTED] wrote:
I am using 9.0.1 - can the system tablespace be setup as lmt? Or is
that new in 9.2?
Michele
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network
of list ORACLE-L
Subject: RE: Locally Managed Tablespaces
only in 9.2 can system be an lmt
--- [EMAIL PROTECTED] wrote:
I am using 9.0.1 - can the system tablespace be setup as lmt? Or is
that new in 9.2?
Michele
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
:
Subject:RE: Locally Managed Tablespaces
Well, not really. I did that by slightly modifying sql.bsq
in 8.1.7.2, but I'm not quite sure that modifying sql.bsq
is something that Oracle Support likes to hear about...
You can do it in a supported way as of 9.2.
The same goes
-
From: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 14, 2002 2:57 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Locally Managed Tablespaces
only in 9.2 can system be an lmt
--- [EMAIL PROTECTED] wrote:
I am using 9.0.1 - can the system tablespace
this list.
Jared
Gogala, Mladen [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
06/14/2002 12:28 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Locally Managed Tablespaces
Well, not really. I did
I would treat dmt's as obsolete. (In 9.2, all
tablespaces default to locally managed, and
furthermore, if you create system as lmt in 9.2, all
subsequent tablespaces must also be lmt)..
I would not be surprised to see dmt's disappear
altogether at some stage in future.
hth
connor
--- [EMAIL
PROTECTED]]
Sent:Wednesday, April 17, 2002 11:34 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Locally managed tablespaces/What a DBA can do ?
What about your locally managed temporary
tablespace?
--- Koivu, Lisa [EMAIL PROTECTED] wrote:
What? I was just
-Original Message-
From: Mohammad Rafiq [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, April 17, 2002 1:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Locally managed tablespaces/What a DBA can do ?
Lisa,
What version you are talking about? I am using locally managed
of a temporary tablespace.
LK
-Original Message-
From:Mohammad Rafiq [SMTP:[EMAIL PROTECTED]]
Sent:Wednesday, April 17, 2002 1:54 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Locally managed tablespaces/What a DBA can do ?
Lisa,
What version
Rafiq,
I upgraded an HP-UX 11.0 64-bit machine in early April. (8.1.6.3.0 to
8.1.7.3.0) It was the worst upgrade I've ever gone through, but everthing
appears to be running normally. (I'm not using locally managed tablespaces
yet though.) I was missing a couple of operating system patches and
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 17, 2002 11:34 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Locally managed tablespaces/What a DBA can do ?
What about your locally managed temporary
tablespace?
--- Koivu, Lisa [EMAIL PROTECTED] wrote:
What? I was just
Julie,
We are also going through same exercise now a days as we are moving to new
HP hardware with HP-UX 11 64 bits with Hitachi Storage. These machines do
not run on 32 bit Unix. However we are still using 32 bit Oracle and Oracle
Financials 10.7 char software.
In our situation we have to
I have upgraded 8.1.6 to 8.1.7.2(32-bit) on HP-UX 11.0 64-bit machine and
use LMT on all my databases. They run fine and one of the database has been
running over 1 year now.
-Original Message-
Sent: Wednesday, April 17, 2002 12:59 PM
To: Multiple recipients of list ORACLE-L
.
LK
-Original Message-
From: Robert Pegram [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, April 17, 2002 11:34 AM
To: Multiple recipients of list ORACLE-L
Subject:RE: Locally managed tablespaces/What a
DBA can do ?
What about your locally managed temporary
;
Tablespace altered.
-Original Message-
From: Robert Pegram [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, April 17, 2002 9:58 AM
To: Multiple recipients of list ORACLE-L
Subject:RE: Locally managed tablespaces/What
a
DBA can do ?
Darren
Okay, maybe re-org would be a little too much, as you say, I would need
to know the application better, and with 8 distinctly different apps,
including
oracle financials, I maybe heading for trouble.
But what about simply turning the existing dictionary-managed tablespaces
into
locally managed
Yes - on 8i and 9i.
There were a couple of issues under 8i, where you
needed to have an additional rollback segment in a
non-LMT (ie SYSTEM) in order to create lmt rollback
tspaces and segments, but other than that, there have
been no problems so far.
hth
connor
--- [EMAIL PROTECTED] wrote:
Antonio,
We use LMTs for all tablespaces - except for System, of course. No issues,
problems, etc. We're happy.
8.1.7.2.5 on Win2k; ~340GB in 29 tablespaces.
Jack
Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin,
And the hot tip is that SYSTEM will be locally managed
(by default) from 9i.2, which (I hope) will render
dictionary managed tspaces to the dustbin.
Cheers
Connor
--- Jack C. Applewhite [EMAIL PROTECTED]
wrote: Antonio,
We use LMTs for all tablespaces - except for System,
of course. No
:
Fax to:
Subject:RE: Locally managed tablespaces
Heather:
Another thing to consider. If the vendor of your student application
system has not worked with LMTs, you may encounter support issues should you
have problems with database accessibility. I would verify that LMTs are
supported
Hi
Any reason that this external consultant is not using the package
dbms_space_admin.tablespace_migrate_to_local procedure ??
This would seem a much cleaner method of doing this than the method they
are recommending.
>From the plsql supplied reference.
TABLESPACE_MIGRATE_TO_LOCAL
Hi
John,
Thanks so much for ur
advice.never knew about that procedure before. By the
way,
could u please tell me what
do the allocation_unit, relative_fnowithin the paranthesis
mean ?? Or could u just
give me a document link where I can read more about this procedure
??
Regards,
The two main benefit of lmt's are
- the enforcement of a consistent extent size.
- avoiding issues with FET$ and UET$
Using migrate_to_local achieves the latter but not the
former.
hth
connor
--- orantdba [EMAIL PROTECTED] wrote: Hi
Any reason that this external consultant is not
If you can avoid the downtime, then its a good move.
Its really a move toward consistent extent sizes per
tablespace - LMT's just enforce that.
hth
connor
--- Docherty, Heather [EMAIL PROTECTED]
wrote: I have just heard today that an external
consultant,
who is coming to
upgrade software
Heather - Is there a particular reason the consultant is doing this other
than maybe this is the first opportunity to learn this? Just my cynical
side. Mentioning cynical, I was leery of the procedure to convert an
existing dictionary-managed tablespace to a locally-managed one, but we
production
-
Sent: Friday, December 14, 2001 3:26 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Locally managed tablespaces
Heather - Is there a particular reason the consultant is doing this other
than maybe this is the first opportunity to learn this? Just my cynical
side
: (248) 408-2918
Email: [EMAIL PROTECTED]
Web:www.compuware.com
-Original Message-
Sent: Friday, December 14, 2001 3:26 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Locally managed tablespaces
Heather - Is there a particular reason the consultant is doing
: Locally managed tablespaces
Heather - Is there a particular reason the consultant is doing this other
than maybe this is the first opportunity to learn this? Just my cynical
side. Mentioning cynical, I was leery of the procedure to convert an
existing dictionary-managed tablespace to a locally
Quick follow up to the bitmap sizing for
larger files: I've quoted this 20-30,000
figure in my book as applying to both
'ordinary' LMTs and 'temporary' LMTs.
However following our discussions, and
a conversation with Ken Robinson from
Oracle, I've run a couple of tests on 8.1.7
and the 2-bytes
Thanks for investigating and sharing this with us
- Babette
-Original Message-
Lewis
Sent: Wednesday, August 15, 2001 7:16 AM
To: Multiple recipients of list ORACLE-L
Quick follow up to the bitmap sizing for
larger files: I've quoted this 20-30,000
figure in my book as applying to
Is there really any considerable overhead involved in dynamically
allocating an extent? By creating free space chunks in a tablespace
of uniform extent size, will you really reduce the amount of time and
resources spent when a segment allocates those extents?
I doubt it, but statistics from
Considerable overhead? Not really, but I only said overhead, not
considerable overhead. There are, as you are well aware, potential
issues with ST locking on very busy systems with dictionary managed
tablespaces. Pre-fragmenting can (not will) reduce the duration of the ST
lock hold. Extent
If you are using uniform extents in LMT, you don't want to coalesce - ever.
Since every extent will be the same size, it is useless overhead. This is
even more true if you have adopted a uniform extents policy in any
dictionary managed tablespaces. In fact, to reduce the overhead of throwing
a
SMON will only automatically coalesce if pctincrease is 0 but you
can always ask for a coalesce. Its part of the alter tablespace command
and is the method I use.
However, according to the doco there is no need to coalesce a LMT.
-Original Message-
Sent: Friday, July 20, 2001 7:01 AM
consider this also..
if u make a tablespace as LMT, the SMON process is not going to coalesce it
automatically..
as it in the case of dictionary managed TS.
if i'm wrong.. pls correct.
Saurabh Sharma
[EMAIL PROTECTED]
http://www.geocities.com/saurabh00pc/stride.html
- Original Message
That's kind of the point -- in an LMT the free space is managed by bitmap. If
it's contiguous in the bitmap it's contiguous, so if you free two adjacent
blocks then they're already coalesced, nothing for SMON to do. That's their
advantage -- no overhead for coalesce and no overhead for creating
It does not need to do it. Also SMON will coalesce only if pctincrease0 and
this is not a good idea.
Alex Hillman
-Original Message-
Sent: Friday, July 20, 2001 8:41 AM
To: Multiple recipients of list ORACLE-L
consider this also..
if u make a tablespace as LMT, the SMON process is not
[EMAIL PROTECTED]
Subject: Re: Locally managed tablespaces
Date: Fri, 20 Jul 2001 04:40:49 -0800
consider this also..
if u make a tablespace as LMT, the SMON process is not going to coalesce it
automatically..
as it in the case of dictionary managed TS.
if i'm wrong.. pls correct.
Saurabh Sharma
Thursday, July 19, 2001, 12:42:33 PM, Kimberly Smith wrote:
KS However, I was under the impression that you could not specify a next in the
KS storage clause of a table creation. Yet you can. So what happens if I have
KS a next defined? Do they pretty much just throw that away or should I
Not positive but I would think Oracle just ignores the NEXT parameter in
locally managed tablespaces
Rick
-Original Message-
Sent: Thursday, July 19, 2001 12:43 PM
To: Multiple recipients of list ORACLE-L
HPUX 11i 64 bit
Oracle 8.1.7.1 32 bit
I am creating a new database and decided
You canuse any storage parameters you like but oracle will ignore them exept
for initial for LMT with autoallocate. I would personally do not use
autoallocate. Also you cannot use autoallocate for temporary tablespace and
shouldn't use it for rollback.
Alex Hillman
-Original Message-
With LMT, Oracle allocates extents the way the
tablespace was created even if you specify storage at
the table level.
--- Kimberly Smith [EMAIL PROTECTED] a
écrit : HPUX 11i 64 bit
Oracle 8.1.7.1 32 bit
I am creating a new database and decided to go with
locally managed
tablespaces. I
The size of extents that are managed locally are determined by the system.
The LOCAL clause of the extent management specifies this menthod in create
statement.
Sunil Nookala
Dell Computer Corp.
Austin, TX
-Original Message-
Sent: Thursday, July 19, 2001 11:43 AM
To: Multiple recipients
Actually, I discovered that you cannot use it for the rollback tablespace
either. Oh, you can create the tablespace no problem but then you cannot
create the rollback segments in it. Something that the doco failed to
mention.
I did catch the temporary one in the doco though.
-Original
Kimberly,
Keep in mind that when you use LMT autoallocate option that the extents can grow at a
different sizes. The Autuallocate option allows
Oracle to take control of the extent allocation. Oracle will use extent
sizes of 64KB, 1MB, 8MB, and 64MB to manage space in the tablespace. The
table
But I am having rollback segments using LMT.. I am on 8.1.5...
-Original Message-
Sent: 19 July 2001 18:57
To: Multiple recipients of list ORACLE-L
Actually, I discovered that you cannot use it for the rollback tablespace
either. Oh, you can create the tablespace no problem but then
On Thursday 19 July 2001 10:56, Kimberly Smith wrote:
Actually, I discovered that you cannot use it for the rollback tablespace
either. Oh, you can create the tablespace no problem but then you cannot
create the rollback segments in it. Something that the doco failed to
mention.
It is the autoallocate that it complains about. Not even sure
why I would have wanted to use autoallocate anyway but you
have to give it the uniform size parameter.
-Original Message-
Sent: Thursday, July 19, 2001 11:47 AM
To: Multiple recipients of list ORACLE-L
But I am having
On Thursday 19 July 2001 10:56, Kimberly Smith wrote:
Actually, I discovered that you cannot use it for the rollback tablespace
either. Oh, you can create the tablespace no problem but then you cannot
create the rollback segments in it. Something that the doco failed to
mention.
Ah, never
Guess I really have to learn to write in full sentences, which
will be hard since I don't even talk that way. Its the autoallocate
you can't use. I created it just fine with the uniform size.
Sorry for the confusion everyone.
-Original Message-
Sent: Thursday, July 19, 2001 10:59 AM
Yes, you can make any tablespace a locally-managed tablespace except SYSTEM.
What they're saying, I think, is that you can't set up temporary tablespaces
and shouldn't set up rollback segments with the AUTOALLOCATE allocation
management.
Jon Walthour
- Original Message -
To: Multiple
We are using LMTs with several of our databases. No problems. With these
databases we are also running large file systems. Although our OS imposes
a theoretical limit of 31.G, we have kept our datafiles to 20G or
less. Again, no problems.
Pete Barnett
Oracle Database Administrator
]]
Sent: 18 April 2001 01:03
To: Multiple recipients of list ORACLE-L
Subject:Re: Locally Managed Tablespaces
Patricia,
FWIW, We have been using them for several months now and
have not seen anything negative as a result
We've been using them here for about 8 months
now with no problems. Solaris 2.7, Oracle 8.1.6.3.
Patricia Ashe [EMAIL PROTECTED]@fatcity.com on 04/17/2001 07:35:49 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL
I'm using LMTs with the UNIFORM EXTENT option on 8.1.6[.2], with great
results. Two caveats I've noticed:
1) If you want to use LMTs for rollback segments, you MUST create at
least one rollback segment in a dictionary managed tablespace first (in
addition to the ever-present "system" RBS).
I remember ssing somebody suggestion to create normal file in advance and
then create temporary tablespace with reuse.
Alex Hillman
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 18, 2001 2:56 PM
To: Multiple recipients of list ORACLE-L
I'm using LMTs with the
Patricia,
FWIW, We have been using them for several months now and have not seen anything
negative as a result. We decided to start using them for all new tablespaces and we
add a new set of tablespaces every month to accomodate our partitioning strategy.
Our biggest databases that we are
80 matches
Mail list logo
