[Oracle] 사용자 생성하기 - User / Tablespace / Table 생성하기

Spring Boot / Oracle / Mybatis 차례

 

docker에서 sqlplus 접속

$ docker ps -a
CONTAINER ID        IMAGE                   COMMAND             CREATED             STATUS              PORTS                              NAMES
abd9f65ca3dd        jaspeen/oracle-xe-11g   "/entrypoint.sh "   2 weeks ago         Up 2 minutes        0.0.0.0:1521->1521/tcp, 8080/tcp   oracle11g

STATUS에 oracle11g가 구동 중인 것을 확인할 수 있습니다.

$ docker exec -it oracle11g sqlplus
Enter user-name: system
Enter password: oracle
SQL> 

 

Table Space

생성하기

SQL> CREATE TABLESPACE DEV_SPACE DATAFILE 'dev_space.dat' SIZE 500M AUTOEXTEND ON NEXT 10M;

Tablespace created.

Tablespace 크기 변경하기

SQL> ALTER DATABASE DATAFILE 'dev_space.dat' RESIZE 900M;

Tablespace 정보확인

SQL> SELECT FILE_NAME, TABLESPACE_NAME, BYTES, STATUS FROM DBA_DATA_FILES;

Tablespace 삭제하기

SQL> DROP TABLESPACE DEV_SPACE INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

 

User

생성하기

SQL> DEFINE NEW_USER = 'dev_user';
SQL> CREATE USER &NEW_USER. IDENTIFIED BY &NEW_USER.;
old   1: CREATE USER &NEW_USER. IDENTIFIED BY &NEW_USER.
new   1: CREATE USER dev_user IDENTIFIED BY dev_user

User created.

ID: dev_user

PASSWORD: dev_user로 생성했습니다.

User 생성 확인하기

SQL> SELECT * FROM ALL_USERS;
USERNAME			  USER_ID CREATED
------------------------------ ---------- ---------
XS$NULL 		       2147483638 28-AUG-11
DEV_USER			       48 18-SEP-19
APEX_040000			       47 28-AUG-11
APEX_PUBLIC_USER		       45 28-AUG-11
FLOWS_FILES			       44 28-AUG-11
HR				       43 28-AUG-11
MDSYS				       42 28-AUG-11
ANONYMOUS			       35 28-AUG-11
XDB				       34 28-AUG-11
CTXSYS				       32 28-AUG-11
OUTLN					9 28-AUG-11

USERNAME			  USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM					5 28-AUG-11
SYS					0 28-AUG-11

13 rows selected.

user 삭제하기

SQL> DROP USER &NEW_USER. CASCADE;
old   1: DROP USER &NEW_USER. CASCADE
new   1: DROP USER dev_user CASCADE

User dropped.

 

권한 부여

SQL> GRANT CONNECT, RESOURCE, CREATE JOB, CREATE VIEW, CREATE ANY CONTEXT TO &NEW_USER;
old   1: GRANT CONNECT, RESOURCE, CREATE JOB, CREATE VIEW, CREATE ANY CONTEXT TO &NEW_USER
new   1: GRANT CONNECT, RESOURCE, CREATE JOB, CREATE VIEW, CREATE ANY CONTEXT TO dev_user

Grant succeeded.

권한 해제

SQL> REVOKE CONNECT, RESOURCE, CREATE JOB, CREATE VIEW, CREATE ANY CONTEXT FROM &NEW_USER;
old   1: REVOKE CONNECT, RESOURCE, CREATE JOB, CREATE VIEW, CREATE ANY CONTEXT FROM &NEW_USER
new   1: REVOKE CONNECT, RESOURCE, CREATE JOB, CREATE VIEW, CREATE ANY CONTEXT FROM dev_user

Revoke succeeded.

 

user 비밀번호 변경

SQL> ALTER USER &NEW_USER IDENTIFIED BY "1234";
old   1: ALTER USER &NEW_USER IDENTIFIED BY "1234"
new   1: ALTER USER dev_user IDENTIFIED BY "1234"

User altered.

user 용량할당

SQL> ALTER USER &NEW_USER QUOTA UNLIMITED ON USERS;
old   1: ALTER USER &NEW_USER QUOTA UNLIMITED ON USERS
new   1: ALTER USER dev_user QUOTA UNLIMITED ON USERS

User altered.

user tablespace 할당

SQL> ALTER USER &NEW_USER DEFAULT TABLESPACE DEV_SPACE;
old   1: ALTER USER &NEW_USER DEFAULT TABLESPACE DEV_SPACE
new   1: ALTER USER dev_user DEFAULT TABLESPACE DEV_SPACE

User altered.

 

SQL Developer로 접속하기

생성한 dev_user로 새로운 접속을 합니다.

Table 생성하기

-- 테이블 생성
CREATE TABLE BOARD (
      IDX        NUMBER          NOT NULL PRIMARY KEY
    , NAME       VARCHAR2(50)    NOT NULL
    , EMAIL      VARCHAR2(50)    NOT NULL
    , PASSWD     VARCHAR2(50)    NOT NULL
    , CONTENT    VARCHAR2(1000)  NOT NULL
    , POST_DATE  DATE DEFAULT SYSDATE
);

-- 시퀀스
CREATE SEQUENCE BOARD_SEQ
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE 
    NOCACHE; 
 
 -- 데이터 입력
INSERT INTO BOARD (
      IDX
    , NAME
    , EMAIL
    , PASSWD
    , CONTENT
) VALUES ( 
      BOARD_SEQ.NEXTVAL
    , 'james'
    , 'james@gmail.com'
    , '1111'
    , '안녕하세요. 제임스입니다.'
);

SELECT 
       IDX 
     , NAME 
     , EMAIL 
     , PASSWD
     , CONTENT 
  FROM BOARD 
 ORDER BY IDX DESC;

Spring Boot / Oracle / Mybatis 차례