一、起因及难点
在进行Oracle数据库维护、优化操作时，我们经常需要查看表及其相关信息，比如表的存储空间使用情况、字典信息等。但是Oracle的表名长度限制只有30个字符，很多时候我们使用的表名会很复杂，查询时需要键入很长的表名，非常繁琐。
此时，Oracle的同义词功能就派上用场了。同义词是指在一个数据库中给一个表或视图取一个“别名”，使用这个“别名”就可以代替表或视图名字。
但是在使用同义词查询表及其相关信息时，Oracle的数据字典里显示的却是同义词的信息，如何快速查询到实际的表信息，成为了我们需要解决的难点。
二、解决方案
我们可以写一个PL/SQL脚本，用于查询同义词关联的实际表名，并且将查询结果保存到表中。这个脚本可以定期执行，以更新保存的信息。下面是一个简单的脚本示例：
CREATE TABLE syn_table_info (
syn_name VARCHAR2(30),
table_name VARCHAR2(30),
owner VARCHAR2(30)
);
DECLARE
cursor c_synonym is select synonym_name from all_synonyms where table_owner='DB_USER';
syn_name VARCHAR2(30);
table_name VARCHAR2(30);
owner VARCHAR2(30);
BEGIN
for s in c_synonym LOOP
begin
EXECUTE IMMEDIATE 'select table_name, table_owner from all_synonyms where synonym_name = :1' INTO table_name, owner USING s.synonym_name;
insert into syn_table_info(syn_name, table_name, owner) values (s.synonym_name, table_name, owner);
exception
when others then null;
end;
end loop;
END;
这个脚本首先创建了一个表syn_table_info，用于保存同义词信息。然后在循环中查询所有同义词关联的实际表名，并将查询结果保存到表syn_table_info中。
如果需要查询某个同义词关联的表信息，只需要使用如下的SQL语句：
SELECT * FROM syn_table_info WHERE syn_name = 'SYN_TABLE';其中SYN_TABLE为要查询的同义词名。
三、优化方案
上面的解决方案已经可以满足我们的需求，但有两个问题需要优化。
第一个问题是：如果同义词关联的实际表名发生变化，我们需要重新执行脚本来更新syn_table_info表。这个问题可以通过Oracle的触发器来解决。
我们可以在创建同义词时，同时在一个专门的表中记录同义词和表名的对应关系，并在更新或删除同义词时自动更新这个表，从而保持同义词信息的及时性。例如：
CREATE TABLE syn_table_map (
syn_name VARCHAR2(30),
table_name VARCHAR2(30),
owner VARCHAR2(30)
);
CREATE OR REPLACE TRIGGER syn_table_map_trig
AFTER CREATE OR ALTER OR DROP OR RENAME ON DATABASE
DECLARE
syn_name VARCHAR2(30);
table_name VARCHAR2(30);
owner VARCHAR2(30);
BEGIN
if ORA_DICT_OBJ_TYPE = 'SYNONYM' then
if ORA_DICT_OBJ_NAME like 'DB_USER.%' then
select table_name, table_owner into table_name, owner from all_synonyms where synonym_name = ORA_DICT_OBJ_NAME;
syn_name := substr(ORA_DICT_OBJ_NAME, instr(ORA_DICT_OBJ_NAME, '.') + 1);
if ORA_DICT_OP_TYPE = 'CREATE' then
insert into syn_table_map(syn_name, table_name, owner) values (syn_name, table_name, owner);
elsif ORA_DICT_OP_TYPE = 'ALTER' or ORA_DICT_OP_TYPE = 'RENAME' then
update syn_table_map set table_name = table_name, owner = owner where syn_name = syn_name;
elsif ORA_DICT_OP_TYPE = 'DROP' then
delete from syn_table_map where syn_name = syn_name;
end if;
end if;
end if;
END;
这个触发器监视数据库中同义词的创建、更改、删除和重命名事件，并自动更新syn_table_map表，保证同义词信息的及时性。
第二个问题是：我们需要经常查询同义词信息，但是每次都查询syn_table_info表的效率较低。这个问题可以通过创建视图来解决。
CREATE OR REPLACE VIEW v_syn_table_info
AS SELECT * FROM syn_table_map UNION ALL SELECT syn_name, table_name, owner FROM syn_table_info;这个视图可以将syn_table_info表和syn_table_map表合并在一起，从而在查询时只需要查询这个视图即可。
四、总结
通过使用同义词、定期更新实际表信息、创建触发器和视图，我们可以快速查询Oracle数据库中的表及其相关信息，提高数据库维护和优化效率。
原创文章，作者：FPYK，如若转载，请注明出处：https://www.506064.com/n/148925.html
微信扫一扫 
支付宝扫一扫 