utic-center/utic-ptis-server/Reference.TXT

select * from OCCURID_POLICE_LOG where REG_DATE between '20251017093700' and '20251017094352';
select t.LINK_ID, t.REPORT_DATE, t.*
  from UTISINCI.TB_IMS_DATA@UTISDB t
 where REPORT_DATE  > TO_DATE('20251017093700', 'YYYYMMDDHH24MISS')
   AND REPORT_DATE <= TO_DATE('20251017094352',   'YYYYMMDDHH24MISS')
   AND INCIDENT_TYPE_CD IN ('1', '2', '3', '4');



select min(t.reg_date), max(t.reg_date), count(1) from EVENTID_POLICE_LOG t;
select min(t.reg_date), max(t.reg_date), count(1) from EVENTID_POLICE_LOG_TMP t;
select min(t.reg_date), max(t.reg_date), count(1) from OCCURID_POLICE_LOG t;
select min(t.reg_date), max(t.reg_date), count(1) from OCCURID_POLICE_LOG_TMP t;

DELETE FROM EVENTID_POLICE_LOG WHERE REG_DATE < TO_CHAR(SYSDATE - 30, 'YYYYMMDDHH24MISS');
DELETE FROM EVENTID_POLICE_LOG_TMP WHERE REG_DATE < TO_CHAR(SYSDATE - 30, 'YYYYMMDDHH24MISS');
DELETE FROM OCCURID_POLICE_LOG WHERE REG_DATE < TO_CHAR(SYSDATE - 30, 'YYYYMMDDHH24MISS');
DELETE FROM OCCURID_POLICE_LOG_TMP WHERE REG_DATE < TO_CHAR(SYSDATE - 30, 'YYYYMMDDHH24MISS');

SELECT SEGMENT_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS SIZE_MB
FROM USER_SEGMENTS
WHERE
  SEGMENT_NAME = 'EVENTID_POLICE_LOG'
  AND SEGMENT_TYPE = 'TABLE'
GROUP BY SEGMENT_NAME;

ALTER TABLE EVENTID_POLICE_LOG_TMP MOVE;
ALTER INDEX EVENTID_POLICE_LOG_TMP_IDX01 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX EVENTID_POLICE_LOG_TMP_IDX02 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX EVENTID_POLICE_LOG_TMP_IDX03 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX EVENTID_POLICE_LOG_TMP_IDX1 REBUILD TABLESPACE user_default_data;
ALTER INDEX EVENTID_POLICE_LOG_TMP_IDX2 REBUILD TABLESPACE user_default_data;
ALTER INDEX PK_EVENTID_POLICE_LOG_TMP REBUILD TABLESPACE occurid_police_log_idx;

ALTER TABLE EVENTID_POLICE_LOG MOVE;
ALTER INDEX EVENTID_POLICE_LOG_IDX01 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX EVENTID_POLICE_LOG_IDX02 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX EVENTID_POLICE_LOG_IDX03 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX EVENTID_POLICE_LOG_IDX1 REBUILD TABLESPACE user_default_data;
ALTER INDEX EVENTID_POLICE_LOG_IDX2 REBUILD TABLESPACE user_default_data;
ALTER INDEX PK_EVENTID_POLICE_LOG REBUILD TABLESPACE occurid_police_log_idx;

ALTER TABLE OCCURID_POLICE_LOG MOVE;
ALTER INDEX OCCURID_POLICE_LOG_IDX01 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX OCCURID_POLICE_LOG_IDX02 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX OCCURID_POLICE_LOG_IDX03 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX OCCURID_POLICE_LOG_IDX1 REBUILD TABLESPACE user_default_data;
ALTER INDEX OCCURID_POLICE_LOG_IDX2 REBUILD TABLESPACE user_default_data;
ALTER INDEX PK_OCCURID_POLICE_LOG REBUILD TABLESPACE occurid_police_log_idx;

ALTER TABLE OCCURID_POLICE_LOG_TMP MOVE;
ALTER INDEX OCCURID_POLICE_LOG_TMP_IDX01 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX OCCURID_POLICE_LOG_TMP_IDX02 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX OCCURID_POLICE_LOG_TMP_IDX03 REBUILD TABLESPACE occurid_police_log_idx;
ALTER INDEX OCCURID_POLICE_LOG_TMP_IDX1 REBUILD TABLESPACE user_default_data;
ALTER INDEX OCCURID_POLICE_LOG_TMP_IDX2 REBUILD TABLESPACE user_default_data;
ALTER INDEX PK_OCCURID_POLICE_LOG_TMP REBUILD TABLESPACE occurid_police_log_idx;


-- 또는
--ALTER TABLE EVENTID_POLICE_LOG_TMP SHRINK SPACE;
ALTER TABLE EVENTID_POLICE_LOG ENABLE ROW MOVEMENT;
ALTER TABLE EVENTID_POLICE_LOG SHRINK SPACE;
ALTER TABLE EVENTID_POLICE_LOG DISABLE ROW MOVEMENT;

