Sunday, March 30, 2014

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>

Friday, March 28, 2014

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>

Sunday, March 23, 2014

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>

Thursday, March 20, 2014

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>

Tuesday, March 18, 2014

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>