test.sql

修改oracle 当前session的时间格式

alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
alter session set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh24:mi:ss';
select * from wi_jobinfo;

select * from lsb_events;

truncate table wi_jobinfo drop storage;
select * from aggregate_info;
truncate table  aggregate_info drop storage;

commit;

select min(event_time),max(event_time) from wi_jobinfo;

CREATE SEQUENCE WI_JOBINFO_INS_SEQ
START  WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999999999999
NOCYCLE 
ORDER;


CREATE OR REPLACE TRIGGER WI_JOBINFO_INS
BEFORE INSERT
on WI_JOBINFO for each row
DECLARE
   ins_seq WI_JOBINFO.INSERT_SEQ%type; 
   
BEGIN   
   SELECT WI_JOBINFO_INS_SEQ.nextval
   INTO   ins_seq
   FROM   dual;
   :new.insert_seq := ins_seq;   
END;
/

CREATE GLOBAL TEMPORARY TABLE DATATMP1  ON COMMIT PRESERVE ROWS  AS SELECT * FROM WI_JOBINFO WHERE 1=2;
CREATE GLOBAL TEMPORARY TABLE DATATMP2  ON COMMIT PRESERVE ROWS  AS SELECT * FROM WI_JOBINFO WHERE 1=2;
CREATE GLOBAL TEMPORARY TABLE DATATMP3  ON COMMIT PRESERVE ROWS  AS SELECT * FROM WI_JOBINFO WHERE 1=2;

select * from wi_numberofjobs;
select distinct(CLUSTER_NAME) from wi_jobinfo;
select * from wi_jobresusagebygroup;

select * from REGISTER_INFO;
select distinct(CLUSTER_NAME) from lsb_events;

select * from aggregate_info;
delete from aggregate_info where cluster_name='-';
select max(event_time),min(event_time) from lsb_events;