1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using one global temporary table for multiple schemas

Discussion in 'General' started by Mariane, Dec 21, 2009.

  1. Mariane

    Mariane Guest

    Hi there,

    I'm new to using global temporary tables in Oracle.
    I'm using Ora cle 10g R2.
    I have multiple schema's in the database. Each schema has its own set of database objects (tables, views, procedures ... etc).
    One of the schemas has a global temporary table which is filled with a stored procedure.
    If I want to use the global temporary table and the stored procedure in another schema ( or maybe more schemas in the future) what is the best practice?
    Is it better to grant insert, select, update, delete rights on the temporary table to the other schemas or should I create the global temp table for the other schema(-s)?

    Thanks in advance.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    By definition a global temporary table is visible and accessible to all sessions; the restriction is that only the data inserted by the current session is visible to that session. You will, of course, need to grant execute on the stored procedure that populates this global temporary table to the users who will use it.