Re: Little competition

From: Mladen Gogala <mladen_at_wangtrading.com>
Date: Thu, 11 Dec 2003 06:29:24 -0800
Message-ID: <F001.005D98DB.20031211062924@fatcity.com>

Pctfree and pctused are not storage parameters.
Oracle allows using both of those parameters as shown by the next example:

SQL> select tablespace_name from dba_tablespaces 2 where segment_space_management='AUTO';

TABLESPACE_NAME

CWMLITE
DRSYS
EXAMPLE
INDX
ODM
TOOLS
USERS
XDB
COMPLIANCE
VIEWS 10 rows selected.

SQL> set termout on
SQL> set echo on
SQL> @/tmp/1
SQL> create table
  2           test_table
  3          (c1 number)
  4          tablespace
  5           compliance
  6           pctfree 20 pctused 30

7 /

Table created.

SQL> 3) I have no idea who the "guru" is, but he or she hasn't been following this list.

There was quite an extensive debate about ASSM not so long ago, resolved by Tanel Poder's detailed presentation of the new bitmap mechanism.

On 12/11/2003 06:39:26 AM, Richard Foote wrote:
> Little competition for you all :)
>
> It's a two part question:
> a.. What's wrong with the following piece of expert analysis ?
> b.. Which well know "Oracle Guru" published this (and continues to display it on his web-page) ?
>
>
> "Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance."
>
> SQL> create table
> 2 test_table
> 3 (c1 number)
> 4 tablespace
> 5 asm_test
> 6 storage
> 7 ( pctfree 20 pctused 30 )
> 8 ;
>
> ( pctfree 20 pctused 30 )
> *
> ERROR at line 7:
> ORA-02143: invalid STORAGE option
>
> However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings"
>
>
>
> You've gotta love it !!
>
> Sorry no clues ....
>
> Cheers ;)
>
> Richard
>

Mladen Gogala
Oracle DBA

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: mladen_at_wangtrading.com

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: ListGuru_at_fatcity.com (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).

Received on Thu Dec 11 2003 - 08:29:24 CST