개발은 너무해

사용자의 패스워드 주기를 알고 싶을 때 쿼리

사용자에 적용된 profile 확인

프로파일에 적용된 패스워드 권한 확인

PASSWORD_LIFE_TIME:패스워드를 변경해야하는 주기

PASSWORD_REUSE_TIME:패스워드 변경시 이전 패스워드를 재사용할 수 있는 기간(즉, 해당 기간이 지나야 재사용 가능)

아래와 같은 테이블이 존재한다.

SQL> select name, reg_date from temp_food_info;

여기서 우리는 등록날짜가 4/6~4/7 데이터를 산출 할 것이다.

SQL> select *

from temp_food_info

where reg_date between to_date('20220406','yyyymmdd') and to_date('20220407','yyyymmdd')

;

결과값

등록일이 4/7 임에도 불구하고 chicken 이 누락되었다.

이유는 날짜만 지정하였을 경우 시간대는 00:00:00으로 인식하기 때문에 4/7 00:00:00 이후 데이터는 조회가 되지 않는다.

그러므로 2022/04/07 00:00:01 에 등록된 chicken은 누락이 된 것이였다.

해결방법은 간단하다. 날짜 조건을 시간까지 포함 또는 그 이상의 날짜를 넣자. 

(1) 시간포함

where reg_date between to_date('2022/04/06 00:00:00','yyyy/mm/dd hh24:mi:ss')

and to_date('2022/04/07 23:59:59','yyyy/mm/dd hh24:mi:ss')

(2) 그 이상의 날짜

where reg_date >= to_date('20220406','yyyymmdd')

and reg_date < to_date('20220408','yyyymmdd')

간과하지 말고 항상 주의를 기울어야 겠다.

(1) 생성

SQL> create user scott identified by "tiger";

ERROR at line 1:

ORA-65096: invalid common user or role name

SQL> create user "C##SCOTT" identified by "tiger";

-> 12c부터 바뀌 C##을 붙여줘야한다고 함. 근데 저렇게 생성하면 C##도 사용자명에 포함되버리는데 왜 저렇게 했을까나?

or

SQL> alter session set "_ORACLE_SCRIPT"=true;

SQL> create user scott identified by "tiger";

(2) 삭제

SQL> drop user scott [CASCADE];

여기서 ORA-28014: cannot drop administrative user or role

이런 에러가 발생된다면 _ORACLE_SCRIPT가 true로 설정 되어 있는지 확인해보자.

SQL> show parameter _oracle_script;

_ORACLE_SCRIPT 파라미터 값이 왜 생겼는지 의문이다.

은근 불편할뿐더러 이 파라미터 존재를 모른다면 맨땅에 헤딩하며 시간만 낭비할 것으로 보인다.

필자도 역시 짜증이 밀려온다 ㅎㅎ

SQL> select * from v$diag_info where name = 'Diag Trace';

현재 서버

shmmax=1g

shmseg=120

SGA = 48G

신규서버

shmmax=1g

shmseg=120

SGA = 110G 위로 올라가지 않음.

왜 그런 것인지를 찾아봄.

shmmax 할당 : SGA 보다 크거나 같게 설정하는 것을 권장한다고 함.

현재 알아본 바로는,

1. sga 값보다 shmmax를 낮게 설정하면, 여러개의 shared memory가 뜨는 것을 볼 수 있다.

 - ipcs -ma 명령어로 SEGSZ 값을 합산하면 대략적으로 sga값과 동일하게 나온다.

2. sga 값보다 shmmax를 높게 설정하면, 한개의 shared memory가 뜨는 것을 볼 수 있다.

의문. sga 값보다 shmmax를 낮게 설정하였는데, 에러가 발생하였다.

       - ORA-27123: unable to attach to shared memory segment

      위 1번에 의하면 여러개의 shared memory가 떠야하는 것이 아닌가?

답변추측. shmseg 값이 현재 120으로 설정되어있다. shared memory가 120개가 초과하여 발생하는 문제 같다.

          sga 가 48g로 설정되어 있고 shmmax 가 1기가로 설정되어 있었을 때, 53개의 메모리가 떴다.

          그러면 192g로 설정을 하려고 하니 120개를 초과하여 발생하는 문제 인 것 같다.

이를 검증하기 위해서는, 아래와 같이 한번 해보자

1. shmmax 에 값을 sga 값만큼 올려주고, shared memory 개수를 확인.

2. shmmax 값은 그대로 두고 shmseg 값만 올려주고 shared memory 개수를 확인.

설명에 의하면 아래와 같으니 내 추측이 맞을 것이라 봄.

