Obsidian/Recognition/Programing/Tibero/스키마 생성.md

DB생성

-- 티베로 종료
tbdown

-- nomount 로 부팅
tbboot nomount
-- sys계정 접속
tbsql sys/tibero


-- DB생성성
create database "SACP_T_DB"
	user sys identified by tibero
	maxinstances 8
	-- 데이터베이스에서 사용할 데이터 파일의 최댓값을 설정한다. (기본값: 100)
	maxdatafiles 100 
	-- 데이터베이스의 문자 집합을 설정한다.
	character set MSWIN949
	national character set UTF16
	logfile
	group 1 'log001.log' size 300M,
	group 2 'log002.log' size 300M,
	group 3 'log003.log' size 300M
	-- 로그 그룹의 최댓값을 설정한다. (기본값: 255)
	maxloggroups 255
	-- 로그 그룹 내의 로그 파일의 최댓값을 설정한다. (기본값: 8)
	maxlogmembers 8
	noarchivelog
	datafile 'system001.dtf' size 1G autoextend on next 100M maxsize unlimited
	default temporary tablespace TEMP
	tempfile 'temp001.dtf' size 1G autoextend on next 100M maxsize unlimited
	extent management local autoallocate
	undo tablespace UNDO
	datafile 'undo001.dtf' size 1G autoextend on next 100M maxsize unlimited
	extent management local autoallocate
	SYSSUB
	datafile 'syssub001.dtf' size 1G autoextend on next 100M maxsize unlimited
	default tablespace USR
	datafile 'usr001.dtf' size 1G autoextend on next 100M maxsize unlimited
	extent management local autoallocate;

-----------------------------------------------------------------------------------------------
create database "SACP_T_DB"
	user sys identified by tibero
	maxinstances 8
	maxdatafiles 100
	character set MSWIN949
	national character set UTF16
	logfile
	group 1 'log001.log' size 300M,
	group 2 'log002.log' size 300M,
	group 3 'log003.log' size 300M
	maxloggroups 255
	maxlogmembers 8
	noarchivelog
	datafile 'system001.dtf' size 5G autoextend on next 100M maxsize unlimited
	default temporary tablespace TEMP
	tempfile 'temp001.dtf' size 10G autoextend on next 100M maxsize unlimited
	extent management local autoallocate
	undo tablespace UNDO
	datafile 'undo001.dtf' size 10G autoextend on next 100M maxsize unlimited
	extent management local autoallocate
	SYSSUB
	datafile 'syssub001.dtf' size 30M autoextend on next 100M maxsize unlimited
	default tablespace USR
	datafile 'usr001.dtf' size 5G autoextend on next 100M maxsize unlimited
	extent management local autoallocate;
-----------------------------------------------------------------------------------------------
> Database created. 



SQL> quit

-- 티베로 재시작
tbdown
tbboot

-- Data Dictionary 및 System 패키지 생성
cd $TB_HOME/scripts
sh $TB_HOME/scripts/system.sh -p1 tibero -p2 syscat -a1 y -a2 y -a3 y -a4 y

테이블 스페이스 생성

-- 서버 물리경로에 폴더 생성
mkdir -p /home/tibero/tibero_data/sacp/datafile/{UGAM,UGAMEAI}

/* 테이블 스패이스 생성 */
create tablespace TS_EAI datafile '/Data/tibero/SACP_T_DB/datafile/UEAI/TS_EAI01.dtf' size 10G;
ALTER TABLESPACE TS_EAI add datafile '/Data/tibero/SACP_T_DB/datafile/UEAI/TS_EAI02.dtf' size 10G;
alter tablespace TS_EAI add datafile '/Data/tibero/SACP_T_DB/datafile/UEAI/TS_EAI03.dtf' size 10G;

create tablespace TS_CM datafile '/Data/tibero/SACP_T_DB/datafile/UCM/TS_CM01.dtf' size 10G;

create tablespace TS_IC datafile '/Data/tibero/SACP_T_DB/datafile/UIC/TS_IC01.dtf' size 10G;

create tablespace TS_DT datafile '/etc/tibero/tibero7/database/SACP_DT_DB/TS_DT01.dtf' size 1G;
alter tablespace TS_DT add datafile '/etc/tibero/tibero7/database/SACP_DT_DB/TS_IC02.dtf' size 1G;
alter tablespace TS_DT add datafile '/etc/tibero/tibero7/database/SACP_DT_DB/TS_IC03.dtf' size 1G;

/* 유저생성 */
CREATE USER UEAI IDENTIFIED BY UEAI DEFAULT TABLESPACE TS_EAI;
CREATE USER UTRACK IDENTIFIED BY UTRACK DEFAULT TABLESPACE TS_TRACK;
CREATE USER UCM IDENTIFIED BY UCM DEFAULT TABLESPACE TS_CM;
CREATE USER UIC IDENTIFIED BY UIC DEFAULT TABLESPACE TS_IC;
CREATE USER UDT IDENTIFIED BY UDT DEFAULT TABLESPACE TS_DT;

/* 권한 부여 */
GRANT RESOURCE, CONNECT TO UEAI;
GRANT RESOURCE, CONNECT TO UTRACK;
GRANT RESOURCE, CONNECT TO UCM;
GRANT RESOURCE, CONNECT TO UIC;
GRANT RESOURCE, CONNECT TO UDT;

 GRANT SELECT ANY TABLE TO UCM;
 GRANT SELECT ANY TABLE TO UIC;
 GRANT SELECT ANY TABLE TO UTRACK;
 GRANT SELECT ANY TABLE TO UEAI;
 GRANT SELECT ANY TABLE TO UDT;


------------


create tablespace TS_GAMEAI datafile '/home/tibero/tibero_data/sacp/datafile/UGAMEAI/TS_GAMEAI.dtf' size 10G;
create tablespace TS_GAM datafile '/home/tibero/tibero_data/sacp/datafile/UGAM/TS_GAM.dtf' size 10G;


CREATE USER UGAMEAI IDENTIFIED BY UGAMEAI DEFAULT TABLESPACE TS_GAMEAI;

CREATE USER UGAM IDENTIFIED BY UGAM DEFAULT TABLESPACE TS_GAM;


GRANT RESOURCE, CONNECT TO UGAMEAI;

GRANT RESOURCE, CONNECT TO UGAM;


 GRANT SELECT ANY TABLE TO UGAM;

기타

-- 테이블 스페이스 삭제
DROP TABLESPACE ts_tigers INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

-- 유저생성
CREATE USER tigers IDENTIFIED BY tigers
DEFAULT TABLESPACE ts_tigers
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK ;

-- 유저 삭제
DEFAULT TABLESPACE user01

-- 유저 권한
GRANT RESOURCE, CONNECT, DBA TO user01