undo, redo, controlfile

YoonSeo Park ·2023년 12월 18일

Oracle Architecture

목록 보기

3/11

2023/12/18

문제

oracle 계정 홈 디렉터리에 userdata 디렉터리를 생성하세요.

[oracle@oracle ~]$ cd ~
[oracle@oracle ~]$ mkdir userdata

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 2M MAXSIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

select * from dba_tablespaces;
select * from dba_data_files;
select * from dba_free_space;

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;

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

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

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

DROP TABLE hr.emp purge;

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

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

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

-1
ALTER TABLESPACE dw_tbs OFFLINE normal;

-2
[oracle@oracle ~]$ mv -v /home/oracle/userdata/dw_tbs01.dbf /u01/app/oracle/oradata/ora11g/

[oracle@oracle ~]$mv -v /home/oracle/userdata/dw_tbs02.dbf /u01/app/oracle/oradata/ora11g/

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

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

-4
ALTER TABLESPACE dw_tbs ONLINE;

-5
select * from dba_data_files;

dw_tbs 테이블스페이스 삭제

DROP TABLESPACE dw_tbs INCLUDING CONTENTS 
AND DATAFILES;

■ OFFLINE 설정할 수 없는 테이블스페이스

system
undo
temp

■ 모든 데이터파일 이관작업

■ 시나리오

▶ 1. 데이터파일 확인

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/example01.dbf
/u01/app/oracle/oradata/ora11g/undo1.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf

▶ 2. 오라클 db 정상적인 종료

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

▶ 3. 모든 데이터파일을 새로운 디스크 위치로 이동

▶ 모양 만들기

select 'mv -v ' ||name ||' '|| '/home/oracle/userdata/' from v$datafile;
select 'mv -v ' ||name ||' '|| '/home/oracle/userdata/' from v$tempfile;

▶ mv

mv -v /u01/app/oracle/oradata/ora11g/system01.dbf /home/oracle/userdata/
mv -v /u01/app/oracle/oradata/ora11g/sysaux01.dbf /home/oracle/userdata/
mv -v /u01/app/oracle/oradata/ora11g/users01.dbf /home/oracle/userdata/
mv -v /u01/app/oracle/oradata/ora11g/example01.dbf /home/oracle/userdata/
mv -v /u01/app/oracle/oradata/ora11g/undo1.dbf /home/oracle/userdata/
mv -v /u01/app/oracle/oradata/ora11g/temp01.dbf /home/oracle/userdata/

▶ 4. 오라클 db mount 까지만 열기

SQL> startup mount
SQL> select status from v$instance;

▶ 5. 기존 파일을 새로운 데이터 파일로 수정

컨트롤파일까지만 열려있으므로 rename file을 써야한다.

alter database rename file '전에꺼' to '새거';

▶ 모양 만들기

select 'alter database rename file ' ||''''||name||'''' ||' to '||''''
||'/home/oracle/userdata/'||''''||';'
from v$datafile;

select 'alter database rename file ' ||''''||name||'''' ||' to '||''''
||'/home/oracle/userdata/'||''''||';'
from v$tempfile;

▶ alter [local]

ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/system01.dbf' to '/home/oracle/userdata/system01.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/sysaux01.dbf' to '/home/oracle/userdata/sysaux01.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/users01.dbf' to '/home/oracle/userdata/users01.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/example01.dbf' to '/home/oracle/userdata/example01.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/undo1.dbf' to '/home/oracle/userdata/undo1.dbf';
ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/ora11g/temp01.dbf' to '/home/oracle/userdata/temp01.dbf';

▶ 확인
mount까지만해도 보인다

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/example01.dbf
/u01/app/oracle/oradata/ora11g/undo1.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf

▶ 6. 오라클 db open

SQL> alter database open;
SQL> select status from v$instance;
SQL> select status from dba_data_files;
SQL> select count(*) from hr.employees;

■ 모든 데이터파일 이관작업 돌려놓기

■ 시나리오

▶ 1. 데이터파일 확인

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/oracle/userdata/system01.dbf
/home/oracle/userdata/sysaux01.dbf
/home/oracle/userdata/users01.dbf
/home/oracle/userdata/example01.dbf
/home/oracle/userdata/undo1.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/userdata/temp01.dbf

