Create Synonyms for Entire Schema

This query generates another SQL query (output on left) that issues DROP and then CREATE SYNONYM commands. In this instance, it is doing it for the tables in ALL_CATALOG that begin with 'USER.' It doesn't actually run the code so you have a chance to evaluate if it's doing what you want.

The second part generates a list (output on right) of those synonyms that had to have a number attached to make a unique synonym. So, unless a table/view name appears on this list, you would just have to type the initials. For example to query User_Nested_Tables, type UNT. But, since there are User_Objects and User_Opancillary, you can't type UO; you must type UO1 or UO2.

--+- 1 -----------------------------------------------------------------
--| This data is pulled twice.  Once by the query generation, and a
--| second time by the report that shows like keys.
--+---------------------------------------------------------------------

CREATE GLOBAL TEMPORARY TABLE Global_Temp_TabKeys
 ON COMMIT PRESERVE ROWS
 AS
   Select         Table_Key||
          ------------------------------------------------------------
          -- When the number of like keys is greater than one, take
          -- the row_number and concatenate it to the key to get the
          -- synonym.
          ------------------------------------------------------------
                  Case When Count(*) Over(Partition By Table_Key)>1
                       Then Row_Number( ) Over(Partition By Table_Key
                                                   Order By Table_Key)
                  End As
          xSynonym,
          ---------------------------------------------------------
                   Count(*) Over(Partition By Table_Key) As
          Ctr,
          ---------------------------------------------------------
          Table_Name,
          ---------------------------------------------------------
          Table_Key
     From
          --+- 1.1 ---------------------------------------------------
          --| Create the key by getting the first character.
          --| If there is a 1st occurence of '_' in the name,
          --|      then pick up the next character.
          --| If there is a 2nd occurence of '_' in the name,
          --|      then pick up the next character.  Etc.
          --+---------------------------------------------------------
          (
           Select                    Substr(Table_Name,1,1)  ||
                           ---------------------------------------
                           Case When  Instr(Table_Name,'_',2,1) <> 0
                                Then Substr(Table_Name,
                                     (Instr(Table_Name,'_',2,1))+1,1)
                           End ||
                           ---------------------------------------
                           Case When  Instr(Table_Name,'_',2,2) <> 0
                                Then Substr(Table_Name,
                                     (Instr(Table_Name,'_',2,2))+1,1)
                           End ||
                           ---------------------------------------
                           Case When  Instr(Table_Name,'_',2,3) <> 0
                                Then Substr(Table_Name,
                                     (Instr(Table_Name,'_',2,3))+1,1)
                           End ||
                           ---------------------------------------
                           Case When  Instr(Table_Name,'_',2,4) <> 0
                                Then Substr(Table_Name,
                                     (Instr(Table_Name,'_',2,4))+1,1)
                           End
                           ---------------------------------------
                           As
                 Table_Key,
                 ---------------------------------------------------
                 Table_Name
                 ---------------------------------------------------
            From All_Catalog
           Where Table_Type In ('TABLE','VIEW')
             And Table_Name Like 'USER%'
          )
;

--+- 2 -----------------------------------------------------------------
--| This will generate a bit of code that will drop and create all
--| synonyms based on the keys created in the global temp table.
--+---------------------------------------------------------------------

Set Term Off
Set Pagesize 0

Column Table_Key Print
Column Semicolon Fold_After Format A10
Column xSynonym             Format A10

Spool CrSyn.Sql

Select 'Drop   Synonym',         -- spaced to match Create
       -------------------------------------------------
       xSynonym,
                 ';' As
       Semicolon,                -- named so I can fold after
       -------------------------------------------------
       'Create Synonym',
       -------------------------------------------------
       xSynonym,
       -------------------------------------------------
       'for ' || Table_Name || ';'
  From Global_Temp_TabKeys;

Spool Off

--+- 3 -----------------------------------------------------------------
--| Make a list of those tables that need a number to be unique.
--+---------------------------------------------------------------------

Spool SynLst.Txt
Break On Table_Key Skip 1
Column Table_Key &xPrint
Set Term On

Prompt
Prompt These Tables Have Liked-name Keys:
Prompt

Select xSynonym, Table_Name , Table_Key
  From Global_Temp_TabKeys
 Where Ctr > 1
 Order By xSynonym
/
Spool Off

Prompt
Prompt Your Sql code has been spool to CrSyn.Sql.
Prompt Execute it after you check and approve it.
Prompt
Prompt This report is SynLst.Txt
Prompt

TRUNCATE TABLE Global_Temp_TabKeys;
    DROP TABLE Global_Temp_TabKeys;