1. UTL_HTTP 사용하는 프로시저를 컴파일을 하였지만 다음과 같은 에러가 발생하였다.

2. 원인을 찾던 중 utl_http 가 정의(?)가 되지 않았음을 알 수 있었다. 알 수 있었던 방법은 단순하였다.

https://community.oracle.com/tech/developers/discussion/469405/problem-calling-utl-http-in-oracle-xe

$ sqlplus "/as sysdba"

SQL> desc utl_http

utl_http의 정보들이 출력이 될 것이다.

SQL> conn system/123456

SQL> desc utl_http

SQL> select * from dba_tab_privs where table_name = 'UTL_HTTP';

3. object 권한을 주면 해결이 된다.

https://stackoverflow.com/questions/27379194/grant-utl-http-permission-in-plsql

$ sqlplus "/as sysdba"

SQL> grant execute on UTL_HTTP to system;

SQL> conn system/123456

SQL> desc UTL_HTTP

SQL> select * from dba_tab_privs where table_name = 'UTL_HTTP';

4. 컴파일할 때 발생되었던 에러는 사라졌으나, 막상 프로시저를 실행시켰더니 다음과 같은 에러가 발생하였다.

5. 여기에도 ACL권한을 부여해야 한다는 것이다. 등록되어있지 않다면 등록을 해줘야한다.

SQL> select * from dba_network_acl_privileges;

SQL> select * from dba_network_acls;

등록(생성)은 이렇게 하자

삭제가 필요하다면

SQL> select * from dba_network_acl_privileges;

SQL> select * from dba_network_acls;

* 참고로 12c 부터 아래 object로 변경되었다.

dba_network_acl_privileges => DBA_HOST_ACES

dba_network_acls => DBA_HOST_ACLS

6. 프로시저가 제대로 작동하는 것을 알 수 있을 것이다.

이것은 11g부터 적용된 사항이라 하며, 10g 이하 버전은 해당사항이 없다고 한다.

다만 UTL_HTTP 권한 정도는 확인을 하자.(본 글 "3. object 권한을 주면 해결이 된다." 참고)

7. 참고 URL(더 상세하게 나와있으니 참고하자)

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=nkyle9361&logNo=220676267478

https://blog.naver.com/PostView.naver?blogId=nkyle9361&logNo=221989499061&parentCategoryNo=&categoryNo=1&viewDate=&isShowPopularPosts=false&from=postView 

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

사용자에 적용된 profile 확인

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

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

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

(1) 테스트 테이블 생성

(2) REGEXP_SUBSTR

결과

(3) REGEXP_REPLACE

결과

(4) 세로로 나열

결과

참고 : https://good4uprin.tistory.com/11

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

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 파라미터 값이 왜 생겼는지 의문이다.

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

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

DML문을 실행하려고 했는데 ORA-01950 에러가 발생한다면 해당 테이블스페이스에 insert 할수 있는 권한이 없다는 것이다.

권한을 아래와 같이 부여해 줄 수 있다.

권한을 확인 하는 방법은 

조회하였을때 해당 테이블스페이스의 계정이 등록되어 있으면 권한을 부여받은 것이다.

여기서 max_bytes 가 -1 이면 unlimited 로 부여 받은 것이고, 값이 존재하면 그 byte만큼 저장이 가능하게 부여받은 것이다.

오라클에서 제공하는 SQL developer 에서 system 또는 sys 계정에 접속하고자 할 때, 정확한 비밀번호를 입력했음에도 불구하고 접속이 안되는 경우가 있다. 

분명 sqlplus에서는 system 계정으로 매우 접속이 잘된다.

SQL> sqlplus /nolog

SQL> conn system/oracle as sysdba

왜 이런지 원인은 알 수가 없다. 문제점을 알려주는 곳을 도저히 찾을수가 없었다.

해결방안은 그냥 패스워드를 다시 만들어주면 해결된다. 너무 간단했다. ㅠㅠ

SQL> alter user system identified by "oracle";

참고로 원인을 찾아보던 중 19c 경우 기본암호가 "manager"에서  "no authentication" 로 변경되었다고 하는데,

필자는 초기 기본암호가 "oracle" 이였으며 no authentication 에 대해서는 이해가 되지 않는다. ㅠㅠ

즉, 아래 링크에 대한 글이 이해가 되지 않는다. 누군가 알려주었으면 좋겠다.

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2620296_1.html

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의 변화는 없었다.

IMP-00032: SQL statement exceeded buffer length

IMP-00008: unrecognized statement in the export file

buffer size를 늘려주어라.

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

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

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 는 테이블들이 설정되어 있는 값들이다.

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

