EXP-00037: EXPORT VIEW NOT COMPATIBLE

Cause: Incompatibility

 

Action:

1. Export the data with the Export utility of the lowest database version involved.
2. Import the data with the Import utility of the target database.

Posted by No comments : Labels:

Oracle Materialized Views

Definitions

Materialized View

A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.

 

Materialized View Log

When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.

 

Privileges required

create materialized view
create any materialized view
drop any materialized view
delete any table
insert any table
lock any table
select any table
under any table
update any table
create table
create view

 

Syntax (Fast Refresh)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
BUILD IMMEDIATE
REFRESH <FAST | FORCE> ON <COMMIT | DEMAND>
<USING INDEX | USING NO INDEX>
INITRANS <integer>
STORAGE CLAUSE
AS (<SQL statement>);

 

Example:

CREATE MATERIALIZED VIEW mv_simple
TABLESPACE uwdata
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT * FROM servers;

 

Syntax (Force Refresh)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
BUILD IMMEDIATE
REFRESH <FAST | FORCE> ON <COMMIT | DEMAND> 
AS (<SQL statement>);

 

Example:

CREATE MATERIALIZED VIEW mv_force
TABLESPACE uwdata
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH ROWID AS
SELECT * FROM servers;

 

Syntax (Complete Refresh)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
REFRESH <COMPLETE | FORCE>
START WITH <date>
NEXT <date_calculation>
[FOR UPDATE]
AS (<SQL statement>);

 

Example:

CREATE MATERIALIZED VIEW mv_complete
TABLESPACE uwdata
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id, i.installstatus;

 

Syntax (Complete Refresh Using Index)

CREATE MATERIALIZED VIEW <schema.name>
[LOGGING] [CACHE]
PCTFREE <integer>
PCTUSED <integer>
USING INDEX
TABLESPACE <tablespace_name>
REFRESH <COMPLETE | FORCE>
START WITH <date>
NEXT <date_calculation>
[FOR UPDATE]
AS (<SQL statement>);

 

Example:

CREATE SNAPSHOT mv_w_index
LOGGING CACHE
PCTFREE 0 PCTUSED 99
TABLESPACE uwdata
REFRESH COMPLETE
AS SELECT s.srvr_id, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id;

 

Syntax (Prebuilt Table)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
REFRESH <COMPLETE | FORCE>
START WITH <date>
NEXT <date_calculation>
[FOR UPDATE]
AS (<SQL statement>);

 

Example:

CREATE TABLE mv_prebuilt (
month VARCHAR2(8),
state VARCHAR2(40),
sales NUMBER(10,2));

CREATE MATERIALIZED VIEW mv_prebuilt
ON PREBUILT TABLE WITH REDUCED PRECISION
AS SELECT t.calendar_month_desc AS month,
c.cust_state_province AS state,
SUM(s.amount_sold) AS sales
FROM times t, customers c, sales s
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
GROUP BY t.calendar_month_desc, c.cust_state_province;

 

Syntax (Enable Query Rewrite)

CREATE MATERIALIZED VIEW <schema.name>
PCTFREE <integer>
PCTUSED <integer>
TABLESPACE <tablespace_name>
REFRESH <COMPLETE | FORCE>
START WITH <date>
NEXT <date_calculation>
[FOR UPDATE]
AS (<SQL statement>);

 

Example:

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%rewrite%';

EXPLAIN PLAN FOR
SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
AND s.srvr_id = 502
GROUP BY s.srvr_id, i.installstatus;

SELECT * FROM TABLE(dbms_xplan.display);

CREATE MATERIALIZED VIEW mv_rewrite
TABLESPACE uwdata
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id, i.installstatus;

EXPLAIN PLAN FOR
SELECT s.srvr_id, i.installstatus, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
AND s.srvr_id = 502
GROUP BY s.srvr_id, i.installstatus;

SELECT * FROM TABLE(dbms_xplan.display);

-- if the base table may be updated then
ALTER SESSION SET query_rewrite_integrity = STALE_TOLERATED;

 

For more information click this link

 

Posted by 8 comments : Labels:

Selecting Nth MAX or MIN

This query has become the mostly asked question in any Oracle technical Interview. Following is an excerpt tip which comes from Ramcharan Karthic, software engineer in Bangalore, India.

 

Note: This tip was published in the January/February 2003 issue of Oracle Magazine.

 

This Select query will help you select the Nth Max or Min value from any table.

 

For example, consider a table TAB1 in which you want to find the Nth Max or Min from the column, COL1.

 

First, the query for Max:

 

