MY COMPASS

* Sites to Learn Coding FreeCodeCamp Codecademy Code * Refs Top 10 Websites to Learn Coding DataScience Learning Site Class Central : MOOC Trending Courses Online 45    

* Communication P2 Slack : A messaging app Zoom : Video & Web Conferencing Service Github : Trello : Project Management Software

R 환경 구축  https://cran.r-project.org/ https://www.rstudio.com/ 저자 URL facebook : www.facebook.com/hellodatascience blog : www.hellodatascience.com facebook2 : www.facebook.com/group/livingdata github https://github.com/jykim/dbook QS http://quantifiedself.com Reference https://en.wikipedia.org/wiki/Missing_data https://www.kaggle.com/competitions OECD - https://data.oecd.org 국가 통계 포털 - http://kosis.kr

1. repo url   http://mirrorlist.centos.org/?release=6&arch=x86_64&repo=os 2. vi /etc/yum.repos.d/CentOS-Base.repo baseurl modify 3. yum clean all http://www.unixmen.com/ifconfig-command-found-centos-7-minimal-installation-quick-tip-fix/ * proxy config vi /etc/yum.conf proxy=http s ://x.x.x.x:8080  

http://intomysql.blogspot.kr/2010/12/mysql-mycnf.html mysqld --verbose --help | grep -A 1 'Default options'

mysql > SET sql_mode = 'ONLY_FULL_GROUP_BY' ;

http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ http://blog.naver.com/PostView.nhn?blogId=endstar7&logNo=100124603859&categoryNo=32&viewDate=&currentPage=1&listtype=0

http://mechanics.flite.com/blog/2013/09/30/how-to-do-an-upsert-in-mysql/ merge =>insert ... on duplicate key update

http://rocksea.tistory.com/213 http://dhplanner.blogspot.kr/2009/07/mysql-rownum-%EA%B5%AC%ED%98%84%ED%95%98%EA%B8%B0.html DBMS별 rownum사용법. 개발을 하다보면 Paging처리나 그외 정렬의 문제로 Rownum을 써야 하는 상황이 오게 됩니다. Oracle을 사용 해 본 사람이라면 일상적으로 사용하겠지만 그 외 DBMS만 사용해본 개발자 라면 Rownum이 생소하게 느껴 질 수 도 있기 떄문입니다. (Paging 구현시 LIMIT 와 OFFSET 으로 구현이 가능하기 떄문에.) 그래서 자주쓰는 DBMS에 rownum사용법에 대해 정리 해 보았습니다. User Table name  company_no  홍길동  4  신길동  2  심길동  3  고길동  1  - ORACLE 일단 Oracle에선 너무나 기본적으로 사용하기 떄문에 많은 분들이 아실거라 생각 됩니다만 정리 하는 차원에서 설명합니다. SELECT name,  ROW_NUMBER() OVER   (ORDER BY company_no ASC) no FROM USER; OR SELECT  ROWNUM , a.* FROM ( SELECT * from USER order bu company_no ASC ) a - MySQL SELECT   @RNUM := @RNUM + 1 AS NO , a.* FROM   (     SELECT *     FROM user   ORDER BY company_no ASC   ) a ,     ( SELECT @R...

-- The SHOW_SPACE routine prints detailed space utilization information for database segments.  desc show_space PROCEDURE show_space  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  P_SEGNAME                      VARCHAR2                IN  P_OWNER                        VARCHAR2                IN     DEFAULT  P_TYPE                         VARCHAR2                IN     DEFAULT  P_PARTITION                ...

--The mystat.sql and its companion, mystat2.sql, are used to show the increase in some Oracle “statistic” before and --after some operation. -- mystat.sql set echo off set verify off column value new_val V define S="&1" set autotrace off select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) = lower('&S') / set echo on --mystat2.sql -- mystat2.sql reports the difference (&V is populated by running the first script, mystat.sql—it uses the -- SQL*Plus NEW_VAL feature for that. It contains the last VALUE selected from the preceding query): set echo off set verify off select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) = lower('&S') / set echo on -- For example, to see how much redo is generated by an UPDATE statement, we can do the following: @mystat "r...

