'데이터베이스'에 해당되는 글 7건
- 2017.06.30 :: 오라클 Hidden Column - SYS_로 시작하는 컬럼 1
- 2017.06.28 :: 오라클 권한 조회
- 2017.06.23 :: 오라클 계정과 권한
- 2017.06.16 :: 오라클 TABLESPACE와 DATAFILE
- 2017.05.11 :: TIMESTAMP 연산
- 2017.02.02 :: 오라클 - PIVOT과 UNPIVOT
- 2017.01.24 :: 오라클의 동적 PL SQL 작성 - EXECUTE IMMEDIATE
Function Based Index 또는 Descending Index로 인덱스 생성 시 Dictionary 테이블(ALL_IND_COLUMNS)의 인덱스 컬럼이 SYS_로 시작하는 알 수 없는 컬럼으로 조회가 됩니다. 또는 ALL_TAB_COLS 테이블에는 생성하지 않는 컬럼(SYS_로 시작하는)이 조회됩니다. 테이블 정의서 작성을 위해 위의 Dictionary 테이블을 사용할 때 주의하세요!
Descending Index 생성 및 Dictionary 테이블 조회
1. 테스트 테이블 생성 및 Descending Index 생성
1 2 3 4 5 6 7 8 9 10 | /* 테이블 생성 */ CREATE TABLE TB_IND_TEST( COL1 VARCHAR2(10), COL2 VARCHAR2(20), COL3 VARCHAR2(30) GENERATED ALWAYS AS ( COL1 || COL2 ) VIRTUAL ); /* Descending Index 생성 */ CREATE INDEX IDX_TB_IND_TEST_01 ON TB_IND_TEST (COL1 DESC, COL2 DESC); | cs |
ALL_TAB_COLS 테이블의 HIDDEN_COLUMN항목과 VIRTUAL_COLUMN의 차이를 알기 위해 테이블을 생성 시 COL3 컬럼을 가상컬럼으로 생성하였습니다.
가상컬럼 SYNTAX
COLUMN_NAME [DATA_TYPE] [GENERATED ALWAYS] AS ( expression ) [VIRTUAL]
2. Dictionary 테이블 조회
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 | /* 인덱스 컬럼 조회 */ SELECT T.INDEX_NAME , T.TABLE_NAME , T.COLUMN_NAME FROM ALL_IND_COLUMNS T WHERE 1=1 AND T.TABLE_NAME = 'TB_IND_TEST' ; /* ALL_TAB_COLUMNS 테이블 조회 */ SELECT T.TABLE_NAME , T.COLUMN_NAME , T.DATA_TYPE FROM ALL_TAB_COLUMNS T WHERE 1=1 AND T.TABLE_NAME = 'TB_IND_TEST' ORDER BY T.COLUMN_ID ; /* ALL_TAB_COLS 테이블 조회 */ SELECT T.TABLE_NAME , T.COLUMN_NAME , T.DATA_TYPE , T.HIDDEN_COLUMN , T.VIRTUAL_COLUMN FROM ALL_TAB_COLS T WHERE 1=1 AND T.TABLE_NAME = 'TB_IND_TEST' ORDER BY T.COLUMN_ID ; | cs |
'데이터베이스' 카테고리의 다른 글
| 오라클 권한 조회 (0) | 2017.06.28 |
|---|---|
| 오라클 계정과 권한 (0) | 2017.06.23 |
| 오라클 TABLESPACE와 DATAFILE (0) | 2017.06.16 |
| TIMESTAMP 연산 (0) | 2017.05.11 |
| 오라클 - PIVOT과 UNPIVOT (0) | 2017.02.02 |
사용자 및 ROLE에 부여된 권한조회하기
데이터베이스 작업 시 "권한이 불충분합니다."라는 오류 메시지와 마주할 때가 있습니다. 그럴때는 당황하지 마시고 현재 사용자의 권한을 확인하여 처리하시면 됩니다.
사용자 생성
권한부여 및 조회를 위한 테스트 계정을 만들어보겠습니다.
- SYNTAX
CREATE USER USER_NAME IDENTIFIED BY PASSWORD
[ DEFAULT TABLESPACE TABLESPACE_NAME ]
[ TEMPORARY TABLESPACE TEMP ]
;
- EX)
CREATE USER TEST IDENTIFIED BY TEST1234
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
※ 사용자 삭제
- SYNTAX
DROP USER USER_NAME [CASCADE];
- EX)
DROP USER TEST_USER CASCADE;
- CASCADE : 사용자에게 부여된 권한이나 사용자가 만든 객체가 존재할 경우 CASCADE 옵션을 사용해야 해당 사용자를 삭제할 수 있음
데이터베이스 접속 테스트
사용자가 정상적으로 생성되었는지 데이터베이스에 접속을 시도하면
아래와 같은 오류가 발생됩니다.
sqlplus TEST_USER/TEST1234@ORCL
ERROR: ORA-01045: user TEST_USER lacks CREATE SESSION privilege; logon denied
사용자에게 접속권한이 없다고 ERROR 메시지를 던져주네요!
권한부여
sqlplus / as sysdba
SQL> GRANT CREATE SESSION, CREATE TABLE TO TEST_USER;
-접속확인
SQL> conn TEST_USER/TEST1234@ORCL
※ 사용자에게 권한 부여 및 회수와 관련된 자세한 내용은 오라클 계정과 권한 포스팅을 참고하세요!
권한조회
1. USER 또는 ROLE에 부여된 시스템 권한조회
SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION
FROM DBA_SYS_PRIVS;
또는
SELECT USERNAME, PRIVILEGE, ADMIN_OPTION
FROM USER_SYS_PRIVS;
- GRANTEE : 시스템 권한을 부여받은 USER 또는 ROLE
- USERNAME : 현재 접속된 사용자
- PRIVILEGE : 부여받은 시스템 권한
- ADMIN_OPTION : 부여받은 시스템 권한을 다른 사용자에게 부여할 수 있음(YES일 경우)
2. USER 또는 ROLE에 부여된 객체 권한조회
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTOR
FROM DBA_TAB_PRIVS
또는
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE, GRANTOR
FROM USER_TAB_PRIVS
- GRANTEE : 객체 권한을 부여받은 USER 또는 ROLE
- OWNER : 객체의 소유자
- TABLE_NAME : 객체
- GRANTOR : 객체 권한을 부여해준 USER
3. USER 또는 ROLE에 부여된 ROLE 조회
※ DEFAULT ROLE
- 데이터베이스 설치 시 자동으로 생성되는 ROLE
- 대표적인 Default Role은 CONNECT, RESOURCE가 있음
- RESOURCE 롤은 UNLIMITED_TABLESPACE라는 권한이 할당되어 있음
- UNLIMITED_TABLESPACE 권한을 가진 사용자는 데이터베이스에 존재하는 모든 테이블 스페이스를 저장공간으로 사용가능
'데이터베이스' 카테고리의 다른 글
| 오라클 Hidden Column - SYS_로 시작하는 컬럼 (1) | 2017.06.30 |
|---|---|
| 오라클 계정과 권한 (0) | 2017.06.23 |
| 오라클 TABLESPACE와 DATAFILE (0) | 2017.06.16 |
| TIMESTAMP 연산 (0) | 2017.05.11 |
| 오라클 - PIVOT과 UNPIVOT (0) | 2017.02.02 |
SYS와 SYSTEM 계정
1. SYS 계정
- 오라클 데이터베이스가 설치되면 자동으로 생성되는 계정(슈퍼 사용자)
- Dictionary Table의 소유자
- SYS 스키마에는 테이블 생성 불가(데이터베이스에 의해서만 수정가능)
- sysdba로 접속할 때 기본 스키마
- 데이터베이스 생성가능(sysdba privilege를 가짐)
2. SYSTEM
- SYS계정과 마찬가지로 데이터베이스가 설치되면서 생성되는 계정
- Dictionary View 소유자
- 데이터베이스 생성 불가
- 데이터베이스 관리에 필요한 table 및 view 생성
- 시스템 권한 : 데이터베이스 접속, 사용자 및 오브젝트 생성 등
- 오브젝트 권한 : 오브젝트 사용에 대한 권한
1. 시스템 권한
시스템 권한 (CREATE, ALTER, DROP) |
설명 |
CREATE USER |
사용자 생성 권한 |
CREATE SESSION |
데이터베이스 접속 권한 |
CREATE ANY TABLE |
모든 사용자의 테이블 생성 권한 |
SELECT ANY TABLE |
모든 사용자의 테이블 조회 권한 |
CREATE TABLE |
테이블 생성 권한 |
CREATE VIEW |
뷰 생성 권한 |
CREATE PROCEDURE |
프로시저 생성 권한 |
CREATE SEQUENCE |
시퀀스 생성 권한 |
SYSDBA |
데이터베이스 관리를 위한 슈퍼권한 |
| SYSOPER | 데이터베이스 관리 권한 |
1.1 SYSDBA와 SYSOPER의 상세권한
권한 |
설명 |
SYSOPER |
※ 다른 유저의 데이터를 조회할 수는 없음 |
| SYSDBA |
|
SQL PLUS를 이용해서 SYSDBA로 접속하기
=> sqlplus / as sysdba
=> show user 명령어 결과는 SYS로 나옵니다.
위의 SYS계정에서 설명했듯이 SYSDBA 접속 기본 스키마이기 때문입니다.
1.2 시스템 권한 부여 및 회수 문법
1.2.1 권한 부여
GRANT [권한/ROLE] TO [USER/ROLE/PUBLIC]
[WITH ADMIN OPTION]
- PUBLIC : 시스템 권한 및 ROLE을 모든 사용자에게 부여
- WITH ADMIN OPTION : 부여받은 권한을 다른 사용자 및 ROLE에게 부여
※ WITH ADMIN OPTION을 사용하여 부여한 권한은 연쇄적으로 회수되지 않음
EX) GRANT CREATE TABLE, CREATE USER TO SCOTT WITH ADMIN OPTION;
1.2.2. 권한 회수
REVOKE [권한/ROLE] FROM [USER/ROLE/PUBLIC]
EX) REVOKE CREATE TABLE, CREATE USER FROM SCOTT;
2. 오브젝트 권한
오브젝트 소유자는 해당 오브젝트의 모은 권한을 가지며, 오브젝트에 대한 특정 권한을 다른 사용자에게 부여할 수 있습니다.
권한 |
TABLE | VIEW | PROCEDURE | SEQUENCE |
SELECT |
O | O |
| O |
INSERT |
O |
|
|
|
DELETE |
O | O |
|
|
ALTER |
O |
|
| O |
INDEX |
O |
|
|
|
EXECUTE |
| O |
|
2.1 오브젝트 권한 부여 및 회수 문법
2.1.1 권한 부여
GRANT 권한[COLUMN] ON OBJECT TO [USER/ROLE/PUBLIC]
[WITH GRANT OPTION]
- WITH GRANT OPTION : 부여받은 권한을 다른 사용자 및 ROLE에게 부여
※ WITH GRANT OPTION을 사용하여 부여한 권한은 연쇄적으로 회수됨
EX) GRANT INSERT ON TB_TEST TO SCOTT WITH ADMIN OPTION;
2.1.2 권한 회수
REVOKE [권한/ALL] ON OBJECT FROM [USER/ROLE/PUBLIC]
[CASCADE CONSTRAINTS]
- 객체권한의 회수는 부여한 사용자만이 할 수 있음
- CASCADE CONSTRAINTS : 참조 객체에 사용된 참조 무결성 제약을 함께 삭제할 수 있음
EX) REVOKE INSERT ON TB_TEST FROM SCOTT;
'데이터베이스' 카테고리의 다른 글
| 오라클 Hidden Column - SYS_로 시작하는 컬럼 (1) | 2017.06.30 |
|---|---|
| 오라클 권한 조회 (0) | 2017.06.28 |
| 오라클 TABLESPACE와 DATAFILE (0) | 2017.06.16 |
| TIMESTAMP 연산 (0) | 2017.05.11 |
| 오라클 - PIVOT과 UNPIVOT (0) | 2017.02.02 |
TABLESPACE와 DATAFILE
- TABLESPACE : 논리적 데이터 저장 공간
- DATAFILE : 물리적 데이터 저장 공간, 즉 실제 데이터가 저장되는 Disk영역을 말 함
TABLESPACE 생성 Script
- DATAFILE 1개
1 2 3 | CREATE TABLESPACE TABLESPACE_NAME DATAFILE 'D:\ORACLE\ORADATA\ORCL\TEST_TS01' SIZE 100M AUTOEXTEND ON NEXT 2M MAXSIZE 500M; | cs |
- DATAFILE 1개 이상
1 2 3 | DATAFILE 'D:\ORACLE\ORADATA\ORCL\TEST_TS01' SIZE 30G AUTOEXTEND OFF , 'D:\ORACLE\ORADATA\ORCL\TEST_TS02' SIZE 30G AUTOEXTEND OFF , 'D:\ORACLE\ORADATA\ORCL\TEST_TS03' SIZE 30G AUTOEXTEND OFF | cs |
AUTOEXTEND
- DATAFILE SIZE 자동확장 여부, ON/OFF로 설정
- AUTOEXTEND가 OFF로 설정할 경우, NEXT, MAXSIZE의 크기는 0으로 설정해야 한다.
NEXT, MAXSIZE를 기술하지 않으면 Default 값(0)으로 설정됨
NEXT
- 초기설정 값(100M)보다 많은 공간이 요구될때 자동으로 확장되는 디스크 공간크기
- K(킬로 바이트), M(메가 바이트)단위 사용
MAXSIZE
- 확장 될수 있는 최대 크기 입니다.
- K(킬로 바이트), M(메가 바이트)단위 사용
- MAXSIZE가 명시되지 않았으면 데이터 파일의 최대 크기는 디스크 공간과 OS에서 지원되는 최대 파일 크기로 제한 된다.
ex) Windows 32bit : 16G
Windows 64bit : 32G
TABLESPACE 수정 Script
1 2 3 4 5 6 7 8 9 10 | 1) TABLEPACE에 수동으로 DATAFILE 추가 ALTER TABLESPACE DQ_MAN_TS ADD DATAFILE 'D:\ORACLE\ORADATA\ORCL\DQ_MAN_TS02.DBF' SIZE 30G; 2) 기존 DATAFILE 속성 변경 - 수동으로 SIZE증가 ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\ORCL\DQ_MAN_TS01.DBF' resize 2G; 3) 기존 DATAFILE 속성 변경 - 자동으로 SIZE증가 ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\ORCL\DQ_MAN_TS01.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 30G; | cs |
TABLESPACE 용량 및 사용량 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SELECT A.TABLESPACE_NAME AS "테이블스페이스" , ROUND(SUM(A.TOTAL)/1024/1024,1) "전체(MB)" , ROUND(SUM(A.TOTAL)/1024/1024,1)-ROUND(SUM(A.FREE_SPACE)/1024/1024,1) "사용(MB)" , ROUND(SUM(A.FREE_SPACE)/1024/1024,1) "여유(MB)" , ROUND((ROUND(SUM(A.TOTAL)/1024/1024,1)- ROUND(SUM(A.FREE_SPACE)/1024/1024,1))/ROUND(SUM(A.TOTAL)/1024/1024,1)*100,2) "사용률(%)" FROM (SELECT TABLESPACE_NAME , 0 AS TOTAL , SUM(BYTES) AS FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME UNION SELECT TABLESPACE_NAME , SUM(BYTES) TOTAL , 0 AS FREE_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A GROUP BY A.TABLESPACE_NAME ORDER BY TABLESPACE_NAME ; | cs |
DBA_SEGMENTS : 세그먼트의 할당량을 조회
DBA_FREE_SPACE : 사용가능한 익스텐트 정보 조회
DBA_DATA_FILES : 할당된 물리적 공간
'데이터베이스' 카테고리의 다른 글
| 오라클 권한 조회 (0) | 2017.06.28 |
|---|---|
| 오라클 계정과 권한 (0) | 2017.06.23 |
| TIMESTAMP 연산 (0) | 2017.05.11 |
| 오라클 - PIVOT과 UNPIVOT (0) | 2017.02.02 |
| 오라클의 동적 PL SQL 작성 - EXECUTE IMMEDIATE (0) | 2017.01.24 |
SQL의 실행 시간 측정등 밀리 세컨드 단위의 정밀한 작업이 필요한 곳에 TIMESTAMP 데이터 타입을 사용하죠!
그렇다면, TIMESTAMP 형식으로 등록된 데이터의 비교연산은 어떻게 할까요?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | -- TIMESTAMP 연산을 위한 2개의 COLUMN이 포함된 테이블을 생성합니다. CREATE TABLE TB_TIME_STAMP( FROM_TIME TIMESTAMP, TO_TIME TIMESTAMP ) ; -- 생성된 테이블에 TIMESTAMP 형식의 데이터를 등록합니다. INSERT INTO SCOTT.TB_TIME_STAMP(FROM_TIME, TO_TIME) VALUES(SYSTIMESTAMP - 1/24, SYSTIMESTAMP) ; -- 마직막으로 아래처럼 TIMESTAMP 연산을 수행하여 시간차이를 초단위로 변환합니다. SELECT EXTRACT(HOUR FROM DT) * 3600 + EXTRACT(MINUTE FROM DT) * 60 + EXTRACT(SECOND FROM DT) AS DIFF_TIME FROM ( SELECT TO_TIME - FROM_TIME AS DT FROM SCOTT.TB_TIME_STAMP ) ; | cs |
참고로, 위의 SYSTIMESTAMP - 1/24의 의미는 현재 시간에서 1시간 전의 값을 의미합니다.
그래서 두 컬럼의 연산 결과는 항상 3600.XXX가 되겠죠!
'데이터베이스' 카테고리의 다른 글
| 오라클 권한 조회 (0) | 2017.06.28 |
|---|---|
| 오라클 계정과 권한 (0) | 2017.06.23 |
| 오라클 TABLESPACE와 DATAFILE (0) | 2017.06.16 |
| 오라클 - PIVOT과 UNPIVOT (0) | 2017.02.02 |
| 오라클의 동적 PL SQL 작성 - EXECUTE IMMEDIATE (0) | 2017.01.24 |
PIVOT과 UNPIVOT
출력 데이터를 열 --> 행으로 또는 행 --> 열로 변환하기 위해 오라클 11g 이전 버전에서는 decode(CASE구분)을 사용해 해결하였으나, SQL의 가독성 및 성능에 문제점이 있었습니다. 그래서 오라클 11g 에서는 PIVOT/UNPIVOT이라는 기능을 지원하여 보다 쉽게 데이터를 가공할 수 있게 되었습니다.
사용 사례를 보면서 PIVOT과 UNPIVOT의 기능을 알아보겠습니다.
1. PIVOT(열 --> 행)
오라클에 기본적으로 설치된 SCOTT계정의 "SALGRADE" 라는 테이블을 사용해 설명하겠습니다.
PIVOT 기능을 적용할 데이터는 아래 그림과 같습니다.
[PIVOT 적용전 데이터]
PIVOT 기능을 적용하여 열로(LOSAL 값) 된 데이터를 행으로 변환
SYNTAX
PIVOT (집계함수([행의로 변환할 칼럼]) FOR [칼럼명으로 사용될 칼럼] IN ( 칼럼명으로 사용될 칼럼의 값, ...))
|
2. UNPIVOT(행 --> 열)
위에서 PIVOT 기능을 이용해 열 --> 행으로 변환된 데이터를 UNPIVOT 기능을 사용해 원래되로 다시 변환해 보겠습니다.
|
이론적으로 좀 더 깊게 알고 싶으시면 여기를 참조하세요!
'데이터베이스' 카테고리의 다른 글
| 오라클 권한 조회 (0) | 2017.06.28 |
|---|---|
| 오라클 계정과 권한 (0) | 2017.06.23 |
| 오라클 TABLESPACE와 DATAFILE (0) | 2017.06.16 |
| TIMESTAMP 연산 (0) | 2017.05.11 |
| 오라클의 동적 PL SQL 작성 - EXECUTE IMMEDIATE (0) | 2017.01.24 |
PL SQL 작성 시 동적으로 DDL구문 또는 SELECT/INSERT/UPDATE/DELETE 구문을 수행해야 할 때가 있다.
이때 유용하게 사용하는 것이 EXECUTE IMMEDIATE 문법이다.
SCOTT 계정의 테이블들의 건수를 TBL_CNT_LIST라는 테이블에 INSERT하는 예시로 EXECUTE IMMEDIATE 구문을 이해해보자!
1. SCOTT 계정으로 생성된 테이블들의 레코드 건수 정보를 INSERT할 TBL_CNT_LIST 테이블 생성
1 2 3 4 | CREATE TABLE TBL_CNT_LIST( TBL_NM VARCHAR2(30), CNT NUMBER(10) ); | cs |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE BEGIN -- 테이블 건수 정보 테이블의 데이터 삭제 DELETE FROM TBL_CNT_LIST; FOR X IN ( -- SCOTT 계정의 테이블 목록을 활용하여 INSERT 구문을 동적으로 생성한다. SELECT 'INSERT INTO TBL_CNT_LIST SELECT ' || CHR(39) || T.TABLE_NAME || CHR(39) || ' AS TBL_NM, COUNT(*) AS CNT FROM ' || T.TABLE_NAME AS INS_SQL FROM USER_TABLES T WHERE 1=1 ) LOOP -- EXECUTE IMMEDIATE 구문을 사용하여 INSERT 구문 수행 EXECUTE IMMEDIATE X.INS_SQL; END LOOP; COMMIT; END; / | cs |
'데이터베이스' 카테고리의 다른 글
| 오라클 권한 조회 (0) | 2017.06.28 |
|---|---|
| 오라클 계정과 권한 (0) | 2017.06.23 |
| 오라클 TABLESPACE와 DATAFILE (0) | 2017.06.16 |
| TIMESTAMP 연산 (0) | 2017.05.11 |
| 오라클 - PIVOT과 UNPIVOT (0) | 2017.02.02 |