[ORACLE]Oracle Default Profile 확인

Database 2013.02.21 09:06 Posted by 애플자라

오라클 운영을 할때!!
신규DB설치를 하거나, DB upgrade로 작업으로
오라클 버젼이 바뀔때면 주의해야 될것이 default profile인것 같습니다.

처음에는 운영 잘하다가
나중에 profile에 의해 유저로그인이 되지 않아 서비스를 중지될수도 있기 때문입니다.
가령 패스워드 시도(FAILED_LOGIN_ATTEMPTS) 횟수라던가?  패스워드 지속시간(PASSWORD_LIFE_TIME) 이라던가?
(물론 보안과 리소스 제한이라는 장점도 있지만.. 서비스 중지보다는 중요하지 않겠죠. ㅋ)

그래서 한번 집고 넘어가려고 합니다.
오라클 버젼별 Default Profile은 어떻게 변화 되있을까요?

오라클 10g default profile

sqlplus "/as sysdba"

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT'; 

- 기존패스워드 변경 및 접속확인

SQL> alter user APLUSORA identified by "**********" ;

SQL> conn APLUSORA/**********

RESOURCE_NAME LIMIT ------------------------------ ---------------------------------------- COMPOSITE_LIMIT UNLIMITED SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED IDLE_TIME UNLIMITED CONNECT_TIME UNLIMITED PRIVATE_SGA UNLIMITED FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION NULL PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME UNLIMITED 16 rows selected.


오라클 11g default profile

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';

RESOURCE_NAME                   LIMIT  
------------------------------  ---------------------------------------- 
FAILED_LOGIN_ATTEMPTS           10 
PASSWORD_VERIFY_FUNCTION        NULL 
PASSWORD_REUSE_MAX              UNLIMITED 
PASSWORD_REUSE_TIME             UNLIMITED 
PRIVATE_SGA                     UNLIMITED 
CONNECT_TIME                    UNLIMITED 
IDLE_TIME                       UNLIMITED 
LOGICAL_READS_PER_CALL          UNLIMITED 
LOGICAL_READS_PER_SESSION       UNLIMITED 
CPU_PER_CALL                    UNLIMITED 
CPU_PER_SESSION                 UNLIMITED 
SESSIONS_PER_USER               UNLIMITED 
COMPOSITE_LIMIT                 UNLIMITED 
PASSWORD_GRACE_TIME             7 
PASSWORD_LIFE_TIME              180 
PASSWORD_LOCK_TIME              1 

16 rows selected. 


그럼 변경은 어떻게 해야될까요?
아래처럼 각 RESOURCE_NAME별로 변경이 가능하고, default profile이니까 unlimited로 설정을 했씁니다.

ALTER PROFILE DEFAULT limit PASSWORD_GRACE_TIME UNLIMITED ; 
ALTER PROFILE DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED ; 
ALTER PROFILE DEFAULT limit PASSWORD_LIFE_TIME UNLIMITED ; 
ALTER PROFILE DEFAULT limit PASSWORD_LOCK_TIME UNLIMITED ; 



여담입니다..
인터페이스 유저처럼 타시스템에서 접속하는 유저에 대해서는 꼭 profile를 생성하고 세션갯수(SESSIONS_PER_USER ) 나 IDLE_TIME등 지정하여 리소스 제한를 통한 타시스템의 영향을 줄이는 방법으로 운영하셔야 할것 같습니다.
(리소스 제한을 하기 위해서는 init parameter로 resource_limit = true 로 설정이 되어 있어야합니다.)

출처 - http://kosate.tistory.com/144

 

SQL> select resource_name,limit from dba_profiles;

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT                  UNLIMITED
SESSIONS_PER_USER                UNLIMITED
CPU_PER_SESSION                  UNLIMITED
CPU_PER_CALL                     UNLIMITED
LOGICAL_READS_PER_SESSION        UNLIMITED
LOGICAL_READS_PER_CALL           UNLIMITED
IDLE_TIME                        30
CONNECT_TIME                     UNLIMITED
PRIVATE_SGA                      UNLIMITED
FAILED_LOGIN_ATTEMPTS            3
PASSWORD_LIFE_TIME               60

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
PASSWORD_REUSE_TIME              1800
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         VERIFY_FUNCTION
PASSWORD_LOCK_TIME               .0006
PASSWORD_GRACE_TIME              UNLIMITED
COMPOSITE_LIMIT                  DEFAULT
SESSIONS_PER_USER                DEFAULT
CPU_PER_SESSION                  DEFAULT
CPU_PER_CALL                     DEFAULT
LOGICAL_READS_PER_SESSION        DEFAULT
LOGICAL_READS_PER_CALL           DEFAULT

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
IDLE_TIME                        DEFAULT
CONNECT_TIME                     DEFAULT
PRIVATE_SGA                      DEFAULT
FAILED_LOGIN_ATTEMPTS            UNLIMITED
PASSWORD_LIFE_TIME               DEFAULT
PASSWORD_REUSE_TIME              DEFAULT
PASSWORD_REUSE_MAX               DEFAULT
PASSWORD_VERIFY_FUNCTION         DEFAULT
PASSWORD_LOCK_TIME               DEFAULT
PASSWORD_GRACE_TIME              DEFAULT

댓글을 달아 주세요

[ORACLE]Tablespace별로 용량 체크하기

Database 2012.12.05 14:40 Posted by 애플자라

SELECT 
    SUBSTR(MAX(A.TABLESPACE_NAME),1,16) "Tablespace",
    --TO_CHAR(A.FILE_ID, '9999') "File ID",
    SUBSTR(MAX(A.FILE_NAME),1,43) "Data file",
    SUBSTR(MAX(A.STATUS),1,10) "Status",
    MAX(A.BYTES)/1024/1024 "TOTAL SIZE(Mb)" ,
    (MAX(A.BYTES)-NVL(SUM(B.BYTES),0))/1024/1024 "USED SIZE(Mb)",
    NVL(SUM(B.BYTES),0)/1024/1024 "FREE SIZE(Mb)",    
    --MAX(A.BLOCKS)-NVL(SUM(B.BLOCKS),0) "USED BLOCKS",
    TO_CHAR((MAX(A.BYTES)-NVL(SUM(B.BYTES),0))*100/MAX(A.BYTES),'990.99')||'%' "USED USAGE",
    --NVL(SUM(B.BLOCKS),0) "FREE BLOCKS",
    TO_CHAR(NVL(SUM(B.BYTES),0)*100/MAX(A.BYTES),'990.99')||'%' "FREE USAGE"
    --MAX(A.BLOCKS) "TOTAL BLOCKS"
FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
WHERE A.FILE_ID=B.FILE_ID(+)
--AND   A.FILE_ID IN ('6','7','8')
GROUP BY A.FILE_ID
ORDER BY 1,2;

 

 

SELECT U.TABLESPACE_NAME "테이블 스페이스"
, U.BYTES / 1048576 "크기(mb)"
, (U.BYTES - SUM(NVL(F.BYTES,0))) / 1048576 "사용됨(mb)"
, (SUM(NVL(F.BYTES,0))) / 1048576 "남음(mb)"
, TRUNC((SUM(NVL(F.BYTES,0)) / U.BYTES) * 100,2) "남은 %"
, U.FILE_NAME "저장위치"
FROM DBA_FREE_SPACE F
, DBA_DATA_FILES U
WHERE F.FILE_ID(+) = U.FILE_ID
GROUP BY U.TABLESPACE_NAME
, U.FILE_NAME
, U.BYTES
ORDER BY U.TABLESPACE_NAME;

 

 

select file_name, tablespace_name, bytes, autoextensible
  from dba_data_files
  where tablespace_name='AMAIL_DATA_TS';

댓글을 달아 주세요

[ORACLE]이메일 도메인 걸러내기

Database 2012.11.22 17:36 Posted by 애플자라

SELECT SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL))) AS DOMAIN,
COUNT(SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))) AS DOMAIN_CNTS
FROM AUTO120
WHERE WORKDAY='20121116'
AND SEQNO=5
GROUP BY SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))
HAVING COUNT(SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))) > 0
ORDER BY COUNT(SUBSTR(TO_EMAIL, INSTR(TO_EMAIL,'@')+ 1,LENGTH(RTRIM(TO_EMAIL)))) DESC

 

SELECT
DOMAIN,
COUNT(DOMAIN) AS DOMAIN_CNTS
FROM EBAD1060
WHERE POST_ID = '2008101400001'
GROUP BY DOMAIN
HAVING COUNT(DOMAIN) > 100
ORDER BY COUNT(DOMAIN) DESC

댓글을 달아 주세요

[ORACLE]전월 1일, 말일

Database 2012.11.22 14:01 Posted by 애플자라

SELECT TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE,-1)+1)-TO_CHAR(SYSDATE,'DD'),'YYYYMMDD')||TO_CHAR(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))+0.99999421,'YYYYMMDD') AS "대상기간" FROM DUAL

댓글을 달아 주세요

[ORACLE]데이터 페이징 처리방법(게시판)

Database 2012.10.11 17:00 Posted by 애플자라
SELECT B.*
FROM ( SELECT ROW_NUMBER() OVER(ORDER BY DATE DESC) RNUM,A.*
            FROM TABLE A ) B
WHERE RNUM BETWEEN 1 AND 50

댓글을 달아 주세요

[ORACLE]SELECT INSERT 속도개선, nologging

Database 2012.09.05 10:19 Posted by 애플자라

alter table "해당 테이블" nologging -> 넣을 테이블

alter session set db_file_multiblock_read_count = 128; -> 옵션

insert /*+append*/ into "해당테이블"

