웹 개발과 관련된 모든 것들

형태

오라클 계정 -> 5분 단위로 해서 -> 아래의 테이블 생성후 -> Crontab 으로 조회하여 입력하는 형태

자세한 것은 Shell, Crontab 정보를 찾으면서 하면됨

-- 임의로 실행하는 쿼리목록 조회하기
select      to_char(sysdate,'yyyymmdd'),
            to_char(sysdate, 'HH24'),
            to_char(sysdate, 'MI'),
            A.inst_id,
            A.SID,
            A.SERIAL#,
            A.USERNAME,
            A.TYPE,
            A.STATUS,
            A.STATE,
            A.EVENT,
            A.OSUSER,
            A.PROGRAM,
            A.MACHINE,
            A.SQL_ADDRESS,
            B.SQL_ID,
            B.sharable_mem,
            B.persistent_mem,
            B.runtime_mem,
            B.first_load_time,
            B.OPTIMIZER_MODE,
            B.OPTIMIZER_COST,
            B.MODULE,
            B.SQL_FULLTEXT
   from     GV$SESSION a, GV$SQL B
  WHERE     A.STATUS = 'ACTIVE'                       -- 현재 활성화된
    AND     B.ADDRESS=A.SQL_ADDRESS                   -- FULL SQL TEXT 를 얻기 위해
    AND     A.MACHINE NOT IN ('NewWas1','NewWas2')    -- 접속 Machine 이름이 NewWas1, NewWas2 는 아님
    AND     UPPER(B.SQL_FULLTEXT) NOT LIKE '%V$%';    -- V$ 로 시작하는 뷰를 보는 경우는 제외
   
   
CREATE TABLE MStorage.USER_SQL_LOG
(
      VIEW_YMD   VARCHAR2(8) DEFAULT to_char(sysdate,'yyyymmdd'),
      VIEW_HH    VARCHAR2(2) DEFAULT to_char(sysdate, 'HH24'),
      VIEW_MI    VARCHAR2(2) DEFAULT to_char(sysdate, 'MI'),
      INST_ID    NUMBER,
      SID      NUMBER,
      SERIAL#    NUMBER,
      USERNAME   VARCHAR2(30),
      TYPE     VARCHAR2(10),
      STATUS    VARCHAR2(8),
      STATE     VARCHAR2(19),
      EVENT     VARCHAR2(64),
      OSUSER    VARCHAR2(30),
      PROGRAM    VARCHAR2(48),
      MACHINE    VARCHAR2(64),
      SQL_ADDRESS RAW(8),
      SQL_ID   VARCHAR2(13),
      SHARABLE_MEM NUMBER,
      PERSISTENT_MEM NUMBER,
      RUNTIME_MEM NUMBER,
      FIRST_LOAD_TIME VARCHAR2(38),
      OPTIMIZER_MODE VARCHAR2(10),
      OPTIMIZER_COST NUMBER,
      MODULE     VARCHAR2(64),
      SQL_FULLTEXT  CLOB
) TABLESPACE MStorage;

COMMENT ON TABLE MStorage.USER_SQL_LOG IS '로그 모니터링 테이블(GV$SESSION) 참조';

 

-- 인덱스 생성
CREATE INDEX MStorage.IX_USER_SQL_LOG_01 ON MStorage.USER_SQL_LOG
(
  VIEW_YMD,VIEW_HH, VIEW_MI
)
TABLESPACE MStorage;

 

-- 실제 입력 구문

INSERT INTO MStorage.USER_SQL_LOG
       (
       inst_id,
            SID,
            SERIAL#,
            USERNAME,
            TYPE,
            STATUS,
            STATE,
            EVENT,
            OSUSER,
            PROGRAM,
            MACHINE,
            SQL_ADDRESS,
            SQL_ID,
            sharable_mem,
            persistent_mem,
            runtime_mem,
            first_load_time,
            OPTIMIZER_MODE,
            OPTIMIZER_COST,
            MODULE,
            SQL_FULLTEXT               
            )
select      A.inst_id,
            A.SID,
            A.SERIAL#,
            A.USERNAME,
            A.TYPE,
            A.STATUS,
            A.STATE,
            A.EVENT,
            A.OSUSER,
            A.PROGRAM,
            A.MACHINE,
            A.SQL_ADDRESS,
            B.SQL_ID,
            B.sharable_mem,
            B.persistent_mem,
            B.runtime_mem,
            B.first_load_time,
            B.OPTIMIZER_MODE,
            B.OPTIMIZER_COST,
            B.MODULE,
            B.SQL_FULLTEXT
   from     GV$SESSION a, GV$SQL B
  WHERE     1=1-- A.STATUS = 'ACTIVE'                       -- 현재 활성화된
    AND     B.ADDRESS=A.SQL_ADDRESS                   -- FULL SQL TEXT 를 얻기 위해
    AND     A.MACHINE NOT IN ('NewWas1','NewWas2')    -- 접속 Machine 이름이 NewWas1, NewWas2 는 아님
    AND     UPPER(B.SQL_FULLTEXT) NOT LIKE '%V$%';    -- V$ 로 시작하는 뷰를 보는 경우는 제외

---

금일..

이클립스 상에서 apache 5.5를 실행하던중

다음과 같은 오류 발생

org.apache.catalina.session.StoreBase processExpires

심각 : java.io.EOFException

...

원인

JDK 1.4에서 발생하며

이파일은 톰켓이 시작 정보 백업을 위해 만드는 것인데, 아마도 시작 정보 만들때 뭔가 문제가 생겨서 그런듯...

결론 !

이클립스 종료 ->

