Toad World® Forums

Materialized Views


#1

As a suggested enhancement, we would like to see the Materialized Views shown as a node under a user in the New DB Explorer as it is in the old explorer. We know MV’s show up under tables but since our naming convention doesn’t identify them. We have to check under materialized views for the list.

Thanks…


#2

I think the ‘MV’ node is under ‘OTHER’ node. Couldn’t find anything under ‘table’. Anyway, the MV node doesn’t give the user as many actions as in DB Nav tree. You can’t even open the MV to view/edit. I will log in an enhancement requesr for this.


#3

I see the MV’s section under others, we didn’t see them before because like triggers we are not owners. They need to use the DBA tables as do triggers (per the view preferences setting). Actually I did a select with all_tables and I can see materialized views in the list which leads me to believe this is using user_tables instead???

Thanks…


#4

If you can see the objects in the all_objects view, you should see them in the DB Explorer. As Gwen said, they will be under the Other node, in the owner’s schema.

Regards,
Roman


#5

I see the error of my ways. Sorry about that… There is still the problem of triggers, the print screen is comparing the new db explorer in 6.0 to the old db Navigator in 6.0 with the same user. As you can see there are triggers that can be seen by the old explorer, but can not be seen by the new. I did some additional checking and this seems to be only on 8 and 10G. I can see triggers owned by other users in the 9 schema. I can no longer compare the 10G to 9 for the same owner, our 9 has been upgraded, but it is basically the same type of scenario (Third Party applications). Let me know if I can be of more help on this.

Message was edited by: Lozier
trigger_printscreen.gif


#6

Hi Dale,

So sorry I missed this thread. Could you please provide me with the script to create the user you were using? I need to know which privileges/ roles this users have.

Gwen


#7

I do not know how to get the ddl for creating the user. I have to belive the permissions are correct otherwise I wouldn’t be able to see the triggers in the old db navigator. On a bright note it only seems to be triggers, I can expand Constraints columns, indexes and comments.


#8

Hi Dale,

Look for your user under Users node in DB Navigator and right click to select Extract DDL. An alternative is double click to open the User Editor and look for granted privileges and roles in the associated tabs.

Gwen


#9

select * from dba_triggers

where owner = ‘XXXXXXXXXXX’;

select * from all_triggers

where owner = ‘XXXXXXXXXXX’;

First query returns 38 rows the second returns none…

Sorry this took so long, my Dba’s have asked that I not give that information out. I guess I am at a loss for what this does. If you would like, I can do a select against whatever table you are using to get the list of triggers. As I have stated, I can see the triggers in the dba_triggers but can not see them in all_triggers. So if I run the following 2 queries:
********


#10

Dale,

I think (from glancing the code behind all_triggers) you can only see triggers in all_triggers if:

  1. you are the owner of the table (if its is table-trigger) OR
  2. you are the owner of the trigger OR
  3. you have access to the table through a role OR
  4. you have the “CREATE ANY TRIGGER” privilege

The latter one should do nicely, if your DBA’s allow it…


#11

Hi Dale,

Please capture the queries executed by Nav when you expand the node using SQL Monitor. By doing that, we will be able to see the difference between the two.

Gwen


#12

Ok, I am sorry to be so dense on this, but I searched Help in navigator for Monitor and could not find anything. I sent your request to the dba’s and they are not sure what it is you are looking for. What is sql monitor?


#13

Hi Dale,

To access SQL Monitor, go to Start > Programs > Quest Software > SQL Navigator x.x >SQL Monitor.
What it does is it captures the sql excuted by Nav. For example, when you expand the tables node in one of the tree, a sql will be fired in the background to retrieve the list of tables from the database. That SQL will be captured in SQL Monitor if you have it opened and the right application ticked.
A useful tip here is to not tick the application until you finish setting up the environment. In your case, you need to capture the query fired when you expand the node so don’t tick the application until you get to that step. Otherwise it will capture more information than what we need.

Hope this helps
Gwen


#14

Here are the 2 selects:
This is from the new explorer:

