Infra & Security Eng/Database Engineering

Tablespace READ ONLY, OPEN 상태에서 tablespace 이동, Close 상태에서 tablespace 이동, Default permanent tablespace 생성 실습

엔지니어 E 2026. 4. 9. 16:36
반응형

확인사항

1. st 계정을 만들었을 경우 st 가 책임자(소유자)가 됨

 

Tablespace READ ONLY 실습

사용 명령어
SQL> ALTER TABLESPACE [ READ ONLY / READ WRITE ]

ALTER TABLESPACE 테이블스페이스명 READ ONLY;
ALTER TABLESPACE 테이블스페이스명 READ WRITE;
* 지정한 tablespace를 읽기 전용(읽기 쓰기)으로 변경


[st 계정 생성하기]

CREATE USER st
IDENTIFIED BY st
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;

* CREATE USER: st 라는 사용자 생성
* IDENTIFIED BY: st 비밀번호는 st
* DEFAULT TABLESPACE users: 기본 저장공간은 users 테이블스페이스
* QUOTA UNLIMITED ON users: users 테이블스페이스를 용량 제한 없이 사용 가능

[st 계정에 접속권한 부여하기]


GRANT connect, resource TO st;

[st 계정의 계정 상태와 기본 테이블스페이스 조회]

SELECT username, account_status, default_tablespace
FROM dba_users
WHERE username = 'ST';

* default_tablespace: 기본 테이블스페이스




 CREATE TABLE st.test (no NUMBER);

* no NUMBER: no 라는 숫자형 컬럼


[st 계정의 TEST 테이블이 어느 테이블 스페이스에 저장되어 있는지 조회]

SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE owner = 'ST' AND table_name = 'TEST';

[st.test 테이블에 no 컬럼 값으로 10을 삽입]

INSERT INTO st.test VALUES (10);


COMMIT;
* 반드시 COMMIT을 해야 저장이 됨

[st.test 테이블의 모든 데이터 조회]

SELECT * FROM st.test;

[tablespace에 Users를 읽기 전용으로 변경]

ALTER TABLESPACE users READ ONLY;

[USERS tablespace에 READ ONLY 출력물 확인]


ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
[st 사용자로 로그인]

CONN st/st

[st 계정에서 test 테이블에 20 삽입 오류 화면]

INSERT INTO test VALUES (20)
 
* 현재 users 테이블스페이스가 READ ONLY 상태이기 때문에 삽입이 안됨을 확인 

[test table 삭제]

마찬가지로 삭제도 불가함 

차이점
DELETE FROM test: 현재 st로 로그인된 상태에서 본인 테이블 삭제
DELETE FROM st.test" 다른 사용자가 st 소유 테이블을 지정해서 삭제

[TABLE 삭제]

DROP TABLE test;

* 읽기 전용 tablespace의 table은 삭제가능함

차이점
 DROP TABLE: testtest 테이블 삭제
DROP USER ST: st 사용자 삭제
DROP TABLESPACE test: test 테이블스페이스 삭제

[TABLE 삭제 됐는지 조회]

SELECT * FROM tab WHERE tname = 'TEST';

[sys로 접속]

CONN / AS sysdba

[TABLESPACE를 READ ONLY에서 정상으로 되돌리기]

ALTER TABLESPACE users READ WRITE;

[명령어로 확인하기]

SELECT tablespace_name, status, contents, extent_management, segment_space_management FROM dba_tablespaces ORDER BY 1;

* USERS TABLESPACE가 READ ONLY에서 ONLINE으로 되돌아간 것을 볼 수 있다

 

 

OPEN 상태에서 tablespace 이동 실습

확인사항
tablespace 에 te 계정 만들어 놔야됨
사용 명령어
1. 지정된 tablespace OFFLINE 하기
SQL> ALTER TABLESPACE OFFLINE;

* Offline된 tablespace는 더 이상 접근이 불가능함

2. Data file 등록 정보 수정하기(rename)
SQL> ALTER TABLESPACE <tablespace> RENAME DATAFILE
 2    <원래 data file명> TO <이동한 data file>;


3. 지정된 tablespace ONLINE 하기
SQL> ALTER TABLESPACE <tablespace> ONLINE;

4. 데이터 파일 손상시 DROP 하기
ALTER DATABASE DATAFILE '<data file>' OFFLINE [DROP]