▶ 2. 오라클 db 정상적인 종료

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

▶ 3. 모든 데이터파일을 새로운 디스크 위치로 이동

▶ 모양 만들기

select 'mv -v ' ||name ||' '|| '/u01/app/oracle/oradata/ora11g/'
from v$datafile;

select 'mv -v ' ||name ||' '|| '/u01/app/oracle/oradata/ora11g/'
from v$tempfile;

▶ mv

mv -v /home/oracle/userdata/system01.dbf /u01/app/oracle/oradata/ora11g/
mv -v /home/oracle/userdata/sysaux01.dbf /u01/app/oracle/oradata/ora11g/
mv -v /home/oracle/userdata/users01.dbf /u01/app/oracle/oradata/ora11g/
mv -v /home/oracle/userdata/example01.dbf /u01/app/oracle/oradata/ora11g/
mv -v /home/oracle/userdata/undo1.dbf /u01/app/oracle/oradata/ora11g/
mv -v /home/oracle/userdata/temp01.dbf /u01/app/oracle/oradata/ora11g/

▶ 4. 오라클 db mount 까지만 열기

SQL> startup mount
SQL> select status from v$instance;

▶ 5. 기존 파일을 새로운 데이터 파일로 수정

컨트롤파일까지만 열려있으므로 rename file을 써야한다.

alter database rename file '전에꺼' to '새거';

▶ 모양 만들기

SELECT q'[ALTER DATABASE RENAME FILE ']'|| name || q'[' to '/u01/app/oracle/oradata/ora11g]'
|| substr(name,INSTR(name, '/', -1, 1)) || q'[';]'
from v$datafile;

SELECT q'[ALTER DATABASE RENAME FILE ']'|| name || q'[' to '/u01/app/oracle/oradata/ora11g]'
|| substr(name,INSTR(name, '/', -1, 1)) || q'[';]'
from v$tempfile;

▶ alter [local]

ALTER DATABASE RENAME FILE '/home/oracle/userdata/system01.dbf' to '/u01/app/oracle/oradata/ora11g/system01.dbf';
ALTER DATABASE RENAME FILE '/home/oracle/userdata/sysaux01.dbf' to '/u01/app/oracle/oradata/ora11g/sysaux01.dbf';
ALTER DATABASE RENAME FILE '/home/oracle/userdata/users01.dbf' to '/u01/app/oracle/oradata/ora11g/users01.dbf';
ALTER DATABASE RENAME FILE '/home/oracle/userdata/example01.dbf' to '/u01/app/oracle/oradata/ora11g/example01.dbf';
ALTER DATABASE RENAME FILE '/home/oracle/userdata/undo1.dbf' to '/u01/app/oracle/oradata/ora11g/undo1.dbf';
ALTER DATABASE RENAME FILE '/home/oracle/userdata/temp01.dbf' to '/u01/app/oracle/oradata/ora11g/temp01.dbf';

▶ 확인
mount까지만해도 보인다

SQL> select name from v $datafile; SQL> select name from v$ tempfile;

▶ 6. 오라클 db open

SQL> alter database open;
SQL> select status from v$instance;
SQL> select status from dba_data_files;
SQL> select count(*) from hr.employees;

■ 전체 백업

▶ 1. 복제

▶ 1. db 종료
SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

▶ 2. 가상머신 종료
루트계정으로 이동

[oracle@oracle userdata]$ su -
Password:
Last login: Thu Dec  7 04:45:17 EST 2023 on pts/1

[root@oracle ~]# shutdown -h 0

▶ 3. 가상머신에서

복사할거 오른쪽 마우스 복제
경로선택후

모든네트워크 어탭처 새 mac주소 생성
다음 완료

▶ 2. 가상시스템 내보내기

▶ 1. db 종료

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

▶ 2. 가상머신 종료
루트계정으로 이동

[oracle@oracle userdata]$ su -
Password:
Last login: Thu Dec  7 04:45:17 EST 2023 on pts/1

[root@oracle ~]# shutdown -h 0

▶ 3. 가상시스템 내보내기

그대로 캡처느낌

