Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: error import for multiple tablespaces

    Hi,
    I have 2 instances oracle (db1, db2) with 2 user2 (tom,sam), tom see the 2 tables of sam with synonyms (create public synonym AD for sam.AD), (create public synonym AC for sam.AC)
    I'd like to create just one instance db1 with 2 users (tom, tom1), in 2 tablespaces and users (sam, sam1).

    I exported the istances:

    exp system/manager@db2 full=y file=x.dmp


    sam is owner of 2 tables "ad", "ac" and this table are in one file dmp (tbls.dmp)

    Tablespace tb1 is present, after I created tb2 on instance db1.
    connect system/manager@db1
    CREATE TABLESPACE tb2 DATAFILE tb02.dbf' SIZE 2500M REUSE DEFAULT STORAGE
    (INITIAL 1024 NEXT 1024 MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0) ONLINE;


    CREATE USER sam1 IDENTIFIED BY sam1 DEFAULT TABLESPACE tb2 TEMPORARY TABLESPACE TEMP;
    CREATE USER tom1 IDENTIFIED BY tom1 DEFAULT TABLESPACE tb2 TEMPORARY TABLESPACE TEMP;

    CREATE ROLE tom1_ROLE;
    CREATE ROLE tom1_USER;
    REVOKE tom1_ROLE FROM SYSTEM;
    GRANT CONNECT, RESOURCE, CREATE PUBLIC SYNONYM TO tom1;
    GRANT CONNECT, RESOURCE TO sam1;
    GRANT tom1_ROLE TO SYSTEM WITH ADMIN OPTION;
    GRANT tom1_ROLE TO sam1;
    GRANT tom1_ROLE TO tom1;

    NOW, my real problem is on import of dmp.

    imp system/manager@db1 fromuser=sam touser=sam1 file=tbls.dmp log=ERROR.log

    this import is correct

    imp system/manager@db1 fromuser=tom touser=tom1 file=x.dmp log=ERROR1.log

    this import create this error:
    ALTER TABLE "CAT" ADD CONSTRAINT "CAT_GROUP" FOREIGN KEY"
    " ("SECURITY_GROUP") REFERENCES "sam"."ac" ("GROUP_NAME") ENA"
    "BLE NOVALIDATE"
    IMP-00003: Riscontrato errore ORACLE 942
    ORA-00942: tabella o vista inesistente
    IMP-00017: l'istruzione seguente non riuscita causa errore ORACLE 942:
    "ALTER TABLE "CLASS" ADD CONSTRAINT "CLASS_SECURITY" FOREIGN K"
    "EY ("SECURITY_GROUP") REFERENCES "sam"."ac" ("GROUP_NAME") E"
    "NABLE NOVALIDATE"
    IMP-00003: Riscontrato errore ORACLE 942
    ORA-00942: tabella o vista inesistente
    IMP-00017: l'istruzione seguente non riuscita causa errore ORACLE 942:
    "ALTER TABLE "FLDS" ADD CONSTRAINT "FLDS_GROUP" FOREIGN KEY ("E"
    "DIT_GROUP") REFERENCES "sam"."ac" ("GROUP_NAME") ENABLE NOVA"
    "LIDATE"
    IMP-00003: Riscontrato errore ORACLE 942
    ORA-00942: tabella o vista inesistente

    THIS ERROR BECAUSE tom1 DON'T SEE TABLES "AD" and "AC", and can't add these constraints.

    How can tom1 see these tables.
    If I create public synonyms oracle tell me that this synonym exist.

    Thanks in advance.
    Raf

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    public synonyms are necessary but not sufficient . you have to grant select , update, delete ... to tom1; from user sam1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •