Results 1 to 9 of 9

Thread: moving tables

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: moving tables

    If I have to move tables from a different schema with all the PK and FK, how can I do it. Using a DBLINK copies only the table structure and data right. How can I copy the constraints at the same time?

    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    export/import
    or
    create an empty schema with all objects, then disable constraints, dblink-load the data and then enable all constraints
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    Thanks so much Duck.

    I have another question. I have 2 schemas, SCH1, SYSADM on database db1.
    I'm getting tables from schema SCX on database DBX TO SYSADM.
    I'm using these tables in my code on SCH1.

    Do I have to create synonyms in SCH1 to use the tables in SYSADM and then grant these tables to SCH1 from SYSADM or create a DBLINK to get these tables from SYSADM to SCH1? In some conversation with my Manager, he said you create synonyms only for reporting. I didnt know how am I supposed to use the tables in SYSADM when I'm working on SCH1?
    Hope I'm not confusing you.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I would suggest both. You have to make the database link if you are accessing data on another instance, but I would make local synonyms for ease of coding that point to the tables on the other side of the database link.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    Thank you very much.

    Another question again.
    I created the synonyms yesterday giving the necessary grants to SCH1 from SYSADM....
    I created the indexes today on tables in SYSADM. Do I need to create the synonyms in SCH1 or give the grants from SYSADM TO SCH1 again for the indexes to be effective?
    Last edited by nandinir; 01-23-07 at 17:03.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    No, if you can access the tables you will automatically use the indexes.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jul 2005
    Posts
    276
    Thanks again !!!

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    after the load, analyze the schema:
    exec dbms_stats.gather_schema_stats (ownname => 'SCHEMA_NAME', cascade => TRUE);
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jul 2005
    Posts
    276
    Thanks Duck !!

Posting Permissions

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