02. tablespace생성, 사용자 생성

01.오라클TableSpace생성.txt

Tablespace 생성

Tablespace 생성에 앞서 데이터를 어느곳에 보관하는것이 안전하고 편리한지 한번 고민해 보는게 좋다.

sys계정으로 접속 후 

select * from dba_data_files;

Data tablespace 생성

CREATE TABLESPACE SAMPLE_DATA
DATAFILE 'D:\database\sample\data\SAMPLE_DATA.DBF' SIZE 1G 
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Index tablespace 생성

CREATE TABLESPACE SAMPLE_IDX
DATAFILE 'D:\database\sample\index\SAMPLE_IDX.DBF' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Temp tablespace 생성

CREATE TEMPORARY TABLESPACE SAMPLE_TMP
TEMPFILE 'D:\database\sample\temp\SAMPLE_TMP.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

사용자 생성

CREATE USER sample_user IDENTIFIED BY pass00 
DEFAULT TABLESPACE SAMPLE_DATA
TEMPORARY TABLESPACE SAMPLE_TMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

사용자 권한 부여

GRANT RESOURCE TO sample_user;
GRANT CONNECT TO sample_user;
GRANT CREATE VIEW TO sample_user;
GRANT CREATE SYNONYM TO sample_user;