Materialized Views

SQL Navigator
1

Is there anyway with SQL Navigator to view the select statement for a Materialized View and extract the DDL?

2

Out of curiosity, which version of Nav are you using?

3

In v5.1 try this:

  • Select VIEW :: Visual Object Editor :: Materialized View/Object Editor

  • Then, choose the MV/Snapshot from the snapshot dropdown

  • Once it’s open, click the “Extract DDL” button on the toolbar (or, type CTRL+D)

  • this opens a preferences screen. Select the correct action and click OK.

    You should now see the entire DDL for the MV with the SQL as well.

I looked at the 5.5 beta and it works exactly the same there.

4

As Daniel advised , it can be accessed from the associated VOE or from the Db nav tree by double clicking on the Snapshot/Materialized View , then it will be opened via VOE.Here in the Voe the "Select " statement can be viewed .By performing “CTRL + D” on specific snapshot in Db nav tree also gives the DDL for the object .

regards
sekhar

5

Thanks that works fine.

I guess I am just use to “materialized view” terminology as opposed to “snapshots”.
I just noticed that “Snapshots” are in the object scheme viewer.
Thanks for the info.

6

It is great to hear that our team could assist you through our Inside SQL Navigator Community. Hope that you will be able to get more assistance and valuable info from our Dev team and our other SQL Nav users through the Community in the future.

We will make changes to the DB Tree to display the node text for Snapshots/Materialized View (terminology) correctly accordingly to the version of Oracle DB. Hope that it would make it easier for you and our users.

Best regards,
BD

7

Bruce, can we get that in the next beta build?

8

Yeh, it is on the plan for Beta 3 cycle. We could not get the changes in the beta build (Beta 2) which is to be release in a few days, but it will be definitely in Beta 3 - promise

Beta 3 build will be released on 19 June 06 (US time).

9

I too, would love to see enhanced support of Materialized Views.

Additionally, when I upgraded to 5.1.0.655, in spite of selecting the option to reset my toolbars and menus so that I could take advantage of the new features during the install, it combined them to show both my customized toolbars, as well as the new features. I shutdown SQL Nav, opened it the next morning, and the new features vanished, leaving just my customized toolbars. I manually added the Materialized View Object Editor to the menu and toolbar, after several attempts to reset my toolbars manually.

10

CR178644 has been raised for changes to be made to the DB Navigator Tree to display the text as Materialized Views for Oracle DB version 8i and above. Changes will be also be made to fix the Search Object screen which currenlty does not include Snapshot/Materialized Views in the search. These changes will be included in the next beta build which will be released early next week.

Thanks and regards,
Bruce

11

Hi Micheal,

Thanks for you feedback. As per my previous post. More changes have been made to displa ‘Materialized Views’ for Oracle 8i and above, The search object issue will be fixed as well. We would love to hear more from you, would you please provide us the list of top 3 things of enhancement support of MV you would like to see in the next release of Nav.

Regarding the toolbar issues, we could not reproduce here ourselves yet.
The workaround to force the reset at startup is to change the value of the following registry key minus 1. HKEY_CURRENT_USER\Software\Quest Software\2SQL Navigator 4.0a1\Main Form\ToolbarVersion\InternalVersion.

Regards,
Bruce