IT당~얼쑤가 아니네

SELECT

--달력 맵핑

        MIN(DECODE(TO_CHAR(DATES,'D'),1,TO_CHAR(DATES,'DD'))) 일

        ,MIN(DECODE(TO_CHAR(DATES,'D'),2,TO_CHAR(DATES,'DD'))) 월

        ,MIN(DECODE(TO_CHAR(DATES,'D'),3,TO_CHAR(DATES,'DD'))) 화

        ,MIN(DECODE(TO_CHAR(DATES,'D'),4,TO_CHAR(DATES,'DD'))) 수

        ,MIN(DECODE(TO_CHAR(DATES,'D'),5,TO_CHAR(DATES,'DD'))) 목

        ,MIN(DECODE(TO_CHAR(DATES,'D'),6,TO_CHAR(DATES,'DD'))) 금

        ,MIN(DECODE(TO_CHAR(DATES,'D'),7,TO_CHAR(DATES,'DD'))) 토

FROM(

    SELECT (MAKE_DATES + LEVEL - 1) DATES   --달력 하위 일수 구하기

    FROM(

        SELECT (TO_DATE('200701','YYYYMM')) MAKE_DATES FROM DUAL --기준달력 생성

    )

    CONNECT BY (MAKE_DATES+LEVEL-1) <= LAST_DAY(MAKE_DATES) --마지막일자

)

GROUP BY DECODE(TO_CHAR(DATES,'D'),1, TO_CHAR(DATES,'W')+1,TO_CHAR(DATES,'W')) --합치기

ORDER BY DECODE(TO_CHAR(DATES,'D'),1, TO_CHAR(DATES,'W')+1,TO_CHAR(DATES,'W')); --주차 정렬

cl scr 화면초기화

parseInt(daySplit[0]);  //8진수 -> 08 or 09 입력시 0리턴

parseInt(daySplit[0],10); //10진수

참조 : http://blog.naver.com/my0biho?Redirect=Log&logNo=40149256484

원 저작자 : http://www.bysql.net/index.php?document_srl=10922&mid=ORACLE

[oracle@localhost ~]$ sqlplus '/as sysdba'

plan table 생성
SQL> conn scott/tiger
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.
플랜 테이블명 : PLAN_TABLE

오렌지 플랜테이블 설정

PLAN TOOL(ALT+F7) -> OPTION -> OPTIONS -> PLAN TABLE -> PLAN_TABLE(플랜테이블명)

PLUSTRACE 룰 생성

SQL> conn sys/manager as sysdba
Connected.

SQL>
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>

plustrce.sql 내용 시작

//기존 롤 제거
SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

//롤 생성
SQL> create role plustrace;

Role created.
//권한부여
SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

plustrce.sql 내용 끝

SQL>
SQL> set echo off
SQL> grant plustrace to scott(사용자);

Grant succeeded.

PLAN 보기 확인

 EXPLAIN PLAN FOR
  2  쿼리~~~

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

SQL> conn scott/tiger
Connected.
SQL> set linesize 120
SQL> set autot on   or  set autot trace exp
SQL> select * from emp where rownum=1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 1973284518

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    87 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |  1218 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

수동 plan 설정

DESC PLAN_TABLE; <플랜테이블 확인

EXPLAIN PLAN SET STATEMENT_ID = 'TEMP1'
INTO PLAN_TABLE FOR
SELECT *
FROM MEMBER
WHERE 1=1;

플랜확인
SELECT ID, PARENT_ID P_ID
,LPAD(' ',2*(LEVEL))||OPERATION,OPTIONS, OBJECT_NAME
FROM PLAN_TABLE
START WITH ID =0 AND STATEMENT_ID = 'TEMP1'
CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'TEMP1';

