PCTFREE on a clustered table

[akwete]

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

[kiewer]

Might be worth take a look into error description:

:~] $ 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). 

[akwete]

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.

[kiewer]

Yes, clustered table doesn't have. But cluster does!
I thought it is understable from error description:

//        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

[akwete]

Thanks kiewer.