커널이 공유메모리(오라클이 SGA로 사용할) 할 당 방법

 1. 공유메모리로 사용할 물리적 메모리가 충분할 경우.

  -> 하나의 세그먼트에 전체 SGA 할당

 2. 하나의 세그먼트에 다 할당할 수 없는 경우

  -> 연속된 여러 세그먼트로 분산시켜 할당

 3. 두번째 방법도 여의치 않을 경우

  -> 연속되지 않은 여러 세그먼트에 분산시켜 할당

* SGA내 fixed Area 부분은 반드시 전체가 하나의 세그먼트에 할당되어야 함.

=====================================================================================================================

테스트 결과는 맞게 나옴.

다만, 커널값을 shmmax, shmseg 등 설정값을 늘려주었다고 하더라도,

pfile에 또 하나의 값을 변경해줘야지 DB가 startup 될 수 있다.

만약 그렇지 않으면, ORA-00064 에러가 난다.(ORA-00064: object is too large to allocate on this OS(1,4086000))

pfile에 추가할 파라미터, _ksmg_granule_size=33554432(32M)

- default = 16777216(16M)

* granule size 확인

select name, bytes/1024/1024 "size" from v$sgainfo where upper(name) like '%GRANULE%'

NAME                                                                   size

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

Granule Size                                                            32

Granule(메모리 할당 단위)

 - SGA 메모리는 연속적인 메모리 조각(Chunk) 을 뜻하는 Granule 이란 단위로 할당된다.

 - Granule의 크기는 SGA 총 크기에 의해 결정된다. SGA의 크기가 1GB 이하라면 Granule의 크기는 4MB, 1GB 이상이라면 Granule 크기는 16MB 가 된다.

 - SGA의 각 컴포넌트들에 쓰여지는 Granule size는 v$sgainfo 로 조회가 가능하다.

drop table temp_pgheo_external;

drop directory TEST;

create directory TESTas '/data16/tmp';

create table temp_pgheo_external

(server_date varchar2(14)

,code1 varchar2(20)

,code2 varchar2(20)

,code3 varchar2(20)

,code4 varchar2(20)

,code5 varchar2(20)  

)

organization external

(

    type oracle_loader

    default directory BDUMP

    access parameters(

                        --recodrds fixed 63

                        records delimited by newline

                        fields terminated by ","  --필드를 구분할 수 있는 구분자 지정

(server_date varchar2(14)

,code1 varchar2(20)

,code2 varchar2(20)

,code3 varchar2(20)

,code4 varchar2(20)

,code5 varchar2(20)  

)

                     )

    location('external_01.txt','external_02.txt','external_03.txt','external_04.txt')

)

reject limit unlimited

;

데이터가 계속 0 row로 뿌려주고 있어, 명령어가 잘못되었는 줄 알고 한참을 찾아보고 수정했더니,

결론은 데이터 포맷이 맞지 않아 발생된 문제였음.

컬럼에 따라 하나라도 어긋나면 작동이 안되는 것 같다.

v$log 가 처리되는 순서(?)를 보고자 함..

현재 대량 데이터를 삭제중에 있다.

select a.group#, member, bytes/1024/1024, a.status, archived

from

(select * from v$log) a,

(select * From v$logfile) b

where a.group#=b.group#

order by group#, member

;

위 쿼리를 지속적으로 모니터링을 해보았다.

log switch가 발생하면 alert 파일에 기록이 남고 status는 active 상태로 변환. 

status가 inactive 로 변경되면, archive 파일로 떨구는 작업을 진행하게 된다.

이후 archived 컬럼은 NO에서 YES로 변경되면서 다음 log switch가 발생할 때 까지 기다린다.

archive 파일로 떨구는 작업보다 log switch가 더 빠르면 alert 파일에는 아래와 같은 문구가 나온다.

All online logs needed archiving

현재 데이터의 rowid가 dml 작업시 변화가 있을까하여 몇가지 테스트를 해보았다.

* TEMP 테이블 생성

create table temp_rid_test tablespace users nologging as

select 1 no,'aaaaa' depth from dual union all

select 2,'bbbb' from dual union all

select 3,'xxxxx' from dual union all

select 4,'ddddd' from dual union all

select 5,'hhhha' from dual union all

select 6,'agghha' from dual

;

* 현재 rowid

select rowid, no, depth from temp_rid_test;

* update

update temp_rid_test set no=7 where no=1;

commit;

select rowid, no, depth from temp_rid_test;

* delete

delete from temp_rid_test where no=7;

commit;

select rowid, no, depth from temp_rid_test;

* update(특정로우에 대한 큰용량 byte로 갱신) - 2000byte

alter table temp_rid_test modify depth varchar2(2000);

select lengthb('AAA......생략')

from dual  --1710

;

update temp_rid_test

set depth = 'AAA......생략'