파일
가상시스템 내보내기
하나 선택후
모든네트워크 어댑터 mac주소포함

가상시스템가져오기

▶ 3. host ip manual로 만들어주기

▶ 1. 오른쪽상단 Eternet(emp0s8)
-> Wired Settings

▶ 2. 2번째 톱니바퀴

▶ 3. ipv4 -> manual ->
adresses
바꿀거 255.255.255.0
껏다키기

▶ 4.
루트계정에서
vi /etc/hosts
호스트 ip 바꾸고
reboot

▶ 5.
오라클계정
sqlplus / as sysdba
shutdown immediate
lsnrctl stop

[oracle@oracle userdata]$ su -
Password:
Last login: Thu Dec 7 04:45:17 EST 2023 on pts/1

[root@oracle ~]# shutdown -h 0

■ 테이블스페이스 확인하는 쿼리문 (중요)

SELECT
 A.tablespace_name AS "TABLESPACE",
 B.file_name AS "FILE",
 A.status,
 B.bytes AS "TOTAL SIZE",
(B.bytes - C.free_bytes) AS "USED SIZE",
C.free_bytes AS "FREE SIZE"
FROM
DBA_TABLESPACES A,
 DBA_DATA_FILES B,
 ( SELECT tablespace_name, file_id, SUM(bytes) AS free_bytes
 FROM DBA_FREE_SPACE
 GROUP BY tablespace_name, file_id
 ) C
WHERE A.tablespace_name = B.tablespace_name
AND A.tablespace_name = C.tablespace_name
AND B.file_id = C.file_id
ORDER BY B.file_id;

■ 컨트롤 파일 단일화 테스트

pfile 백업

create pfile from spfile

하나로 단일화

select * from v$controlfile;

변경

ALTER SYSTEM SET control_files = 
'/u01/app/oracle/product/11.2.0.4/db_1/dbs/control01.ctl' SCOPE = SPFILE;

데이터베이스를 정상적인 종료

SQL > shutdown immediate

만약에 새로운 위치에 컨트롤 파일을 이동 할꺼면 이 단계에서 물리적인 컨트롤 파일을 이동(mv)하면 된다

mv -v '/u01/app/oracle/oradata/ora11g/control01.ctl' '/u01/app/oracle/product/11.2.0.4/db_1/dbs/control01.ctl'

데이터 베이스 오픈

SQL > startup
SQL > show parameter control_files

SQL > select name from v$controlfile;

기존 컨트롤 파일 삭제

cd /u01/app/oracle/fast_recovery_

rm control02.ctl ??

■ redo log 파일 단일화 테스트

리두 로그 그룹 멤버 확인하기

SELECT * FROM v$logfile;
SELECT * FROM v$log;

새로운 그룹 멤버 생성하기

ALTER DATABASE ADD LOGFILE GROUP 7 '/u01/app/oracle/oradata/ora11g/redo07.log' size 100m;
ALTER DATABASE ADD LOGFILE GROUP 8 '/u01/app/oracle/oradata/ora11g/redo08.log' size 100m;
ALTER DATABASE ADD LOGFILE GROUP 9 '/u01/app/oracle/oradata/ora11g/redo09.log' size 100m;

기존 리두 로그 그룹을 새로운 그룹으로 옮기고 비활성화 시키기

SELECT * FROM v$log;
ALTER SYSTEM SWITCH logfile;
SELECT * FROM v$log;
ALTER SYSTEM checkpoint;
SELECT * FROM v$log;

기존 리두 로그 그룹 삭제하기

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;

SELECT * FROM v$log;

리두 로그 그룹 삭제하기

ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/fast_recovery_area/ora11g/redo02.log';

물리적인 파일 삭제하기

cd /u01/app/oracle/oradata/ora11g

cd /u01/app/oracle/fast_recovery_area/ora11g

SELECT * FROM v$log;

2023/12/19

■ block

▶ SGA free 메모리 확인

select * from dba_tablespaces;
select * from v$sgastat;
select round(sum(bytes)/1024/1024,2) free_mb
from v$sgastat where name='free memory';

▶ 기본 블록 크기는 db 생성시 결정 한다.