SELECT
       SUBSTR(SO.OBJECT_NAME,1,12) "오브젝트명"
      , SO.OBJECT_TYPE
   /*   ,DECODE(SO.OBJECT_TYPE
                      , 1, 'INDEX'
                      , 2, 'TABLE'
                      , 3, 'CLUST'
                      , 4, 'VIEW '   6
                      , 5, 'SYNON'
                      , 6, 'SEQUE'
                      , 7, 'PROCE'
                      , 8, 'FUNCT'
                      , 9, 'PACKA'
                      ,11, 'PAC-B'
                      ,12, 'TRIGG'
                      ,TO_CHAR(SO.OBJECT_TYPE,'9999')
             )                 "TYPE"    */
      ,SUBSTR(VS.OSUSER,1,6)     "로그인"
      ,VP.TERMINAL               "터미날"
      ,SUBSTR(VP.SPID,1,5)       "UXPID"
      ,SUBSTR(VS.PROCESS,1,6)    "UXPPID"
      ,SUBSTR(VS.USERNAME,1,4)   "USER"
      ,SUBSTR(DECODE(VS.COMMAND,'2','INSERT'
                               ,'3','SELECT'
                               ,'6','UPDATE'
                               ,'7','DELETE'
                               ,VS.COMMAND
             ),1,6)
                                 "명령어"
      ,DECODE(VL.LMODE,1,'NULL',
                       2,'ROW-SHARE',
                       3,'ROW-EXCLUSIVE',
                       4,'SHARE',
                       5,'SHARE-ROW-EXCLU',
                       6,'EXCLU') "LOCK-상태"
      ,'ALTER SYSTEM KILL SESSION '||CHR(39)||VS.SID||','|| VS.SERIAL# ||CHR(39)||';'
  FROM
       V$LOCK    VL
     , V$SESSION VS
     , V$PROCESS VP
     , OBJ       SO
 WHERE VS.PADDR     = VP.ADDR
   AND VL.SID       = VS.SID
 --  AND VS.USERNAME != 'ORACLE8'
   AND VL.TYPE      = 'TM'
   AND SO.OBJECT_ID      = VL.ID1
 ORDER BY 1,2 ;

/* 락걸린 테이블 확인 */
SELECT do.object_name, do.owner, do.object_type, do.owner,
vo.xidusn, vo.session_id, vo.locked_mode
FROM
v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id ;

/*  해당테이블이 락에 걸렸는지.. */
SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
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 IN ('테이블명');

/* 락발생 사용자와 sql, object 조회 */

SELECT distinct x.session_id, a.serial#,
d.object_name, a.machine, a.terminal,
a.program, b.address, b.piece, b.sql_text
FROM v$locked_object x, v$session a, v$sqltext b, dba_objects d
WHERE x.session_id = a.sid and
x.object_id = d.object_id and
a.sql_address = b.address
order by b.address,b.piece;

/* 락 발생 사용자확인 */

SELECT distinct x.session_id, a.serial#,
d.object_name, a.machine, a.terminal, a.program,
a.logon_time , 'alter system kill session ''' || a.sid ||',' || a.serial# || ''';' as KILL_CMD
FROM gv$locked_object x, gv$session a, dba_objects d
WHERE x.session_id = a.sid and x.object_id = d.object_id
order by logon_time;

/* 접속 사용자 제거 */

–alter system kill session ’session_id,serial#’;
alter system kill session '26,6044';  -- '26(세션id),6044(시리얼번호)';

/* 현재 접속자의 sql 분석 */

SELECT distinct a.sid, a.serial#,
a.machine, a.terminal, a.program,
b.address, b.piece, b.sql_text
FROM v$session a, v$sqltext b
WHERE a.sql_address = b.address
order by a.sid, a.serial#,b.address,b.piece;

--오라클 환경설정 값 확인(세션, 프로세스)
select * from v$resource_limit;

참조 주소 : http://www.explab.net/?p=384

오라클 홈경로 : /u01/app/oracle/product/10.2.0/db_1/

vi /etc/rc.d/rc.local  수정
-- 시작시 자동실행

### Oracle Start ###
su - oracle -c /u01/app/oracle/product/10.2.0/db_1/bin/dbstart
su  - oracle -c /u01/app/oracle/product/10.2.0/db_1/bin/'lsnrctl start'
사용하면 추가
//  su  - oracle -c /u01/app/oracle/product/10.2.0/db_1/bin/'emctl start dbconsole'

실행 테스트
/u01/app/oracle/product/10.2.0/db_1/bin/dbstart
/u01/app/oracle/product/10.2.0/db_1/bin/'lsnrctl start'

실행시 오류가 떨어지면
Failed to auto-start Oracle Net Listene using /ade/vikrkuma_new/oracle/bin/tnslsnr
Processing Database instance "orcl": log file /home/oracle/product/10g/startup.log
Processing Database instance "orcl": log file /home/oracle/product/10g/startup.log

>> dbstart 파일 수정
vi /u01/app/oracle/product/10.2.0/db_1/bin/dbstart

아래 부분 찾아 오라클 HOME 수정!

