Orcal建立分区表，触发器、序列、查表结构

orcal建立分区表

-- Create table
create table DB.TABLE
(
  data_date  DATE,
  data_type  VARCHAR2(2),
  num1         NUMBER(18,6),
  name1     VARCHAR2(16)
)
-- 日期可以变成DAY需要把函数改为NUMTODSINTERVAL
partition by range (DATA_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) store in (SPACE)
-- subpartition by range(org_no)   --子分区
--     subpartition template   
--     (  
--       subpartition org_no_111  VALUES less than ('37401'), 
--       subpartition org_no_223  VALUES  less than (maxvalue)
--     )
(
  partition P0 values less than (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 	'NLS_CALENDAR=GREGORIAN'))
    tablespace SPACE
);
-- 按地区分
partition by range(NAME1)(
partition p_37416 values less than ('111')
   tablespace SPACE,
 partition p_37417 values less than ('223')
   tablespace SPACE,
  partition p_MAXVALUE values less than (MAXVALUE)
   tablespace SPACE
);


-- Add comments to the table 
comment on table DB.TABLE
  is '测试表';
-- Add comments to the columns 
comment on column DB.TABLE.data_date
  is '数据日期';

create unique index DB.TABLE_INDEX on DB.TABLE (NAME1,DATA_DATE, DATA_TYPE)
  tablespace SPACE

触发器和序列

create sequence sgamitmp.TEST_SEQ
minvalue 1
nomaxvalue
start with 1
increment by 1
cache 20;
create or replace trigger sgamitmp.TEST_TRIGGER
  before insert on sgamitmp.TEST_TABLE
 for each row
begin
 select sgamitmp.TEST_SEQ.nextval into :new.record_id from dual;
end ;

查表结构

SELECT t1.Table_Name AS "表名称",
t3.comments AS "表说明", 
t1.Column_Name AS "字段名称",
t1.Data_Type AS "数据类型",
t1.Data_Length AS "长度",
t1.DATA_PRECISION as "小数位数",
t1.NullAble AS "是否为空",
t2.Comments AS "字段说明",
t1.Data_Default "默认值"
FROM all_tab_columns t1 
left join all_col_comments t2
on t1.Owner=t2.Owner and t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
left join all_tab_comments t3 
on t1.Owner=t3.Owner and t1.Table_name=t3.Table_name 
WHERE NOT EXISTS ( SELECT t4.Object_Name FROM all_objects t4
WHERE t4.Object_Type='TABLE' 
AND t4.Temporary='Y' 
AND t4.Owner=t1.Owner
AND t4.Object_Name=t1.Table_Name ) 
and t1.Owner = UPPER('test_db') --指定数据库
and t1.TABLE_NAME=UPPER('test_table') --指定表名
ORDER BY t1.Table_Name, t1.Column_ID;