Home » SQL & PL/SQL » Client Tools » view red cross
view red cross [message #594538] Thu, 29 August 2013 15:22 Go to next message
anncao
Messages: 74
Registered: August 2013
Member
We have a materialized view showing red cross in oralce sql developer. But it functions well, we can select on it, no errors.

I searched on web, and some tells if underlying table changed, it may be broken. and need to refresh.


So I think I need to refresh it, using alter materialized view ... compile, but it told me the table or view doesnot exist, but it exists.


What could be wrong?


Thanks




Re: view red cross [message #594540 is a reply to message #594538] Thu, 29 August 2013 15:28 Go to previous messageGo to next message
Bill B
Messages: 1069
Registered: December 2004
Senior Member
Thats not how you refresh a MVIEW, use dbms_mview.refresh('MVIEW_NAME','C');
Re: view red cross [message #594542 is a reply to message #594540] Thu, 29 August 2013 15:38 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member
Thanks, I tried that, and I got error like below:

Also what is command to recomplie it?

Error starting at line 1 in command:
Dbms_Mview.Refresh(Prefsschoolyeardaily,'C')
Error report:
Unknown Command
Error starting at line 1 in command:
exec Dbms_Mview.Refresh(Prefsschoolyeardaily,'C')
Error report:
ORA-06550: line 1, column 26:
PLS-00357: Table,View Or Sequence reference 'PREFSSCHOOLYEARDAILY' not allowed in this context
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

[Updated on: Thu, 29 August 2013 15:39]

Report message to a moderator

Re: view red cross [message #594543 is a reply to message #594542] Thu, 29 August 2013 15:52 Go to previous messageGo to next message
Bill B
Messages: 1069
Registered: December 2004
Senior Member
You have to have single quotes around the MVIEW name and it must be in uppercase.

Dbms_Mview.Refresh('PREFSSCHOOLYEARDAILY','C')

[Updated on: Thu, 29 August 2013 15:52]

Report message to a moderator

Re: view red cross [message #594544 is a reply to message #594543] Thu, 29 August 2013 16:01 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member
Thanks, that works!

So what does the red cross mean, does it mean a compile error or just need to refresh, or they are the same?
I have another view (not materialized view) depends on this materialized view, do I need to refresh that too, it doesnot have red cross though.

Thanks
Re: view red cross [message #594545 is a reply to message #594544] Thu, 29 August 2013 16:08 Go to previous messageGo to next message
Bill B
Messages: 1069
Registered: December 2004
Senior Member
If it is a normal view then no, its just a stored query and needs no refresh. As for the red cross, I have no idea. I don't use sql developer.
Re: view red cross [message #594546 is a reply to message #594545] Thu, 29 August 2013 16:09 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member
Thanks, can I ask what tool you use?
Re: view red cross [message #594547 is a reply to message #594546] Thu, 29 August 2013 16:12 Go to previous messageGo to next message
Bill B
Messages: 1069
Registered: December 2004
Senior Member
Sure, sql*plus and TOAD. Mostly sql*plus. I got used to it, I have been programming in oracle for 20 years.
Re: view red cross [message #594548 is a reply to message #594547] Thu, 29 August 2013 16:13 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member
Thank you very much!
Re: view red cross [message #594550 is a reply to message #594548] Thu, 29 August 2013 17:11 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member
I come back again, I found if I change the data in the underlying table of the materialized view, the materialized view turns to red cross again.
does that mean materialized view needs to setup something to refresh it automatically?

Thanks
Re: view red cross [message #594554 is a reply to message #594550] Fri, 30 August 2013 00:12 Go to previous messageGo to next message
Littlefoot
Messages: 19346
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It might help if you posted CREATE MATERIALIZED VIEW command.
Re: view red cross [message #594584 is a reply to message #594554] Fri, 30 August 2013 07:48 Go to previous messageGo to next message
Bill B
Messages: 1069
Registered: December 2004
Senior Member
You can have it automatically refresh or refresh on a schedule but like littlefoot said we need to see the script you used to make the MVIEW.
Re: view red cross [message #594586 is a reply to message #594584] Fri, 30 August 2013 10:29 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member
Here is the materialized view
-- Create a materialized view used by a number of different views for extracts.
--

Declare
v_count Number;
Begin

select count(*) into v_count from user_objects where object_type = 'MATERIALIZED VIEW' and object_name = Upper('PrefsSchoolYearDaily');
If v_count > 0 Then
execute immediate 'drop materialized view PrefsSchoolYearDaily';
End If;

End;

/

Create Materialized View PrefsSchoolYearDaily
Build IMMEDIATE
As
Select Distinct name, DBMS_LOB.substr(Value, 5, 1) as Value, schoolid, yearid
From prefs
Where lower(name) = 'att_recordmodedaily'
And value like '1%'
;

Create Index
idx_PSYD_SchoolID
On PrefsSchoolYearDaily (schoolid)
;

Create Index
idx_PSYD_schoolidyearid
On PrefsSchoolYearDaily (schoolid, yearid)
;

Create Index
idx_PSYD_yearid
On PrefsSchoolYearDaily (yearid)
;


Grant Select on PrefsSchoolYearDaily To Public;

-- Select Count(*) From PrefsSchoolYearDaily;

-- Drop Materialized View PrefsSchoolYearDaily;
Re: view red cross [message #594589 is a reply to message #594586] Fri, 30 August 2013 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58639
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: view red cross [message #594590 is a reply to message #594586] Fri, 30 August 2013 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58639
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Declare
v_count Number;
Begin

select count(*) into v_count from user_objects where object_type = 'MATERIALIZED VIEW' and object_name = Upper('PrefsSchoolYearDaily');
If v_count > 0 Then
execute immediate 'drop materialized view PrefsSchoolYearDaily';
End If;

End;

/


This is silly, just drop the mview, no need of all this PL/SQL stuff.

Regards
Michel
Re: view red cross [message #594605 is a reply to message #594590] Fri, 30 August 2013 11:40 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member
Our oracle version is 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Re: view red cross [message #594609 is a reply to message #594605] Fri, 30 August 2013 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58639
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good, now comply for the rest.

Regards
Michel
Re: view red cross [message #594611 is a reply to message #594609] Fri, 30 August 2013 12:55 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member
Here is the formatted code, I don't see an edit button, so repost,
-- Create a materialized view used by a number of different views for extracts. 
DECLARE 
    v_count NUMBER; 
BEGIN 
    SELECT Count(*) 
    INTO   v_count 
    FROM   user_objects 
    WHERE  object_type = 'MATERIALIZED VIEW' 
           AND object_name = Upper('PrefsSchoolYearDaily'); 

    IF v_count > 0 THEN 
      EXECUTE IMMEDIATE 'drop materialized view PrefsSchoolYearDaily'; 
    END IF; 
END; 

/ 
CREATE materialized VIEW prefsschoolyeardaily 
build IMMEDIATE 
AS SELECT DISTINCT name, 
                   dbms_lob.Substr(value, 5, 1) AS Value, 
                   schoolid, 
                   yearid 
   FROM   prefs 
   WHERE  Lower(name) = 'att_recordmodedaily' 
          AND value LIKE '1%'; 

CREATE INDEX idx_psyd_schoolid 
  ON prefsschoolyeardaily (schoolid); 

CREATE INDEX idx_psyd_schoolidyearid 
  ON prefsschoolyeardaily (schoolid, yearid); 

CREATE INDEX idx_psyd_yearid 
  ON prefsschoolyeardaily (yearid); 

GRANT SELECT ON prefsschoolyeardaily TO PUBLIC; 
Re: view red cross [message #594618 is a reply to message #594550] Sat, 31 August 2013 03:00 Go to previous messageGo to next message
Littlefoot
Messages: 19346
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Back to what you asked previously:
annacao wrote on Fri, 30 August 2013 00:11
does that mean materialized view needs to setup something to refresh it automatically?


Code you used to create a materialized view suggests that it doesn't refresh at all in a scheduled manner.

Have a look at Refresh Methods for Materialized Views and then follow suggested links to apply the most appropriate refresh option for your materialized view.
Re: view red cross [message #594696 is a reply to message #594618] Sun, 01 September 2013 21:40 Go to previous messageGo to next message
anncao
Messages: 74
Registered: August 2013
Member

I found any time DML happened on the underlying table, the MV shows a red cross next to it.
I did a query:Select * From User_Mviews, I see in the refresh Mode it is ON Demand, the staleness column is need_compile, so I run the following command:
Alter Materialized View Prefsschoolyeardaily Compile, I see compile state is Valid, and staleness column becomes unknown,
If I do
exec DBMS_MVIEW.REFRESH('PREFSSCHOOLYEARDAILY','c'); the result is the same.

What does this mean, shall I still ignore the red cross, and the table data is stale or not?
Thanks much
Re: view red cross [message #594704 is a reply to message #594696] Mon, 02 September 2013 00:57 Go to previous messageGo to next message
Littlefoot
Messages: 19346
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know why the icon turns to the red cross; occasionally, Jeff Smith (his handle here is thatjeffsmith) stops by and answers SQL Developer related questions - I believe that he might know the background of the issue. You could try to contact him and see what he says.

On the other hand: if you can select data from that materialized view, you can use it (obviously) and work with data it contains. As you have found out, newly added (or modified) date in its underlying table do not affect materialized view contents until you refresh it ("on demand"). So, if you are happy with its current contents, fine. If not, refresh it. I suppose that you'll find that refreshing option annoying sooner or later and switch to another scheduling method.
Re: view red cross [message #594707 is a reply to message #594704] Mon, 02 September 2013 02:11 Go to previous message
anncao
Messages: 74
Registered: August 2013
Member
Thanks, we do schedule the refresh nightly.
Previous Topic: Sql developer not working constantly
Next Topic: SQLPLUS for Oracle 11 not rolling back
Goto Forum:
  


Current Time: Fri Aug 01 02:00:27 CDT 2014

Total time taken to generate the page: 0.09189 seconds