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 November 13, 2014

Oracle: Deferred Segment Creation And Tablespace Restrictions

By David Fitzjarrell

An interesting 'problem' surfaced a while ago, one where a user with zero quota on every tablespace could successfully create tables. Of course once it was time to insert data the inserts failed, but this was confusing the user creating the tables. The 'problem' stems from enabling deferred segment creation in the database. Let's see how that can create a confusing situation,

Deferred segment creation allows tables and indexes to be created without physical segments which can be a double-edged sword as it allows objects to be created even if there is no space available for those objects in the specified tablespace. Just like the user who has no quota on the desired tablespace a false sense of security can result when the database reports 'Table created' or 'Index created'. The data dictionary contains the object information so a query of DBA_TABLES, DBA_INDEXES or any of the %TAB% and %IND% tables and views will return the expected information. Querying DBA_SEGMENTS, however, may fail to return data since an object may not have any segments associated with it:


SQL> --
SQL> -- Prove the table exists
SQL> --
SQL> select table_name, tablespace_name
  2  from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
KRONK                          USERS

SQL>
SQL> --
SQL> -- Now prove it doesn't exist
SQL> --
SQL> select segment_name, tablespace_name
  2  from user_segments;

no rows selected

SQL>

Looking at a session creating a table, owned by a user who has 0 quota on every tablespace, reveals this possibly confusing behavior:


SQL> 
SQL> --
SQL> -- See if deferred segment creation is enabled
SQL> --
SQL> show parameter defer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> 
SQL> --
SQL> -- We see that it is
SQL> --
SQL> -- Show quotas for user BLORPOFORST
SQL> --
SQL> @get_user_quotas.sql blorpoforst
SQL> with ttlbytes as (
  2  	select tablespace_name, sum(bytes) ttl_bytes, sum(blocks) ttl_blocks
  3  	from dba_data_files
  4  	group by tablespace_name
  5  ),
  6  userquotas as(
  7  	select
  8  	TABLESPACE_NAME,
  9  	USERNAME,
 10  	bytes,
 11  	nvl(max_bytes,0) max_bytes,
 12  	nvl(blocks,0) blocks,
 13  	nvl(max_blocks,0) MAX_BLOCKS
 14  	from dba_ts_quotas
 15  	where username = upper('&&1')
 16  )
 17  select
 18  s.tablespace_name,
 19  nvl(q.username, upper('&&1')) username,
 20  nvl(BYTES,0) bytes,
 21  case when nvl(q.MAX_BYTES,0) = -1 then s.ttl_bytes else nvl(q.max_bytes,0) end max_bytes,
 22  nvl(blocks, 0) blocks,
 23  case when nvl(q.MAX_BLOCKS,0) = -1 then s.ttl_blocks else nvl(q.max_blocks,0) end max_blocks,
 24  case when nvl(q.bytes,0) > 0 and abs(nvl(q.max_bytes,0) - nvl(q.bytes,0)) < 102400 then 'ALERT'
 25  	  when nvl(q.bytes,0) > 0 and abs(nvl(q.max_bytes,0) - nvl(q.bytes,0)) between 102400 and 10240000 then 'WARNING'
 26  	  else 'OK' end status
 27  from userquotas q full outer join ttlbytes s
 28  	   on (q.tablespace_name = s.tablespace_name)
 29  order by q.username, s.tablespace_name;
old  15:    where username = upper('&&1')
new  15:    where username = upper('blorpoforst')
old  19: nvl(q.username, upper('&&1')) username,
new  19: nvl(q.username, upper('blorpoforst')) username,

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS STATUS
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
DFLT                           BLORPOFORST                             0          0          0          0 OK
INDX                           BLORPOFORST                             0          0          0          0 OK
SYSAUX                         BLORPOFORST                             0          0          0          0 OK
SYSTEM                         BLORPOFORST                             0          0          0          0 OK
UNDOTBS1                       BLORPOFORST                             0          0          0          0 OK
USERS                          BLORPOFORST                             0          0          0          0 OK

6 rows selected.

SQL> 
SQL> 
SQL> --
SQL> -- Absolutely no quotas on any tablespace
SQL> --
SQL> -- Try to create a table, which should fail
SQL> --
SQL> -- Succeeds due to deferred segment creation
SQL> --
SQL> create table kronk(ipso number, egrud varchar2(40))
  2  tablespace users;

Table created.

SQL> 
SQL> --
SQL> -- This will be confusing to users creating tables
SQL> --
SQL> -- 'Table' creates, but no data can be added
SQL> --
SQL> insert into kronk(ipso, egrud)
  2  values (1, 'Insert fails!!!!!');
insert into kronk(ipso, egrud)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


SQL> 
SQL> --
SQL> -- Deferred segment creation allows the 'object' to
SQL> -- exist in the data dictionary even though the quota
SQL> -- prohibits any actual segments being created
SQL> --
SQL> -- Try this again, with an additional parameter
SQL> -- to the create table statement
SQL> --
SQL> drop table kronk purge;

Table dropped.

SQL> 
SQL> create table kronk(ipso number, egrud varchar2(40))
  2  segment creation immediate
  3  tablespace users;
create table kronk(ipso number, egrud varchar2(40))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


SQL> 
SQL> 
SQL> --
SQL> -- Now the table create fails, as expected
SQL> -- because segment creation was forced
SQL> --
SQL> -- Another way to do this is to disable
SQL> -- deferred segment creation at the session
SQL> -- level
SQL> --
SQL> -- This can also be done at the system level
SQL> -- without restarting the database
SQL> --
SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> 
SQL> --
SQL> -- Now the original create table statement will fail
SQL> --
SQL> create table kronk(ipso number, egrud varchar2(40))
  2  tablespace users;