제목과 같이 갑자기 의문이 들었다.

파일은 복사하는 중인데, 인서트는 계속 되고 있는 상황이 발생하였다.

이론적으로 생각하였을 때는 아카이브 파일을 불러와 복구가 되겠다는 생각은 들어지만..정말 그렇게 될까?ㅎㅎ

테스트를 해보자.

session 을 sys 와 scott 계정 2개를 열었다.

users 테이블스페이스를 제외한 나머지 테이블스페이스에 대해서는 미리 hotbackup을 진행 완료.

users 테이블스페이스를 남겨놓기 위해 alter database begin backup; 명령어를 사용하지 않고,

alter tablespace system begin backup; 명령어를 사용하였다.(참고로 작업환경은 윈도우에서 하였음.)

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME

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

         1 NOT ACTIVE             175887 12/09/23

         2 NOT ACTIVE             175909 12/09/23

         3 NOT ACTIVE             175923 12/09/23

         4 NOT ACTIVE                  0                       <-- users 테이블스페이스

         5 NOT ACTIVE             175940 12/09/23

파일 용량이 적어 미리 잽싸게 scott 계정에서 dml 작업을 하기 위해 스크립트를 만들어주었다.

* sequence 생성

SQL> create sequence seq_hot

  2  start with 1

  3  increment by 1

  4  nomaxvalue

  5  nocycle

  6  ;

* scott_insert.sql

insert into temp_hot_table select seq_hot.nextval,'AA',sysdate from dual connect by level <= 100000;

commit;

select count(*) cnt,max(id) max_id from temp_hot_table;

불완전 복구가 되었을 시 어느 단계까지 복구가 되는지 확인을 하기 위해, 단계별로 insert를 수행함.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME

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

         1 NOT ACTIVE             175887 12/09/23

         2 NOT ACTIVE             175909 12/09/23

         3 NOT ACTIVE             175923 12/09/23

         4 NOT ACTIVE             175961 12/09/23   <-- 백업이 완료 되었음.

         5 NOT ACTIVE             175940 12/09/23

백업은 완료가 되고, 해당 데이터 파일이 깨졌을 때의 상황을 만들자.

SQL> shutdown immediate;

데이터베이스가 닫혔습니다.

데이터베이스가 마운트 해제되었습니다.

ORACLE 인스턴스가 종료되었습니다.

SQL> $del D:\PROD\DATA2\ORADATA\USERS01.DBF

SQL> startup

ORACLE 인스턴스가 시작되었습니다.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             171969412 bytes

Database Buffers          432013312 bytes

Redo Buffers                7135232 bytes

데이터베이스가 마운트되었습니다.

ORA-01157: 데이터 4 파일을 식별 또는 잠금 할 수 없습니다- DBWR 추적 파일을 보십시오

ORA-01110: 4 데이터 파일: 'D:\PROD\DATA2\ORADATA\USERS01.DBF'

데이터 파일이 깨져 복구를 해야하는 단계이다.

SQL> alter database datafile 4 offline;

데이타베이스가 변경되었습니다.

SQL> alter database open;

데이타베이스가 변경되었습니다.

SQL> $copy d:\PROD\backup\users01.dbf d:\PROD\data2\oradata\users01.dbf  : hotbackup 한 파일로 복원하자.

        1개 파일이 복사되었습니다.

SQL> recover datafile 4

매체 복구가 완료되었습니다.    <-- 데이터가 적다보니 아카이브 파일을 만들어 지지 않아 필요가 없나보다. 바로 복구가 된다.

SQL> alter database datafile 4 online;

데이타베이스가 변경되었습니다.

SQL> conn scott/tiger

연결되었습니다.

SQL> select count(*), max(id) from temp_hot_table;

  COUNT(*)    MAX(ID)

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

    400000     400000

완전 복구가 되었다...훌륭하네!ㅎㅎ

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

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

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

pfile의 undo_management 를 MANUAL로 수정한 후 pfil로 open 한다.

SQL> startup '$ORACLE_HOME/dbs/init$ORACLE_SID.ora;

SQL> drop tablespace undotbs1 including contents and datafiles cascade constraints;

재생성한다.

SQL> create undo tablespace undotbs1 datafile '/data1/oradata/undotbs01.dbf' size 10m

   2  autoextend on next  5m maxsize 50m;

SQL> shutdown immediate;

pfile의 undo_management 를 다시 AUTO로 수정한 후 startup 한다.

PK 제거 후 삭제 해야 함.

ALTER TABLE table_name DROP PRIMARY KEY;