This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,901 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

If Table names changed will it affect DB link?

kalyan vedagiri
kalyan vedagiri Member Posts: 32 Red Ribbon

I have table in Database 1, Database 2 have access to it though DB link. If table name is changed in Database 1, Can Database 2 still access the table? will it affect DB link in any way?

Tagged:

Best Answers

  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    Answer ✓

    What does one have to do with the other? A dblink points from one server to another server, not to a specific table.

    Of course, to access the table, it will have to be referenced by the new name, but other than that, and if nothing else has changed, if the table was accessible before, it will still be accessible after the name change.

    Note that if access is granted to another user (or if another user grants access to their table to you), and then the table name changes, the grant follows the name change. This has nothing to do with dblink, anyway.

    Procedures that reference the table, and materialized views, etc., are not updated automatically; they will have to be updated manually. But this, too, has nothing to do with dblink.

    kalyan vedagiri
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    Answer ✓

    Here is a short demo. I am logged in as MATHGUY, and I will grant SELECT and UPDATE on a table I have in my schema. Then I will check what table privileges I have granted to SCOTT. Then I will rename the table (in my schema) and check again. As you can see in this example, the privilege is attached to the table, not to the name.

    show user
    
    USER is "MATHGUY"
    
    
    
    grant select, update on engines to scott;
    
    Grant succeeded.
    
    select table_name, privilege
    from   all_tab_privs
    where  grantor = 'MATHGUY' and grantee = 'SCOTT'
    ;
    
    TABLE_NAME      PRIVILEGE      
    --------------- ---------------
    ENGINES         SELECT         
    ENGINES         UPDATE
    
    
    
    alter table engines rename to motors;
    
    Table ENGINES altered.
    
    select table_name, privilege
    from   all_tab_privs
    where  grantor = 'MATHGUY' and grantee = 'SCOTT'
    ;
    
    TABLE_NAME      PRIVILEGE      
    --------------- ---------------
    MOTORS          SELECT         
    MOTORS          UPDATE
    
    


Answers

  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    Answer ✓

    What does one have to do with the other? A dblink points from one server to another server, not to a specific table.

    Of course, to access the table, it will have to be referenced by the new name, but other than that, and if nothing else has changed, if the table was accessible before, it will still be accessible after the name change.

    Note that if access is granted to another user (or if another user grants access to their table to you), and then the table name changes, the grant follows the name change. This has nothing to do with dblink, anyway.

    Procedures that reference the table, and materialized views, etc., are not updated automatically; they will have to be updated manually. But this, too, has nothing to do with dblink.

    kalyan vedagiri
  • kalyan vedagiri
    kalyan vedagiri Member Posts: 32 Red Ribbon

    Had a doubt if grant follows name change or we need to grant access again with new name. Thanks for clearing it out and confirming grant follows the name change.

  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    Answer ✓

    Here is a short demo. I am logged in as MATHGUY, and I will grant SELECT and UPDATE on a table I have in my schema. Then I will check what table privileges I have granted to SCOTT. Then I will rename the table (in my schema) and check again. As you can see in this example, the privilege is attached to the table, not to the name.

    show user
    
    USER is "MATHGUY"
    
    
    
    grant select, update on engines to scott;
    
    Grant succeeded.
    
    select table_name, privilege
    from   all_tab_privs
    where  grantor = 'MATHGUY' and grantee = 'SCOTT'
    ;
    
    TABLE_NAME      PRIVILEGE      
    --------------- ---------------
    ENGINES         SELECT         
    ENGINES         UPDATE
    
    
    
    alter table engines rename to motors;
    
    Table ENGINES altered.
    
    select table_name, privilege
    from   all_tab_privs
    where  grantor = 'MATHGUY' and grantee = 'SCOTT'
    ;
    
    TABLE_NAME      PRIVILEGE      
    --------------- ---------------
    MOTORS          SELECT         
    MOTORS          UPDATE