SELECT * FROM TAB1 a

WHERE &N = (SELECT count (DISTINCT (b.col1))

FROM TAB1 b WHERE a.col1<=b.col1)

 

Next, the query for Min:

 

SELECT * FROM TAB1 a

WHERE &N = (SELECT count (DISTINCT (b.col1))

FROM TAB1 b WHERE a.col1>=b.col1)

 

If N=1 will return first max or first min. N=2 will return second max or min.

Posted by 1 comment : Labels: , ,

Query to get free used and total space of each tablespace

The following query if run will fetch Free space, Used space and Total space of each tablespace available.

 

SELECT Total.name "Tablespace Name",

Free_space,

(total_space-Free_space) Used_space,

total_space

FROM

  (select tablespace_name, sum(bytes/1024/1024) Free_Space

  from sys.dba_free_space

  group by tablespace_name

  ) Free,

  (select b.name, sum(bytes/1024/1024) TOTAL_SPACE

  from sys.v_$datafile a, sys.v_$tablespace B

  where a.ts# = b.ts#

  group by b.name

  ) Total

WHERE Free.Tablespace_name = Total.name;

 

This tip comes from Lazydba

Posted by No comments : Labels: ,

ORA-00980: synonym translation is no longer valid

This tip comes from Vikash Varma, Oracle DBA at Intelligent Consulting, in New Jersey.

"ORA-00980: synonym translation is no longer valid" is a common error encountered in a development environment. This can happen for many reasons.

Some of them are:

  1. You created a synonym on non-existing object by mistake.
    • For example, you created a synonym on SCOTT.DEPT where either the SCOTT schema in not present or the DEPT table is missing.
  2. You dropped an object but you did not drop the synonyms referencing the object.
  3. You dropped a user, but you did not drop synonyms referencing the objects owned by that user.

When an object is dropped, synonyms referring to the object are not dropped. The following script lists all such invalid synonyms:

select * from dba_synonyms s

where table_owner not in('SYSTEM','SYS')

and db_link is null

and not exists

(select 1

from dba_objects o

where s.table_owner=o.owner

and s.table_name=o.object_name);

The following script generates DDL to drop synonyms whose translation is no longer valid. USE WITH CAUTION. 

 
rem 
rem  Exludes SYS and SYSTEM users
rem
select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',
'SYNONYM'||s.owner||'.')||s.synonym_name||';'
from dba_synonyms  s
where table_owner not in('SYSTEM','SYS')
and db_link is null
and not exists
     (select  1
      from dba_objects o
      where s.table_owner=o.owner
      and s.table_name=o.object_name)
/

Read more, a mini Tip on Synonyms
Posted by 11 comments : Labels: ,

Dynamic Ref Cursor with Dynamic Fetch

This tip comes from Zlatko Sirotic, Software Developer at Istra Informaticki Inzenjering d.o.o., in Pula, Croatia.

Suppose you've got a function that is based on a dynamically generated query that returns a ref cursor variable. Now suppose you want to use this ref cursor variable in your procedure, but you don't know the record structure. So how do you make a "FETCH l_ref_cur INTO record_variable" when you don't know the record variable structure.

Because ref cursors do not (directly) support description, the solution is quite complicated and requires that the function (or package) returns not only a ref cursor variable but a (dynamically) generated query, too.

I am going to use "a good old" DBMS_SQL package and its PARSE and DESCRIBE_COLUMNS procedures in order to make an unknown record variable.

1. Make the "generic" package.

First I am going to make a "dyn_fetch" package in which the "describe_columns" procedure (using query recorded in a global "g_query" variable) creates a "g_desc_tab" PL/SQL table used by the "record_def" function for making a record structure: 

 
CREATE OR REPLACE PACKAGE dyn_fetch IS
   TYPE ref_cur_t IS REF CURSOR;
 
 
   g_query    VARCHAR2 (32000);
   g_count    NUMBER;
   g_desc_tab DBMS_SQL.DESC_TAB;
 
   varchar2_type CONSTANT PLS_INTEGER := 1;
   number_type   CONSTANT PLS_INTEGER := 2;
   date_type     CONSTANT PLS_INTEGER := 12;
   rowid_type    CONSTANT PLS_INTEGER := 11;
   char_type     CONSTANT PLS_INTEGER := 96;
 
   long_type     CONSTANT PLS_INTEGER := 8;
   raw_type      CONSTANT PLS_INTEGER := 23;
   mlslabel_type CONSTANT PLS_INTEGER := 106;
   clob_type     CONSTANT PLS_INTEGER := 112;
   blob_type     CONSTANT PLS_INTEGER := 113;
   bfile_type    CONSTANT PLS_INTEGER := 114;
 
   PROCEDURE describe_columns;
   FUNCTION record_def RETURN VARCHAR2;
