Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted September 12, 2016

The Oracle Bug That Could Bite You Has No Teeth

By David Fitzjarrell

In my previous article an issue was discussed that Oracle Support declared as a bug. Further investigation indicates this is not a bug but expected behavior for locally managed tablespaces with system-managed extents, which is why no solution or workaround has been posted as of now. Let's look at the problem again, and show that the error message indicated this. Running the original script again, in Oracle 11.2.0.4, provides the background and the error message that was apparently misinterpreted.

The example starts, as last time, creating an empty tablespace to create the tables in:


SQL> --
SQL> -- Create a blank tablespace to run
SQL> -- the example from
SQL> --
SQL> connect / as sysdba
Connected.
SQL> create tablespace users2 datafile 'C:\APP\DFITZJARRELL\ORADATA\DELP11G\USERS201.DBF' size 3M segment space management manual;

Tablespace created.

SQL> 
SQL> --
SQL> -- Create a non-privileged user
SQL> -- to create tables in the new
SQL> -- tablespace
SQL> --
SQL> -- Grant quotas to two users
SQL> --
SQL> @bong
SQL> create user bong identified by !@#!@#!@#
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

SQL> 
SQL> grant connect, create table to bong;

Grant succeeded.

SQL> grant create procedure to bong;

Grant succeeded.

SQL> grant create synonym to bong;

Grant succeeded.

SQL> grant create public synonym to bong;

Grant succeeded.

SQL> grant create view to bong;

Grant succeeded.

SQL> 
SQL> alter user bing quota unlimited on users2;

User altered.

SQL> alter user bong quota unlimited on users2;

User altered.

SQL> grant select on sys.dba_free_space to bong;

Grant succeeded.

SQL> grant select on dba_extents to bong;

Grant succeeded.

SQL> 

SQL> --
SQL> -- Create demo tables
SQL> --
SQL> -- Populate EMP with a sizeable
SQL> -- 'chunk' of data
SQL> --
SQL> -- Leave more than enough room to create
SQL> -- another table with ~1MB extent size
SQL> --
SQL> connect bing/@#$%@#$%
Connected.
SQL> @demobld_users2
Building demonstration tables.  Please wait.
Demonstration table build is complete.
SQL> 
SQL> insert into emp select * From emp;

14 rows created.

SQL> /

28 rows created.

SQL> /

56 rows created.

SQL> /

112 rows created.

SQL> /

224 rows created.

SQL> /

448 rows created.

SQL> /

896 rows created.

SQL> /

1792 rows created.

SQL> /

3584 rows created.

SQL> commit;

Commit complete.

SQL> 

SQL> 
SQL> --
SQL> -- Verify sufficient free space
SQL> -- in USERS2
SQL> --
SQL> connect bong/!@#!@#!@#
Connected.
SQL> alter session set deferred_segment_creation = false;

Session altered.

SQL> select file_id,block_id,bytes,blocks from dba_free_space where
  2  tablespace_name='USERS2';

   FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         5        200    1507328        184

SQL> 
SQL> --
SQL> -- Try to create a table with 1MB initial
SQL> -- extent
SQL> --
SQL> create table bong.t1 (no number) tablespace users2 storage(initial 1048576);

Table created.

SQL> insert into bong.t1 values(1);

1 row created.

SQL> 
SQL> --
SQL> -- Table created
SQL> --
SQL> -- Report free space remaining
SQL> --
SQL> select file_id,block_id,bytes,blocks from dba_free_space where
  2  tablespace_name='USERS2';

   FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         5        200     458752         56

SQL> select segment_name,extent_id,bytes,blocks from dba_extents where
  2  segment_name='T1';

SEGMENT_NAME                                                                       EXTENT_ID      BYTES     BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
T1                                                                                         0    1048576        128

SQL> 
SQL> --
SQL> -- Drop table T1
SQL> --
SQL> drop table bong.t1 purge;

Table dropped.

SQL> 

The original table was successfully created because there was sufficient free space to create the single 1 MB extent required. The misunderstanding from the person who submitted the SR, and with Oracle Support, was not reading the error message carefully. With locally managed tablespaces using extents of this size any table requiring more space than a single extent can supply requires multiple extents. This was (presumably) clearly indicated in the error message generated, shown in the section below:


SQL> --
SQL> -- Try again with a different initial size
SQL> --
SQL> select file_id,block_id,bytes,blocks from dba_free_space where
  2  tablespace_name='USERS2';

   FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         5        200    1507328        184

SQL> select segment_name,extent_id,bytes,blocks from dba_extents where
  2  segment_name='T1';

no rows selected

SQL> create table bong.t1 (no number) tablespace users2 storage(initial 1179648);
create table bong.t1 (no number) tablespace users2 storage(initial 1179648)
*
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 1 in tablespace USERS2