[users Tablespace OFFLINE 하기] - step1

ALTER TABLESPACE users OFFLINE;

[명령어로 확인하기] - step2

1)

SELECT tablespace_name, 
       status, 
       contents, 
       extent_management, 
       segment_space_management
FROM dba_tablespaces
ORDER BY 1;

2)

SELECT tablespace_name, bytes, file_name FROM dba_data_files; 

*OFFLINE 된 tablespace는 data file의 크기가 표시되지 않는다

3) 

SELECT t.name tablespace_name, 
       d.bytes, 
       d.name file_name    
FROM v$tablespace t, v$datafile d  
WHERE t.ts#=d.ts# 
ORDER BY 1;

* 동적 성능 뷰를 이용한 데이터 파일 조회도 동일하게 크기가 0으로 나온다 

[OS 명령어로 해당 경로의 파일 목록을 보는 명령어]

!ls /app/ora19c/oradata/DB19/

[data file을 disk3으로 이동] - step3

!mv /app/ora19c/oradata/DB19/users01.dbf /app/ora19c/oradata/disk3/

[disk3에 users01.dbf 파일이 잘 옮겨졌는지 확인]

!ls /app/ora19c/oradata/disk3/

[dictionary 정보 확인하기]

SELECT tablespace_name, bytes, file_name FROM dba_data_files

* data file이 이동 되었으나 dictionary 정보는 수정되지 않은 상태

[date file의 등록 정보를 수정 하기] -  step4

ALTER TABLESPACE users RENAME DATAFILE '/app/ora19c/oradata/DB19/users01.dbf' TO '/app/ora19c/oradata/disk3/users01.dbf';

[dictionary 정보 다시 확인하기]

SELECT tablespace_name, bytes, file_name FROM dba_data_files

* 경로가 제대로 수정되었음을 확인함

[이동된 tablespace를 ONLINE 으로 바꾸기] - step5

ALTER TABLESPACE users ONLINE;

[dictionary 정보 다시 확인하기]
SELECT tablespace_name, bytes, file_name FROM dba_data_files;

[System, undo용 tablespace는 OFFLINE이 안됨을 확인]



ALTER TABLESPACE system OFFLINE;
ALTER TABLESPACE undotbs1 OFFLINE;

 

Close 상태에서 tablespace 이동 실습


MOUNT 상태에서 DATE FILE 이동하는 프로세스

step 1. DB SHUTDOWN
step 2. DATA FILE 이동
step 3. DB MOUNT
step 4. ALTER DATABASE 명령으로 DATA FILE 등록
step 5. DB OPEN

사용명령어
Data file 등록 정보 수정
SQL> ALTER DATABASE RENAME FILE
2       <원래 data file명> TO <이동한 data file명>;

[현재 경로 확인하기]

SELECT tablespace_name, bytes, file_name FROM dba_data_files;

[DB SHUTDOWN] - step1

SHUTDOWN IMMEDIATE

* DB를 shutdown 한다

[DATA FILE 이동] - step2

SQL> !mv /app/ora19c/oradata/DB19/sysaux01.dbf /app/ora19c/oradata/disk3/
SQL> !mv /app/ora19c/oradata/DB19/system01.dbf /app/ora19c/oradata/disk3/
SQL> !mv /app/ora19c/oradata/DB19/undotbs01.dbf /app/ora19c/oradata/disk3/
SQL> !mv /app/ora19c/oradata/DB19/temp01.dbf  /app/ora19c/oradata/disk3/

* data file을 db19에서 disk3 으로 이동한다

[DB MOUNT] - step3

STARTUP MOUNT
* db를 mount 한다

[명령어로 현 상태 확인]

SELECT t.name tablespace_name, d.bytes, d.name file_name
FROM v$tablespace t, v$datafile d
WHERE t.ts# = d.ts#
ORDER BY 1;

* Data file은 이동했으나 아직 오라클은 파일이 이전 경로에 위차하는 것으로 알고 있다

[데이터 파일을 다른 경로로 이동]

