Creating a Materialized View

So my issue today is I am trying to create a materialized view using information for a remote db.  My credentials to access the remote database is fine as well as the database link used to create the materialized view.  It is just when I actually try to create it I get an error stating

CREATE MATERIALIZED VIEW "schema"."table"
*
ERROR at line 1:
ORA-00942: table or view does not exist

Now when it says "table or view does not exist" not entirely sure what it is referring to (still learning oracle) because I created a table with that exact same name.  And obviously the view does not exist because that's what I'm trying to make.

Little more background, the database I am working on is a migrated one meaning the old db was oracle 10g and I migrated it over to 11g.  10g is still online "so to speak" meaning I can use it for reference purposes.  I matched everything up and still nada, any help would be appreciated.

It's also sitting on top of a windows 2008 r2 x64 server OS
jjmekkattilAsked:
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.

slightwv (䄆 Netminder) Commented:
Does the user used in the database link have access to the table to are trying to reference?

Without the create materialized view, try selecting from the table:

select column from table_name@remotedb;
0
jjmekkattilAuthor Commented:
I typed that I got missing expression at line 1:

SQL> SELECT column FROM table_name@remotedb;
                            *
ERROR at line 1:
ORA-00936: missing expression
0
jjmekkattilAuthor Commented:
Not sure if you would want me to connect to the remote database before I try that statement.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

slightwv (䄆 Netminder) Commented:
You need to select an actual column name from the table.

You also need to provide the correct table name and database link name.
0
slightwv (䄆 Netminder) Commented:
>>Not sure if you would want me to connect to the remote database before I try that statement.

No.  I want you to see if you can actually select from the remote table.

I don't think the problem is with the create statement.  I think it is in the actual query you are trying to make a materialized view.
0
jjmekkattilAuthor Commented:
OK so I'm confused now.  From the local db I typed:

SQL> SELECT FIRST_NM FROM HR_PEOPLE@REMOTEDB;
SELECT FIRST_NM FROM HR_PEOPLE@REMOTEDB
                               *
ERROR at line 1:
ORA-02019: connection description for remote database not found

Then I connected to the remote DB

Did the exact same statement and I received back data.

Well w/o the @remotedb part
0
slightwv (䄆 Netminder) Commented:
You need to replace remotedb in my select with the remote database alias you are using in the materialized view.

>>I am trying to create a materialized view using information for a remote db

Somewhere in the select used to create the materialized view there is a remote database alias used somewhere.  select a column from the remote table while connected to the local database where you are trying to create the view.

What I want you to do is forget about the "CREATE MATERIALIZED VIEW" command for a minute and focus on the select.


So if the actual create view syntax you are trying to run is:
create materialized view my_mv as
select fred,bob from some_remote_table@some_remote_database;


I want you to just try the select:
select fred,bob from some_remote_table@some_remote_database;
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
jjmekkattilAuthor Commented:
This is my select statement for creating the materialized view with it being scrubbed but generally the same:

SELECT a.SPACE_FAC_NBR FAC_NBR,
a.SPACE_ID Room,
b.first_nm First,
rtrim(b.last_nm) Last,
b.sex_cd Gender,
d.nm organization,
FPR_GET_PHONE@remotedb(b.id, 'BUSINESS') Duty_Phone,
FPR_GET_PHONE@remotedb(b.id, 'PERSONAL') Home_Phone,
FROM FAC_spaces@remotedb a, HR_people@remotedb b, HM_people@remotedb c, PR_organizations@remotedb d
WHERE a.HPER_ID_ASSIGNED = c.id(+)
AND b.ID(+) = c.ID
AND c.prorg_id = d.id(+)
0
jjmekkattilAuthor Commented:
This may help with confusion here is my database link info:

Name      REMOTEDB.US.ORACLE.COM
Net Service Name      REMOTEDB
Schema      ORA_ADMIN
0
johnsoneSenior Oracle DBACommented:
Going by the error:

ORA-02019: connection description for remote database not found

I believe that would indicate that REMOTEDB is not in the tnsnames.ora file on the database server.
0
jjmekkattilAuthor Commented:
That's what I thought too so here is that info:

REMOTEDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = XXXX))
    )
    (CONNECT_DATA =
      (SID = RDB)
    )
  )

I can tnsping it just fine:

C:\Users>TNSPING REMOTEDB

TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 28-AUG-2
013 16:45:57

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
D:\app\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = XXX.XXX.XXX.XXX)(PORT = XXXX))) (CONNECT_DATA = (SID = RDB)))
OK (80 msec)
0
slightwv (䄆 Netminder) Commented:
I was ignoring the ORA-02019 for now and focusing on the original ORA-00942.

>>This is my select statement for creating

OK from a SQL prompt on the local machine, execute it.

My thought is the ORA-00942 means the user that created the database link doesn't have access to one of those tables.

To narrow it down:
select count(*) from FAC_spaces@remotedb;
select count(*) from HR_people@remotedb;
select count(*) from HM_people@remotedb;
select count(*) from PR_organizations@remotedb;

The other issue may be the remote functions.  The user that created the database link may not have execute on those but I would expect a different error.
0
johnsoneSenior Oracle DBACommented:
In addition to all the selects from the individual tables, I would include:

select * from dual@remotedb;

That should tell you for sure whether or not the link is working correctly.

After that you could be seeing a permission issue on one of the underlying tables as well.
0
jjmekkattilAuthor Commented:
Ok, I figured out the issue it was because initially when I log into sqlplus I logged in as sys and when I would try to use the select statement to create the materialized view it wouldn't work.  So I logged in as ORA_ADMIN in sqlplus tried the SELECT statement again and guess what it works.

I'm still learning but that worked but now I am having another issue with my materialized view, I'm using the GUI interface to create it and once the view is created there is a column titled "Can Use Log" which shows as "YES".  Is this for Tablespace logging?  Because if that is the case I specifically stated "NO" for it and once it is created it still shows "YES" under "Can Use Log".  How can I change it to NO?
0
slightwv (䄆 Netminder) Commented:
>> I log into sqlplus I logged in as sys

Get in the habit of:  NEVER log in as SYS!

Always log in as the user that you want to own/use whatever objects you need.

>>once the view is created there is a column titled "Can Use Log"

That isn't something I'm familiar with but I never use a GUI.

What GUI are you using and what are you looking at?
0
jjmekkattilAuthor Commented:
OEM attached shows an image of the said column within the Materialized view section.
Capture.PNG
0
slightwv (䄆 Netminder) Commented:
OK, prepare to be consufed (I was).

Looks like that column is from the deprecated view all_snapshots.  You have to go all the way back to the 8i docs for a description.

The view still exists at least until 11gR2 but the definition is no longer in the docs.

Here's the description form the 8i docs:

CAN_USE_LOG
 
YES if this snapshot can use a snapshot log, NO if this snapshot is too complex to use a log.
 


http://docs.oracle.com/cd/A87860_01/doc/server.817/a76958/rarddv98.htm#29732
0
jjmekkattilAuthor Commented:
Ok so is there a way through the GUI interface to make it a NO?
0
johnsoneSenior Oracle DBACommented:
You cannot change CAN_USE_LOG to NO.  It is simply a flag that is basically determining if you can do a fast refresh or not.
0
slightwv (䄆 Netminder) Commented:
Agree with johnsone.

The question I have is:  Do you understand the explanation I posted?

It should have answered the question you asked above " Is this for Tablespace logging?"

The answer to that question is NO.  There are many forms of logging in Oracle.
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.