Creating Tables in the UNDO Tablespace??

I was reading an article written by Martin Widlake in Oracle Scene Issue 58 (Autumn/Winter 2015). It said:

The second new item is the UNDO tablespace. This is a special tablespace that is only used for internal purposes and one that users cannot put any tables or indexes into.

This seemed perfectly reasonable so I wondered what might happen if I tried to do it. In an Oracle 9.2.0.7 database Oracle returned an error:

SQL> create table tab1

  2  (col1 number)

  3  tablespace undo_1

  4  /

create table tab1

*

ERROR at line 1:

ORA-30022: Cannot create segments in undo tablespace

SQL>

In an Oracle 11.2.0.1 database, I was not allowed to use the UNDO tablespace as a user’s default tablespace:

SQL> l

  1  create user andrew

  2  identified by reid

  3* default tablespace undotbs1

SQL> /

create user andrew

*

ERROR at line 1:

ORA-30033: Undo tablespace cannot be specified as

default user tablespace

SQL>

... but I was allowed to create a table in it:

SQL> create table tab1

  2  (col1 number)

  3  tablespace undotbs1

  4  /

Table created.

SQL> 

Does anybody know if this is a bug? I will update this post if I find out.

Postscript written on 19^th February 2016:

I wrote the post above yesterday and, if you check the comments below, you will see that a couple of people have now helped me to understand what happened. It was all down to deferred segment creation. I have discussed this before here and here (and other places too) but it still catches me out from time to time. I returned to the Oracle 11.2.0.1 database and ran Andrzej’s SQL for confirming that UNDOTBS1 was an UNDO tablespace:

SQL> select contents from dba_tablespaces

  2  where tablespace_name = 'UNDOTBS1'

  3  /

CONTENTS

---------

UNDO

SQL>

Then I confirmed that deferred segment creation was turned on:

SQL> l

  1  select value from v$parameter

  2* where name = 'deferred_segment_creation'

SQL> /

VALUE

--------------------

TRUE

SQL>

I created another table in the UNDO tablespace:

SQL> create table dom (col1 number)

  2  tablespace undotbs1

  3  /

Table created.

SQL>

That worked but, as Dom suggested, when I tried to insert a row, Oracle needed to create a segment and was unable to do so:

SQL> insert into dom values (1)

  2  /

insert into dom values (1)

            *

ERROR at line 1:

ORA-30022: Cannot create segments in undo tablespace

SQL>

Finally, I turned deferred segment creation off:

SQL> l

  1  alter session

  2* set deferred_segment_creation = false

SQL> /

Session altered.

SQL>

Once I had done that, I was unable to create a table in the UNDO tablespace at all:

SQL> create table andrzej (col1 number)

  2  tablespace undotbs1

  3  /

create table andrzej (col1 number)

*

ERROR at line 1:

ORA-30022: Cannot create segments in undo tablespace

SQL>

I’m guessing that when Andrzej created his example, deferred_segment_creation was set to false at the session level as above, at the system level like this:

SQL> alter system set deferred_segment_creation = false

  2  /

System altered.

SQL>

Or via an initialisation parameter in his database’s parameter file.

ORA-04000

I created a table in an Oracle 11.2 database. I did not specify pctfree or pctused so it was given the defaults of 10% and 40% respectively:

SQL> conn system/manager
Connected.
SQL> create table t1 (c1 number)
  2  /

Table created.

SQL> select pct_free, pct_used
  2  from user_tables
  3  where table_name = 'T1'
  4  /

  PCT_FREE   PCT_USED
---------- ----------
        10         40

SQL>

When Oracle inserts rows into a table, the pctfree specifies the percentage of space to leave free in each block for subsequent updates to the rows. This free space is used later if an extra column is added to the table or if a varchar2 column is updated to store a longer value than before. Once the pctfree in a given block falls below the specified value, no new rows can be inserted in that block so it is removed from the free list. It is then not allowed to accept new rows until the percentage of space used in the block falls below the pctused figure. When this happens, the block goes back onto the free list again. You can alter the pctfree and/or pctused settings like this:

SQL> alter table t1 pctfree 20
  2  /

Table altered.

SQL>

Going through some old notes from an Oracle 9 performance tuning course, I read that the sum of pctfree and pctused cannot be more than 100. This seemed reasonable. If you had a pctused of 40%, a pctfree of 70% and a block which was 35% full, Oracle would not know what to do with it. The pctused figure of 40% would tell Oracle to leave the block on the free list wheras the pctfree figure of 70% would tell Oracle to remove it (from the free list). In situations like this, Oracle usually has a special error message to display. In this case, it is ORA-04000, as you can see below:

SQL> alter table t1 pctfree 70
  2  /
alter table t1 pctfree 70
*
ERROR at line 1:
ORA-04000: the sum of PCTUSED and PCTFREE cannot
exceed 100

SQL>

Default Size of a CHAR Column

If you do not specify a size for a CHAR column, the default is 1. You can see what I mean in the example below, which I tested on Oracle 11.2:

SQL> create table t1

  2  (c1 char,

  3   c2 char(1))

  4  /

 

