[Oracle] 20231017 [프로그램 소스] - 20231017_01_sys, 20231017_02_hr, 20231017_03_kjm

 

 

 

 

20231017_01_sys.sql

-- 1줄 주석문 처리(단일행 주석문 처리)

/*
여러 줄 
(다중행)
주석문
처리
*/


--○ 현재 오라클 서버에 접속한 자신의 계정 조회
show user
--==>> USER이(가) "SYS"입니다.
--> sqlplus 상태일 때 사용하는 명령어

select user
from dual;
--==>> SYS

--* 오라클의 select 절은 from 없이 사용할 수 없다.
SELECT USER
FROM DUAL;
--==>> SYS

SELECT 1 + 2
FROM DUAL;
--==>> 3


SELECT              1         +         2
FROM dual;
--==>> 3

SELECT 1 + 2
FROMDUAL;
--==>> 에러 발생
--  (ORA-00923: FROM keyword not found where expected)
--  select 절이 있는데 from 절이 없으므로

SELECT 오라클수업;
--==>> 에러 발생
--  (ORA-00923: FROM keyword not found where expected)


SELECT 오라클수업
FROM DUAL;
--==>> 에러 발생
--  (ORA-00904: "오라클수업": invalid identifier)


SELECT "오라클수업"
FROM DUAL;
--==>> 에러 발생
--  (ORA-00904: "오라클수업": invalid identifier)


SELECT '오라클수업'
FROM DUAL;
--==>> 오라클수업

SELECT '한 발 한 발 힘겨운 오라클 수업'
FROM DUAL;
--==>> 한 발 한 발 힘겨운 오라클 수업


SELECT 3.14 + 3.14
FROM DUAL;
--==>> 6.28


SELECT 10 * 5
FROM DUAL;
--==>> 50


SELECT 10 * 5.0
FROM DUAL;
--==>> 50


SELECT 4 / 2
FROM DUAL;
--==>> 2

SELECT 10 / 2.5
FROM DUAL;
--==>> 4

SELECT 10 / 2.4
FROM DUAL;
--==>> 4.16666666666666666666666666666666666667


SELECT 4.0 / 2
FROM DUAL;
--==>> 2


SELECT 5 / 2
FROM DUAL;
--==>> 2.5


SELECT 100 - 78
FROM DUAL;
--==>> 22

SELECT '김동민' + '정한울'
FROM DUAL;
--==>> 에러 발생
--  (ORA-01722: invalid number)


--○ 현재 오라클 서버에 존재하는 사용자 계정 상태 조회

SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS;
--==>>
/*
SYS	                    OPEN
SYSTEM	                OPEN
ANONYMOUS	            OPEN
HR	                    OPEN
APEX_PUBLIC_USER	    LOCKED
FLOWS_FILES	            LOCKED
APEX_040000	            LOCKED
OUTLN	                EXPIRED & LOCKED
DIP                 	EXPIRED & LOCKED
ORACLE_OCM	            EXPIRED & LOCKED
XS$NULL	                EXPIRED & LOCKED
MDSYS	                EXPIRED & LOCKED
CTXSYS	                EXPIRED & LOCKED
DBSNMP	                EXPIRED & LOCKED
XDB                 	EXPIRED & LOCKED
APPQOSSYS	            EXPIRED & LOCKED
*/

