Materialized views in oracle.

We have Materialized views. There is data different (count ) from original table  and views.
SO I want to know,
  how to find details about views (query etc to generate view ) in oracle.
why it is different? how to find that? Is it possible that views refresh late from original table?

Thanks for reply.
-/Karan.
LVL 11
ManishLeadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Swadhin RaySenior Technical Engineer Commented:
>>> how to find details about views (query etc to generate view ) in oracle.

Try to query for the view code:

select dbms_metadata.get_ddl('VIEW','YOURVIEWNAME') VIEWCODE FROM DUAL;

Then check if your query for the view is really pulling the data or what logic is been used to pull the data from the source tables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Swadhin RaySenior Technical Engineer Commented:
Materialized views in oracle update periodically base upon the query definition, for more information you can check from the below link:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm
0
Wasim Akram ShaikCommented:
Materialized views are views of the object located in remote database, unlike normal views, materialized view will have a base table in your local database which should get refreshed accordingly as per ur need.

If this doesnot get refreshed, then you will find the difference in the local replica and the actual base table located in the remote database.

There are various methods of refresh
1. Fast(loads the delta between the local and remote db from the last refreshed time at local)
2. Complete(Truncates the table in local db and reloads it again from remote db)

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Swadhin RaySenior Technical Engineer Commented:
@wasimibm : can't we create a Materialized view within the database?
0
Wasim Akram ShaikCommented:
@slobaray: yes you can ofcourse create a materialized view in the same database, again. the concept of materialized view remains the same.

If you create the materialzed view in the same database then you will create a self db link(db link pointing to the same database) so that concept of source db(local db) and target db(remote db) remains there..
0
ManishLeadAuthor Commented:
Are there sql to refresh mat. view.?

0
Wasim Akram ShaikCommented:
syntax can be found here;

http://docs.oracle.com/cd/B10501_01/server.920/a96568/rarmviea.htm#94135

sample syntax found here:

DBMS_MVIEW.REFRESH (
   { list                 IN     VARCHAR2,
   | tab                  IN OUT DBMS_UTILITY.UNCL_ARRAY,}
   method                 IN     VARCHAR2       := NULL,
   rollback_seg           IN     VARCHAR2       := NULL,
   push_deferred_rpc      IN     BOOLEAN        := true,
   refresh_after_errors   IN     BOOLEAN        := false,
   purge_option           IN     BINARY_INTEGER := 1,
   parallelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0,
   atomic_refresh         IN     BOOLEAN        := true);

exec DBMS_MVIEW.REFRESH('MVIEW_NAME','C','',FALSE,FALSE,0,0,0,TRUE);

MVIEW_NAME--> Name of Materialized View
C--->Type of Refresh, Complete(C) or Fast(F)
0
ManishLeadAuthor Commented:
Thanks. Will check.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.