ALTER DATABASE RENAME FILE '/app/ora19c/oradata/DB19/sysaux01.dbf' TO '/app/ora19c/oradata/disk3/sysaux01.dbf';
ALTER DATABASE RENAME FILE '/app/ora19c/oradata/DB19/system01.dbf' TO '/app/ora19c/oradata/disk3/system01.dbf';
ALTER DATABASE RENAME FILE '/app/ora19c/oradata/DB19/undotbs01.dbf' TO '/app/ora19c/oradata/disk3/undotbs01.dbf';
ALTER DATABASE RENAME FILE '/app/ora19c/oradata/DB19/temp01.dbf' TO '/app/ora19c/oradata/disk3/temp01.dbf';

[명령어로 수정됐는지 확인]

SELECT t.name tablespace_name, d.bytes, d.name file_name
FROM v$tablespace t, v$datafile d
WHERE t.ts# = d.ts#
ORDER BY 1;

* temporary tablespace의 파일은 data file이 아니라 temp file로 9i 버전부터 별도로 관리된다

[컨트롤 파일 경로 확인]
컨트롤 파일은 DB의 가장 중요한 파일 (DB 구조, 파일 위치 등 기록) 이라 경로를 파악해두는 것이 필수

SELECT name FROM v$controlfile;

* data file이외에 컨트롤 파일이나  리두 로그 파일이 어디에 위치하는  확인하고 불필요한 DB19 디렉토리를 삭제


[redo log 파일 실제 경로 확인]
데이터파일을 다른 경로로 이동하는 작업 중이니까 redo log 파일 위치도 파악해두는 것

SELECT member FROM v$logfile;

[DB19 폴더 강제로 삭제하기] - 앞서 데이터파일을 전부 disk3로 이동했으니 비어있는 기존 DB19 폴더를 정리

!rm -rf /app/ora19c/oradata/DB19


[ DB19 폴더 강제로 삭제한 것 확인하기]

!ls /app/ora19c/oradata/
[MOUNT 상태로 있던 DB를 OPEN(정상상태)으로 되돌리기]

ALTER DATABASE OPEN;

 

Default permanent tablespace 실습

실습에 앞서 ST, INDX T/S 생성- 각 TS는 두 개의 Datafile 을 disk1, disk2에 10M 크기로 갖도록 생성한다

ST, INDX 테이블스페이스 생성

CREATE TABLESPACE st
DATAFILE '/app/ora19c/oradata/disk1/st01.dbf' SIZE 10M,
         '/app/ora19c/oradata/disk2/st02.dbf' SIZE 10M;

CREATE TABLESPACE indx 
DATAFILE '/app/ora19c/oradata/disk1/indx01.dbf' SIZE 10M,
         '/app/ora19c/oradata/disk2/indx02.dbf' SIZE 10M;
사용명령어
DB 전체에 대해 정의된 DEFAULT TABLESPACE를 조회함
SQL> SELECT * FROM DATABASE_PROPERTIES
2        WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';


[DEFAULT TABLESPACE 조회]

SELECT * FROM DATABASE_PROPERTIES WHERE property_name LIKE '%DEFAULT_PER%';

* DATABASE_PROPERTIES: DB 전체 속성 정보 뷰
* LIKE '%DEFAULT_PER%': DEFAULT_PERMANENT 관련 항목만 필터링

[te01 사용자 , 비밀번호 생성]

CREATE USER te01 IDENTIFIED BY te01;


[DB에 존재하는 모든 사용자 정보를 조회]

SELECT username, default_tablespace, temporary_tablespace, account_status, profile
FROM dba_users
ORDER BY 1;

* user별(ex. TE01) DEFAULT TABLESPACE를 지정하지 않으면 DB에 지정된 TABLESPACE가 자동으로 지정됨

[te 테이블스페이스를 DEFAULT TABLESPACE 로 지정]

1) TE 테이블스페이스 생성(DISK1)

CREATE TABLESPACE te DATAFILE '/app/ora19c/oradata/disk1/te01.dbf' SIZE 10M;

2) DB 기본 테이블스페이스를 TE로 변경 

ALTER DATABASE DEFAULT TABLESPACE te;

3) 명령어로 확인하기

SELECT * FROM DATABASE_PROPERTIES WHERE property_name LIKE '%DEFAULT_PERMANENT%';

[ te02 계정, 비밀번호 생성]

CREATE USER te02 IDENTIFIED BY te02;

[te01,02 계정 생성 됐는지 확인]

SELECT username, default_tablespace FROM dba_users WHERE username IN ('TE01', 'TE02');