SELECT *
FROM DBA_USERS;
--==>>
/*
SYS	0		OPEN		24/04/13	SYSTEM	TEMP	14/05/29	DEFAULT	SYS_GROUP		10G 11G 	N	PASSWORD
SYSTEM	5		OPEN		24/04/13	SYSTEM	TEMP	14/05/29	DEFAULT	SYS_GROUP		10G 11G 	N	PASSWORD
ANONYMOUS	35		OPEN		14/11/25	SYSAUX	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP			N	PASSWORD
HR	43		OPEN		24/04/14	USERS	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
APEX_PUBLIC_USER	45		LOCKED	14/05/29	14/11/25	SYSTEM	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
FLOWS_FILES	44		LOCKED	14/05/29	14/11/25	SYSAUX	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
APEX_040000	47		LOCKED	14/05/29	14/11/25	SYSAUX	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
OUTLN	9		EXPIRED & LOCKED	23/10/16	23/10/16	SYSTEM	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
DIP	14		EXPIRED & LOCKED	14/05/29	14/05/29	SYSTEM	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
ORACLE_OCM	21		EXPIRED & LOCKED	14/05/29	14/05/29	SYSTEM	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
XS$NULL	2147483638		EXPIRED & LOCKED	14/05/29	14/05/29	SYSTEM	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
MDSYS	42		EXPIRED & LOCKED	14/05/29	23/10/16	SYSAUX	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
CTXSYS	32		EXPIRED & LOCKED	23/10/16	23/10/16	SYSAUX	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
DBSNMP	29		EXPIRED & LOCKED	14/05/29	14/05/29	SYSAUX	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
XDB	34		EXPIRED & LOCKED	14/05/29	14/05/29	SYSAUX	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
APPQOSSYS	30		EXPIRED & LOCKED	14/05/29	14/05/29	SYSAUX	TEMP	14/05/29	DEFAULT	DEFAULT_CONSUMER_GROUP		10G 11G 	N	PASSWORD
*/


SELECT USERNAME, CREATED 
FROM DBA_USERS;
--==>>
/*
SYS	                14/05/29
SYSTEM	            14/05/29
ANONYMOUS	        14/05/29
HR	                14/05/29
APEX_PUBLIC_USER	14/05/29
FLOWS_FILES	        14/05/29
APEX_040000	        14/05/29
OUTLN	            14/05/29
DIP	                14/05/29
ORACLE_OCM	        14/05/29
XS$NULL	            14/05/29
MDSYS	            14/05/29
CTXSYS	            14/05/29
DBSNMP	            14/05/29
XDB	                14/05/29
APPQOSSYS	        14/05/29
*/

/*
SYS	                2014-05-29
SYSTEM	            2014-05-29
ANONYMOUS	        2014-05-29
HR	                2014-05-29
APEX_PUBLIC_USER	2014-05-29
FLOWS_FILES	        2014-05-29
APEX_040000	        2014-05-29
OUTLN	            2014-05-29
DIP               	2014-05-29
ORACLE_OCM      	2014-05-29
XS$NULL	            2014-05-29
MDSYS	            2014-05-29
CTXSYS	            2014-05-29
DBSNMP	            2014-05-29
XDB             	2014-05-29
APPQOSSYS	        2014-05-29
*/

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
--==>> Session이(가) 변경되었습니다.


--> 『DBA_』로 시작하는 Oracle Data Dictionary View 는
--   오로지 관리자 권한으로 접속했을 경우에만 조회가 가능하다.


--○ 『HR』 사용자 계정을 잠금 상태로 설정

ALTER USER HR ACCOUNT LOCK;
//--==>> User HR이(가) 변경되었습니다.

--○ 사용자 계정 상태 조회
SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS;
--==>>
/*
     :
 HR     LOCKED
     :
*/


--○ 『HR』 사용자 계정을 다시 잠금 해제 상태로 설정
ALTER USER HR ACCOUNT UNLOCK;
--==>> User HR이(가) 변경되었습니다.

SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS;
--==>>
/*
     :
 HR     OPEN
     :
*/


SELECT USER
FROM DUAL;



--------------------------------------------------------------------------------

-- TABLE 이 들어가서 저장되는 공간 (사물함) - TABLESPACE
--○ TABLESPACE 생성

--※ TABLESPACE 란?
-- 세그먼트(테이블, 인덱스, ...)를 담아두는(저장해두는)
-- 오라클의 논리적인 저장구조(하드디스크)를 의미한다.


CREATE TABLESPACE TBS_EDUA              -- 생성하겠다. 테이블스페이스를... TBS_EDUA 라는 이름으로
DATAFILE 'C:\TESTDATA\TBS_EDUA01.DBF'   -- 물리적 데이터 파일 경로 및 이름
SIZE 4M                                 -- 사이즈(용량)
EXTENT MANAGEMENT LOCAL                 -- 오라클 서버가 세그먼트를 알아서 관리
SEGMENT SPACE MANAGEMENT AUTO;          -- 세그먼트 공간 관리도 오라클 서버가 자동으로 관리
--==>> TABLESPACE TBS_EDUA이(가) 생성되었습니다.

