복습
테이블 스페이스 생성시 옵션
💡 두개의 차이점은, free space에 대한 관리를 system tablespace에서 하느냐, local의 해당 테이블 스페이스의 data file에서 하느냐의 차이이다.
1.locally managed tablespace: 빈 공간에 대한 관리를 local table space에서 수행create tablespace ts20 datafile '/home/oracle/ts20.dbf' size 10m extent managed local;
dictionary managed tablespace: 빈 공간에 대한 관리를 system tablespace에서 수행create tablespace ts20 datafile '/home/oracle/ts20.dbf' size 10m extent managed dictionary; -- 이렇게 하면 에러난다. 이게 가능하려면 db 생성시 system tablespace를 dictionary managed 방식으로 만들어야 가능하다.✅ dictionary managed tablespace는 옛날방식, locally managed tablespace는 최근방식이다.
default tablespace란 ?
💡 우리가 다음과 같이 테이블을 생성했을 때 테이블이 만들어지는 기본 tablespace를 말한다.
* 생성하고
create table emp70
(empno number(10),
ename varchar2(10) );* 확인해보면 USERS로 되어있을 것이다.
select table_name, tablespace_name
from user_tables
where table_name='EMP70';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP70 USERS* 아래처럼 default tablespace 변경 가능
alter database default tablespace example;
alter database default tablespace users;오늘의 TIL
temporary tablespace 생성하기
❓ temporary tablespace의 역할?
1. 정렬작업이 일어난다.
PGA 영역에서 정렬작업을 하는데 PGA 영역에서 정렬 작업을 다 수행하지 못하면 temporary tablespace에서 정렬 작업을 하기위해 disk i/o를 일으킵니다.
예) 아래는 크고 과도한 정렬작업이라고 가정 select ename, sal from emp order by sal desc;✅ PGA도 한정된 메모리다. 만약 정렬작업이 작다면 PGA에서 끝나지만 너무 크면 temporary tablespace에 내려쓰며 정렬을 한다. 다 정리를 못하니까 어딘가 창고처럼 보관을 해야하는데 이것을(정렬된 내용을) disk i/o를 일으키며 정렬된것을 내려 보관하고, 다시 정렬된것을 내려 보관하고.... 한다.
✅ 정렬을 일으키는 SQL을 날리려면 temporary tablespace 확인해야한다. 아래는 정렬을 일으키는 애들...! 이작업들을 temporary tablespace 공간이 없는 상태에서 한다면 실패할 것이다.
1.order by
2.create index..
3.sort merge join
4. minus
5. intrsect
6. union
2. with절
with절의 temp table이 temporary tablespace에서 생성됩니다.
with deptno_sumsal as (select/*+materialize*/ deptno, sum(sal) as sumsal from emp group by deptno) select * from deptno_sumsal; select * from table(dbms_xplan.display) -- 실행계획 확인✅materialize힌트를 쓰면 temporary tablespace에 만들어진다. 실행계획 확인해보면
TEMP TABLE TRANSFORMATION이라고 뜨는데, 만약 tempoary tablespace가 풀이 났으면 with절이 실패한다. 그럼 dba가 빨리 temp tablespace의 공간을 늘리거나 또는 다른 temp tablespace를 만들고 그 테이블 스페이스를 default temp tablespace로 지정하면 된다.✅ 만약 공간을 바로 늘릴 수 없다면? 개발자에게 inline 힌트를 쓰라고 이야기하면 된다.
inline힌트는 temp table을 만들지 않고 서브쿼리 형태르 with절을 수행하는 것이다.with deptno_sumsal as (select/*+inline*/ deptno, sum(sal) as sumsal from emp group by deptno) select * from deptno_sumsal;
3. temporary table이 temp tablespace에 생성된다.
- 임시 테이블 만들기전에 temp tablespace 공간이 넉넉한지 확인을 해야한다.
실습 (3.4.3) default temporary tablespace 를 변경하는 방법
실습 전에 temp02 테이블을 만든다.
drop tablespace temp02 including contents and datafiles;
SYS> create temporary tablespace temp02
tempfile '/home/oracle/temp02b.dbf' size 10m;
SQL> select tablespace_name from user_tablespaces; ✔️생성 확인
SYS> select property_name, property_value
from database_properties; 
✔️default temporary tablespace 를 변경한 후, 유저들이 사용하는 temporary tablespace 가 변경되었는지 확인하기
SYS> select username, temporary_tablespace
from dba_users
where username in ('SH', 'SCOTT');
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SCOTT TEMP02
SH TEMP02 PGA 영역이 1MB 이고, Temp Tablespace 가 9MB 일때, Sort 해야 할 데이터가 100MB 이면 ?
✅ 오류 발생 !
PGA 영역이 1MB 이고, Temp Tablespace 가 50MB 일때, Sort 해야 할 데이터가 50MB 이면 ?
✅ One pass 방식으로 정상적인 정렬이 일어남
관련문제 temp tablespace 의 사이즈를 확인하고, temp tablespace 의 사이즈를 50MB로 늘리시오 !
✔️ temp tablespace 사이즈 확인
SYS> select tablespace_name, sum(bytes)/1024/1024||'MB' total_size
from dba_temp_files
group by tablespace_name;
TABLESPACE_NAME TOTAL_SIZE
------------------------------ ------------------------------------------
TEMP02 10MB
TEMP 29MB ✔️ temp tablespace 사이즈 50MB로 늘리기
-- 그전에 주소를 확인하기
SQL> select tablespace_name,file_name
from dba_temp_files;
TEMP
+DATA/orcl/tempfile/temp.264.796857793
TEMP02
/home/oracle/temp02b.dbf우리는 TEMP를 늘릴거니까! 아래처럼 작성
SYS> alter database tempfile '+DATA/orcl/tempfile/temp.264.796857793'
resize 50m;
TABLESPACE_NAME TOTAL_SIZE
------------------------------ ------------------------------------------
TEMP02 10MB
TEMP 50MB -- 변경되었다. 실습) 정렬 작업 시, temporary tablespace 공간이 부족하다는 오류 발생 시키기(ORA-01652)
관련문제 temp03 temporary tablespace 를 사이즈 2m 로 생성하고, temp03 테이블 스페이스를 default temporary tablespace 로 설정하시오
✔️ temp tablespace 생성
SYS> create temporary tablespace temp03
tempfile '/home/oracle/temp03.dbf' size 2m;✔️ default temp tablespace 로 설정
SYS> alter database default temporary tablespace temp03;✔️default temp tablespace 확인
SYS> select property_name, property_value
from database_properties;지금 sh 잠겨있어서 sys에서
alter user sh account unlock;
alter user sh identified by sh;
connect sh/sh✔️ SH계정에서 공간 부족 오류 발생시키기
SH> alter session set workarea_size_policy=manual;
-- pga영역 관리는 내 세션에서 자동으로 안하고 수동으로 하겠다 라는 것.
SH> alter session set sort_area_size=1000; --작게잡기SH> select /*+ leading(a b) use_merge(a b) */ a.*, b.*
from sales a, sales b
where a.prod_id = b.prod_id
order by a.prod_id desc, a.promo_id asc;
ERROR at line 2:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP03 문제 위 문제를 해결하여 에러난 SQL이 잘 수행될 수 있도록 하시오 !
✔️ temp tablespace 사이즈 50MB로 늘리기
그전에 주소를 확인하기
SQL> select tablespace_name,file_name
from dba_temp_files;
TEMP
+DATA/orcl/tempfile/temp.264.796857793
TEMP02
/home/oracle/temp02b.dbf
TEMP03
/home/oracle/temp03.dbf
SYS> alter database tempfile '/home/oracle/temp03.dbf'
resize 50m;
SYS> alter database tempfile '/home/oracle/temp03.dbf'
resize 100m; ✔️ 사이즈 확인
SYS> select tablespace_name, sum(bytes)/1024/1024||'MB' total_size
from dba_temp_files
group by tablespace_name;
TABLESPACE_NAME TOTAL_SIZE
------------------------------ ------------------------------------------
TEMP02 10MB
TEMP 50MB
TEMP03 50MB
✅ 공간을 늘리는것이 한계가 있어서 아래와 같이 sql을 튜닝합니다
1. hash 조인
SH> select /*+ leading(a b) use_hash(b) */ a.*, b.*
from sales a, sales b
where a.prod_id = b.prod_id
order by a.prod_id desc, a.promo_id asc;2. temp05 따로 만들기
create temporary tablespace temp05
tempfile '/home/oracle/temp05.dbf' size 1024m
SYS> alter database default temporary tablespace temp05;3. 위 두방법으로도 안되는 과도한 정렬이었다 더 temp늘릴 수 없으니 위 SQL을 튜닝하는데 order by 빼고 하라고 권고한다.
✍🏻 실습 후 실행
✔️ 다시 default temporary tablespace를 temp로 돌려놓기
SYS> alter database default temporary tablespace temp;✔️ temp 빼고 나머지 모두 드롭하기
SYS> select tablespace_name from dba_temp_files; SYS> drop tablespace temp02 including contents and datafiles; SYS> drop tablespace temp03 including contents and datafiles;
💡 dba를 위한 tip
모든 스크립트는 save 명령어로 저장하고 작업하기
sql을 하나 쳤다면 바로 save시키기
sql쳤음
save temp_ts.sql
Created file temp_ts.sql
SQL> @temp_ts.sql
drop tablespace temp03 including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP03' does not exist
SQL> ed temp_ts.sql
SQL> @temp_ts.sqlundo tablespace 생성, 관리
💡 undo tablespace의 역할
: undo 의 뜻이 취소하다는 뜻이다. 취소하기 위한 데이터가 들어가는 테이블 스페이스 입니다. 우리가 db에서 update, delete를 잘못해서 롤백을 해야할 일이 있는데 이 롤백할 데이터인 변경전 데이터를 저장하는 공간이다.
❓ undo tablespace 관리를 dba가 해주어야 하는이유?
: undo tablespace 가 공간이 부족하면 DML작업시 에러가 나면서 작업이 안된다.
✍🏻 실습 1. undo tablespace를 다른 undo tablespace로 변경**
1. database 에서 사용하는 기본 undo tablespace 가 무엇인지 확인
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
2. undo tablespace를 undotbs2로 생성합니다.
SQL> create undo tablespace undotbs2
datafile '/home/oracle/undotbs2.dbf' size 10m;3. default undo tablespace를 undotbs2로 변경
SQL> alter system set undo_tablespace='UNDOTBS2' scope=both;
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2 --변경되었다. 💡 dba를 위한 tip
아래의 스크립트를 glogin.sql에 설정하면 접속할 때 유저이름이 앞에 나온다.
$ cd $ORACLE_HOME/sqlplus/admin
$ vi glogin.sql
set sqlprompt "_user '@' _connect_identifier > "
cd ~
sys위처럼 하면 아래 이미지처럼 나온다 . 그동안 우리는 SQL> 이라고 나왔었음.
✍🏻 실습2. 과도한 delete작업을 하면 undo tablespace의 공간이 부족하다고 나오는 오류를 경험하기
SH @ orcl > show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SH @ orcl > delete from sales;
delete from sales
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'ORA-30036 유명한 에러다. undo tablespace (롤백할데이터가 들어갈 공간 부족) 공간이 없다.
문제 위 상황을 해결하기.
1. UNDOTBS2 사이즈를 늘리기
2. 기존 undo로 다시 돌려놓기(사이즈 컸던거)
1. UNDOTBS2 사이즈를 늘리고 다시 delete를 시도하기!
✔️ 현재 운영 중인 undo tablespace name 확인
현재 운영 중인 undo tablespace name 확인
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
✔️ undo tablespace 를 500m로 resize
SQL> alter database datafile '/home/oracle/undotbs2.dbf' resize 500m;
SQL> select tablespace_name, bytes/1024/1024 MB, maxbytes/1024/1024 MMB, file_name, autoextensible AUTO
from dba_data_files;✔️ SH 계정에서 delete 실행
SQL> delete from sales;
918843 rows deleted.
SQL> rollback;
Rollback complete.✍🏻 문제 다시 원래 쓰던 undotbs1로 기본 언두 테이블 스페이스를 돌려 놓기
SYS> alter system set undo_tablespace='UNDOTBS1' scope=both; SYS> show parameter undo;
✍🏻 문제 undotbs1 외에 다른 undo 테이블 스페이스는 모두 drop 하기
SYS> select tablespace_name from dba_tablespaces where tablespace_name like 'UNDO%'; TABLESPACE_NAME ------------------------------ UNDOTBS02 UNDOTBS1 UNDOTBS2 UNDOTBS3 SYS> drop tablespace UNDOTBS02 including contents and datafiles; SYS> drop tablespace UNDOTBS2 including contents and datafiles; SYS> drop tablespace UNDOTBS3 including contents and datafiles; TABLESPACE_NAME ------------------------------ UNDOTBS1
including contents and datafiles를 해야 os에 관련된 파일들도 함께 삭제된다. -> 공간 확보 가능
tablespace 상태에 대한 옵션 3가지(p.7-12)
1. read write : 테이블 스페이스의 데이터를 읽고/쓸 수 있는 상태
2. read only : 테이블 스페이스의 데이터를 읽기만 하는 상태
3. offline : 테이블 스페이스의 데이터를 접근하지 못하는 상태🤔 언제 read only로, 언제 offline으로 변경할까?
- 테이블 스페이스의 data file의 위치를 변경하거나 data file이름을 변경하고 싶을 때
- 온라인 백업할 때
관련 실습
1. 테이블 스페이스의 상태를 확인한다.
SYS> select t.name, d.enabled, d.status
from v$tablespace t, v$datafile d
where t.ts# = d.ts#;
2. ts07 이라는 테이블스페이스를 사이즈 5m 로 생성하고, read only 로 상태를 변경하시오.
-- 테이블스페이스 생성
drop tablespace ts07 including contents and datafiles;
SYS> create tablespace ts07
datafile '/home/oracle/ts07.dbf' size 5m;
Tablespace created.
-- 테이블스페이스를 read only 로 변경
SYS> alter tablespace ts07 read only;
Tablespace altered.
SYS> select t.name, d.enabled, d.status
from v$tablespace t, v$datafile d
where t.ts# = d.ts#;
✍🏻문제 ts07 테이블 스페이스 다시 reade write로 변경하기
SYS> alter tablespace ts07 read write; SYS> select t.name, d.enabled, d.status from v$tablespace t, v$datafile d where t.ts# = d.ts#; NAME ENABLED STATUS ------------------------------ ---------- ------- TS07 READ WRITE ONLINE
✍🏻문제 scott으로 접속해서 ts07 테이블 스페이스에 emp 테이블과 똑같은 테이블을 생성하는데 이름을 emp100으로 생성
SCOTT @ orcl > create table emp100 tablespace ts07 as select * from emp; Table created. SCOTT @ orcl > select table_name, tablespace_name 2 from user_tables; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ DEPT USERS EMP USERS BONUS USERS SALGRADE USERS EMP10 USERS EMP100 TS07 6 rows selected. SCOTT @ orcl > save table.sql Created file table.sql SCOTT @ orcl > @table.sql
✍🏻문제 ts07 테이블 스페이스 다시 reade only로 변경하기
SYS> alter tablespace ts07 read only;
✍🏻문제 scott유저에서 emp100 테이블 월급을 모두 0으로 변경
update emp100 set sal=0;
✍🏻문제 scott유저에서 emp100 테이블을 drop해보기
SCOTT @ orcl > update emp100 set sal=0; 2 update emp100 * ERROR at line 1: ORA-00372: file 10 cannot be modified at this time ORA-01110: data file 10: '/home/oracle/ts07.dbf' SCOTT @ orcl > drop table emp100; Table dropped.✅ drop 하면 시스템 테이블 스페이스의 데이터 사전에서 emp100테이블에 대한 정보만 지우기 때문에 drop은 된다.
-> ts07을 다시 read write로 돌려놓기
offline 실습
💡 테이블스페이스를 offline 시킨다는 것은 테이블 스페이스에 존재하는 모든 데이터에 대한 엑세스 자체가 불가능해지게 하는 것이다.
- ts07 테이블 스페이스에 emp300 테이블을 생성하고 ts07 테이블 스페이스를 offlice 시키기. 그리고 emp300 table을 select 해보기.
1. emp300 만들기
SCOTT @ orcl > create table emp300
tablespace ts07
as
select * from emp;2. ts07 을 offline 시키기
-- offline시키기
SYS> alter tablespace ts07 offline3. emp300 select 해보기
SCOTT @ orcl > select * from emp300;
select * from emp300
*
ERROR at line 1:
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/home/oracle/ts07.dbf'✅ 다시 ts07의 상태를 online으로 변경
SYS> alter tablespace ts07 online;
SYS> select t.name, d.enabled, d.status
from v$tablespace t, v$datafile d
where t.ts# = d.ts#;
SYS @ orcl > save status.sql
Created file status.sql
SYS @ orcl > @status.sql
NAME ENABLED STATUS
------------------------------ ---------- -------
TS07 READ WRITE ONLINE 테이블 스페이스 offline 옵션중, immediate가 무엇인가?
SYS> alter tablespace ts07 offline immediate;✅ immediate를 안붙이고 offline 시키면 ts07 테이블 스페이스에 대한 메모리의 변경사항을 check point를 일으켜서 다 내려쓰고 offline 시킨다. 만약 immediate를 붙이면 check point를 일으키지 않고 offline시킨다.
db buffer cache -> 여기 SGA
3000 -> 0
user example ts07 -> 여기 data file들
3000
인데 offline까지 하면 ts07이 0으로 바뀐 후에 오프라인이 되지만 immediate까지 쓰면 0이 아닌 3000으로 남긴채 그냥 바로 offline이 된다. 나중에 3000일거 알면서 수행해야한다. 리커버리 해야함
⭐ 중요 정리 내용 ⭐ - 테이블 스페이스의 속성
- 테이블 스페이스의 종류 3가지
permanent : 영구히 저장할 데이터 temporary : 정렬작업 수행하는 공간 undo : 롤백 작업을 위한 데이터 저장하는 공간
- 테이블 스페이스의 상태 4가지
read write : 데이터 읽고 쓸 수 있는 상태 read only : 데이터 읽기만 할 수 있는 상태 online : 데이터 엑세스가 가능한 상태. 이거여야 리드온리, 리드라이트 가능 offline : 데이터 엑세스가 불가능한 상태.
- 테이블 스페이스의 공간 관리 2가지 (ocp 문제 많다)
* free space의 공간 관리 방식 1. dictionary 관리 방식 - system 테이블 스페이스의 data dictionary에서 비어있는 공간에 대한 정보를 얻는 방식. 이거 하면 i/o 많아져서 안좋다. 2. local 관리 방식 - 데이터 입력할 때 비어있는 공간에 대한 정보를 로컬 테이블 스페이스의 데이터 파일 헤더에 비트맵 정보로 얻는 방식 * sagment space management 방식 (OCP 시험 도배) 1. auto 2. manual
✅ 세그먼트 -> 테이블 , 인덱스
✅ 왼쪽 그림에서 가장 아래에 있는 데이터 블록이 오른쪽 그림이다. 저기서 사용 가능 영역이 pct free 라고 한다. (퍼센테이지의 약자)
SCOTT @ orcl > select table_name, pct_free
2 from user_tables;
TABLE_NAME PCT_FREE
------------------------------ ----------
DEPT 10
EMP 10
BONUS 10
SALGRADE 10
EMP10 10
EMP300 10💡 pct free 영역은 update를 위한 블럭의 공간에 대한 퍼센트 (default가 10%)
create table emp700
( empno number(10),
ename varchar2(20) )
pctfree 30;
--이렇게 해주면 기본 10% 아니면 정해준 퍼센트. 이렇게 해주는건 뭔가 업데이트가
--자주 일어나서 row 마이그레이션이 자주일어난다 하면 크게 늘려준다. 🤔 pct free영역이 클수록 장점과 단점
장점: row migration ↓
단점: data의 공간 낭비가 발생할 수 있다. tablespace가 금방 꽉 찰 수 있다.
❗ 그럼 pctfree 어떻게 얼마나 크게 만들어야하는데???????
manual: pce free를 dba가 직접 지정해주어야 한다.auto: 오라클이 알아서 자동으로 관리 ! -> 오라클이 참 자랑스러워해서 시험문제에 많다.
가장 이상적으로 만드는 방법
create tablespace ts22 datafile '/home/oracle/ts22.dbf' size 10m extent management local segment space management auto;✅ 위처럼 만들지 않고 매뉴얼로 만든다면 pct free를 직접 dba가 지정해주어야 한다.
SYS> create tablespace ts23 datafile '/home/oracle/ts23.dbf' size 10m extent management local segment space management manual; --manual로 만들었다면 SCOTT> create table emp23 (empno number(10), ename varchar2(10) ) tablespace ts23 pctfree20; -- 테이블 만들 때 이거 꼭 써줘야한다.
row migration 향상에 대한 실습
💡 emp 테이블에 row migration 현상이 발생했는지 확인하는 방법
- 확인하기 위한 테이블 생성
SQL> @?/rdbms/admin/utlchain.sql -- 이거 돌려라.
SQL> desc chained_rows ✅ 설명: chained_rows 에 row migration 이 발생한 데이터가 입력됩니다.
- emp 테이블을 분석한다. -> emp 테이블에 row migration현상이 일어났는지 검사
SQL> analyze table emp list chained rows;- 분석된 결과를 확인한다.
SQL> select count(*) from chained_rows; - row migration 이 발생하게끔 update 수행
SQL> alter table emp
modify job varchar2(100);
SQL> update emp
set job='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';- emp 테이블을 분석한다.
SQL> analyze table emp list chained rows;- 분석된 결과를 확인한다.
SQL> select count(*) from chained_rows; ❗ ts23 테이블 스페이스를 segment space management 를 manual 로 생성합니다.
SQL> create tablespace ts23
datafile '/home/oracle/ts23.dbf' size 10m
extent management local
segment space management manual; - demobld 를 돌린다.
demobld.sql 을 열어서 emp 테이블의 테이블 스페이스를 ts23으로 변경합니다.
alter session set nls_Date_format='RR/MM/DD';
drop table emp;
drop table dept;
CREATE TABLE DEPT
(DEPTNO number(10),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) ,
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) )
tablespace ts23;
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'81-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'81-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'81-05-09',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'81-04-01',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'81-09-10',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'81-02-11',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'81-08-21',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'81-12-11',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'81-02-23',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'81-12-11',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'80-12-09',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'82-12-22',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'83-01-15',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'82-01-11',1300,NULL,10);
commit;
SCOTT @ orcl > @demobld.sql
SCOTT @ orcl > select table_name, tablespace_name
2 from user_tables
3 where table_name='EMP';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP TS23- emp 테이블의 pctfree 를 1로 변경한다.
alter table emp pctfree 1; 9. row migration 이 발생하겠금 update 수행
SQL> alter table emp
modify job varchar2(4000);
SQL> update emp
set job='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';10. emp 테이블을 분석한다.
SQL> analyze table emp list chained rows; 11. 분석된 결과를 본다.
SQL> select count(*) from chained_rows; 
SCOTT @ orcl > select * from chained_rows;
OWNER_NAME TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME HEAD_ROWID ANALYZE_
------------------------------ ------------------ --------
SCOTT EMP
N/A AAASRiAAMAAAACBAAK 23/09/19
SCOTT EMP
N/A AAASRiAAMAAAACBAAL 23/09/19
OWNER_NAME TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME HEAD_ROWID ANALYZE_
------------------------------ ------------------ --------
SCOTT EMP
N/A AAASRiAAMAAAACBAAM 23/09/19
SCOTT EMP
OWNER_NAME TABLE_NAME
------------------------------ ------------------------------
CLUSTER_NAME PARTITION_NAME
------------------------------ ------------------------------
SUBPARTITION_NAME HEAD_ROWID ANALYZE_
------------------------------ ------------------ --------
N/A AAASRiAAMAAAACBAAN 23/09/19테이블 스페이스의 생성 스크립트 뽑아내기(p7-14)
운영 db ---------------------------> 테스트 db✅ 운영 디비와 똑같이 테스트 db에도 테이블 스페이스를 똑같이 구성해야하고 테이블도 똑같이 만들어주어야 하는 db 작업이 있다.
관련 실습 현재 데이터베이스에 생성된 테이블 스페이스를 생성하는 생성 스크립트를 추출하기
SYS> set long 50000 --> sqlplus 에서 ddl script 가 잘리지 않게 설정함 SYS> set pages 4000 SYS> set lines 3000 SYS> select dbms_metadata.get_ddl('TABLESPACE', tablespace_name) from dba_tablespaces;
CREATE TABLESPACE "SYSTEM" DATAFILE
SIZE 524288000
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M,
SIZE 104857600,
SIZE 104857600,
'/home/oracle/system02.dbf' SIZE 104857600
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEM
ENT MANUAL
ALTER DATABASE DATAFILE
'+DATA/orcl/datafile/system.256.796857621' RESIZE 1426063360
ALTER DATABASE DATAFILE
'+DATA/orcl/datafile/system.269.1145464533' RESIZE 20971520
ALTER DATABASE DATAFILE
'+DATA/orcl/datafile/system.270.1145464597' RESIZE 20971520
ALTER DATABASE DATAFILE
'/home/oracle/system02.dbf' RESIZE 209715200;
CREATE TABLESPACE "SYSAUX" DATAFILE
SIZE 419430400
AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M,
SIZE 104857600,
'/home/oracle/sysaux02.dbf' SIZE 104857600
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEM
ENT AUTO
ALTER DATABASE DATAFILE
'+DATA/orcl/datafile/sysaux.257.796857623' RESIZE 555745280
ALTER DATABASE DATAFILE
'+DATA/orcl/datafile/sysaux.267.1145464529' RESIZE 20971520
ALTER DATABASE DATAFILE
'/home/oracle/sysaux02.dbf' RESIZE 20971520;
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
SIZE 26214400
AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
ALTER DATABASE DATAFILE
'+DATA/orcl/datafile/undotbs1.258.796857625' RESIZE 104857600;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
SIZE 128974848
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
CREATE TABLESPACE "USERS" DATAFILE
SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M,
SIZE 104857600
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEM
ENT AUTO;
CREATE TABLESPACE "EXAMPLE" DATAFILE
SIZE 104857600
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M,
'/home/oracle/example02.dbf' SIZE 104857600
NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEM
ENT AUTO;
CREATE TABLESPACE "TS07" DATAFILE
'/home/oracle/ts07.dbf' SIZE 5242880
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEM
ENT AUTO;
CREATE TABLESPACE "TS04" DATAFILE
'/home/oracle/ts04.dbf' SIZE 104857600
AUTOEXTEND ON NEXT 10485760 MAXSIZE 1073741824
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEM
ENT AUTO;
CREATE TABLESPACE "TS22" DATAFILE
'/home/oracle/ts22.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEM
ENT AUTO;
CREATE TABLESPACE "TS23" DATAFILE
'/home/oracle/ts23.dbf' SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEM
ENT MANUAL;문제 scott 유저가 가지고있는 테이블 생성 스크립트를 모두 뽑아내보자 !
SCOTT> select dbms_metadata.get_ddl('TABLE', table_name) || ';' from dba_tables where owner='SCOTT';
CREATE TABLE "SCOTT"."BONUS"
( "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"SAL" NUMBER,
"COMM" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
;
CREATE TABLE "SCOTT"."SALGRADE"
( "GRADE" NUMBER,
"LOSAL" NUMBER,
"HISAL" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
;
CREATE TABLE "SCOTT"."EMP10"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
;
CREATE TABLE "SCOTT"."EMP300"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS07"
;
CREATE TABLE "SCOTT"."CHAINED_ROWS"
( "OWNER_NAME" VARCHAR2(30),
"TABLE_NAME" VARCHAR2(30),
"CLUSTER_NAME" VARCHAR2(30),
"PARTITION_NAME" VARCHAR2(30),
"SUBPARTITION_NAME" VARCHAR2(30),
"HEAD_ROWID" ROWID,
"ANALYZE_TIMESTAMP" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
;
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(10,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(4000),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 1 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS23"
;문제 위에서 출력되는 결과를 a.sql로 저장하기
SCOTT > spool on
SCOTT > spool a.sql
SCOTT > set long 5000
SCOTT > select dbms_metadata.get_ddl('TABLE', table_name) || ';'
from dba_tables
where owner='SCOTT';
SCOTT > spool off
--------- sql develper에서 수행 ---------
alter system flush shared_pool;
set long 50000
select dbms_metadata.get_ddl('TABLE', table_name) || ';'
from dba_tables
where owner='SCOTT';
select table_name
from dba_tables
where owner='SCOTT';다잡고 익스포트 > 형식은 text > 저장
테이블스페이스 삭제 (p.7-16)
SQL> drop tablespace ts01 including contents and datafiles;✅ including contents and datafiles; 를 써주어야 os에서 만들어진 data file까지 같이 삭제가 된다!
✔️ table space 뭐있는지 확인해보기
select tablespace_name, file_name
from dba_data_files;오늘의 마지막 문제1. ts로 시작하는 테이블 스페이스 모두 drop하기
SYS> drop tablespace ts07 including contents and datafiles; SYS> drop tablespace ts22 including contents and datafiles; SYS> drop tablespace ts23 including contents and datafiles; SYS> drop tablespace ts04 including contents and datafiles;
오늘의 마지막 문제2. OCP 문제 workshop 16번 tablespace에 대한 문제(ASSM)
Slow and steady wins the race.