# Set this to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=/u01/app/oracle/product/10.2.0/db_1
--오라클 홈경로의 데이터베이스 경로까지 설정 자동으로 /bin/tnslsnr 경로 추가됨 

/etc/oratab    수정
orcl:/u01/app/oracle/product/10.2.0/db_1:Y

천단위 콤마 + 수정시 다시 콤마 찍기 + 더블클릭시 0으로 초기화 + 붙여넣기시 숫자외 방지(클립보드 변경) + 공백일경우 0 삽입 + 입력가능 수 백만단위 제한.

<%--
/************************************************************/
/*      숫자만 입력해주는 스크립트     */
/************************************************************/
--%>
function getOnlyNumberKey() {

  if ((event.keyCode >=48 && event.keyCode <=57)   // 자판 0~9
   || (event.keyCode == 8)              // back space
 )
  {
   event.returnValue = true;
  } else {
   event.returnValue = false;
  }
}
<%--
/************************************************************/
/*      숫자가 0일경우 공백으로 리턴       */
/************************************************************/
--%>
function zeroNullChange(num) {
 var retNum;
 
  if(num==0){
   retNum = "";
  }else{
   retNum = num;
  }
 return retNum;
}
<%--
/************************************************************/
/*      천단위 콤마찍기         */
/************************************************************/
--%>
function num_format(n) {

   var pre_reg = /[^0-9]/gi;
   n = String(n);
   n = n.replace(pre_reg, '');
 
   var reg = /(^[+-]?\d+)(\d{3})/;   // 정규식
   n = String(n);                    // 숫자를 문자열로 변환
  
   while (reg.test(n))
     n = n.replace(reg, '$1' + ',' + '$2');
   return n;
}
<%--
/************************************************************/
/*      천단위 콤마제거         */
/************************************************************/
--%>
function del_Comma(n) {

   var pre_reg = /[^0-9]/gi;
   n = String(n);
   n = n.replace(pre_reg, '');
   return n;
}
<%--
/************************************************************/
/*      숫자외 문자 걸러내고 클립보드 데이터 변경     */
/************************************************************/
--%>
function notNumberFormatReplace(str){
 
 var reg = /[^0-9]/gi;
 var clipData = window.clipboardData.getData("Text");  //클립보드 데이터 가져오기

 clipData = String(clipData);

 if(window.event.ctrlKey && window.event.keyCode == 86) {
  clipData = clipData.replace(reg, '');
  window.clipboardData.setData('Text',clipData);
  
  return clipData;
 }else{
  return str;
 } 
}

사용법

<%--
/************************************************************/
/*      숫자외 문자 걸러내고 클립보드 데이터 변경     */
/************************************************************/
--%>
function notNumberFormatReplace(str){
 
 var reg = /[^0-9]/gi;
 var clipData = window.clipboardData.getData("Text");  //클립보드 데이터 가져오기

 clipData = String(clipData);
 clipData = clipData.replace(reg, '');                       //클립보드 데이터 정규식 적용
 window.clipboardData.setData('Text',clipData);      //클립보드 데이터 변경하여 저장
 
 if(window.event.ctrlKey){
  return str;
 }else if(window.event.ctrlKey && event.ketCode == 86) {
  return clipData;
 }else{
  return str;
 } 
}
사용법
onkeydown="this.value=notNumberFormatReplace(this.value);"

원본 : http://sepiroth11.tistory.com/44

unicode.html

솔라리스를 처음 설치하고 루트권한으로 접속이 안되서 당황했는데 아래와 같이 설정하면 접속이 가능하다.

[텔넷설정]
vi /etc/default/login
CONSOLE=/dev/console  -> #CONSOLE=/dev/console

[SSH 설정]
vi  /etc/ssh/sshd_config

PasswordAuthentication  no (솔라리스)
PasswordAuthentication  yes (리눅스)

SSH 재시작
svcadm restart sshd

var screenX = window.screenLeft;   //칭의 왼쪽 기준위치
var screenY = window.screenTop;   //창의 상단 기준위치
var docWidth = document.documentElement.clientWidth;  //문서 X 크기
var docHeight = document.documentElement.clientHeight;   //문서Y의크기

var poupX = ((docWidth/2)/2)+screenX;
var poupY = ((docHeight/2)/2)+screenY;

window.open("/주소~~~", "", "top="+(screenY+100)+"px, left="+(screenX+200)+"px, width=600, height=500");

설정 바로 적용