日期:2014-05-17  浏览次数:20690 次

50分求解:能不能做到用“A”代替访问以“A”开头的表?
orcle中的表按小时生成,如A-2007031400,A-2007031401,...,A-2007031423,A-2007031500,A-2007031501,...,A-2007031523...等,其结构相同,能不能做到用“A”代替访问以“A”开头的表?
如,我只需要   select   *   from   A   就可以访问oracle中以“A”开头的表。

------解决方案--------------------
建立同义词咯。
CREATE SYNONYM A FOR A-2007031400




------解决方案--------------------
SQL> create table A20060101 (id number, name varchar2(20));

Table created

SQL> insert into A20060101 values(1, 'A20060101 ');

1 row inserted

SQL> create table A20060901 (id number, name varchar2(20));

Table created

SQL> insert into A20060901 values(1, 'A20060901 ');

1 row inserted

SQL> commit;

Commit complete
SQL> create or replace procedure testpro as
2 str varchar2(30000);
3 num number;
4 begin
5 execute immediate ' grant create view to db2inst2 '; //db2inst2 is username
6 str := 'create view A as ';
7 for y in (select table_name from user_tables where table_name like 'A% ')
8 loop
9 str := str || 'select * from ' || y.table_name || ' union all ';
10 end loop;
11 num := length(str);
12 str := substr(str, 0, num - 10);
13 execute immediate str ;
14 end;
15 /

Procedure created
SQL> exec testpro;

PL/SQL procedure successfully completed

SQL> select * from a;

ID NAME
---------- --------------------
1 A20060101
1 A20060901

然后创建一个job,定时执行这个存储过程就可以通过直接防问A来访问A开头的所有表

------解决方案--------------------
考虑分区表吧,按照时间分区

举例:


create table test
(
id number primary key,
name varchar2(20),
rq date
)
partition by range (rq)
(
partition rq_00 values less than(to_date( '2006-01-01 ', 'yyyy-mm-dd ')) tablespace xy,
partition rq_01 values less than( '2007-01-01 ', 'yyyy-mm-dd ') tablespace xy1,
partition rq_02 values less than(maxvalue) tablespace xy2
)