RE: Convert to Locally-Managed Tablespaces
The release specific bits are stored in release specific tablespaces,
which are migrated to new tablespaces during the upgrade. You can
reclaim the space afterward.
For example, upgrading 4.0B to 4.6C will cause a migration of data from
PSAPES40BD to PSAPES46CD, among others. Newer releases based on WAS
6.20 and higher use PSAP instead (e.g. PSAPDEV620 or
PSAPPRD620 for a DEV or PRD db running 6.20). Upgrades are done the
same way, e.g. PSAPDEV620 -> PSAPDEV630 and you can free up PSAPDEV620's
segments afterward.
Rich
--
Rich Holland(913) 645-1950SAP Technical Consultant
print unpack("u","92G5S\=\"!A;F]T:&5R(\'!E -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> Behalf Of DENNIS WILLIAMS
> Sent: Tuesday, January 06, 2004 3:54 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Convert to Locally-Managed Tablespaces
>
>
> Jared - I don't know SAP, but other canned applications have
> a "wonderful"
> process they put you through every few years called an
> upgrade. Often you
> end up creating another database and moving the data. This is a great
> opportunity for the DBA to fix some issues like this.
> However, since SAP is
> used by some REALLY large organizations with really, really
> large amounts of
> data, perhaps SAP uses a different practice. I would like to
> know if that is
> the case.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
> -Original Message-
> Sent: Monday, January 05, 2004 8:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Tanel,
>
> That's a good idea. I briefly considered this, but
> didn't really dig into it.
>
> The systems I need to do this on is our SAP systems, and
> downtime is a precious commodity, especially for production.
>
> I just may try this on our test system. The problem with
> SAP of course, and many other ERP's is that there are 22k+
> tables, which could consume a bit of time.
>
> The amount of fragmented space that would be recovered is
> probably not worth the trouble of this procedure, depending
> on how much time it takes.
>
> I see that you too need to keep the original tablespace names,
> is this SAP per chance?
>
> If you have already performed a test of this, what kind of
> times are you seeing, along with relevant platform information,
> and the number of tables/indexes?
>
> Jared
>
>
> On Mon, 2004-01-05 at 16:49, Tanel Poder wrote:
> > Hi!
> >
> > > This is what I will need to use on our systems, as there
> are about 400
> gig
> > > of data and indexes. 200 gig of data is too large to
> export/import, at
> > least
> > > it is for this project. So dbms_space_admin it will be.
> >
> > I'm about to do a reorg+conversion of a 250GB 8.1.6
> database in next week,
> > here's what I'll do (there is practically no free space for
> temporary
> > usage):
> >
> > 1) Export index definitions (normal export with rows=n)
> > 2) Drop all indexes
> > 3) use alter table move with parallel 16 and nologging to
> move all tables
> to
> > old index tablespaces (the indexes consumed more space than tables)
> > 4) drop and recreate data tablespaces
> > 5) use alter table move again to move tables back (the
> segments have to
> > reside in original tablespaces, otherwise I could have
> skipped this step)
> > 6) drop and recreate index tablespaces
> > 7) get index definitions out of exportfile and modify them
> to add parallel
> &
> > nologging (with big sort area size)
> > 8) rebuild indexes
> > 9) do a full backup
> >
> > It might help to recreate index tablespaces even before
> step 3, to speed
> up
> > parallel table moving a bit..
> >
> > Maybe you want to test this Jared, this approach is much faster than
> > export/import, because everything can be done with direct
> path operations
> > and nologging (import doesn't have direct path facility, so
> regular array
> > inserts are used, which always require logging as well).
> > Also, your tables/datablocks will be optimized after moving
> them (which is
> > not the case with dbms_space_admin) and you don't have to
> have any space
> for
> > reorg in case your cleared index tablespace can temporarily
> accommodate
> your
> > data.
> >
> > > IIRC one of the drawbacks of using dbms_space_admin to convert is
> > > that you won't be converting to nice unifo
RE: Convert to Locally-Managed Tablespaces
SAP expects tables to be in certain tablespaces: PSAPBTABD for instance. Same with indexes: PSAPBTABI The SAPDBA interactive tool is being phased out in favor of the command line tool brconnect. I don't know if brconnect allows some flexibility in tablespace names, but I doubt it. Just haven't got around to that particular topic yet. There are methods for doing fairly fast migrations should you need it, such as changing platforms. I would have preferred to do an exp/imp or something like it when we moved to new servers, but in the amount of time I had to work with, I could not come up with what I thought an acceptable method on windows. On *nix there would have been more leeway. There isn't any native named pipe functionality that is exposed to the shell on Win32. On top of that, Oracle compiles the utilities such as imp/exp with libs that prevent you from using stdin/stdout in the manner you would on unix. I found out too late about netcat: (http://dbasupport.com/oracle/ora9i/resolutions.shtml) So, it was the mig utility for us. Jared On Tue, 2004-01-06 at 12:54, DENNIS WILLIAMS wrote: > Jared - I don't know SAP, but other canned applications have a "wonderful" > process they put you through every few years called an upgrade. Often you > end up creating another database and moving the data. This is a great > opportunity for the DBA to fix some issues like this. However, since SAP is > used by some REALLY large organizations with really, really large amounts of > data, perhaps SAP uses a different practice. I would like to know if that is > the case. > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > -Original Message- > Sent: Monday, January 05, 2004 8:09 PM > To: Multiple recipients of list ORACLE-L > > > Tanel, > > That's a good idea. I briefly considered this, but > didn't really dig into it. > > The systems I need to do this on is our SAP systems, and > downtime is a precious commodity, especially for production. > > I just may try this on our test system. The problem with > SAP of course, and many other ERP's is that there are 22k+ > tables, which could consume a bit of time. > > The amount of fragmented space that would be recovered is > probably not worth the trouble of this procedure, depending > on how much time it takes. > > I see that you too need to keep the original tablespace names, > is this SAP per chance? > > If you have already performed a test of this, what kind of > times are you seeing, along with relevant platform information, > and the number of tables/indexes? > > Jared > > > On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: > > Hi! > > > > > This is what I will need to use on our systems, as there are about 400 > gig > > > of data and indexes. 200 gig of data is too large to export/import, at > > least > > > it is for this project. So dbms_space_admin it will be. > > > > I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, > > here's what I'll do (there is practically no free space for temporary > > usage): > > > > 1) Export index definitions (normal export with rows=n) > > 2) Drop all indexes > > 3) use alter table move with parallel 16 and nologging to move all tables > to > > old index tablespaces (the indexes consumed more space than tables) > > 4) drop and recreate data tablespaces > > 5) use alter table move again to move tables back (the segments have to > > reside in original tablespaces, otherwise I could have skipped this step) > > 6) drop and recreate index tablespaces > > 7) get index definitions out of exportfile and modify them to add parallel > & > > nologging (with big sort area size) > > 8) rebuild indexes > > 9) do a full backup > > > > It might help to recreate index tablespaces even before step 3, to speed > up > > parallel table moving a bit.. > > > > Maybe you want to test this Jared, this approach is much faster than > > export/import, because everything can be done with direct path operations > > and nologging (import doesn't have direct path facility, so regular array > > inserts are used, which always require logging as well). > > Also, your tables/datablocks will be optimized after moving them (which is > > not the case with dbms_space_admin) and you don't have to have any space > for > > reorg in case your cleared index tablespace can temporarily accommodate > your > > data. > > > > > IIRC one of the drawbacks of using dbms_space_admin to convert is > > > that you won't be converting to nice uniform extent sizes for existing > > data. > > > > Yes, and if your tablespace is fragmented, the fragmentation will remain > > there, despite your conversions (of course, smaller extents might be able > to > > use some of this fragmented space later on). > > > > Tanel. > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Tanel Poder > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.f
Re: Convert to Locally-Managed Tablespaces
Hi, I just use vi :%s/oldtablespacename/newtablespacename/g to edit the dump file and import it back.In my case, it did work. And it is oracle supported method of renaming tablespace in versions before 10g. Manual parallel means: I have to move 10 table, for example, so in one script, I will do: alter table a move parallel(10) nologging; alter table b move parallel(10) nologging; etc. With manual parallel, assume, table a-d has 100m, table e-f has 200m, table g has 400m, I will do: script a: alter table a move nologging; alter table b move nologging; alter table c move nologging ... script b: alter table e move nologing; alter table f move nilogging; script c: alter table g move nologging; And I run the three script in the same time. Regards. Zhu Chao. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 06, 2004 8:34 PM > Two questions for you: > > 1. The export file is binary format. Can you tell me > which editor you use? > > 2. How to manually parallel? > > Thanks. > > > --- zhu chao <[EMAIL PROTECTED]> wrote: > > Hi, Tanel: > > If you have plenty of downtime, everything will > > be ok. > > If you want to further limit the downtime, here > > is some suggestions: > > 1. I think manual parallel will always be better > > than oracle parallel. So I always use script, that > > means, I split the move table nologging script to 10 > > scripts and let them run concurrently. I call it > > manual parallel:).So does the index rebuild. > > > > 2. To avoid that huge table move again, we can > > consider using transportable_tablespace feature. I > > did test on my linux 920 box, but not in production. > > It did work. Steps like: > > set tablespace read only; > > check self constrainted. > > exp the metadata. > > edit the metadata dump, replace tablespace with > > new_tablespacename; > > drop the tablespace. > > imp back the metadata back. > > (The above is from oracle metalink ,but I forget > > about the noteid). > > > > regards > > Zhu Chao. > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" > > <[EMAIL PROTECTED]> > > Sent: Tuesday, January 06, 2004 8:49 AM > > > > > > > Hi! > > > > > > > This is what I will need to use on our systems, > > as there are about 400 gig > > > > of data and indexes. 200 gig of data is too > > large to export/import, at > > > least > > > > it is for this project. So dbms_space_admin it > > will be. > > > > > > I'm about to do a reorg+conversion of a 250GB > > 8.1.6 database in next week, > > > here's what I'll do (there is practically no free > > space for temporary > > > usage): > > > > > > 1) Export index definitions (normal export with > > rows=n) > > > 2) Drop all indexes > > > 3) use alter table move with parallel 16 and > > nologging to move all tables to > > > old index tablespaces (the indexes consumed more > > space than tables) > > > 4) drop and recreate data tablespaces > > > 5) use alter table move again to move tables back > > (the segments have to > > > reside in original tablespaces, otherwise I could > > have skipped this step) > > > 6) drop and recreate index tablespaces > > > 7) get index definitions out of exportfile and > > modify them to add parallel & > > > nologging (with big sort area size) > > > 8) rebuild indexes > > > 9) do a full backup > > > > > > It might help to recreate index tablespaces even > > before step 3, to speed up > > > parallel table moving a bit.. > > > > > > Maybe you want to test this Jared, this approach > > is much faster than > > > export/import, because everything can be done with > > direct path operations > > > and nologging (import doesn't have direct path > > facility, so regular array > > > inserts are used, which always require logging as > > well). > > > Also, your tables/datablocks will be optimized > > after moving them (which is > > > not the case with dbms_space_admin) and you don't > > have to have any space for > > > reorg in case your cleared index tablespace can > > temporarily accommodate your > > > data. > > > > > > > IIRC one of the drawbacks of using > > dbms_space_admin to convert is > > > > that you won't be converting to nice uniform > > extent sizes for existing > > > data. > > > > > > Yes, and if your tablespace is fragmented, the > > fragmentation will remain > > > there, despite your conversions (of course, > > smaller extents might be able to > > > use some of this fragmented space later on). > > > > > > Tanel. > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > > -- > > > Author: Tanel Poder > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > > http://www.fatcity.com > > > San Diego, California-- Mailing list and > > web hosting services > > > > > > -
RE: Convert to Locally-Managed Tablespaces
Hi Obviously I'm not Tanel, but we have a similar situation where we don't actually need to keep segments in specifically named tablespaces - but we do need when upgrading all of the specifically named tablespaces to exist, and have sufficient free speace for the upgrade. The app is cross-dbplatform and hardcodes at least some of the tablespace names (on mssql some of the database names) in the upgrade scripts. Frustratingly the tablespace names static, histr,transactional etc suggest an attempt to maintain segments in sappropriate tablespaces. Unfortunately the static segments change, the historic sgements contain current data, the transactional tables might be lookups My *impression* is that this sort of setup is actually quite common. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Convert to Locally-Managed Tablespaces
Well I don't know about SAP either, thank GOD. PeopleSoft does maintain the tablespace where tables and index are suppose to reside so that come upgrade time you can see what's moved, but at least they provide a rather painless way to change that if needed. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, January 06, 2004 3:54 PM To: Multiple recipients of list ORACLE-L Jared - I don't know SAP, but other canned applications have a "wonderful" process they put you through every few years called an upgrade. Often you end up creating another database and moving the data. This is a great opportunity for the DBA to fix some issues like this. However, since SAP is used by some REALLY large organizations with really, really large amounts of data, perhaps SAP uses a different practice. I would like to know if that is the case. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 05, 2004 8:09 PM To: Multiple recipients of list ORACLE-L Tanel, That's a good idea. I briefly considered this, but didn't really dig into it. The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production. I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time. The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes. I see that you too need to keep the original tablespace names, is this SAP per chance? If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes? Jared On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: > Hi! > > > This is what I will need to use on our systems, as there are about 400 gig > > of data and indexes. 200 gig of data is too large to export/import, at > least > > it is for this project. So dbms_space_admin it will be. > > I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, > here's what I'll do (there is practically no free space for temporary > usage): > > 1) Export index definitions (normal export with rows=n) > 2) Drop all indexes > 3) use alter table move with parallel 16 and nologging to move all tables to > old index tablespaces (the indexes consumed more space than tables) > 4) drop and recreate data tablespaces > 5) use alter table move again to move tables back (the segments have to > reside in original tablespaces, otherwise I could have skipped this step) > 6) drop and recreate index tablespaces > 7) get index definitions out of exportfile and modify them to add parallel & > nologging (with big sort area size) > 8) rebuild indexes > 9) do a full backup > > It might help to recreate index tablespaces even before step 3, to speed up > parallel table moving a bit.. > > Maybe you want to test this Jared, this approach is much faster than > export/import, because everything can be done with direct path operations > and nologging (import doesn't have direct path facility, so regular array > inserts are used, which always require logging as well). > Also, your tables/datablocks will be optimized after moving them (which is > not the case with dbms_space_admin) and you don't have to have any space for > reorg in case your cleared index tablespace can temporarily accommodate your > data. > > > IIRC one of the drawbacks of using dbms_space_admin to convert is > > that you won't be converting to nice uniform extent sizes for existing > data. > > Yes, and if your tablespace is fragmented, the fragmentation will remain > there, despite your conversions (of course, smaller extents might be able to > use some of this fragmented space later on). > > Tanel. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTE
RE: Convert to Locally-Managed Tablespaces
Jared - I don't know SAP, but other canned applications have a "wonderful" process they put you through every few years called an upgrade. Often you end up creating another database and moving the data. This is a great opportunity for the DBA to fix some issues like this. However, since SAP is used by some REALLY large organizations with really, really large amounts of data, perhaps SAP uses a different practice. I would like to know if that is the case. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 05, 2004 8:09 PM To: Multiple recipients of list ORACLE-L Tanel, That's a good idea. I briefly considered this, but didn't really dig into it. The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production. I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time. The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes. I see that you too need to keep the original tablespace names, is this SAP per chance? If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes? Jared On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: > Hi! > > > This is what I will need to use on our systems, as there are about 400 gig > > of data and indexes. 200 gig of data is too large to export/import, at > least > > it is for this project. So dbms_space_admin it will be. > > I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, > here's what I'll do (there is practically no free space for temporary > usage): > > 1) Export index definitions (normal export with rows=n) > 2) Drop all indexes > 3) use alter table move with parallel 16 and nologging to move all tables to > old index tablespaces (the indexes consumed more space than tables) > 4) drop and recreate data tablespaces > 5) use alter table move again to move tables back (the segments have to > reside in original tablespaces, otherwise I could have skipped this step) > 6) drop and recreate index tablespaces > 7) get index definitions out of exportfile and modify them to add parallel & > nologging (with big sort area size) > 8) rebuild indexes > 9) do a full backup > > It might help to recreate index tablespaces even before step 3, to speed up > parallel table moving a bit.. > > Maybe you want to test this Jared, this approach is much faster than > export/import, because everything can be done with direct path operations > and nologging (import doesn't have direct path facility, so regular array > inserts are used, which always require logging as well). > Also, your tables/datablocks will be optimized after moving them (which is > not the case with dbms_space_admin) and you don't have to have any space for > reorg in case your cleared index tablespace can temporarily accommodate your > data. > > > IIRC one of the drawbacks of using dbms_space_admin to convert is > > that you won't be converting to nice uniform extent sizes for existing > data. > > Yes, and if your tablespace is fragmented, the fragmentation will remain > there, despite your conversions (of course, smaller extents might be able to > use some of this fragmented space later on). > > Tanel. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.co
RE: Convert to Locally-Managed Tablespaces
Hi,Thank you all for your prompt suggestions. I was leaning towards EXP/IMP due to its small size. I will test out some of your suggestions in our test environment nevertheless. - Paula W. Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
RE: Convert to Locally-Managed Tablespaces
An additional consideration: If you convert the tablespaces in place with dbms_space_admin, check the default storage for initial and next extent. When you do the conversion with dbms_space_admin, a space header is created and the extent allocation for the space header will be whatever the default extent allocation is. You might also have to change how you monitor the free space in your locally managed tablespaces. For example, we used to monitor for the ability to add additional extents for whatever the largest next extent size for all the tables in the tablespace. Monitoring would e-mail for inability to accommodate two additional extents and send out a page for inability to accommodate one additional extent. We had to change this some with local management using uniform extents. With uniform extents, it is likely that you will be using smaller extents. So if you don't trust the users enough to allow auto-extend, then your space monitoring must be enhanced to take into consideration smaller uniform extents. The dbms_space_admin conversion takes only a few seconds even on 50 Gig tablespaces. And if you don't like what you see, you can convert the tablespace back to dictionary managed; then back to local; then back to dictionary Oh this is fun! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Two questions for you: 1. The export file is binary format. Can you tell me which editor you use? 2. How to manually parallel? Thanks. --- zhu chao <[EMAIL PROTECTED]> wrote: > Hi, Tanel: > If you have plenty of downtime, everything will > be ok. > If you want to further limit the downtime, here > is some suggestions: > 1. I think manual parallel will always be better > than oracle parallel. So I always use script, that > means, I split the move table nologging script to 10 > scripts and let them run concurrently. I call it > manual parallel:).So does the index rebuild. > > 2. To avoid that huge table move again, we can > consider using transportable_tablespace feature. I > did test on my linux 920 box, but not in production. > It did work. Steps like: > set tablespace read only; > check self constrainted. > exp the metadata. > edit the metadata dump, replace tablespace with > new_tablespacename; > drop the tablespace. > imp back the metadata back. > (The above is from oracle metalink ,but I forget > about the noteid). > > regards > Zhu Chao. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > Sent: Tuesday, January 06, 2004 8:49 AM > > > > Hi! > > > > > This is what I will need to use on our systems, > as there are about 400 gig > > > of data and indexes. 200 gig of data is too > large to export/import, at > > least > > > it is for this project. So dbms_space_admin it > will be. > > > > I'm about to do a reorg+conversion of a 250GB > 8.1.6 database in next week, > > here's what I'll do (there is practically no free > space for temporary > > usage): > > > > 1) Export index definitions (normal export with > rows=n) > > 2) Drop all indexes > > 3) use alter table move with parallel 16 and > nologging to move all tables to > > old index tablespaces (the indexes consumed more > space than tables) > > 4) drop and recreate data tablespaces > > 5) use alter table move again to move tables back > (the segments have to > > reside in original tablespaces, otherwise I could > have skipped this step) > > 6) drop and recreate index tablespaces > > 7) get index definitions out of exportfile and > modify them to add parallel & > > nologging (with big sort area size) > > 8) rebuild indexes > > 9) do a full backup > > > > It might help to recreate index tablespaces even > before step 3, to speed up > > parallel table moving a bit.. > > > > Maybe you want to test this Jared, this approach > is much faster than > > export/import, because everything can be done with > direct path operations > > and nologging (import doesn't have direct path > facility, so regular array > > inserts are used, which always require logging as > well). > > Also, your tables/datablocks will be optimized > after moving them (which is > > not the case with dbms_space_admin) and you don't > have to have any space for > > reorg in case your cleared index tablespace can > temporarily accommodate your > > data. > > > > > IIRC one of the drawbacks of using > dbms_space_admin to convert is > > > that you won't be converting to nice uniform > extent sizes for existing > > data. > > > > Yes, and if your tablespace is fragmented, the > fragmentation will remain > > there, despite your conversions (of course, > smaller extents might be able to > > use some of this fragmented space later on). > > > > Tanel. > > > > > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Tanel Poder > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > San Diego, California-- Mailing list and > web hosting services > > > - > > To REMOVE yourself from this mailing list, send an > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > ORACLE-L > > (or the name of mailing list you want to be > removed from). You may > > also send the HELP command for other information > (like subscribing). > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: zhu chao > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo
Re: Convert to Locally-Managed Tablespaces
The answer is in my book. Assume your original setting for pctfree is correct, by the time rows are full length, they are filling the block. If any rows are still part-grown, there will be the right amount of space left in the block for them to grow. If all the rows are new, there will be plenty of space to grow. Inherently there is a 'right number' of rows that will fit a block when full grown. Set the hakan factor to this, and when you move the table, each block will get exactly the right number of rows, with exactly the right amount of space left for any rows that need to grow to full size. (However, due to a bug in the Hakan-related code, you have to fiddle the Hakan factor by one for some functionality, as it is stored as N-1, rather than N - and some code uses N, some uses N-1 when working out how many rows go into a block. Details are in the book). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 06, 2004 10:54 AM Good point about pctfree, but how could minimize records_per_block help in this case? I´ve used it for optimizing bitmap indexes, but for table moving? Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Hi > I am curious - why are you forced to keep > segments in specifically named tablespaces. This Concorde XAL application has an internal repository which stores information about physical segment structures (as tablespace name, initial & next extent etc). And it can start reorganizing segments without any warning if it finds some inconsistencies between data dictionary and its own information. So, we decided it´d be easier to move data back to original tablespaces instead of starting re-configuring the app (we have whole weekend to do the job). > I think the ideal is to MINIMIZE records_per_block on > such tables, then set pctfree to zero, then MOVE them, > then set pctfree back to what it was. But if you do have > any such tables, you might want to experiment. Good point about pctfree, but how could minimize records_per_block help in this case? I´ve used it for optimizing bitmap indexes, but for table moving? Tanel.
Re: Convert to Locally-Managed Tablespaces
Tanel I am curious - why are you forced to keep segments in specifically named tablespaces. I did come across one system that would actually reject (application) upgrades if the database didn't have exactly the right two tablespaces (E_DATA and E_INDX or some such) but I can't think of any other reason. A couple of add-on points for Paula: There is a drawback with using parallel moves - at parallel 16, you end up with 16 extents with an average of 50% space wastage - This may not be significant, especially on a large table with a reasonable number of extents that will be subject to lots of subsequent inserts - but it could make a big difference in some cases. (Side effects include changes in execution path) Regardless of whether you use export or move, you may also have to consider a few special cases of tables where the typical row starts small, and grows over the course of time. You do get systems where a graph of row-lengths shows things like: 80%140 bytes 10%100 bytes 10% 60 bytes If this is the case, then moving the table can result in a problems with lots of wasted space (if you set pctfree for the new rows that need to grow from 60 bytes to 140 bytes) or lots of chained rows (if you set pctfree to suit the 80% of the data that is never going to grow again). I think the ideal is to MINIMIZE records_per_block on such tables, then set pctfree to zero, then MOVE them, then set pctfree back to what it was. But if you do have any such tables, you might want to experiment. One last thought - does the entire operation have to be done in one shot anyway ? You could create a few spare empty tablespaces, and move a couple of tables and indexes at a time if you wanted to avoid high visibility periods of non-availability, and didn't want to come in at the week-end. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 06, 2004 12:49 AM > Hi! > > > This is what I will need to use on our systems, as there are about 400 gig > > of data and indexes. 200 gig of data is too large to export/import, at > least > > it is for this project. So dbms_space_admin it will be. > > I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, > here's what I'll do (there is practically no free space for temporary > usage): > > 1) Export index definitions (normal export with rows=n) > 2) Drop all indexes > 3) use alter table move with parallel 16 and nologging to move all tables to > old index tablespaces (the indexes consumed more space than tables) > 4) drop and recreate data tablespaces > 5) use alter table move again to move tables back (the segments have to > reside in original tablespaces, otherwise I could have skipped this step) > 6) drop and recreate index tablespaces > 7) get index definitions out of exportfile and modify them to add parallel & > nologging (with big sort area size) > 8) rebuild indexes > 9) do a full backup > > It might help to recreate index tablespaces even before step 3, to speed up > parallel table moving a bit.. > > Maybe you want to test this Jared, this approach is much faster than > export/import, because everything can be done with direct path operations > and nologging (import doesn't have direct path facility, so regular array > inserts are used, which always require logging as well). > Also, your tables/datablocks will be optimized after moving them (which is > not the case with dbms_space_admin) and you don't have to have any space for > reorg in case your cleared index tablespace can temporarily accommodate your > data. > > > IIRC one of the drawbacks of using dbms_space_admin to convert is > > that you won't be converting to nice uniform extent sizes for existing > data. > > Yes, and if your tablespace is fragmented, the fragmentation will remain > there, despite your conversions (of course, smaller extents might be able to > use some of this fragmented space later on). > > Tanel. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed fro
Re: Convert to Locally-Managed Tablespaces
Hi, Tanel: If you have plenty of downtime, everything will be ok. If you want to further limit the downtime, here is some suggestions: 1. I think manual parallel will always be better than oracle parallel. So I always use script, that means, I split the move table nologging script to 10 scripts and let them run concurrently. I call it manual parallel:).So does the index rebuild. 2. To avoid that huge table move again, we can consider using transportable_tablespace feature. I did test on my linux 920 box, but not in production. It did work. Steps like: set tablespace read only; check self constrainted. exp the metadata. edit the metadata dump, replace tablespace with new_tablespacename; drop the tablespace. imp back the metadata back. (The above is from oracle metalink ,but I forget about the noteid). regards Zhu Chao. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 06, 2004 8:49 AM > Hi! > > > This is what I will need to use on our systems, as there are about 400 gig > > of data and indexes. 200 gig of data is too large to export/import, at > least > > it is for this project. So dbms_space_admin it will be. > > I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, > here's what I'll do (there is practically no free space for temporary > usage): > > 1) Export index definitions (normal export with rows=n) > 2) Drop all indexes > 3) use alter table move with parallel 16 and nologging to move all tables to > old index tablespaces (the indexes consumed more space than tables) > 4) drop and recreate data tablespaces > 5) use alter table move again to move tables back (the segments have to > reside in original tablespaces, otherwise I could have skipped this step) > 6) drop and recreate index tablespaces > 7) get index definitions out of exportfile and modify them to add parallel & > nologging (with big sort area size) > 8) rebuild indexes > 9) do a full backup > > It might help to recreate index tablespaces even before step 3, to speed up > parallel table moving a bit.. > > Maybe you want to test this Jared, this approach is much faster than > export/import, because everything can be done with direct path operations > and nologging (import doesn't have direct path facility, so regular array > inserts are used, which always require logging as well). > Also, your tables/datablocks will be optimized after moving them (which is > not the case with dbms_space_admin) and you don't have to have any space for > reorg in case your cleared index tablespace can temporarily accommodate your > data. > > > IIRC one of the drawbacks of using dbms_space_admin to convert is > > that you won't be converting to nice uniform extent sizes for existing > data. > > Yes, and if your tablespace is fragmented, the fragmentation will remain > there, despite your conversions (of course, smaller extents might be able to > use some of this fragmented space later on). > > Tanel. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Convert to Locally-Managed Tablespaces
I can see you everywhere :D >Hi, >Think what benefit can you get via such reorg before doing this reorg. >Since only a small database, I won't change it as performance benefit via reorg > will be small. >As others said, exp/imp is the most easy way, as movetable/rebuild index has > trouble sometimes when you have long column/iot table with overflow segment etc. > > >Regards. >Zhu Chao. > >- Original Message - >To: Multiple recipients of list ORACLE-L >Sent: Tuesday, January 06, 2004 6:39 AM > > >Hi all, >I'm not sure if this question has been posted or not. I inheritated an Oracle9i >(9.2.0.4) database which contains all dictionary-managed tablespaces. This small >database is approx. 1 GB and resides on a HP server. I plan to convert all the >dictionary-managed tablespaces to Locally Managed tablespaces. What is the best >approach to accomplish this? >Thank you in advance for your help! >- Paula W. > > > >Do you Yahoo!? >New Yahoo! Photos - easier uploading and sharing >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: zhu chao > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > Best regards, Fenng [EMAIL PROTECTED] 2004-01-06 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fenng INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Tanel, That's a good idea. I briefly considered this, but didn't really dig into it. The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production. I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time. The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes. I see that you too need to keep the original tablespace names, is this SAP per chance? If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes? Jared On Mon, 2004-01-05 at 16:49, Tanel Poder wrote: > Hi! > > > This is what I will need to use on our systems, as there are about 400 gig > > of data and indexes. 200 gig of data is too large to export/import, at > least > > it is for this project. So dbms_space_admin it will be. > > I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, > here's what I'll do (there is practically no free space for temporary > usage): > > 1) Export index definitions (normal export with rows=n) > 2) Drop all indexes > 3) use alter table move with parallel 16 and nologging to move all tables to > old index tablespaces (the indexes consumed more space than tables) > 4) drop and recreate data tablespaces > 5) use alter table move again to move tables back (the segments have to > reside in original tablespaces, otherwise I could have skipped this step) > 6) drop and recreate index tablespaces > 7) get index definitions out of exportfile and modify them to add parallel & > nologging (with big sort area size) > 8) rebuild indexes > 9) do a full backup > > It might help to recreate index tablespaces even before step 3, to speed up > parallel table moving a bit.. > > Maybe you want to test this Jared, this approach is much faster than > export/import, because everything can be done with direct path operations > and nologging (import doesn't have direct path facility, so regular array > inserts are used, which always require logging as well). > Also, your tables/datablocks will be optimized after moving them (which is > not the case with dbms_space_admin) and you don't have to have any space for > reorg in case your cleared index tablespace can temporarily accommodate your > data. > > > IIRC one of the drawbacks of using dbms_space_admin to convert is > > that you won't be converting to nice uniform extent sizes for existing > data. > > Yes, and if your tablespace is fragmented, the fragmentation will remain > there, despite your conversions (of course, smaller extents might be able to > use some of this fragmented space later on). > > Tanel. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Hi, Think what benefit can you get via such reorg before doing this reorg. Since only a small database, I won't change it as performance benefit via reorg will be small. As others said, exp/imp is the most easy way, as movetable/rebuild index has trouble sometimes when you have long column/iot table with overflow segment etc. Regards. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 06, 2004 6:39 AM Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This small database is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
A small addition, PK/UQ constraints have to be disabled in order to drop their indexes (and index definitions should be exported before disabling constraints, because implicitly created indexes will be automatically dropped if a constraint is disabled (without keep indexes option)). Tanel. > I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, > here's what I'll do (there is practically no free space for temporary > usage): > > 1) Export index definitions (normal export with rows=n) > 2) Drop all indexes > 3) use alter table move with parallel 16 and nologging to move all tables to > old index tablespaces (the indexes consumed more space than tables) > 4) drop and recreate data tablespaces > 5) use alter table move again to move tables back (the segments have to > reside in original tablespaces, otherwise I could have skipped this step) > 6) drop and recreate index tablespaces > 7) get index definitions out of exportfile and modify them to add parallel & > nologging (with big sort area size) > 8) rebuild indexes > 9) do a full backup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
Hi! > This is what I will need to use on our systems, as there are about 400 gig > of data and indexes. 200 gig of data is too large to export/import, at least > it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel & nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. > IIRC one of the drawbacks of using dbms_space_admin to convert is > that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Convert to Locally-Managed Tablespaces
On a gig of data, you could easily export the data and re-import into new locally managed tablespaces. An alternative is to use the dbms_space_admin package to convert DD managed tablespaces to locally managed. This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. The DD data for the existing extents is simply converted to bitmaps. The advantage of getting extent mgt out of the DD should outweigh that IMO. There may be other drawbacks, I haven't started on this project yet. I'm sure someone else on the list can respond with some experiences. For 1 gig of data though, I personally would just go the export/import route. HTH Jared Paula Winkler <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/05/2004 02:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Convert to Locally-Managed Tablespaces Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This small database is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Convert to Locally-Managed Tablespaces
Hi all, I'm not sure if this question has been posted or not. I inheritated an Oracle9i (9.2.0.4) database which contains all dictionary-managed tablespaces. This small database is approx. 1 GB and resides on a HP server. I plan to convert all the dictionary-managed tablespaces to Locally Managed tablespaces. What is the best approach to accomplish this? Thank you in advance for your help! - Paula W. Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Re: Locally managed tablespaces and raw devices
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 8.1.7.4 database running on Solaris 2.8, > 16 k block size, using raw devices for database datafiles. The > raw slices all appear to be a uniform size, 200 meg each. As > part of my initial site survey, I noticed that the tablespaces > are all dictionary managed. > > Would it be optimal to convert the existing application data > and index tablespaces to locally managed tablespaces? > > Does anyone know of any issues/problems when using locally managed > tablespaces with raw devices? > > Another question that I do have concerns the proper sizing of > datafiles for new tablespaces. I have only utilized locally > managed tablespaces on OS filesystems. With OS-level datafiles > I usually create datafiles as a multiple of the OS filesystem > block size + 64K for the bitmap header and metadata blocks. > For example: > > OS block size datafile size > - - >8k256 meg + 64k > > > With raw disk slices, the OS filesystem is bypassed. What then > should be the optimal "formula" when creating new datafiles? > > Thanks, > Lou Avrami > > > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Locally managed tablespaces and raw devices
Hi all, I just inherited an 8.1.7.4 database running on Solaris 2.8, 16 k block size, using raw devices for database datafiles. The raw slices all appear to be a uniform size, 200 meg each. As part of my initial site survey, I noticed that the tablespaces are all dictionary managed. Would it be optimal to convert the existing application data and index tablespaces to locally managed tablespaces? Does anyone know of any issues/problems when using locally managed tablespaces with raw devices? Another question that I do have concerns the proper sizing of datafiles for new tablespaces. I have only utilized locally managed tablespaces on OS filesystems. With OS-level datafiles I usually create datafiles as a multiple of the OS filesystem block size + 64K for the bitmap header and metadata blocks. For example: OS block size datafile size - - 8k256 meg + 64k With raw disk slices, the OS filesystem is bypassed. What then should be the optimal "formula" when creating new datafiles? Thanks, Lou Avrami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Louis Avrami INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Good link on Locally Managed Tablespaces
Thanks for this link, Dave. Regards, Ruth - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 26, 2003 10:19 AM > I want to share this link with the listers. It is a good article on Oracle locally managed tablespaces with some good info for those that are going to migrate to LMT's. > > http://databasejournal.com/features/oracle/article.php/10893_2223631_1 > > Dave > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Farnsworth, Dave > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Good link on Locally Managed Tablespaces
I want to share this link with the listers. It is a good article on Oracle locally managed tablespaces with some good info for those that are going to migrate to LMT's. http://databasejournal.com/features/oracle/article.php/10893_2223631_1 Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Farnsworth, Dave INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Locally Managed Tablespaces
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 longer and meanwhile my available space window kept shrinking. I wrote scripts to help, but there are always decisions involved. Since switching to autoextend, we've experienced only a couple of incidents where something ran the disk out of space, and these turned out to be easier to deal with than the out of tablespace calls from the users. We use big RAID sets, so it is a matter of checking a couple of RAID sets vs. checking hundreds of tablespaces. There is also the advantage of less wasted space. If you leave enough free space in each tablespace to accommodate the largest next extent, that adds up. With LMT and autoextend, there is zero free space on most of these tablespaces. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Saturday, March 22, 2003 5:49 PM To: Multiple recipients of list ORACLE-L 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 takes awhile. If you start each file out as 500m with a next size of 500m and a max of 4g, you can defer the time spent creating the files to load time, rather than waiting around for 100g of files to be created before you start loading. Still takes the same amount of time, but you get to go home earlier. :) Jared On Friday 21 March 2003 18:23, Jacques Kilchoer wrote: > 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 a report that shows the number of extents and > the amount of freespace in each tablespace, and review the report > periodically (say once a week). Which is what I did back in my production > DBA days. I imagine you could have a database procedure that checks the > free space in a tablespace and sends you an e-mail, or even pages you if > you have e-mail forwarded to a pager. > Setting the datafiles to autoextend just pushes the problem back to the OS > level - how do you know when your disks will be full? > > > -Original Message- > > From: Ryan [mailto:[EMAIL PROTECTED] > > > > so for normal business you should not use autoextend? You > > should monitor it > > yourself? What are some tips for monitoring the database to > > see if you need > > to extend your tablespace manually? Do you use DBMS_ALERT and > > read the v$ > > views and then broadcast a message if you need to extend a tablespace? Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Locally Managed Tablespaces
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 takes awhile. If you start each file out as 500m with a next size of 500m and a max of 4g, you can defer the time spent creating the files to load time, rather than waiting around for 100g of files to be created before you start loading. Still takes the same amount of time, but you get to go home earlier. :) Jared On Friday 21 March 2003 18:23, Jacques Kilchoer wrote: > 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 a report that shows the number of extents and > the amount of freespace in each tablespace, and review the report > periodically (say once a week). Which is what I did back in my production > DBA days. I imagine you could have a database procedure that checks the > free space in a tablespace and sends you an e-mail, or even pages you if > you have e-mail forwarded to a pager. > Setting the datafiles to autoextend just pushes the problem back to the OS > level - how do you know when your disks will be full? > > > -Original Message- > > From: Ryan [mailto:[EMAIL PROTECTED] > > > > so for normal business you should not use autoextend? You > > should monitor it > > yourself? What are some tips for monitoring the database to > > see if you need > > to extend your tablespace manually? Do you use DBMS_ALERT and > > read the v$ > > views and then broadcast a message if you need to extend a tablespace? Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally Managed Tablespaces
thanks for all the responses to such a basic question. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, March 22, 2003 6:03 AM > The other pctincrease option that preserves a multiple of the extent > size is 100. > Similiar to LMT autoallocate extent sizes always being a multiple of 64K. > > Have Fun :) > > Rachel Carmichael wrote: > > >pctincrease=0 and set the storage parameters at the tablespace level > >and do NOT put storage parameters on the individual objects. > > > >you can "fake" the workings (without the bitmap!) of an LMT by doing > >that. Next extent=initial extent, pctincrease=0 will effectively > >allocate extents of equal sizes > > > > > >--- [EMAIL PROTECTED] wrote: > > > > > >>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. > >> > >>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, 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 that Im a > >>> > >>> > >>developer > >> > >> > >>>and Im trying to pick up more of the DBA side. > >>> > >>>I am assuming that the preferred way to create a tablespace in 8i, > >>> > >>> > >>9i is as > >> > >> > >>>follows(this is out of OTN docs) > >>> > >>>CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' > >>> > >>> > >>SIZE 50M > >> > >> > >>>EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; > >>> > >>> > >>>When you use Uniform Extents you know longer have to worry about > >>> > >>> > >>tablespace > >> > >> > >>>fragmentation correct? You also do not need to worry > >>> > >>> > >>minimum,initial,and > >> > >> > >>>next extents correct? > >>> > >>>What are some rules of thumb for setting PCTINCREASE(there is > >>> > >>> > >>another PCT > >> > >> > >>>setting too right)? A DBA I used to work with said you should > >>> > >>> > >>almost always > >> > >> > >>>use zero for PCTINCREASE. Could someone please tell me why? The > >>> > >>> > >>default is > >> > >> > >>>like 40? > >>> > >>>Is there a time when I should not use Uniform Extents? > >>> > >>>Thanks... hope this isnt too basic. > >>> > >>>-- > >>>Please see the official ORACLE-L FAQ: http://www.orafaq.net > >>>-- > >>>Author: <[EMAIL PROTECTED] > >>> INET: [EMAIL PROTECTED] > >>> > >>>Fat City Network Services-- 858-538-5051 http://www.fatcity.com > >>>San Diego, California-- Mailing list and web hosting > >>> > >>> > >>services > >> > >> > >>- > >> > >> > >>>To REMOVE yourself from this mailing list, send an E-Mail message > >>>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > >>>the message BODY, include a line containing: UNSUB ORACLE-L > >>>(or the name of mailing list you want to be removed from). You may > >>>also send the HELP command for other information (like > >>> > >>> > >>subscribing). > >> > >> > >>>
Re: Locally Managed Tablespaces
The other pctincrease option that preserves a multiple of the extent size is 100. Similiar to LMT autoallocate extent sizes always being a multiple of 64K. Have Fun :) Rachel Carmichael wrote: pctincrease=0 and set the storage parameters at the tablespace level and do NOT put storage parameters on the individual objects. you can "fake" the workings (without the bitmap!) of an LMT by doing that. Next extent=initial extent, pctincrease=0 will effectively allocate extents of equal sizes --- [EMAIL PROTECTED] wrote: 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. 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, 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 that Im a developer and Im trying to pick up more of the DBA side. I am assuming that the preferred way to create a tablespace in 8i, 9i is as follows(this is out of OTN docs) CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; When you use Uniform Extents you know longer have to worry about tablespace fragmentation correct? You also do not need to worry minimum,initial,and next extents correct? What are some rules of thumb for setting PCTINCREASE(there is another PCT setting too right)? A DBA I used to work with said you should almost always use zero for PCTINCREASE. Could someone please tell me why? The default is like 40? Is there a time when I should not use Uniform Extents? Thanks... hope this isnt too basic. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: BALA,PRAKASH (HP-USA,ex1) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chip INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Locally Managed Tablespaces
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 a report that shows the number of extents and the amount of freespace in each tablespace, and review the report periodically (say once a week). Which is what I did back in my production DBA days. I imagine you could have a database procedure that checks the free space in a tablespace and sends you an e-mail, or even pages you if you have e-mail forwarded to a pager. Setting the datafiles to autoextend just pushes the problem back to the OS level - how do you know when your disks will be full? > -Original Message- > From: Ryan [mailto:[EMAIL PROTECTED]] > > so for normal business you should not use autoextend? You > should monitor it > yourself? What are some tips for monitoring the database to > see if you need > to extend your tablespace manually? Do you use DBMS_ALERT and > read the v$ > views and then broadcast a message if you need to extend a tablespace?
Re: RE: Locally Managed Tablespaces
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 > extent size whatever a rogue user does. > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > For one-day tutorials: > (see http://www.jlcomp.demon.co.uk/tutorial.html ) > > UK___April 8th > UK___April 22nd > Denmark May 21-23rd > USA_(FL)_May 2nd > > Next dates for the 3-day seminar: > (see http://www.jlcomp.demon.co.uk/seminar.html ) > UK_(Manchester)_May > Estonia___June (provisional) > USA_(CA, TX)_August > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: 21 March 2003 19:20 > > > > pctincrease=0 and set the storage parameters at the tablespace > level > > and do NOT put storage parameters on the individual objects. > > > > you can "fake" the workings (without the bitmap!) of an LMT by > doing > > that. Next extent=initial extent, pctincrease=0 will effectively > > allocate extents of equal sizes > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Locally Managed Tablespaces
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 extent has to be at worst a multiple of the minimum > extent size whatever a rogue user does.
Re: RE: Locally Managed Tablespaces
so for normal business you should not use autoextend? You should monitor it yourself? What are some tips for monitoring the database to see if you need to extend your tablespace manually? Do you use DBMS_ALERT and read the v$ views and then broadcast a message if you need to extend a tablespace? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, March 21, 2003 6:38 PM > FWIW I've come to think of autoextend as a valuable ally in certain cases. > > When loading data it's nice to enable autoextend when you don't know > how large you really need to have the database files. Create several > and set autoextend on, being sure that if all were to fill up, it won't > fill up > the drive, as that can cause some sticky problems. > > When upgrading a database, I may set autoextend on on the SYSTEM > datafiles so they don't run out. > > Along the same lines, I set maxextents to unlimited and monitor the number > of extents so that it is not unreasonable. Better to have a couple > thousand > extents during a data load that someone 'forgot' to inform you about than > to have the job die in the middle of the night. > > I think my 'reasonable' # of extents 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 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 is that uniform extents are a good thing. > So in 7.3, try and "manually" enforce uniform extents: > initial = next for all clusters/tables/indexes in the same tablespace > pctincrease=0 for all objects > These should of course be the settings for the DEFAULT STORAGE clause on > the create tablespace. Then to create a cluster/table/index you can > (should?) use tablespace defaults and skip the storage parameter on the > CREATE cluster/table/index statement. > My personal opinion: always use maxextents unlimited but put your object > in a tablespace where the values of INITIAL and NEXT will prevent the > object from having more than 1000 extents. > Autoextend datafiles: my personal opinion is don't use those, because you > should have an idea of how and when your database is going to grow, and if > you need more space it's nice to be aware of it. Of course this means the > risk of failure when a datafile is full, but the same thing can happen > with autoextend when the disk gets full. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Locally Managed Tablespaces
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 hand a poorly written job that runs amuck can be stopped before creating too much damage if you set limits on your datafile sizes. But yes, autoextend definitely has its uses. As far as my suggested maximum of 1000 extents: some people give me grief about that. I have worked mostly in development environments and so I might be out of touch. But 1000 extents should be plenty for anybody. When is the last time you had to drop a table with 1 extents, and have the developer looking over your shoulder tell you "In Access when you drop a table it's instantaneous?"
Re: RE: Locally Managed Tablespaces
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 http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May Estonia___June (provisional) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 21 March 2003 19:20 > pctincrease=0 and set the storage parameters at the tablespace level > and do NOT put storage parameters on the individual objects. > > you can "fake" the workings (without the bitmap!) of an LMT by doing > that. Next extent=initial extent, pctincrease=0 will effectively > allocate extents of equal sizes -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Locally Managed Tablespaces
FWIW I've come to think of autoextend as a valuable ally in certain cases.
When loading data it's nice to enable autoextend when you don't know
how large you really need to have the database files. Create several
and set autoextend on, being sure that if all were to fill up, it won't
fill up
the drive, as that can cause some sticky problems.
When upgrading a database, I may set autoextend on on the SYSTEM
datafiles so they don't run out.
Along the same lines, I set maxextents to unlimited and monitor the number
of extents so that it is not unreasonable. Better to have a couple
thousand
extents during a data load that someone 'forgot' to inform you about than
to have the job die in the middle of the night.
I think my 'reasonable' # of extents 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 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 is that uniform extents are a good thing.
So in 7.3, try and "manually" enforce uniform extents:
initial = next for all clusters/tables/indexes in the same tablespace
pctincrease=0 for all objects
These should of course be the settings for the DEFAULT STORAGE clause on
the create tablespace. Then to create a cluster/table/index you can
(should?) use tablespace defaults and skip the storage parameter on the
CREATE cluster/table/index statement.
My personal opinion: always use maxextents unlimited but put your object
in a tablespace where the values of INITIAL and NEXT will prevent the
object from having more than 1000 extents.
Autoextend datafiles: my personal opinion is don't use those, because you
should have an idea of how and when your database is going to grow, and if
you need more space it's nice to be aware of it. Of course this means the
risk of failure when a datafile is full, but the same thing can happen
with autoextend when the disk gets full.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: RE: Locally Managed Tablespaces
pctincrease=0 and set the storage parameters at the tablespace level and do NOT put storage parameters on the individual objects. you can "fake" the workings (without the bitmap!) of an LMT by doing that. Next extent=initial extent, pctincrease=0 will effectively allocate extents of equal sizes --- [EMAIL PROTECTED] wrote: > 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. > > 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, 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 that Im a > developer > > and Im trying to pick up more of the DBA side. > > > > I am assuming that the preferred way to create a tablespace in 8i, > 9i is as > > follows(this is out of OTN docs) > > > > CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' > SIZE 50M > > EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; > > > > > > When you use Uniform Extents you know longer have to worry about > tablespace > > fragmentation correct? You also do not need to worry > minimum,initial,and > > next extents correct? > > > > What are some rules of thumb for setting PCTINCREASE(there is > another PCT > > setting too right)? A DBA I used to work with said you should > almost always > > use zero for PCTINCREASE. Could someone please tell me why? The > default is > > like 40? > > > > Is there a time when I should not use Uniform Extents? > > > > Thanks... hope this isnt too basic. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: <[EMAIL PROTECTED] > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: BALA,PRAKASH (HP-USA,ex1) > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Locally Managed Tablespaces
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 is that uniform extents are a good thing. So in 7.3, try and "manually" enforce uniform extents: initial = next for all clusters/tables/indexes in the same tablespace pctincrease=0 for all objects These should of course be the settings for the DEFAULT STORAGE clause on the create tablespace. Then to create a cluster/table/index you can (should?) use tablespace defaults and skip the storage parameter on the CREATE cluster/table/index statement. My personal opinion: always use maxextents unlimited but put your object in a tablespace where the values of INITIAL and NEXT will prevent the object from having more than 1000 extents. Autoextend datafiles: my personal opinion is don't use those, because you should have an idea of how and when your database is going to grow, and if you need more space it's nice to be aware of it. Of course this means the risk of failure when a datafile is full, but the same thing can happen with autoextend when the disk gets full.
Re: RE: Locally Managed Tablespaces
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. 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, 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 that Im a developer > and Im trying to pick up more of the DBA side. > > I am assuming that the preferred way to create a tablespace in 8i, 9i is as > follows(this is out of OTN docs) > > CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M > EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; > > > When you use Uniform Extents you know longer have to worry about tablespace > fragmentation correct? You also do not need to worry minimum,initial,and > next extents correct? > > What are some rules of thumb for setting PCTINCREASE(there is another PCT > setting too right)? A DBA I used to work with said you should almost always > use zero for PCTINCREASE. Could someone please tell me why? The default is > like 40? > > Is there a time when I should not use Uniform Extents? > > Thanks... hope this isnt too basic. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: BALA,PRAKASH (HP-USA,ex1) > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
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 This is probably pretty basic, so please keep in mind that Im a developer and Im trying to pick up more of the DBA side. I am assuming that the preferred way to create a tablespace in 8i, 9i is as follows(this is out of OTN docs) CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; When you use Uniform Extents you know longer have to worry about tablespace fragmentation correct? You also do not need to worry minimum,initial,and next extents correct? What are some rules of thumb for setting PCTINCREASE(there is another PCT setting too right)? A DBA I used to work with said you should almost always use zero for PCTINCREASE. Could someone please tell me why? The default is like 40? Is there a time when I should not use Uniform Extents? Thanks... hope this isnt too basic. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: BALA,PRAKASH (HP-USA,ex1) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
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 it, it doesn't make much sense anyway with uniform extents. You just want it to take an extent when it needs one. There is no value for taking multiple extents at a time. I haven't found a time not to use uniform extents. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, March 21, 2003 9:59 AM To: Multiple recipients of list ORACLE-L This is probably pretty basic, so please keep in mind that Im a developer and Im trying to pick up more of the DBA side. I am assuming that the preferred way to create a tablespace in 8i, 9i is as follows(this is out of OTN docs) CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; When you use Uniform Extents you know longer have to worry about tablespace fragmentation correct? You also do not need to worry minimum,initial,and next extents correct? What are some rules of thumb for setting PCTINCREASE(there is another PCT setting too right)? A DBA I used to work with said you should almost always use zero for PCTINCREASE. Could someone please tell me why? The default is like 40? Is there a time when I should not use Uniform Extents? Thanks... hope this isnt too basic. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally Managed Tablespaces
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] wrote: > This is probably pretty basic, so please keep in mind that Im a > developer and Im trying to pick up more of the DBA side. > > I am assuming that the preferred way to create a tablespace in 8i, 9i > is as follows(this is out of OTN docs) > > CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' > SIZE 50M > EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; > > > When you use Uniform Extents you know longer have to worry about > tablespace fragmentation correct? You also do not need to worry > minimum,initial,and next extents correct? > > What are some rules of thumb for setting PCTINCREASE(there is another > PCT setting too right)? A DBA I used to work with said you should > almost always use zero for PCTINCREASE. Could someone please tell me > why? The default is like 40? > > Is there a time when I should not use Uniform Extents? > > Thanks... hope this isnt too basic. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Locally Managed Tablespaces
This is probably pretty basic, so please keep in mind that Im a developer and Im trying to pick up more of the DBA side. I am assuming that the preferred way to create a tablespace in 8i, 9i is as follows(this is out of OTN docs) CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; When you use Uniform Extents you know longer have to worry about tablespace fragmentation correct? You also do not need to worry minimum,initial,and next extents correct? What are some rules of thumb for setting PCTINCREASE(there is another PCT setting too right)? A DBA I used to work with said you should almost always use zero for PCTINCREASE. Could someone please tell me why? The default is like 40? Is there a time when I should not use Uniform Extents? Thanks... hope this isnt too basic. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: When were Locally Managed Tablespaces introduced?
8i - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, February 17, 2003 9:53 AM > I'm not having much luck - I thought it was with 9i, but the new features > guide doesn't list it. So now I'm trawling through old "new" features > guides ... and thought some bright people out there might remember (faster > than I can find it in a bunch of pdfs, web pages, etc.). > > Ciao > Fuzzy > :-) > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Grant Allen > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: When were Locally Managed Tablespaces introduced?
It's from 8i. Good article on www.oaktable.net by Connor McDonald. Mogens Grant Allen wrote: I'm not having much luck - I thought it was with 9i, but the new features guide doesn't list it. So now I'm trawling through old "new" features guides ... and thought some bright people out there might remember (faster than I can find it in a bunch of pdfs, web pages, etc.). Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: When were Locally Managed Tablespaces introduced?
It was available in 8.1.7... I just implemented it in our APPS database and it is running 8.1.7.3 April Wells Oracle DBA Great spirits have always encountered violent opposition from mediocre minds -- Albert Einstein -Original Message- Sent: Monday, February 17, 2003 10:54 AM To: Multiple recipients of list ORACLE-L I'm not having much luck - I thought it was with 9i, but the new features guide doesn't list it. So now I'm trawling through old "new" features guides ... and thought some bright people out there might remember (faster than I can find it in a bunch of pdfs, web pages, etc.). Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim all liability and will accept no responsibility for any damage sustained as a result of software viruses and advise you to carry out your own virus checks before opening any attachment. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: April Wells INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: When were Locally Managed Tablespaces introduced?
I think, it was 8.1.5. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, February 17, 2003 11:53 AM > I'm not having much luck - I thought it was with 9i, but the new features > guide doesn't list it. So now I'm trawling through old "new" features > guides ... and thought some bright people out there might remember (faster > than I can find it in a bunch of pdfs, web pages, etc.). > > Ciao > Fuzzy > :-) > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Grant Allen > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: When were Locally Managed Tablespaces introduced?
8i "Grant Allen" ft.co.uk>cc: Sent by: Subject: When were Locally Managed Tablespaces introduced? [EMAIL PROTECTED] om 02/17/2003 11:53 AM Please respond to ORACLE-L I'm not having much luck - I thought it was with 9i, but the new features guide doesn't list it. So now I'm trawling through old "new" features guides ... and thought some bright people out there might remember (faster than I can find it in a bunch of pdfs, web pages, etc.). Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: When were Locally Managed Tablespaces introduced?
Locally managed tablespaces first came out with Oracle 8i, release 8.1 to be specific. -Original Message- Sent: Monday, February 17, 2003 12:54 PM To: Multiple recipients of list ORACLE-L I'm not having much luck - I thought it was with 9i, but the new features guide doesn't list it. So now I'm trawling through old "new" features guides ... and thought some bright people out there might remember (faster than I can find it in a bunch of pdfs, web pages, etc.). Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wayne Straughn INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: When were Locally Managed Tablespaces introduced?
Lmt were introduced in 8i -Original Message- Allen Sent: Monday, February 17, 2003 11:54 AM To: Multiple recipients of list ORACLE-L I'm not having much luck - I thought it was with 9i, but the new features guide doesn't list it. So now I'm trawling through old "new" features guides ... and thought some bright people out there might remember (faster than I can find it in a bunch of pdfs, web pages, etc.). Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ramon E. Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
When were Locally Managed Tablespaces introduced?
I'm not having much luck - I thought it was with 9i, but the new features guide doesn't list it. So now I'm trawling through old "new" features guides ... and thought some bright people out there might remember (faster than I can find it in a bunch of pdfs, web pages, etc.). Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
But she is missing from this list for a while...Hope to see her again
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Fri, 14 Jun 2002 11:51:37 -0800
I concur. Anita's one of the most knowledgeable replication
people to ever grace 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 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 for renaming a user. One or two quick updates
of the sys.user$ table and it is done. Again, your favorite
support engineer will not like that. BTW, my favorite support
engineer is Anita Bardeen from FLA. She's great!
> -Original Message-
> 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 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-- Public Internet access / Mailing
> > Lists
> >
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>
> __
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
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-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
RE: Locally Managed Tablespaces
Yeah Anita is my favorite support engineer too... except that she's not on the "front lines" of support anymore :( But I think she's going to be able to come to NY to present for the user group in December. We're looking into it now :) You are right, knowing the members of this list I should have said "only in 9.2 is it supported for system to be an lmt" --- "Gogala, Mladen" <[EMAIL PROTECTED]> wrote: > 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 for renaming a user. One or two quick updates > of the sys.user$ table and it is done. Again, your favorite > support engineer will not like that. BTW, my favorite support > engineer is Anita Bardeen from FLA. She's great! > > > -Original Message- > > 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 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-- Public Internet access / Mailing > > > Lists > > > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > subscribing). > > > > > > __ > > Do You Yahoo!? > > Yahoo! - Official partner of 2002 FIFA World Cup > > http://fifaworldcup.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Rachel Carmichael > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing > Lists > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Gogala, Mladen > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
I concur. Anita's one of the most knowledgeable replication
people to ever grace 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 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 for renaming a user. One or two quick updates
of the sys.user$ table and it is done. Again, your favorite
support engineer will not like that. BTW, my favorite support
engineer is Anita Bardeen from FLA. She's great!
> -Original Message-
> 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 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-- Public Internet access / Mailing
> > Lists
> >
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>
> __
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
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-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
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 for renaming a user. One or two quick updates of the sys.user$ table and it is done. Again, your favorite support engineer will not like that. BTW, my favorite support engineer is Anita Bardeen from FLA. She's great! > -Original Message- > 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 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-- Public Internet access / Mailing > > Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > __ > Do You Yahoo!? > Yahoo! - Official partner of 2002 FIFA World Cup > http://fifaworldcup.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
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 > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally Managed Tablespaces
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: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
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? > 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-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
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 having an opinion is an art! -Original Message- Sent: Friday, June 14, 2002 2:04 PM To: Multiple recipients of list ORACLE-L 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-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Locally Managed Tablespaces
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-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
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, 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] wrote: > Hi All, > > I am setting up a 9i instance and am wondering if > there are any tablespaces that you would not set up > as locally managed. Would system and rollback > tablespaces be set up as dictionary managed or > locally managed? Are there any guidelines when > considering lmt or dmt? > > Thanks, > Michele Armstrong > -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally Managed Tablespaces
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] wrote: > Hi All, > > I am setting up a 9i instance and am wondering if > there are any tablespaces that you would not set up > as locally managed. Would system and rollback > tablespaces be set up as dictionary managed or > locally managed? Are there any guidelines when > considering lmt or dmt? > > Thanks, > Michele Armstrong > -- > 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-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Some days you're the pigeon, some days you're the statue" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Locally Managed Tablespaces
Hi All, I am setting up a 9i instance and am wondering if there are any tablespaces that you would not set up as locally managed. Would system and rollback tablespaces be set up as dictionary managed or locally managed? Are there any guidelines when considering lmt or dmt? Thanks, Michele Armstrong -- 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-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally managed tablespaces/What a DBA can do ?
When you backup, temporary tablespace does not need to be backed up and it is not a required tablespace for creating/bringing up database. My hot backup does not include temporary tablespace and I was many times able to restore/recovery the database without any problem. -Original Message- Sent: Wednesday, April 17, 2002 4:59 PM To: Multiple recipients of list ORACLE-L The poster implied he was going to change the temporary tablespace to locally managed. > > > > > to some of the other org's, so maybe staying > > > with > > > > > dictionary-managed would > > > > > be > > > > > best, except for the temporary tablespace. > > > > > > > > > > Darren. All of my production databases are still dictionary managed. I played around with locally managed tablespaces a while back on a test server, and when I ran a generic hot backup script, which attempted to dynamically put each tablespace in backup mode, it failed on the temporary tablespace. Of course all other tablespaces were successful (as you pointed out). Hopefully he would be doing this in a test environment first anyway, but I was just pointing out that he may want to test backup/recovery after converting. Rob Pegram Oracle Certified DBA --- "Koivu, Lisa" <[EMAIL PROTECTED]> wrote: > That's not a temp tablespace (TEMP_DATA_A). It's a > place to put temporary > load tables. My temporary tablespace is TEMP, which > is dictionary. From > the research I did on LMT's before I decided to use > them, it looked like > there were several bugs associated with temporary > tablespaces being LMT's so > I left my temporary ts dictionary. > > 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? > > > > > > --- "Koivu, Lisa" <[EMAIL PROTECTED]> > wrote: > > > What? I was just able to do it. 8.1.7, W2K > > > > > > (INV-SYSTEM)>@ts > > > > > > TS_NAME LMT MB_FREE MB_TOTAL > > > PCT_USED > > > --- -- - - > > > - > > > LOAD_DATA_A LOCAL740 2000 > > > > 63 > > > LRG_INDEX_A LOCAL390 2500 > > > > 84.4 > > > LRG_INDEX_B LOCAL980 1000 > > > > 2 > > > LRG_TABLE_A LOCAL 2440 4000 > > > > 39 > > > LRG_TABLE_B LOCAL360 1000 > > > > 64 > > > MED_INDEX_A LOCAL480 1000 > > > > 52 > > > MED_TABLE_A LOCAL680 1000 > > > > 32 > > > RBS DICTIONARY 299 1000 > > > > 70.1 > > > SMALL_INDEX_A LOCAL183 200 > > > > 8.5 > > > SMALL_TABLE_A LOCAL169 200 > > > > 15.5 > > > SYSTEM DICTIONARY 110 200 > > > > 45 > > > TEMPDICTIONARY 961 1000 > > > > 3.9 > > > TEMP_DATA_A LOCAL900 1000 > > > > 10 > > > TOOLS LOCAL 1920 > > > > 5 > > > USERS LOCAL 3475 > > > > 54.7 > > > > > > 15 rows selected. > > > > > > (INV-SYSTEM)>alter tablespace load_data_a begin > > > backup; > > > > > > Tablespace altered. > > > > > > (INV-SYSTEM)>alter tablespace load_data_a end > > > backup; > > > > > > 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, > > > > > > > > If using a hot backup strategy (other than > Rman), > > > > make sure to test your backup/recovery if you > > > switch > > > > the temp
RE: Locally managed tablespaces/What a DBA can do ?
The poster implied he was going to change the temporary tablespace to locally managed. > > > > > to some of the other org's, so maybe staying > > > with > > > > > dictionary-managed would > > > > > be > > > > > best, except for the temporary tablespace. > > > > > > > > > > Darren. All of my production databases are still dictionary managed. I played around with locally managed tablespaces a while back on a test server, and when I ran a generic hot backup script, which attempted to dynamically put each tablespace in backup mode, it failed on the temporary tablespace. Of course all other tablespaces were successful (as you pointed out). Hopefully he would be doing this in a test environment first anyway, but I was just pointing out that he may want to test backup/recovery after converting. Rob Pegram Oracle Certified DBA --- "Koivu, Lisa" <[EMAIL PROTECTED]> wrote: > That's not a temp tablespace (TEMP_DATA_A). It's a > place to put temporary > load tables. My temporary tablespace is TEMP, which > is dictionary. From > the research I did on LMT's before I decided to use > them, it looked like > there were several bugs associated with temporary > tablespaces being LMT's so > I left my temporary ts dictionary. > > 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? > > > > > > --- "Koivu, Lisa" <[EMAIL PROTECTED]> > wrote: > > > What? I was just able to do it. 8.1.7, W2K > > > > > > (INV-SYSTEM)>@ts > > > > > > TS_NAME LMT MB_FREE MB_TOTAL > > > PCT_USED > > > --- -- - - > > > - > > > LOAD_DATA_A LOCAL740 2000 > > > > 63 > > > LRG_INDEX_A LOCAL390 2500 > > > > 84.4 > > > LRG_INDEX_B LOCAL980 1000 > > > > 2 > > > LRG_TABLE_A LOCAL 2440 4000 > > > > 39 > > > LRG_TABLE_B LOCAL360 1000 > > > > 64 > > > MED_INDEX_A LOCAL480 1000 > > > > 52 > > > MED_TABLE_A LOCAL680 1000 > > > > 32 > > > RBS DICTIONARY 299 1000 > > > > 70.1 > > > SMALL_INDEX_A LOCAL183 200 > > > > 8.5 > > > SMALL_TABLE_A LOCAL169 200 > > > > 15.5 > > > SYSTEM DICTIONARY 110 200 > > > > 45 > > > TEMPDICTIONARY 961 1000 > > > > 3.9 > > > TEMP_DATA_A LOCAL900 1000 > > > > 10 > > > TOOLS LOCAL 1920 > > > > 5 > > > USERS LOCAL 3475 > > > > 54.7 > > > > > > 15 rows selected. > > > > > > (INV-SYSTEM)>alter tablespace load_data_a begin > > > backup; > > > > > > Tablespace altered. > > > > > > (INV-SYSTEM)>alter tablespace load_data_a end > > > backup; > > > > > > 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, > > > > > > > > If using a hot backup strategy (other than > Rman), > > > > make sure to test your backup/recovery if you > > > switch > > > > the temporary tablespace to locally managed. > You > > > > can't put a locally managed temporary > tablespace > > > in > > > > backup mode. > > > > > > > > Rob Pegram > > > > Oracle Certified DBA > > > > > > > > > > > > > > > > SQL> create
RE: Locally managed tablespaces/What a DBA can do ?
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 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 the machine isn't the quickest in the world (and I have 3 databases on that machine). I plan on upgrading another HP-UX 11.0 64-bit machine to 8.1.7.3.0 by the end of the month. I'm a little nervous about 8.1.7.3.0 on Solaris 7, so I'm going to do some reading before taking the databases to either 8.1.7.2.0 or 8.1.7.3.0. HTH, Julie Julie Fisher Sandia National Laboratories Oracle 8i DBA - OCP8i Solaris 2.6,7/HP-UX 11.0 System Administrator Web Server Administrator -Original Message- Sent: Wednesday, April 17, 2002 1:04 PM To: Multiple recipients of list ORACLE-L Lisa, Thanks. I saw couple of messages today about problems with 8.1.7.3 on Solaris. We are also thinking to move to 8.1.7.3 on HP-UX 11 in near future but now we have to wait and see for a while. Regards Rafiq -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fisher, Julie INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally managed tablespaces/What a DBA can do ?
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 change blocksize from 4k to 16k , we created 8.1.7.2 database with locally managed tablespaces and imported our 8.1.6.2 database there. So far so good in testing. These new box (3 of them)will go in production in 3/4 weeks time and then we will upgrade to 8.1.7.3(except Oracle Financials)once we feel comfortable. We are HP shop. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 17 Apr 2002 11:59:26 -0800 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 the machine isn't the quickest in the world (and I have 3 databases on that machine). I plan on upgrading another HP-UX 11.0 64-bit machine to 8.1.7.3.0 by the end of the month. I'm a little nervous about 8.1.7.3.0 on Solaris 7, so I'm going to do some reading before taking the databases to either 8.1.7.2.0 or 8.1.7.3.0. HTH, Julie Julie Fisher Sandia National Laboratories Oracle 8i DBA - OCP8i Solaris 2.6,7/HP-UX 11.0 System Administrator Web Server Administrator -Original Message- Sent: Wednesday, April 17, 2002 1:04 PM To: Multiple recipients of list ORACLE-L Lisa, Thanks. I saw couple of messages today about problems with 8.1.7.3 on Solaris. We are also thinking to move to 8.1.7.3 on HP-UX 11 in near future but now we have to wait and see for a while. Regards Rafiq -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fisher, Julie INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MOHAMMAD RAFIQ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally managed tablespaces/What a DBA can do ?
My TEMP tablespace is a locally managed temporary tablespace using a tempfile (not datafile). See results below; SQL> alter tablespace TEMP begin backup; alter tablespace TEMP begin backup * ERROR at line 1: ORA-03217: invalid option for alter of TEMPORARY TABLESPACE which makes perfect sense, because you wouldn't want to restore that sucker anyway. Here's the skinny from Oracle: cause: Alter database and alter tablespace will not work on locally managed tempfiles as they have no reference in the data dictionary. There is no need to back up the tempfiles as they are used and destroyed every time you start up and shut down the database. fix: There is no need to backup the temporary locally manged tablespaces because: 1. Locally managed tempfiles are always set to NOLOGGING mode. So thus will have no undo. 2. Extents are managed by bitmap in each datafile to keep track of free or used status of blocks in that datafile. 3. The data dictionary does not manage the tablespace. 4. Rollback information is not generated because there is no update on the data dictionary. 5. Media recovery does not recognize tempfiles. > -Original Message- > From: Robert Pegram [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 able to do it. 8.1.7, W2K > > > > (INV-SYSTEM)>@ts > > > > TS_NAME LMT MB_FREE MB_TOTAL > > PCT_USED > > --- -- - - > > - > > LOAD_DATA_A LOCAL740 2000 > > 63 > > LRG_INDEX_A LOCAL390 2500 > > 84.4 > > LRG_INDEX_B LOCAL980 1000 > > 2 > > LRG_TABLE_A LOCAL 2440 4000 > > 39 > > LRG_TABLE_B LOCAL360 1000 > > 64 > > MED_INDEX_A LOCAL480 1000 > > 52 > > MED_TABLE_A LOCAL680 1000 > > 32 > > RBS DICTIONARY 299 1000 > > 70.1 > > SMALL_INDEX_A LOCAL183 200 > > 8.5 > > SMALL_TABLE_A LOCAL169 200 > > 15.5 > > SYSTEM DICTIONARY 110 200 > > 45 > > TEMPDICTIONARY 961 1000 > > 3.9 > > TEMP_DATA_A LOCAL900 1000 > > 10 > > TOOLS LOCAL 1920 > > 5 > > USERS LOCAL 3475 > > 54.7 > > > > 15 rows selected. > > > > (INV-SYSTEM)>alter tablespace load_data_a begin > > backup; > > > > Tablespace altered. > > > > (INV-SYSTEM)>alter tablespace load_data_a end > > backup; > > > > 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, > > > > > > If using a hot backup strategy (other than Rman), > > > make sure to test your backup/recovery if you > > switch > > > the temporary tablespace to locally managed. You > > > can't put a locally managed temporary tablespace > > in > > > backup mode. > > > > > > Rob Pegram > > > Oracle Certified DBA > > > > > > > > > > > > SQL> create tablespace temp_dict > > > 2 datafile 'c:oracleoradataorclemp_dict.dbf' > > size > > > 10M > > > 3 default storage (initial 1M next 1M > > maxextents > > > unlimited pctincrease 0) > > > 4* temporary > > > SQL> / > > > > > > Tablespace created. > > > > > > SQL> create temporary tablespace temp_local > > > 2 tempfile 'c:oracleoradataorclemp_local.dbf' > > size > > > 10M > > > 3 extent management local > > > 4 uniform size 1M; > > > > > > Tablespace created. > > > > > > > > > SQL> alter tablespace temp_dict begin backup; > > > > > > T
RE: Locally managed tablespaces/What a DBA can do ?
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 the machine isn't the quickest in the world (and I have 3 databases on that machine). I plan on upgrading another HP-UX 11.0 64-bit machine to 8.1.7.3.0 by the end of the month. I'm a little nervous about 8.1.7.3.0 on Solaris 7, so I'm going to do some reading before taking the databases to either 8.1.7.2.0 or 8.1.7.3.0. HTH, Julie Julie Fisher Sandia National Laboratories Oracle 8i DBA - OCP8i Solaris 2.6,7/HP-UX 11.0 System Administrator Web Server Administrator -Original Message- Sent: Wednesday, April 17, 2002 1:04 PM To: Multiple recipients of list ORACLE-L Lisa, Thanks. I saw couple of messages today about problems with 8.1.7.3 on Solaris. We are also thinking to move to 8.1.7.3 on HP-UX 11 in near future but now we have to wait and see for a while. Regards Rafiq -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fisher, Julie INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally managed tablespaces/What a DBA can do ?
Lisa, Thanks. I saw couple of messages today about problems with 8.1.7.3 on Solaris. We are also thinking to move to 8.1.7.3 on HP-UX 11 in near future but now we have to wait and see for a while. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 17 Apr 2002 10:21:07 -0800 Hi Rafiq, Sorry. I'm 8.1.7.3 on W2K. It's very possible that things have changed drastically since last year when I created this database. If I remember right there was an issue with export. I may be off my rocker, because nothing is exported out 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 you are talking about? I am using locally managed tablesspace > > with uniform extents for temporary tablespace with tempfile for last one > year with oracle version 8.1.6.2 on HP-UX 11 for datawarehouse database > without any issue/problem. Now we have migrated it to 8.1.7.2 and put it > into production in a month time... > > Regards > Rafiq > > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Wed, 17 Apr 2002 08:53:45 -0800 > > That's not a temp tablespace (TEMP_DATA_A). It's a place to put temporary > load tables. My temporary tablespace is TEMP, which is dictionary. From > the research I did on LMT's before I decided to use them, it looked like > there were several bugs associated with temporary tablespaces being LMT's > so > I left my temporary ts dictionary. > > 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? > > > > > > --- "Koivu, Lisa" <[EMAIL PROTECTED]> wrote: > > > What? I was just able to do it. 8.1.7, W2K > > > > > > (INV-SYSTEM)>@ts > > > > > > TS_NAME LMT MB_FREE MB_TOTAL > > > PCT_USED > > > --- -- - - > > > - > > > LOAD_DATA_A LOCAL740 2000 > > > 63 > > > LRG_INDEX_A LOCAL390 2500 > > > 84.4 > > > LRG_INDEX_B LOCAL980 1000 > > > 2 > > > LRG_TABLE_A LOCAL 2440 4000 > > > 39 > > > LRG_TABLE_B LOCAL360 1000 > > > 64 > > > MED_INDEX_A LOCAL480 1000 > > > 52 > > > MED_TABLE_A LOCAL680 1000 > > > 32 > > > RBS DICTIONARY 299 1000 > > > 70.1 > > > SMALL_INDEX_A LOCAL183 200 > > > 8.5 > > > SMALL_TABLE_A LOCAL169 200 > > > 15.5 > > > SYSTEM DICTIONARY 110 200 > > > 45 > > > TEMPDICTIONARY 961 1000 > > > 3.9 > > > TEMP_DATA_A LOCAL900 1000 > > > 10 > > > TOOLS LOCAL 1920 > > > 5 > > > USERS LOCAL 3475 > > > 54.7 > > > > > > 15 rows selected. > > > > > > (INV-SYSTEM)>alter tablespace load_data_a begin > > > backup; > > > > > > Tablespace altered. > > > > > > (INV-SYSTEM)>alter tablespace load_data_a end > > > backup; > > > > > > 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, > > > > > > > > If using a hot backup strategy (other than Rman), > > > > make sure to test your backup/recovery if you
RE: Locally managed tablespaces/What a DBA can do ?
Hi Rafiq, Sorry. I'm 8.1.7.3 on W2K. It's very possible that things have changed drastically since last year when I created this database. If I remember right there was an issue with export. I may be off my rocker, because nothing is exported out 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 you are talking about? I am using locally managed tablesspace > > with uniform extents for temporary tablespace with tempfile for last one > year with oracle version 8.1.6.2 on HP-UX 11 for datawarehouse database > without any issue/problem. Now we have migrated it to 8.1.7.2 and put it > into production in a month time... > > Regards > Rafiq > > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Wed, 17 Apr 2002 08:53:45 -0800 > > That's not a temp tablespace (TEMP_DATA_A). It's a place to put temporary > load tables. My temporary tablespace is TEMP, which is dictionary. From > the research I did on LMT's before I decided to use them, it looked like > there were several bugs associated with temporary tablespaces being LMT's > so > I left my temporary ts dictionary. > > 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? > > > > > > --- "Koivu, Lisa" <[EMAIL PROTECTED]> wrote: > > > What? I was just able to do it. 8.1.7, W2K > > > > > > (INV-SYSTEM)>@ts > > > > > > TS_NAME LMT MB_FREE MB_TOTAL > > > PCT_USED > > > --- -- - - > > > - > > > LOAD_DATA_A LOCAL740 2000 > > > 63 > > > LRG_INDEX_A LOCAL390 2500 > > > 84.4 > > > LRG_INDEX_B LOCAL980 1000 > > > 2 > > > LRG_TABLE_A LOCAL 2440 4000 > > > 39 > > > LRG_TABLE_B LOCAL360 1000 > > > 64 > > > MED_INDEX_A LOCAL480 1000 > > > 52 > > > MED_TABLE_A LOCAL680 1000 > > > 32 > > > RBS DICTIONARY 299 1000 > > > 70.1 > > > SMALL_INDEX_A LOCAL183 200 > > > 8.5 > > > SMALL_TABLE_A LOCAL169 200 > > > 15.5 > > > SYSTEM DICTIONARY 110 200 > > > 45 > > > TEMPDICTIONARY 961 1000 > > > 3.9 > > > TEMP_DATA_A LOCAL900 1000 > > > 10 > > > TOOLS LOCAL 1920 > > > 5 > > > USERS LOCAL 3475 > > > 54.7 > > > > > > 15 rows selected. > > > > > > (INV-SYSTEM)>alter tablespace load_data_a begin > > > backup; > > > > > > Tablespace altered. > > > > > > (INV-SYSTEM)>alter tablespace load_data_a end > > > backup; > > > > > > 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, > > > > > > > > If using a hot backup strategy (other than Rman), > > > > make sure to test your backup/recovery if you > > > switch > > > > the temporary tablespace to locally managed. You > > > > can't put a locally managed temporary tablespace > > > in > > > > backup mode. > > > > > > > > Rob Pegram > > > > Oracle Certified DBA > > > > > > > > > > > > > > > > SQL> create tablespace temp_
RE: Locally managed tablespaces/What a DBA can do ?
Lisa, What version you are talking about? I am using locally managed tablesspace with uniform extents for temporary tablespace with tempfile for last one year with oracle version 8.1.6.2 on HP-UX 11 for datawarehouse database without any issue/problem. Now we have migrated it to 8.1.7.2 and put it into production in a month time... Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 17 Apr 2002 08:53:45 -0800 That's not a temp tablespace (TEMP_DATA_A). It's a place to put temporary load tables. My temporary tablespace is TEMP, which is dictionary. From the research I did on LMT's before I decided to use them, it looked like there were several bugs associated with temporary tablespaces being LMT's so I left my temporary ts dictionary. 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? > > > --- "Koivu, Lisa" <[EMAIL PROTECTED]> wrote: > > What? I was just able to do it. 8.1.7, W2K > > > > (INV-SYSTEM)>@ts > > > > TS_NAME LMT MB_FREE MB_TOTAL > > PCT_USED > > --- -- - - > > - > > LOAD_DATA_A LOCAL740 2000 > > 63 > > LRG_INDEX_A LOCAL390 2500 > > 84.4 > > LRG_INDEX_B LOCAL980 1000 > > 2 > > LRG_TABLE_A LOCAL 2440 4000 > > 39 > > LRG_TABLE_B LOCAL360 1000 > > 64 > > MED_INDEX_A LOCAL480 1000 > > 52 > > MED_TABLE_A LOCAL680 1000 > > 32 > > RBS DICTIONARY 299 1000 > > 70.1 > > SMALL_INDEX_A LOCAL183 200 > > 8.5 > > SMALL_TABLE_A LOCAL169 200 > > 15.5 > > SYSTEM DICTIONARY 110 200 > > 45 > > TEMPDICTIONARY 961 1000 > > 3.9 > > TEMP_DATA_A LOCAL900 1000 > > 10 > > TOOLS LOCAL 1920 > > 5 > > USERS LOCAL 3475 > > 54.7 > > > > 15 rows selected. > > > > (INV-SYSTEM)>alter tablespace load_data_a begin > > backup; > > > > Tablespace altered. > > > > (INV-SYSTEM)>alter tablespace load_data_a end > > backup; > > > > 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, > > > > > > If using a hot backup strategy (other than Rman), > > > make sure to test your backup/recovery if you > > switch > > > the temporary tablespace to locally managed. You > > > can't put a locally managed temporary tablespace > > in > > > backup mode. > > > > > > Rob Pegram > > > Oracle Certified DBA > > > > > > > > > > > > SQL> create tablespace temp_dict > > > 2 datafile 'c:oracleoradataorcl emp_dict.dbf' > > size > > > 10M > > > 3 default storage (initial 1M next 1M > > maxextents > > > unlimited pctincrease 0) > > > 4* temporary > > > SQL> / > > > > > > Tablespace created. > > > > > > SQL> create temporary tablespace temp_local > > > 2 tempfile 'c:oracleoradataorcl emp_local.dbf' > > size > > > 10M > > > 3 extent management local > > > 4 uniform size 1M; > > > > > > Tablespace created. > > > > > > > > > SQL> alter tablespace temp_dict begin backup; > > > > > > Tablespace altered. > > > > > > SQL> alter tablespace temp_local begin backup; > > > alter tablespace temp_local begin backup > > > * > > > ERROR at line 1: > > > ORA-03217: invalid option for alter of TEMPORARY > > > TABLESPACE > &
RE: Locally managed tablespaces/What a DBA can do ?
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 tablespaces, other then maintenance, would I gain performance ? >From what I read on this list, the growth of my tables is very small compared to some of the other org's, so maybe staying with dictionary-managed would be best, except for the temporary tablespace. Darren. -Original Message- Sent: April 15, 2002 3:58 PM To: Multiple recipients of list ORACLE-L Darren - Do you have to reorganize the vendor's files now? The objective of LMT's is to make Oracle more self-managing (you know, so the MS SQL people can't say it takes a lot more DBA time to manage the database). To me in your situation, the bottom line is whether you have to spend quite a bit of time reorganizing those files today. If the answer is "no", then LMT's probably aren't going to help you much. If the answer is "yes", then that gives you some opportunity try something with their files, since you are doing that anyway. The key point is whether you know how the vendor's software interfaces with Oracle. If you have to reorg quite a bit, then I am confident you know that interface well (otherwise you are jeopardizing the data). If the vendor has made it pretty automated (management of the data), then you probably aren't that familiar with the details of how the vendor accomplishes that, and shouldn't mess with their stuff. I apologize for being wordy here, but maybe you can see my point. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 15, 2002 4:33 PM To: Multiple recipients of list ORACLE-L I am looking into locally managed tablespaces, have done some research, and have a basic understanding of how they work, but I am unsure about the benefits if any for my environment. I have multiple instances running, one for each application. In some cases these apps just have two tablespaces, data and index. If I have a tablespace that is composed of tables that are various sizes and activity, will LMT help or hinder ? I have read the paper about creating tablespaces based on extent size, which is the concept I would follow, but I am unsure how the vendor(s) would feel if I re-org'd their database(s). Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally managed tablespaces/What a DBA can do ?
Daren, Localy managed tablespaces are good for reducing maintenance. You can limit fragmentation. In our shop we are using locally manged tablespaces and it is working fine for us. As far as Vendors are concerned, you can keep a note of what have you done as far as reorganization is concerned just in case any problem you face while implementing a vendor upgrade or patch. Shaibal Senior Oracle DBA Department of Transportation >From: "Browett, Darren" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Locally managed tablespaces/What a DBA can do ? >Date: Mon, 15 Apr 2002 13:33:19 -0800 > >I am looking into locally managed tablespaces, have done some research, and >have a basic >understanding of how they work, but I am unsure about the benefits if any >for my environment. > >I have multiple instances running, one for each application. In some cases >these apps just have two tablespaces, data and index. > >If I have a tablespace that is composed of tables that are various sizes >and >activity, will LMT help or hinder ? > >I have read the paper about creating tablespaces based on extent size, >which is the concept >I would follow, but I am unsure how the vendor(s) would feel if I re-org'd >their database(s). > >Thanks > >Darren > >-- >Darren Browett P.Eng This message >was transmitted >Data Administrator using 100% >recycled electrons >Information and Communication Technology >City of Coquitlam >P:(604)927 - 3614 >E:[EMAIL PROTECTED] > >--- > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Browett, Darren > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaibal Talukder INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally managed tablespaces/What a DBA can do ?
Darren - Do you have to reorganize the vendor's files now? The objective of LMT's is to make Oracle more self-managing (you know, so the MS SQL people can't say it takes a lot more DBA time to manage the database). To me in your situation, the bottom line is whether you have to spend quite a bit of time reorganizing those files today. If the answer is "no", then LMT's probably aren't going to help you much. If the answer is "yes", then that gives you some opportunity try something with their files, since you are doing that anyway. The key point is whether you know how the vendor's software interfaces with Oracle. If you have to reorg quite a bit, then I am confident you know that interface well (otherwise you are jeopardizing the data). If the vendor has made it pretty automated (management of the data), then you probably aren't that familiar with the details of how the vendor accomplishes that, and shouldn't mess with their stuff. I apologize for being wordy here, but maybe you can see my point. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 15, 2002 4:33 PM To: Multiple recipients of list ORACLE-L I am looking into locally managed tablespaces, have done some research, and have a basic understanding of how they work, but I am unsure about the benefits if any for my environment. I have multiple instances running, one for each application. In some cases these apps just have two tablespaces, data and index. If I have a tablespace that is composed of tables that are various sizes and activity, will LMT help or hinder ? I have read the paper about creating tablespaces based on extent size, which is the concept I would follow, but I am unsure how the vendor(s) would feel if I re-org'd their database(s). Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Locally managed tablespaces/What a DBA can do ?
I am looking into locally managed tablespaces, have done some research, and have a basic understanding of how they work, but I am unsure about the benefits if any for my environment. I have multiple instances running, one for each application. In some cases these apps just have two tablespaces, data and index. If I have a tablespace that is composed of tables that are various sizes and activity, will LMT help or hinder ? I have read the paper about creating tablespaces based on extent size, which is the concept I would follow, but I am unsure how the vendor(s) would feel if I re-org'd their database(s). Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
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 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, Texas > www.iNetProfit.com > [EMAIL PROTECTED] > (512)327-9068 > > > -Original Message- > [EMAIL PROTECTED] > Sent: Monday, March 18, 2002 7:28 AM > To: Multiple recipients of list ORACLE-L > > > > Hi, > > Anyone using LMT for rollback segments ? Any issues > , suggestions , ... ? > > TIA, > Antonio Belloni > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jack C. Applewhite > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces
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, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Monday, March 18, 2002 7:28 AM To: Multiple recipients of list ORACLE-L Hi, Anyone using LMT for rollback segments ? Any issues , suggestions , ... ? TIA, Antonio Belloni -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally Managed Tablespaces
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: > > Hi, > > Anyone using LMT for rollback segments ? Any issues > , suggestions , ... ? > > TIA, > Antonio Belloni > > -- > 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-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Locally Managed Tablespaces
Hi, Anyone using LMT for rollback segments ? Any issues , suggestions , ... ? TIA, Antonio Belloni -- 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-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: Thanks for replies to locally managed tablespaces
Thanks for all the help I received, especially as this was my first posting. LMTs are now a separate, and definitely, tested, project for the future. >From us here in Edinburgh, Scotland, have a peaceful Christmas and a memorable Hogmany! Heather -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Docherty, Heather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT: Thanks for replies to locally managed tablespaces
Thanks for all the help I received, especially as this was my first posting. LMTs are now a separate, and definitely, tested, project for the future. >From us here in Edinburgh, Scotland, have a peaceful Christmas and a memorable Hogmany! Heather -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Docherty, Heather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally managed tablespaces
Hi I have recently done this for a client and found it to take quite a period of time 3 days actually in this case to do the analysis of the tables to determine to most likely best fit for tables to extent sizes and which extent size to use. We also did use the below mentioned PDF as a guide as to the approach to take for sizing. You may find that your database grows but this will really depend on the size of tables relative to the extent size of the tablespace you put it in. Best of luck. I would have been trying this in a development environment before doing it to a live database as you may need to re-run some loads due to insufficient tablespace area. Timing could be an issue for a live system Cheers Peter McLarty E-mail: [EMAIL PROTECTED] Technical Consultant WWW: http://www.Mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, Australia Mobile: +61 (0)402 094 238 -- Facsimile: +61 (0)7 3303 3048 "Karniotis, Stephen" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 15/12/2001 07:05 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: 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 before converting. In terms of usage, LMTs are great as they remove all of the ridiculous I/O encountered by the SYSTEM tablespace for monitoring extent performance, allocation, and deallocation. Denise is correct that uniform extents significantly improve the performance of LMTs, however, multiple uniform extent types can be used. However, if your extent sizes are all over the map, you should create some uniform size and then move to LMT. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (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 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 DBAs tend to be a conservative lot. If you go that route, be sure that you end up with uniform extents which I consider the best part of LMT. Be sure to study the paper "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation" by Himatsingka and Loaiza. before your consultant comes so that both of you agree on the approach to take. This is available on Oracle's Web site. The worst circumstance (see cynical above) would be for one person to create them and the other person to maintain them, but each with a different philosophy. I think LMT and uniform extents and extensible tablespaces are the greatest features Oracle has added recently. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 14, 2001 8:55 AM To: Multiple recipients of list ORACLE-L I have just heard today that an external consultant, who is coming to upgrade software for our Student Records system next week, wants to unload the live, test and training databases, and recreate them using locally managed tablespaces. I've been reading all the incredibly positive things oracle have to say about this, but has anybody any real experience of using locally managed tablespaces, and if so, are there any major disadvantages or knock-on effects that I should be aware of? Apart from trying to find disk space to unload each database to do this, would it have any additional space implications? Basically, I need to decide if I should let this go ahead. Heather -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Docherty, Heather INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the off
RE: Locally managed tablespaces
Hello all, FWIW, I use managed in dictionary, no auto-extend, on an HP-UX, OPS 8.1.6.3 system. I considered locally managed (and auto vs. uniform), but as I have extremely fast I/O, I could not justify pulling it out of the dictionary (no problems of excessive $FET and $UET pinging). Now, that was 1 1/2 years ago, so things may have changed. I am upgrading to 8.1.7.2.1 w/i the next month, so I could use that time to switch over, if things have really changed. Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Friday, December 14, 2001 5:25 PM To: Multiple recipients of list ORACLE-L Hey Stephen - That's DENNIS damnit! And I didn't say that uniform extents increase performance (I like autoextend and not getting awakened in the middle of the night), but if anyone has evidence that they do, please share it. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 14, 2001 3:05 PM To: Multiple recipients of list ORACLE-L 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 before converting. In terms of usage, LMTs are great as they remove all of the ridiculous I/O encountered by the SYSTEM tablespace for monitoring extent performance, allocation, and deallocation. Denise is correct that uniform extents significantly improve the performance of LMTs, however, multiple uniform extent types can be used. However, if your extent sizes are all over the map, you should create some uniform size and then move to LMT. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (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 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 DBAs tend to be a conservative lot. If you go that route, be sure that you end up with uniform extents which I consider the best part of LMT. Be sure to study the paper "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation" by Himatsingka and Loaiza. before your consultant comes so that both of you agree on the approach to take. This is available on Oracle's Web site. The worst circumstance (see cynical above) would be for one person to create them and the other person to maintain them, but each with a different philosophy. I think LMT and uniform extents and extensible tablespaces are the greatest features Oracle has added recently. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 14, 2001 8:55 AM To: Multiple recipients of list ORACLE-L I have just heard today that an external consultant, who is coming to upgrade software for our Student Records system next week, wants to unload the live, test and training databases, and recreate them using locally managed tablespaces. I've been reading all the incredibly positive things oracle have to say about this, but has anybody any real experience of using locally managed tablespaces, and if so, are there any major disadvantages or knock-on effects that I should be aware of? Apart from trying to find disk space to unload each database to do this, would it have any additional space implications? Basically, I need to decide if I should let this go ahead. Heather -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Docherty, Heather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yo
RE: Locally managed tablespaces
Hey Stephen - That's DENNIS damnit! And I didn't say that uniform extents increase performance (I like autoextend and not getting awakened in the middle of the night), but if anyone has evidence that they do, please share it. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 14, 2001 3:05 PM To: Multiple recipients of list ORACLE-L 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 before converting. In terms of usage, LMTs are great as they remove all of the ridiculous I/O encountered by the SYSTEM tablespace for monitoring extent performance, allocation, and deallocation. Denise is correct that uniform extents significantly improve the performance of LMTs, however, multiple uniform extent types can be used. However, if your extent sizes are all over the map, you should create some uniform size and then move to LMT. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (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 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 DBAs tend to be a conservative lot. If you go that route, be sure that you end up with uniform extents which I consider the best part of LMT. Be sure to study the paper "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation" by Himatsingka and Loaiza. before your consultant comes so that both of you agree on the approach to take. This is available on Oracle's Web site. The worst circumstance (see cynical above) would be for one person to create them and the other person to maintain them, but each with a different philosophy. I think LMT and uniform extents and extensible tablespaces are the greatest features Oracle has added recently. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 14, 2001 8:55 AM To: Multiple recipients of list ORACLE-L I have just heard today that an external consultant, who is coming to upgrade software for our Student Records system next week, wants to unload the live, test and training databases, and recreate them using locally managed tablespaces. I've been reading all the incredibly positive things oracle have to say about this, but has anybody any real experience of using locally managed tablespaces, and if so, are there any major disadvantages or knock-on effects that I should be aware of? Apart from trying to find disk space to unload each database to do this, would it have any additional space implications? Basically, I need to decide if I should let this go ahead. Heather -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Docherty, Heather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list,
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 before converting. In terms of usage, LMTs are great as they remove all of the ridiculous I/O encountered by the SYSTEM tablespace for monitoring extent performance, allocation, and deallocation. Denise is correct that uniform extents significantly improve the performance of LMTs, however, multiple uniform extent types can be used. However, if your extent sizes are all over the map, you should create some uniform size and then move to LMT. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (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 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 DBAs tend to be a conservative lot. If you go that route, be sure that you end up with uniform extents which I consider the best part of LMT. Be sure to study the paper "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation" by Himatsingka and Loaiza. before your consultant comes so that both of you agree on the approach to take. This is available on Oracle's Web site. The worst circumstance (see cynical above) would be for one person to create them and the other person to maintain them, but each with a different philosophy. I think LMT and uniform extents and extensible tablespaces are the greatest features Oracle has added recently. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 14, 2001 8:55 AM To: Multiple recipients of list ORACLE-L I have just heard today that an external consultant, who is coming to upgrade software for our Student Records system next week, wants to unload the live, test and training databases, and recreate them using locally managed tablespaces. I've been reading all the incredibly positive things oracle have to say about this, but has anybody any real experience of using locally managed tablespaces, and if so, are there any major disadvantages or knock-on effects that I should be aware of? Apart from trying to find disk space to unload each database to do this, would it have any additional space implications? Basically, I need to decide if I should let this go ahead. Heather -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Docherty, Heather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
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. Mentioning cynical, I was leery of the procedure to convert an existing dictionary-managed tablespace to a locally-managed one, but we production DBAs tend to be a conservative lot. If you go that route, be sure that you end up with uniform extents which I consider the best part of LMT. Be sure to study the paper "How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation" by Himatsingka and Loaiza. before your consultant comes so that both of you agree on the approach to take. This is available on Oracle's Web site. The worst circumstance (see cynical above) would be for one person to create them and the other person to maintain them, but each with a different philosophy. I think LMT and uniform extents and extensible tablespaces are the greatest features Oracle has added recently. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, December 14, 2001 8:55 AM To: Multiple recipients of list ORACLE-L I have just heard today that an external consultant, who is coming to upgrade software for our Student Records system next week, wants to unload the live, test and training databases, and recreate them using locally managed tablespaces. I've been reading all the incredibly positive things oracle have to say about this, but has anybody any real experience of using locally managed tablespaces, and if so, are there any major disadvantages or knock-on effects that I should be aware of? Apart from trying to find disk space to unload each database to do this, would it have any additional space implications? Basically, I need to decide if I should let this go ahead. Heather -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Docherty, Heather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally managed tablespaces
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 for our Student Records system next > week, wants to unload > the live, test and training databases, and recreate > them using locally > managed tablespaces. > > I've been reading all the incredibly positive things > oracle have to say > about this, but has anybody any real experience of > using locally managed > tablespaces, and if so, are there any major > disadvantages or knock-on > effects that I should be aware of? Apart from > trying to find disk space to > unload each database to do this, would it have any > additional space > implications? > > Basically, I need to decide if I should let this go > ahead. > > Heather > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Docherty, Heather > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally managed tablespaces
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 > 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 Procedure > > Use this procedure to migrate the tablespace from > dictionary managed > format to locally managed format. Tablespaces > migrated to locally > managed format are user-managed. > > > Syntax > > TABLESPACE_MIGRATE_TO_LOCAL(tablespace_name, > allocation_unit, relative_fno) > > Hope this helps, > John > > > [EMAIL PROTECTED] wrote: > > >I have just heard today that an external > consultant, who is coming to > >upgrade software for our Student Records system > next week, wants to unload > >the live, test and training databases, and recreate > them using locally > >managed tablespaces. > > > >I've been reading all the incredibly positive > things oracle have to say > >about this, but has anybody any real experience of > using locally managed > >tablespaces, and if so, are there any major > disadvantages or knock-on > >effects that I should be aware of? Apart from > trying to find disk space to > >unload each database to do this, would it have any > additional space > >implications? > > > >Basically, I need to decide if I should let this go > ahead. > > > >Heather > > > > = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally managed tablespaces
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_fno within the paranthesis mean ?? Or could u just give me a document link where I can read more about this procedure ?? Regards, Samir Samir Sarkar< BR>Oracle DBA - LennonT eam SchlumbergerSema Email : [EMAIL PROTECTED] s [EMAIL PROTECTED] Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message-From: orantdba [mailto:[EMAIL PROTECTED]]Sent: 14 December 2001 15:40To: Multiple recipients of list ORACLE-LSubject: Re: Locally managed tablespacesHiAny reason that this external consultant is not using the packagedbms_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 Procedure Use this procedure to migrate the tablespace from dictionary managed format to locally managed format. Tablespaces migrated to locally managed format are user-managed. SyntaxTABLESPACE_MIGRATE_TO_LOCAL(tablespace_name, allocation_unit, relative_fno) Hope this helps,John[EMAIL PROTECTED] wrote: I have just heard today that an external consultant, who is coming toupgrade software for our Student Records system next week, wants to unloadthe live, test and training databases, and recreate them using locallymanaged tablespaces.I've been reading all the incredibly positive things oracle have to sayabout this, but has anybody any real experience of using locally managedtablespaces, and if so, are there any major disadvantages or knock-oneffects that I should be aware of? Apart from trying to find disk space tounload each database to do this, would it have any additional spaceimplications?Basically, I need to decide if I should let this go ahead.Heather ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___
Re: Locally managed tablespaces
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 Procedure Use this procedure to migrate the tablespace from dictionary managed format to locally managed format. Tablespaces migrated to locally managed format are user-managed. Syntax TABLESPACE_MIGRATE_TO_LOCAL(tablespace_name, allocation_unit, relative_fno) Hope this helps, John [EMAIL PROTECTED] wrote: I have just heard today that an external consultant, who is coming toupgrade software for our Student Records system next week, wants to unloadthe live, test and training databases, and recreate them using locallymanaged tablespaces.I've been reading all the incredibly positive things oracle have to sayabout this, but has anybody any real experience of using locally managedtablespaces, and if so, are there any major disadvantages or knock-oneffects that I should be aware of? Apart from trying to find disk space tounload each database to do this, would it have any additional spaceimplications?Basically, I need to decide if I should let this go ahead.Heather
Locally managed tablespaces
I have just heard today that an external consultant, who is coming to upgrade software for our Student Records system next week, wants to unload the live, test and training databases, and recreate them using locally managed tablespaces. I've been reading all the incredibly positive things oracle have to say about this, but has anybody any real experience of using locally managed tablespaces, and if so, are there any major disadvantages or knock-on effects that I should be aware of? Apart from trying to find disk space to unload each database to do this, would it have any additional space implications? Basically, I need to decide if I should let this go ahead. Heather -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Docherty, Heather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Locally Managed Tablespaces and autoextend
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 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 per extent that led to me quoting these figures applies only to temporary LMTs. In an ordinary LMT, one bit equates to one extent, so for an 8K block size, which gives you 6 blocks (64K - 2 blocks) in the first bitmap you could build ca. 380,000 extents in a single file before adding more bitmap at the end; for a very small file with its special one-block bitmap, you could still at about 63,500 extents without a new chunk of bitmap appearing. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html | Earlier experiments indicated that for very | large files, with small extents, so that the | total extent count exceeded about 20,000 - | 30,000 (related to block size and therefore | number of blocks actually available from the | 64K for bitmap) - an extra 64K space would | be pre-allocated at the head of file. I did not | test for further extremes - if you have small | extents you should not have very large files. [snipped] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babette Turner-Underwood INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally Managed Tablespaces and autoextend
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 per extent that led to me quoting these figures applies only to temporary LMTs. In an ordinary LMT, one bit equates to one extent, so for an 8K block size, which gives you 6 blocks (64K - 2 blocks) in the first bitmap you could build ca. 380,000 extents in a single file before adding more bitmap at the end; for a very small file with its special one-block bitmap, you could still at about 63,500 extents without a new chunk of bitmap appearing. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html | Earlier experiments indicated that for very | large files, with small extents, so that the | total extent count exceeded about 20,000 - | 30,000 (related to block size and therefore | number of blocks actually available from the | 64K for bitmap) - an extra 64K space would | be pre-allocated at the head of file. I did not | test for further extremes - if you have small | extents you should not have very large files. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally managed tablespaces
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 allocation becomes a delete from FET$ and an insert into UET$ rather than an update to FET$ and an insert into UET$. (I hope I got that right!) Its a rather minor tweaker's thing that probably only makes any detectable difference on a handful of systems. -Don Granaman [certifiable OraSaurus] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, July 23, 2001 10:46 AM > 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 v$sesstat could convince me otherwise. > > -- > Jeremiah Wilton > http://www.speakeasy.net/~jwilton > > On Sun, 22 Jul 2001, Don Granaman wrote: > > > 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 new extent in the latter, you > > might try "pre-fragmenting" the tablespace by creating a junk table > > in it and manually allocating new extents, then dropping the table - > > during a low activity period of course. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jeremiah Wilton > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally managed tablespaces
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 v$sesstat could convince me otherwise. -- Jeremiah Wilton http://www.speakeasy.net/~jwilton On Sun, 22 Jul 2001, Don Granaman wrote: > 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 new extent in the latter, you > might try "pre-fragmenting" the tablespace by creating a junk table > in it and manually allocating new extents, then dropping the table - > during a low activity period of course. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jeremiah Wilton INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Locally managed tablespaces
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 new extent in the latter, you might try "pre-fragmenting" the tablespace by creating a junk table in it and manually allocating new extents, then dropping the table - during a low activity period of course. -Don Granaman [ceritifiable OraSaurus] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, July 21, 2001 3:46 PM > 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 > To: Multiple recipients of list ORACLE-L > > > It does not need to do it. Also SMON will coalesce only if pctincrease>0 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 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 - > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Friday, July 20, 2001 5:16 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 recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Thursday, July 19, 2001 4:32 PM > > > > > > > I was pretty sure you could use it for any TS but SYSTEM. Is this > > documented? > > > > > > Cheers, > > > > > > Earl > > > > > > --- > > > > > > TheOracleDBA > > > [EMAIL PROTECTED] > > > > > > > > > > > > On Thu, 19 Jul 2001 09:32:27 > > > Hillman, Alex wrote: > > > >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- > > > >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 to go with locally managed > > > >tablespaces. I was going to go with autoallocate because the best I > can > > > >tell the only possible drawback with this is a little wasted space. > > > >However, I was under the impression that you could not specify a next > in > > the > > > >storage clause of a table creation. Yet you can. So what happens if I > > have > > > >a next defined? Do they pretty much just throw that away or should I > > really > > > >not define it? > > > > > > > > > > > > > > > > > > > >Kimberly Smith > > > >Database Administrator > > > >IT Dept. - Fujitsu/GMD > > > >Phone: (503) 669-6050 > > > >Fax: (503) 669-5705 > > > >Email : [EMAIL PROTECTED] > > > > > > > >-- > > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > >-- > > > >Author: Kimberly Smith > > > > INET: [EMAIL PROTECTED] > > > > > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > >San Diego, California-- Public Internet access / Mailing Lists > > > >--
RE: Locally managed tablespaces
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 To: Multiple recipients of list ORACLE-L It does not need to do it. Also SMON will coalesce only if pctincrease>0 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 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 - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 5:16 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 recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, July 19, 2001 4:32 PM > > > > I was pretty sure you could use it for any TS but SYSTEM. Is this > documented? > > > > Cheers, > > > > Earl > > > > --- > > > > TheOracleDBA > > [EMAIL PROTECTED] > > > > > > > > On Thu, 19 Jul 2001 09:32:27 > > Hillman, Alex wrote: > > >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- > > >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 to go with locally managed > > >tablespaces. I was going to go with autoallocate because the best I can > > >tell the only possible drawback with this is a little wasted space. > > >However, I was under the impression that you could not specify a next in > the > > >storage clause of a table creation. Yet you can. So what happens if I > have > > >a next defined? Do they pretty much just throw that away or should I > really > > >not define it? > > > > > > > > > > > > > > >Kimberly Smith > > >Database Administrator > > >IT Dept. - Fujitsu/GMD > > >Phone: (503) 669-6050 > > >Fax: (503) 669-5705 > > >Email : [EMAIL PROTECTED] > > > > > >-- > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > >-- > > >Author: Kimberly Smith > > > INET: [EMAIL PROTECTED] > > > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > >San Diego, California-- Public Internet access / Mailing Lists > > > > > >To REMOVE yourself from this mailing list, send an E-Mail message > > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > >the message BODY, include a line containing: UNSUB ORACLE-L > > >(or the name of mailing list you want to be removed from). You may > > >also send the HELP command for other information (like subscribing). > > >-- > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > >-- > > >Author: Hillman, Alex > > > INET: [EMAIL PROTECTED] > > > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > >San Diego, California-- Public Internet access / Mailing Lists > > > > > >To REMOVE yourself from this mailing list, send an E-Mail message > > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > >the message BODY, include a line containing: UNSUB ORACLE-L > > >(or the name of mailing list you want to be removed from). You may > > >also send the HELP command for
Re: Locally managed tablespaces
SMON only coalesces tablespaces with a non-zero percent increase. And current philosophy is to make all extents in a tablespace the same size anyway, so coalescing becomes a non-issue. >From: "Saurabh Sharma" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[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 > >[EMAIL PROTECTED] >http://www.geocities.com/saurabh00pc/stride.html > >- Original Message - >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Sent: Friday, July 20, 2001 5:16 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 recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Thursday, July 19, 2001 4:32 PM > > > > > > > I was pretty sure you could use it for any TS but SYSTEM. Is this > > documented? > > > > > > Cheers, > > > > > > Earl > > > > > > --- > > > > > > TheOracleDBA > > > [EMAIL PROTECTED] > > > > > > > > > > > > On Thu, 19 Jul 2001 09:32:27 > > > Hillman, Alex wrote: > > > >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- > > > >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 to go with locally managed > > > >tablespaces. I was going to go with autoallocate because the best I >can > > > >tell the only possible drawback with this is a little wasted space. > > > >However, I was under the impression that you could not specify a next >in > > the > > > >storage clause of a table creation. Yet you can. So what happens if >I > > have > > > >a next defined? Do they pretty much just throw that away or should I > > really > > > >not define it? > > > > > > > > > > > > > > > > > > > >Kimberly Smith > > > >Database Administrator > > > >IT Dept. - Fujitsu/GMD > > > >Phone: (503) 669-6050 > > > >Fax: (503) 669-5705 > > > >Email : [EMAIL PROTECTED] > > > > > > > >-- > > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > >-- > > > >Author: Kimberly Smith > > > > INET: [EMAIL PROTECTED] > > > > > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > >San Diego, California-- Public Internet access / Mailing >Lists > > > > > > > >To REMOVE yourself from this mailing list, send an E-Mail message > > > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > >the message BODY, include a line containing: UNSUB ORACLE-L > > > >(or the name of mailing list you want to be removed from). You may > > > >also send the HELP command for other information (like subscribing). > > > >-- > > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > >-- > > > >Author: Hillman, Alex > > > > INET: [EMAIL PROTECTED] > > > > > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > >San Diego, California-- Public Internet access / Mailing >Lists > > > >--
RE: Locally managed tablespaces
It does not need to do it. Also SMON will coalesce only if pctincrease>0 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 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 - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 5:16 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 recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, July 19, 2001 4:32 PM > > > > I was pretty sure you could use it for any TS but SYSTEM. Is this > documented? > > > > Cheers, > > > > Earl > > > > --- > > > > TheOracleDBA > > [EMAIL PROTECTED] > > > > > > > > On Thu, 19 Jul 2001 09:32:27 > > Hillman, Alex wrote: > > >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- > > >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 to go with locally managed > > >tablespaces. I was going to go with autoallocate because the best I can > > >tell the only possible drawback with this is a little wasted space. > > >However, I was under the impression that you could not specify a next in > the > > >storage clause of a table creation. Yet you can. So what happens if I > have > > >a next defined? Do they pretty much just throw that away or should I > really > > >not define it? > > > > > > > > > > > > > > >Kimberly Smith > > >Database Administrator > > >IT Dept. - Fujitsu/GMD > > >Phone: (503) 669-6050 > > >Fax: (503) 669-5705 > > >Email : [EMAIL PROTECTED] > > > > > >-- > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > >-- > > >Author: Kimberly Smith > > > INET: [EMAIL PROTECTED] > > > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > >San Diego, California-- Public Internet access / Mailing Lists > > > > > >To REMOVE yourself from this mailing list, send an E-Mail message > > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > >the message BODY, include a line containing: UNSUB ORACLE-L > > >(or the name of mailing list you want to be removed from). You may > > >also send the HELP command for other information (like subscribing). > > >-- > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > >-- > > >Author: Hillman, Alex > > > INET: [EMAIL PROTECTED] > > > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > >San Diego, California-- Public Internet access / Mailing Lists > > > > > >To REMOVE yourself from this mailing list, send an E-Mail message > > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > >the message BODY, include a line containing: UNSUB ORACLE-L > > >(or the name of mailing list you want to be removed from). You may > > >also send the HELP command for other information (like subscribing). > > > > > > > > > Get 250 color business cards for FREE! > > http://businesscards.lycos.com/vp/fastpath/ > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: The Oracle DBA > > INET: [EMAIL PROTECTED] >
Re: Locally managed tablespaces
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 dictionary rows to manage free space. Saurabh Sharma wrote: > 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 - > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Sent: Friday, July 20, 2001 5:16 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 recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Thursday, July 19, 2001 4:32 PM > > > > > > > I was pretty sure you could use it for any TS but SYSTEM. Is this > > documented? > > > > > > Cheers, > > > > > > Earl > > > > > > --- > > > > > > TheOracleDBA > > > [EMAIL PROTECTED] > > > > > > > > > > > > On Thu, 19 Jul 2001 09:32:27 > > > Hillman, Alex wrote: > > > >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- > > > >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 to go with locally managed > > > >tablespaces. I was going to go with autoallocate because the best I > can > > > >tell the only possible drawback with this is a little wasted space. > > > >However, I was under the impression that you could not specify a next > in > > the > > > >storage clause of a table creation. Yet you can. So what happens if I > > have > > > >a next defined? Do they pretty much just throw that away or should I > > really > > > >not define it? > > > > > > > > > > > > > > > > > > > >Kimberly Smith > > > >Database Administrator > > > >IT Dept. - Fujitsu/GMD > > > >Phone: (503) 669-6050 > > > >Fax: (503) 669-5705 > > > >Email : [EMAIL PROTECTED] > > > > > > > >-- > > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > >-- > > > >Author: Kimberly Smith > > > > INET: [EMAIL PROTECTED] > > > > > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > >San Diego, California-- Public Internet access / Mailing Lists > > > > > > > >To REMOVE yourself from this mailing list, send an E-Mail message > > > >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > >the message BODY, include a line containing: UNSUB ORACLE-L > > > >(or the name of mailing list you want to be removed from). You may > > > >also send the HELP command for other information (like subscribing). > > > >-- > > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > >-- > > > >Author: Hillman, Alex > > > > INET: [EMAIL PROTECTED] > > > > > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > >San Diego, California-- Public Internet access / Mailing Lists > > > > > > > &
