How to Allow a User to Refresh Another User's Materialized View

I needed to find out how to allow a user to refresh another user’s materialized view. Once I had done this I decided to document it for future reference with a worked example, which I ran on an Oracle 11.2.0.2.7 database. First I created user A, who will own the materialized view:

 

SQL> create user a identified by a

  2  default tablespace users

  3  quota unlimited on users

  4  /

 

User created.

 

SQL> grant

  2  create session,

  3  create materialized view,

  4  create procedure,

  5  create table,

  6  select any dictionary to a

  7  /

 

Grant succeeded.

 

SQL>

 

I logged in as user A, created a table then created a materialized view on that table:

 

SQL> conn a/a

Connected.

SQL> create table tab1

  2  as select * from dba_tables

  3  /

 

Table created.

 

SQL> create materialized view mv1

  2  as select * from tab1

  3  /

 

Materialized view created.

 

SQL>

 

I showed that user A could refresh the materialized view as he owned it:

 

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

 

PL/SQL procedure successfully completed.

 

SQL>

 

I created user B, logged in as this user and tried to refresh the materialized view again. This failed as user B did not own the materialized view:

 

SQL> conn / as sysdba

Connected.

SQL> create user b identified by b

  2  /

 

User created.

 

SQL> grant create session to b

  2  /

 

Grant succeeded.

 

SQL> conn b/b

Connected.

SQL> exec dbms_mview.refresh('a.mv1');

BEGIN dbms_mview.refresh('a.mv1'); END;

 

*

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748

ORA-06512: at line 1

 

SQL>

 

I granted alter any materialized view to user B and this allowed him to refresh user A’s materialized view:

 

SQL> conn / as sysdba

Connected.

SQL> grant alter any materialized view to b

  2  /

 

Grant succeeded.

 

SQL> conn b/b

Connected.

SQL> exec dbms_mview.refresh('a.mv1');

 

PL/SQL procedure successfully completed.

 

SQL>

 

I did not like this approach as it allowed user B to refresh any materialized view so I revoked the privilege:

 

SQL> conn / as sysdba

Connected.

SQL> revoke alter any materialized view from b

  2  /

 

Revoke succeeded.

 

SQL>

 

I reconnected as user A, created a procedure to refresh the materialized view then allowed user B to execute it:

 

SQL> conn a/a

Connected.

SQL> create or replace procedure pr1 as

  2  begin

  3  dbms_mview.refresh('a.mv1');

  4  end;

  5  /

 

Procedure created.

 

SQL> grant execute on pr1 to b

  2  /

 

Grant succeeded.

 

SQL>

 

Finally, I logged in as user B and was able to refresh the materialized view:

 

SQL> conn b/b

Connected.

SQL> exec a.pr1;

 

PL/SQL procedure successfully completed.

 

SQL>

Another Possible Cause of ORA-12537

I have changed the names of the database and server in this example.

I had a new Oracle 11.1 database, which I was trying to access remotely using an Oracle 11.2 listener:

 

Solaris > sqlplus /@XXXXXX

 

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 6 18:25:48 2012

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

ERROR:

ORA-12537: TNS:connection closed

 

The following errors appeared in the listener log:

 

<msg time='2012-11-06T18:25:48.133+00:00' org_id='oracle' comp_id='tnslsnr'

type='UNKNOWN' level='16' host_id='xxx-xxxxxx-xxxx'

host_addr='99.99.9.999'>

<txt>TNS-12518: TNS:listener could not hand off client connection

TNS-12547: TNS:lost contact

  TNS-12560: TNS:protocol adapter error

   TNS-00517: Lost contact

    Solaris Error: 32: Broken pipe

</txt>

</msg>

 

There is more than one possible reason for this. On this occasion the entry for the new database in the listener parameter file was as follows:

 

(SID_DESC =

 (SID_NAME = XXXXXX)

 (ORACLE_HOME = /oracle/app/oracle/product/11.1.0)

)

 

I added an extra line like this:

 

(SID_DESC =

  (SID_NAME = XXXXXX)

  (ORACLE_HOME = /oracle/app/oracle/product/11.1.0)

  (ENVS="LD_LIBRARY_PATH=/oracle/app/oracle/product/11.1.0/lib")

)

 

I restarted the listener:

 

Solaris > lsnrctl reload listener_11

 

LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 06-NOV-2012 18:40:10

 

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx-xxxxxx-xxxx)(PORT=1529)))

The command completed successfully

Solaris >

 

Then I was able to connect to the database remotely:

 

Solaris > sqlplus /@XXXXXX

 

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 6 18:42:27 2012

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL>

How to Rename an Oracle Tablespace

In Oracle 9 you got an error if you tried to rename a tablespace: 

SQL> l

  1* alter tablespace users rename to andrew

SQL> /

alter tablespace users rename to andrew

                              *

ERROR at line 1:

ORA-01904: DATAFILE keyword expected

 

SQL>

… but from Oracle 10 onwards you can rename tablespaces. I decided to demonstrate this by renaming the USERS tablespace to ANDREW. First I checked that there was a tablespace called USERS but no tablespace called ANDREW:

SQL> select tablespace_name from dba_tablespaces
  2  where tablespace_name in ('ANDREW', 'USERS')
  3  /

TABLESPACE_NAME
------------------------------
USERS

 
SQL> 

Then I showed that there was a table called CARS in the USERS tablespace: 

SQL> select tablespace_name from dba_tables
  2  where table_name = 'CARS'
  3  /

TABLESPACE_NAME
------------------------------
USERS

SQL>

I renamed the USERS tablespace and called it ANDREW instead:

SQL> alter tablespace users rename to andrew
  2  /

Tablespace altered.

SQL>

