Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Grants On Remote User Tables.., Table does not exist

Posted on 2006-05-16
7
Medium Priority
?
914 Views
Last Modified: 2012-05-05

i'm working with 9i AS, R2 .. i hav two databases located in two servers
i have user b in database b that user have grants and read some of the user a in database a tables ..
the two databases in linked through Database Link...

when i import user B tables to database B an error occurs.

[QUOTE]IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "EMP_ALL_DEDS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist[QUOTE]

how can i virsualy mounted the remote user in Local database...


this is all the log>>>

[CODE]Username:
Connected to: Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by PAY, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing PAY's objects into CVPAY
. . importing table "ACCOUNTS" 137 rows imported
. . importing table "ALLDED_DEDUCTIONS" 260 rows imported
. . importing table "ALLOWS" 1949 rows imported
. . importing table "ALLSDEDS" 282 rows imported
. . importing table "BANKS" 10 rows imported
. . importing table "CENTER" 10 rows imported
. . importing table "DEDUCTS" 1478 rows imported
. . importing table "DMASTER" 1174 rows imported
. . importing table "DTRUNCATE" 0 rows imported
. . importing table "ECASH" 0 rows imported
. . importing table "EMPALLDED" 67702 rows imported
. . importing table "EMPS" 2 rows imported
. . importing table "EMPS1" 0 rows imported
. . importing table "EMP_ALL_DEDS" 1538 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "EMP_ALL_DEDS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT UPDATE ON "EMP_ALL_DEDS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT DELETE ON "EMP_ALL_DEDS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
. . importing table "EMP_JOB" 1 rows imported
. . importing table "FACTORS" 232 rows imported
. . importing table "FACTOR_TYPES" 22 rows imported
. . importing table "FORM_HEDAER" 0 rows imported
. . importing table "GRADE" 33 rows imported
. . importing table "HDMASTER" 0 rows imported
. . importing table "HFACTORS" 1215 rows imported
. . importing table "HLOANS_DETS" 5678 rows imported
. . importing table "H_ALLDED" 283 rows imported
. . importing table "H_ALLO" 126 rows imported
. . importing table "H_ALL_DED" 8192 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT DELETE ON "H_ALL_DED" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "H_ALL_DED" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT UPDATE ON "H_ALL_DED" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
. . importing table "H_DEDS" 157 rows imported
. . importing table "JOBS" 0 rows imported
. . importing table "LABOUR" 0 rows imported
. . importing table "LOANSES" 47 rows imported
. . importing table "LOANS_DETS" 492 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "LOANS_DETS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT UPDATE ON "LOANS_DETS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT DELETE ON "LOANS_DETS" TO "PERSONEL""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'PERSONEL' does not exist
. . importing table "MANAGE" 171 rows imported
. . importing table "MASTER" 171 rows imported
. . importing table "OVER" 0 rows imported
. . importing table "OVER1" 161 rows imported
. . importing table "OVERTAX" 158 rows imported
. . importing table "PARM" 116 rows imported
. . importing table "SALARY" 124832 rows imported
. . importing table "SALARY1" 12036 rows imported
. . importing table "SALHAF" 512 rows imported
. . importing table "SALNET" 512 rows imported
. . importing table "SALNET_H" 0 rows imported
. . importing table "SAL_HAF_H" 0 rows imported
. . importing table "SAS" 1 rows imported
. . importing table "SECTIONS" 25 rows imported
. . importing table "SECU" 1 rows imported
. . importing table "TAFKITAS" 33 rows imported
. . importing table "TAXSES" 0 rows imported
. . importing table "TAXZAKS" 512 rows imported
. . importing table "TRUNCATES" 0 rows imported
. . importing table "UNIT" 0 rows imported
. . importing table "WINDOWS" 3 rows imported
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "FACTORS" ADD FOREIGN KEY ("EMP_NO") REFERENCES "PERSONEL"."EMP"
"LOYEES" ("EMP_NO") ENABLE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "H_ALL_DED" ADD FOREIGN KEY ("EMP_NO") REFERENCES "PERSONEL"."E"
"MPLOYEES" ("EMP_NO") ENABLE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"ALTER TABLE "ECASH" ADD FOREIGN KEY ("EMP_NO") REFERENCES "PERSONEL"."EMPLO"
"YEES" ("EMP_NO") ENABLE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_002" BEFORE INSERT ON PAY.H_ALLO REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" INSERT INTO H_ALLDED(GRADE,CODE,NAME,TAX_IND,DAY_IND,VAL,ACC_NO,FLAG)"
" VALUES (:NEW.GRADE,:NEW.CODE,:NEW.NAME,:NEW.TAX_IND,:NEW.DAY_IND,:NEW.VAL"
",:NEW.ACC_NO,:NEW.FLAG);"
"END;"
""
""
""
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_003" BEFORE UPDATE ON PAY.H_ALLO REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW BEGIN"
"UPDATE H_ALLDED SET VAL=:NEW.VAL,NAME=:NEW.NAME,TAX_IND=:NEW.TAX_IND,DAY_IN"
"D=:NEW.DAY_IND,FLAG=:NEW.FLAG "
"WHERE GRADE=:NEW.GRADE AND CODE=:NEW.CODE AND ACC_NO=:NEW.ACC_NO;"
"END;"
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_004" BEFORE DELETE ON PAY.H_ALLO REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW BEGIN"
" DELETE FROM H_ALLDED"
" WHERE GRADE=:OLD.GRADE AND CODE=:OLD.CODE AND ACC_NO=:OLD.ACC_NO;"
"END;"
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_005" AFTER DELETE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
"DELETE FROM PAY.EMP_ALL_DEDS "
"WHERE EMP_NO = :NEW.EMP_NO AND :NEW.BAN_CODE=0;"
"END;"
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_006" AFTER DELETE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
"DELETE FROM PAY.LOANS_DETS "
"WHERE EMP_NO = :NEW.EMP_NO AND :NEW.BAN_CODE=0;"
"END;"
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_007" AFTER DELETE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
"DELETE FROM PAY.H_ALL_DED "
"WHERE EMP_NO = :NEW.EMP_NO AND :NEW.BAN_CODE=0;"
"END;"
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_108" AFTER UPDATE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" IF :NEW.SUSPEND= 'ä' THEN"
"UPDATE PAY.LOANS_DETS SET IND = 'ä'"
"WHERE EMP_NO = :NEW.EMP_NO;"
"END IF;"
"END;"
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_109" AFTER UPDATE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" IF :NEW.SUSPEND= 'á' THEN"
"UPDATE PAY.LOANS_DETS SET IND = ' '"
"WHERE EMP_NO = :NEW.EMP_NO;"
"END IF;"
"END;"
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_110" AFTER UPDATE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" IF :NEW.SUSPEND= 'ä' THEN"
"UPDATE PAY.EMP_ALL_DEDS SET IND = 'ä'"
"WHERE EMP_NO = :NEW.EMP_NO;"
"END IF;"
"END;"
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_112" AFTER UPDATE ON PERSONEL.EMPLOYEES REFERE"
"NCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" IF :NEW.SUSPEND= 'á' THEN"
"UPDATE PAY.EMP_ALL_DEDS SET IND = ' '"
"WHERE EMP_NO = :NEW.EMP_NO;"
"END IF;"
"END;"
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_228" BEFORE INSERT ON PAY.H_DEDS REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW DECLARE"
"X NUMBER;"
"BEGIN"
" INSERT INTO H_ALLDED(GRADE,CODE,NAME,VAL,ACC_NO,FLAG)"
" VALUES (:NEW.GRADE,:NEW.CODE,:NEW.NAME,:NEW.VAL,:NEW.ACC_NO,:NEW.FLAG);"
"END;"
""
""
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_231" BEFORE UPDATE ON PAY.H_DEDS REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW "
""
""
""
"BEGIN"
" UPDATE H_ALLDED SET VAL=:NEW.VAL,NAME=:NEW.NAME,FLAG=:NEW.FLAG"
" WHERE GRADE=:NEW.GRADE AND CODE=:NEW.CODE AND ACC_NO=:NEW.ACC_NO;"
"END;"
""
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 942:
"CREATE TRIGGER "CVPAY"."TRIG_232" BEFORE DELETE ON PAY.H_DEDS REFERENCING N"
"EW AS NEW OLD AS OLD FOR EACH ROW "
""
""
"BEGIN"
" DELETE FROM H_ALLDED"
" WHERE GRADE=:OLD.GRADE AND CODE=:OLD.CODE AND ACC_NO=:OLD.ACC_NO;"
"END;"
""
""
""
""
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
Import terminated successfully with warnings.
[CODE]

