Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Creating Materialized View in Toad

606275Nov 7 2007 — edited Nov 7 2007
As with every new endeavor - come new questions.

I am trying to create a materialized view via toad (first time doing this). Now - toad is great, since it provides a kind-of wizard interface. Hence, one doesn't have to completely code in the create statement, with all of the options, etc.

Instead, in toad, one can (via the schema browser), go into the Materialized Views tab and click on create new. This opens a window with 6 tabs: Basic Info, Refresh Info, Physical Attributes, Query, Partitions & Subpartition Template.

In the Basic Info, one can put a check mark next to some of the following options:
-Build Deffered
-Parallel
-Cache
-Logging
-Using index
-Allow updates
etc.

I have read that build deferred refers to whether or not you would like the view to be created automatically or to be deferred.

Anyways, I tried to find Toad documentation to explain each of the options in-detail. No success. Hence, am researching each part piece by piece.

But - here is my question:
In the Query tab, we are asked to specify a query. I am assuming that the query does not have to be a full query, beginning with CREATE MATERIALIZED VIEW mv_table1 REFRESH FAST etc...

Since in this 'wizard', we are providing the MV name at the top of the dialog box, we are checking the 'options' in the other tabs, etc.

And so, I assumed that the query should merely be a select query in the window mentioned above.

So I entered the following:
select * from table1@remote_db;

When I go to verify the syntax, I get the error message: ORA_00911: invalid character. It seems to be pointing to my db_link (remote_db). I have been using this link throughout many places, w/o any problems.

Has anyone created a MV in toad before? Any links to good toad documentation would be helpful as well.
Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 5 2007
Added on Nov 7 2007
4 comments
4,350 views