웹 개발과 관련된 모든 것들

sysaux 테이블스페이스는 오라클 10g 버전을 통하여 새롭게 소개되는 테이블스페이스로서 기존의 시스템 테이블스페이스에 저장되어 관리되어 오던 여러 요소들 가운데, 일부 또는 별도의 테이블스페이스의 생성을 요구하는 이들 요소를 한 공간에 저장, 관리하는 기능을 제공하게 될 시스템 관리 테이블스페이스이다.

sysaux 테이블스페이스에 저장, 관리되어지는 기능은 다음과 같다.

STATSPACK	statspack 패키지, 정보저장공간	perstat
LOGMGR	Logminer 정보저장공간	system
STREAMS	오라클 스트림 정보저장공간	sys
SMC	서버관리 요소 저장공간	sys
ODM	오라클 데이터 mining 정보 저장공간	dmsys
WM	workspace manager 정보 저장공간	wmsys
ORDIM	오라클 인터미니어 ORDPLUG 요소 정보 저장공간	ordsys
TEXT	오라클 text 정보 저장공간	ctxsys
ULTRASEARCH	오라클 ultrasearch 정보 저장공간	wksys
JOB_SCHEDULER	오라클 job scheduler 정보 저장공간	sys
XSOQHIST	OLAP API 테이블 정보 저장공간	sys
LOGSTDBY	logical standby	system
EM	enterprise manager 정보 저장공간	sysman

기본적으로 오라클을 설치하면 sysaux 테이블스페이스가 설정되지만, 사용자가 다음과 같이 임의로 데이터 파일의 위치와 이름을 지정하여 만들 수도 있다.

SQL> crerate database jo  2  datafile '/export/home0/oracle/app/oracle/oradata/orcl/jo_system.dbf' size 500M 3  sysaux datafile '/export/home0/oracle/app/oracle/oradata/orcl/jo_sysaux.dbf' size 200M 4  default temporary tablespace temp_ts 5  tempfile '/export/home0/oracle/app/oracle/oradata/orcl/jo_temp.dbf' size 50M 6  undo tablespace undo 7  datafile '/export/home0/oracle/app/oracle/oradata/orcl/jo_undo.dbf' size 100M; 그리고 다음과 같이 이미 생성된 sysaux 테이블스페이스의 공간을 인위적으로 조절할 수 도 있다. SQL> alter tablespace sysaux add datafile 2  '/export/home0/oracle/app/oracle/oradata/orcl/jo_sysaux02.dbf' size 200M; sysaux 테이블스페이스는 다음과 같은 점에 유의해야 한다.

. 일단 데이터베이스를 시작하게 되면 sysaux 테이블스페이스를 제거할 수 없다.
. sysaux 테이블스페이스는 오라클 이동 가능 테이블스페이스(transportable tablespace) 기능을 사용하여 다른 데이터베이스에 이동할 수 없다.
. 데이터베이스가 시작되어진 상태에서 sysaux 테이블스페이스의 이름을 변경(rename)할 수 없다.
. 오라클 10g 버전으로 migration하는 경우 sysaux 테이블스페이스를 생성해 줄 수 있는데 이때 반드시 데이터베이스는 migrate 모드에서 오픈 되어져 있어야 한다.
. sysaux 테이블스페이스에 손상이 발생한 경우 전체 시스템에는 별 영향을 주지 않는다.
단, sysaux 테이블스페이스에 저장, 관리되고 있는 아큐펀트에 대한 기능을 사용할 수 없다.

SQL>  select occupant_name,schema_name from v$sysaux_occupants; 

 
OCCUPANT_NAME      SCHEMA_NAME
------------------ -----------------------
LOGMNR             SYSTEM
LOGSTDBY           SYSTEM
STREAMS            SYS
XDB                XDB
AO                 SYS
XSOQHIST           SYS
XSAMD              OLAPSYS
SM/AWR             SYS
SM/ADVISOR         SYS
SM/OPTSTAT         SYS
SM/OTHER           SYS
STATSPACK          PERFSTAT
ODM                DMSYS
SDO                MDSYS
WM                 WMSYS
ORDIM              ORDSYS
ORDIM/PLUGINS      ORDPLUGINS
ORDIM/SQLMM        SI_INFORMTN_SCHEMA
EM                 SYSMAN
TEXT               CTXSYS
ULTRASEARCH        WKSYS
JOB_SCHEDULER      SYS
 
