Home » SQL & PL/SQL » SQL & PL/SQL » not able to get single mvlog (oracle 10.2.0.1)
not able to get single mvlog [message #446447] Mon, 08 March 2010 07:51 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
I'm able to get the total materialized view logs of a schema but fail to filter to get single one. Could you tell me the suitable set filter to get single one. There is no error also.
my code is
SQL> CREATE OR REPLACE function DDI.f_depen_obj 
  2  return clob
  3  AUTHID CURRENT_USER
  4  as
  5  a number;
  6  b number;
  7  abc clob;
  8  DEFQ CLOB;
  9  
 10  begin
 11  a := dbms_metadata.open('DATABASE_EXPORT');
 12  DBMS_METADATA.SET_FILTER(A,'INCLUDE_PATH_EXPR','=''MATERIALIZED_VIEW_LOG''');
 13  DBMS_METADATA.SET_FILTER(A, 'NAME_EXPR','=''EMACH''','SCHEMA');
 14  DBMS_METADATA.SET_FILTER(A, 'NAME_EXPR','=''PMN_RA_MST_T''','LOG_TABLE');
 15  B:= DBMS_METADATA.ADD_TRANSFORM(A,'DDL');
 16  LOOP
 17  ABC := DBMS_METADATA.FETCH_CLOB(A);
 18  
 19  EXIT WHEN ABC IS NULL;
 20  IF DEFQ IS NULL THEN 
 21  DEFQ := ABC;
 22  ELSE 
 23  DEFQ := DEFQ || ABC;
 24  END IF;
 25  END LOOP;
 26  RETURN DEFQ;
 27  END;
 28  /

Function created.

In line 14, LOG_TABLE is a wrong filter i think. The above function returns all mv logs. If i use 'TABLE' there is no output and no error. If i use MATERIALIZED_VIEW, got total output without error.
Regards,
Madhavi.
Re: not able to get single mvlog [message #446456 is a reply to message #446447] Mon, 08 March 2010 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
We don't have your tables.
We don't have your data.
Therefore we can't run your code.
Re: not able to get single mvlog [message #446494 is a reply to message #446447] Mon, 08 March 2010 21:15 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
my general code is..
SQL> CREATE OR REPLACE function SCOTT.F_DEPENDENT_OBJECTS
  2  return clob
  3  AUTHID CURRENT_USER
  4  as
  5  a number;
  6  b number;
  7  abc clob;
  8  DEFQ CLOB;
  9  
 10  begin
 11  a := dbms_metadata.open('DATABASE_EXPORT');
 12  DBMS_METADATA.SET_FILTER(A,'INCLUDE_PATH_EXPR','=''MATERIALIZED_VIEW_LOG''');
 13  DBMS_METADATA.SET_FILTER(A, 'NAME_EXPR','=''SCOTT''','SCHEMA');
 14  DBMS_METADATA.SET_FILTER(A, 'NAME_EXPR','=''EMP''','LOG_TABLE');
 15  B:= DBMS_METADATA.ADD_TRANSFORM(A,'DDL');
 16  LOOP
 17  ABC := DBMS_METADATA.FETCH_CLOB(A);
 18  
 19  EXIT WHEN ABC IS NULL;
 20  IF DEFQ IS NULL THEN 
 21  DEFQ := ABC;
 22  ELSE 
 23  DEFQ := DEFQ || ABC;
 24  END IF;
 25  END LOOP;
 26  RETURN DEFQ;
 27  END;


Regards,
Madhavi.
Re: not able to get single mvlog [message #446495 is a reply to message #446494] Mon, 08 March 2010 21:19 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
We don't have your tables.
We don't have your data.
Therefore we can't run your code.

For your consideration -
Last month I spent a decent number of hours fighting with DBMS_METADATA in an attempt to get DBMS_SCHEDULER job DDL.
I found a comment by Tom Kyte that it is not (yet) supported.

Perhaps what you desire is not supported either.

[Updated on: Mon, 08 March 2010 22:11]

Report message to a moderator

Re: not able to get single mvlog [message #446497 is a reply to message #446447] Mon, 08 March 2010 22:13 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Blackswan,
I provided my code with general table (EMP in SCOTT Schema).
regards,
madhavi.
Re: not able to get single mvlog [message #446499 is a reply to message #446497] Mon, 08 March 2010 22:24 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>I provided my code with general table (EMP in SCOTT Schema).

I disagree.

select object_type, count(*) from dba_objects where owner = 'SCOTT' GROUP BY OBJECT_TYPE ORDER BY 1;

OBJECT_TYPE	      COUNT(*)
------------------- ----------
INDEX			     2
TABLE			     5


my SCOTT schema has no MATERIALIZED VIEW; does yours?

  1* select object_type, count(*) from dba_objects  GROUP BY OBJECT_TYPE ORDER BY 1
SQL> /

OBJECT_TYPE	      COUNT(*)
------------------- ----------
CLUSTER 		    10
CONSUMER GROUP		     5
CONTEXT 		     5
DATABASE LINK		     2
DIMENSION		     5
DIRECTORY		     8
EVALUATION CONTEXT	    14
FUNCTION		   265
INDEX			  1826
INDEX PARTITION 	   352
INDEXTYPE		    10

OBJECT_TYPE	      COUNT(*)
------------------- ----------
JAVA CLASS		 16417
JAVA DATA		   306
JAVA RESOURCE		   770
JOB			     6
JOB CLASS		     2
LIBRARY 		   150
LOB			   572
LOB PARTITION		     1
MATERIALIZED VIEW	     2
OPERATOR		    57
PACKAGE 		   843

OBJECT_TYPE	      COUNT(*)
------------------- ----------
PACKAGE BODY		   787
PROCEDURE		    85
PROGRAM 		    12
QUEUE			    27
RESOURCE PLAN		     3
RULE			     4
RULE SET		    19
SCHEDULE		     1
SEQUENCE		   141
SYNONYM 		 20026
TABLE			  1687

OBJECT_TYPE	      COUNT(*)
------------------- ----------
TABLE PARTITION 	   200
TRIGGER 		   170
TYPE			  1927
TYPE BODY		   173
UNDEFINED		     6
VIEW			  3670
WINDOW			     2
WINDOW GROUP		     1
XML SCHEMA		    26

42 rows selected.

[Updated on: Mon, 08 March 2010 22:41]

Report message to a moderator

Re: not able to get single mvlog [message #446502 is a reply to message #446447] Mon, 08 March 2010 22:58 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Sorry for inconvinience. I have given the materialized view log script for two tables. I want to apply set filter to get mv log of a single object but i got two logs when i apply 'BASE_TABLE' or 'LOG_TABLE'. I got empty clob when using 'TABLE' or MATERIALIZED_VIEW_LOG' in the line 14 (4th parameter).

CREATE MATERIALIZED VIEW LOG ON SCOTT.DEPT
WITH PRIMARY KEY EXCLUDING NEW VALUES;
   
CREATE MATERIALIZED VIEW LOG ON SCOTT.EMP
WITH PRIMARY KEY EXCLUDING NEW VALUES;


Regards,
Madhavi.
Re: not able to get single mvlog [message #446503 is a reply to message #446447] Mon, 08 March 2010 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>I'm able to get the total materialized view logs of a schema but fail to filter to get single one

When I Read The Fine Manual, I agree with statement above.
It appears to be working as designed.

MATERIALIZED_VIEW      materialized views     SN None
MATERIALIZED_VIEW_LOG  materialized view logs D  None


http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA

"In the attributes column, S represents a schema object, N represents a named object, D represents a dependent object, G represents a granted object, and H represents a heterogeneous object."

MATERIALIZED_VIEW_LOG only has attribute of "D";
& therefore can not be "named"

[Updated on: Mon, 08 March 2010 23:15]

Report message to a moderator

Re: not able to get single mvlog [message #446514 is a reply to message #446447] Tue, 09 March 2010 00:06 Go to previous message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi Blackswan,
Thank you for response.
But i'm able to filter the other dependent objects like COMMENTS, INDEX_STATISTICS, TABLE_STATISTICS...(In my database, i dont have other dependent objects like RMGR_PLAN_DIRECTIVE, RLS_GROUP...to test) by keeping BASE DEPENDENT OBJECT TYPE(TABLE, view for above dependencies) as 4th parameter even though they dont have name. But in case of mv log, i failed to filter to get single object.

Regards,
Madhavi.
Previous Topic: Precompiled sql
Next Topic: Preventing duplicate column value using Trigger
Goto Forum:
  


Current Time: Fri Sep 30 21:00:07 CDT 2016

Total time taken to generate the page: 0.10632 seconds