--※ 테이블 스페이스 생성 구문을 실행하기 전에
--   해당 경로의 물리적인 디렉터리 생성이 필요하다.
--   (C:\TESTDATA)

-- DBA_ : 오라클이 자체적으로 관리하는 것 [데이터 딕셔너리 뷰]
-- dba_users; 데이터 딕셔너리 뷰...
-- 직접 insert, delete, .. 등을 할 수 없고 오라클에서 자체적으로 관리하면서 상태를 알려주는 표..

--○ 생성된 테이블스페이스 조회
SELECT *
FROM DBA_TABLESPACES;
--==>>
/*
SYSTEM	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	NO	LOCAL	SYSTEM	NO	MANUAL	DISABLED	NOT APPLY	NO	HOST	NO	
SYSAUX	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	NO	LOCAL	SYSTEM	NO	AUTO	DISABLED	NOT APPLY	NO	HOST	NO	
UNDOTBS1	8192	65536		1	2147483645	2147483645		65536	ONLINE	UNDO	LOGGING	NO	LOCAL	SYSTEM	NO	MANUAL	DISABLED	NOGUARANTEE	NO	HOST	NO	
TEMP	8192	1048576	1048576	1		2147483645	0	1048576	ONLINE	TEMPORARY	NOLOGGING	NO	LOCAL	UNIFORM	NO	MANUAL	DISABLED	NOT APPLY	NO	HOST	NO	
USERS	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	NO	LOCAL	SYSTEM	NO	AUTO	DISABLED	NOT APPLY	NO	HOST	NO	
TBS_EDUA	8192	65536		1	2147483645	2147483645		65536	ONLINE	PERMANENT	LOGGING	NO	LOCAL	SYSTEM	NO	AUTO	DISABLED	NOT APPLY	NO	HOST	NO	
*/

--○ 파일 용량 정보 조회(물리적인 파일 이름 조회)
SELECT *
FROM DBA_DATA_FILES;
--==>>
/*
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF	4	USERS	104857600	12800	AVAILABLE	4	YES	11811160064	1441792	1280	103809024	12672	ONLINE
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF	2	SYSAUX	692060160	84480	AVAILABLE	2	YES	34359721984	4194302	1280	691011584	84352	ONLINE
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF	3	UNDOTBS1	398458880	48640	AVAILABLE	3	YES	524288000	64000	640	397410304	48512	ONLINE
C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF	1	SYSTEM	377487360	46080	AVAILABLE	1	YES	629145600	76800	1280	376438784	45952	SYSTEM
C:\TESTDATA\TBS_EDUA01.DBF	5	TBS_EDUA	4194304	512	AVAILABLE	5	NO	0	0	0	3145728	384	ONLINE
*/


-- 오라클은 계정이 만들어졌다고 해서 바로 접속할 수 있는 것이 아니다.
-- 권한을 모두 설정해 주어야만 일반 사용자의 역할을 받아낼 수 있다.

--○ 오라클 사용자 계정 생성
CREATE USER kjm IDENTIFIED BY java006$
DEFAULT TABLESPACE TBS_EDUA;
--==>> User KJM이(가) 생성되었습니다.
--> kjm 이라는 사용자 계정을 생성하겠다. (만들겠다. 만들어줘.)
--  이 사용자 계정의 패스워드는 java006$ 로 하겠다.
--  이 계정을 통해 생성하는 오라클 세그먼트는
--  기본적으로 TBS_EDUA 라는 테이블스페이스에 생성할 수 있도록 설정하겠다.

--※ 생성된 오라클 사용자 계정(각자 본인의 이름 이니셜 계정)을 통해 접속 시도
--   → 접속 불가(실패)
--      『CREATE SESSION』 권한이 없기 때문에 접속 불가.

--○ 생성된 오라클 사용자 계정(각자 본인의 이름 이니셜 계정)에
--   오라클 서버 접속이 가능하도록 CREATE SESSION 권한 부여
GRANT CREATE SESSION TO KJM;
--==>> Grant을(를) 성공했습니다.

-- PRIVILEGE
--○ 각자 생성한 오라클 사용자 계정의 시스템 관련 권한 조회
SELECT *
FROM DBA_SYS_PRIVS;
--WHERE GRANTEE='KJM';
--==>>
/*
KJM	CREATE SESSION	NO
*/

