오라클 파라미터 뷰, 파일 관련 정리 spfile, v$parameter, v$parameter2, v$spparameter 비교

OS환경 : Oracle Linux 5.8 (64bit)

DB 환경 : Oracle Database 10.2.0.5

방법 : 오라클 파라미터 뷰, 파일 관련 정리 spfile, v$parameter, v$parameter2, v$spparameter 비교

scope 옵션 정리

scope=memory : 현재 Open된 db에서만 영향을 미치며 db가 재기동되면 변경 이전값으로 돌아감

scope=spfile : 변경 내용을 spfile에만 저장하고 현재 db에는 영향을 미치지 않게함

static parameter의 경우는 이 scope만이 지정가능함

즉, spfile을 사용하더라도 static parameter에 대해서는 db 운영중에 바로 값을 변경하여 재기동없이 반영하는 것은 불가능

scope=both : 변경 내용을 현재상태에도 바로 반영하고, spfile에도 반영시켜, 이후 재기동시에도 영향을 미치도록 함

v$parameter, v$parameter2, v$spparameter 뷰의 의미

v$parameter

현재 세션에 유효한 초기화 매개 변수에 대한 정보를 표시함

새 세션은 v$system_parameter 뷰에서 파라미터 값을 상속받음

v$spparameter

spfile 의 내용에 대한 정보를 표시함 

spfile을 사용하여 인스턴스를 시작하지 않은 경우 ISSPECIFIED 열에 FALSE 값이 포함됨

v$parameter2

현재 세션에 유효한 초기화 매개 변수에 대한 정보를 표시하며 각 목록 매개 변수 값은보기에서 행으로 표시됨 

새 세션은 v$system_parameter2 뷰에서 파라미터 값을 상속받음

v$parameter, v$parameter2 주요 컬럼 설명

name : 파라미터 이름

type : 파라미터 타입에 대한 번호 유형

 1 - Boolean

 2 - String

 3 - Integer

 4 - Parameter file

 5 - Reserved

 6 - Big integer

value : 세션의 파라미터 값 (세션 내에서 수정 된 경우(alter session)) 또는 인스턴스 전체 파라미터 값

isdefault : 파라미터가 기본값 (TRUE)으로 설정되었는지 또는 파라미터 값이 파라미터 파일(false)에 지정되었는지를 나타냄

isses_modifiable : alter session로 매개 변수를 변경할 수 있는지 여부를 나타냄(true), 안되면 false

issys_modifiable : alter system을 통해 변경되는 파라미터가 언제 적용 되는가의 여부

 immediate - 파라미터 타입에 상관 없이 alter system 명령 즉시 적용됨

 deferred - 파라미터 타입에 관계 없이 alter system을 통해 변경, 변경한 파라미터는 이후 세션에 적용됨

 false - 인스턴스 시작에 spfile이 사용되지 않았다면, alter system으로 변경 할 수 없는 파라미터, 변경 사항은 이후 인스턴스에 적용됨

ismodified : 인스턴스 시작 후 파라미터가 수정되었는지 여부를 나타냄

 modified - alter session으로 수정된 파라미터

 system_mod - alter system으로 수정된 파라미터(현재 로그인 된 모든 세션 값 변경의 원인)

 false - 인스턴스 시작 이후 수정되지 않은 파라미터

isdeprecated : 파라미터가 중지되었는지(더 이상 사용되지 않는지) 여부

 true - 중지됨

 false - 중지 안됨

description : 파라미터에 대한 설명

alter system, alter session 의미

alter system

현재 사용하고 있는 데이터베이스 인스턴스를 동적으로 변경

mount 단계에서 부터 사용가능

alter session

현재 데이터베이스에 영향을 미치는 조건이나 parameter 설정

alter session으로 설정할수 있는 parameter는 v$parameter에서 isses_modifiable 컬럼을 보고 확인할수있음 

현재 session이 종료 될때까지 유효함

파라미터 적용 테스트1

alter system set 명령으로 undo_retention 파라미터 변경 테스트를 해보겠음

파라미터 파일 확인

SQL> show parameter spfile
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /oracle/app/oracle/produ
                         ct/10.2.0/db_1/dbs/spfileORCL1
                         0.ora

현재 spfile 로 기동되어 있음

기존 v$spparameter, v$parameter, v$parameter2 확인

SQL>
col name for a20
col value for a20
select name, value from v$spparameter
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- -------------------- 
undo_retention         900          
 
SQL>
select name, value from v$parameter
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- -------------------- 
undo_retention         900   
 
SQL> 
select name, value from v$parameter2
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- -------------------- 
undo_retention         900   

파라미터를 scope=memory로 변경

SQL> alter system set undo_retention=1000 scope=memory;
 
System altered.

v$spparameter, v$parameter, v$parameter2 확인

SQL>
col name for a20
col value for a20
select name, value from v$spparameter
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- -------------------- 
undo_retention         900   
 
SQL>
select name, value from v$parameter
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- -------------------- 
undo_retention         1000   
 
SQL> 
select name, value from v$parameter2
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- -------------------- 
undo_retention         1000   

v$spparameter는 변경되지 않고 v$parameter, v$parameter2 의 value 만 변경되었음

파라미터를 scope=both로 변경

SQL> alter system set undo_retention=1100 scope=both;
 
System altered.

v$spparameter, v$parameter, v$parameter2 확인

SQL>
col name for a20
col value for a20
select name, value from v$spparameter
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- --------------------
undo_retention         1100         
 
SQL>
select name, value, display_value from v$parameter
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- --------------------
undo_retention         1100         
 
SQL> 
select name, value, display_value from v$parameter2
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- --------------------
undo_retention         1100         

v$spparameter, v$parameter, v$parameter2 의 value, display_value 모두 변경되었음

