231218 Linux 문제 겸 test

1. oracle 계정 홈 디렉터리에 userdata 디렉터리를 생성하세요.
[oracle@oracle ~]$ pwd
/home/oracle
[oracle@oracle ~]$ mkdir userdata
[oracle@oracle userdata]$ pwd
/home/oracle/userdata

2. tablespace 생성하세요.
    tablespace 이름  : dw_tbs
    datafile 위치 및 이름  : /home/oracle/userdata/dw_tbs01.dbf
    datafile 사이즈  : 5m
    datafile 자동 확장 활성화
    extent 관리 : local uniform size 1m
    segment space management: auto

CREATE TABLESPACE dw_tbs
DATAFILE '/home/oracle/userdata/dw_tbs01.dbf' SIZE 5M
AUTOEXTEND ON NEXT 1M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

# 생성 확인

SELECT * FROM dba_tablespaces WHERE tablespace_name = 'DW_TBS';
SELECT * FROM dba_data_files WHERE tablespace_name = 'DW_TBS';

3. dw_tbs 테이블 스페이스에 데이터 파일을 추가 해주세요.
    datafile 위치 및 이름  : /home/oracle/userdata/dw_tbs02.dbf
    datafile 사이즈  : 5m
    datafile 자동 확장 비활성화

ALTER TABLESPACE dw_tbs ADD DATAFILE '/home/oracle/userdata/dw_tbs02.dbf' SIZE 5M;
SELECT * FROM dba_data_files;

4. /home/oracle/userdata/dw_tbs02.dbf 데이터 파일을 자동 확장 기능으로 수정하세요.

ALTER DATABASE DATAFILE '/home/oracle/userdata/dw_tbs02.dbf' AUTOEXTEND ON;

5. hr.employees 테이블을 hr.emp 복제하세요. hr.emp 테이블은 dw_tbs 테이블스페이스에 생성하세요.

DROP TABLE hr.emp PURGE;

CREATE TABLE hr.emp
TABLESAPCE dw_tbs
AS SELECT * FROM hr.employees;

SELECT * FROM dba_segments 
WHERE owner = 'HR' AND segment_name = 'EMP';

SELECT * FROM dba_free_space 
WHERE tablesapce_name = 'DW_TBS';

6. dw_tbs에 있는 데이터 파일을  
/u01/app/oracle/oradata/ora11g/ 디렉터리로  이관 작업하세요.
ⓐ sql developer 창에서 

ALTER TABLESPACE dw_tbs OFFLINE NORMAL;

SELECT * FROM dba_tablespaces;
SELECT * FROM v$datafile;

ⓑ putty 창에서
[oracle@oracle ~]$ mv -v /home/oracle/userdata/dw_tbs01.dbf /u01/app/oracle/oradata/ora11g/dw_tbs01.dbf
‘/home/oracle/userdata/dw_tbs01.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/dw_tbs01.dbf’
removed ‘/home/oracle/userdata/dw_tbs01.dbf’
[oracle@oracle ~]$ mv -v /home/oracle/userdata/dw_tbs02.dbf /u01/app/oracle/oradata/ora11g/dw_tbs02.dbf
‘/home/oracle/userdata/dw_tbs02.dbf’ -> ‘/u01/app/oracle/oradata/ora11g/dw_tbs02.dbf’
removed ‘/home/oracle/userdata/dw_tbs02.dbf’

ⓒ SQL Developer 창에서

ALTER TABLESPACE dw_tbs RENAME DATAFILE 
'/u01/app/oracle/userdata/dw_tbs01.dbf' TO
'/u01/app/oracle/oradata/ora11g/dw_tbs01.dbf';

ALTER TABLESPACE dw_tbs RENAME DATAFILE 
'/u01/app/oracle/userdata/dw_tbs02.dbf' TO
'/u01/app/oracle/oradata/ora11g/dw_tbs02.dbf';

ⓓ SQL Developer 창에서

ALTER TABLESPACE tablespace dw_tbs ONLINE;

SELECT * FROM dba_tablespaces;
SELECT * FROM v$datafile;

SELECT COUNT(*) FROM hr.emp;

7. dw_tbs 테이블스페이스 삭제

DROP TABLESPACE dw_tbs INCLUDING CONTENTS AND DATAFILES;