22 rows selected.
 
SQL>
[출처] sysaux 테이블스페이스란?|작성자 레인보우
 
 
 
 -- 조회방법



select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES from V$sysaux_occupants;


select sum(bytes)/1024/1024/1024 from dba_segments where tablespace_name='SYSAUX';
 

결정 요소 : UNDO_RETENTION, DB_BLOCK_SIZE, 초당 생성되는 UNDO 데이터 블럭 수

SELECT SUM(UNDOBLKS)/SUM((EDN_TIME-BEGIN_TIME)*86400) FROM V$UNDOSTAT;

==> 초당 UNDO 데이터 블럭 수

 

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"

FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),

          (SELECT (SUM(undoblks)/SUM(((end_time-begin_time)*86400))) AS UPS

           FROM v$undostat),

          (SELECT value AS DBS FROM v$parameter WHERE name = 'db_block_size');

토드에서 스키마 브라우져(Schema Brwoser) 에서

TAB들이 난무할때 좌우 선택 형태로 변환시키는거...

 

1. RANK () OVER ( [query_partition_clause] order_by_clause)

2. DENSE_RANK () OVER ( [query_partition_clause] order_by_clause)

3. PERCENT_RANK () OVER ([query_partition_clause] order_by_clause )

  --그룹 수에 대한 값의 순위 퍼센트를 계산,  범위 : 0 < PERCENT_RANK < 1

4. CUME_DIST ( ) OVER ( [query_partition_clause] order_by_clause )

  -- 누적 분포 (전체 중 특정한 값의 위치를 계산), 범위 : 0 < CUME_DIST < 1

5. NTILE (expr) OVER ( [query_partition_clause] order_by_clause )

  -- 분위수 계산

6. ROW_NUMBER () OVER ( [query_partition_clause] order_by_clause )

  -- 행의 수 계산 (파티션 내에서 각 행에 대해 1로 시작하여 정렬되어 정의되는 유일한 수를 할당)

 

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

[누적합]  :   ROWS UNBOUNDED PRECEDING

[이동평균]  :   ROWS 2 PRECEDING  (시간에 근거 - 시계열)

[중심평균]  :  현재 행이 중심이 된다.   (예. 전날과 다음날과의 평균)

 

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

RATIO_TO_REPORT  : RATIO_TO_REPORT ( expr ) OVER ( [query_partition_clause] )

 

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

{LAG | LEAD} ( value_expr [, offset] [, default] )  OVER ( [query_partition_clause] order_by_clause )

 

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

[CUME DIST]  :   RANK () OVER ( [query_partition_clause] order_by_clause)  --> 누적분포

db특별sql-drake661.doc

[출처] 오라클 특별한 함수 모음|작성자 구름할까

EXPORT받은 DUMP 파일의 CHARACTER SET 확인 방법
====================================================

다른 server에서 export받아온 dump file을 import시킬 경우 자주 겪게 되는
문제가 CHARACTERset에 관련된 것이다.
양쪽의 CHARACTERset이 같고 .profile의 환경변수(NLS_LANG)도 맞다고
생각되는 데도 import 시 segment fault가 발생되거나
imp-16, 36, 37, 38, 42, 43, 45, 49 등의 error가 발생되면 dump받아온 file을
의심해볼 필요가 있다.

이 때 dump file을 8진수로 변환해 보면 현재 export받은 file의
CHARACTERset을 쉽게 알 수 있다.


오라클 export file은 특별한 CHARACTER set을 사용해서 쓰여진다.
version 5, 6에서는 export 시 단지 두개의 CHARACTER set-ASCII와 EBCIDC-만이
지원되었으나, version 7에서는 지원하는 모든 CHARACTER set이 export 시에
사용될 수 있다. export 시 사용된 CHARACTER set은 dump file 내에 code로써
기술되는데 file의 첫 byte가 이것을 나타낸다.