dbca를 통해서 데이터베이스 생성시에는 8k 로 결정이 된다.
다른 block 크기를 기본 블록으로 설정할려면 수동으로 db생성하면 된다.

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

▶ non standard block

db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0 <- 기본 블록제외
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0

db_cache_size <- db_block_size 로 설정한 값의 data buffer cache 공간

data_buffer_cache
의 default 공간은 db_cache_size 이며
8k 짜리 블록이 디스크에서 메모리로 올라간다.

4k 블록은 default공간에 들어갈수없고
data_buffer_cache 의
db_4k_cache_size 에 들어간다.
그런데 블록을 먼저 만드는것이 아니라
메모리에 먼저 공간을 만들어줘야한다.

테이블 스페이스레벨로 블락 크기를 정한다.

■ 4k block 테이블스페이스 생성

▶ 1. 메모리부터 설정

SQL> alter system set db_4k_cache_size = 12m;

System altered.

SQL> show parameter db_4k_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_4k_cache_size                     big integer 12M

▶ 2. 테이블스페이스 , 테이블 생성

▶ 테이블스페이스 생성

CREATE TABLESPACE oltp_tbs
DATAFILE '/home/oracle/userdata/oltp_tbs01.dbf' size 5m
AUTOEXTEND ON
BLOCKSIZE 4K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

▶ 테이블 생성

CREATE TABLE hr.oltp_emp
TABLESPACE oltp_tbs
AS SELECT * FROM hr.employees;

▶ 확인

select * from dba_segments
where owner = 'HR'
and segment_name in ('OLTP_EMP','EMPLOYEES');

select * from dba_extents
where owner = 'HR'
and segment_name in ('OLTP_EMP','EMPLOYEES');

▶ select 실행시
select * from hr.oltp_emp where department_id =20;
1. parse
syntax

문법체크

시멘틱

메모리에 shared pool의 data libary 캐시에 있는지보고
없으면 databuffer cache 를 보고 만약 메모리 공간이없으면
오래된 더티버퍼를 디스크에 내려야하는데 DBW 를 읽기 전에
LGWR로 리두 로그파일의 current를 컨트롤파일을 이용해 찾아서
내린후에 DBW로 읽어서 여유공간을 만든다.

실행계획 3가지

data access
- row id : 인덱스번호를 찾고가므로 가장빠르다.
- full table scan

bind
: 실행계획 sharing
execute
찾고자하는 정보를 cursor에 담는것
fetch
cursor의 정보를 유저에게

언두
트랜잭션롤백
플래시백쿼리
리커버리

■ undo 테이블스페이스

▶ 1. sys session에서 undo 테이블스페이스 생성

CREATE UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/ora11g/undotbs01.dbf' size 10m
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL;

▶ 확인

select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

▶ 2. 새로운 hr session에서 update

update hr.employees
set salary = salary *1.1
where employee_id =200;

▶ 3. sys session에서 undo 테이블스페이스 변경

SQL> alter system set undo_tablespace = undotbs;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1800
undo_tablespace                      string      UNDOTBS

▶ 확인하면 undotbs가 online으로 바뀌고
변경전 undo는 트랜잭션이 걸린것빼고 offline으로 바뀐다

select segment_id, segment_name, owner, tablespace_name, status
from dba_rollback_segs;

▶ 확인하면 트랜잭션걸린곳은 pending offline 으로 상태가 나온다.

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

▶ 4. 기존 hr session에서 delete 작업

delete from hr.employees where employee_id = 202;

▶ 5. sys session에서 기존 undo 트랜잭션 사용하는지 확인 : 트랜잭션이기떄문에

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

▶ 6. 새로운 hr session에서 update 작업

update hr.employees
set salary = salary *1.1
where employee_id =100;

▶ 7. sys session에서

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

▶ 8. 기존 hr session에서 rollback;

rollback;

▶ 9. sys session에서

SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

rollback으로 트랜잭션이 끝낫지만
undo_retention 때문에 아직 전에 트랜잭션하던곳은
pending offline으로 된다

▶ 10. 기존 hr session에서 delete

delete from hr.employees where employee_id = 202;

▶ 11. sys session에서

drop tablespace undo1 including contents and datafiles;


SELECT n.name, s.extents, s.rssize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

▶ session, transaction