END;
 
/
 
CREATE OR REPLACE PACKAGE BODY dyn_fetch IS
   PROCEDURE describe_columns IS
      l_cur INTEGER;
   BEGIN
      l_cur := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE            (l_cur, g_query, DBMS_SQL.NATIVE);
      DBMS_SQL.DESCRIBE_COLUMNS (l_cur, g_count, g_desc_tab);
      DBMS_SQL.CLOSE_CURSOR     (l_cur);
   EXCEPTION
 
      WHEN OTHERS THEN
         IF DBMS_SQL.IS_OPEN (l_cur) THEN
            DBMS_SQL.CLOSE_CURSOR (l_cur);
         END IF;
         RAISE;
   END;
 
   FUNCTION record_def RETURN VARCHAR2 IS
      l_record_def    VARCHAR2 (32000);
      l_type          VARCHAR2 (100);
      l_col_type      PLS_INTEGER;
      l_col_max_len   PLS_INTEGER;
 
      l_col_precision PLS_INTEGER;
      l_col_scale     PLS_INTEGER;
   BEGIN
      FOR i IN 1..g_count LOOP
         l_col_type      := g_desc_tab(i).col_type;
         l_col_max_len   := g_desc_tab(i).col_max_len;
         l_col_precision := g_desc_tab(i).col_precision;
         l_col_scale     := g_desc_tab(i).col_scale;
 
         IF    l_col_type = varchar2_type THEN
            l_type := 'VARCHAR2(' || l_col_max_len || ')';
         ELSIF l_col_type = number_type THEN
            l_type := 'NUMBER(' || l_col_precision || ',' || l_col_scale || ')';
         ELSIF l_col_type = date_type THEN
            l_type := 'DATE';
         ELSIF l_col_type = rowid_type THEN
            l_type := 'ROWID';
         ELSIF l_col_type = char_type THEN
            l_type := 'CHAR(' || l_col_max_len || ')';
      -- ELSIF  l_col_type = ...
            -- long_type, raw_type ...
         END IF;
 
         l_record_def := l_record_def || ' col_' || i || ' ' || l_type || ',';
      END LOOP;
 
      l_record_def := RTRIM (l_record_def, ',');
      RETURN l_record_def;
   END;
END;
/
 

Note that the RECORD_DEF procedure creates column names as col_1 (col_2, ...) because the SELECT clause in the query can be without aliases, for example, "SELECT deptno || dname FROM dept".

 

2. Create the package that returns the query and ref cursor.

 

The function that returns the ref cursor variable should return the query, too. So it's better to make two separate functions and put them into the package.

The "set_query" procedure saves the query into the global package variable and the ref cursor is returned by the "ref_cur" function: 

 
CREATE OR REPLACE PACKAGE test IS
 
   PROCEDURE set_query (p_query VARCHAR2 := NULL);
   FUNCTION ref_cur RETURN dyn_fetch.ref_cur_t;
END;
/
 
CREATE OR REPLACE PACKAGE BODY test IS
   PROCEDURE set_query (p_query VARCHAR2 := NULL) IS
      l_query VARCHAR2 (32000) :=
      '  SELECT e.empno, e.ename,'   ||
      '         e.deptno, d.dname'   ||
      '    FROM emp  e,'             ||
      '         dept d'              ||
      '   WHERE e.deptno = d.deptno';
   BEGIN
      IF p_query IS NULL THEN
         dyn_fetch.g_query := l_query;
      ELSE
 
         dyn_fetch.g_query := p_query;
      END IF;
   END;
 
   FUNCTION ref_cur RETURN dyn_fetch.ref_cur_t IS
      l_ref_cur dyn_fetch.ref_cur_t;
   BEGIN
      OPEN l_ref_cur FOR dyn_fetch.g_query;
      RETURN l_ref_cur;
   END;
END;
/
 

So why do I need two separate procedures (functions) in the package?

a) The receiving program must use dynamic SQL, but in the dynamic block I can access only PL/SQL code elements that have a global scope (standalone functions and procedures, and elements defined in the specification of a package). Unfortunately, cursor variables cannot be defined in the specification of a package (so they cannot be global variables).

b) The receiving program must get the column list before ref cursor.

So, there are two options:

a.) Call (in the receiving program) the same function two times (once to get the column list and once to return a ref cursor), or