옵션 주면 일반 full에 16배 더 빨라져
천 만건이면..어지간한 데이터도 30분 안 쪽으로 끝남


INSERT /*+APPEND/ INTO APLUSORA.NEW_ADDRESS_20120823
(
      BUPJUNG_CODE, SIDO, SIGUNGU,
   DONG, LI, SAN,
   BUNJI_NO1, BUNJI_NO2, DORO_CODE,
   DORO_NAME, JIHA, GUNMUL_NO1,
   GUNMUL_NO2, GUNMUL_NAME, GUNMUL_NAME_DTL,
   GUNMUL_GWANRI_NO, DONG_SEQ, HANGJUNG_CODE,
   HANGJUNG_NAME, ZIP_CODE, ZIP_CODE_SEQ,
   DELIVER_NAME, UPDATE_CODE, UPDATE_DAY,
   DORO_NAME_BEFORE, SIGUNGU_GUNMUL_NAME, APARTMENT
)
SELECT /*+FULL(APLUSORA.NEW_ADDRESS_20120823@APLUS_TEST_LINK) PARALLEL(APLUSORA.NEW_ADDRESS_20120823@APLUS_TEST_LINK, 4)/
   BUPJUNG_CODE, SIDO, SIGUNGU,
   DONG, LI, SAN,
   BUNJI_NO1, BUNJI_NO2, DORO_CODE,
   DORO_NAME, JIHA, GUNMUL_NO1,
   GUNMUL_NO2, GUNMUL_NAME, GUNMUL_NAME_DTL,
   GUNMUL_GWANRI_NO, DONG_SEQ, HANGJUNG_CODE,
   HANGJUNG_NAME, ZIP_CODE, ZIP_CODE_SEQ,
   DELIVER_NAME, UPDATE_CODE, UPDATE_DAY,
   DORO_NAME_BEFORE, SIGUNGU_GUNMUL_NAME, APARTMENT
FROM APLUSORA.NEW_ADDRESS_20120823@APLUS_TEST_LINK;

이렇게 Full 태우고 parallel 옵션주면 더 빨라짐

맨 처음 alter -> alter session set -> insert /append/ select /full....이런 순서로

출처 - 하정민 님

댓글을 달아 주세요

[ORACLE]휴일감안 전/후영업일자 조회

Database 2012.09.04 10:37 Posted by 애플자라

-- 기준일자와  휴일여부를 이용하여 전/전전/전전전, 후/후후/후후후 영업일자를 조회하기위한 sql

-- 인터넷 무쟈게 돌아댕겨도 전/후영업일자 조회하는 sql이 없어서 함. 만들어봤어요(내가 필요해서..ㅎㅎ)

-- dd: 특정일자, ck:2:비영업일, 1:영업일

with cal_t as(select '20110901' dd, '1' ck from dual union all
select '20110902' dd, '1' ck from dual union all
select '20110903' dd, '2' ck from dual union all
select '20110904' dd, '2' ck from dual union all
select '20110905' dd, '1' ck from dual union all
select '20110906' dd, '1' ck from dual union all
select '20110907' dd, '1' ck from dual union all
select '20110908' dd, '1' ck from dual union all
select '20110909' dd, '1' ck from dual union all
select '20110910' dd, '2' ck from dual union all
select '20110911' dd, '2' ck from dual union all
select '20110912' dd, '2' ck from dual union all
select '20110913' dd, '2' ck from dual union all
select '20110914' dd, '1' ck from dual union all
select '20110915' dd, '1' ck from dual union all
select '20110916' dd, '1' ck from dual union all
select '20110917' dd, '2' ck from dual union all
select '20110918' dd, '2' ck from dual union all
select '20110919' dd, '1' ck from dual union all
select '20110920' dd, '1' ck from dual union all
select '20110921' dd, '2' ck from dual union all
select '20110922' dd, '1' ck from dual union all
select '20110923' dd, '1' ck from dual union all
select '20110924' dd, '2' ck from dual union all
select '20110925' dd, '2' ck from dual union all
select '20110926' dd, '1' ck from dual union all
select '20110927' dd, '1' ck from dual union all
select '20110928' dd, '1' ck from dual union all
select '20110929' dd, '1' ck from dual union all
select '20110930' dd, '1' ck from dual
)
select a.dd
,a.ck
,case when a.ck = '1' then a.후영업일     else max(a.후영업일)     over(order by a.dd) end 후영업일
,case when a.ck = '1' then a.후후영업일   else max(a.후후영업일)   over(order by a.dd) end 후후영업일
,case when a.ck = '1' then a.후후후영업일 else max(a.후후후영업일) over(order by a.dd) end 후후후영업일
,case when a.ck = '1' then a.전영업일     else min(a.전영업일)     over(order by a.dd desc) end 전영업일
,case when a.ck = '1' then a.전전영업일   else min(a.전전영업일)   over(order by a.dd desc) end 전전영업일
,case when a.ck = '1' then a.전전전영업일 else min(a.전전전영업일) over(order by a.dd desc) end 전전전영업일  
from (
  select dd
  ,ck
  ,case when ck = '2' then null else lead(dd, 1) over(partition by ck order by dd) end 후영업일
  ,case when ck = '2' then null else lead(dd, 2) over(partition by ck order by dd) end 후후영업일
  ,case when ck = '2' then null else lead(dd, 3) over(partition by ck order by dd) end 후후후영업일  
  ,case when ck = '2' then null else lag(dd, 1)  over(partition by ck order by dd) end 전영업일
  ,case when ck = '2' then null else lag(dd, 2)  over(partition by ck order by dd) end 전전영업일
  ,case when ck = '2' then null else lag(dd, 3)  over(partition by ck order by dd) end 전전전영업일
  from cal_t
  where 1=1
 ) a
 order by a.dd

[출처] 휴일감안 전/후영업일자 조회|작성자 마중물

====================================================================뷰테이블 만들어서 사용

CREATE OR REPLACE FORCE VIEW APLUSORA.APLUS_V_WORK_DAY
(
   DD,
   ISWORK,
   B_DD

)
AS
     WITH CAL_T AS
(
    SELECT WORK_DAY DD, ISWORK FROM IFT_WORKING_DAY
    WHERE  WORK_DAY BETWEEN '20120700' AND '20121231'
)
SELECT A.DD
,A.ISWORK
,CASE WHEN A.ISWORK = 'Y' THEN DD ELSE MIN(A.B_DD)     OVER(ORDER BY A.DD DESC) END B_DD
FROM (
  SELECT DD
  ,ISWORK
  ,CASE WHEN ISWORK = 'N' THEN NULL ELSE LAG(DD, 1)  OVER(PARTITION BY ISWORK ORDER BY DD) END B_DD
  FROM CAL_T
  WHERE 1=1
 ) A;


SELECT TXDATE,권유자,권유자변경일,사번,계좌명,펀드계좌번호,AAA,BBB,AAA-BBB AS CCC
FROM
(
    SELECT
      A.TXDATE
     --,LENGTH(RECOMMEND_ID)
     ,A.RECOMMEND_NAME 권유자
     ,A.RECOMM_DATE 권유자변경일
     ,A.RECOMMEND_ID 사번
     ,A.CUSTOMER_NAME 계좌명
     ,A.FUND_ACCOUNT_NO 펀드계좌번호
     ,NVL(A.PAYMENT_AMOUNT_TOTAL,0) AAA
     ,NVL((SELECT PAYMENT_AMOUNT_TOTAL FROM IFK_FUND_RECOMMENDER_141
           WHERE TXDATE = CASE WHEN A.RECOMM_DATE-1 < '20120706'  THEN '20120706'
           ELSE (SELECT DECODE(ISWORK,'Y',DD,B_DD) AS WORK FROM APLUSORA.APLUS_V_WORK_DAY
                    WHERE DD = TO_CHAR(TO_DATE(A.RECOMM_DATE)-1,'YYYYMMDD')) END 
           AND FUND_ACCOUNT_NO = A.FUND_ACCOUNT_NO),0) BBB
    FROM APLUSORA.IFK_FUND_RECOMMENDER_141 A
    WHERE LENGTH(A.RECOMMEND_ID) =6
    AND A.TXDATE BETWEEN '20120706' AND '20120831'
    --AND A.RECOMM_DATE='20091001'
    AND A.RECOMMEND_ID NOT IN ('090001','090002')
) X

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

댓글을 달아 주세요

[ORACLE]Listener.log 관리

Database 2012.08.24 17:06 Posted by 애플자라

(로그 위치를 변경하지 않았다면)
$ORACLE_HOME/network/log 디렉토리에 보면, 
listener.log 가 꾸준히 또는 급격하게 사이즈가 증가할 경우가 있다.  

이럴 경우, 

lsnrctl  ->

lsnrctl> set current_listener <리스너명> --구성된 리스너명이 Listener 가 아닌 경우, 지정해 줌.

lsnrctl> set log_status off -- listener.log 파일에 로그를 기록하지 않는다.  

lsnrctl> set log_status on -- listener.log 파일에 로그를 기록.

set log_status off 로 변경하고, 파일 제거 또는 백업하고 listener.log 파일 생성 (생성하지 않아도 자동 생성) 

출처 - http://jmkjb.tistory.com/entry/ListenerlogManage

 

명령어는 lnsrctl start /stop/ status 사용

lsnrctl status (리스너 서비스 확인)

lsnrctl stop (리스너 서비스 멈춤)

lsnrctl start (리스너 서비스 시작)

서비스이름 확인후 tnsping service_name //host ip주소확인 상태확인

댓글을 달아 주세요

[ORACLE]SQL*Loader 사용법

Database 2012.08.24 10:39 Posted by 애플자라

문법
      SQLLDR [keyword=] value  [ [keyword=] value ]...

 

예제
      SQLLDR scott/tiger control='c:\xxx.ctl' log='xxx.log' direct=true ERRORS=99999999

 

키워드

  USERID
  오라클 사용자 이름과 암호를 지정 합니다.

  CONTROL
  콘트롤 파일 이름, SQL*Loader을 수행하기 위해서는 항상 지정해 주어야  합니다.

  LOG
  로그 파일 이름을 지정 합니다. (기본 이름은 controlfile.log)

  BAD
  거부된 레코드 모두를 저장하는 배드 파일 이름을 지정 합니다.

  DATA
  입력 데이터 파일 이름을 지정 합니다.

  DISCARD
  Load시 선택되지 않은 레코드가 저장되는 디스카드 파일(선택 사항)

  DISCARDMAX
  버림(discard)의 최대 허용 갯수를 지정 합니다.

  ERRORS
  허용하는 배드 레코드의 최대 수를 지정 합니다.

  DIRECT
  TRUE로 설정되면 SQL*Loader는 DIRECT PATH를 사용.
  반대의 경우는 기본 값인 CONVENTIONAL PATH를 사용 합니다.

  PARFILE
  추가 파라미터 파일을 지정 합니다.

  PARALLEL
  DIRECT 로드에서만 적합한 이 파라미터는 다중 병렬 DIRECT로드가
  수행되도록 지정 합니다.

  FILE
  병렬 DIRECT로드의 경우 임시 세그먼트가 생성될 파일을 지정 합니다.

-- SQLLoader 로그

제어 파일:    S_DATAFILE.CTL
데이터 파일:    NEW_ADDRESS_sam.txt
  잘못된 파일: C:\SQLLOADER\NEW_ADDRESS_sam.BAD
  폐기 파일:    지정 사항 없음

 (모든 폐기된 레코드 허용)

로드할 건수: ALL
생략 건수:  0
허용 오류수:  50
바인드 배열:  1000 행, 최대 256000 바이트
계속:    지정 사항 없음
사용된 경로:      규약

최대 오류 수 초과 - 상기 통계는 부분적인 실행을 반영한 것입니다 --이것 때문에 찾게된
테이블 NEW_ADDRESS_20120823:
  2895500 행 로드되었습니다.
  데이터 오류 때문에 51 행(이)가 로드되지 않았습니다
  모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
  모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다

 

댓글을 달아 주세요

 테이블 복사하기 스키마 데이터

CREATE TABLE 새로만들테이블명 AS

SELECT * FROM 복사할테이블명 [WHERE 절]

 

 테이블 구조만 복사하기

CREATE TABLE 새로만들테이블명 AS

SELECT * FROM 복사할테이블명 WHERE 1=2 [where에다가 참이 아닌 조건을 넣어줌]

 

 테이블은 이미 생성되어 있고 데이터만 복사

INSERT INTO 복사할테이블명 SELECT * FROM 복사할테이블명 [WHERE 절]

 

테이블 이름 변경

ALTER TABLE 구테이블명 RENAME TO 신테이블명

주의할 점은, Primary Key 나 Index 등 Constraint 는 복제되지 않습니다. 당연한 것이, 복제된다면 동일한 이름의 Object 가 이미 존재한다는 에러를 만나게 되겠죠. 데이터를 잠시 옮겨놓기 위한 용도의 테이블 복제에선 불필요하다고 봅니다.

댓글을 달아 주세요