ALTER TABLE EVENTID_POLICE_LOG_TMP ENABLE ROW MOVEMENT;
ALTER TABLE EVENTID_POLICE_LOG_TMP SHRINK SPACE;
ALTER TABLE EVENTID_POLICE_LOG_TMP DISABLE ROW MOVEMENT;

ALTER TABLE OCCURID_POLICE_LOG ENABLE ROW MOVEMENT;
ALTER TABLE OCCURID_POLICE_LOG SHRINK SPACE;
ALTER TABLE OCCURID_POLICE_LOG DISABLE ROW MOVEMENT;

ALTER TABLE OCCURID_POLICE_LOG_TMP ENABLE ROW MOVEMENT;
ALTER TABLE OCCURID_POLICE_LOG_TMP SHRINK SPACE;
ALTER TABLE OCCURID_POLICE_LOG_TMP DISABLE ROW MOVEMENT;

-- 테이블에 NOLOGGING 설정
ALTER TABLE EVENTID_POLICE_LOG NOLOGGING;
ALTER TABLE EVENTID_POLICE_LOG_TMP NOLOGGING;
ALTER TABLE OCCURID_POLICE_LOG NOLOGGING;
ALTER TABLE OCCURID_POLICE_LOG_TMP NOLOGGING;


BEGIN
DBMS_STATS.GATHER_TABLE_STATS('PTDBS', 'EVENTID_POLICE_LOG_TMP', CASCADE => TRUE);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('PTDBS', 'EVENTID_POLICE_LOG', CASCADE => TRUE);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('PTDBS', 'OCCURID_POLICE_LOG', CASCADE => TRUE);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('PTDBS', 'OCCURID_POLICE_LOG_TMP', CASCADE => TRUE);
END;
/

select * from dba_synonyms where db_link is not null;
--1	PUBLIC	CURLINKST	ITSAPP	CURLINKST	EXT12LOCDB
--2	PUBLIC	GET_EXTDATE	ITSAPP	GET_EXTDATE	EXT12LOCDB
--3	PUBLIC	RCV_LINK_TRAFFIC		RCV_LINK_TRAFFIC	XMLDB
select * from dba_db_links;
--1	PUBLIC	INCIDB	UTISINCI	UTIS	2022-08-17 오후 2:06:50
--2	PUBLIC	UTISDB	UTIADMIN	UTIS	2025-05-13 오전 11:20:54
--3	PUBLIC	XMLDB	SECTION	UTIS	2018-11-26 오전 11:21:53
--4	PUBLIC	EXT12LOCDB	ITSAPP	EXT12LOCDB	2016-09-22 오전 9:42:13
--5	PUBLIC	MAINDB	UTIADMIN	UTIS	2016-09-22 오전 9:42:13
--6	PUBLIC	UTIS	PTAPP	UTIS	2016-09-22 오전 9:42:13
--7	TIMS	MTE_06	NLDBS	NLDBS	2016-09-22 오전 9:42:13
<!--    select 'IF_POL_MPSS_001', trunc(sysdate), '도로교통공단', '돌발정보', '5분', count(1) as count_snd from OCCURID_POLICE_LOG t where t.reg_date between to_char(sysdate-1, 'YYYYMMDD') || '000000' and to_char(sysdate-1, 'YYYYMMDD') || '235959';-->
<!--    select 'IF_POL_MPSS_002', trunc(sysdate), '도로교통공단', '통제정보', '5분', count(1) as count_snd from EVENTID_POLICE_LOG t where t.reg_date between to_char(sysdate-1, 'YYYYMMDD') || '000000' and to_char(sysdate-1, 'YYYYMMDD') || '235959';-->
<!--    select 'IF_POL_MPSS_003', trunc(sysdate), '도로교통공단', '소통정보', '5분', count(1) as count_snd from CURLINKST_POLICE_LOG t where t.reg_date between to_char(sysdate-1, 'YYYYMMDD') || '000000' and to_char(sysdate-1, 'YYYYMMDD') || '235959';-->

INSERT INTO INDIGO_STATS (IF_ID, INIT_DATE, AGENCY, INFO, PERIOD, COUNT_SND)
VALUES ('IF_POL_MPSS_001', trunc(sysdate+1), '도로교통공단', '돌발정보', '5분', );
INSERT INTO INDIGO_STATS (IF_ID, INIT_DATE, AGENCY, INFO, PERIOD, COUNT_SND)
VALUES ('IF_POL_MPSS_002', trunc(sysdate+1), '도로교통공단', '통제정보', '5분', );
INSERT INTO INDIGO_STATS (IF_ID, INIT_DATE, AGENCY, INFO, PERIOD, COUNT_SND)
VALUES ('IF_POL_MPSS_003', trunc(sysdate+1), '도로교통공단', '소통정보', '5분', );
commit;