b.) Use one procedure (or function) for returning query (to get the column list) and a second function for returning a ref cursor.

 

3. Create the receiving program.

 

Finally I create a procedure that reads the ref cursor. First, the procedure calls the "test.set_query" and "dyn_fetch.describe_columns" in order to get dynamically generated record structure through the "dyn_fetch.record_def" function and to get process definition through (internal) "process_def" function (in this case, to show rows with DBMS_SQL.PUT_LINE): 

 
CREATE OR REPLACE PROCEDURE test_fetch_ref_cur (p_query VARCHAR2 :=
NULL) IS
   l_statement VARCHAR2 (32000);
 
   FUNCTION process_def RETURN VARCHAR2 IS
 
      l_process_def VARCHAR2 (32000);
   BEGIN
      l_process_def := 'DBMS_OUTPUT.PUT_LINE (';
 
      FOR i IN 1 .. dyn_fetch.g_count LOOP
         l_process_def := l_process_def || ' l_record.col_' || i || ' || ''>>'' || ';
      END LOOP;
 
      l_process_def := RTRIM (l_process_def, ' || ''>>'' || ') || ');';
      RETURN l_process_def;
   END;
BEGIN
   test.set_query (p_query);
   dyn_fetch.describe_columns;
 
   l_statement :=
   '  DECLARE'                              ||
   '     TYPE record_t IS RECORD ('         ||
            dyn_fetch.record_def || ');'    ||
   '     l_record  record_t;'               ||
 
   '     l_ref_cur dyn_fetch.ref_cur_t;'    ||
   '  BEGIN'                                ||
   '     l_ref_cur := test.ref_cur;'        ||
   '     LOOP'                              ||
   '        FETCH l_ref_cur INTO l_record;' ||
   '        EXIT WHEN l_ref_cur%NOTFOUND;'  ||
            process_def                     ||
   '     END LOOP;'                         ||
   '     CLOSE l_ref_cur;'                  ||
   '  END;';
 
   EXECUTE IMMEDIATE l_statement;
END;
/
 

I can test this with: 

 
SET SERVEROUTPUT ON;
 
EXECUTE test_fetch_ref_cur;

Note that I can try to use a more generic solution. If I take a look at the "test_fetch_ref_cur" procedure, I can see that the part I use for loop can be used in more cases. So I move this part into the "dyn_fetch" package, into the "fetch_ref_cur" procedure to which two parameters has to be sent: the process description and the function name that returns ref cursor.

Here's the changed package: 

 
CREATE OR REPLACE PACKAGE dyn_fetch IS
 
   -- SAME AS BEFORE, PLUS:
 
   PROCEDURE fetch_ref_cur (
      p_function_ref_cur VARCHAR2,
      p_process_def      VARCHAR2);
END;
/
 
CREATE OR REPLACE PACKAGE BODY dyn_fetch IS
 
 
   -- SAME AS BEFORE, PLUS:
 
   PROCEDURE fetch_ref_cur (
      p_function_ref_cur VARCHAR2,
      p_process_def      VARCHAR2)
   IS
      l_statement VARCHAR2 (32000);
   BEGIN
      l_statement :=
      '  DECLARE'                              ||
      '     TYPE record_t IS RECORD ('         ||
                record_def || ');'             ||
      '     l_record  record_t;'               ||
      '     l_ref_cur dyn_fetch.ref_cur_t;'    ||
      '  BEGIN'                                ||
      '     l_ref_cur := '                     ||
               p_function_ref_cur || ';'       ||
      '     LOOP'                              ||
      '        FETCH l_ref_cur INTO l_record;' ||
      '        EXIT WHEN l_ref_cur%NOTFOUND;'  ||
               p_process_def                   ||
      '     END LOOP;'                         ||
      '     CLOSE l_ref_cur;'                  ||
 
      '  END;';
 
      EXECUTE IMMEDIATE l_statement;
   END;
END;
/

And here's the changed procedure "test_fetch_ref_cur": 

 
CREATE OR REPLACE PROCEDURE test_fetch_ref_cur (p_query VARCHAR2 :=
NULL) IS
   FUNCTION process_def RETURN VARCHAR2 IS
     -- SAME AS BEFORE
   END;
BEGIN
   test.set_query (p_query);
   dyn_fetch.describe_columns;
   dyn_fetch.fetch_ref_cur (
      p_function_ref_cur => 'test.ref_cur',
      p_process_def      => process_def);
END;
 
/
Posted by 3 comments : Labels:
Subscribe to: Posts ( Atom )