SQL> 
SQL> --
SQL> -- Table create fails!!
SQL> --
SQL> -- Verify free space yet again
SQL> --
SQL> select file_id,block_id,bytes,blocks from dba_free_space where
  2  tablespace_name='USERS2';

   FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         5        200    1507328        184

SQL> select segment_name,extent_id,bytes,blocks from dba_extents where
  2  segment_name='T1';

no rows selected

SQL> drop table bong.t1 purge;
drop table bong.t1 purge
                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> --
SQL> -- Try one more time
SQL> --
SQL> create table bong.t1 (no number) tablespace users2 storage(initial 1114112);
create table bong.t1 (no number) tablespace users2 storage(initial 1114112)
*
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 1 in tablespace USERS2


SQL> 
SQL> --
SQL> -- And again it fails!!!
SQL> --
SQL> -- Report free space
SQL> --
SQL> select file_id,block_id,bytes,blocks from dba_free_space where
  2  tablespace_name='USERS2';

   FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         5        200    1507328        184

SQL> select segment_name,extent_id,bytes,blocks from dba_extents where
  2  segment_name='T1';

no rows selected

SQL> 
SQL> drop table bong.t1 purge;
drop table bong.t1 purge
                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 

As can be seen the error states no more than one extent can be created. Since in all recent releases of Oracle the SYSTEM tablespace is locally managed this limits the extent management to system-managed with a size cycle that increases to a maximum of 16 MB or to a uniform extent size set at tablespace creation. Had Oracle been able to create a dictionary managed tablespace the error wouldn't have been thrown with the space available as only a single extent would be required for any of the table sizes provided in the example.

Fixing this issue is as simple as adding space to the tablespace:


SQL> 
SQL> connect / as sysdba
Connected.
SQL> 
SQL> alter database datafile 5 resize 5M;

Database altered.

SQL> 

After the tablespace is resized the table creation is tried again:


SQL> 
SQL> connect bong/!@#!@#!@#
Connected.
SQL> --
SQL> -- Report free space
SQL> --
SQL> select file_id,block_id,bytes,blocks from dba_free_space where
  2  tablespace_name='USERS2';

   FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         5        200    3604480        440

SQL> select segment_name,extent_id,bytes,blocks from dba_extents where
  2  segment_name='T1';

no rows selected

SQL> 
SQL> --
SQL> -- Try one more time
SQL> --
SQL> create table bong.t1 (no number) tablespace users2 storage(initial 1114112);

Table created.

SQL> insert into bong.t1 values(1);

1 row created.

SQL> 
SQL> --
SQL> -- It succeeds, and it's apparent why
SQL> -- Oracle needs two 1 MB extents to allocate
SQL> -- more than 1 MB of space unless the tablespace
SQL> -- is dictionary managed
SQL> --
SQL> -- Report free space
SQL> --
SQL> select file_id,block_id,bytes,blocks from dba_free_space where
  2  tablespace_name='USERS2';

   FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ----------
         5        200     458752         56
         5        512    1048576        128

SQL> select segment_name,extent_id,bytes,blocks from dba_extents where
  2  segment_name='T1';

SEGMENT_NAME                                                                       EXTENT_ID      BYTES     BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
T1                                                                                         0    1048576        128
T1                                                                                         1    1048576        128

SQL> 
SQL> drop table bong.t1 purge;

Table dropped.

SQL> 
SQL> connect  / as sysdba
Connected.
SQL> 
SQL> drop tablespace users2 including contents and datafiles;

Tablespace dropped.

SQL> 

Notice that two extents are created for tables exceeding exactly 1 MB in size, which coincides with the condition reported in the error message.

Carefully reading error messages is necessary to accurately diagnose problems; glossing over a message and making assumptions can lead the DBA, and others providing assistance, down the wrong path delaying problem resolution. It's difficult, if not impossible, to solve a problem if the problem declaration isn't accurately interpreted.

This bug has no teeth, but the problem can still affect production systems if the DBA isn't careful reading the error text. Jumping to conclusions is easy, jumping back is hard. It pays to understand what is actually happening rather than what someone thinks is happening. A Latin phrase comes to mind: "Post hoc, ergo proptor hoc". Translated it means "after this therefore because of this", and just because something happens doesn't mean the event prior to that something was what caused it. Oracle reported insufficient space and it was right, regardless of what others saw and interpreted. It wasn't that there appeared to be sufficient space that was the problem; it was the mechanism of creating extents in a locally managed tablespace that threw the error. Reading the error text carefully hopefully would have made that clear. It's good to keep that in mind to avoid unnecessary interruptions in production systems.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM