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 August 29, 2016

This Oracle Bug Could Bite You

By David Fitzjarrell

An interesting bug in Oracle 11.2.0.4 was reported to Oracle Support recently, a bug that can cause a DBA to scratch his or her head in either wonder or disbelief. Listed as Bug 24390197 it is fairly easy to replicate. The example starts by creating an empty tablespace of sufficient size to contain several tables, then creates the DEMO schema and adds records to consume space, leaving more than enough space to create a table of approximately 1 MB in size:


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

Let's verify that we have over 1 MB of free space available:


SQL> --
SQL> -- Verify sufficient free space
SQL> -- in USERS2
SQL> --
SQL> connect bong/bing
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> 

Now let's create a table of exactly 1 MB:


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> 

That table created successfully, was populated then dropped and purged to reclaim the space. Now this gets interesting, as a new table creation, of slightly more than 1 MB, but less than the available free space, is attempted:


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> insert into bong.t1 values(1);
insert into bong.t1 values(1)
                 *
ERROR at line 1:
ORA-00942: table or view does not exist


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> 

Why this occurs is still a mystery as Oracle Support hasn't provided any reason or workaround to the bug. Just to prove that the above wasn't a one-time anomaly, another table creation of slightly smaller size than the last is tried:


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> insert into bong.t1 values(1);
insert into bong.t1 values(1)
                 *
ERROR at line 1:
ORA-00942: table or view does not exist


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> 

And again a failure. There is obviously enough space to have created any of the three tables; the problem arises when a table is created and dropped, leaving a 'hole' the size of the original free space available. Regardless of how many free space segments are present in the tablespace and how many of those are equal to or greater than the initial extent specified the error occurs. The bug lists both 11.2.0.3 and 11.2.0.4 as being affected. Testing in 12.1.0.2 shows the bug to be resolved as the error does not reproduce.

In the above example deferred segment creation was turned off; if deferred segment creation is in force the table WILL create (since no actual segments are associated with the table at that time) and the error will be thrown at the first insert into the table, because the segment is then needed and, thus, created.

It is a strange bug, indeed, as no indication of why this occurs is present in the database. Since Oracle Support has posted no resolution the only way around this is to upgrade to 12.1.0.2 or add space to the tablespace to work around the 'space' problem.

Sometimes just knowing what to expect is enough to manage a problem. Hopefully knowing that table creations can fail even with sufficient free space in 11.2.0.3 and 11.2.0.4 will be enough to be able to manage the issue without prolonged interruptions in service.

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