After DML on the Master Table(s) of Local Materialized View, USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE' and USER_OBJECTS.STATUS becomes 'INVALID' (Doc ID 264036.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later

Information in this document applies to any platform.

SYMPTOMS

After executing a DML statement on base table(s) of MV that is on the same database as its master table(s) (i.e. a local MV), USER_OBJECTS.STATUS becomes INVALID for the MV.  Also, USER_MVIEWS.COMPILE_STATE shows status "NEEDS_COMPILE'.

Example :

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

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

EMP_MV_PK                      MATERIALIZED VIEW  VALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

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

EMP_MV_PK                      VALID

SQL> update emp set empno = empno where empno = 605;

SQL> commit;

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

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

EMP_MV_PK                      MATERIALIZED VIEW  INVALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                      COMPILE_STATE

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

EMP_MV_PK                       NEEDS_COMPILE

CHANGES

DML was executed and committed on master table of the MV since the MV was last refreshed.

CAUSE

This is expected behavior. Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

SOLUTION

Though the status is INVALID, the MV can still be queried.  However, the query on MV will not return the latest data in master table unless the MV is refreshed.

Example :

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

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

EMP_MV_PK                      MATERIALIZED VIEW  INVALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

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

EMP_MV_PK                      NEEDS_COMPILE

SQL> select count(*) from emp_mv_pk;

Output :

COUNT(*)

----------

2686976

- On next refresh, the status becomes VALID.

Example :

SQL> execute dbms_mview.refresh('emp_mv_pk');

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

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

EMP_MV_PK                      MATERIALIZED VIEW  VALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

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

EMP_MV_PK                      VALID

"REFRESH ON COMMIT" MVs are the exception to this behavior because they are refreshed at the same time of DML.

Using Regular Expression - REGEXP_SUBSTR example

SELECT REGEXP_SUBSTR('(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl-orcl2.myhost.ca)(PORT=41295))(CONNECT_DATA=(SID=ORCL2)))', '[(]HOST[=][^()]+[)]',1,1,'i') RESULT
FROM dual;


Result:

(HOST=orcl-orcl2.myhost.ca)

Find All Non-Numeric Values in a Column

Return All Non-Numeric Values:

SELECT uen

FROM scott.dept 

WHERE NOT REGEXP_LIKE(uen, '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$');

Return Non-Integers:

SELECT c1 FROM t1 WHERE NOT REGEXP_LIKE(c1, '^[0-9]+$');

WM_CONCAT function - How to aggregate data from a number of rows into a single row

Term: WM_CONCAT

Definition:
The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output asWM_CONCAT is both documented and supported by Oracle.


Example Usage:

CREATE TABLE test_wm (

column_1 VARCHAR2(5),

column_2 VARCHAR2(20));



INSERT INTO test_wm VALUES (111, 'This');

INSERT INTO test_wm VALUES (111, 'is');

INSERT INTO test_wm VALUES (111, 'a');

INSERT INTO test_wm VALUES (111, 'test');

INSERT INTO test_wm VALUES (222, 'This is not');



SELECT * FROM test_wm;



col concat format a40



SELECT column_1, wmsys.wm_concat(column_2) CONCAT

FROM test_wm

GROUP BY column_1;



SELECT column_1, TRANSLATE(wmsys.wm_concat(column_2), 'A,', 'A ') CONCAT

FROM test_wm

GROUP BY column_1;

Speeding Up Queries with Semi-Joins and Anti-Joins: How Oracle Evaluates EXISTS, NOT EXISTS, IN, and NOT IN

by Roger Schrag
Database Specialists, Inc.

From http://www.dbspecialists.com/files/presentations/semijoins.html

Introduction
Optimizing SQL usually gives the most significant results when DBAs are called upon to “make the system run faster.” Using tools like Statspack or Enterprise Manager, it is often easy to find the slow SQL. But how do you make the queries run faster? That is the challenge! In this paper we will discuss the semi-join and the anti-join, two powerful SQL constructs Oracle offers for use in your quest for faster queries. In particular, we will define these two terms, discuss when and why you might want to use the [NOT] EXISTS or [NOT] IN constructs, and demonstrate how you can use optimizer hints and make minor query changes in order to enable Oracle to use some very powerful and efficient access paths.

How to move tempfile to another location online!

alter tablespace temp add tempfile '/u01/app/oracle/oradata/KNK/temp02.dbf' size 1024m autoextend on;

alter tablespace temp drop tempfile '/u01/app/oracle/oradata/KNK/temp01.dbf';


Check tempfiles from dba_temp_files.


If you still see the dropped tempfile, check whether any sessions running, which are probably use TEMP tablespace.


After you kill the relevant sessions (if you need to), check dba_temp_files again.


Check if file still exists in OS. If yes then drop it!

How to import data into another tablespace?

If you want to import data from tablespace X to tablespace Y write the following in SQL*Plus : 

 

SQL>REVOKE UNLIMITED TABLESPACE FROM alex; 

Where to find all definitions of ORACLE_HOME etc. in Linux?

When you install Oracle products on Linux, Oracle Universal Installer retreive all the ORACLE_HOME etc. definitions automatically. HOW?

All this information you can find and edit in$ORACLE_BASE/oraInventory/ContentsXML/inventory.xml file.

How to change LOB storage?

SQL>ALTER TABLE xtable

MOVE LOB ( myblob ) STORE AS ( STORAGE (MAXEXTENTS 505 ));

How do I run the command-line interpreter?

Set two variables in your environment then run ’sqlplus’ with a single slash as the only argument.

% export ORACLE_HOME=/opt/oracle-9.2
% export TWO_TASK=acisora1
% sqlplus /

What does "Message file sp1.msb not found" mean? What does "Error 6 initializing SQL" mean?

The sqlplus binary doesn’t know where to find it’s messages file. You need to:

% export ORACLE_HOME=/opt/oracle-9.2

What's the TWO_TASK?

The TWO_TASK is the name of the database server you want to connect to. Oracle binaries lookup it’s value in the file $ORACLE_HOME/network/admin/tnsnames.ora to determine the hostname and port of the database server.
The name ‘TWO_TASK’ is historical and it isn’t clear what it refers to.

The equivalent of TWO_TASK variable on Windows is LOCAL.

set LOCAL=service_name 

What's this sqlnet.log file in my home directory?

Under some conditions a failed connection attempt will generate a ’sqlnet.log’ file giving details pertaining to the failure. Usually the error messages printed to STDOUT will be sufficient to resolve any problems. You can safely delete the sqlnet.log file.