Re: Generic "Can I extend?" check

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 8 Aug 2002 13:18:43 +0100
Message-ID: <3d526240$0$234$ed9e5944@reading.news.pipex.net>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3d523838$0$238$ed9e5944_at_reading.news.pipex.net...
> "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> news:3D518D84.4A77_at_yahoo.com...
> I haven't actually done the test (I'm in the middle of upgrading our ERP
> system so don't get much time for playing) but I suspect that you would be
> able to break your code by filling a small tablespace with a number of
> differently sized objects leaving random chunks of differently sized free
> space scattered about the place. In other words if the tablespace has say
a
> 64k and a 1m chunk of free space left but you are now in the 8mb extent
> range I rather suspect that an autoallocate tablespace will allocate first
> the 1m and then the 64 k as required.

Mind you it appears my suspicionas are wrong

SQL> create user u1 identified by u1
2 ;

User created.

SQL> create tablespace test
2 datafile 'c:\oracle\oradata\test01.dbf' size 66688k 3 extent management local autoallocate; create tablespace test
*
ERROR at line 1:

ORA-01119: error in creating database file 'c:\oracle\oradata\test01.d
ORA-27038: skgfrcre: file exists
OSD-04010: <create> option specified, file already exists

SQL> create tablespace test
2 datafile 'c:\oracle\oradata\test01.dbf' size 66688k 3 extent management local autoallocate;

Tablespace created.

SQL> alter user u1 default tablespace test temporary tablespace temp;

User altered.

SQL> grant create session, create table to u1;

Grant succeeded.

SQL> alter user u1 quota unlimited on test;

User altered.

SQL> connect u1/u1;
Connected.
SQL> create table t1(col1 varchar2(20))
2 storage (initial 64k next 64k)
3 tablespace test;

Table created.

SQL> begin
  2 for i in 1..79 loop
  3 execute immediate 'alter table t1 allocate extent';   4 end loop;
  5 end;
  6 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table U1.T1 by 1024 in tablespace TEST ORA-06512: at line 3

SQL> select count(*) from user_extents;

COUNT(*)

SQL> select segment_name,bytes/1024 size_in_k,count(*) 2 from user_extents
3 group by segment_name,bytes/1024;

SEGMENT_NAME

SIZE_IN_K COUNT(*)
---------- ----------
T1

64 16

1024 63

SQL> col segment_name for a20
SQL> / SEGMENT_NAME SIZE_IN_K COUNT(*)

-------------------- ---------- ----------
T1                           64         16
T1                         1024         63

SQL> select bytes/1024 size_in_k
2 from user_free_space
3 where tablespace_name = 'TEST';

SIZE_IN_K

1088 Received on Thu Aug 08 2002 - 07:18:43 CDT