【翻译自mos文章】为table 的partition 建立synonym

最新推荐文章于 2024-05-17 22:49:44 发布

翻译最新推荐文章于 2024-05-17 22:49:44 发布 · 785 阅读

文章标签：

#table #partition #synonym

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文介绍如何通过两步法在Oracle表分区上创建同义词：首先创建视图来封装特定分区的数据，然后基于该视图创建同义词。此方法适用于希望简化对表分区访问的应用程序。

本文参考自：

How to Create a Synonym on a Table Partition (Doc ID 119805.1)

How to Create a Synonym on a Table Partition:
=============================================

You cannot directly create a synonym on a table partition by specifying the 
partition name and table name with the CREATE SYNONYM command.  You have 
to create a view on the partition, and then create a synonym on the view.  
This is a two-step method instead of a single-step method.

Follow the steps in this example to create the synonym:

 $ sqlplus scott/tiger

 SQL> create table emp_pt
      ( empno number primary key,
        ename varchar2(30))
      partition by range (empno)
      ( partition p8000 values less than (8001),
        partition p9000 values less than (9001),
        partition p10000 values less than (10001),
        partition pothers values less than (99999999));

 SQL> insert into emp_pt
      select empno, ename from emp;
 
       --- Populated the partition table EMP_PT with existing 
       --- data from the EMP table.

 SQL> commit;

 SQL> select * from emp_pt;

      EMPNO ENAME
      ----- -----------------
      7369  SMITH
      7499  ALLEN
      7521  WARD
      7566  JONES
      7654  MARTIN
      ...
      9999  ANDY


 SQL> select * from emp_pt partition (p8000);

      EMPNO ENAME
      ----- ---------------------
      7369  SMITH
      7499  ALLEN
      7521  WARD
      7566  JONES
      7654  MARTIN
       ...
      7934  MILLER

 SQL> create view emp_pt_p8000 as select * from emp_pt partition (p8000);

 SQL> desc emp_pt_p8000
 
      Name                                Null?    Type
      ----------------------------------- ------ -----------   
      EMPNO                                       NUMBER
      ENAME                                       VARCHAR2(30)

 SQL> create synonym p8000 for emp_pt_p8000;

 SQL> desc p8000
     
      Name                                Null?    Type
      ----------------------------------- ------ -----------   
      EMPNO                                       NUMBER
      ENAME                                       VARCHAR2(30)
    
SQL> select * from p8000;

     EMPNO ENAME
     ----- ------------------------------
     7369 SMITH
     7499 ALLEN
     7521 WARD
     7566 JONES
     7654 MARTIN
     ...
     7934 MILLER

 SQL>