thanks for help...

sorry it seems i post in the wrong place...

Regards
0
Comment
Question by:linuxo
  • 3
  • 2
  • 2
7 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 16689138
Hello linuxo,

error is streightforward I think.

you have either to specify existing schema or to create one with this name...

HTH

I
0
 
LVL 4

Author Comment

by:linuxo
ID: 16689505
Hello,

the question is who can i specify a remote schema ...
0
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 16689586
Hi linuxo,

you cannot import tables localy and use a remote shcema. You could link tables (grant) to remote schema or remote object to local schema, but the owner cannot be a remote user - if I understood correctly the question.

regards

I
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 16692151
Database links allow two (or more) different Oracle databases to share information with each other, but you cannot grant access on a table in database "a" to a user (or role) that only exists in database "b".  Grants can only be given to users or roles within each database.

When you use a database link, like from database "a" to get to tables in database "b", you have to connect to database "b" with a username that is valid in database "b".  Through the link then, you will be able to have the same access as that uses does when that user connects locally to database "b".

It is not clear to me why you are doing an export and import.  If you plan to use the link, you don't need to export the data from one database to the other, right?
0
 
LVL 4

Author Comment

by:linuxo
ID: 16694792
Hello,

the Idea is to create a new user in database b with the same  database user in (a) schema and relations.
but in a the primary schema is existed what make everything goes fine, user B should use the primary user a in a database...

ok letme c.

0
 
LVL 4

Author Comment

by:linuxo
ID: 16703068
hi,

the problem is solved with....

I use the Database link and create synonym for schema i want to by available...
every thing is fine now..
when i import the DUMP file everything is ok...


regards
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 16703738
So, can you accept one (or more) of the comments here as the "answer" to close this question?  Or, do you plan to ask Community Support to cancel this question and refund your points?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question