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.
