SQLD 0 - oracle DB 설치

Oracle DB 설치

https://drive.google.com/file/d/171wMo0d1us8iRCbfcATjX8P529KXWdUL/view

OracleXE184_Win64.zip

 

lsnrctl status <- 접속 정보

 

sqlplus "/AS SYSDBA"

 

 

SET LINESIZE 200;
SET TIMING ON;
SET SERVEROUTPUT ON;

 

 

oracle 경로는 `C:\app\{사용자명}\product\18.0.0`이다

 

https://dbeaver.io/download/

Download | DBeaver Community

DBMS 도 함께 설치해주자

 

 

 

 

 

 

Oracle 실습환경

/* 전통적인 oracle script 를 이 세션에서만큼은 사용가능하도록 하겠다.*/
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;

DROP USER SQLD CASCADE;
DROP TABLESPACE SQLD_DATA INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE SQLD_TEMP INCLUDING CONTENTS AND DATAFILES;

/* 사용자 만들기 */
CREATE USER SQLD IDENTIFIED BY 1234;
ALTER USER SQLD ACCOUNT UNLOCK;
GRANT RESOURCE, DBA, CONNECT TO SQLD;

CREATE TABLESPACE SQLD_DATA
datafile 'C:\app\ourcl\product\18.0.0\oradata\XE\SQLD_DATA.dbf' SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TEMPORARY TABLESPACE SQLD_TMP
TEMPFILE 'C:\app\ourcl\product\18.0.0\oradata\XE\SQLD_TMP.dbf' SIZE 100M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

/* 사용자가 사용할 db를 설정 */
ALTER USER SQLD DEFAULT TABLESPACE SQLD_DATA;
ALTER USER SQLD TEMPORARY TABLESPACE SQLD_TMP;

먼저 사용자와 테이블을 생성해주자

 

 

 

 

그리고 데이터 삽입을 해주는 스크립트를 이용해주자

https://drive.google.com/file/d/14gn4eMa8sTLB4fLH9fhA3hxXa_l3rG6h/view

SQLD_사용자_실습환경구축_스크립트.sql

cd C:\SQLD

 

DBeaver에 들어가서 SQLD 접속 세션을 만들어준다.

 

그리고 sql 로 다음을 테스트해준다.

SELECT SUM(A.DATA_CNT) AS DATA_CNT_SUM
     , COUNT(A.TABLE_NAME) AS TABLE_CNT_SUM
  FROM
     (
       SELECT COUNT(*) AS DATA_CNT, 'TB_CERTI'        AS TABLE_NAME FROM TB_CERTI           UNION ALL        
       SELECT COUNT(*) AS DATA_CNT, 'TB_DEPT'         AS TABLE_NAME FROM TB_DEPT            UNION ALL
       SELECT COUNT(*) AS DATA_CNT, 'TB_EMP'          AS TABLE_NAME FROM TB_EMP             UNION ALL      
       SELECT COUNT(*) AS DATA_CNT, 'TB_EMP_CERTI'    AS TABLE_NAME FROM TB_EMP_CERTI       UNION ALL
       SELECT COUNT(*) AS DATA_CNT, 'TB_SAL'          AS TABLE_NAME FROM TB_SAL             UNION ALL
       SELECT COUNT(*) AS DATA_CNT, 'TB_SAL_HIS'      AS TABLE_NAME FROM TB_SAL_HIS         UNION ALL
       SELECT COUNT(*) AS DATA_CNT, 'TB_SAL_HIS_DTL'  AS TABLE_NAME FROM TB_SAL_HIS_DTL     
     ) A 
;