파라미터를 scope=spfile로 변경

SQL> alter system set undo_retention=1200 scope=spfile;
 
System altered.

v$spparameter, v$parameter, v$parameter2 확인

SQL>
col name for a20
col value for a20
select name, value from v$spparameter
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- -------------------- 
undo_retention         1200         
 
SQL>
select name, value from v$parameter
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- --------------------
undo_retention         1100       
 
SQL> 
select name, value from v$parameter2
where name in ('undo_retention');
 
NAME             VALUE          
-------------------- --------------------
undo_retention         1100       

v$spparameter만 변경되고 v$parameter, v$parameter2 의 value 는 변경되지 않았음

재기동 이후에 v$parameter, v$parameter2에 적용되어 보임

파라미터 적용 테스트2

alter session set 명령으로 db_file_multiblock_read_count 파라미터 변경 테스트를 해보겠음

파라미터 파일 확인

SQL> show parameter spfile
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /oracle/app/oracle/produ
                         ct/10.2.0/db_1/dbs/spfileORCL1
                         0.ora

현재 spfile 로 기동되어 있음

세션1에서 v$spparameter, v$parameter, v$parameter2 확인

SQL>
col name for a40
col value for a20
select name, value from v$spparameter
where name in ('db_file_multiblock_read_count');
 
NAME                     VALUE
---------------------------------------- --------------------
db_file_multiblock_read_count         16        
 
SQL>
select name, value from v$parameter
where name in ('db_file_multiblock_read_count');
 
NAME                     VALUE
---------------------------------------- --------------------
db_file_multiblock_read_count         16    
 
SQL> 
select name, value from v$parameter2
where name in ('db_file_multiblock_read_count');
 
NAME                     VALUE
---------------------------------------- --------------------
db_file_multiblock_read_count         16    

세션1에서 scope를 붙여서 변경

SQL> alter session set db_file_multiblock_read_count=32 scope=memory;
alter session set db_file_multiblock_read_count=32 scope=memory
                                                   *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
 
SQL> alter session set db_file_multiblock_read_count=32 scope=both;
alter session set db_file_multiblock_read_count=32 scope=both
                                                   *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
 
SQL> alter session set db_file_multiblock_read_count=32 scope=spfile;
alter session set db_file_multiblock_read_count=32 scope=spfile
                                                   *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

alter session 에선 scope 명령이 불가능

세션1에서 scope를 붙이지 않고 변경

SQL> alter session set db_file_multiblock_read_count=32;
 
Session altered.

세션1에서 v$spparameter, v$parameter, v$parameter2 확인

SQL>
col name for a40
col value for a20
select name, value from v$spparameter
where name in ('db_file_multiblock_read_count');
 
NAME                     VALUE
---------------------------------------- --------------------
db_file_multiblock_read_count         16        
 
SQL>
select name, value from v$parameter
where name in ('db_file_multiblock_read_count');
 
NAME                     VALUE
---------------------------------------- --------------------
db_file_multiblock_read_count         32    
 
SQL> 
select name, value from v$parameter2
where name in ('db_file_multiblock_read_count');
 
NAME                     VALUE
---------------------------------------- --------------------
db_file_multiblock_read_count         32    
 
SQL> select name, value from v$system_parameter2
where name in ('db_file_multiblock_read_count');
 
NAME                     VALUE
---------------------------------------- --------------------
db_file_multiblock_read_count         16

spfile에 적용되는 spparameter의 값은 변경되지 않고 v$parameter, v$parameter2의 값만 변경됨

또한 본문에 작성한 내용처럼 새로운 세션은 v$system_parameter2 뷰에서 파라미터 값을 상속받음(16)

세션2를 새로 접속해서 v$spparameter, v$parameter, v$parameter2 확인

SQL>
col name for a40
col value for a20
select name, value from v$spparameter
where name in ('db_file_multiblock_read_count');
 
NAME                     VALUE
---------------------------------------- --------------------
db_file_multiblock_read_count         16        
 
SQL>
select name, value from v$parameter
where name in ('db_file_multiblock_read_count');
 
NAME                     VALUE
---------------------------------------- --------------------
db_file_multiblock_read_count         16    
 
SQL> 
select name, value from v$parameter2
where name in ('db_file_multiblock_read_count');
 
NAME                     VALUE
---------------------------------------- --------------------
db_file_multiblock_read_count         16    

새로운 세션은 32가 아닌 16이 적용되어 있음

세션 1에서 v$parameter이나 v$parameter2에 있는 ismodified 컬럼을 이용해 

변경이 일어난 파라미터 확인

SQL> select name, value, ismodified from v$parameter2 where ismodified !='FALSE';
 
NAME                     VALUE              ISMODIFIED
---------------------------------------- -------------------- ----------
db_file_multiblock_read_count         32              MODIFIED

참조 : 

http://database.sarang.net/?inc=read&aid=12528&criteria=oracle&subcrit=&id=&limit=&keyword=&page=

https://tawool.tistory.com/267

https://techgoeasy.com/difference-vsystem_parameter-vparameter/

https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2012.htm#REFRN30176

https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2014.htm#REFRN30177

https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2112.htm

http://blog.daum.net/_blog/BlogTypeView.do?blogid=0TuGj&articleno=441&categoryId=20&regdt=20150225143439

https://positivemh.tistory.com/492

http://dbcafe.co.kr/wiki/index.php/%EC%98%A4%EB%9D%BC%ED%81%B4_%ED%8C%8C%EB%9D%BC%EB%AF%B8%ED%84%B0#.ED.8C.8C.EB.9D.BC.EB.AF.B8.ED.84.B0_.ED.8C.8C.EC.9D.BC.EC.9D.98_.EC.A2.85.EB.A5.98