Oracle Materialized View Notes

Check all the mviews:

SELECT * FROM all_mviews;

Refresh an mview:

execute dbms_mview.refresh('my_cool_mview','f');

Oracle Troubleshooting: ORA-01114, ORA-01110, ORA-27091, ORA-27041

During troubleshooting on an issue in Oracle DB I was getting these errors:

sqlplus / as sysdba

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size      2233344 bytes
Variable Size    809503744 bytes
Database Buffers   251658240 bytes
Redo Buffers      5541888 bytes
Database mounted.
ORA-01114: IO error writing block to file 5 (block # 1)
ORA-01110: data file 5: '/data/oracle/bb/bb1.dbf'
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3

I changed the permissions to data file and problem solved:

sudo chmod 660 /data/oracle/bb/bb1.dbf

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 16 17:59:36 2015

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size      2233344 bytes
Variable Size    809503744 bytes
Database Buffers   251658240 bytes
Redo Buffers      5541888 bytes
Database mounted.
Database opened.

Oracle: Search text in name of tables and columns

Just a couple of handy Oracle SQL sentences to search for a specific string contained in the name of tables and columns. I found them useful when working in maintenance for legacy systems, or big projects where you didn't start from the beginning.

To search inside name of tables, indexes, etc:

SELECT * 
FROM dba_objects 
WHERE object_name LIKE '%STRING%';

To search inside name of columns:

SELECT owner, table_name, column_name 
FROM all_tab_columns 
WHERE column_name LIKE '%COL_STRING%';

Oracle: ORA-12519, TNS: no appropriate service handler found

This error can have various root causes, but in my particular case it was bothering me while trying to open new connections because apparently I had exceeded the maximum amount of processes and/or allowed sessions.

Fortunately, this value can be modified by running a simple SQL sentence (Oracle needs to be restarted after):

alter system set processes=150 scope=spfile;

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 27 14:46:58 2013

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


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select * from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
INITIAL_ALLOCATION
----------------------------------------
LIMIT_VALUE
----------------------------------------
processes                                       38              40
        40
        40

sessions                                        42              49
        49
        49

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
INITIAL_ALLOCATION
----------------------------------------
LIMIT_VALUE
----------------------------------------


SQL> alter system set processes=150 scope=spfile;

System altered.

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
gabo@gabo-Precision-M6600:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server$ sudo /etc/init.d/oracle-xe restart
Shutting down Oracle Database 10g Express Edition Instance.
Stopping Oracle Net Listener.

Starting Oracle Net Listener.
Starting Oracle Database 10g Express Edition Instance.

Function-based indexes used as constraints

Let's suppose we want to avoid duplicity in the values of a column produced by a bad checking in a formulary of a program, that ends up allowing to add existing values, with the only difference that the user adds leading/trailing blank spaces, or changes one letter to upper/lower case.

A function based index does not only permits to speed up queries by expresion. I recommend this video for a brief explanation:

A function based index can also be used as a type of constraint. Given the example at the beggining, we could create an index of type "UNIQUE" to prevent duplicity cleaning the value to be inserted and checking its uniqueness.

create unique index INDEX_NAME on TABLE (lower(trim(COL_1)));

Once the index is added to the table, any insertion with a duplicate value (with blank spaces or lower/upper case changed) will throw the follow exception:

SQL Error: ORA-00001: unique constraint (TABLE.INDEX_NAME) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.