create table kronk(ipso number, egrud varchar2(40))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'


SQL> 

Besides confusing any users creating tables it can also take a DBA by surprise if, in earlier releases of Oracle, the DBA checked for object existence by querying the DBA_SEGMENTS view:


SQL> show parameter defer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL>
SQL> create table yazzoo(plunk number, geepo varchar2(40), alpinga date)
  2  tablespace users;

Table created.

SQL>
SQL> select table_name, tablespace_name
  2  from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
YAZZOO                         USERS
DUMMY                          USERS
SALGRADE                       USERS
BONUS                          USERS
DEPT                           USERS
EMP                            USERS

6 rows selected.

SQL>
SQL> select segment_name, tablespace_name
  2  from user_segments;

SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
DEPT                                                                              USERS
DUMMY                                                                             USERS
EMP                                                                               USERS
SALGRADE                                                                          USERS

SQL>

Notice that two tables, the one just created (YAZZOO) and one of the demonstration tables, BONUS, have no segments even though they do exist in USER_TABLES. I know of DBAs who use DBA_SEGMENTS to check objects and with deferred segment creation that may provide a list that doesn't include every table a user has created. With deferred segment creation enabled older scripts that used DBA_SEGMENTS to successfully report on all objects need to be modified to use DBA_TABLES and DBA_INDEXES to return all tables and indexes regardless of whether segments are created or not; the older scripts can also be modified to show tables and indexes which have no segments as these example using USER_TABLES, USER_INDEXES and USER_SEGMENTS illustrate:


SQL> select table_name, tablespace_name, nvl(ttlbytes,0) ttlbytes, nvl(ttlext,0) ttlext
  2  from
  3  (select t.table_name, t.tablespace_name, sum(s.bytes) ttlbytes, sum(s.extents) ttlext
  4  from user_tables t left outer join user_segments s on (s.segment_name = t.table_name)
  5  group by t.table_name, t.tablespace_name);

TABLE_NAME                     TABLESPACE_NAME                  TTLBYTES     TTLEXT
------------------------------ ------------------------------ ---------- ----------
DUMMY                          USERS                               65536          1
EMP                            USERS                               65536          1
DEPT                           USERS                               65536          1
BONUS                          USERS                                   0          0
YAZZOO                         USERS                                   0          0
SALGRADE                       USERS                               65536          1

6 rows selected.

SQL>
SQL> select index_name, tablespace_name, nvl(ttlbytes,0) ttlbytes, nvl(ttlext,0) ttlext
  2  from
  3  (select t.index_name, t.tablespace_name, sum(s.bytes) ttlbytes, sum(s.extents) ttlext
  4  from user_indexes t left outer join user_segments s on (s.segment_name = t.index_name)
  5  group by t.index_name, t.tablespace_name);

INDEX_NAME                     TABLESPACE_NAME                  TTLBYTES     TTLEXT
------------------------------ ------------------------------ ---------- ----------
SALGRD_IDX                     INDX                                65536          1
YAZZOO_IDX                     INDX                                    0          0
DEPT_IDX                       INDX                                65536          1
EMP_IDX                        INDX                                65536          1
BONUS_IDX                      INDX                                    0          0

SQL>

Deferred segment creation can be very helpful when creating objects that may not be used immediately; it can also create problems when those objects are created by a user account with no quota on a tablespace or when the available space in a tablespace would prevent a segment from being created. The problem won't become apparent until the first insert is met with failure because of a missing quota or insufficient available space. The DBA should get used to checking DBA_TABLES, DBA_INDEXES and DBA_SEGMENTS to verify the object can contain data. Also users who create tables should test an insert after the table is created to prevent any surprises later. Indexes would be tested with that same test insert; if the index is the 'offending' object the test insert will reveal this:


SQL> insert into kronk(ipso, egrud)
  2  values (1, 'Insert fails!!!!!');
insert into kronk(ipso, egrud)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'INDX'


SQL>

Deferred segment creation is, for the most part, a good thing as it allows tables and indexes to be created even when space isn't immediately available. (Of course it's not a good idea to create tables and indexes when it's known there is insufficient space, but in the course of table/index maintenance space may be freed by dropping old versions of a table or by archiving and purging old data no longer needed for daily operation.) A good example for deferred segment creation would be migrating a table to a new definition; deferring segmnent creation allows the new table to grow as the data is inserted rather than creating a large extent where some of that allocated space may remain unused. Another benefit is the new table may be smaller initially than the original even though the number of rows has not decreased. Deferred segment creation can also be ignored, either by setting the deferred_segment_creation parameter to FALSE or by explicitly coding SEGMENT CREATION IMMEDIATE in the create table/create index statement, as shown in the examples above.

How to manage segment creation is a decision that should not be made without serious discussion between the development teams and the DBAs. It can generate a lot of create table activity in a database, creating various incarnations of a table before deciding on the final structure (a task usually left to the architects as an exercise on paper before any code is written and executed). It's also possible that the development team isn't aware of deferred segment creation; this can result in the 'surprise' reported at the beginning of this article when the first attempt to use the created table is executed. It may be better to see the error when the statement is executed rather than later, when data cannot be inserted because segments cannot be created.

Should you use deferred segment creation? That choice is one both the DBA team and development team need to make. Look at all aspects of the argument then decide accordingly. Remember that the 'right' answer is the one that's suited to your environment. One size does not fit all. Choose wisely for the best fit for your environment.

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