hyeonga_code
Database_78_DATABASE DB_테이블 스페이스 본문
-----< 관리자 >-----
-- 데이터베이스 저장 영역
-- 블록을 확인합니다.
SHOW PARAMETER block;
/*
NAME TYPE VALUE
----------------------------- ------- -------
db_block_buffers integer 0
db_block_checking string FALSE
db_block_checksum string TYPICAL
db_block_size integer 8192
db_file_multiblock_read_count integer 128
-- 블록 사이즈보다 작은 데이터가 저장되는 경우 여러 행이 하나의 블록 안에 저장됩니다.
-- 블록 사이즈보다 큰 데이터가 저장되는 경우 여러 블록이 연결되어 저장됩니다.
*/
-- 테이블 스페이스 블록 크기를 확인합니다.
SELECT tablespace_name, contents, status, block_size
FROM dba_tablespaces;
-- 기본적으로 존재하는 테이블스페이스입니다.
-- SYSTEM : 시스템이 사용하는 딕셔너리가 저장되어 있습니다.
-- SYSAUX : 시스템이 사용하는 딕셔너리가 저장되어 있습니다.
-- UNDOTBS1 : 읽기 일관성을 유지하기 위해 사용되는 테이블스페이스입니다.
-- TEMP : 큰 테이블을 정렬할 때에 사용합니다.
-- USERS
-- CONTENTS
-- PERMANENT : 정보가 저장되면 수정, 삭제를 하기 전에는 정보가 영구적으로 저장되는 구조입니다.
-- UNDO : 데이터가 TEMPORARY보다 오래 저장됩니다.
-- TEMPORARY : 정렬을 한 데이터 구조입니다. 정렬이 완료되면 데이터가 휘발됩니다.
-- STATUS : 개별 테이블스페이스의 상태를 관리할 수 있습니다.
-- ONLINE : 테이블스페이스에 접근할 수 있습니다.
-- OFFLINE : 테이블스페이스에 접근할 수 없습니다.
-- 테이블스페이스 데이터파일 경로, 크기를 확인합니다.
SELECT tablespace_name, file_name, bytes/1024/1024 || 'mb' AS file_size
FROM dba_data_files;
-- 메모리 대용으로 사용하는 TEMP파일
SELECT tablespace_name, file_name, bytes/1024/1024 || 'mb' AS file_size
FROM dba_temp_files;
-- Header 파일이 없어 Oracle이 체크하지 않습니다.
-- 장애가 발생해도 정렬만 불가능합니다.
-- 장애가 발생하는 경우 장애를 유발하는 파일만 삭제 후 다시 생성이 가능합니다.
-- 문제가 있어도 불편함이 없는 경우 STARTUP을 할 때에 ORACLE이 자동으로 재생성합니다.
-- 경로를 복사하여 파일탐색기에 복사합니다.(파일 이름 제외)
-- 리두 로그 파일 경로 확인하기
SELECT member
FROM v$logfile;
-- 데이터베이스 공간 확장
-- 테이블스페이스 추가
-- 경로를 직접 작성해야합니다.
CREATE TABLESPACE sales
DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES01.DBF'
SIZE 5m;
/*
TABLESPACE SALES이(가) 생성되었습니다.
*/
-- 테이블 스페이스 블록 크기를 확인합니다.
SELECT tablespace_name, contents, status, block_size
FROM dba_tablespaces;
-- 옵션을 설정하지 않아도 자동으로 PERMANENT로 설정됩니다.
-- 테이블스페이스 용량과 경로를 확인합니다.
SELECT tablespace_name, file_name, bytes/1024/1024 || 'mb' AS file_size, autoextensible
FROM dba_data_files;
-- 데이터파일 정보를 확인합니다.
DESC dba_data_files;
/*
이름 널? 유형
--------------- -- -------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
*/
-- 기존 테이블스페이스에 새 데이터파일 추가
ALTER TABLESPACE sales
ADD DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES02.DBF'
SIZE 5M;
/*
TABLESPACE SALES이(가) 변경되었습니다.
*/
-- SALES 테이블스페이스를 활용
-- 기본 테이블스페이스를 SALES로 하는 사용자 계정을 생성합니다.
CREATE USER sh IDENTIFIED BY sh
DEFAULT TABLESPACE sales;
/*
User SH이(가) 생성되었습니다.
*/
-- 권한을 부여합니다.
GRANT connect, resource TO sh;
/*
Grant을(를) 성공했습니다.
*/
-- 테이블 복사
CREATE TABLE sh.emp
AS
SELECT * FROM hr.employees;
/*
Table SH.EMP이(가) 생성되었습니다.
*/
-----< SH >-----
-- 테이블 조회
SELECT * FROM emp;
-- 테이블 정보를 조회합니다.
SELECT table_name, tablespace_name
FROM user_tables;
-- 테이블을 생성합니다.
CREATE TABLE customer
AS
SELECT * FROM emp;
/*
Table CUSTOMER이(가) 생성되었습니다.
*/
-- 테이블을 생성합니다.
CREATE TABLE dept
AS
SELECT * FROM emp;
/*
Table DEPT이(가) 생성되었습니다.
*/
-- 테이블을 생성합니다.
CREATE TABLE member
AS
SELECT * FROM emp;
/*
Table MEMBER이(가) 생성되었습니다.
*/
-- 테이블 정보를 조회합니다.
SELECT table_name, tablespace_name
FROM user_tables;
-- EMP 테이블을 확장합니다. (반복)
INSERT INTO emp
SELECT * FROM emp;
/*
31개 행 이(가) 삽입되었습니다.
62개 행 이(가) 삽입되었습니다.
124개 행 이(가) 삽입되었습니다.
248개 행 이(가) 삽입되었습니다.
496개 행 이(가) 삽입되었습니다.
992개 행 이(가) 삽입되었습니다.
1,984개 행 이(가) 삽입되었습니다.
3,968개 행 이(가) 삽입되었습니다.
7,936개 행 이(가) 삽입되었습니다.
15,872개 행 이(가) 삽입되었습니다.
31,744개 행 이(가) 삽입되었습니다.
--<오류>---------------------------------------------------------------------------
명령의 48 행에서 시작하는 중 오류 발생 -
INSERT INTO emp
SELECT * FROM emp
오류 보고 -
ORA-01653: unable to extend table SH.EMP by 128 in tablespace SALES
-------------------------------------------------------------------------------------
-- 구문 오류가 아닌 메모리 공간 부족 문제입니다.
*/
-----< 관리자 >-----
SELECT tablespace_name, bytes/1024/1024 || 'mb' AS file_size
FROM dba_free_space;
-- 자투리 공간이 각각 조회됩니다.
-- FREE SPACE를 확인합니다.
SELECT tablespace_name, COUNT(*), SUM(bytes/1024/1024) AS free_size
FROM dba_free_space
GROUP BY tablespace_name;
-- 테이블 스페이스 크기를 변경합니다.
ALTER TABLESPACE sales
ADD DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES02.DBF'
SIZE 10m;
/*
TABLESPACE SALES이(가) 변경되었습니다.
*/
---안되는 이유
오류 보고 -
ORA-01537: cannot add file 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES02.DBF' - file already part of database
01537. 00000 - "cannot add file '%s' - file already part of database"
*Cause: During CREATE or ALTER TABLESPACE, a file being added is already
part of the database.
*Action: Use a different file name.
ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES02.DBF'
RESIZE 10m;
/*
Database이(가) 변경되었습니다.
*/
-----< SH >-----
-- EMP 테이블을 확장합니다. (반복)
INSERT INTO emp
SELECT * FROM emp;
/*
63,488개 행 이(가) 삽입되었습니다.
-- 데이터 저장 공간이 부족하지 않아 저장이 가능합니다.
-- 15M의 DB 공간을 더 사용중입니다.
*/
-----< 관리자 >-----
-- 테이블스페이스 용량과 경로를 확인합니다.
SELECT tablespace_name, file_name, bytes/1024/1024 || 'mb' AS file_size, autoextensible
FROM dba_data_files;
-- FREE SPACE를 확인합니다.
SELECT tablespace_name, COUNT(*), SUM(bytes/1024/1024) AS free_size
FROM dba_free_space
GROUP BY tablespace_name;
-----< SH >-----
-- 테이블을 삭제합니다.
DROP TABLE customer;
/*
Table CUSTOMER이(가) 삭제되었습니다.
*/
-----< 관리자 >-----
-- FREE SPACE를 확인합니다.
SELECT tablespace_name, COUNT(*), SUM(bytes/1024/1024) AS free_size
FROM dba_free_space
GROUP BY tablespace_name;
-- 테이블스페이스 개수가 3개가 됩니다.
-----< SH >-----
-- 삭제한 테이블을 복구합니다.
FLASHBACK TABLE customer TO BEFORE DROP;
/*
Flashback을(를) 성공했습니다.
*/
-----< 관리자 >-----
-- FREE SPACE를 확인합니다.
SELECT tablespace_name, COUNT(*), SUM(bytes/1024/1024) AS free_size
FROM dba_free_space
GROUP BY tablespace_name;
-- 테이블스페이스 개수가 2개로 돌아갑니다.
-- 기존 데이터 파일 사이즈 변경
-- SALES01.DBF 공간 확장
ALTER DATABASE DATAFILE
'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES01.DBF' RESIZE 10M;
/*
Database이(가) 변경되었습니다.
*/
-- 기존 데이터 파일 사이즈 변경
ALTER DATABASE DATAFILE
'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES01.DBF' RESIZE 10M;
/*
Database이(가) 변경되었습니다.
*/
-- FREE SPACE를 확인합니다.
SELECT tablespace_name, COUNT(*), SUM(bytes/1024/1024) AS free_size
FROM dba_free_space
GROUP BY tablespace_name;
-----< 인사 관리 >-----
SELECT default_tablespace FROM user_users;
-- 테이블 목록을 조회합니다.
SELECT table_name, tablespace_name
FROM user_tables;
-- 테이블스페이스를 지정하여 테이블을 생성합니다.
CREATE TABLE prod
TABLESPACE sales
AS
SELECT * FROM employees;
/*
Table PROD이(가) 생성되었습니다.
*/
CREATE TABLE std (
std_id NUMBER(4),
std_name VARCHAR2(20))
TABLESPACE sales;
/*
Table STD이(가) 생성되었습니다.
*/
SELECT table_name, tablespace_name
FROM user_tables;
-- STD 테이블에 데이터를 삽입합니다.
INSERT INTO std
VALUES (1, 'TEST' );
/*
1 행 이(가) 삽입되었습니다.
*/
-- 커밋합니다.
COMMIT;
/*
커밋 완료.
*/
-- STD 테이블을 조회합니다.
SELECT * FROM std;
-----< 관리자 >-----
-- SALES 테이블스페이스를 중지합니다.
ALTER TABLESPACE sales OFFLINE;
/*
TABLESPACE SALES이(가) 변경되었습니다.
*/
-- 테이블 스페이스 블록 크기를 확인합니다.
SELECT tablespace_name, contents, status, block_size
FROM dba_tablespaces;
-----< 인사 관리 >-----
-- USERS 테이블스페이스에 있는 테이블을 조회합니다.
SELECT * FROM departments;
SELECT * FROM employees;
-- 조회가 가능합니다.
-- SALES 테이블스페이스에 있는 테이블을 조회합니다.
SELECT * FROM prod;
/*
--< 오류 >----------------------------------------------------------------------------------------
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES01.DBF'
00376. 00000 - "file %s cannot be read at this time"
*Cause: attempting to read from a file that is not readable. Most likely
the file is offline.
*Action: Check the state of the file. Bring it online
----------------------------------------------------------------------------------------------------
-- 테이블스페이스를 읽어올 수 없습니다.
*/
-----< 관리자 >-----
-- SALES 테이블스페이스를 시작합니다.
ALTER TABLESPACE sales ONLINE;
/*
TABLESPACE SALES이(가) 변경되었습니다.
*/
-- 테이블 스페이스 상태를 확인합니다.
SELECT tablespace_name, contents, status, block_size
FROM dba_tablespaces;
-- SALES 테이블스페이스를 읽기 전용으로 변경합니다.
ALTER TABLESPACE sales READ ONLY;
/*
TABLESPACE SALES이(가) 변경되었습니다.
*/
-- 테이블 스페이스 크기를 확인합니다.
SELECT tablespace_name, contents, status, block_size
FROM dba_tablespaces;
-----< 인사 관리 >-----
-- SALES 테이블스페이스에 있는 테이블을 조회합니다.
SELECT * FROM prod;
-- 읽어올 수 있습니다.
-- SALES 테이블스페이스에 있는 테이블의 데이터를 수정합니다.
UPDATE prod
SET salary=salary*1.1;
/*
--< 오류 >----------------------------------------------------------------------------------------
오류 보고 -
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5: 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES01.DBF'
----------------------------------------------------------------------------------------------------
-- 테이블스페이스를 읽어올 수 없습니다.
*/
-----< 관리자 >-----
-- 읽기 전용 테이블스페이스 상태를 ONLINE으로 변경합니다.
ALTER TABLESPACE sales READ write;
/*
TABLESPACE SALES이(가) 변경되었습니다.
*/
-- 테이블 스페이스 크기를 확인합니다.
SELECT tablespace_name, contents, status, block_size
FROM dba_tablespaces;
-- 데이터베이스 공간 축소
-- 테이블스페이스 삭제
-- 테이블 삭제 방법
-- DROP TABLE table;
-- DROP USER user;
-- DROP DATAFILE;
-- 테이블스페이스를 삭제합니다.
DROP TABLESPACE sales;
/*
--<오류>----------------------------------------------------------------------------
오류 보고 -
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
01549. 00000 - "tablespace not empty, use INCLUDING CONTENTS option"
*Cause: Tried to drop a non-empty tablespace
*Action: To drop all the objects in the tablespace, use the INCLUDING
CONTENTS option
--------------------------------------------------------------------------------------
-- 테이블스페이스 내의 데이터가 존재하므로 데이터도 모두 삭제하겠다고 명시해야 합니다.
*/
-- 테이블스페이스를 데이터와 같이 삭제합니다.
DROP TABLESPACE sales
INCLUDING CONTENTS AND DATAFILES;
/*
TABLESPACE SALES이(가) 삭제되었습니다.
-- 파일 탐색기에서 SALES01,02 파일이 있지만 더미 파일로 사용할 수는 없습니다.
*/
-----< 인사 관리 >------
-- 테이블 목록을 조회합니다.
SELECT table_name, tablespace_name
FROM user_tables;
-- SALES 테이블스페이스에 존재하던 테이블이 출력되지 않습니다.
-- SH 사용자계정을 삭제합니다.
DROP USER sh CASCADE;
/*
User SH이(가) 삭제되었습니다.
*/
-- 데이터파일 삭제
ALTER TABLESPACE sales
DROP DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SALES02.DBF';
/*
TABLESPACE SALES이(가) 변경되었습니다.
*/
-- 테이블스페이스 삭제
DROP TABLESPACE sales INCLUDING CONTENTS AND DATAFILES;
/*
TABLESPACE SALES이(가) 삭제되었습니다.
*/
-- 테이블스페이스 상태
-- 상태 변경
/*
ALTER TABLESPACE tablespace_name [ONLINE | OFFLINE ];
ALTER TABLESPACE tablespace_name [ READ WRITE | READ ONLY ];
*/
'Oracle Database' 카테고리의 다른 글
Database_80_PL/SQL_개요 (0) | 2023.08.30 |
---|---|
Database_79_DATABASE_DB 구축 실습 (0) | 2023.08.29 |
Database_77_DATABASE DB 연결_SELECT, SHUTDOWN (0) | 2023.08.27 |
Database_76_ORACLE 구조_ORACLE DBMS, COMMIT 처리, CKPT (0) | 2023.08.27 |
Database_75_정규화 (0) | 2023.08.27 |