Re: pctfree,pctused

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Thu, 20 Jul 2006 00:04:22 GMT
Message-Id: <pan.2006.07.20.00.04.19.691532@sbcglobal.net>

On Wed, 19 Jul 2006 12:12:24 -0700, agnieszka.sztela wrote:

> but in the created table there is: pctused 0,pctfree 50
> Why??

Sybrand has asked you for the version, and that is the root cause of what you're seeing. The behavior you describe is consistent with version 10.2. In 9i, when a table was created in ASSM tablespace, PCTUSED was ignored but was written down as specified. In 10g, ass managed tablespaces are the default and PCTUSED is NULL and in some versions is probably shown as 0 (NVL). Here is how it works:

SQL> CREATE TABLE EMP1
   ( EMPNO NUMBER(4,0),

     ENAME VARCHAR2(10),
     JOB VARCHAR2(9),
     MGR NUMBER(4,0),
     HIREDATE DATE,
     SAL NUMBER(7,2),
     COMM NUMBER(7,2),
     DEPTNO NUMBER(2,0)

    ) PCTFREE 50 PCTUSED 30
/
  2 3 4 5 6 7 8 9 10 11 Table created.

SQL> select pct_free,pct_used from user_tables   2 where table_name='EMP1'
  3 /

  PCT_FREE PCT_USED
---------- ----------

        50

I'm getting the same results as you.

SQL> select segment_space_management
  2 from user_Tablespaces
  3 where tablespace_name='USERS';

SEGMEN

---

AUTO SQL> drop table emp1
  2 /

Table dropped.

SQL> purge recyclebin;

Recyclebin purged.

(This shows you that I too am operating on a 10g instance. Oracle 9i doesn't have recyclebin)
SQL> Now, let's try with another tablespace:

SQL> create tablespace test segment space management manual;

Tablespace created.

SQL> alter user scott quota unlimited on test;

User altered.

SQL> connect scott/tiger
Connected.
SQL> CREATE TABLE EMP1

  2      ( EMPNO NUMBER(4,0),
  3        ENAME VARCHAR2(10),
  4        JOB VARCHAR2(9),
  5        MGR NUMBER(4,0),
  6        HIREDATE DATE,
  7        SAL NUMBER(7,2),
  8        COMM NUMBER(7,2),
  9       DEPTNO NUMBER(2,0)
 10      ) tablespace test PCTFREE 50 PCTUSED 30

 11 /

Table created.

SQL> select pct_free,pct_used from user_tables   2 where table_name='EMP1';

  PCT_FREE PCT_USED
---------- ----------

        50 30

SQL> Now, it's OK. Consequently, you need to create table in a tablespace which is not ass managed. And you did tell us your version: it's 10g.

PS:

---
The expression "ass managed tablespaces" was invented by Pete Sharman, a
brilliant DBA and RAC Oracle support engineer from Australia. I gladly
accepted his witticism as my personal standard. 

-- 
http://www.mgogala.com

Received on Wed Jul 19 2006 - 19:04:22 CDT