오라클 데이터베이스에는 하나 이상의 논리적인 저장 단위인 데이블 스페이스로 구성 되어 있고 각 테이블 스페이스 역시 하나 이상의 물리적인 데이터 파일(DataFile)들로 이루어져 있다. 여기에서 물리적 이라는 의미는 실제 오라클 데이터파일의 이름에 따라 하드디스크에 파일들이 존재한다는 의미이며 논리적 이라는 의미는 이론적인 구성일 뿐 명시적으로 확인 할 수 없다는 의미이다.
DataFile들을 오라클 서버에서는 어떻게 확인 할까? 다음을 따라해 보자.
결과 예측이 애매한 DDL의 수행에 대해서는 고민 해 보아야 한다. 임의로 테이블 스페이스나 데이터 파일에 관해 CREATE, ALTER, DROP 명령을 수행 하면 데이터 베이스의 시작에 문제가 있을 수 있으니 실습이외의 내용은 충분히 학습 후 해 보길 바란다.
scott 계정으로 현재 접속해 있다면 아래와 같이 하여 DBA 권한을 가진 관리자로 접근을 하자. 이후의 실습에서는 뷰(View)라는 용어가 자주 나오게 될 것이다. 당황하지 말고 단순히 데이터를 볼수 있게 해주는 틀이라고 생각하자.
SQL> connect / as sysdba (또는 conn sys/change_on_install as sysdba)
연결되었습니다.
dba_data_files 라는 뷰를 통해 현재 오라클 데이터 베이스의 데이터 파일과 관련된 구조를 확인 할 수 있다. 아래를 보면 알겠지만 USERS01.DBF와 USERS02.DBF 두개의 DataFile이 모여 하나의 USERS 테이블 스페이스를 이루고 있다.
SQL>select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------
C:\ORACLE\ORADATA\WINK\SYSTEM01.DBF SYSTEM
C:\ORACLE\ORADATA\WINK\UNDOTBS01.DBF UNDOTBS1
C:\ORACLE\ORADATA\WINK\CWMLITE01.DBF CWMLITE
C:\ORACLE\ORADATA\WINK\DRSYS01.DBF DRSYS
C:\ORACLE\ORADATA\WINK\EXAMPLE01.DBF EXAMPLE
C:\ORACLE\ORADATA\WINK\INDX01.DBF INDX
C:\ORACLE\ORADATA\WINK\ODM01.DBF ODM
C:\ORACLE\ORADATA\WINK\TOOLS01.DBF TOOLS
C:\ORACLE\ORADATA\WINK\USERS01.DBF USERS
C:\ORACLE\ORADATA\WINK\XDB01.DBF XDB
C:\ORACLE\ORADATA\WINK\USERS02.DBF USERS
이번에는 현재 데이터베이스의 테이블 스페이스를 확인해 보기로 하자.
scott 계정으로 현재 접속해 있다면 아래와 같이 하여 DBA 권한을 가진 관리자로 접근을 하자.
SQL> connect / as sysdba
연결되었습니다.
dba_tablesapces 라는 뷰를 질의하면 Tablespace와 관련된 여러 정보를 확인 할 수 있다.
SQL> select TABLESPACE_NAME from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
TOOLS
USERS
XDB
11 개의 행이 선택되었습니다.
데이블 스페이스 작성 실습
SYSTEM 테이블 스페이스는 데이터베이스가 생성될 때 기본적으로 작성 되어지며 여기에는 오라클이 뷰의 형태로 제공 되어지는 Data Dictionary 와 관련된 Table들이 저장 되어져 있다.
여러분들이 Table이나 Index와 같은 Schema Obejct를 만들 때는 어느 테이블 스페이스에 만들 건지 지정을 하여야 한다. 물론 명시적으로 지정을 안한다면 기본적으로 어디에 만들건지가 이미 설정 되어 있어 그곳(DEFAULT TABLESPACE)에 만들어 지는 것이다. 지금까지 많은 예문을 하면서 테이블 들을 만들었는데 그럼 이 테이블들은 도대체 어디에 저장 되었다는 말인가? 이것에 대한 해답은 아래의 실습을 따라 해 가면서 이해 하도록 하자.
scott 계정으로 현재 접속해 있다면 아래와 같이 하여 DBA 권한을 가진 관리자로 접근을 하자.
SQL> connect / as sysdba
연결되었습니다.
TEST라는 테이블 스페이스를 만들면서 SIZE는 10M로 주었다. 테이블 스페이스는 하나 이상의 데이터 파일로 이루어 지므로 TEST 테이블 스페이스를 구성하는 데이터 파일의 위치를 기술 하였다. 결국 테이블 스페이스에 테이블이나 인덱스 같은 스키마 오브젝트들을 만드는데 TEST라는 테이블 스페이스에 이번 실습을 통해 Table을 만들어 볼 것이다. 예문을 수행하면 TEST라는 테이블 스페이스가 생성되며 TEST01.DBF로 구성이 되며 총 테이블 스페이스 사이즈는 10M 이다.
SQL> create tablespace test datafile 'C:\oracle\oradata\wink\test01.dbf' size 10
m ;
테이블 영역이 생성되었습니다.
아래 예문은 TEST 테이블 스페이스를 구성하는 데이터 파일을 확인 하는 것이다.
SQL> select file_name, tablespace_name from dba_data_files
2 where tablespace_name = 'TEST';
FILE_NAME TABLESPACE_NAME
----------------------------------------------------------
C:\ORACLE\ORADATA\WINK\TEST01.DBF TEST
이번에는 새로운 사용자 JCLEE를 만들고 기본 테이블 스페이스(DEFAULT TABLESPACE)를 TEST로 기술 하자. 이 말은 JCLEE라는 사용자로 접속을 하여 TABLE 을 만들 때 오느 테이블 스페이스에 만들건지 기술 하지 않으면 TEST 테이블 스페이스에 만들라는 의미이다. 그럼 이제까지 실습 하면서 만든 테이블들은 SCOTT 계정의 기본 테이블 스페이스에 만들어 졌음을 추측 할 수 있을 것이다. 잠시 문법을 설명하면 identified by 구는 비밀번호를 기술 하는 것이며 즉 ID는 jclee PASSWORD는 test2003 이 되는 것이다. Default tablespace 구문은 기본 테이블 스페이스를 지정 하는 것이며 temporary tablespace는 임시 테이블 스페이스를 지정 하는 것이다. 그럼 임시 테이블 스페이스란 무엇인가? 여러분들이 SELECT 문을 사용하다가 정렬을 위해 ORDER BY를 사용하여 대량의 데이터 건수를 가지는 테이블을 ORDER BY로 정렬한다면 TEMPORARY TABLESPACE가 사용되어 지는 것이다. 결국 ORDER BY와 같은 것은 정렬을 위해 별도의 테이블 스페이스를 사용 할 수 있으므로 수행 속도 면에서는 좋지 않으니 인덱스를 적절히 이용한다든지 해서 사용을 자제 해야 할 것이다. CREATE INDEX, SELECT .. ORDER BY, SELECT DISTINCT, SELECT ... GROUP BY, SELECT .. UNION, SELECT ... INTERSECT, SELECT ... MINU 와 같은 SQL문을 사용시에는 TEMPORARY TABLESPACE가 사용되어 질 수 있으므로 사용시 주의를 해야 한다.
SQL> create user jclee identified by test2003 default tablespace test temporary
tablespace temp;
사용자가 생성되었습니다.
사용자를 생성 하였으니 Oracle Net을 통해 데이터베이스에 접속할 수 있는 권한과 자원을 조작할 수 있는 권한을 이미 만들어져 있는 롤(Role)을 통해 부여 하기로 하자.
SQL> grant connect, resource to jclee;
권한이 부여되었습니다.
아래는 JCLEE라는 사용자에 부여된 권한을 확인 하는 예 이다. SCOTT 사용자와 권한이 같음을 알 수 있다.
SQL> select * from DBA_ROLE_PRIVS
2 where grantee in ('SCOTT','JCLEE');
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
JCLEE CONNECT NO YES
JCLEE RESOURCE NO YES
SCOTT CONNECT NO YES
SCOTT RESOURCE NO YES
이제 테이블을 생성해 보기로 하자. 첫번째 예문은 테이블 스페이스를 기술하지 않았으므로 JCLEE 사용자의 기본 테이블 스페이스인 TEST 테이블 스페이스에 저장 될 것이고 두번 째 예문은 명시적으로 테이블 스페이스를 기술 하였으므로 USERS 라는 테이블 스페이스에 저장이 될 것이다. 먼저 jclee 계정으로 접속을 하자.
SQL> connect jclee/test2003;
연결되었습니다.
SQL> create table mytest (
2 name varchar2(10) not null,
3 age number(5)
4 );
테이블이 생성되었습니다.
SQL> create table mytest2 (
2 name varchar2(10) not null,
3 age number(5)
4 ) tablespace users;
테이블이 생성되었습니다.
이번에는 JCLEE 사용자와 SCOTT 사용자의 기본 테이블 스페이스(DEFAULT TABLESPACE)와 임시 테이블 스페이스(TEMPORARY TABLESPACE)를 확인 해 보자. SCOTT 사용자의 기본 테이블 스페이스는 SYSTEM이고 JCLEE 사용자는 TESRT 이다. 이 부분에는 한 가지 알아야 할 사실이 있다. SYSTEM 테이블 스페이스는 데이터베이스의 데이터 딕셔너리(Data Dictionary)를 위한 공간이다. 이곳에 사용자 테이블을 만들면 좋을 것이 하나도 없다. 그러므로 여러분들이 연습용 테이블등을 만들려면 별도의 계정에 별도의 테이블 스페이스 또는 이미 제공 되어있는 USERS 테이블 스페이스 등에 만드는 것이 좋다.
SQL>connect / as sysdba
SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
2 from dba_users
3 where username in ('JCLEE','SCOTT');
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
---------------------------- ------------------------------
SCOTT SYSTEM TEMP
JCLEE TEST TEMP
다음 예문은 이미 만들어져 있는 테이블이 어느 테이블 스페이스에 있는지 확인하는 것이다.
SQL>connect / as sysdba
SQL> select table_name, tablespace_name
2 from dba_tables
3 where table_name in ('MYTEST','MYTEST2');
TABLE_NAME TABLESPACE_NAME
------------------------------ --------------------
MYTEST SYSTEM
MYTEST2 USERS
아래의 경우는 저장 매개변수(Storage Parameter)를 이용한 테이블 스페이스의 생성이다. 오라클 9i의 경우 extents에 대해 autoallocation 이 기본이니 자주 사용되지는 않지만 참고로 하자.(initial은 최초 테이블 스페이스 생성시 할당 되는 extent의 사이즈 이며 초기엔 비어 있을 것이다. 만약 꽉 차게 되면 next 사이즈 만큼 확장하게 되며 extent의 수는 최소2개, 최대 50개가 가능하다는 의미이다.)
SQL> CREATE TABLESPACE data05
2 DATAFILE 'C:\oracle\oradata\wink\data05.dbf' SIZE 10M
3 DEFAULT STORAGE (
4 INITIAL 50K
5 NEXT 50K
6 MINEXTENTS 2
7 MAXEXTENTS 50
8 PCTINCREASE 0
9* )
SQL> /
테이블 영역이 생성되었습니다.
테이블 스페이스의 변경
테이블 스페이스에 대한 변경은 ALTER TABLESPACE를 사용하면 된다.
만약 여러분이 온라인 테이블 스페이스 백업을 한다면 백업 시작 전후 다음과 같이 해야 한다.
SQL> alter tablespace test begin backup;
SQL> alter tablespace test end backup;
또한 테이블 스페이스를 온라인 또는 오프라인으로 만들수 있다. 만약 테이블 스페이스가 오프라인이 된다면 해당 테이블 스페이스에 스키마 객체를 만들 수 없다. 테이블 스페이스를 OFFLINE 시키는 옵션은 3가지 정도 있는데 첫째 아래 예문처럼 OFFLINE NORMAL이 있다. 이는 TABLESPACE에 있는 데이터 파일들이 오류가 없는 상태라면 OFFLINE이 가능 한 모드 이다. 오류가 있으면 테이블 스페이스의 OFFLINE은 불가능 하다. 둘째 TEMPORARY OFFLINE 이 있는데 이 경우엔 테이블 스페이스의 데이터 파일중 오류가 있다고 하더라도 임시적으로 OFFLINE 이 가능한 모드 이다. 마지막으로 세번째 IMMEDIATE OFFLINE 이 있는데 이는 테이블 스페이스의 어떠한 데이터 파일에 대한 CHECK POINT 없이 즉시 OFFLINE 이 가능한 모드 이다.
SQL> connect / as sysdba
SQL> SQL> alter tablespace test offline normal;
테이블 영역이 변경되었습니다.;
테이블 영역이 변경되었습니다.
SQL> connect jclee/test2003
연결되었습니다.
SQL> create table test (
2 column1 varchar2(10),
3 column2 number(5,0)
4 );
create table test (
*
1행에 오류:
ORA-01542: 'TEST' 테이블스페이스가 오프라인입니다. 영역을 할당할 수 없습니다
SQL> connect / as sysdba;
연결되었습니다.
SQL> alter tablespace test online;
테이블 영역이 변경되었습니다.
SQL> create table test (
2 column1 varchar2(10),
3 column2 number(5,0)
4 );
테이블이 생성되었습니다.
테이블 스페이스의 삭제
데이블 스페이스의 삭제는 DROP TABLESPACE 명령을 이용하면 된다.
SQL> connect / as sysdba
연결되었습니다.
SQL> drop tablespace test;
테이블 영역이 삭제되었습니다.
SQL> connect jclee/test2003;
연결되었습니다.
아래는 JCLEE 사용자의 기본 테이블 스페이스가 삭제 됨으로서 JCLEE 사용자가 객체를 만들 때 오류가 발생하는 예문이다. 만약 테이블 스페이스가 영구히 삭제 되었다면 사용자를 변경하여 작업에 무리가 없도록 해야 한다. 아래는 JCLEE 사용자의 기본 테이블 스페이스를 기본적으로 생성되는 USERS라는 테이블 스페이스로 변경 하는 예문 이다.
SQL> create table test (
2 col1 varchar2(10),
3 col2 number(5,0)
4 );
create table test (
*
1행에 오류:
ORA-00959: 테이블 영역 'TEST' 가 존재하지 않습니다
SQL> connect / as sysdba;
연결되었습니다.
SQL> alter user jclee default tablespace users;
사용자가 변경되었습니다.
Database의 크기 늘이기
데이터베이스의 크기를 늘인 다는 것은 결국 데이터 파일 사이즈 합을 늘이는 것과 같다. 아래의 실습을 따라 하면서 데이블 스페이스 및 데이터 파일 다루는 것을 익히도록 하자.
SCOTT 계정으로 접속해 있다면 데이터베이스 관리자의 권한으로 접속을 하자. 아래의 실습에 나오는 경로는 여러분들의 환경에 맞게 적절히 수정 하기 바란다.
SQL> connect / as sysdba
이번 실습은 오라클을 설치하면 기본적으로 생성되는 USERS라는 테이블 스페이스를 가지고 예문들을 생성 해 보겠다.
데이터베이스의 크기를 늘이기 위한 첫번째 방법은 기존의 테이블 스페이스의 크기를 늘이는 것이다. 이경우는 USERS 테이블 스페이스를 계속 사용하다 보니 정해진 한계 사이즈에 도달 한 경우에 주로 사용하는 방법이다. 즉 기존 테이블 스페이스에 새로운 DataFiles을
Add 하는 방법이다.
SQL> alter tablespace users add datafile 'C:\oracle\oradata\wink\users03.dbf' size 10m;
테이블 영역이 변경되었습니다.
두번째 방법은 이전에 실습해 보았던 방법으로 새로운 테이블 스페이스를 추가 하는 것이다. TEST라는 테이블 스페이스를 추가하자. 혹시 이미 있다는 오류가 뜨면 DROP TABLESPACE TEST 명령으로 삭제 후 다시 실행 하자. 또한 아래처럼 파일이 존재한다는 오류가 뜨는 이유는 테이블 스페이스는 삭제 했으나 해당 데이터 파일을 지우지 않아 같은 곳에 같은 이름의 파일을 만들 수 없으므로 나는 오류이니 해당 디렉토리에 가서 TEST01.DBF 파일을 지운 후 CREATE TABLESPACE 명령을 수행 하자.
SQL> create tablespace test datafile 'C:\oracle\oradata\wink\test01.dbf' size 10
m ;
create tablespace test datafile 'C:\oracle\oradata\wink\test01.dbf' size 10m
*
1행에 오류:
ORA-01119: 'C:\oracle\oradata\wink\test01.dbf' 데이터베이스 파일 작성시 오류가
생겼습니다
ORA-27038: skgfrcre: 파일이 존재합니다
OSD-04010: <create> 옵션이 지정되었으나 파일이 존재함
TEST01.DBF를 지운 후……
SQL> create tablespace test datafile 'C:\oracle\oradata\wink\test01.dbf' size 10
m ;
테이블 영역이 생성되었습니다.
세번째 방법은 ALTER DATABASE 명령을 이용하여 기존에 존재하는 데이터 파일의 사이즈를 늘이는 방법이다. 아래 예문은 기존 10M인 TEST01.DBF를 20M로 늘임으로서 TEST 테이블 스페이스의 크기를 늘이는 예문 이다.
SQL> alter database datafile 'C:\oracle\oradata\wink\test01.dbf' resize 20m;
데이타베이스가 변경되었습니다.
DataFile들을 오라클 서버에서는 어떻게 확인 할까? 다음을 따라해 보자.
결과 예측이 애매한 DDL의 수행에 대해서는 고민 해 보아야 한다. 임의로 테이블 스페이스나 데이터 파일에 관해 CREATE, ALTER, DROP 명령을 수행 하면 데이터 베이스의 시작에 문제가 있을 수 있으니 실습이외의 내용은 충분히 학습 후 해 보길 바란다.
scott 계정으로 현재 접속해 있다면 아래와 같이 하여 DBA 권한을 가진 관리자로 접근을 하자. 이후의 실습에서는 뷰(View)라는 용어가 자주 나오게 될 것이다. 당황하지 말고 단순히 데이터를 볼수 있게 해주는 틀이라고 생각하자.
SQL> connect / as sysdba (또는 conn sys/change_on_install as sysdba)
연결되었습니다.
dba_data_files 라는 뷰를 통해 현재 오라클 데이터 베이스의 데이터 파일과 관련된 구조를 확인 할 수 있다. 아래를 보면 알겠지만 USERS01.DBF와 USERS02.DBF 두개의 DataFile이 모여 하나의 USERS 테이블 스페이스를 이루고 있다.
SQL>select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------
C:\ORACLE\ORADATA\WINK\SYSTEM01.DBF SYSTEM
C:\ORACLE\ORADATA\WINK\UNDOTBS01.DBF UNDOTBS1
C:\ORACLE\ORADATA\WINK\CWMLITE01.DBF CWMLITE
C:\ORACLE\ORADATA\WINK\DRSYS01.DBF DRSYS
C:\ORACLE\ORADATA\WINK\EXAMPLE01.DBF EXAMPLE
C:\ORACLE\ORADATA\WINK\INDX01.DBF INDX
C:\ORACLE\ORADATA\WINK\ODM01.DBF ODM
C:\ORACLE\ORADATA\WINK\TOOLS01.DBF TOOLS
C:\ORACLE\ORADATA\WINK\USERS01.DBF USERS
C:\ORACLE\ORADATA\WINK\XDB01.DBF XDB
C:\ORACLE\ORADATA\WINK\USERS02.DBF USERS
이번에는 현재 데이터베이스의 테이블 스페이스를 확인해 보기로 하자.
scott 계정으로 현재 접속해 있다면 아래와 같이 하여 DBA 권한을 가진 관리자로 접근을 하자.
SQL> connect / as sysdba
연결되었습니다.
dba_tablesapces 라는 뷰를 질의하면 Tablespace와 관련된 여러 정보를 확인 할 수 있다.
SQL> select TABLESPACE_NAME from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
TOOLS
USERS
XDB
11 개의 행이 선택되었습니다.
데이블 스페이스 작성 실습
SYSTEM 테이블 스페이스는 데이터베이스가 생성될 때 기본적으로 작성 되어지며 여기에는 오라클이 뷰의 형태로 제공 되어지는 Data Dictionary 와 관련된 Table들이 저장 되어져 있다.
여러분들이 Table이나 Index와 같은 Schema Obejct를 만들 때는 어느 테이블 스페이스에 만들 건지 지정을 하여야 한다. 물론 명시적으로 지정을 안한다면 기본적으로 어디에 만들건지가 이미 설정 되어 있어 그곳(DEFAULT TABLESPACE)에 만들어 지는 것이다. 지금까지 많은 예문을 하면서 테이블 들을 만들었는데 그럼 이 테이블들은 도대체 어디에 저장 되었다는 말인가? 이것에 대한 해답은 아래의 실습을 따라 해 가면서 이해 하도록 하자.
scott 계정으로 현재 접속해 있다면 아래와 같이 하여 DBA 권한을 가진 관리자로 접근을 하자.
SQL> connect / as sysdba
연결되었습니다.
TEST라는 테이블 스페이스를 만들면서 SIZE는 10M로 주었다. 테이블 스페이스는 하나 이상의 데이터 파일로 이루어 지므로 TEST 테이블 스페이스를 구성하는 데이터 파일의 위치를 기술 하였다. 결국 테이블 스페이스에 테이블이나 인덱스 같은 스키마 오브젝트들을 만드는데 TEST라는 테이블 스페이스에 이번 실습을 통해 Table을 만들어 볼 것이다. 예문을 수행하면 TEST라는 테이블 스페이스가 생성되며 TEST01.DBF로 구성이 되며 총 테이블 스페이스 사이즈는 10M 이다.
SQL> create tablespace test datafile 'C:\oracle\oradata\wink\test01.dbf' size 10
m ;
테이블 영역이 생성되었습니다.
아래 예문은 TEST 테이블 스페이스를 구성하는 데이터 파일을 확인 하는 것이다.
SQL> select file_name, tablespace_name from dba_data_files
2 where tablespace_name = 'TEST';
FILE_NAME TABLESPACE_NAME
----------------------------------------------------------
C:\ORACLE\ORADATA\WINK\TEST01.DBF TEST
이번에는 새로운 사용자 JCLEE를 만들고 기본 테이블 스페이스(DEFAULT TABLESPACE)를 TEST로 기술 하자. 이 말은 JCLEE라는 사용자로 접속을 하여 TABLE 을 만들 때 오느 테이블 스페이스에 만들건지 기술 하지 않으면 TEST 테이블 스페이스에 만들라는 의미이다. 그럼 이제까지 실습 하면서 만든 테이블들은 SCOTT 계정의 기본 테이블 스페이스에 만들어 졌음을 추측 할 수 있을 것이다. 잠시 문법을 설명하면 identified by 구는 비밀번호를 기술 하는 것이며 즉 ID는 jclee PASSWORD는 test2003 이 되는 것이다. Default tablespace 구문은 기본 테이블 스페이스를 지정 하는 것이며 temporary tablespace는 임시 테이블 스페이스를 지정 하는 것이다. 그럼 임시 테이블 스페이스란 무엇인가? 여러분들이 SELECT 문을 사용하다가 정렬을 위해 ORDER BY를 사용하여 대량의 데이터 건수를 가지는 테이블을 ORDER BY로 정렬한다면 TEMPORARY TABLESPACE가 사용되어 지는 것이다. 결국 ORDER BY와 같은 것은 정렬을 위해 별도의 테이블 스페이스를 사용 할 수 있으므로 수행 속도 면에서는 좋지 않으니 인덱스를 적절히 이용한다든지 해서 사용을 자제 해야 할 것이다. CREATE INDEX, SELECT .. ORDER BY, SELECT DISTINCT, SELECT ... GROUP BY, SELECT .. UNION, SELECT ... INTERSECT, SELECT ... MINU 와 같은 SQL문을 사용시에는 TEMPORARY TABLESPACE가 사용되어 질 수 있으므로 사용시 주의를 해야 한다.
SQL> create user jclee identified by test2003 default tablespace test temporary
tablespace temp;
사용자가 생성되었습니다.
사용자를 생성 하였으니 Oracle Net을 통해 데이터베이스에 접속할 수 있는 권한과 자원을 조작할 수 있는 권한을 이미 만들어져 있는 롤(Role)을 통해 부여 하기로 하자.
SQL> grant connect, resource to jclee;
권한이 부여되었습니다.
아래는 JCLEE라는 사용자에 부여된 권한을 확인 하는 예 이다. SCOTT 사용자와 권한이 같음을 알 수 있다.
SQL> select * from DBA_ROLE_PRIVS
2 where grantee in ('SCOTT','JCLEE');
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
JCLEE CONNECT NO YES
JCLEE RESOURCE NO YES
SCOTT CONNECT NO YES
SCOTT RESOURCE NO YES
이제 테이블을 생성해 보기로 하자. 첫번째 예문은 테이블 스페이스를 기술하지 않았으므로 JCLEE 사용자의 기본 테이블 스페이스인 TEST 테이블 스페이스에 저장 될 것이고 두번 째 예문은 명시적으로 테이블 스페이스를 기술 하였으므로 USERS 라는 테이블 스페이스에 저장이 될 것이다. 먼저 jclee 계정으로 접속을 하자.
SQL> connect jclee/test2003;
연결되었습니다.
SQL> create table mytest (
2 name varchar2(10) not null,
3 age number(5)
4 );
테이블이 생성되었습니다.
SQL> create table mytest2 (
2 name varchar2(10) not null,
3 age number(5)
4 ) tablespace users;
테이블이 생성되었습니다.
이번에는 JCLEE 사용자와 SCOTT 사용자의 기본 테이블 스페이스(DEFAULT TABLESPACE)와 임시 테이블 스페이스(TEMPORARY TABLESPACE)를 확인 해 보자. SCOTT 사용자의 기본 테이블 스페이스는 SYSTEM이고 JCLEE 사용자는 TESRT 이다. 이 부분에는 한 가지 알아야 할 사실이 있다. SYSTEM 테이블 스페이스는 데이터베이스의 데이터 딕셔너리(Data Dictionary)를 위한 공간이다. 이곳에 사용자 테이블을 만들면 좋을 것이 하나도 없다. 그러므로 여러분들이 연습용 테이블등을 만들려면 별도의 계정에 별도의 테이블 스페이스 또는 이미 제공 되어있는 USERS 테이블 스페이스 등에 만드는 것이 좋다.
SQL>connect / as sysdba
SQL> select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
2 from dba_users
3 where username in ('JCLEE','SCOTT');
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
---------------------------- ------------------------------
SCOTT SYSTEM TEMP
JCLEE TEST TEMP
다음 예문은 이미 만들어져 있는 테이블이 어느 테이블 스페이스에 있는지 확인하는 것이다.
SQL>connect / as sysdba
SQL> select table_name, tablespace_name
2 from dba_tables
3 where table_name in ('MYTEST','MYTEST2');
TABLE_NAME TABLESPACE_NAME
------------------------------ --------------------
MYTEST SYSTEM
MYTEST2 USERS
아래의 경우는 저장 매개변수(Storage Parameter)를 이용한 테이블 스페이스의 생성이다. 오라클 9i의 경우 extents에 대해 autoallocation 이 기본이니 자주 사용되지는 않지만 참고로 하자.(initial은 최초 테이블 스페이스 생성시 할당 되는 extent의 사이즈 이며 초기엔 비어 있을 것이다. 만약 꽉 차게 되면 next 사이즈 만큼 확장하게 되며 extent의 수는 최소2개, 최대 50개가 가능하다는 의미이다.)
SQL> CREATE TABLESPACE data05
2 DATAFILE 'C:\oracle\oradata\wink\data05.dbf' SIZE 10M
3 DEFAULT STORAGE (
4 INITIAL 50K
5 NEXT 50K
6 MINEXTENTS 2
7 MAXEXTENTS 50
8 PCTINCREASE 0
9* )
SQL> /
테이블 영역이 생성되었습니다.
테이블 스페이스의 변경
테이블 스페이스에 대한 변경은 ALTER TABLESPACE를 사용하면 된다.
만약 여러분이 온라인 테이블 스페이스 백업을 한다면 백업 시작 전후 다음과 같이 해야 한다.
SQL> alter tablespace test begin backup;
SQL> alter tablespace test end backup;
또한 테이블 스페이스를 온라인 또는 오프라인으로 만들수 있다. 만약 테이블 스페이스가 오프라인이 된다면 해당 테이블 스페이스에 스키마 객체를 만들 수 없다. 테이블 스페이스를 OFFLINE 시키는 옵션은 3가지 정도 있는데 첫째 아래 예문처럼 OFFLINE NORMAL이 있다. 이는 TABLESPACE에 있는 데이터 파일들이 오류가 없는 상태라면 OFFLINE이 가능 한 모드 이다. 오류가 있으면 테이블 스페이스의 OFFLINE은 불가능 하다. 둘째 TEMPORARY OFFLINE 이 있는데 이 경우엔 테이블 스페이스의 데이터 파일중 오류가 있다고 하더라도 임시적으로 OFFLINE 이 가능한 모드 이다. 마지막으로 세번째 IMMEDIATE OFFLINE 이 있는데 이는 테이블 스페이스의 어떠한 데이터 파일에 대한 CHECK POINT 없이 즉시 OFFLINE 이 가능한 모드 이다.
SQL> connect / as sysdba
SQL> SQL> alter tablespace test offline normal;
테이블 영역이 변경되었습니다.;
테이블 영역이 변경되었습니다.
SQL> connect jclee/test2003
연결되었습니다.
SQL> create table test (
2 column1 varchar2(10),
3 column2 number(5,0)
4 );
create table test (
*
1행에 오류:
ORA-01542: 'TEST' 테이블스페이스가 오프라인입니다. 영역을 할당할 수 없습니다
SQL> connect / as sysdba;
연결되었습니다.
SQL> alter tablespace test online;
테이블 영역이 변경되었습니다.
SQL> create table test (
2 column1 varchar2(10),
3 column2 number(5,0)
4 );
테이블이 생성되었습니다.
테이블 스페이스의 삭제
데이블 스페이스의 삭제는 DROP TABLESPACE 명령을 이용하면 된다.
SQL> connect / as sysdba
연결되었습니다.
SQL> drop tablespace test;
테이블 영역이 삭제되었습니다.
SQL> connect jclee/test2003;
연결되었습니다.
아래는 JCLEE 사용자의 기본 테이블 스페이스가 삭제 됨으로서 JCLEE 사용자가 객체를 만들 때 오류가 발생하는 예문이다. 만약 테이블 스페이스가 영구히 삭제 되었다면 사용자를 변경하여 작업에 무리가 없도록 해야 한다. 아래는 JCLEE 사용자의 기본 테이블 스페이스를 기본적으로 생성되는 USERS라는 테이블 스페이스로 변경 하는 예문 이다.
SQL> create table test (
2 col1 varchar2(10),
3 col2 number(5,0)
4 );
create table test (
*
1행에 오류:
ORA-00959: 테이블 영역 'TEST' 가 존재하지 않습니다
SQL> connect / as sysdba;
연결되었습니다.
SQL> alter user jclee default tablespace users;
사용자가 변경되었습니다.
Database의 크기 늘이기
데이터베이스의 크기를 늘인 다는 것은 결국 데이터 파일 사이즈 합을 늘이는 것과 같다. 아래의 실습을 따라 하면서 데이블 스페이스 및 데이터 파일 다루는 것을 익히도록 하자.
SCOTT 계정으로 접속해 있다면 데이터베이스 관리자의 권한으로 접속을 하자. 아래의 실습에 나오는 경로는 여러분들의 환경에 맞게 적절히 수정 하기 바란다.
SQL> connect / as sysdba
이번 실습은 오라클을 설치하면 기본적으로 생성되는 USERS라는 테이블 스페이스를 가지고 예문들을 생성 해 보겠다.
데이터베이스의 크기를 늘이기 위한 첫번째 방법은 기존의 테이블 스페이스의 크기를 늘이는 것이다. 이경우는 USERS 테이블 스페이스를 계속 사용하다 보니 정해진 한계 사이즈에 도달 한 경우에 주로 사용하는 방법이다. 즉 기존 테이블 스페이스에 새로운 DataFiles을
Add 하는 방법이다.
SQL> alter tablespace users add datafile 'C:\oracle\oradata\wink\users03.dbf' size 10m;
테이블 영역이 변경되었습니다.
두번째 방법은 이전에 실습해 보았던 방법으로 새로운 테이블 스페이스를 추가 하는 것이다. TEST라는 테이블 스페이스를 추가하자. 혹시 이미 있다는 오류가 뜨면 DROP TABLESPACE TEST 명령으로 삭제 후 다시 실행 하자. 또한 아래처럼 파일이 존재한다는 오류가 뜨는 이유는 테이블 스페이스는 삭제 했으나 해당 데이터 파일을 지우지 않아 같은 곳에 같은 이름의 파일을 만들 수 없으므로 나는 오류이니 해당 디렉토리에 가서 TEST01.DBF 파일을 지운 후 CREATE TABLESPACE 명령을 수행 하자.
SQL> create tablespace test datafile 'C:\oracle\oradata\wink\test01.dbf' size 10
m ;
create tablespace test datafile 'C:\oracle\oradata\wink\test01.dbf' size 10m
*
1행에 오류:
ORA-01119: 'C:\oracle\oradata\wink\test01.dbf' 데이터베이스 파일 작성시 오류가
생겼습니다
ORA-27038: skgfrcre: 파일이 존재합니다
OSD-04010: <create> 옵션이 지정되었으나 파일이 존재함
TEST01.DBF를 지운 후……
SQL> create tablespace test datafile 'C:\oracle\oradata\wink\test01.dbf' size 10
m ;
테이블 영역이 생성되었습니다.
세번째 방법은 ALTER DATABASE 명령을 이용하여 기존에 존재하는 데이터 파일의 사이즈를 늘이는 방법이다. 아래 예문은 기존 10M인 TEST01.DBF를 20M로 늘임으로서 TEST 테이블 스페이스의 크기를 늘이는 예문 이다.
SQL> alter database datafile 'C:\oracle\oradata\wink\test01.dbf' resize 20m;
데이타베이스가 변경되었습니다.
 |  기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱…  | 12-27 | 2236 | |
| [채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취… | 12-11 | 1609 | |
| 53 | [평일주간100%환급]Spring,JAVA,JSP,안드로이드,C#닷넷,SQL,튜닝… | 03-15 | 1394 | |
| 52 | [주말]C#,ASP.NET마스터 | 01-31 | 1514 | |
| 51 | [기업100%환급,평일주간]SQL기초에서스키마오브젝트,PLSQL,힌트… | 01-31 | 1721 | |
| 50 | [기업100%환급]개발자를위한스프링,마이바티스,하이버네이트(스… | 01-31 | 1205 | |
| 49 | [평일주간,평일야간,주말]Spring,MyBatis,Hibernate개발자과정 | 01-19 | 1504 | |
| 48 | [평일주간,평일야간,주말]안드로이드개발자과정 | 01-11 | 1335 | |
| 47 | [평일야간,주말주간,주말야간]JAVA,Network&JSP&Spring,MyBatis,… | 01-03 | 1862 | |
| 46 | 기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱… | 12-27 | 2236 | |
| 45 | [기업100%환급]자바웹개발기초과정(JAVA,JDBC,JSP,Servlet,Aajx,… | 12-19 | 1611 | |
| 44 | [평일주간야간, 주말]웹퍼블리싱 마스터(HTML5,CSS3,jQUERY,AJAX… | 12-14 | 1584 | |
| 43 | [채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취… | 12-11 | 1609 | |
| 42 | [주말주간]자바&웹,jQUERY,스프링프레임워크 | 12-09 | 1284 | |
| 41 | [평일야간]닷넷(C#,Network,ADO.NET,ASP.NET)마스터 | 12-01 | 1506 | |
| 40 | [기업100%환급]자바기초&안드로이드개발자과정(Android전액환급… | 12-01 | 1675 | |
| 39 | [평일야간,주말]SQL기초에서실무까지(SQL기초,PLSQL,힌트,튜닝) | 12-01 | 1154 | |
댓글 없음:
댓글 쓰기