In the 9i SQL Reference Guide Release 2 9.2,
under "CREATE TABLESPACE", "segment_management_clause"
Restrictions on the AUTO clause:
·
·
In the 9i Application Developer's Guide - Large Objects (LOBs) Release 2 9.2,
the chapter "What's New with Large Objects (LOBs) ?", under
"LOB Features Introduced with Oracle 9i, Release 2 (9.2)" ,
under "Removed Restrictions" :
Locally managed tablespaces restriction removed
You can now create LOB columns in locally managed tablespaces.
Also, in the 9iRelease2 9.2.0 Administrator's Guide,
Chapter 11 "Managing Tablespaces", there is no restriction on LOBs
for Segment_Space_Management AUTO. [here, too, there's a line
indicating Segment_Space_Management AUTO is preferred for RAC
environments]
Specifying Segment Space Management in Locally Managed Tablespaces
When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:
·
- Specifying MANUAL tells Oracle that
you want to use free lists for managing free space within segments. Free
lists are lists of data blocks that have space available for inserting
rows. This form of managing space within segments is called manual
segment-space management because of the need to specify and tune the
PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema
objects created in the tablespace.
- MANUAL is the default.
- This keyword tells Oracle that you want to use bitmaps to manage the
free space within segments. A bitmap, in this case, is a map that
describes the status of each data block within a segment with respect to
the amount of space in the block available for inserting rows. As more or
less space becomes available in a data block, its new state is reflected
in the bitmap. Bitmaps allow Oracle to manage free space more
automatically, and thus, this form of space management is called
automatic segment-space management.
Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances. For a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups.
For many standard workloads, application performance when using automatic segment space management is better than the performance of a well tuned application using manual segment-space management.
Bottomline : Either it was a bug or a restriction, it was not possible in 9.0.1 and supposed to be possible in 9.2.0 --- definitely
works in 9.2.0.2
Why do you say "In a single instance environment unless you are doing something out of the ordinary, use of the clause will probably be detrimental. " ?
Hemant
At 03:03 PM 14-12-02 -0800, you wrote:
Was the prohibitition on placing LOB's into tablespaces with automatic segment space management only a bug. I ask because the description of "auto" segment space management includes the following restrictions
Restrictions on AUTO:
* You can specify this clause only for permanent, locally managed tablespace.
* You cannot specify this clause for the SYSTEM tablespace.
* You cannot store LOBs in AUTO segment-managed tablespaces.
------------------------------------------------------------------------------------------------------------------------
The above is from the 9.0.1 SQL manual.
One usually associates a bug with something the manual says one can do, but one cannot. I've not seen the 9.0.2 manual. Are the restrictions lifted?
In a single instance environment unless you are doing something out of the ordinary, use of the clause will probably be detrimental.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-----Original Message-----
Sent: Friday, December 13, 2002 7:19 PM
To: Multiple recipients of list ORACLE-L
The first time we tried importing our 8.1.5 [OPS] database
into 9.0.1 and 9.2.0.1 with
EXTENT_MANAGEMENT Local for ALL tablespaces
SEGMENT_SPACE_MANAGEMENT Auto
we found that tables with LOB segments could not be created. This was a 9.0.1 bug which was expected to have been fixed in 9.2 [I believe it was Bug 1626182, referenced in Note 159078.1]
However, after going to 9.2.0.2, I WAS able to create
such tables in the database.
[the Patchset Notes include a reference to Bug 2326066. Unfortunately, I can't see either bug on MetaLink to verify if both are the same issue]
9iRAC strongly recommends SEGMENT_SPACE_MANAGEMENT Auto
instead of using Free_Lists and Free_List_Groups. [Part 3, Appendix B of the 9iRAC Deployment and Performance manual].
Are there any real-life experiences / gotchas with LOB Segments in Locally Managed Tablespaces and Segment_Space_Management Auto ? There seem to be a number of bug-fixes for Segment_Space_Management=AUTO in the 9.2.0.2 PatchSet Release Notes.
Hemant K Chitale
My web site page is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hemant K Chitale
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.com
--
Author: MacGregor, Ian A.
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).
My web site page is : http://hkchital.tripod.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hemant K Chitale 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).