1st byte 의미
----------------------------------------------------
1 version 5, 6의 ASCII CHARACTER set
2 version 5, 6의 EBCDIC CHARACTER set
3 version 7의 CHARACTER set.

"3"일 경우 두, 세번째 byte를 보고서 CHARACTER set을 판단한다.
아래에는 우리가 흔히 접하는 character set에 대한 CHARACTER set ID이다.

2nd 3rd value
--------------------------------------------------
000 001 US7ASCII
000 002 WE8DEC
000 037 WE8ISO8859P1
003 110 KO16KSC5601
003 147 UTF8 (oracle8.0 이상부터 지원)
: :
: :

export file을 체크하기 위해서는 다음의 unix command를 사용한다.


$ od -b expdat.dmp|more

0000000 003 000 001 105 130 120 117 122 124 072 126 060 067 056 060 062
0000020 056 060 062 012 104 112 115 113 111 115 012 122 124 101 102 114
          :
          :
위의 경우 1st byte가 003이므로 version 7이며, 3rd byte가 001이므로 ID가
1번인 US7ASCII가 CHARACTER set이 된다.

(비고:
  HP-UX에서 TEST 결과 DB CHARACTERSET이 무엇으로 지정되었건 EXPORT 시는
  USER의 .PROFILE에 있는 환경 변수를 기준하여 DUMP FILE을 WRITE하므로
  EXPORT 실시 때의 OS USER의 환경변수인 NLS_LANG 값이 상당히 중요함.)

          
from www.oracle.co.kr 

SQL> set markup html on

출처:http://blog.naver.com/rojinsong?Redirect=Log&logNo=50004423503

$ORACLE_HOME 디렉토리에 있는 설치화일을 전부삭제
/etc밑에 orainst.loc, oratab 삭제
/usr/local/bin/oraenv 파일삭제
/tmp디렉토리에서 관련파일삭제
elete the /etc/oratab file. If using 9iAS delete the /etc/emtab file also.
# rm /etc/oratab /etc/emtab
끝 재설치 하면됩니다.

얼마 전 database.sarang.net의 오라클 게시판에 DBMS_JOB을 이용해 원하는 작업을 08시, 14시, 20시에 실행시키는 방법을 묻는 질문이 올라왔다. 작업 간격이 규칙적일 때는 문제가 간단하지만 원하는 시간 간격이 불규칙하므로 그냥 JOB을 세 개 등록하면 어떻겠냐고 답했더니 이번에는 이 작업을 평일에만 실행시키게 하고 싶다고 했다. 즉 평일 08시, 14시, 20시에 작업이 실행되도록 하고 싶다는 것이었다.

그럼 문제를 풀기 전에 DBMS_JOB.SUBMIT 프로시저를 살펴보자. DBMS_JOB을 이용해 JOB을 등록시키려면 SUBMIT 프로시저를 사용해야 한다. 파라미터 중 next_date와 interval를 통해 작업 실행 시각을 조절할 수 있다.

DBMS_JOB.SUBMIT (
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT sysdate,
interval IN VARCHAR2 DEFAULT 'null',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT any_instance,
force IN BOOLEAN DEFAULT FALSE);

next_date의 디폴트 값은 sysdate이므로 값을 주지 않으면 등록 즉시 실행된다. 그 다음 실행 시각은 JOB이 실행되기 직전 interval에 지정된 수식을 이용해 계산한다. (interval이 NULL일 경우는 작업이 한 번만 실행된다.) 파라미터 이름이 interval이기는 하지만 실제 의미는 interval이 아니라 "다음 실행될 시점을 계산할 수식"인 것이다. 만약 어떤 작업을 1시간에 1번씩 실행시키고 싶다면 interval을 'sysdate+1/24'로 주면 된다. 작업을 시작하기 전에 sysdate+1/24를 통해 다음 실행할 시각을 구하면 작업 시작 시간으로부터 1시간 후인 시각이 된다. 다음 작업 시작 시각을 알고 싶으면 ALL_JOBS의 NEXT_DATE 컬럼을 조회해 확인할 수 있다.


