Oracle批量生成表空间创建语句

Oracle批量生成表空间创建语句

用途：用于创建基于年月的表空间；

-- Oracle批量生成表空间创建语句with x1 as (/*年份*/     select cast(2010 + rownum as varchar(4)) as y from dual connect by level<=6), x2 as (/*季度*/   select '01' as q from dual union all    select '02' as q from dual union all    select '03' as q from dual union all    select '04' as q from dual ) select * from (select 'create tablespace ts_'|| x1.y ||'_'|| x2.q ||' logging datafile ''h:\database\db\ts_'|| x1.y ||'_'|| x2.q ||'.dbf'' size 10m autoextend on next 10m maxsize 32767m extent management local;' as sqlfrom x1, x2 order by x1.y, x2.q)union all select 'create tablespace ts_max logging datafile ''h:\database\db\ts_max.dbf'' size 10m autoextend on next 10m maxsize 32767m extent management local;' as sqlfrom dual;-- 表分区创建语句模版with x1 as (/*年份*/     select cast(2010 + rownum as varchar(4)) as y     from dual connect by level<=6), x2 as (/*季度*/   select '01' as q, '04' as q2 from dual union all    select '02' as q, '07' as q2 from dual union all    select '03' as q, '10' as q2 from dual union all    select '04' as q, '01' as q2 from dual ) select * from (select 'PARTITION ts_'|| x1.y ||'_'|| x2.q ||' VALUES less than (to_date('''        || cast(case when x2.q2='01' then cast(x1.y as number(4))+1 else cast(x1.y as number(4)) end as varchar(4))        ||'-'|| x2.q2 ||'-01'',''yyyy-mm-dd'')) tablespace ts_'|| x1.y ||'_'|| x2.q ||', ' as sqlfrom x1, x2order by x1.y, x2.q)union all select 'PARTITION ts_max VALUES less than (maxvalue) tablespace ts_max' from dual;