ORA-29339

I discovered a new error today. I was using Data Pump to copy a database from Sun SPARC to X86 and from Oracle 11.1.0.6.0 to 11.2.0.4.0 for testing purposes. I extracted the tablespace creation SQL from the old database like this. (This is only some of it.):

 

SQL> set lines 32000

SQL> set pages 50

SQL> exec dbms_metadata.set_transform_param(-

> DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

 

PL/SQL procedure successfully completed.

 

SQL> select dbms_metadata.get_ddl(-

> 'TABLESPACE','PINDAR_LARGE_32KBLOCKS')

  2  from dual

  3  /

 

DBMS_METADATA.GET_DDL('TABLESPACE','PINDAR_LARGE_32KBLOCKS')

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

 

  CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE

  '/cisdpt/nbadpt1/nba_data/pindar_large_32kblocks_a.dbf' SIZE 8388608000,

  '/cisdpt/nbadpt1/nba_data/pindar_large_32kblocks_b.dbf' SIZE 10485760000,

  '/cisdpt/nbadpt1/nba_data/pindar_large_32k_blocks_c.dbf' SIZE 15728640000

  LOGGING ONLINE PERMANENT BLOCKSIZE 32768

  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 209715200 SEGMENT SPACE MANAGEMENT AUTO;

 

SQL>

 

I did a global replace to change part of the file name to bring it in line with the file system naming on the X86 server. Then I ran the SQL to create the tablespace in the new database but it failed:

 

SQL>   CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE

  2    '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_a.dbf' SIZE 8388608000,

  3    '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_b.dbf' SIZE 10485760000,

  4    '/database/GBNBAPF1/nba_data/pindar_large_32k_blocks_c.dbf' SIZE 15728640000

  5    LOGGING ONLINE PERMANENT BLOCKSIZE 32768

  6    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 209715200 SEGMENT SPACE MANAGEMENT AUTO;

  CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE

*

ERROR at line 1:

ORA-29339: tablespace block size 32768 does not match configured block sizes

 

SQL>

 

The db_block_size was 8192 in both the old and new databases but the old one had the following extra line in its parameter file:

 

db_32k_cache_size=12m

 

This is to set up a cache for tablespaces with a 32K block size. I added this line to the parameter file for the new database and bounced it. Then I was able to create the tablespace there successfully:

 

SQL> CREATE TABLESPACE "PINDAR_LARGE_32KBLOCKS" DATAFILE

  2  '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_a.dbf' SIZE 8388608000,

  3  '/database/GBNBAPF1/nba_data/pindar_large_32kblocks_b.dbf' SIZE 10485760000,

  4  '/database/GBNBAPF1/nba_data/pindar_large_32k_blocks_c.dbf' SIZE 15728640000

  5  LOGGING ONLINE PERMANENT BLOCKSIZE 32768

  6  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 209715200 SEGMENT SPACE MANAGEMENT AUTO;

 

Tablespace created.

 

SQL>

How to Diagnose a Locking Issue

A user ran an update statement and noticed that it did not seem to be doing anything:

 

SQL> conn gordon/bennett

Connected.

SQL> update andrew.emp

  2  set ename = 'COLIN'

  3  where ename = 'BRIAN'

  4  /

 

I looked up his SID in V$SESSION:

 

SQL> select sid from v$session

  2  where username = 'GORDON'

  3  /

 

       SID

----------

      393

 

SQL>

I ran the following SQL, waited 10 seconds and ran it again:

 

SQL> l

  1  select event, time_waited/100

  2  from v$session_event

  3  where sid = 393

  4  and wait_class != 'Idle'

  5* order by 2 desc

SQL> /

 

EVENT                          TIME_WAITED/100

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

enq: TX - row lock contention           249.58

Disk file operations I/O                   .01

SQL*Net message to client                    0

 

SQL> /

 

EVENT                          TIME_WAITED/100

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

enq: TX - row lock contention            258.6

Disk file operations I/O                   .01

SQL*Net message to client                    0

 

SQL> 

This showed me that there was an ongoing locking issue with this user. I looked up the SID of the blocking session like this:

 

SQL> l

  1  select blocking_session from v$session

  2* where sid = 393

SQL> /

 

BLOCKING_SESSION

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

             101

 

SQL>

 

I saw that it belonged to Fred:

 

SQL> l

  1  select username from v$session

  2* where sid = 101

SQL> /

 

USERNAME

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

FRED

 

SQL>

I found some SQL on the Internet and hoped that it would tell me what Fred was doing but it did not work:

 

SQL> l

  1  select b.sql_text

  2  from v$session a, v$sqlarea b

  3  where a.sql_address = b.address

  4* and a.sid = 101

SQL> /

 

no rows selected

 

SQL>

 

I wondered if Fred had run his SQL but not commited it and changed my SQL as follows:

 

SQL> l

  1  select b.sql_text

  2  from v$session a, v$sqlarea b

  3  where a.prev_sql_addr = b.address

  4* and a.sid = 101

SQL> /

 

SQL_TEXT

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

update andrew.emp set enum = 2 where enum = 1

 

SQL> 

Then I looked in the EMP table and saw that both Fred and Gordon were trying to update the same row:

 

SQL> select * from andrew.emp

  2  /

 

ENUM  ENAME

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

1     BRIAN

 

SQL>

 

I asked Fred to COMMIT his UPDATE statement:

 

SQL> update andrew.emp

  2  set enum = 2

  3  where enum = 1

  4  /

 

1 row updated.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

This allowed Gordon’s UPDATE to finish:

 

SQL> conn gordon/bennett

Connected.

SQL> update andrew.emp

  2  set ename = 'COLIN'

  3  where ename = 'BRIAN'

  4  /

 

1 row updated.

 

SQL>

Moving a Table Deletes its Statistics

Statistics are important as they help the optimizer to work out the execution plan for a SQL statement. If you move a table, this deletes its statistics so you need to analyze it again afterwards. You can see this in the example below. First I created a table:

SQL> create table object_list

  2  as select * from dba_objects

  3  /

Table created.

SQL>

When you create a table it has no statistics so the num_rows column is null:

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------

NULL

                                                  

SQL>

When you calculate statistics, the num_rows column is updated:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

----------------------------------------
7932

SQL>

Moving the table deletes the statistics so num_rows is null afterwards:

SQL> alter table object_list move

  2  /

Table altered.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

---------------------------------------- NULL                                       

SQL>

To reinstate the statistics, simply analyze the table again:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------
7932

SQL>

ORA-01442

This was tested on Oracle 11.2. If you define a column as NOT NULL then try to modify it to NOT NULL again, you get an ORA-01442:

SQL> create table tab1 (col1 number not null)

  2  /

 

Table created.

 

SQL> alter table tab1 modify (col1 number not null)

  2  /

alter table tab1 modify (col1 number not null)

                         *

ERROR at line 1:

ORA-01442: column to be modified to NOT NULL is

already NOT NULL

 

SQL>

INVALID Materialized View with COMPILATION_ERROR

In this example, Andrew grants access on a table to a role then grants this role to John. John creates a materialized view on Andrew’s table. Andrew updates his table, making John’s materialized view INVALID. John then tries to compile his materialized view, which gives it a COMPILE_STATE of COMPILATION_ERROR. Andrew then grants access to his table directly to John and the problem goes away. It was tested on Oracle 11.2:

 

First, user Andrew creates a table with one row of data: 

SQL> conn andrew/reid

Connected.

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

 

SQL>

Then he creates a role and grants access on the table to it:

 

SQL> create role role1

  2  /

 

Role created.

 

SQL> grant select on tab1 to role1

  2  /

 

Grant succeeded.

 

SQL> 

A new user called John is set up. He has access to Andrew’s table via ROLE1 and he creates an ON DEMAND materialized view on it:

 

SQL> conn / as sysdba

Connected.

SQL> create user john identified by smith

  2  default tablespace users

  3  quota unlimited on users

  4  /

 

User created.

 

SQL> grant create session,

  2        create table,

  3        create materialized view,

  4        role1

  5        to john

  6  /

 

Grant succeeded.

 

SQL> conn john/smith

Connected.

SQL> create materialized view mv1

  2  refresh on demand

  3  as select * from andrew.tab1

  4  /

 

Materialized view created.

 

SQL> select * from mv1

  2  /

 

      COL1

----------

         1

 

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

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

TABLE               VALID

MATERIALIZED VIEW   VALID

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

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

MV1                            VALID

 

SQL>

 

Andrew updates his table:

SQL> conn andrew/reid

Connected.

SQL> update tab1 set col1 = 2

  2  /

 

1 row updated.

 

SQL>

This makes John’s materialized view INVALID and gives it a COMPILE_STATE of NEEDS_COMPILE. I showed you this in an earlier example:

 

SQL> conn john/smith

Connected.

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

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

TABLE               VALID

MATERIALIZED VIEW   INVALID

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

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

MV1                            NEEDS_COMPILE

 

SQL> select * from mv1

  2  /

 

      COL1

----------

         1

 

SQL>

Johns spots this and tries to compile his materialized view but this time, it remains INVALID:

SQL> alter materialized view mv1 compile

  2  /

 

Materialized view altered.

 

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

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

TABLE               VALID

MATERIALIZED VIEW   INVALID

 

SQL>

… and it now has a COMPILE_STATE of COMPILATION_ERROR:

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

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

MV1                            COMPILATION_ERROR

 

SQL> select * from mv1

  2  /

 

      COL1

----------

         1

 

SQL>

Even refreshing the materialized view does not make the problem go away:

 

SQL> exec dbms_mview.refresh('MV1');

 

PL/SQL procedure successfully completed.

 

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

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

TABLE               VALID

MATERIALIZED VIEW   INVALID

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

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

MV1                            COMPILATION_ERROR

 

SQL>

Although it does bring the data up to date:

SQL> select * from mv1

  2  /

 

      COL1

----------

         2

 

SQL>

Andrew grants SELECT access directly to John:

 

SQL> conn andrew/reid

Connected.

SQL> grant select on tab1 to john

  2  /

 

Grant succeeded.

 

SQL>

… and when John tries to compile the materialized view again, the problem disappears:

 

SQL> conn john/smith

Connected.

SQL> alter materialized view mv1 compile

  2  /

 

Materialized view altered.

 

SQL> select object_type, status

  2  from user_objects

  3  where object_name = 'MV1'

  4  /

 

OBJECT_TYPE         STATUS

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

TABLE               VALID

MATERIALIZED VIEW   VALID

 

SQL> select mview_name, compile_state

  2  from user_mviews

  3  /

 

MVIEW_NAME                     COMPILE_STATE

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

MV1                            VALID

 

SQL> select * from mv1

  2  /

 

      COL1

----------

         2

 

SQL>