반응형
Notice
Recent Posts
Recent Comments
Link
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | |||||
3 | 4 | 5 | 6 | 7 | 8 | 9 |
10 | 11 | 12 | 13 | 14 | 15 | 16 |
17 | 18 | 19 | 20 | 21 | 22 | 23 |
24 | 25 | 26 | 27 | 28 | 29 | 30 |
Tags
- xcode
- ReactNative
- node
- vsCode
- 리눅스
- androidstudio
- IOS
- picker
- 센토스
- linux
- localserver
- 네트워크
- 개발
- TensorFlow
- build
- webpack
- Android
- MAC
- VirtualBox
- centos
- qunit
- jest
- 맥
- PYTHON
- Chrome
- unittest
- react
- avds
- MachineLearning
Archives
- Today
- Total
로메오의 블로그
[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;
반응형
'Backend > Oracle & SQL' 카테고리의 다른 글
[Docker] Docker-compose 설정 (0) | 2019.09.22 |
---|---|
[SQL Developer] Oracle 서버 접속시 무한로딩 해결방법 (1) | 2019.09.19 |
[SQL] Oracle SQL - NVL, NVL2, DECADE (0) | 2019.09.03 |
[SQL] Oracle SQL Query - CASE WHEN ELSE (0) | 2019.09.02 |
[SQLDeveloper] locale not recognized 오류 (0) | 2019.09.02 |
Comments