2 Replies Latest reply on May 22, 2013 4:21 PM by rp0428

    How to view SQL for a materialized view in SQL Developer (Oracle 11g R2)

    1010423
      Good Morning,

      I am new to SQL Developer 3.1.07 and just installed it on a Windows 7 PC yesterday.

      There's a materialized view for which I need to view (and eventually update) the SQL. The mat'lized view is in another schema, but my Oracle user does have access to the schema and to this particular mat'lized view. My user also has SELECT, INSERT, UPDATE, DELETE, DEBUG, and ALTER permissions. When I select (click on) the matl'ized view in the object tree on the left, a multi-tab window opens with information in the Columns, Indexes, Grants, Dependencies and Details tabs, but the SQL pane is blank. I don't know if there's some other GRANT that I need in Oracle, or if there's something else I need to do in SQL Developer to see the matl'ized view SQL.

      Thanks

      Bob Gardner
      Robert_Gardner@swn.com
        • 1. Re: How to view SQL for a materialized view in SQL Developer (Oracle 11g R2)
          riedelme
          1007420 wrote:
          There's a materialized view for which I need to view (and eventually update) the SQL. The mat'lized view is in another schema, but my Oracle user does have access to the schema and to this particular mat'lized view. My user also has SELECT, INSERT, UPDATE, DELETE, DEBUG, and ALTER permissions. When I select (click on) the matl'ized view in the object tree on the left, a multi-tab window opens with information in the Columns, Indexes, Grants, Dependencies and Details tabs, but the SQL pane is blank. I don't know if there's some other GRANT that I need in Oracle, or if there's something else I need to do in SQL Developer to see the matl'ized view SQL.
          SQL*Developer can be tricky regarding what it shows for other schemas. I'm not sure what privileges you might need besides the one you listed except RESOURCE on the MV, or maybe CREATE ANY MATERIALIZED VIEW.

          Can you use DBMS_METADATA.GET_DDL to get the data you want (may/may not work due to privileges)?
          • 2. Re: How to view SQL for a materialized view in SQL Developer (Oracle 11g R2)
            >
            I am new to SQL Developer 3.1.07 and just installed it on a Windows 7 PC yesterday.
            >
            If you are just starting with sql developer I suggest you download and use the CURRENT version. That version you installed is pretty old and there have been many bug fixes since then.

            You can download the current version from here.
            http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html