그런데 위와 같이 하면 작업 주기만 지정한 것일 뿐이다. 특정 시각에 JOB을 실행시키려면 다음과 같이 하면 된다.


interval 파라미터는 문자열로 주어야 하므로 수식 내에 따옴표(single quotation)이 있으면 따옴표를 두 개 써줘야 하는 것에 유의해야 한다. interval 수식이 복잡할 때는 확인하기가 어려울 수 있는데, 그럴 때는 interval 수식으로 직접 쿼리를 작성해 확인할 수 있다.

select trunc(sysdate, 'D') + 7 from dual;

이제 다음과 같이 다양한 경우에 대한 interval을 구해보자.

1. 매주 토요일 새벽 1시에 실행
   
2. 매월 1일 새벽 0시에 실행
   
3. 매월 말일 밤 11시에 실행
   
4. 평일(월화수목금) 밤 10시에 실행
   
5. 불규칙한 시각, 8시, 14시, 20시에 한번씩

1번은 쉽다. 일단 next_date를 이번 주 토요일 새벽 1시로 지정하고, 그 다음 실행될 날은 거기서 7일 후가 된다. 즉,

next_date=>to_date('2007102701','YYYYMMDDHH24'),
interval=>'sysdate + 7'

월초나 월말의 경우는 add_months나 last_day를 이용해 구하면 된다.

매월1일 새벽 0시 작업 실행
next_date=>add_months(trunc(sysdate,'MM'),1),
interval=>'add_months(trunc(sysdate,''MM''),1)'

매월 말일 밤 11시에 작업 실행
next_date=>last_day(trunc(sysdate))+23/24,
interval=>'last_day(trunc(sysdate)+1)+23/24' -- 말일+1일은 다음달 1일

평일만 실행되도록 하기 위해서는 interval이 좀더 복잡해진다.

interval=>'trunc(sysdate) + decode(to_char(sysdate,''D''), ''6'', 3, ''7'', 2, 1) + 22/24'

요일을 구한 다음 토요일(to_char(sysdate,'D')='6')에는 작업 후 3일 후에, 일요일(to_char(sysdate,'D')='7')에는 작업 후 2일 후에, 평일에는 자업 후 1일 후에 작업이 다시 시작되도록 하면 된다. 이를 위해 DECODE 함수를 활용했다.

불규칙한 시간 간격일 경우에도 작업 시각을 기반으로 DECODE를 활용하면 가능할 것 같다. 그러나 하루 수행 횟수가 서너 번 정도라면 그냥 각 시각마다 실행되도록 서너 개의 JOB을 등록시켜주는 것도 생각해볼 수 있다.

원래 문제는 불규칙한 시각+평일 조건을 만족해야 하므로 하나의 interval 수식으로 해결하려면 수식이 무척 복잡해질 것 같다. interval 수식이 복잡해지면 이해가기도 어려워 진고, 나중에 수정하고 싶을 때 문제가 생길 수도 있다.

참고로 10g부터는 DBMS_JOB 대신 DBMS_SCHEDULER을 쓰도록 권고하고 있다.

출처 : http://ntalbs.blogspot.com/2007/10/database.html

Oracle SID를 알려면 다음 처럼 질의 합니다.

SQL> select instance from v$thread;

INSTANCE
----------------
NEWS

Oracle DB_NAME은 다음 처럼 질의 하구요...

SQL> select name from v$database;

NAME
---------
NEWS

ORACLE_SID나 DB_SID는 같은 값을 가리키며 v$thread에서 확인 가능!!
DB_NAME은 v$database에서 확인 가능!!!

FIRST_VALUE() OVER() -> 전체 값들 중 첫번째값을 반환합니다

 LAST_VALUE() OVER() -> 전체 값들 중 마지막값을 반환합니다

 COUNT() OVER() -> 조건을 만족하는 행의 수를 반환합니다

 SUM() OVER() -> 특정 값을 누적하여 결과를 반환합니다

 DENSE_RANK() OVER() -> RANK()와는 달리 같은 순위가 둘 이상 있어도 다음 순위는 1증가