<dotcms home>\work\Catalina\localhost\_\SESSIONS.ser

삭제

이클립스 시작

근데.. 플러그인 형태로 설치한 난.. tldCache.ser 로 검색하니 금일 날짜로 나왔슴...

삭제하고 재시작하니 깔끔하고 상쾌한 기분 ~

  SELECT   ROUND (BITAND (s.ownerid, 65535)) parent_session_sid,
           ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
           s.INST_ID,
           s.SADDR,
           s.SID,
           s.SERIAL#,
           s.AUDSID,
           s.PADDR,
           s.USER#,
           s.USERNAME,
           s.COMMAND,
           s.OWNERID,
           s.TADDR,
           s.LOCKWAIT,
           s.STATUS,
           s.SERVER,
           s.SCHEMA#,
           s.SCHEMANAME,
           s.OSUSER,
           s.PROCESS,
           s.MACHINE,
           s.PORT,
           s.TERMINAL,
           UPPER (s.PROGRAM) PROGRAM,
           s.TYPE,
           s.SQL_ADDRESS,
           s.SQL_HASH_VALUE,
           s.SQL_ID,
           s.SQL_CHILD_NUMBER,
           s.PREV_SQL_ADDR,
           s.PREV_HASH_VALUE,
           s.PREV_SQL_ID,
           s.PREV_CHILD_NUMBER,
           s.PLSQL_ENTRY_OBJECT_ID,
           s.PLSQL_ENTRY_SUBPROGRAM_ID,
           s.PLSQL_OBJECT_ID,
           s.PLSQL_SUBPROGRAM_ID,
           s.MODULE,
           s.MODULE_HASH,
           s.ACTION,
           s.ACTION_HASH,
           s.CLIENT_INFO,
           s.FIXED_TABLE_SEQUENCE,
           s.ROW_WAIT_OBJ#,
           s.ROW_WAIT_FILE#,
           s.ROW_WAIT_BLOCK#,
           s.ROW_WAIT_ROW#,
           s.LOGON_TIME,
           s.LAST_CALL_ET,
           s.PDML_ENABLED,
           s.FAILOVER_TYPE,
           s.FAILOVER_METHOD,
           s.FAILED_OVER,
           s.RESOURCE_CONSUMER_GROUP,
           s.PDML_STATUS,
           s.PDDL_STATUS,
           s.PQ_STATUS,
           s.CURRENT_QUEUE_DURATION,
           s.CLIENT_IDENTIFIER,
           s.BLOCKING_SESSION_STATUS,
           s.BLOCKING_INSTANCE,
           s.BLOCKING_SESSION,
           s.SEQ#,
           s.EVENT#,
           s.EVENT,
           s.P1TEXT,
           s.P1,
           s.P1RAW,
           s.P2TEXT,
           s.P2,
           s.P2RAW,
           s.P3TEXT,
           s.P3,
           s.P3RAW,
           s.WAIT_CLASS_ID,
           s.WAIT_CLASS#,
           s.WAIT_CLASS,
           s.WAIT_TIME,
           s.SECONDS_IN_WAIT,
           s.STATE,
           s.SERVICE_NAME,
           s.SQL_TRACE,
           s.SQL_TRACE_WAITS,
           s.SQL_TRACE_BINDS,
           s.ECID,
           stat.cpu - stat.CPU_this_call_start cpu_this_call,
           stat.CPU,
           stat.UGA_memory,
           stat.PGA_memory,
           stat.Commits,
           stat.Rollbacks
    FROM   GV$SESSION S,
           (  SELECT   ss.inst_id stat_inst_id,
                       ss.sid stat_sid,
                       SUM(DECODE (sn.name,
                                   'CPU used when call started', ss.VALUE,
                                   0))
                          CPU_this_call_start,
                       SUM(DECODE (sn.name,
                                   'CPU used by this session', ss.VALUE,
                                   0))
                          CPU,
                       SUM (DECODE (sn.name, 'session uga memory', ss.VALUE, 0))
                          uga_memory,
                       SUM (DECODE (sn.name, 'session pga memory', ss.VALUE, 0))
                          pga_memory,
                       SUM (DECODE (sn.name, 'user commits', ss.VALUE, 0))
                          commits,
                       SUM (DECODE (sn.name, 'user rollbacks', ss.VALUE, 0))
                          rollbacks
                FROM   GV$sesstat ss, GV$statname sn
               WHERE   ss.STATISTIC# = sn.STATISTIC# AND ss.inst_id = sn.inst_id
                       AND (   sn.name = 'CPU used when call started'
                            OR sn.name = 'CPU used by this session'
                            OR sn.name = 'session uga memory'
                            OR sn.name = 'session pga memory'
                            OR sn.name = 'user commits'
                            OR sn.name = 'user rollbacks')
            GROUP BY   ss.inst_id, ss.sid) stat
   WHERE   (    (s.USERNAME IS NOT NULL)
            AND (NVL (s.osuser, 'x') <> 'SYSTEM')
            AND (s.TYPE <> 'BACKGROUND'))
           AND ( (stat.stat_inst_id = s.inst_id) AND (stat.stat_sid = s.sid))
ORDER BY   "PROGRAM", OWNERID

ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다 해결 방법

테이블명이 WOONJIN의 LOCK 걸려있는 SID와 SERIAL을 찾는다.

select a.sid, a.serial#
from v$session a, v$lock b, dba_objects c
where a.sid=b.sid and
b.id1=c.object_id and
b.type='TM' and
c.object_name='JOONI';

SID SERIAL#
--- -------
82   8210

alter system kill session '82, 8210';