If Table names changed will it affect DB link?

kalyan vedagiri

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?

mathguy

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.

mathguy

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

mathguy

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

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

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