Where did the Materialized Views go?

SQL Navigator
1

DB: Oracle 10.2.0.2
OS client: Windows XP Pro

In SQL Nav 5.5, all of the materialized views appear in the DB Navigator. In 6.3, the materialized views are empty in DB Explorer. I see the node for Materialized View, but none of them are listed.

What might cause this to happen? I tried a different database that also had materialized views, and they seemed to appear fine. For this particular database, they are missing. What would cause the newer version of SQL Nav to make them disappear? Is it a permissions-issue?

Thanks,
-Michael

2

Hi Michael,

In 6.3, When connect to DB, please make sure you have the 'Enable using DBA View' option check box selected in the log in window (click the Options button to expand the option panel in the log in window).

Thanks and regards,
Bruce

Message was edited by: bruceduong

logon.jpeg
logon.jpeg379×605 26.9 KB

3

Hi Bruce,

I gave that a try, but still no luck. The list is still empty.

Thanks,
-Michael

4

Hi Michael,

Can you confirm that you couldn’t see any materialized view in DB Navigator as well, using 6.3? You can press F12 to open DB Navigator in 6.3 then try to find the Materialized Views node like in 5.5.

If you couldn’t find any materialized view in DB Navigator, could you open Quest SQL Tracker from Start -> All Programs -> Quest Software -> SQL Navigator 6.3 -> Quest SQL Tracker? You will find sqlnavigator.exe in the list. Highlight it and press Start Monitoring button before you go to SQL Navigator to expend the Materialized Views node. Then go back to SQL Tracker you will be able to see the queries that SQL Nav just ran. Could you please send those queries to me? I will check them to see we can figure out the problem here.

Thanks,
Vincent

5

Timestamp: 9/8/2010 9:59:17 AM

　

Elapsed time: 0.140

Timestamp: 9/8/2010 9:59:27 AM

select object_name,decode(status,‘VALID’,0,1) from sys.all_objects

where owner=‘XXXX’ and object_type=:type and object_name not like ‘BIN$%’ and object_name like ‘%’

　

TYPE=[‘MATERIALIZED VIEW’]filter=[

　

Elapsed time: 0.078

Thanks,
-Michael

Hi Vincent,

Yes, I also tried DB Navigator in 6.3. Still no materialized views there. Below is the output from SQL Tracker:

****select u.username from all_users u where username <> user and exists (select object_name from all_objects o where o.owner=u.username)************owner=[‘XXXX’]’%’]

6

select /*+ QCO9i QCOALL */ sn.owner,sn.name object_name,sn.table_name,sn.master_view,sn.master_owner,sn.master,sn.master_link,

decode(sn.can_use_log,‘YES’,1,0) can_use_log,decode(sn.updatable,‘YES’,1,0) updatable,sr.last_refresh,sn.error,decode(sn.type,

‘COMPLETE’,1,‘FAST’,2,‘FORCE’,3,‘ERROR’,4,‘NEVER’,5,0) type,sn.next,to_char(sn.start_with) start_with,sn.refresh_group,

sn.update_trig,sn.update_log,sn.query,decode(sn.refresh_method,‘ROWID’,1,‘PRIMARY KEY’,2,‘COMPLEX’,3,‘JOIN VIEW’,4,‘AGGREGATE’,

5,0) refresh_method,decode(sn.fr_operations,

I’m not sure if it’s of use, but here’s the output from 5.5:

____****

7

Hi Michael,

Do you want to see the Materialized Views in the log-in user’s schema or from another schema?

The difference between 6.3 and 5.5 is we involve sys.all_objects to look for materialized views in 6.3. It seems there’re certain materialized views can be seen from sys.all_snapshots but not from sys.all_objects. We will investigate this and will fix it. We will keep you posted.

Thanks,
Vincent

8

Hi Vincent,

I use it both ways depending on which database I’m connected to. In this instance, I’m logged in as one schema, but looking at materialized views that were created in another schema.

Thanks,
-Michael

9

Hi Vincent,

Do you happen to have any update on whether or not this can be fixed?

Thanks,
-Michael

10

Hi Michael,

I have managed to reproduce the problem. A CR has been created and we will fix it in the coming release(6.5) for you.

Thanks,
Vincent