Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    5

    Unanswered: name is already used by an existing object

    Hi

    I am trying to execute this command

    "Create SYNONYM temp_master for dba.temp_master"


    I am getting error

    "name is already used by an existing object"

    Can somebody help me out

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    There is already an object there named temp_master...
    If you are logged on as DBA, then you cannot create a synonym
    (unless it is a public synonym) with the same name as the table.

    sql> select owner,object_type,object_name from all_objects
    where object_name = 'TEMP_MASTER';

    This will show you what you have out there for objects ...

    sql> select owner,synonym_name,table_owner,table_name from
    all_synonyms where synonym_name = 'TEMP_MASTER';
    OR
    sql> select owner,synonym_name,table_owner,table_name from
    all_synonyms where table_name = 'TEMP_MASTER';

    Gregg

  3. #3
    Join Date
    Apr 2003
    Posts
    5
    Originally posted by gbrabham
    There is already an object there named temp_master...
    If you are logged on as DBA, then you cannot create a synonym
    (unless it is a public synonym) with the same name as the table.

    sql> select owner,object_type,object_name from all_objects
    where object_name = 'TEMP_MASTER';

    This will show you what you have out there for objects ...

    sql> select owner,synonym_name,table_owner,table_name from
    all_synonyms where synonym_name = 'TEMP_MASTER';
    OR
    sql> select owner,synonym_name,table_owner,table_name from
    all_synonyms where table_name = 'TEMP_MASTER';

    Gregg

  4. #4
    Join Date
    Apr 2003
    Posts
    5
    Can we drop the exisiting one and then create a new one ?

    Originally posted by gbrabham
    There is already an object there named temp_master...
    If you are logged on as DBA, then you cannot create a synonym
    (unless it is a public synonym) with the same name as the table.

    sql> select owner,object_type,object_name from all_objects
    where object_name = 'TEMP_MASTER';

    This will show you what you have out there for objects ...

    sql> select owner,synonym_name,table_owner,table_name from
    all_synonyms where synonym_name = 'TEMP_MASTER';
    OR
    sql> select owner,synonym_name,table_owner,table_name from
    all_synonyms where table_name = 'TEMP_MASTER';

    Gregg

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If the synonym points to an old object that you want to replace with
    the new temp_master table, then yes...

    sql> drop synonym temp_master;

    If it is a public synonym then,

    sql> drop public synonym temp_master;

    ....

    I would ensure that removing the synonym is what you want to do, ie,
    it is not an object in another schema that is needed somewhere ...

  6. #6
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Thumbs up

    There could be a either PUBLIC synonym or local user synonym.

    In your case is most probably PUBLIC one. Oracle first checks the lublic synonyms, then in the local user schema.


    Hope that helps,

    clio_usa
    OCP - DBA
    Visit our Web Site

Posting Permissions

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