select t.trigger_name,decode(t.status,‘DISABLED’,2,decode(o.status,‘VALID’,0,1)) from sys.all_triggers t,sys.all_objects o
where t.table_owner=:owner and t.table_name=:name and o.owner=t.owner and o.object_name=t.trigger_name
owner = ‘S’
name = ‘I’

This is from the old way:
select owner,a.object_name,a.object_id,a.created,a.last_ddl_time,decode(a.status,‘VALID’,0,‘INVALID’,1,2) status
from sys.dba_objects a
where a.owner like :schema
and object_name like :object_name
and object_type=:object_type
object_type = ‘TRIGGER’
schema = ‘S’
object_name = ‘T’


#15

I noticed in the list of fixes there was a mention about triggers (way to geek for me to understand), if this was for the issue in this chain I want to make a note that it is not just triggers, Package bodies seem to have the same problem…
Synonyms are another questionable item. I can see synonyms under my schema (under other), but I don’t believe they are mine. However, I can not see the public synonyms I know exist. I assumed that if a user has an object that has a public synonym it would show up somewhere in that users schema not public. But as the second print screen shows there are no public synonyms but there are numerous synonyms for tables owned by this user… Sorry if this is late and if I misunderstand synonyms.
synonyms.gif


#16

I noticed in the list of fixes there was a mention about triggers (way to geek for me to understand), if this was for the issue in this chain I want to make a note that it is not just triggers, Package bodies seem to have the same problem…
Synonyms are another questionable item. I can see synonyms under my schema (under other), but I don’t believe they are mine. However, I can not see the public synonyms I know exist. I assumed that if a user has an object that has a public synonym it would show up somewhere in that users schema not public. But as the second print screen shows there are no public synonyms but there are numerous synonyms for tables owned by this user… Sorry if this is late and if I misunderstand synonyms.
dbapackages.gif


#17

Hi Dale,

The first one is with the DB Explorer does not show the same objects as DB Navigator. This one is not the same with the ‘triggers’ one in the announcement but it’s in our system under a different name.

Second one is with public synonyms. Punlic synonyms will be displayed under PUBLIC schema, synonyms node. This work correct for DB Navigator. In DB Explorer, I can’t find the ‘synonyms’ node or ‘Others’ node under the Public schema so it will need to be addressed. Although I have a feeling that it may be fixed if the first one is fixed. I will add notes to the first CR and it it doesn’t work I will raise a different CR for the second issue.

You said “there are numerous synonyms for tables owned by this user”. In the screenshot, I can see the number ‘0’ next to the ‘Synonyms’ node so there’s no synonym at all in the current schema.

Gwen


#18

It could be that I don’t understand the “concept” of how you are going to display synonyms. Yes all synonyms for objects under this schema are public there are no “private”. But it seemed like I read somewhere that even though it was a public synonym it made more sense to list it in the schema of the object it references, which I would agree. I never use the public node because it returns so much data it kind of useless. Filtering doesn’t help because the names aren’t necessarily filterable. But if you listed public synonyms under a schema of the object it references it might be more useful. So you have Schema Alpha with object Beta and a public schema Beta rather than listing it under public it would be helpful to see that alpha has a public synonym beta. Just a thought


#19

Hi Dale,

You got a point there but I’m not so sure it’s a common practice. I checked with Toad and it behaves the same way Nav does. Also, in this link I found that “A public synonym does not belong to any schema” so it may be wrong if we show them in the current schema.
http://www.enterprisedb.com/documentation/ddl-synonims.html

Gwen


#20

I don’t necessarily disagree, I was simply referring to this statement made by Roman in a thread labeled “Synonyms in DB Esplorer”:
Hi Filipe,

"
As DB Explorer is a developer (not DBA) tool, we took a slightly different approach to synonyms compared to DB Navigator. They are now grouped under the object types they refer to: table synonyms under Tables, package synonyms under Packages etc. Private synonyms will be located under own schema, public synonyms under PUBLIC. The original idea was that opening a synonym from DB Explorer will open the referenced object, so synonyms would be transparent to the user; however, this was later changed because of some complaints. We welcome users’ opinions on the new design so that we can choose the most suitable solution.

Regards,
Roman
"

This sounded interesting, but I must have misuderstood the intent…