Re: Space Usage Question

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 05 Feb 2004 09:37:41 GMT
Message-ID: <F9oUb.43144$Wa.33347@news-server.bigpond.net.au>

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:40215d51$0$42$cc9e4d1f_at_news.dial.pipex.com...
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:_U5Ub.42357$Wa.22097_at_news-server.bigpond.net.au...
> > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
message
> <snip>
> > Hi Niall,
> >
> > Thanks for the example.
> >
> > I followed it and too got Oracle to fail to reuse the 64K chunks.
> >
> > Little more digging to go ....
> >
> > Cheers ;)
>
> Hey, I showed you mine, will you show me yours? offline if you wish.

Hi Niall

Sure !!

Here goes ....

SQL> create tablespace bowie_auto datafile 'c:\tmp\bowie_auto.dbf' size 200m   2 autoallocate;

Tablespace created.

SQL> create table ziggy (x number) tablespace bowie_auto;

Table created.

SQL> create table aladdin (x number) tablespace bowie_auto;

Table created.

SQL> begin

     2    for i in 1..100 loop
     3      execute immediate 'alter table ziggy allocate extent';
     4      execute immediate 'alter table aladdin allocate extent';
     5    end loop;
     6    end;
     7    /

begin
*
ERROR at line 1:
ORA-01653: unable to extend table BOWIE.ZIGGY by 1024 in tablespace BOWIE_AUTO
ORA-06512: at line 3

SQL> select tablespace_name, blocks from dba_free_space where tablespace_name =
'BOWIE_AUTO';

TABLESPACE_NAME                    BLOCKS
------------------------------ ----------
BOWIE_AUTO                           1016

SQL> create table fill_rest (x number) storage (initial 7m) tablespace bowie_aut
o;

Table created.

SQL> create table fill_rest_2 (x number) storage (initial 960k) tablespace bowie
_auto;

Table created.

SQL> select tablespace_name, blocks from dba_free_space where tablespace_name =
'BOWIE_AUTO'; no rows selected

• I simply created the above two tables to fill up all remaining space in the tablespace ****

SQL> select segment_name, blocks, count(*) from dba_extents where tablespace_nam
e = 'BOWIE_AUTO' group by segment_name, blocks;

SEGMENT_NA BLOCKS COUNT(*)
---------- ---------- ----------

ZIGGY               8         16
ZIGGY             128         63
ZIGGY            1024          4
ALADDIN             8         16
ALADDIN           128         63
ALADDIN          1024          4
FILL_REST         128          7
FILL_REST_          8         15

2

• You can see that both tables ZIGGY and ALADDIN have reached the 8M extent requirement ****

SQL> drop table aladdin;

Table dropped.

SQL> select tablespace_name, blocks, count(*) from dba_free_space where tablespa
ce_name = 'BOWIE_AUTO' group by tablespace_name, blocks;

TABLESPACE_NAME                    BLOCKS   COUNT(*)
------------------------------ ---------- ----------
BOWIE_AUTO                              8         16
BOWIE_AUTO                            128         63
BOWIE_AUTO                           1024          4

SQL> alter table ziggy allocate extent;

Table altered.

SQL> alter table ziggy allocate extent;

Table altered.

SQL> alter table ziggy allocate extent;

Table altered.

SQL> alter table ziggy allocate extent;

Table altered.

SQL> select tablespace_name, blocks, count(*) from dba_free_space where tablespa
ce_name = 'BOWIE_AUTO' group by tablespace_name, blocks;

TABLESPACE_NAME                    BLOCKS   COUNT(*)
------------------------------ ---------- ----------
BOWIE_AUTO                              8         16
BOWIE_AUTO                            128         63

• I've used up the four free 8m extents. The question now is will we be able to continue now there are no more 8m areas of free space ???? ****

SQL> alter table ziggy allocate extent;

Table altered.

SQL> alter table ziggy allocate extent;

Table altered.

SQL> alter table ziggy allocate extent;

Table altered.

SQL> select tablespace_name, blocks, count(*) from dba_free_space where tablespa
ce_name = 'BOWIE_AUTO' group by tablespace_name, blocks;

TABLESPACE_NAME                    BLOCKS   COUNT(*)
------------------------------ ---------- ----------
BOWIE_AUTO                              8         16
BOWIE_AUTO                            128         60

• And the answer is YES. The next three requested extents have gone back to using just the 1M extents as they're the best we can do. ****

SQL> begin
  2 for i in 1..60 loop
  3 execute immediate 'alter table ziggy allocate extent';   4 end loop;
  5 end;
  6 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table BOWIE.ZIGGY by 1024 in tablespace BOWIE_AUTO
ORA-06512: at line 3

SQL> select segment_name, blocks, count(*) from dba_extents where tablespace_nam
e = 'BOWIE_AUTO' group by segment_name, blocks;

SEGMENT_NA BLOCKS COUNT(*)
---------- ---------- ----------

ZIGGY               8         16
ZIGGY             128        120
ZIGGY            1024          8
FILL_REST         128          7
FILL_REST_          8         15

2

SQL> select tablespace_name, blocks, count(*) from dba_free_space where tablespa
ce_name = 'BOWIE_AUTO' group by tablespace_name, blocks;

TABLESPACE_NAME                    BLOCKS   COUNT(*)
------------------------------ ---------- ----------
BOWIE_AUTO                              8         16
BOWIE_AUTO                            128          6

• Interestingly, once we hit 120 1M extents, Oracle decides enough is enough and it really wants to go back to 8M extents again. Even though there are a number of 1M chunks of storage still available ***

Like I said, a little more digging is required (on my part anyways) !!

Cheers

Richard Received on Thu Feb 05 2004 - 03:37:41 CST