오라클 문제정리

1)
pfile 과 spfile 의 특징은 ?

-(pfile)파라미터 파일은 오파클을 시작하는데에 필수적인 파라미터 들이 정의 되어 있으며, 기본적으로 관리를 하는 파일입니다.

-SPFILE을 사용하게 되면서 ALTER SYSTEM명령을 통해 database가 운영 중에도 파라미터를
   수정할 수  있고, 서버를 Restart하지 않아도 반영이 됩니다.
 - SPFILE은 바이너리파일 이고, 파일의 디폴트 저장위치는 PFILE과 동일 합니다.

2)

create tablespace MTDI
 data 'd:\db\MTDI01.dbf' size 100m;

dba가 데이터 파일의 사용량을 체크하는 과정에서 92%를 사용하여 파일의 크기를 증가시켜 주어야 한다.
파일의 크기를 증가하는 방식과 문장을 모두 서술 하시오.

ALTER DATABASE DATAFILE
           'C:\ORACLE\ORADATA\app_data02.dbf' RESIZE300M
 
   RESIZE명령으로 데이터 파일의 크기를 300M로 했습니다.
   기존 데이터 파일의 크기가 300M가 넘을 경우에는 300M로 줄어들게되고,
   300M가 되지 않았을 경우에는 300M로 늘어나게 됩니다.

생성한 데이터파일이 다 채워졌을 경우 자동으로 데이터 파일을 확장하는 방법
AUTOEXTEND 옵션을 사용 하시면 됩니다.
AUTOEXTEND 옵션 사용법
   - ON 아니면 OFF
   - CREATE DATABASE, CREATE TABLESPACE, ALTER TABLESPACE에서
      사용 할 수 있습니다.

AUTOEXTEND 사용 예제
SVRMGR> ALTER TABLESPACE storm
                ADD DATAFILE 'C:ORACLEORADATAapp_data02.dbf' SIZE 200M
                AUTOEXTEND ON NEXT 10M  MAXSIZE 500M;

storm 테이블스페이스에 200M 데이터 파일을 추가 했는데요, 
이 데이터 파일의 크기인 200M를 전부 사용하게 되면 500M이 될 때까지 자동으로  10M 증가합니다.

AUTOEXTEND 의 OFF
 SVRMGR>  ALTER DATABASE DATAFILE ‘C:ORACLEORADATAapp_data02.dbf'
                 AUTOEXTEND OFF;
App_data02.dbf파일의 자동확장을 중지 시킵니다. 

3)
system 으로 접속하여 user 'JUNG' 에게 create user system privilege 를 with admin option 으로 부여 하고 확인하시오

GRANT CREATE USER, ALTER USER, DROP USER TO JUNG
        WITH  ADMIN  OPTION.

-확인 select * from user_tab_privs_made;

 

4)
오라클의 논리적 구조는
) 데이터베이스

오라클 데이터베이스는 크게 논리적 구조와 물리적 구조로 나누어 구성되어 있습니다.
일반적으로 사용자들이 말하는 데이터베이스(Database)는 논리적 개념에서 사용되는 용어이며, 데이터(Data)의 집합이란 의미를 가지고 있습니다.
하지만, 오라클 서버에서 데이터베이스는 하나의 연속적인 공간을 일컫는 말은 아니며, 위 그림을 보시는 것처럼, 여러 개의 테이블스페이스(Tablespace)라는 논리적 구조가 모여서 하나의 데이터베이스를 구성하게 됩니다.
 
 2) 테이블스페이스와 데이터 파일

하나의 데이터베이스를 구성하는 여러 개의 논리적 구조들을 테이블스페이스(Tablespace)라고 합니다. 일반적으로, 테이블스페이스는 유니버셜 인스톨러(Universal Installer)에 의해 기본적으로 3개(SYSTEM , UNDOTBS, TEMP 테이블스페이스)가 생성되며 또한, 사용자에 의해 추가적으로 여러 개의 테이블스페이스가 생성될 수도 있습니다.

(1) SYSTEM 테이블스페이스
데이터베이스는 기본적으로 자료사전(Data Dictionary) 테이블을 제공합니다. 데이터베이스 내에 생성되어 있는 모든 객체들에 대한 정보 뿐만 아니라 데이터베이스의 현재 상태를 보여주며 서버에 의해 생성되며 관리됩니다. 이와 같은, 자료사전 테이블들이 저장되는 논리적 구조를 SYSTEM 테이블스페이스 라고 합니다.

SQL> CONNECT system/manager
SQL> SELECT * FROM DBA_USERS;
SQL> SELECT * FROM DBA_DATA_FILES;
결론적으로 이 공간에는 데이터베이스의 모든 상태정보가 저장되어 있는 공간입니다.

(2) UNDOTBS 테이블스페이스
사용자들이 데이터베이스에 접속한 다음 DML문(Update, Delete, Insert)을 실행한 후 만약, 트랜잭션을 취소해야 한다면 ROLLBACK문을 수행하게 됩니다.

SQL> CONNECT scott/tiger 
SQL> SELECT empno, ename, sal
FROM emp WHERE empno = 7934; 
 