SELECT s.username, t.xidusn, t.ubafil , t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;

xidusn : undo segment 번호
ubafil : undo data file 번호
ubablk : 데이터 블락 번호
used_ublk : 사용하고 있는 undo블락의 수

■ undo 모니터링

10분 단위 undo 발생량 정보 확인
select * from v$undostat;

어느시간떄에 undo 블락의 수를 모니터링한다.
select to_char(begin_time,'yyyy/mm/dd hh24:mi:ss')
, to_char(end_time,'yyyy/mm/dd hh24:mi:ss')
, undoblks
from v$undostat;

하루동안 undo 블락의 수 합계

select to_char(begin_time,'yyyy/mm/dd')
, sum(undoblks)
from v$undostat
group by to_char(begin_time,'yyyy/mm/dd');

■ user 관리 (tablespace,temporary tablespace)

▶ ora1 유저 생성 (기본테이블 스페이스로)

create user ora1 identified by oracle;

예) create table test(id number);
test segment 는 어느 tablespace 에 저장되나요?
default tablespace (user)

▶ default 테이블스페이스 확인

select * from dba_users where username='ORA1';

유저생성시 DEFAULT TABLESPACE 가 지정되어 있지 않으면 SYSTEM TABLESPACE가
되는 문제점 때문에 10g 버전 부터 db 생성시에 db레벨에서
default 테이블스페이가 생성 되어 있다.

▶ db레벨에 기본값으로 설정된 정보 확인
select * from database_properties;

▶ ora2 유저 생성 (테이블 스페이스 example로 고정, temporary tablespace temp(기본값) 으로)

create user ora2 identified by oracle
default tablespace example -- users가 기본이다.
temporary tablespace temp -- temp 가 기본이다.
quota 1m on example;

select * from dba_users where username in ('ORA1','ORA2');

▶ 테이블스페이스 생성

CREATE TABLESPACE user_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/user_tbs01.dbf' size 10m
AUTOEXTEND ON 
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

▶ temporary 테이블스페이스 생성 생성
일시적으로 저장하는것은 TEMPFILE

CREATE TEMPORARY TABLESPACE user_temp
TEMPFILE '/u01/app/oracle/oradata/ora11g/user_temp01.dbf' size 10m
AUTOEXTEND ON 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT MANUAL;

TEMPORARY 테이블스페이스는
유저별로 분리가 되어있다.

▶ default 테이블 스페이스 변경

alter database default tablespace user_tbs;

▶ default temporary 테이블스페이스 변경

alter database default temporary tablespace user_temp;

select * from database_properties;
select * from dba_users where username in ('ORA1','ORA2');

▶ temp10, temporary 테이블스페이스 생성

CREATE TEMPORARY TABLESPACE temp10
TEMPFILE '/u01/app/oracle/oradata/ora11g/user_temp10.dbf' size 10m
AUTOEXTEND ON 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT MANUAL;

SELECT * FROM dba_tablespaces;
SELECT * FROM dba_data_files;
SELECT * FROM dba_temp_files;

▶ ora3 유저 생성 (테이블 스페이스 example로 고정, temporary tablespace temp10(고정값) 으로)

create user ora3 identified by oracle
default tablespace example
temporary tablespace temp10
quota 1m on example;

▶ 확인
select * from dba_users where username in ('ORA1','ORA2','ORA3');

▶ 변경
alter database default tablespace users;
alter database default temporary tablespace temp;

▶ 확인
select from database_properties;
select from dba_users where username in ('ORA1','ORA2','ORA3');

■ 요약

default 테이블스페이스, temporary 테이블스페이스는 변경가능하다.

단, 기본값이었던 유저는 변경되고, 다른 테이블스페이스, temporary테이블스페이스로 지정했던 유저는 변경이 안된다.

■ 따라서 업무별로 default 테이블스페이스와, temporary 테이블스페이스가 나누어져있다.

일반유저는 그냥 디폴트로 쓴다

유저의 기본 테이블스페이스 변경
: ALTER USER 유저명 DEFAULT TABLESPACE 테이블스페이스명;

■ 유저가 사용할 수있는 테이블 스페이스 (쿼터값 확인)

▶ 다시 변경후