---------------------------------------------------------------

--○ 각자 생성한 오라클 사용자 계정에
--   테이블 생성이 가능하도록 CREATE TABLE 권한 부여
GRANT CREATE TABLE TO KJM;
--==>> Grant을(를) 성공했습니다.


--QUOTA : 할당량
-- TBS_EDUA에서 할당량이 무제한.

--○ 각자 생성한 오라클 사용자 계정에
--   테이블 스페이스(TBS_EDUA)에서 사용할 수 있는 공간(할당량) 지정.
ALTER USER KJM 
QUOTA UNLIMITED ON TBS_EDUA;
--==>> User KJM이(가) 변경되었습니다.

 

---

 

20231017_02_hr.sql

 

SELECT USER
FROM DUAL;
--==>> HR

 

---

20231017_03_kjm.sql

SELECT USER
FROM DUAL;
--==>> KJM

-- CREATE SESSION 권한 - 접속만 가능한 권한.

-- 오른쪽이 데이터 타입, 왼쪽이 데이터명
-- 10BYTE 30BYTE
-- 한 건의 레코드(한 건의 데이터 입력 - 레코드)에 대한 크기지정
--○ 테이블 생성(TBL_ORAUSERTEST)
CREATE TABLE TBL_ORAUSERTEST
( NO        NUMBER(10)
, NAME      VARCHAR2(30)
);
--==>> 에러 발생
--     (ORA-01031: insufficient privileges) 권한 불충분 에러
--> 현재 각자의 이름 계정은 CREATE SESSION 권한만 갖고 있으며
--  테이블 생성 권한은 갖고 있지 않은 상태이다.
--  그러므로 관리자로부터 테이블을 생성할 수 있는 권한을 부여받아야 한다.


--○ SYS로 부터 테이블 생성 권한(CREATE TABLE)을 부여받은 후
--   다시 테이블 생성(TBL_TBL_ORAUSERTEST)

CREATE TABLE TBL_ORAUSERTEST
( NO        NUMBER(10)
, NAME    VARCHAR2(30)
);
--==>> 에러 발생
--     (ORA-01950: no privileges on tablespace 'TBS_EDUA')

--> 테이블 생성 권한까지 부여받은 상황이지만
--  각자의이름 계정의 기본 테이블 스페이스(DEFAULT TABLESPACE)는
--  TBS_EDUA 이며, 이 공간에 대한 할당량을 부여받지 못한 상태이다.
--  그러므로 이 테이블스페이스를 사용할 권한이 없다는 에러 메세지를
--  오라클이 안내해 주고 있는 상황

-- 서버용, 개인용 운영체제 차이...
-- 서비스 제공 : 할당량 존재 (여러 사용자들이 사용한다는 특징)
--               할당량이 존재하지 않으면 너무 많이 리소스를 사용한 사람 때문에
--               다른 사람이 피해를 받으므로...또는 한 사람밖에 사용하지 못하므로

-- 개인이 사용 : 할당량 Ⅹ   (내가 사용하는 것이므로 자유로움)

--○ SYS로부터 테이블스페이스(TBS_EDUA)에 대한 할당량을 부여받은 후
--   다시 테이블 생성(TBL_ORAUSERTEST)

CREATE TABLE TBL_ORAUSERTEST
( NO      NUMBER(10)
, NAME    VARCHAR2(30)
);
--==>> Table TBL_ORAUSERTEST이(가) 생성되었습니다.


--○ 생성된 테이블 조회
SELECT *
FROM TBL_ORAUSERTEST;
--> 테이블의 구조만 확인할 수 있는 상태 확인.
--  단, 조회된 데이터는 없음.


--○ 자신에게 부여된 할당량 조회
SELECT *
FROM USER_TS_QUOTAS;
--==>> TBS_EDUA	65536	-1	8	-1	NO
--※ 『-1』은 무제한을 의미

--○ 생성된 테이블(TBL_ORAUSERTEST)이
--   어떤 테이블스페이스에 저장되어 있는지 조회
SELECT TABLE_NAME, TABLESPACE_NAME
FROM USER_TABLES;
--==>> TBL_ORAUSERTEST	TBS_EDUA