SQL> UPDATE emp
SET sal = sal * 1.1 WHERE empno = 7934; 
SQL> SELECT empno, ename, sal
FROM emp WHERE empno = 7934; 
 
SQL> ROLLBACK; 
SQL> SELECT empno, ename, sal
FROM emp WHERE empno = 7934; 

UPDATE문을 실행한 후 SELECT문을 수행하면 해당 사원의 SAL 컬럼 값이 변경되어 있는 것을 확인할 수 있습니다. 하지만, ROLLBACK문을 수행한 후 다시 SELECT문을 실행하면 UPDATE문을 실행하기 이전 상태로 돌아가 있는 것을 확인할 수 있을 것 입니다.
그렇다면, ROLLBACK문을 실행하기 이전에는 변경 후의 값이, 실행하고 난 후에는 변경 전의 값을 확인할 수 있다는 것은 사용자는 알 수 없지만 어딘가에 변경 전의 값이 잠시 저장되어 있다는 것을 미루어 짐작할 수 있을 것 입니다.
바로, 이 공간이 UNDOTBS 테이블스페이스 입니다. 결론적으로 이 공간에는 사용자의 ROLLBACK 데이터가 ROLLBACK 문장이 수행될 때까지 잠시 저장되어 있는 임시 공간입니다.

 
 
 
 
 (3) TEMPORARY 테이블스페이스
UNDOTBS 테이블스페이스와 함께 사용자들이 자주 사용하는 것이 TEMP 테이블스페이스 입니다.

SQL> CONNECT scott/tiger
 
SQL> SELECT empno, ename, sal
FROM emp
ORDER BY deptno; 

대부분의 사용자들이 데이터베이스에 접속하여 실행하는 문장들은 내부적으로 분류작업(Sorting)이 발생하게 됩니다. (ORDER BY, GROUP BY, DISTINCT, UNION CREATE INDEX~ 등)
왜냐하면, 기본적인 테이블 구조들은 데이터를 입력했을 때 입력하는 순으로 저장되기 때문에 테이블의 정보를 SELECT 해 보면 그 결과를 쉽게 참조할 수 없는 단점을 가지고 있기 때문입니다. 결국, 그 데이터를 분류작업(Sorting) 하기 위해서는 별도의 임시공간이 필요하게 되는데, 바로 이 공간이 TEMP 테이블스페이스 입니다.
결론적으로 이 공간에는 사용자의 SQL문이 분류작업을 요구하는 경우 잠시 사용하는 임시 공간입니다.

(4) 데이터 파일
지금까지 소개 드린 SYSTEM, UNDOTBS, TEMP 테이블스페이스는 다음과 같이 CREATE DATABASE ~ ; 문법에 의해 오라클 데이터베이스가 설치될 때 생성됩니다.

<FONT size=2>CREATE DATABASE  ora90
LOGFILE  GROUP 1 ('c:\oracle\oradata\ora92\redo01.log') size 10m,
           GROUP 2 ('c:\oracle\oradata\ora92\redo02.log') size 10m)
DATAFILE 'c:\oracle\oradata\ora92\system01.dbf' size 100m
UNDO TABLESPACE undo
DATAFILE 'c:\oracle\oradata\ora92\undo01.dbf' size 50m
DEFAULT TEMPORARY TABLESPACE temp
       TEMPFILE 'c:\oracle\oradata\ora92\temp01.dbf' size 30m
       EXTENT MANAGEMENT LOCAL UNIFORM size 1m
CHARACTER SET ko16ksc5601
NATIONAL CHARACTER SET al16utf16
SET TIME_ZONE = 'Korea/Seoul';</FONT>
이 문법에서 SYSTEM01.DBF 데이터 파일은 SYSTEM 테이블스페이스에 대한 물리적 구조를 표현한 것이며, UNDO01.DBF 는 UNDOTBS 테이블스페이스, TEMP01.DBF는 TEMPORARY 테이블스페이스에 대한 물리적 구조를 표현한 것 입니다.

 
 3) 세그멘트

데이터베이스 내에 생성되는 모든 객체( 테이블, 인덱스, 뷰, 시퀀스, 시노늄 등)들을 세그멘트(Segment) 라고 합니다. 다음 문법을 보시는 것처럼, 하나의 세그멘트는 하나의 테이블스페이스에 저장되는 하나의 구성요소 입니다.

SQL> CONNECT system/manager
SQL> CREATE TABLESPACE insa
DATAFILE 'C:\ORACLE\ORADATA\ORA92\test01.dbf' size 10M;
 
SQL> CONNECT scott/tiger 
SQL> CREATE TABLE test
(a NUMBER(2), b CHAR(10))
TABLESPACE insa; 
← TEST 테이블(세그멘트)는 INSA 테이블스페이스에 생성됩니다.

 
 4) 익스텐트

하나의 테이블스페이스가 여러 개의 세그멘트로 구성되어 있는 것처럼, 하나의 세그멘트도 여러 개의 익스텐트(Extent)가 모여서 구성됩니다. 하나의 테이블을 생성하면 처음부터 아주 큰 하나의 저장공간이 할당되는 것이 아니라, 처음에는 익스텐트라는 공간이 할당되고, 이 공간이 모두 사용되면 다시 익스텐트를 할당 받아 연속적으로 데이터를 저장하게 되는 것 입니다.

