[10/16] 실습 - 3 resumable space allocation

참조 http://cafe.naver.com/ocmkorea/3220

==============================DBA session================================

SYS@orcl>create tablespace resum_test

  2  datafile '/u01/app/oracle/oradata/orcl/resum_test01.dbf' size 1m;

Tablespace created.

create user u_resum identified by oracle

default tablespace resum_test

temporary tablespace temp

  4  quota unlimited on resum_test;

User created.

SYS@orcl>grant connect, resource, resumable to u_resum;

Grant succeeded.

SYS@orcl>grant all on sh.sales to u_resum;

Grant succeeded.

SYS@orcl>create or replace trigger logon_set_resumable

  2  after logon

  3  on scott.schema

  4  begin

  5  execute immediate 'alter session enable resumable timeout 1200';

  6  end;

  7  /

Trigger created.

===========================user session========================

U_RESUM@orcl>create table resum_sales(

  2  prod_id number,

  3  cust_id number,

  4  time_id date,

  5  channel_id number,

  6  promo_id number,

  7  quantity_sold number(10,2),

  8  amount_sold number(10,2));

Table created.

U_RESUM@orcl>alter session enable resumable;

Session altered.

U_RESUM@orcl>insert into resum_sales

  2  select * from sh.sales;

계속 기다리는 상태가 됨

-------------------------Alert Log-----------------------------

statement in resumable session 'User U_RESUM(95), Session 22, Instance 1' was suspended due to

    ORA-01653: unable to extend table U_RESUM.RESUM_SALES by 8 in tablespace RESUM_TEST

-------------------------------------------------------------

-----------------------------DBA SESSION----------------------

SYS@orcl>select decode(message_level,5,'WARNING',1,'CRITICAL') alert_level,

  2  reason from dba_outstanding_alerts

  3  where reason like '%resumable%';

ALERT_LE

--------

REASON

------------------------------------------------------------------------------------------------------------------------------------------------------

WARNING

Operation on resumable session User U_RESUM(95), Session 22, Instance 1 session id 22 suspended because of errors in tablespace RESUM_TEST. Error mess

age is ORA-01653: unable to extend table U_RESUM.RESUM_SALES by 8 in tablespace RESUM_TEST

SYS@orcl>conn u_resum/oracle

Connected.

U_RESUM@orcl>select session_id, suspend_time, name, error_msg from user_resumable;

SESSION_ID SUSPEND_TIME

---------- --------------------

NAME

------------------------------------------------------------------------------------------------------------------------------------------------------

ERROR_MSG

------------------------------------------------------------------------------------------------------------------------------------------------------

        22 10/16/15 12:20:27

User U_RESUM(95), Session 22, Instance 1

ORA-01653: unable to extend table U_RESUM.RESUM_SALES by 8 in tablespace RESUM_TEST

-------------------------------DBA 세션 문제 해결------------------------------------

1. 공간 부족일 경우

alter tablespace resum_test

  2  add datafile '/u01/app/oracle/oradata/orcl/resum_test02.dbf' size 5m

  3  ;

Tablespace altered.

add datafile '/u01/app/oracle/oradata/orcl/resum_test02.dbf' size 5m

Fri Oct 16 12:25:51 2015

statement in resumable session 'User U_RESUM(95), Session 22, Instance 1' was resumed

statement in resumable session 'User U_RESUM(95), Session 22, Instance 1' was suspended due to

    ORA-01653: unable to extend table U_RESUM.RESUM_SALES by 128 in tablespace RESUM_TEST

이걸로는 해결 안됨

SYS@orcl>ed

Wrote file afiedt.buf

  1  alter tablespace resum_test

  2  add datafile '/u01/app/oracle/oradata/orcl/resum_test03.dbf' size 5m

  3* autoextend on next 1m maxsize 100m

  4  /

Tablespace altered.

add datafile '/u01/app/oracle/oradata/orcl/resum_test03.dbf' size 5m

autoextend on next 1m maxsize 100m

Fri Oct 16 12:26:52 2015

statement in resumable session 'User U_RESUM(95), Session 22, Instance 1' was resumed

Fri Oct 16 12:26:53 2015

Thread 1 advanced to log sequence 26 (LGWR switch)

  Current log# 2 seq# 26 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log

  Current log# 2 seq# 26 mem# 1: /u01/app/oracle/oradata/orcl/redo02_b.log

Fri Oct 16 12:26:53 2015

Archived Log entry 142 added for thread 1 sequence 25 ID 0x54b08c25 dest 1:

----------------------------------USER SESSION---------------------------------

U_RESUM@orcl>insert into resum_sales

  2  select * from sh.sales;

918843 rows created.

작업이 완료된걸 확인할 수 있다.

-------------------------------DBA 문제 해결 방법----------------------------

2. extent 부족일 경우는

alter table XXX storage ( maxextents 100); 문제 해결될떄까지 추가시도

이 원인은 오직 딕셔너리 관리 방식 테이블 스페이스에서만 유효함

3. 사용자 Quota 부족시

alter user u_resum qouta nM on tablespace_name;

----------------------------------DBA SESSION------------------------------

select sid, event from v$session_wait;

내용중 해당 유저에 대한 이벤트로 다음 내용 추가됨

statement suspended wait error to be cleared

-------------------------------USER SESSION--------------------------

문제 해결 후 바로 정상 처리 완료

insert into resum_sales

select * from sh.sales;

alter session disable resumable;

-------------------------------DBA SESSION--------------------------

resumable 트랜잭션 강제 종료

execute dbms_resumable.abort(sid#); <- from dba_resumable or v$session 으로 조회