2023-08-14 16:19:25 +00:00
|
|
|
|
### DB생성
|
|
|
|
|
```SQL
|
|
|
|
|
|
|
|
|
|
-- 티베로 종료
|
|
|
|
|
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
### 테이블 스페이스 생성
|
|
|
|
|
```SQL
|
2024-08-23 08:36:39 +00:00
|
|
|
|
-- 서버 물리경로에 폴더 생성
|
|
|
|
|
mkdir -p /home/tibero/tibero_data/sacp/datafile/{UGAM,UGAMEAI}
|
|
|
|
|
|
2023-08-14 16:19:25 +00:00
|
|
|
|
/* 테이블 스패이스 생성 */
|
|
|
|
|
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;
|
|
|
|
|
|
|
|
|
|
|
2024-08-23 08:36:39 +00:00
|
|
|
|
------------
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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;
|
|
|
|
|
|
2023-08-14 16:19:25 +00:00
|
|
|
|
|
|
|
|
|
|
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
### 기타
|
|
|
|
|
``` SQL
|
|
|
|
|
-- 테이블 스페이스 삭제
|
|
|
|
|
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
|
|
|
|
|
|
|
|
|
|
```
|