oracle 同义词建立

1 把一个用户的SELECT 权给另一个用户(以SYS身份执行）：

select 'grant select on cisco.'||table_name||  ' to chen ;' from all_tables where owner=upper('cisco');


把CISCO用户下的所有表授权给CHEN这个用户

2 创建同义词包语句如下(以SYS身份执行）：

CREATE OR REPLACE PACKAGE AMBOW_BI IS

-- Author  : cm
-- Created : 2011-2-17 14:53:15

PROCEDURE create_alltable_synonym(
/*
 创建源用户全部表的同义词到目标用户中
*/
v_sowner            IN             VARCHAR2,       /*[1]源用户*/
v_towner            IN             VARCHAR2,       /*[2]目标用户*/
v_success_xml       OUT            LONG,           /*[3]输出已创建的同义词*/
v_fail_xml          OUT            LONG            /*[3]输出未创建的同义词*/
);

PROCEDURE create_assigntable_synonym(
/*
 创建源用户指定表的同义词到目标用户中
*/
v_sowner            IN OUT             VARCHAR2,       /*[1]源用户*/
v_towner            IN OUT             VARCHAR2        /*[2]目标用户*/
);


END AMBOW_BI;
/
CREATE OR REPLACE PACKAGE BODY AMBOW_BI IS

PROCEDURE create_alltable_synonym(
/*
 创建源用户全部表的同义词到目标用户中
*/
v_sowner            IN             VARCHAR2,       /*[1]源用户*/
v_towner            IN             VARCHAR2,       /*[2]目标用户*/
v_success_xml       OUT            LONG,           /*[3]输出已创建的同义词*/
v_fail_xml          OUT            LONG            /*[3]输出未创建的同义词*/
)
IS
TYPE myCur IS REF Cursor;
cur  myCur;
tmp_sql           VARCHAR2(3000);
vsql              VARCHAR2(5000);
tmp_tablename     VARCHAR2(50);
tmp_count         NUMBER;

BEGIN

  IF v_sowner IS NULL OR v_towner IS NULL THEN
    RETURN;
  END IF;
 
  vsql:='SELECT T.TABLE_NAME FROM DBA_TABLES T WHERE T.OWNER='''||v_sowner||'''';
  --查询源用户所拥有的表
 
  OPEN cur  FOR vsql;
  LOOP
  FETCH  cur INTO  tmp_tablename;               
 
  EXIT WHEN cur%NOTFOUND OR cur IS NULL;
 
     tmp_sql:='SELECT COUNT(1) FROM DBA_SYNONYMS T WHERE T.OWNER='''||v_towner||''' AND T.SYNONYM_NAME='''||tmp_tablename||'''';
     EXECUTE IMMEDIATE tmp_sql INTO tmp_count;
     --查询同义词是否已在目标用户中存在
    
     IF tmp_count=0 THEN
       tmp_sql:='CREATE SYNONYM '||v_towner||'.'||tmp_tablename||' FOR '||v_sowner||'.'||tmp_tablename;
       EXECUTE IMMEDIATE tmp_sql;
       --执行同义词的创建操作
    
       v_success_xml:=v_success_xml||tmp_tablename||'|';
     END IF;
    
     v_fail_xml:=v_fail_xml||tmp_tablename||'|';    
    
  END LOOP;
  CLOSE cur;

/*EXCEPTION
 WHEN OTHERS THEN
 RETURN;*/
END create_alltable_synonym;

PROCEDURE create_assigntable_synonym(
/*
 创建源用户指定表的同义词到目标用户中
*/
v_sowner            IN OUT             VARCHAR2,       /*[1]源用户*/
v_towner            IN OUT             VARCHAR2        /*[2]目标用户*/
)
IS
tmp_sql           VARCHAR2(3000);
BEGIN

  v_sowner:='AMBOWSERVER';
  v_towner:='BIUSER';
 
  tmp_sql:='CREATE SYNONYM '||v_towner||'.AREA_ALL FOR '||v_sowner||'.AREA_ALL';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.SCHOOLTYPE FOR '||v_sowner||'.SCHOOLTYPE';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CLASS_INFO_HY FOR '||v_sowner||'.T_CLASS_INFO_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CLASS_INFO_LM FOR '||v_sowner||'.T_CLASS_INFO_LM';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CLASS_INFO_ZJ FOR '||v_sowner||'.T_CLASS_INFO_ZJ';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CLASSROOM_HY FOR '||v_sowner||'.T_CLASSROOM_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CLASSROOM_SEAT_HY FOR '||v_sowner||'.T_CLASSROOM_SEAT_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CONTRACT_INFO_ZJ FOR '||v_sowner||'.T_CONTRACT_INFO_ZJ';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CONTRACT_ORDER_RELATION_ZJ FOR '||v_sowner||'.T_CONTRACT_ORDER_RELATION_ZJ';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_COUPON_BASE_HY FOR '||v_sowner||'.T_COUPON_BASE_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_COUPON_HY FOR '||v_sowner||'.T_COUPON_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_COURSE_HY FOR '||v_sowner||'.T_COURSE_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_COURSE_LM FOR '||v_sowner||'.T_COURSE_LM';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CREDIT_HY FOR '||v_sowner||'.T_CREDIT_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_CREDIT_PRINT_HY FOR '||v_sowner||'.T_CREDIT_PRINT_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_EDU_STAGE FOR '||v_sowner||'.T_EDU_STAGE';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_GRADE_HY FOR '||v_sowner||'.T_GRADE_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_LEARN_PRODUCT_ZJ FOR '||v_sowner||'.T_LEARN_PRODUCT_ZJ';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_CHANGE_HY FOR '||v_sowner||'.T_ORDER_CHANGE_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_CHANGE_LM FOR '||v_sowner||'.T_ORDER_CHANGE_LM';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_COURSE_REAL_HY FOR '||v_sowner||'.T_ORDER_COURSE_REAL_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_HY FOR '||v_sowner||'.T_ORDER_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_LM FOR '||v_sowner||'.T_ORDER_LM';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_ORDER_OPERATION_ZJ FOR '||v_sowner||'.T_ORDER_OPERATION_ZJ';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_REGIINFO_HY FOR '||v_sowner||'.T_REGIINFO_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_REGIINFO_LM FOR '||v_sowner||'.T_REGIINFO_LM';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_REGIINFO_ZJ FOR '||v_sowner||'.T_REGIINFO_ZJ';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SCHOOL_AREA_HY FOR '||v_sowner||'.T_SCHOOL_AREA_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SCHOOL_AREA_LM FOR '||v_sowner||'.T_SCHOOL_AREA_LM';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SCHOOL_AREA_ZJ FOR '||v_sowner||'.T_SCHOOL_AREA_ZJ';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SEASON_HY FOR '||v_sowner||'.T_SEASON_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SUBJECT_HY FOR '||v_sowner||'.T_SUBJECT_HY';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SUBJECT_LM FOR '||v_sowner||'.T_SUBJECT_LM';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_SYS_BASECODE_ZJ FOR '||v_sowner||'.T_SYS_BASECODE_ZJ';
  EXECUTE IMMEDIATE tmp_sql;
  tmp_sql:='CREATE SYNONYM '||v_towner||'.T_TIME_SPAN_HY FOR '||v_sowner||'.T_TIME_SPAN_HY';
  EXECUTE IMMEDIATE tmp_sql; 

/*EXCEPTION
 WHEN OTHERS THEN
 RETURN;*/
END create_assigntable_synonym;

END AMBOW_BI;
/


3 打开package body 里的PROCEDURE执行其中的一个