Can select from table across db link but cannot create a materialized view

Advertisement
dblink1 is a private db link (it could also be public)
select count(*) from [email protected];
-->
COUNT(*)
5276
create materialized view v1 as
select * from [email protected];
-->
select * from [email protected];
ERROR at line 2:
ORA-00942: table or view does not exist
This only applies to this particular db link. For another db link called "dblink2",
create materialized view v2 as
select * from [email protected];
-->
Materialized view created.
What gives? dblink1 and dblink2 are 2 different databases on 2 different machines.
Advertisement

Replay

This is the simplest cause:
dblink2 points to a database that has not a t1 table (as the message says, the table does not exist)
This is the following simple cause:
dblink2 uses a remote user user2, that can not select the t1 table because it exist in another schema, and user2 has not a synonym to the table (post the result of a query over dba_db_links that shows the remote users). Database objects has name and schema. If the objects are in your own schema you dont need to write the schema name, nor if you has a synonym to the object. But if the table t1 is in another schema, and you write t1, it means user2.t1 and fail.
I hope this helps
Regards,
Alfonso

Can I Select from table skipping extents linked with lost datafiles?

Hi~, I need your help to recover my database. I'm using oracle 9.2.0.8 at Fedora 3 with no-archive mode. and I don't have any backup. Last night, I experenced hard disk failure. I tried OS-level recovery, but I lost some datafiles of tablespace. anyw

Insert into table a (select * from table b) - need pk?

Hi there. I'm going to insert into table FINAL (select * from table STAGING) - same structure but in STAGING the ID column has nulls. Do I need to provide the ID (primary key) for table FINAL or will it get created based on sequence/trigger? If I wer

Select * from table where rownum 5; no rows returned? why is it so?

select from table where rownum > 5;* no rows returned. why is it so? can anyone explain me?Hi, rownum is pseudo column, and it is based on the sort order. For ex, if you want to get the first 5 employees who get the least salary, you use, select enam

Select * from table as of Scn fails in powercenter

Hi all, I have written the below query in SQL override of a Powercenter mapping. This fails with 'FROM keyword not found where expected' error. Can you please suggest how else I can fetch last committed data from my source?Select * from table AS OF S

Special Grant to use "Select * from Table(cast..."??

Hi, I've recently created the types and function to use the Table(Cast(funtion) as type)). It works fine, and gives me the correct result. I've granted execute on the types and on the function to a role that is enabled for a user, but when the user t

Select from table containing clob

If i try to select from table containing clob column in SQL PLus it gives error. Tab1 contains 3 clob columns and 1 blob column select * from tab 1; SP2-0678: Column or attribute type can not be displayed by SQL*Plus The same statement works in SQL D

Xmlgen.getxml("select * from table") returns null pointer exception

I am running oracle 8i on solaris server and clinet on windows NT and i am this select statement select xmlgen.getxml("select * from table") from dual ,its returning null pointer exception,i have tried it through jdbc,even then its returning xml

Html form for select * from table a

Hi all, I m looking for report on header/footer part from one apex pages which will display context from table a, like sql statement select * from table a, written in html. Is any examples in java script/html select from tables (and same insert into

URGENT : select from table statement in ABAP OO

Hi all, I am an absolute ABAP OO beginner and need some quick help from an expert. How can I make a selection from an existing table (eg MARA) in an BADI which is programmed according to ABAP OO principles. In the old ABAP school you could make a TAB

Select from table without specifying the column list.

I want to reference the columns in select list without specifing the column name of a table. For example i am having a table called emp(id number,name varchar2(30)). I want to write a query which will display the name of the employees. But i don't wa

Adding a Quicktime Media Player to a page that can play from a list of links

I'm on a Mac G4 window front tower running OS X 10.4.11. I am using Adobe GoLive 7.2. and Quicktime player 7.6. The project I am involved in entails linking a group of Quicktime movies to a Quicktime media player on the same page. I can handle the li

Create Table Temp As Select * from Table

What happens in the backend (performance point of view) when we create a table as follows: Create Table Temp As Select * from Table1; Suppose that the table Table1 has 10 million rows. How is this different from inserting 10 million rows using Insert

What determines which inputs you can select from when you're creating a VISA Resource Name Constant?

Hey, I'm trying to write a labview driver an instrument. I have another instrument which came with labview drivers, and I had hoped that by looking at how they did it, I could write my own for this piece of equipment. However, the problem is they use

When downloading from Safari I receive ths message "cannot create file" ?

When downloading a file from any website or email etc. I receive this message in the downloads box "cannot create file". I need help. If anyone can point me in the right direction I would greatly appreciate it. This problem has been bugging me f

When clicking on links that open in a new window, the links don't open and Firefox freezes in a way. I can navigate the page I am on but cannot click to open a new tab or bookmark.

When navigating the internet, if I come across a web page that has a link to open a picture or program in a new window, if I click on it, it does not open. Firefox immediately looses functionality in that I cannot open a new tab with the mouse wheel,

Install from scratch 3 times Mavericks but cannot used my restore it will just start for ever. After first install and restore mail and link between folder and files didn't work anymore. Now cant see my disk in finder. Help?

First install through regular update : never finishing the start after automatic restart First install from scratch: erase through disk utility, install OS X Mavericks (using Recover mode), restart ok - Used Migration Assistant to restore application

Select from table on highest priority

I have a table, TAB1, which has, amongst others, 2 fields. FIELD1 is a non-unique ID and FIELD2 is a two character code. For example: 123,AA 123,AZ 124,BB 124,BY I also have a second table TAB2 which contains 3 fields. FIELD1 + FIELD2 are two charact

ORA-14551 when using rules manager inside a SELECT * FROM TABLE(myFunction)

Hello I want to return rows from a pipelined function, and map this to a view, or a BC4J an query this in my application. I want to populate the rows based on many evaluations, to achieve this, I want to include the rules manager functionality. I hav

My fb photos have disappeared from their albums using firefox but i am able to view them using safari. It only happened after i tried to delete one of the photos of myself.

I right clicked the photo and selected one of the options but can't remember the option was and then all the "photos of me" were gone and the other albums when opened up are empty, cannot view. I also cannot view any other photos on my wall when