ITS_OP/SRC - 스마트교차로 테이블 연계 이전 소스/SQL/vds_table.sql

--------------------------------------------------
-- Export file for user GJUTIS                  --
-- Created by OpenValue on 2014-07-16, 11:26:12 --
--------------------------------------------------

spool vds.log

prompt
prompt Creating table TB_VDS_CTLR
prompt ==========================
prompt
create table GJUTIS.TB_VDS_CTLR
(
  ctlr_mngm_nmbr VARCHAR2(20) not null,
  ctlr_type_cd   VARCHAR2(7),
  vald_yn        CHAR(1) default 'N',
  x_crdn         NUMBER(11,8),
  y_crdn         NUMBER(10,8),
  lctn           VARCHAR2(100),
  trmn_ip        VARCHAR2(20),
  trmn_port      VARCHAR2(5),
  ctlr_clct_cycl NUMBER(3),
  ctlr_stts_cycl NUMBER(3),
  loop_bord_num  NUMBER(3) default 0,
  dltr_nmbr      VARCHAR2(20),
  del_dt         VARCHAR2(14),
  del_yn         CHAR(1) default 'N',
  frst_regr_nmbr VARCHAR2(20),
  frst_rgst_dt   VARCHAR2(14),
  last_crpr_nmbr VARCHAR2(20),
  last_crct__dt  VARCHAR2(14)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_CTLR
  is 'VDS 제어기';
comment on column GJUTIS.TB_VDS_CTLR.ctlr_mngm_nmbr
  is '제어기 관리 번호';
comment on column GJUTIS.TB_VDS_CTLR.ctlr_type_cd
  is '제어기 유형 코드';
comment on column GJUTIS.TB_VDS_CTLR.vald_yn
  is '유효 여부';
comment on column GJUTIS.TB_VDS_CTLR.x_crdn
  is 'X 좌표';
comment on column GJUTIS.TB_VDS_CTLR.y_crdn
  is 'Y 좌표';
comment on column GJUTIS.TB_VDS_CTLR.lctn
  is '위치';
comment on column GJUTIS.TB_VDS_CTLR.trmn_ip
  is '단말기 아이피';
comment on column GJUTIS.TB_VDS_CTLR.trmn_port
  is '단말기 포트';
comment on column GJUTIS.TB_VDS_CTLR.ctlr_clct_cycl
  is '제어기 수집 주기';
comment on column GJUTIS.TB_VDS_CTLR.ctlr_stts_cycl
  is '제어기 상태 주기';
comment on column GJUTIS.TB_VDS_CTLR.loop_bord_num
  is '루프 보드 개수';
comment on column GJUTIS.TB_VDS_CTLR.dltr_nmbr
  is '삭제자 번호';
comment on column GJUTIS.TB_VDS_CTLR.del_dt
  is '삭제 일시';
comment on column GJUTIS.TB_VDS_CTLR.del_yn
  is '삭제 여부';
comment on column GJUTIS.TB_VDS_CTLR.frst_regr_nmbr
  is '최초 등록자 번호';
comment on column GJUTIS.TB_VDS_CTLR.frst_rgst_dt
  is '최초 등록 일시';
comment on column GJUTIS.TB_VDS_CTLR.last_crpr_nmbr
  is '최종 수정자 번호';
comment on column GJUTIS.TB_VDS_CTLR.last_crct__dt
  is '최종 수정  일시';
alter table GJUTIS.TB_VDS_CTLR
  add constraint IX_VDS_CTLR_PK primary key (CTLR_MNGM_NMBR)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;

prompt
prompt Creating table TB_VDS_CTLR_STTS
prompt ===============================
prompt
create table GJUTIS.TB_VDS_CTLR_STTS
(
  ctlr_mngm_nmbr    VARCHAR2(20) not null,
  cmnc_stts_cd      VARCHAR2(7),
  prnt_pwer_stts_cd VARCHAR2(7),
  cbox_door_stts_cd VARCHAR2(7),
  fan_stts_cd       VARCHAR2(7),
  hetr_stts_cd      VARCHAR2(7),
  updt_dt           VARCHAR2(14),
  sub_ctlr_val      NUMBER(3) default 0
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_CTLR_STTS
  is 'VDS 제어기 상태';
comment on column GJUTIS.TB_VDS_CTLR_STTS.ctlr_mngm_nmbr
  is '제어기 관리 번호';
comment on column GJUTIS.TB_VDS_CTLR_STTS.cmnc_stts_cd
  is '통신 상태 코드';
comment on column GJUTIS.TB_VDS_CTLR_STTS.prnt_pwer_stts_cd
  is '출력 전원 상태 코드';
comment on column GJUTIS.TB_VDS_CTLR_STTS.cbox_door_stts_cd
  is '함체 문 상태 코드';
comment on column GJUTIS.TB_VDS_CTLR_STTS.fan_stts_cd
  is '팬 상태 코드';
comment on column GJUTIS.TB_VDS_CTLR_STTS.hetr_stts_cd
  is '히터 상태 코드';
comment on column GJUTIS.TB_VDS_CTLR_STTS.updt_dt
  is '갱신 일시';
comment on column GJUTIS.TB_VDS_CTLR_STTS.sub_ctlr_val
  is '부 제어기 값';
alter table GJUTIS.TB_VDS_CTLR_STTS
  add constraint IX_VDS_CTLR_STTS_PK primary key (CTLR_MNGM_NMBR)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_CTLR_STTS
  add constraint FK_TB_VDS_CTLR_STTS_01 foreign key (CTLR_MNGM_NMBR)
  references GJUTIS.TB_VDS_CTLR (CTLR_MNGM_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_CTLR_STTS_RAW
prompt ===================================
prompt
create table GJUTIS.TB_VDS_CTLR_STTS_RAW
(
  ctlr_mngm_nmbr    VARCHAR2(20) not null,
  ocrr_dt           VARCHAR2(14) not null,
  cmnc_stts_cd      VARCHAR2(7),
  prnt_pwer_stts_cd VARCHAR2(7),
  cbox_door_stts_cd VARCHAR2(7),
  fan_stts_cd       VARCHAR2(7),
  hetr_stts_cd      VARCHAR2(7),
  prnt_pwer_val     NUMBER(3) default 0,
  sub_ctlr_val      NUMBER(3) default 0
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_CTLR_STTS_RAW
  is 'VDS 제어기 상태 원시';
comment on column GJUTIS.TB_VDS_CTLR_STTS_RAW.ctlr_mngm_nmbr
  is '제어기 관리 번호';
comment on column GJUTIS.TB_VDS_CTLR_STTS_RAW.ocrr_dt
  is '발생 일시';
comment on column GJUTIS.TB_VDS_CTLR_STTS_RAW.cmnc_stts_cd
  is '통신 상태 코드';
comment on column GJUTIS.TB_VDS_CTLR_STTS_RAW.prnt_pwer_stts_cd
  is '출력 전원 상태 코드';
comment on column GJUTIS.TB_VDS_CTLR_STTS_RAW.cbox_door_stts_cd
  is '함체 문 상태 코드';
comment on column GJUTIS.TB_VDS_CTLR_STTS_RAW.fan_stts_cd
  is '팬 상태 코드';
comment on column GJUTIS.TB_VDS_CTLR_STTS_RAW.hetr_stts_cd
  is '히터 상태 코드';
comment on column GJUTIS.TB_VDS_CTLR_STTS_RAW.prnt_pwer_val
  is '출력 전원 값';
comment on column GJUTIS.TB_VDS_CTLR_STTS_RAW.sub_ctlr_val
  is '부 제어기 값';
alter table GJUTIS.TB_VDS_CTLR_STTS_RAW
  add constraint IX_VDS_CTLR_STTS_RAW_PK primary key (CTLR_MNGM_NMBR, OCRR_DT)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_CTLR_STTS_RAW
  add constraint FK_TB_CTLR_STTS_RAW_01 foreign key (CTLR_MNGM_NMBR)
  references GJUTIS.TB_VDS_CTLR (CTLR_MNGM_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_MSTR
prompt ===============================
prompt
create table GJUTIS.TB_VDS_DTCT_MSTR
(
  dtct_nmbr      VARCHAR2(8) not null,
  dtct_type_cd   VARCHAR2(7),
  stln_dstc      NUMBER(7) default 0,
  istl_lane      NUMBER(2),
  det_nmbr       VARCHAR2(1),
  snst           NUMBER(3) default 0,
  dtct_vald_dstc NUMBER(7) default 0,
  mdvh_uplm_lngt NUMBER(7) default 0,
  mdvh_lwlm_lngt NUMBER(7) default 0,
  use_yn         CHAR(1) default 'N',
  chnl_nmbr      VARCHAR2(4),
  dltr_nmbr      VARCHAR2(20),
  del_dt         VARCHAR2(14),
  del_yn         CHAR(1) default 'N',
  frst_regr_nmbr VARCHAR2(20),
  frst_rgst_dt   VARCHAR2(14),
  last_crpr_nmbr VARCHAR2(20),
  last_crct_dt   VARCHAR2(14),
  ctlr_mngm_nmbr VARCHAR2(20)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_MSTR
  is 'VDS 검지기 마스터';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.dtct_type_cd
  is '검지기 유형 코드';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.stln_dstc
  is '정지선 거리';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.istl_lane
  is '설치 차로';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.det_nmbr
  is 'DET 번호';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.snst
  is '감도';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.dtct_vald_dstc
  is '검지기 유효 거리';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.mdvh_uplm_lngt
  is '중형차 상한 길이';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.mdvh_lwlm_lngt
  is '중형차 하한 길이';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.use_yn
  is '사용 여부';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.chnl_nmbr
  is '채널 번호';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.dltr_nmbr
  is '삭제자 번호';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.del_dt
  is '삭제 일시';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.del_yn
  is '삭제 여부';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.frst_regr_nmbr
  is '최초 등록자 번호';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.frst_rgst_dt
  is '최초 등록 일시';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.last_crpr_nmbr
  is '최종 수정자 번호';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.last_crct_dt
  is '최종 수정 일시';
comment on column GJUTIS.TB_VDS_DTCT_MSTR.ctlr_mngm_nmbr
  is '제어기 관리 번호';
alter table GJUTIS.TB_VDS_DTCT_MSTR
  add constraint IX_VDS_DTCT_MSTR_PK primary key (DTCT_NMBR)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_MSTR
  add constraint FK_VDS_DTCT_MSTR__CTLR_MNGM_NM foreign key (CTLR_MNGM_NMBR)
  references GJUTIS.TB_VDS_CTLR (CTLR_MNGM_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_15M_STAT
prompt ===================================
prompt
create table GJUTIS.TB_VDS_DTCT_15M_STAT
(
  dtct_nmbr      VARCHAR2(8) not null,
  stat_dt        VARCHAR2(14) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  tfvl           NUMBER(6) default 0,
  avrg_sped      NUMBER(3) default 0,
  avrg_ocpy_rate NUMBER(6,2),
  avrg_hdwy      VARCHAR2(6),
  avrg_lngt      NUMBER(5,1)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_15M_STAT
  is 'VDS 검지기 15분 통계';
comment on column GJUTIS.TB_VDS_DTCT_15M_STAT.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_15M_STAT.stat_dt
  is '통계 일시';
comment on column GJUTIS.TB_VDS_DTCT_15M_STAT.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_15M_STAT.tfvl
  is '교통량';
comment on column GJUTIS.TB_VDS_DTCT_15M_STAT.avrg_sped
  is '평균 속도';
comment on column GJUTIS.TB_VDS_DTCT_15M_STAT.avrg_ocpy_rate
  is '평균 점유 율';
comment on column GJUTIS.TB_VDS_DTCT_15M_STAT.avrg_hdwy
  is '평균 차두시간';
comment on column GJUTIS.TB_VDS_DTCT_15M_STAT.avrg_lngt
  is '평균 길이';
alter table GJUTIS.TB_VDS_DTCT_15M_STAT
  add constraint IX_VDS_DTCT_15M_STAT_PK primary key (DTCT_NMBR, STAT_DT, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_15M_STAT
  add constraint FK_TB_DTCT_15M_STAT_01 foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_ADJS
prompt ===============================
prompt
create table GJUTIS.TB_VDS_DTCT_ADJS
(
  dtct_nmbr      VARCHAR2(8) not null,
  ocrr_dt        VARCHAR2(14) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  tfvl           NUMBER(6) default 0,
  avrg_ocpy_rate NUMBER(6,2),
  sped           NUMBER(3) default 0,
  hdwy           VARCHAR2(6),
  avrg_lngt      NUMBER(5,1)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_ADJS
  is 'VDS 검지기 보정';
comment on column GJUTIS.TB_VDS_DTCT_ADJS.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_ADJS.ocrr_dt
  is '발생 일시';
comment on column GJUTIS.TB_VDS_DTCT_ADJS.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_ADJS.tfvl
  is '교통량';
comment on column GJUTIS.TB_VDS_DTCT_ADJS.avrg_ocpy_rate
  is '평균 점유 율';
comment on column GJUTIS.TB_VDS_DTCT_ADJS.sped
  is '속도';
comment on column GJUTIS.TB_VDS_DTCT_ADJS.hdwy
  is '차두시간';
comment on column GJUTIS.TB_VDS_DTCT_ADJS.avrg_lngt
  is '평균 길이';
alter table GJUTIS.TB_VDS_DTCT_ADJS
  add constraint IX_VDS_DTCT_ADJS_PK primary key (DTCT_NMBR, OCRR_DT, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_ADJS
  add constraint FK_VDS_DTCT_ADJS__DTCT_NMBR foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_CLCT_ERR_HS
prompt ======================================
prompt
create table GJUTIS.TB_VDS_DTCT_CLCT_ERR_HS
(
  dtct_nmbr      VARCHAR2(8) not null,
  ocrr_dt        VARCHAR2(14) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  err_type_dvsn  VARCHAR2(2)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_CLCT_ERR_HS
  is 'VDS 검지기 수집 오류 이력';
comment on column GJUTIS.TB_VDS_DTCT_CLCT_ERR_HS.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_CLCT_ERR_HS.ocrr_dt
  is '발생 일시';
comment on column GJUTIS.TB_VDS_DTCT_CLCT_ERR_HS.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_CLCT_ERR_HS.err_type_dvsn
  is '오류 유형 구분';
alter table GJUTIS.TB_VDS_DTCT_CLCT_ERR_HS
  add constraint IX_VDS_DTCT_CLCT_ERR_HS_PK primary key (DTCT_NMBR, OCRR_DT, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_CLCT_ERR_HS
  add constraint FK_TB_DTCT_CLCT_ERR_HS_01 foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_DD_STAT
prompt ==================================
prompt
create table GJUTIS.TB_VDS_DTCT_DD_STAT
(
  dtct_nmbr      VARCHAR2(8) not null,
  stat_dt        VARCHAR2(14) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  tfvl           NUMBER(6) default 0,
  avrg_sped      NUMBER(3) default 0,
  avrg_ocpy_rate NUMBER(6,2),
  avrg_hdwy      VARCHAR2(6),
  avrg_lngt      NUMBER(5,1)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_DD_STAT
  is 'VDS 검지기 일 통계';
comment on column GJUTIS.TB_VDS_DTCT_DD_STAT.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_DD_STAT.stat_dt
  is '통계 일시';
comment on column GJUTIS.TB_VDS_DTCT_DD_STAT.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_DD_STAT.tfvl
  is '교통량';
comment on column GJUTIS.TB_VDS_DTCT_DD_STAT.avrg_sped
  is '평균 속도';
comment on column GJUTIS.TB_VDS_DTCT_DD_STAT.avrg_ocpy_rate
  is '평균 점유 율';
comment on column GJUTIS.TB_VDS_DTCT_DD_STAT.avrg_hdwy
  is '평균 차두시간';
comment on column GJUTIS.TB_VDS_DTCT_DD_STAT.avrg_lngt
  is '평균 길이';
alter table GJUTIS.TB_VDS_DTCT_DD_STAT
  add constraint IX_VDS_DTCT_DD_STAT_PK primary key (DTCT_NMBR, STAT_DT, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_DD_STAT
  add constraint FK_TB_DTCT_DD_STAT_01 foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_HH_STAT
prompt ==================================
prompt
create table GJUTIS.TB_VDS_DTCT_HH_STAT
(
  dtct_nmbr      VARCHAR2(8) not null,
  stat_dt        VARCHAR2(14) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  tfvl           NUMBER(6) default 0,
  avrg_sped      NUMBER(3) default 0,
  avrg_ocpy_rate NUMBER(6,2),
  avrg_hdwy      VARCHAR2(6),
  avrg_lngt      NUMBER(5,1)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_HH_STAT
  is 'VDS 검지기 시간 통계';
comment on column GJUTIS.TB_VDS_DTCT_HH_STAT.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_HH_STAT.stat_dt
  is '통계 일시';
comment on column GJUTIS.TB_VDS_DTCT_HH_STAT.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_HH_STAT.tfvl
  is '교통량';
comment on column GJUTIS.TB_VDS_DTCT_HH_STAT.avrg_sped
  is '평균 속도';
comment on column GJUTIS.TB_VDS_DTCT_HH_STAT.avrg_ocpy_rate
  is '평균 점유 율';
comment on column GJUTIS.TB_VDS_DTCT_HH_STAT.avrg_hdwy
  is '평균 차두시간';
comment on column GJUTIS.TB_VDS_DTCT_HH_STAT.avrg_lngt
  is '평균 길이';
alter table GJUTIS.TB_VDS_DTCT_HH_STAT
  add constraint IX_VDS_DTCT_HH_STAT_PK primary key (DTCT_NMBR, STAT_DT, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_HH_STAT
  add constraint FK_TB_DTCT_HH_STAT_01 foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_LINK_RLTN
prompt ====================================
prompt
create table GJUTIS.TB_VDS_DTCT_LINK_RLTN
(
  dtct_nmbr VARCHAR2(8) not null,
  link_id   NUMBER(10) not null
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_LINK_RLTN
  is 'VDS 검지기 링크 관계';
comment on column GJUTIS.TB_VDS_DTCT_LINK_RLTN.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_LINK_RLTN.link_id
  is '링크ID';
alter table GJUTIS.TB_VDS_DTCT_LINK_RLTN
  add constraint IX_VDS_DTCT_LINK_RLTN_PK primary key (DTCT_NMBR, LINK_ID)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_LINK_RLTN
  add constraint FK_VDS_DTCT_LINK_RLTN_02 foreign key (LINK_ID)
  references GJUTIS.TB_LINK (LINK_ID)
  disable;
alter table GJUTIS.TB_VDS_DTCT_LINK_RLTN
  add constraint FK_VDS_DTCT_LINK_RLTN__DTCT_NM foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_MN_STAT
prompt ==================================
prompt
create table GJUTIS.TB_VDS_DTCT_MN_STAT
(
  dtct_nmbr      VARCHAR2(8) not null,
  stat_dt        VARCHAR2(14) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  tfvl           NUMBER(6) default 0,
  avrg_sped      NUMBER(3) default 0,
  avrg_ocpy_rate NUMBER(6,2),
  avrg_hdwy      VARCHAR2(6),
  avrg_lngt      NUMBER(5,1)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_MN_STAT
  is 'VDS 검지기 월 통계';
comment on column GJUTIS.TB_VDS_DTCT_MN_STAT.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_MN_STAT.stat_dt
  is '통계 일시';
comment on column GJUTIS.TB_VDS_DTCT_MN_STAT.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_MN_STAT.tfvl
  is '교통량';
comment on column GJUTIS.TB_VDS_DTCT_MN_STAT.avrg_sped
  is '평균 속도';
comment on column GJUTIS.TB_VDS_DTCT_MN_STAT.avrg_ocpy_rate
  is '평균 점유 율';
comment on column GJUTIS.TB_VDS_DTCT_MN_STAT.avrg_hdwy
  is '평균 차두시간';
comment on column GJUTIS.TB_VDS_DTCT_MN_STAT.avrg_lngt
  is '평균 길이';
alter table GJUTIS.TB_VDS_DTCT_MN_STAT
  add constraint IX_VDS_DTCT_MN_STAT_PK primary key (DTCT_NMBR, STAT_DT, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_MN_STAT
  add constraint FK_TB_DTCT_MN_STAT_01 foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_PAST_CLCT
prompt ====================================
prompt
create table GJUTIS.TB_VDS_DTCT_PAST_CLCT
(
  dtct_nmbr      VARCHAR2(8) not null,
  ocrr_dt        VARCHAR2(14) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  tfvl           NUMBER(6) default 0,
  sped           NUMBER(3) default 0,
  avrg_ocpy_rate NUMBER(6,2),
  hdwy           VARCHAR2(6),
  avrg_lngt      NUMBER(5,1)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_PAST_CLCT
  is 'VDS 검지기 과거 수집';
comment on column GJUTIS.TB_VDS_DTCT_PAST_CLCT.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_PAST_CLCT.ocrr_dt
  is '발생 일시';
comment on column GJUTIS.TB_VDS_DTCT_PAST_CLCT.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_PAST_CLCT.tfvl
  is '교통량';
comment on column GJUTIS.TB_VDS_DTCT_PAST_CLCT.sped
  is '속도';
comment on column GJUTIS.TB_VDS_DTCT_PAST_CLCT.avrg_ocpy_rate
  is '평균 점유 율';
comment on column GJUTIS.TB_VDS_DTCT_PAST_CLCT.hdwy
  is '차두시간';
comment on column GJUTIS.TB_VDS_DTCT_PAST_CLCT.avrg_lngt
  is '평균 길이';
alter table GJUTIS.TB_VDS_DTCT_PAST_CLCT
  add constraint IX_VDS_DTCT_PAST_CLCT_PK primary key (DTCT_NMBR, OCRR_DT, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_PAST_CLCT
  add constraint FK_TB_PAST_CLCT_01 foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_RAW_CLCT
prompt ===================================
prompt
create table GJUTIS.TB_VDS_DTCT_RAW_CLCT
(
  dtct_nmbr      VARCHAR2(8) not null,
  ocrr_dt        VARCHAR2(14) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  tfvl           NUMBER(6) default 0,
  avrg_ocpy_rate NUMBER(6,2),
  sped           NUMBER(3) default 0,
  hdwy           VARCHAR2(6),
  avrg_lngt      NUMBER(5,1)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_RAW_CLCT
  is 'VDS 검지기 원시 수집';
comment on column GJUTIS.TB_VDS_DTCT_RAW_CLCT.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_RAW_CLCT.ocrr_dt
  is '발생 일시';
comment on column GJUTIS.TB_VDS_DTCT_RAW_CLCT.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_RAW_CLCT.tfvl
  is '교통량';
comment on column GJUTIS.TB_VDS_DTCT_RAW_CLCT.avrg_ocpy_rate
  is '평균 점유 율';
comment on column GJUTIS.TB_VDS_DTCT_RAW_CLCT.sped
  is '속도';
comment on column GJUTIS.TB_VDS_DTCT_RAW_CLCT.hdwy
  is '차두시간';
comment on column GJUTIS.TB_VDS_DTCT_RAW_CLCT.avrg_lngt
  is '평균 길이';
alter table GJUTIS.TB_VDS_DTCT_RAW_CLCT
  add constraint IX_VDS_DTCT_RAW_CLCT_PK primary key (DTCT_NMBR, OCRR_DT, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_RAW_CLCT
  add constraint FK_TB_DTCT_RAW_CLCT_01 foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_RLBL_CNDT
prompt ====================================
prompt
create table GJUTIS.TB_VDS_DTCT_RLBL_CNDT
(
  dtct_nmbr      VARCHAR2(8) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  max_tfvl       NUMBER(6) default 0,
  min_tfvl       NUMBER(6) default 0,
  max_ocpy_rate  NUMBER(6,2),
  min_ocpy_rate  NUMBER(6,2),
  max_sped       NUMBER(3) default 0,
  min_sped       NUMBER(3) default 0,
  del_yn         CHAR(1) default 'N',
  frst_regr_nmbr VARCHAR2(20),
  frst_rgst_dt   VARCHAR2(14),
  last_crpr_nmbr VARCHAR2(20),
  last_crct_dt   VARCHAR2(14)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_RLBL_CNDT
  is 'VDS 검지기 신뢰도 조건';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.max_tfvl
  is '최대 교통량';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.min_tfvl
  is '최소 교통량';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.max_ocpy_rate
  is '최대 점유 율';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.min_ocpy_rate
  is '최소 점유 율';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.max_sped
  is '최대 속도';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.min_sped
  is '최소 속도';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.del_yn
  is '삭제 여부';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.frst_regr_nmbr
  is '최초 등록자 번호';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.frst_rgst_dt
  is '최초 등록 일시';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.last_crpr_nmbr
  is '최종 수정자 번호';
comment on column GJUTIS.TB_VDS_DTCT_RLBL_CNDT.last_crct_dt
  is '최종 수정 일시';
alter table GJUTIS.TB_VDS_DTCT_RLBL_CNDT
  add constraint IX_VDS_DTCT_RLBL_CNDT_PK primary key (DTCT_NMBR, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_RLBL_CNDT
  add constraint FK_TB_DTCT_RLBL_CNDT_01 foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_SMTH
prompt ===============================
prompt
create table GJUTIS.TB_VDS_DTCT_SMTH
(
  dtct_nmbr      VARCHAR2(8) not null,
  ocrr_dt        VARCHAR2(14) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  tfvl           NUMBER(6) default 0,
  ocpy_rate      NUMBER(5,2) default 0,
  sped           NUMBER(3) default 0,
  hdwy           VARCHAR2(6),
  avrg_lngt      NUMBER(5,1)
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_SMTH
  is 'VDS 검지기 평활화';
comment on column GJUTIS.TB_VDS_DTCT_SMTH.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_SMTH.ocrr_dt
  is '발생 일시';
comment on column GJUTIS.TB_VDS_DTCT_SMTH.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_SMTH.tfvl
  is '교통량';
comment on column GJUTIS.TB_VDS_DTCT_SMTH.ocpy_rate
  is '점유 율';
comment on column GJUTIS.TB_VDS_DTCT_SMTH.sped
  is '속도';
comment on column GJUTIS.TB_VDS_DTCT_SMTH.hdwy
  is '차두시간';
comment on column GJUTIS.TB_VDS_DTCT_SMTH.avrg_lngt
  is '평균 길이';
alter table GJUTIS.TB_VDS_DTCT_SMTH
  add constraint IX_VDS_DTCT_SMTH_PK primary key (DTCT_NMBR, OCRR_DT, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_SMTH
  add constraint FK_TB_DTCT_SMTH_01 foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_DTCT_SMTH_PARA
prompt ====================================
prompt
create table GJUTIS.TB_VDS_DTCT_SMTH_PARA
(
  dtct_nmbr      VARCHAR2(8) not null,
  syst_kind_dvsn VARCHAR2(1) not null,
  tfvl_fctr      NUMBER(6,2),
  ocpy_rate_fctr NUMBER(6,2),
  sped_fctr      NUMBER(6,2),
  hdwy_fctr      NUMBER(6,2),
  frst_regr_nmbr VARCHAR2(20),
  frst_rgst_dt   VARCHAR2(14),
  last_crpr_nmbr VARCHAR2(20),
  last_crct_dt   VARCHAR2(14),
  del_yn         CHAR(1) default 'N'
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_DTCT_SMTH_PARA
  is 'VDS 검지기 평활화 파라미터';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.dtct_nmbr
  is '검지기 번호';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.syst_kind_dvsn
  is '시스템 종류 구분';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.tfvl_fctr
  is '교통량 계수';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.ocpy_rate_fctr
  is '점유 율 계수';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.sped_fctr
  is '속도 계수';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.hdwy_fctr
  is '차두시간 계수';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.frst_regr_nmbr
  is '최초 등록자 번호';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.frst_rgst_dt
  is '최초 등록 일시';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.last_crpr_nmbr
  is '최종 수정자 번호';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.last_crct_dt
  is '최종 수정 일시';
comment on column GJUTIS.TB_VDS_DTCT_SMTH_PARA.del_yn
  is '삭제 여부';
alter table GJUTIS.TB_VDS_DTCT_SMTH_PARA
  add constraint IX_VDS_DTCT_SMTH_PARA_PK primary key (DTCT_NMBR, SYST_KIND_DVSN)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_DTCT_SMTH_PARA
  add constraint FK_TB_DTCT_ADJS_01 foreign key (DTCT_NMBR)
  references GJUTIS.TB_VDS_DTCT_MSTR (DTCT_NMBR)
  disable;

prompt
prompt Creating table TB_VDS_EVT_CD
prompt ============================
prompt
create table GJUTIS.TB_VDS_EVT_CD
(
  vds_evt_cd  VARCHAR2(10) not null,
  evt_expl    VARCHAR2(200),
  cd_argm_cnt NUMBER(3) default 0
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_EVT_CD
  is 'VDS 이벤트 코드';
comment on column GJUTIS.TB_VDS_EVT_CD.vds_evt_cd
  is 'VDS 이벤트 코드';
comment on column GJUTIS.TB_VDS_EVT_CD.evt_expl
  is '이벤트 설명';
comment on column GJUTIS.TB_VDS_EVT_CD.cd_argm_cnt
  is '코드 인자 건수';
alter table GJUTIS.TB_VDS_EVT_CD
  add constraint IX_VDS_EVT_CD_PK primary key (VDS_EVT_CD)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;

prompt
prompt Creating table TB_VDS_EVT_HS
prompt ============================
prompt
create table GJUTIS.TB_VDS_EVT_HS
(
  ocrr_dt    VARCHAR2(14) not null,
  seq        VARCHAR2(10) not null,
  vds_evt_cd VARCHAR2(10) not null,
  evt_dvsn   NUMBER(3) default 0
)
tablespace ITS_HIST_DATA
  pctfree 10
  initrans 1
  maxtrans 255
nologging;
comment on table GJUTIS.TB_VDS_EVT_HS
  is 'VDS 이벤트 이력';
comment on column GJUTIS.TB_VDS_EVT_HS.ocrr_dt
  is '발생 일시';
comment on column GJUTIS.TB_VDS_EVT_HS.seq
  is '일련번호';
comment on column GJUTIS.TB_VDS_EVT_HS.vds_evt_cd
  is 'VDS 이벤트 코드';
comment on column GJUTIS.TB_VDS_EVT_HS.evt_dvsn
  is '이벤트 구분';
alter table GJUTIS.TB_VDS_EVT_HS
  add constraint IX_VDS_EVT_HS_PK primary key (OCRR_DT, SEQ, VDS_EVT_CD)
  using index 
  tablespace ITS_HIST_DATA
  pctfree 10
  initrans 2
  maxtrans 255;
alter table GJUTIS.TB_VDS_EVT_HS
  add constraint FK_TB_EVT_HS_01 foreign key (VDS_EVT_CD)
  references GJUTIS.TB_VDS_EVT_CD (VDS_EVT_CD)
  disable;


spool off