Home » RDBMS Server » Server Administration » error in view while moving schema from db1 to db2 (DB2 is oracle 11.2.0.3 and DB1 is 10.2.0.4 and Unix OS)
| error in view while moving schema from db1 to db2 [message #629853] |
Tue, 16 December 2014 13:03  |
 |
nareshrockdude
Messages: 12
Registered: December 2014
|
Junior Member |
|
|
Hello Experts,
I want to change one schema in DB1 to a new schema with a user in DB2. When I tried with data pump i have got an error that the DB versions are different so export is not possible. In this case I got the DDL of the schema in DB1 and then run the DDL schema in DB2 with a new schema. Everything went well except with few views that consists another schema from DB1. I got 19 errors and all of them are views related. Could you give me the solution how can i do this without errors.
FYI, I have included the error messages that I got
GRANT SELECT ON "HELP_OWNER"."EFINDS_INVENTORY_VW" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.EFINDS_INVENTORY_VW" has errors
GRANT SELECT ON "HELP_OWNER"."GGDEV_LOCS_AVAILABLE_FOR_OPS" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.GGDEV_LOCS_AVAILABLE_FOR_OPS" has errors
GRANT SELECT ON "HELP_OWNER"."GGDEV_PLACES_W_CURRENT_FLAGGED" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.GGDEV_PLACES_W_CURRENT_FLAGGED" has errors
GRANT SELECT ON "HELP_OWNER"."GGDEV_VW_ALL_LOCATIONS" TO "HELP"
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON "HELP_OWNER"."GGDEV_VW_COUNTY_EVACUATION" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.GGDEV_VW_COUNTY_EVACUATION" has errors
GRANT SELECT ON "HELP_OWNER"."GGDEV_VW_GENDER_CROSSTAB" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.GGDEV_VW_GENDER_CROSSTAB" has errors
GRANT SELECT ON "HELP_OWNER"."LOCATION" TO "HELP"
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON "HELP_OWNER"."LOCS_AVAILABLE_FOR_OPS" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.LOCS_AVAILABLE_FOR_OPS" has errors
GRANT SELECT ON "HELP_OWNER"."PAT_TRACK_RPT" TO "HELP"
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON "HELP_OWNER"."VW_AGE_EVAC_RECEIVED" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.VW_AGE_EVAC_RECEIVED" has errors
GRANT SELECT ON "HELP_OWNER"."VW_AGE_EVAC_RECEIVED_OLD" TO "HELP"
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON "HELP_OWNER"."VW_ALL_LOCATIONS" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.VW_ALL_LOCATIONS" has errors
GRANT SELECT ON "HELP_OWNER"."VW_COUNTY_EVACUATION" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.VW_COUNTY_EVACUATION" has errors
GRANT SELECT ON "HELP_OWNER"."VW_EVACUATIONS" TO "HELP"
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON "HELP_OWNER"."VW_GENDER_CROSSTAB" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.VW_GENDER_CROSSTAB" has errors
GRANT SELECT ON "HELP_OWNER"."VW_GENDER_EVAC_RECEIVED" TO "HELP"
*
ERROR at line 1:
ORA-00942: table or view does not exist
GRANT SELECT ON "HELP_OWNER"."VW_LOCATIONS_TMSTMP" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.VW_LOCATIONS_TMSTMP" has errors
GRANT SELECT ON "HELP_OWNER"."VW_NOACTIONFACILITY" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.VW_NOACTIONFACILITY" has errors
GRANT SELECT ON "HELP_OWNER"."VW_REPATRIATED" TO "HELP"
*
ERROR at line 1:
ORA-00942: table or view does not exist
[Updated on: Tue, 16 December 2014 13:10] Report message to a moderator |
|
|
|
|
|
| Re: error in view while moving schema from db1 to db2 [message #629856 is a reply to message #629855] |
Tue, 16 December 2014 13:39  |
|
nareshrockdude
Messages: 12
Registered: December 2014
|
Junior Member |
|
|
Thanks for the fast reply.
I have tested the views on DB1 they are working fine. When I try to recreate them on new schema on DB2 it is giving me this error
SQL> CREATE OR REPLACE FORCE VIEW "HElP_OWNER"."HElP_INVENTORY_VW" ("ID1", "NAME", "CTY_NAME", "CENSUS", "ORG_DESC", "TRANS_DATE", "TRANS_DATE_TXT", "TRANS_DATE_FMT") AS
select l.id1, l.name, ct.cty_name, count(sn.code) as Census, ot.org_desc, max(sn.tmstmp) as Trans_Date,
2 3 max(to_char(sn.tmstmp, 'MM/DD/YYYY')) as Trans_date_txt,max(to_char(sn.tmstmp, 'DD-Mon-YYYY')) as Trans_date_fmt
4 from location l, serial_number sn, entitymanager_owner.cntyxref ct, entitymanager_owner.org_type ot
5 where l.county_fips = ct.county_fips
6 and sn.location_id = l.id
7 and l.org_type in (4,5,37,44,79,120,121,122,123,127)
8 and l.org_type = ot.org_type
9 group by l.id1, l.name, ct.cty_name, ot.org_desc
10 order by ot.org_desc, l.name;
Warning: View created with compilation errors.
> I suspect the DB version that was exported is higher than the DB version that was imported into.
expdp help=yes
set VERSION then impdp will not throw error.
The exported database(DB1) is lower version that was the issue.(I am sorry I mentioned wrong in the post, I want to change the schema from DB1(10.2.0.4) to DB2(11.2.0.3))
[Updated on: Tue, 16 December 2014 13:39] Report message to a moderator |
|
|
|
|
|
| Re: error in view while moving schema from db1 to db2 [message #629859 is a reply to message #629857] |
Tue, 16 December 2014 14:09 |
|
nareshrockdude
Messages: 12
Registered: December 2014
|
Junior Member |
|
|
I did it with SYS user
I did not find any information in ALL_ERRORS(its just told me the same mentioned information in the post but in Organized manner owner,name,type,text...etc.)
The View is included with other schema's(here Entitymanager_Owner) from the source database(DB1). Is this the reason? If it is then how can I proceed further?
and also the other views does not have schema(Entitymanager_Owner) included then what is going to be the reason?
If you need any information(on this) please let me know I can able to give.
Thanks
[Updated on: Tue, 16 December 2014 14:11] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: error in view while moving schema from db1 to db2 [message #629871 is a reply to message #629867] |
Tue, 16 December 2014 15:55 |
|
nareshrockdude
Messages: 12
Registered: December 2014
|
Junior Member |
|
|
Sorry there was a type mistake when I post it in the code tag. here is the actual view(FYI, I will provide two views out of 19 for which I am getting errors). by the way its not the typo mistake.
CREATE OR REPLACE FORCE VIEW "HELP_OWNER"."HELP_INVENTORY_VW" ("ID1", "NAME", "CTY_NAME", "CENSUS", "ORG_DESC", "TRANS_DATE", "TRANS_DATE_TXT", "TRANS_DATE_FMT") AS
select l.id1, l.name, ct.cty_name, count(sn.code) as Census, ot.org_desc, max(sn.tmstmp) as Trans_Date,
max(to_char(sn.tmstmp, 'MM/DD/YYYY')) as Trans_date_txt,max(to_char(sn.tmstmp, 'DD-Mon-YYYY')) as Trans_date_fmt
from location l, serial_number sn, entitymanager_owner.cntyxref ct, entitymanager_owner.org_type ot
where l.county_fips = ct.county_fips
and sn.location_id = l.id
and l.org_type in (4,5,37,44,79,120,121,122,123,127)
and l.org_type = ot.org_type
group by l.id1, l.name, ct.cty_name, ot.org_desc
order by ot.org_desc, l.name;
CREATE OR REPLACE FORCE VIEW "HELP_OWNER"."GGDEV_LOCS_AVAILABLE_FOR_OPS" ("ID", "ORG_TYPE", "ID1", "NAME", "ADDR1", "ADDR2", "CITY", "COUNTY_FIPS", "STATE", "ZIP", "PHONE", "AGENCY", "ORGTYPEDESC", "BEGIN_DATE", "END_DATE", "CURRENT_FLAG", "OPERATION_ID", "OP_BEGIN") AS
SELECT ID,
ORG_TYPE,
ID1,
NAME,
ADDR1,
ADDR2,
CITY,
COUNTY_FIPS,
STATE,
ZIP,
PHONE,
AGENCY,
ORGTYPEDESC,
BEGIN_DATE,
END_DATE,
CURRENT_FLAG,
operation_id,
op_begin
FROM
(SELECT z.id,
z.org_type,
z.id1,
z.name,
z.addr1,
z.addr2,
z.city,
z.county_fips,
z.state,
z.zip,
z.phone,
z.agency,
z.orgTypeDesc,
z.begin_date,
z.end_date,
z.CURRENT_FLAG,
o.id AS operation_id,
o.begin_date AS op_begin
FROM GGDEV_PLACES_W_CURRENT_FLAGGED z,
Operation o
WHERE o.begin_date >= z.begin_date
AND o.begin_date < z.end_date
AND (o.end_date < z.end_date
OR o.end_date IS NULL)
)
UNION ALL
(SELECT concat('T',t.id) AS id,
t.org_type,
NULL AS id1,
t.name,
t.addr1,
t.addr2,
t.city,
CAST(t.county_fips AS VARCHAR2(3)) county_fips,
t.state,
t.zip,
t.phone,
'NYSDOH' AS agency,
'Temporary' AS orgTypeDesc,
NULL AS begin_date,
NULL AS end_date,
1 AS CURRENT_FLAG,
o.id AS operation_id,
o.begin_date AS op_begin
FROM trans_location t,
operation o
);
GRANT SELECT ON "HELP_OWNER"."GGDEV_LOCS_AVAILABLE_FOR_OPS" TO "HELP";
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: error in view while moving schema from db1 to db2 [message #629921 is a reply to message #629918] |
Wed, 17 December 2014 08:51 |
|
nareshrockdude
Messages: 12
Registered: December 2014
|
Junior Member |
|
|
still the same error cookiemonster
SQL> show user;
USER is "HELP_OWNER"
SQL> CREATE OR REPLACE FORCE VIEW "HELP_OWNER"."VW_ALL_LOCATIONS" ("ID", "ORG_TYPE", "ID1", "NAME", "ADDR1", "ADDR2", "CITY", "COUNTY_FIPS", "STATE", "ZIP", "PHONE", "AGENCY", "ORGTYPEDESC", "BEGIN_DATE", "END_DATE", "CURRENT_FLAG") AS
(SELECT concat('P',concat(z. 2 org_type,id1)) AS id,
3 z.org_type,
4 z.id1,
5 z.name,
6 z.address_1 AS addr1,
7 z.address_2 AS addr2,
8 z.town_city AS city,
9 CAST(z.county_fips AS VARCHAR2(3)) county_fips,
10 z.state_province AS state,
11 z.postal_code AS zip,
12 z.phone,
13 m.agency,
14 m.dscr AS orgTypeDesc,
15 z.begin_date,
16 z.end_date,
17 z.current_flag
18 FROM ENTITYMANAGER_OWNER.PLACES_WITH_CURRENT_FLAGGED z,
19 ORG_META m
20 WHERE z.end_date >= TO_DATE('01/01/2012', 'MM/DD/YYYY')
21 AND
22 z.org_type = m.org_type)
23 UNION ALL
24 (SELECT concat('T',id) AS id,
25 org_type,
26 NULL AS id1,
27 name,
28 addr1,
29 addr2,
30 city,
31 CAST(county_fips AS VARCHAR2(3)) county_fips,
32 state,
33 zip,
34 phone,
35 'NYSDOH' AS agency,
36 'Temporary' AS orgTypeDesc,
37 NULL AS begin_date,
38 TO_DATE('06/06/2079', 'MM/DD/YYYY') AS end_date,
39 decode(status, 'Active', 1, 'Inactive', 0, 1) AS CURRENT_FLAG
40 FROM trans_location
41 );
Warning: View created with compilation errors.
SQL> GRANT SELECT ON "HELP_OWNER"."VW_ALL_LOCATIONS" TO "HELP";
GRANT SELECT ON "HELP_OWNER"."VW_ALL_LOCATIONS" TO "HELP"
*
ERROR at line 1:
ORA-04063: view "HELP_OWNER.VW_ALL_LOCATIONS" has errors
help me
|
|
|
|
|
|
| Re: error in view while moving schema from db1 to db2 [message #629924 is a reply to message #629923] |
Wed, 17 December 2014 09:02 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Or query user_errors.
You need to find out what the errors are, and fix them.
If you have a view that works in one DB but not another then that's almost certainly because the view is referencing objects that either don't exist, or the view owner lacks the correct privileges/synonyms to see them.
|
|
|
|
| Re: error in view while moving schema from db1 to db2 [message #629925 is a reply to message #629924] |
Wed, 17 December 2014 09:30 |
|
nareshrockdude
Messages: 12
Registered: December 2014
|
Junior Member |
|
|
I did query user_errors. I found these
SQL> select name,text from user_errors
2 ;
NAME TEXT
------------------------------ ------------------------------
VW_NOACTIONFACILITY ORA-00942: table or view does
not exist
VW_LOCATIONS_TMSTMP ORA-00942: table or view does
not exist
VW_GENDER_CROSSTAB ORA-00942: table or view does
not exist
VW_COUNTY_EVACUATION ORA-00942: table or view does
not exist
VW_ALL_LOCATIONS_OLD ORA-00942: table or view does
not exist
VW_ALL_LOCATIONS ORA-00942: table or view does
not exist
VW_AGE_EVAC_RECEIVED ORA-00942: table or view does
not exist
SP_POPULATE_PAT_TRACK_RPT PL/SQL: ORA-04063: view "HELP
_OWNER.PAT_TRACK_RPT" has err
ors
SP_POPULATE_PAT_TRACK_RPT PL/SQL: SQL Statement ignored
SP_POPULATE_PAT_TRACK_RPT PL/SQL: ORA-04063: view "HELP
_OWNER.PAT_TRACK_RPT" has err
ors
SP_POPULATE_PAT_TRACK_RPT PL/SQL: SQL Statement ignored
SP_POPULATE_PAT_TRACK_RPT PL/SQL: ORA-04063: view "EFIND
S_OWNER.LOCATION" has errors
SP_POPULATE_PAT_TRACK_RPT PL/SQL: SQL Statement ignored
SP_POPULATE_PAT_TRACK_RPT PLS-00201: identifier 'UTL_MAI
L.SEND' must be declared
SF_GETEVACUATIONS PL/SQL: SQL Statement ignored
SF_GETEVACUATIONS PLS-00364: loop index variable
'CREC' use is invalid
SF_GETEVACUATIONS PL/SQL: Statement ignored
SF_GETEVACUATIONS PLS-00364: loop index variable
'CREC' use is invalid
SF_GETEVACUATIONS PL/SQL: Statement ignored
SF_GETEVACUATIONS PLS-00364: loop index variable
'CREC' use is invalid
SF_GETEVACUATIONS PL/SQL: Statement ignored
SF_GETEVACUATIONS PLS-00364: loop index variable
'CREC' use is invalid
LOCS_AVAILABLE_FOR_OPS ORA-00942: table or view does
not exist
LOCATION ORA-00942: table or view does
not exist
GGDEV_VW_GENDER_CROSSTAB ORA-00942: table or view does
not exist
GGDEV_VW_COUNTY_EVACUATION ORA-00942: table or view does
not exist
GGDEV_VW_ALL_LOCATIONS ORA-04063: view "HELP_OWNER.
GGDEV_PLACES_W_CURRENT_FLAGGED
" has errors
GGDEV_VW_AGE_EVAC_RECEIVED ORA-00942: table or view does
not exist
GGDEV_PLACES_W_CURRENT_FLAGGED ORA-02019: connection descript
ion for remote database not fo
und
GGDEV_LOCS_AVAILABLE_FOR_OPS ORA-04063: view "HELP_OWNER.
GGDEV_PLACES_W_CURRENT_FLAGGED
" has errors
HELP_INVENTORY_VW ORA-00942: table or view does
not exist
37 rows selected.
[Updated on: Wed, 17 December 2014 09:31] Report message to a moderator |
|
|
|
|
|
|
|
| Re: error in view while moving schema from db1 to db2 [message #629928 is a reply to message #629927] |
Wed, 17 December 2014 09:49 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So you've got a lot of views that reference objects that don't exist or the view owner can't see.
You got some other views that don't compile because they reference the first set of views.
You need to fix all the ORA-00942 errors.
There are lots of ways to work out what the culprit is:
a) run the view script into sqlplus by itself and then type show errors.
b) look the columns in user_errors that show which line/position the error comes from.
c) view the view in a GUI like SQLDeveloper - it'll show the errors.
So find out which objects it's complaining about and then add the missing objects/grants.
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 17 23:34:00 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