After that I showed that there was a tablespace called ANDREW but no tablespace called USERS:

SQL> select tablespace_name from dba_tablespaces
  2  where tablespace_name in ('ANDREW', 'USERS')
  3  /

TABLESPACE_NAME
------------------------------
ANDREW

SQL>

Then I checked that the CARS table was in the ANDREW tablespace:

SQL> select tablespace_name from dba_tables
  2  where table_name = 'CARS'
  3  /

TABLESPACE_NAME
------------------------------
ANDREW

SQL>

I took the ANDREW tablespace offline then tried to rename it back to USERS but found that you cannot rename a tablespace if it is offline:

SQL> alter tablespace andrew offline
  2  /

Tablespace altered.

SQL> alter tablespace andrew rename to users
  2  /
alter tablespace andrew rename to users
*
ERROR at line 1:
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: 'C:\DOCUMENTS AND SETTINGS\ANDREW\MY
DOCUMENTS\ADHOC_DATABASE\ADHOC\USERS01.DBF'

SQL> 

Next I confirmed that you cannot rename the SYSTEM or SYSAUX tablespaces:

SQL> alter tablespace system rename to fred
  2  /
alter tablespace system rename to fred
*
ERROR at line 1:
ORA-00712: cannot rename system tablespace

SQL> alter tablespace sysaux rename to john
  2  /
alter tablespace sysaux rename to john
*
ERROR at line 1:
ORA-13502: Cannot rename SYSAUX tablespace

SQL> 

Finally, if you try to give your tablespace an invalid name, you get an ORA-02150: 

SQL> alter tablespace users rename to 123;

alter tablespace users rename to 123

                                 *

ERROR at line 1:

ORA-02150: invalid new tablespace name

 

SQL> 

 

So, now my problem is - how can I get an ORA-00711? 

 

Solaris > oerr ora 00711

00711, 00000, "new tablespace name is invalid"

// *Cause:  An attempt to rename a tablespace failed because the new name

//          is invalid.

// *Action: Choose a valid new name and retry the command.

Solaris >

ORA-01623, ORA-01624 and ORA-01567

This was tested on an Oracle 11.2.0.2.7 database. The output below shows that this database had 3 online redo log groups. You can only drop a redo log group if its STATUS is INACTIVE so when I tried to drop the CURRENT redo log group, I got an error:

 

SQL> l

  1* select group#, status from v$log

SQL> /

 

    GROUP# STATUS

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

         1 INACTIVE

         2 CURRENT

         3 INACTIVE

 

SQL> l

  1* alter database drop logfile group 2

SQL> /

alter database drop logfile group 2

*

ERROR at line 1:

ORA-01623: log 2 is current log for instance ORCL

(thread 1) - cannot drop

ORA-00312: online log 2 thread 1:

'/oracle/app/oracle/product/11.2.0.2.7/software_downloa

d/ORCL/redo02.log'

 

SQL>

 

I switched to a new redo log group so the previous CURRENT redo log group changed to ACTIVE but I still could not drop it:

 

SQL> alter system switch logfile

  2  /

 

System altered.

 

SQL> select group#, status from v$log

  2  /

 

    GROUP# STATUS

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

         1 INACTIVE

         2 ACTIVE

         3 CURRENT

 

SQL> alter database drop logfile group 2

  2  /

alter database drop logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance

ORCL (thread 1)

ORA-00312: online log 2 thread 1:

'/oracle/app/oracle/product/11.2.0.2.7/software_downloa

d/ORCL/redo02.log'

 

SQL>

 

Eventually it changed to INACTIVE then I was able to drop it:

 

SQL> select group#, status from v$log

  2  /

 

    GROUP# STATUS

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

         1 INACTIVE

         2 INACTIVE

         3 CURRENT

 

SQL> alter database drop logfile group 2

  2  /

 

Database altered.

 

SQL> select group#, status from v$log

  2  /

 

    GROUP# STATUS

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

         1 INACTIVE

         3 CURRENT

 

SQL>

 

You need a minimum of 2 online redo log groups at all times so that one can be archived while the other is in use. When I tried to drop another redo log group, I got a different error again:

 

SQL> select group#, status from v$log

  2  /

 

    GROUP# STATUS

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

         1 INACTIVE

         3 CURRENT

 

SQL> alter database drop logfile group 1

  2  /

alter database drop logfile group 1

*

ERROR at line 1:

ORA-01567: dropping log 1 would leave less than 2 log

files for instance ORCL (thread 1)

ORA-00312: online log 1 thread 1:

'/oracle/app/oracle/product/11.2.0.2.7/software_downloa

d/ORCL/redo01.log'

 

SQL>

Reads and Writes Figures from V$FILESTAT

You can get the number of reads and writes your database has done on each datafile since the instance was started from V$FILESTAT. I have looked at the number of writes (PHYWRTS) before but I do not believe I have ever looked at the number of reads (PHYRDS). The figures below came from an Oracle 9 database: 

SQL> desc v$filestat

Name                       Null?    Type

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

FILE#                               NUMBER

PHYRDS                              NUMBER

PHYWRTS                             NUMBER

PHYBLKRD                            NUMBER

PHYBLKWRT                           NUMBER

SINGLEBLKRDS                        NUMBER

READTIM                             NUMBER

WRITETIM                            NUMBER

SINGLEBLKRDTIM                      NUMBER

AVGIOTIM                            NUMBER

LSTIOTIM                            NUMBER

MINIOTIM                            NUMBER

MAXIORTM                            NUMBER

MAXIOWTM                            NUMBER

 

SQL> l

  1  select sum(phyrds), sum(phywrts)

  2* from v$filestat

SQL> /

 

SUM(PHYRDS) SUM(PHYWRTS)

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

    4604424       463172

 

SQL>