alter database default tablespace user_tbs;
alter database default temporary tablespace user_temp;
select * from database_properties;
select * from dba_users where username in ('ORA1','ORA2','ORA3');

▶ ora1,2,3 유저에 create 권한 주기

grant create session, create table to ora1,ora2,ora3;

▶ 확인, (admin_option: sys계정처럼 권한을줄수있는 옵션)

select * from dba_sys_privs where grantee in ('ORA1','ORA2','ORA3');

▶

conn ora1/oracle

create table test(id number, name varchar2(30), dat date);


select table_name, tablespace_name from user_tables;


select * from user_users;

insert into test(id, name, day) values(1,'james',sysdate);

create 할때는 딕셔너리에 설계정보만 담겨있는것이고

insert 작업시 쿼터값을 안받았으므로 에러가난다

ORA-01950: no privileges on tablespace 'USER_TBS'
이유 : USER_TBS tablespace에 대한 quota을 받지 않아서 발생

▶ 쿼터값 확인

select * from user_ts_quotas;

▶ 쿼터값 변경 후 insert, 확인

conn / as sysdba

alter user ora1 quota 1m on user_tbs;
alter user ora1 quota 1m on example;

select * from dba_ts_quotas where username = 'ORA1';


conn ora1/oracle

select * from user_ts_quotas;

insert into test(id, name, day) values(1,'james',sysdate);
commit;

select * from test;

create table emp(id number, name varchar2(30), day date)
tablespace example;

insert into emp(id, name, day) values(1,'james',sysdate);
commit;

select * from emp;

YoonSeo Park

DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)

이전 포스트

파라미터, SGA, PGA, background process

다음 포스트

undo, redo, controlfile

Oracle Architecture

문제

■ OFFLINE 설정할 수 없는 테이블스페이스

■ 모든 데이터파일 이관작업

▶ 1. 데이터파일 확인

▶ 2. 오라클 db 정상적인 종료

▶ 3. 모든 데이터파일을 새로운 디스크 위치로 이동

▶ 4. 오라클 db mount 까지만 열기

▶ 5. 기존 파일을 새로운 데이터 파일로 수정

▶ 6. 오라클 db open

■ 모든 데이터파일 이관작업 돌려놓기

▶ 1. 데이터파일 확인

▶ 2. 오라클 db 정상적인 종료

▶ 3. 모든 데이터파일을 새로운 디스크 위치로 이동

▶ 4. 오라클 db mount 까지만 열기

▶ 5. 기존 파일을 새로운 데이터 파일로 수정

▶ 6. 오라클 db open

■ 전체 백업

▶ 1. 복제

▶ 2. 가상시스템 내보내기

▶ 3. host ip manual로 만들어주기

■ 테이블스페이스 확인하는 쿼리문 (중요)

■ 컨트롤 파일 단일화 테스트

■ redo log 파일 단일화 테스트

■ block

▶ SGA free 메모리 확인

▶ 기본 블록 크기는 db 생성시 결정 한다.

▶ non standard block

■ 4k block 테이블스페이스 생성

▶ 1. 메모리부터 설정

▶ 2. 테이블스페이스 , 테이블 생성

■ undo 테이블스페이스

▶ 1. sys session에서 undo 테이블스페이스 생성

▶ 2. 새로운 hr session에서 update

▶ 3. sys session에서 undo 테이블스페이스 변경

▶ 4. 기존 hr session에서 delete 작업

▶ 5. sys session에서 기존 undo 트랜잭션 사용하는지 확인 : 트랜잭션이기떄문에

▶ 6. 새로운 hr session에서 update 작업

▶ 7. sys session에서

▶ 8. 기존 hr session에서 rollback;

▶ 9. sys session에서

▶ 10. 기존 hr session에서 delete

▶ 11. sys session에서

■ undo 모니터링

■ user 관리 (tablespace,temporary tablespace)

■ 유저가 사용할 수있는 테이블 스페이스 (쿼터값 확인)

▶ 다시 변경후

▶ ora1,2,3 유저에 create 권한 주기

파라미터, SGA, PGA, background process

user 관리(시스템,오브젝트), role 관리, profile 관리, profile의 resource 관리

0개의 댓글