内部プロジェクトデータベースの作成時の最初のプロトタイプ(バックアップ)
3910 ワード
内部データベースを作成するときの最初のプロトタイプ.(続きはComment、Index、データベース最適化など)
select * from ADMIN.USER_INFO;
select * from all_tables where TABLE_NAME like '%USER%';
------------------create user------------------------
create user admin identified by admin
default tablespace users
Temporary TABLESPACE temp;
grant connect,resource,dba to ADMIN;
-------------------delete user------------------------
DROP USER ADMIN;
------------------create table-------------------------------
create table ADMIN.USER_INFO(
USER_ID VARCHAR2(8) not null primary key,
USER_NM VARCHAR2(40),
LOGIN_ID VARCHAR2(20),
USER_TYP VARCHAR2(2),
PWD VARCHAR2(128),
PWD_UPD_DY VARCHAR2(8),
TEL_NO VARCHAR2(14),
MOB_NO VARCHAR2(14),
EMAIL_ID VARCHAR2(50),
USE_FR_DY VARCHAR2(8),
USE_TO_DY VARCHAR2(8),
REGR_ID VARCHAR2(8),
REG_DT DATE,
UPDR_ID VARCHAR2(8),
UPD_DT DATE);
------------------insert comment--------------------------
comment on table USER_INFO is '';
------------------task table--------------------------
create table ADMIN.TAST(
PGM_NO VARCHAR2(9) not null primary key,
PGM_NM VARCHAR2(100),
SOR_NO VARCHAR2(15),
BUSINES_FG VARCHAR2(2),
BUSINES_AREA VARCHAR2(2),
RESPON_KOR VARCHAR2(8),
ACPT_DY VARCHAR2(8),
END_DY VARCHAR2(8),
CHN_END_DY VARCHAR2(8),
GIVEN_DY VARCHAR2(8),
DEVR_ID VARCHAR2(8),
PGM_STS VARCHAR2(2),
DIFFIC_LEVEL VARCHAR2(1),
USE_TIME VARCHAR2(8),
UPD_CNTS VARCHAR2(3000),
REQRMNT_PATH VARCHAR2(100),
UPD_PGM_PATH VARCHAR2(300),
REGR_ID VARCHAR2(8),
REG_DT DATE,
UPDR_ID VARCHAR2(8),
UPD_DT DATE);
-------------------test table----------------------
create table ADMIN.TEST(
TEST_NO VARCHAR2(9) not null primary key,
PGM_NO VARCHAR2(9) not null,
PGM_NM VARCHAR2(100),
SOR_NO VARCHAR2(15),
BUSINES_FG VARCHAR2(2),
BUSINES_AREA VARCHAR2(2),
DEVR_ID VARCHAR2(8),
TESTR_ID VARCHAR2(8),
TEST_YM VARCHAR2(6),
LAST_TEST_DY VARCHAR2(8),
CHN_END_DY VARCHAR2(8),
DEV_TYP VARCHAR2(2),
DIFFIC_LEVEL VARCHAR2(1),
UPD_CNTS VARCHAR2(3000),
BUG_LIST VARCHAR2(3000),
BUG_TOTAL_CNT NUMBER,
RETURN_CNT NUMBER,
NOT_NOTE_CNT NUMBER,
VERSION_COMP_YN VARCHAR2(1),
BUG_LEVEL_CNT1 NUMBER,
BUG_LEVEL_CNT2 NUMBER,
BUG_LEVEL_CNT3 NUMBER,
TEST_REMARK VARCHAR2(3000),
REGR_ID VARCHAR2(8),
REG_DT DATE,
UPDR_ID VARCHAR2(8),
UPD_DT DATE);
-------------------prob table--------------------
create table ADMIN.PROB(
PROB_NO VARCHAR2(9) not null primary key,
PGM_NO VARCHAR(9) not null,
PGM_NM VARCHAR2(100),
SOR_NO VARCHAR2(15),
BUSINES_FG VARCHAR2(2),
BUSINES_AREA VARCHAR2(2),
PROB_STS VARCHAR2(2),
PROB_TITLE VARCHAR2(300),
PROB_CNTS VARCHAR2(3000),
PROB_CAUSE VARCHAR2(3000),
ANSWER_CNTS VARCHAR2(3000),
REGR_ID VARCHAR2(8),
REG_DT DATE,
UPDR_ID VARCHAR2(8),
UPD_DT DATE);
--------------------------------------------