SQL> CONNECT scott/tiger
SQL> CREATE TABLE test1
  CREATE TABLE test1
  a NUMBER(2), b VARCHAR2(10))
  TABLESPACE insa
  STORAGE ( INITIAL 10K     ← 테이블의 최초 크기를 결정합니다.
    NEXT 10K     ← 최초 크기가 모두 사용된 후
              다음 크기를 결정합니다.
    MINEXTENT 1
    MAXEXTENT UNLIMITED
    PCTINCREASE 50);

 

결론적으로, 하나의 테이블은 하나의 연속적인 공간으로 생성되는 것이 아니라 익스텐트라는 작은 구성요소로 생성되어 있는 것을 확인할 수 있습니다.
 
 5) 블록

오라클 데이터베이스의 가장 작은 저장구조를 블록(Block)이라고 합니다. 앞에서 소개 드린 익스텐트 구조는 사실은 하나의 연속적인 공간이 아니라 블록구조가 여러 개 모여 만들어지는 하나의 공간입니다. 결국, 여러 개의 블록이 모여 하나의 익스텐트를 만들게 됩니다.

그렇다면, 하나의 익스텐트를 왜 여러 개의 작은 구성요소로 만들었을까요 ??

만약, 하나의 익스텐트를 하나의 연속적인 공간으로 만들었다면 테이블의 데이터를 읽을 때 한번에 많은 데이터를 읽어야 하기 때문에 시간이 많이 소요될 뿐만 아니라 한번에 많은 데이터를 저장해야 하기 때문에 효과적인 저장과 관리가 용이하지 않기 때문입니다.
그래서, 연속적인 데이터를 여러 개의 블록구조로 쪼개서 관리함으로써 효율성을 높이게 되는 것 입니다.

오라클 데이터베이스의 논리적, 물리적 저장구조에 대한 보다 자세한 설명은 "오라클 데이터베이스의 관리"에 관련된 참고서적을 참고해 주십시오.

 

6)
아카이브 로그 모드의 특징과 설정 방법

특징
두 번째 방법은 아카이브 모드(ARCHIVE MODE)입니다. 이 모드에는 완전복구(COMPLETE RECOVERY)와 불완전 복구(INCOMPLETE RECOVERY) 방법이 있습니다. 모든 복구 데이터를 가지고 있으며 데이터베이스에 문제가 발생했던 시점까지 복구할 수 있는데 이러한 방법을 완전복구(Complete Recovery)라고 하며, 반대로, 복구할 수 있는 데이터가 백업되어 있지 않다면 문제가 생겼던 시점까지 복구할 수 없는데 이러한 방법을 불완전 복구(In-Complete Recovery)라고 합니다. 사용자들은 이러한 다양한 백업과 복구방법을 통해 데이터베이스의 가용성을 높일 수 있습니다.

설정방식

-->1. 데이터베이스 모드 변경
shutdown immediate;
startup mount
alter database archivelog;
archive log list

--오픈 단계
alter database open;

-->2.아카이브 프로세스  활성화
show parameter log_archive_start
1)아카이브 로그파일 이름 결정
-show parameter log_archive_format

2)위치결정(폴더 위치 생성)
 
alter system
 set log_archive_dest_1='location=D:\IEDU\archive1'
  scope=spfile;
alter system
 set log_archive_dest_2='location=D:\IEDU\archive2'
  scope=spfile;
alter system
 set log_archive_dest_3='location=D:\IEDU\archive3'
  scope=spfile;
3)아카이브 프로세스의 활성화
-alter system set log_archive_start=true scope=spfile;
-shutdown immediate
-startup

6)
컨트롤 파일 백업 과 백업도니 파일을 가지고 컨트롤 파일 생성

alter database backup controlfile cf_user_01 reuse;

 

7)

데이터베이이싀 내의 temporary tablespace 들의 확인학고 그 물리적 파일들을 조회

select file_name,tablespace_name,status from dba_data_files;

11)
데이터 베이스 생성후 데이터 딕션어리를 생성하기 위한 문장

13)다음과 같은 테이블을 생성하고 자 한다. 기본키와 유니크 제약의 경우 자동 인덱스가 생성되는데 테이블 스페이스를 indx 에 테이블은 text테이블 스페이스에 생성하고자 한다.
create table haksa(
 학번 varchar2(10) primary key
  using index(
   create unique index i_haksa_학번 on haksa(학번)
    tablespace indx
      ), 
 이름 varchar2(20)not null,
 과정명 varchar2(2), 
 주민번호 char(14) unique
  using index(
   create unique index i_haksa_주민번호 on haksa(주민번호)
   tablespace indx
     ),
 tel varchar2(20)

)
tablespace test;

12 프로파일 생성
create profile springPro limit
connect_time 5
idle_time 20
session_per_user 5
password_life_time 30
password_grace_time 3

alter user spring
profile springPro;
password_grace_time 3