PCTFREE on a clustered table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: PCTFREE on a clustered table

  1. #1
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175

    PCTFREE on a clustered table

    I can increase PCTFREE easily on a normal table but not on a clustered table. I have checked at metalink and canít find any solution. Has anyone a workaround on how to increase PCTFEE on a clustered table?

    SQL> alter TABLE NORMA PCTFREE 20
    2 /
    Table altered.

    SQL> alter table CUSTOD PCTFREE 30
    2 /
    alter table CUSTOD PCTFREE 30
    *
    ERROR at line 1:
    ORA-01771: illegal option for a clustered table

    C. K.
    Apps DBA

  2. #2
    Join Date
    Dec 2002
    Location
    Munich, Germany
    Posts
    20
    Might be worth take a look into error description:

    PHP Code:
    :~] $ oerr ora 1771
    01771
    00000"illegal option for a clustered table"
    // *Cause: During a CREATE or ALTER of a clustered table, the user attempted
    //        to enter one or more of the following options:
    //        INITRANS, MAXTRANS, PCTFREE, PCTUSED, STORAGE, TABLESPACE.
    //        These options may only be specified for the cluster itself.
    // *Action: Remove the illegal option(s). 

  3. #3
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    hi kiewer,

    Thanks. I looked at the error description at metalink yesterday but was still confused.
    The cluster tables have no such options as you can see below.

    Clustered table description:

    TABLESPACE COND NOLOGGING
    STORAGE (
    INITIAL 65536
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1 FREELIST GROUPS 1 )
    NOCACHE;
    /

    Regular table description:

    TABLESPACE COND NOLOGGING
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE (
    INITIAL 65536
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    FREELISTS 1 FREELIST GROUPS 1 )
    NOCACHE;

    PCTFREE value according to Oracle represents the percentage of each data block that is
    reserved as free space. If PCTFREE is not an option on clustered tables, how does Oracle handle updates on clustered tables?
    I will appreciate anyone's input. I am worried because my clustered tables are basically the main tables and contain huge customer data and records. When I query PCFTREE on those tables, it reads 0. I am concerned about it.

  4. #4
    Join Date
    Dec 2002
    Location
    Munich, Germany
    Posts
    20
    Yes, clustered table doesn't have. But cluster does!
    I thought it is understable from error description:
    PHP Code:
    //        These options may only be specified for the cluster itself. 
    The Oracle's clustered tables is a specific form of storing data. The data from both tables are stored together in each data block of cluster. Therefore you can't change PCTFREE only for one table, just for whole cluster.

    Read this and you understand everything:

    http://download-west.oracle.com/docs...ntro.htm#15172

  5. #5
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    Thanks kiewer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width