Table created.

 

SQL> desc t1

Name                       Null?    Type

-------------------------- -------- ------------------

C1                                  CHAR(1)

C2                                  CHAR(1)

 

SQL> 

However, if you rely on defaults like this and the software supplier changes them, you could be left with an application which does not work. 

Simple Example with REPLACE

The REPLACE function allows you to change a string of characters to another string of characters and can accept three parameters:

 

(1)    Input column name.

(2)    Old string value.

(3)    New string value.

 

You can see what I mean in the example below, which I tested on Oracle 11.2:

 

SQL> create table directory_name

  2  (location varchar2(30))

  3  /

 

Table created.

 

SQL> insert into directory_name

  2  values('/batch/prod/dir1')

  3  /

 

1 row created.

 

SQL> insert into directory_name

  2  values('/batch/prod/dir2')

  3  /

 

1 row created.

 

SQL> select location from directory_name

  2  /

 

LOCATION

------------------------------

/batch/prod/dir1

/batch/prod/dir2

 

SQL> update directory_name

  2  set location = replace(location,'prod','test')

  3  /

 

2 rows updated.

 

SQL> select location from directory_name

  2  /

 

LOCATION

------------------------------

/batch/test/dir1

/batch/test/dir2

 

SQL>

How to Rename a Table

I tested this example in Oracle 12.1. First I created a table:

SQL> create table fred

  2  as select * from user_synonyms

  3  where 1 = 2

  4  /

 

Table created.

SQL>

Then I checked its object_id for later:

SQL> select object_id

  2  from user_objects

  3  where object_name = 'FRED'

  4  /

 

OBJECT_ID

----------

     92212

SQL>

... and described it:

SQL> desc fred

Name                       Null?    Type

-------------------------- -------- ------------------

SYNONYM_NAME               NOT NULL VARCHAR2(128)

TABLE_OWNER                         VARCHAR2(128)

TABLE_NAME                 NOT NULL VARCHAR2(128)

DB_LINK                             VARCHAR2(128)

ORIGIN_CON_ID                       NUMBER

SQL>

Then I changed its name:

SQL> rename fred to joe

  2  /

 

Table renamed.

SQL>

... and finally, to prove I was still looking at the same object, I used the new name to look up the object_id and description and confirmed that they had not changed:

SQL> select object_id

  2  from user_objects

  3  where object_name = 'JOE'

  4  /

 

OBJECT_ID

----------

     92212

 

SQL> desc joe

Name                       Null?    Type

-------------------------- -------- ------------------

SYNONYM_NAME               NOT NULL VARCHAR2(128)

TABLE_OWNER                         VARCHAR2(128)

TABLE_NAME                 NOT NULL VARCHAR2(128)

DB_LINK                             VARCHAR2(128)

ORIGIN_CON_ID                       NUMBER

 

SQL>

Permissions Required to Create a Materialized View

The idea for this post came from a problem, which I saw on Javier Morales Carreras' blog here.

This example was tested on Oracle 11.2. It shows the permissions required to create a materialized view. First I created a user:

 

SQL> conn / as sysdba

Connected.

SQL> create user andrew identified by reid

  2  /

 

User created.

 

SQL> grant create session to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

I logged in as the user and tried to create a materialized view. This failed (obviously):

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

create materialized view mv1 as select * from dual

                                              *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

I granted create materialized view to the user:

 

SQL> conn / as sysdba

Connected.

SQL> grant create materialized view to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

…but when I logged in as the user and tried to create a materialized view again, this failed too:

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

create materialized view mv1 as select * from dual

                                              *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

This is because you also need the create table privilege before you can create a materialized view:

 

SQL> conn / as sysdba

Connected.

SQL> grant create table to andrew

  2  /

 

Grant succeeded.

 

SQL>

 

When I tried to create a materialized view this time, I saw a different error:

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

create materialized view mv1 as select * from dual

                                              *

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

 

SQL>

 

I attended to the ORA-01950 and the user was then able to create a materialized view:

 

SQL> conn / as sysdba

Connected.

SQL> alter user andrew quota unlimited on users

  2  /

 

User altered.

 

SQL> conn andrew/reid

Connected.

SQL> create materialized view mv1 as select * from dual

  2  /

 

Materialized view created.

 

SQL>

 

You need to be able to create tables because a materialized view has an underlying table itself:

 

SQL> select object_name, object_type from user_objects

  2  /

 

OBJECT_NAME     OBJECT_TYPE

--------------- --------------------

MV1             TABLE

MV1             MATERIALIZED VIEW

 

SQL>

 

Oracle does not allow you to drop the table as this would stop the materialized view working:

 

SQL> drop table mv1

  2  /

drop table mv1

           *

ERROR at line 1:

ORA-12083: must use DROP MATERIALIZED VIEW to drop

"ANDREW"."MV1"

 

SQL>

 

Conversely, if you drop a materialized view, the associated table disappears too:

 

SQL> drop materialized view mv1

  2  /

 

Materialized view dropped.

 

SQL> select object_name, object_type from user_objects

  2  /

 

no rows selected

 

SQL>