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 Go to next message
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 #629855 is a reply to message #629853] Tue, 16 December 2014 13:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/


It is not possible to tell what the fix should be when we don't know exactly what the problem is.

>ORA-00942: table or view does not exist
Above gets thrown when either the table or view does not exist or due to lack of appropriate permission (GRANT was not issued)

Essentially you reported that your car does not go & asked us how to make your car go.
There are too many unknowns to give you specific & correct answer.

>When I tried with data pump i have got an error that the DB versions are different so export is not possible.
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.
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 Go to previous messageGo to next message
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 #629857 is a reply to message #629856] Tue, 16 December 2014 13:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
which USER is logged into the DB issuing the CREATE OR REPLACE FORCE VIEW "HElP_OWNER"."HElP_INVENTORY_VW" statement?

you can query ALL_ERRORS to see why this statement fails
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 Go to previous messageGo to next message
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 #629860 is a reply to message #629859] Tue, 16 December 2014 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from SQL below


select username from dba_users where username like 'HE%';

>I did it with SYS user

SYS should only be used for database maintenance; like version upgrade.

double quote marks should be avoided; since they can result in undesirable side effects.
Re: error in view while moving schema from db1 to db2 [message #629865 is a reply to message #629860] Tue, 16 December 2014 15:39 Go to previous messageGo to next message
nareshrockdude
Messages: 12
Registered: December 2014
Junior Member
For >

select username from dba_users where username like 'HE%';


SQL> select username from dba_users where username like 'HE%';

USERNAME
------------------------------
HELP_OWNER
HELP




Re: error in view while moving schema from db1 to db2 [message #629867 is a reply to message #629865] Tue, 16 December 2014 15:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
nareshrockdude wrote on Tue, 16 December 2014 13:39
For >

select username from dba_users where username like 'HE%';


SQL> select username from dba_users where username like 'HE%';

USERNAME
------------------------------
HELP_OWNER
HELP







which is different from "HElP_OWNER" & likely problematic
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 Go to previous messageGo to next message
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 #629873 is a reply to message #629871] Tue, 16 December 2014 16:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
which user is logged onto the DB & issuing CREATE OR REPLACE VIEW?

which user owns trans_location t?
which user owns operation o?
which user owns location l?
which user owns serial_number sn?
which user owns GGDEV_PLACES_W_CURRENT_FLAGGED?

Does logged on USER have SELECT against entitymanager_owner.cntyxref ct, entitymanager_owner.org_type ot ?
Re: error in view while moving schema from db1 to db2 [message #629874 is a reply to message #629873] Tue, 16 December 2014 16:30 Go to previous messageGo to next message
nareshrockdude
Messages: 12
Registered: December 2014
Junior Member
> which user is logged onto the DB & issuing CREATE OR REPLACE VIEW?

SYS user from DB2

> which user owns trans_location t?

locmanager_owner on DB1

> which user owns operation o?

locmanager_owner on DB1

> which user owns location l?

locmanager_owner on DB1

> which user owns serial_number sn?

entitymanager_owner on DB1

> which user owns GGDEV_PLACES_W_CURRENT_FLAGGED?

entitymanager_owner on DB1


> Does logged on USER have SELECT against entitymanager_owner.cntyxref ct, entitymanager_owner.org_type ot ?

yes



Re: error in view while moving schema from db1 to db2 [message #629875 is a reply to message #629874] Tue, 16 December 2014 16:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when SYS issues CREATE VIEW it fails because SYS can not find trans_location, operation, location, serial_number, & GGDEV_PLACES_W_CURRENT_FLAGGED
Re: error in view while moving schema from db1 to db2 [message #629878 is a reply to message #629875] Tue, 16 December 2014 17:03 Go to previous messageGo to next message
nareshrockdude
Messages: 12
Registered: December 2014
Junior Member
Thanks
but how can i able to resolve this?
Re: error in view while moving schema from db1 to db2 [message #629879 is a reply to message #629878] Tue, 16 December 2014 17:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
start by Reading The Fine Manual

https://docs.oracle.com/database/121/CNCPT/toc.htm

Re: error in view while moving schema from db1 to db2 [message #629890 is a reply to message #629874] Wed, 17 December 2014 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
> which user is logged onto the DB & issuing CREATE OR REPLACE VIEW?

SYS user from DB2


Read SYS is special.

Re: error in view while moving schema from db1 to db2 [message #629912 is a reply to message #629878] Wed, 17 December 2014 07:07 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
nareshrockdude wrote on Tue, 16 December 2014 17:03
Thanks
but how can i able to resolve this?


Your first step to resolve this is to STOP doing it while connected as SYS. Connecting as SYS should be a rare occurance, used only when no other account can do what is needed.
Re: error in view while moving schema from db1 to db2 [message #629916 is a reply to message #629912] Wed, 17 December 2014 08:13 Go to previous messageGo to next message
nareshrockdude
Messages: 12
Registered: December 2014
Junior Member
@EdStevens @Michel Cadot @BlackSwan

I understand the first step please tell me next steps to achieve change schema from DB1 to new schema in DB2 if you have any idea. (I want to avoid those errors and get clear output)
Re: error in view while moving schema from db1 to db2 [message #629918 is a reply to message #629916] Wed, 17 December 2014 08:37 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to run the objects in as the correct user in DB2 (not sys) and then see what errors you get if any.
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 Go to previous messageGo to next message
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 #629923 is a reply to message #629921] Wed, 17 December 2014 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> create or replace force view v as select * from foo;

Warning: View created with compilation errors.

SQL> show error view v
Errors for VIEW V:
LINE/COL ERROR
-------- ------------------------------------------------------
0/0      ORA-00942: table or view does not exist

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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 #629926 is a reply to message #629925] Wed, 17 December 2014 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use SHOW ERROR instead.

Re: error in view while moving schema from db1 to db2 [message #629927 is a reply to message #629926] Wed, 17 December 2014 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It appears that you do not know how to write correct syntax SQL statements.

post SQL & results that show all objects in FROM clause actually exist & that HELP_OWNER can access them.
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: error in view while moving schema from db1 to db2 [message #629930 is a reply to message #629928] Wed, 17 December 2014 12:17 Go to previous messageGo to next message
nareshrockdude
Messages: 12
Registered: December 2014
Junior Member
Thanks cookiemonster.
I will ask the other DBA to give access to new schema in DB2. Then I will try.

Re: error in view while moving schema from db1 to db2 [message #629931 is a reply to message #629930] Wed, 17 December 2014 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I will ask the other DBA to give access to new schema in DB2. Then I will try.


ask the other DBA to fix the bad SQL
Re: error in view while moving schema from db1 to db2 [message #629932 is a reply to message #629931] Wed, 17 December 2014 12:29 Go to previous messageGo to next message
nareshrockdude
Messages: 12
Registered: December 2014
Junior Member
Thanks Michel Cadot

For BlackSwan,

The other DBA said that they are working fine on the DB1.
Re: error in view while moving schema from db1 to db2 [message #629933 is a reply to message #629932] Wed, 17 December 2014 12:37 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The other DBA said that they are working fine on the DB1.

Then Problem Exists Between Keyboard And Chair
Previous Topic: utlrp - many invokes different results :/
Next Topic: Standby database-NOT SYNCH WITH PRIMARY even after standby rolling forward
Goto Forum:
  


Current Time: Thu Mar 28 05:23:56 CDT 2024