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 ];
    */