Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Location
    Bangalore
    Posts
    14

    Unanswered: private synonyms-help required

    Hi i have 4 schemas each containing about 800 tables.i am required to create alias users for these schemas and create private synonyms for the corresponding tables.creating the synonyms one by one is nopt practical coz i have oonly one day to do it.i am not familiar with pl/sql.can anyone kindly tell me how to generate the create synonym and grant scripts.

    i am using oracle 8.1.6 on aix4.3.
    Cheers
    Murali

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: private synonyms-help required

    Originally posted by dasari98
    Hi i have 4 schemas each containing about 800 tables.i am required to create alias users for these schemas and create private synonyms for the corresponding tables.creating the synonyms one by one is nopt practical coz i have oonly one day to do it.i am not familiar with pl/sql.can anyone kindly tell me how to generate the create synonym and grant scripts.

    i am using oracle 8.1.6 on aix4.3.
    Use ALL_TABLES to get the list of tables and generate CREATE SYNONYM and GRANT statements like this example:

    select 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||table_name||' TO xxx;'
    from all_tables
    where owner='THEOWNER';

    Spool the output to a file and then run the file in SQL Plus.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    read up on generating dynamic sql.

    fairly easy to do.
    read up on it or I can provide an example.
    basically for specific users, you need to add their user-prefic to the synonym name.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Mar 2003
    Location
    Bangalore
    Posts
    14
    Hi thanks,
    i've generated the script using the all_tables for grants and synonym creation.
    Cheers
    Murali

Posting Permissions

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