WITH    DBA_WITH_SNAPSHOT AS     (         SELECT  MIN(SNAP_ID) AS BEGIN_SNAP_ID, MAX(SNAP_ID) AS END_SNAP_ID         FROM    DBA_HIST_SNAPSHOT         WHERE   INSTANCE_NUMBER = 1         AND     END_INTERVAL_TIME   >= TO_DATE('2016/01/18 09:00:00', 'YYYY/MM/DD HH24:MI:SS')         AND     BEGIN_INTERVAL_TIME <= TO_DATE('2016/01/18 10:00:00', 'YYYY/MM/DD HH24:MI:SS')        )       ,  DBA_WITH_SQLSTAT AS     (         SELECT  /*+ INLINE PARALLEL(4) */                 A.*         FROM    DBA_HIST_SQLSTAT    A               , DBA_WITH_SNAPSHOT   X         WHERE   1 = 1     ...

/* Runstats Runstats is a tool to compare two different methods of doing the same thing and show which one is superior. You supply the two different methods and Runstats does the rest. Runstats simply measures three key things: • Wall clock or elapsed time: This is useful to know, but not the most important piece of information. • System statistics: This shows, side by side, how many times each approach did something (such as a parse call, for example) and the difference between the two. • Latching: This is the key output of this report. */ --In order to use Runstats, you need to set up access to several V$ views, create a table to hold the statistics, and --create the Runstats package. You will need access to four V$ tables (those magic, dynamic performance tables): --V$STATNAME, V$MYSTAT, V$TIMER and V$LATCH. Here is a view I use: create or replace view stats as select 'STAT...' || a.name name, b.value from v$statname a, v$mystat b where a.statistic# = b.st...

PL/SQL 1 PL/SQL 2

https://www.toadworld.com/platforms/oracle/w/wiki/4952.script-to-generate-ddl-for-synonyms REM ****************************************************************** REM REM FUNCTION: Generate DDL for synonyms. REM REM ****************************************************************** UNDEF ENTER_OWNER_NAME UNDEF ENTER_SYNONYM_NAME SET long 1000 SET serveroutput on SET verify off lines 132 DECLARE v_output CLOB := NULL; v_owner VARCHAR2 (30) := '&&ENTER_OWNER_NAME'; v_synonym_name VARCHAR2 (30) := '&&ENTER_SYNONYM_NAME'; BEGIN DBMS_OUTPUT.put_line ('DDL For Database Synonyms'); FOR tt IN (SELECT owner, synonym_name FROM dba_synonyms WHERE owner LIKE v_owner AND synonym_name LIKE v_synonym_name) LOOP SELECT DBMS_METADATA.get_ddl ('SYNONYM', tt.synonym_name, tt.owner) INTO v_output FROM DUAL; DBMS_OUTPUT.put_line (v_output); ...

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ( OWNNAME => 'USER' , CASCADE => TRUE , NO_INVALIDATE => FALSE , DEGREE => 8 );

아래와 같이 sqldeveloper.conf 파일에 옵션을 추가하면  모든 메뉴를 영문으로 볼 수 있습니다.  ##sqldeveloper\sqldeveloper\bin\sqldeveloper.conf AddVMOption -Duser.language=en AddVMOption -Duser.country=US

impdp username directory=directory_name content=metadata_only dumpfile=dump_file_name.dmp sqlfile=sql_file_name.sql  grep -i 'tablespace' sql_file_name.sql | sort | uniq

-- EVENT MONITORING             SELECT  EVENT       , SUM(TIME_WAITED) SUM       , ROUND(RATIO_TO_REPORT(SUM(TIME_WAITED)) OVER (), 2) AS RATIO FROM    GV$ACTIVE_SESSION_HISTORY WHERE   SAMPLE_TIME BETWEEN SYSDATE - 3/1440 AND SYSDATE GROUP BY         EVENT ORDER BY         SUM DESC         ; -- FIND SQL_ID FROM EVENT SELECT  SQL_ID, EVENT       , SUM(TIME_WAITED) SUM       , ROUND(RATIO_TO_REPORT(SUM(TIME_WAITED)) OVER (), 2) AS RATIO FROM    GV$ACTIVE_SESSION_HISTORY WHERE   SAMPLE_TIME BETWEEN SYSDATE - 3/1440 AND SYSDATE AND     EVENT = 'latch: cache buffers chains' GROUP BY         SQL_ID, EVENT ORDER BY         SUM DESC ; -- FIND SQL TEXT FROM SQL_ID SELECT SQL_FULLTEXT FROM V$SQLAREA WHERE SQL_ID = '5frpptd8mtvx0' ...

SELECT EVENT, A.*     FROM V$ACTIVE_SESSION_HISTORY A    WHERE     SAMPLE_TIME BETWEEN TO_DATE ('20160106 120000',                                           'YYYYMMDD HH24MISS')                              AND TO_DATE ('20160106 163000',                                           'YYYYMMDD HH24MISS')          AND EVENT = 'enq: TX - row lock contention' ORDER BY SAMPLE_ID DESC;

SELECT * FROM DBA_HIST_WR_CONTROL; SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC;

-- AWR SQL Stat History 확인 with w_sqlstat as (     select /*+ inline use_nl(a,b,c) leading(a) index(a (sql_id))*/            a.*,            to_char(b.begin_interval_time,'mm/dd hh24:mi') snap_time     from   dba_hist_sqlstat a          , dba_hist_snapshot b     where  b.dbid             = a.dbid     and    b.instance_number  = a.instance_number     and    b.snap_id          = a.snap_id     and    a.sql_id = '7s4bffkwdq1r4' --    and    a.module like 'BTmapsc090%' --    and    b.instance_number  = 1 --    and    b.dbid = 3107085369 --    and    a.snap_id >= 15817     --and    b.snap_id = 55925     --and   ...

/*  **  특정 모듈에서 수행된 Top SQL 추출하기  */  WITH    DBA_WITH_SNAPSHOT  AS     (          SELECT  MIN(SNAP_ID) AS BEGIN_SNAP_ID, MAX(SNAP_ID) AS END_SNAP_ID          FROM    DBA_HIST_SNAPSHOT          WHERE   INSTANCE_NUMBER = 1          AND     END_INTERVAL_TIME   >= TO_DATE('2016/01/06 16:00:00', 'YYYY/MM/DD HH24:MI:SS')          AND     BEGIN_INTERVAL_TIME <= TO_DATE('2016/01/06 17:00:00', 'YYYY/MM/DD HH24:MI:SS')         )        ,  DBA_WITH_SQLSTAT  AS     (          SELECT  /*+ INLINE PARALLEL(4) */                  A.*          FROM    DBA_HIST_SQLSTAT    A    ...