Here is how to replicate the error. Create two users with the needed system privileges. Each user creates a table and view. Test as SYS. The error occurs. user1 grants privileges to user2. Recreate the view. Works.
--setup users as SYS or another DBA user
CREATE USER user1 IDENTIFIED BY pw1;
CREATE USER user2 IDENTIFIED BY pw2;
GRANT CONNECT TO user1, user2;
GRANT CREATE TABLE TO user1, user2;
GRANT CREATE VIEW TO user1, user2;
GRANT UNLIMITED TABLESPACE TO user1, user2;
--connect as user1 and create user1 table and view
CREATE TABLE TABLE1
( COLUMN1A VARCHAR2(20) , COLUMN1B VARCHAR2(20) );
INSERT INTO user1.test1 VALUES ('ABC', 'DEF');
COMMIT;
SELECT * FROM user1.test1;
CREATE OR REPLACE VIEW user1.view1
AS SELECT * FROM user1.test1;
SELECT * FROM user1.view1;
--connect as user2 and create user2 table and view
CREATE TABLE TABLE2
( COLUMN2C VARCHAR2(20) , COLUMN2D VARCHAR2(20) );
INSERT INTO user2.table2 VALUES ('GHI', 'JKL');
COMMIT;
SELECT * FROM user2.table2;
CREATE OR REPLACE VIEW user2.view2
AS SELECT * FROM user2.table2
SELECT * FROM user2.view2;
--now user2 tries to select from user1 view
USER2>
select * from user1.view1;
select * from user1.view1
*
ERROR at line 1:
ORA-00942: table or view does not exist
Elapsed: 00:00:00.01
USER2>
CREATE OR REPLACE FORCE VIEW user2.view3
AS SELECT * FROM user1.view1;
Warning: View created with compilation errors.
USER2>
SELECT * FROM user2.view3;
select * from user2.view3
*
ERROR at line 1:
ORA-04063: view "USER2.VIEW3" has errors
--connect sys as sysdba
Enter password:
Connected.
SELECT * FROM user2.view3;
select * from user2.view3
*
ERROR at line 1:
ORA-04063: view "USER2.VIEW3" has errors
ALTER VIEW user2.view3 COMPILE;
Warning: View altered with compilation errors.
SELECT * FROM user2.view3;
select * from user2.view3
*
ERROR at line 1:
ORA-04063: view "USER2.VIEW3" has errors
CREATE OR REPLACE VIEW user2.view3
AS SELECT * FROM user1.test1;
select * from user1.test1
*
ERROR at line 3:
ORA-01031: insufficient privileges
-- now connect user1 and grant privileges
Enter password:
Connected.
USER1>
grant select, insert, update, delete on user1.test1 to user2;
GRANT succeeded.
--connect user2 to recreate the view and test again
Enter password:
Connected.
CREATE OR REPLACE FORCE VIEW user2.view3
AS SELECT * FROM user1.test1;
VIEW created.
SELECT * FROM user2.view3;
COLUMN1A COLUMN1B
---------- ----------
ABC DEF
1 ROW selected.
-- now it works. It was not the SYS as SYSDBA that did not have privileges. User2 did not have privileges to user1.test1 table.