where no = 6

;

commit;

select rowid, no, depth from temp_rid_test;

* update(특정로우에 대한 큰용량 byte로 갱신) - 4000byte

alter table temp_rid_test modify depth varchar2(4000);

select lengthb('AAA......생략')

from dual  --3478

;

update temp_rid_test

set depth = 'AAA......생략'

where no = 6

;

commit;

select rowid, no, depth from temp_rid_test;

dml 작업시 rowid의 변화는 없었다.

문득 생각났던 부분을 테스트 해본 결과이다. 중요하지는 않지만 궁금해서...

어이없는 궁금증이였던가? ㅋㅋ

1. archive mode 가 아니면 hot backup을 할 수가 없다.

SQL> alter tablespace system begin backup;

alter tablespace system begin backup

*

ERROR at line 1:

ORA-01123: cannot start online backup; media recovery not enabled

SQL>

2. 핫백업시 다시 begin backup 명령어 먹는가?

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> alter tablespace system begin backup;

alter tablespace system begin backup

*

ERROR at line 1:

ORA-01146: cannot start online backup - file 1 is already in backup

ORA-01110: data file 1: '/home/oradata/PROD/data1/system01.bdf'

SQL>

3. 10g부터 바뀐 hotbackup 명령어 : 테이블스페이스마다 begin backup을 해줄 필요가 없다.

SQL> alter database begin backup;

Database altered.

SQL> alter database end backup;

Database altered.

SQL>

4. dba_tables, user_tables, all_tables 에 있는 extents 는 테이블들이 설정되어 있는 값들이다.

   테스트를 해보니 값에 대한 변화가 없었다.

몇 년전 오라클 교육을 받은 이후로 처음 윈도우에 오라클을 설치해보았다.

DB 설치 시 윈도우와 리눅스가 초기  설정이 다소 다르다는 것을 잠시 잊고 있었다.

윈도우에 생성 시 초기 해야할 작업.

1.  서비스 등록 (oradim)

D:\PROD\script>oradim -new -sid PROD -startmode A

인스턴스가 생성되었습니다.

D:\PROD\script>set oroacle_sid=PROD

D:\PROD\script>sqlplus "/as sysdba"

cf.

  oradim -new -sid PROD : 서비스 등록

  oradim -delete -sid PROD : 서비스 해제

  startmode A : 자동모드로 변환

2. shutdown 또는 startup 시 별도의 인증없이 DB 관리자 역할을 수행  (orapwd)

  orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora password=1234

cf.

  pfile 또는 spfile등 리눅스에서는 $ORACLE_HOME/dbs에 종속시켜야 하지만,

  윈도우 에서는 %ORACLE_HOME%\database에 종속시켜야한다.

3. 만약 위 1번의 작업을 수행하지 않는다면 아래와 같은 에러가 나올 것이다.

  ERROR: ORA-12560: TNS:프로토콜 어댑터 오류

SQL> !lsnrctl stop listener

...

..

.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 2550136832 bytes

Fixed Size                  2022920 bytes

Variable Size             536871416 bytes

Database Buffers         1996488704 bytes

Redo Buffers               14753792 bytes

Database mounted.

SQL> alter system enable restricted session;

System altered.

SQL> alter system set job_queue_processes=0;

System altered.

SQL> alter system set aq_tm_processes=0;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set internal_use KO16KSC5601;

Database altered.

SQL> shutdown immediate;

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2550136832 bytes

Fixed Size                  2022920 bytes

Variable Size             536871416 bytes

Database Buffers         1996488704 bytes

Redo Buffers               14753792 bytes

Database mounted.

Database opened.

SQL> select * from props$ where name = 'NLS_CHARACTERSET';

NAME

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

VALUE$

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

COMMENT$

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

NLS_CHARACTERSET

KO16KSC5601

Character set

SQL> !lsnrctl start listener

...

..

.

union 과 union all 의 차이점을 말하라하면 중복이 제거가 되느냐 되지 않느냐의 차이로 알고 있었다.

그러나 minus는?????

단순히 로우에 대한 빼기로만 알고 있었기에 무심코 사용했다.

반전...

결론은 minus를 하면 중복이 제거가 된 값들이 출력된다.

with car as

(

    select 'car' flag from dual union all

    select 'car' flag from dual union all

    select 'air' flag from dual union all

    select 'air' flag from dual union all

    select 'air' flag from dual union all

    select 'air' flag from dual union all

    select 'air' flag from dual union all

    select 'car' flag from dual union all

    select 'car' flag from dual

),

air as

(

    select 'car' flag from dual

)

select flag from car

minus

select flag from air

;

flag

-----

air

한건만이 출력된다...나에게 있어 굉장한 충격이었다.