Wednesday, June 12, 2013

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>

Sunday, June 09, 2013

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>

Thursday, June 06, 2013

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 >

Tuesday, June 04, 2013

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>

Sunday, June 02, 2013

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>