On Tablespace Creation In sys.dba_tablespaces , Field ALLOCATION_TYPE Defaults to
"SYSTEM"
. Hence NEXT_EXTENT of Created Tables is NOT Taken from the User-specified Value but
internally by Oracle itself . This leads to Excessively Large Number of EXTENTs (Small
in Size)
To Allow Table Creation with NEXT_EXTENT taken from User Specified Value , the Value
ALLOCATION_TYPE can be Changed to "USER" From the Original "SYSTEM" Value as follows
SQL> exec sys.dbms_space_admin.tablespace_migrate_from_local('GAM_PT1_TBLSPC');
SQL> exec sys.dbms_space_admin.tablespace_migrate_to_local('GAM_PT1_TBLSPC');
This does the needful & Allows Object's NEXT_EXTENT Size to be User-Specified
We have used Such Tablespaces in Benchmarking Activities BOTH with Oracle 8.1.7 & 9.0
The Performance has been just fine
HTH
P.S. Feel free to mention any Disadvantages with this approach
-----Original Message-----
Sent: Tuesday, September 03, 2002 11:49 PM
To: Multiple recipients of list ORACLE-L
Rachel,
You did not say if you would be using the UNIFORM option for the
LMT's. If you allow the system to choose the initial sizing there can be
a lot of wasted space as the table size grows. The system will choose
sizing options that you most likely would not choose. I can't find my
reference to the sizes that are chosen but 16K 64K 1M and 4M sound
correct. The extends will increase as the total size of the table
increases.
I find it easier to manage the sizes of the tables in the tablespace
with uniform extent sizes that are manageable and mist likely to be
filled in a reasonable time frame. For small static tables I used a
multiple of the block size and tried to group similarly used table in
the same tablespace. The tables that are continually growing daily I
partitioned by date range and allowed the tablespace to autoextend.
There is very little wasted space and the tablespaces are usually 100 %
full until the next extent is needed.
I accepted the default PCTFREE, PCTUSED and FREELIST parameters and I
haven't seen and problems yet.
I hope this helps,
Ron
ROR m���m
>>> [EMAIL PROTECTED] 09/03/02 11:48AM >>>
time for me to ask the experts again.
My data warehouse will be 9.2, with all locally managed tablespaces.
We
will be following what I have taken to calling the "Goldilocks"
principle -- that of small, medium and large tablespace extent sizes,
with variations in that we will separate indexes and data, and will
have even more separation for our fact tables into partitioned tables
and tablespaces.
However, now comes the time for me to work out storage clauses. And a
quick read through the docs leaves me wondering if I should just turn
on automatic segment-space management and not worry about setting
PCTFREE, PCTUSED and FREELIST parameters. I can't find any real
information or bugs on MetaLink either.
Does anyone have any experience, good OR bad, with using this feature?
If you are doing data warehouse work, what are good values for the
parameters if I DO use them? One fact table is likely to be highly
updated (customer info) as we collect more and more specific
information from customers. The rest will be, as you would expect from
a DW, mostly inserts.
Help?
Thanks!
Rachel
__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.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: Ron Rogers
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: VIVEK_SHARMA
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).
