Private2018.06.25 09:08



'Private' 카테고리의 다른 글

node-red oracledb  (0) 2018.06.25
서버 용량 산정 / 성능 측정 / tpmc / bops / tpc / tpc-h  (0) 2013.05.10
오라클 접속 ORA-12505 에러 관련  (0) 2013.05.02
Magicar AF BRONZE (매직카 브론즈)  (0) 2011.11.30
압력 밥솥 구매 ... 예정  (0) 2011.11.26
아놔 가습기  (3) 2011.11.22
Posted by 하이주니
Database/ORACLE2018.02.04 17:37



출처 : http://ukja.tistory.com/232 에서 퍼온 자료입니다.



특정 Query를 수행한 후, 그 결과를 가로 형태가 아닌 세로 형태로 보기 좋게 출력하고 싶다. Tom Kyte가 멋지게 이 작업을 해냈는데, 핵심은 DBMS_SQL 패키지를 이용한 Dynamic SQL에 있다. 

create or replace procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
begin
      dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns
    ( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column
        (l_theCursor, i, l_columnValue, 4000);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value
            ( l_theCursor, i, l_columnValue );
            dbms_output.put_line
            ( rpad( l_descTbl(i).col_name, 30 )
              || ': ' ||
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
exception
    when others then
     raise;
end;
/


약간의 귀찮은 코딩이 필요하지만 그 효과는 입이 딱 벌어질 정도이다. 다음과 같이 아주 보기 편하게 데이터를 출력할 수 있다. 


SYSTEM> grant execute on print_table to public;

SYSTEM> create public synonym print_table for scott.print_table;



UKJA@ukja116> set serveroutput on
UKJA@ukja116> 
UKJA@ukja116> exec print_table('select * from v$session where sid = userenv(''sid'')');
SADDR                         : 35065B10                                        
SID                           : 127                                             
SERIAL#                       : 557                                             
AUDSID                        : 6755975                                         
PADDR                         : 35A94D88                                        
USER#                         : 88                                              
USERNAME                      : UKJA                                            
COMMAND                       : 3                                               
OWNERID                       : 2147483644                                      
TADDR                         :                                                 
...                             
CREATOR_SERIAL#               : 140                                             
-----------------                                                               

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20


참고로, 나는 print_table 함수를 접하는 순간 Toad가 더 이상 불필요하게 되었다. 

DBMS_SQL 패키지를 이용한 Dynamic SQL 구현은 너무 강력하다. 아래는 내가 실제로 테스트를 할 때 많이 사용하는 간단한 PL/SQL Script이다. Column이 50개 이상 존재하는  V$SQL_SHARED_CURSOR 뷰에서 값이 'Y'인 것만 뽑아서 예쁘게 출력하고 싶기 때문이다. 

-- my shared cursor
/*
declare
  c         number;
  col_cnt   number;
  col_rec   dbms_sql.desc_tab;
  col_value varchar2(4000);
  ret_val    number;
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,
      'select q.sql_text, s.*
      from v$sql_shared_cursor s, v$sql q
      where s.sql_id = q.sql_id
          and s.child_number = q.child_number
          and q.sql_text like ''&1''',
      dbms_sql.native);
  dbms_sql.describe_columns(c, col_cnt, col_rec);

  for idx in 1 .. col_cnt loop
    dbms_sql.define_column(c, idx, col_value, 4000);
  end loop;


  ret_val := dbms_sql.execute(c);

  while(dbms_sql.fetch_rows(c) > 0) loop
    for idx in 1 .. col_cnt loop
      dbms_sql.column_value(c, idx, col_value);
      if col_rec(idx).col_name in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS',
                    'CHILD_NUMBER', 'SQL_TEXT') then
        dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
                ' = ' || col_value);
      elsif col_value = 'Y' then
        dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
                ' = ' || col_value);
      end if;

    end loop;

    dbms_output.put_line('--------------------------------------------------');

   end loop;

  dbms_sql.close_cursor(c);

end;
/


만일 이러한 수고없이 V$SQL_SHARED_CURSOR 뷰를 보려고 하면, 곧 좌절하고 말 것이다. 아래 데이터를 보면서 좌절감을 잠깐 맛보자...

UKJA@ukja116> col sql_id new_value v_sql_id
UKJA@ukja116> 
UKJA@ukja116> select sql_id from v$sql where sql_text like 
    'select /* cursor_share */ * from t1%';

SQL_ID                                                                          
-------------                                                                   
2zu6xb9130t89                                                                   

Elapsed: 00:00:00.07
UKJA@ukja116> 
UKJA@ukja116> set serveroutput on
UKJA@ukja116> exec print_table( -
        'select * from v$sql_shared_cursor where sql_id = ''&v_sql_id''');
SQL_ID                        : 2zu6xb9130t89                                   
ADDRESS                       : 2867E250                                        
CHILD_ADDRESS                 : 2FABDDD8                                        
CHILD_NUMBER                  : 2                                               
UNBOUND_CURSOR                : N                                               
SQL_TYPE_MISMATCH             : N                                               
OPTIMIZER_MISMATCH            : N                                               
OUTLINE_MISMATCH              : N                                               
STATS_ROW_MISMATCH            : N                                               
LITERAL_MISMATCH              : N                                               
FORCE_HARD_PARSE              : N                                               
EXPLAIN_PLAN_CURSOR           : N                                               
BUFFERED_DML_MISMATCH         : N                                               
PDML_ENV_MISMATCH             : N                                               
INST_DRTLD_MISMATCH           : N                                               
SLAVE_QC_MISMATCH             : N                                               
TYPECHECK_MISMATCH            : N                                               
AUTH_CHECK_MISMATCH           : N                                               
BIND_MISMATCH                 : N                                               
DESCRIBE_MISMATCH             : N                                               
LANGUAGE_MISMATCH             : N                                               
TRANSLATION_MISMATCH          : N                                               
ROW_LEVEL_SEC_MISMATCH        : N                                               
INSUFF_PRIVS                  : N                                               
INSUFF_PRIVS_REM              : N                                               
REMOTE_TRANS_MISMATCH         : N                                               
LOGMINER_SESSION_MISMATCH     : N                                               
INCOMP_LTRL_MISMATCH          : N                                               
OVERLAP_TIME_MISMATCH         : N                                               
EDITION_MISMATCH              : N                                               
MV_QUERY_GEN_MISMATCH         : N                                               
USER_BIND_PEEK_MISMATCH       : N                                               
TYPCHK_DEP_MISMATCH           : N                                               
NO_TRIGGER_MISMATCH           : N                                               
FLASHBACK_CURSOR              : N                                               
ANYDATA_TRANSFORMATION        : N                                               
INCOMPLETE_CURSOR             : N                                               
TOP_LEVEL_RPI_CURSOR          : N                                               
DIFFERENT_LONG_LENGTH         : N                                               
LOGICAL_STANDBY_APPLY         : N                                               
DIFF_CALL_DURN                : N                                               
BIND_UACS_DIFF                : N                                               
PLSQL_CMP_SWITCHS_DIFF        : N                                               
CURSOR_PARTS_MISMATCH         : N                                               
STB_OBJECT_MISMATCH           : N                                               
CROSSEDITION_TRIGGER_MISMATCH : N                                               
PQ_SLAVE_MISMATCH             : N                                               
TOP_LEVEL_DDL_MISMATCH        : N                                               
MULTI_PX_MISMATCH             : N                                               
BIND_PEEKED_PQ_MISMATCH       : N                                               
MV_REWRITE_MISMATCH           : N                                               
ROLL_INVALID_MISMATCH         : N                                               
OPTIMIZER_MODE_MISMATCH       : N                                               
PX_MISMATCH                   : N                                               
MV_STALEOBJ_MISMATCH          : N                                               
FLASHBACK_TABLE_MISMATCH      : N                                               
LITREP_COMP_MISMATCH          : N                                               
PLSQL_DEBUG                   : N                                               
LOAD_OPTIMIZER_STATS          : N                                               
ACL_MISMATCH                  : N                                               
FLASHBACK_ARCHIVE_MISMATCH    : N                                               
LOCK_USER_SCHEMA_FAILED       : N                                               
REMOTE_MAPPING_MISMATCH       : N                                               
LOAD_RUNTIME_HEAP_FAILED      : N                                               
-----------------                                                               

PL/SQL procedure successfully completed.


너무 많은 데이터때문에 분석하는 시간이 즐거운 시간이 아니라 고생스러운(주로 눈이) 시간이 되어 버린다. 하지만 다음과 같이 간단하게 이 문제를 해결할 수 있다. 

UKJA@ukja116> @shared_cursor 'select /* cursor_share */%'
SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 = 
:b1                                                                             
SQL_ID                         = 2zu6xb9130t89                                  
ADDRESS                        = 2867E250                                       
CHILD_ADDRESS                  = 2DCB06D0                                       
CHILD_NUMBER                   = 0                                              
OPTIMIZER_MODE_MISMATCH        = Y                                              
--------------------------------------------------                              
SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 = 
:b1                                                                             
SQL_ID                         = 2zu6xb9130t89                                  
ADDRESS                        = 2867E250                                       
CHILD_ADDRESS                  = 2DD0DB6C                                       
CHILD_NUMBER                   = 1                                              
BIND_MISMATCH                  = Y                                              
--------------------------------------------------                              
SQL_TEXT                       = select /* cursor_share */ * from t1 where c1 = 
:b1                                                                             
SQL_ID                         = 2zu6xb9130t89                                  
ADDRESS                        = 2867E250                                       
CHILD_ADDRESS                  = 2FABDDD8                                       
CHILD_NUMBER                   = 2                                              
--------------------------------------------------                              

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04


이런 간단하지만 아름다운 결과들이 엔지니어라면 누구나 남을 한번쯤은 놀라게 할 만한 프로그래밍 실력이 필요한 이유이다. 



출처: http://ukja.tistory.com/232 [오라클 성능 문제에 대한 통찰 - 조동욱]

출처: http://ukja.tistory.com/232 [오라클 성능 문제에 대한 통찰 - 조동욱]

Posted by 하이주니
Database/ERWIN자료2018.01.25 09:43


이번에 7.3 으로 ER Win Version UP 했숨다~! 그거에 맞춰서 DB Oracle 에는 테이블에 Comment 컬럼이 있어요.
토드에서 테이블명 입력 후 F4 키를 누르시면 Table Description 을 볼 수 있습니다



이제 저녀석을 Reverse 해서 저 Comment 값을 Logical 로 변경하는 ER Win 작업을 해 보겠습니다.



* Reverse Engineer 를 사용하여 DB 내용 값을 긁어오기

1. Tools - Reverse Engineer.. 를 선택합니다.



2. 어떤 타입의 DB 내용과 Logical 도 함께 긁어올 것인지 Physical 만 긁어 올 것인지 선택합니다.

 Oracle 10g 이므로 Oracle 을 선택 하였습니다 ^^
Next 를 누릅니다.



3. Option 을 선택 합니다.

전, 선택된 사용자만 가져올 것이기 떄문에 Owners 를 선택하여 사용자 스키마 명을 써 주었습니다

Infra 탭에서는 Primary Key(주키) 값 과 Relations (관계) 를 함께 보여줄 것이기 떄문에 체크 합니다.
Physical 및 Logical 의 이름은 모두 대문자로 볼 것 이기 떄문에 UPPER 를 선택하였습니다.

설정이 끝나면 Next 누릅니다.



4. Oracle Connection 화면 창이 뜨네요.

Database : Oracle 10g/11g  (오라클 10g 이므로..)
Authentication : Database Authentication ( 디비 사용자 할꺼임 -0-)
User Name : 사용자 명
Password : 패스워드

Connection String : SID
 명(자기 로컬일 경우)을 써 줍니다.

외부 데이터로 붙을 때에는  Connection String : nerv.kr/xe 이런식으로 써 줍니다.

< 이전 ERWin 4.1 버전에는 ip 주소 까지 써주었는데... 이번 7버전은 없네요 -_-;;; 
tns.ora 파일에서 설정해 준 값으로 가나 봅니다..>

설정이 끝나면 Connect 를 누릅니다.



5. Reverse Engineer 의 상태 값이 뜨네요... 막~ 읽힐꺼예요.. 조금 시간적 여유를 가지세요! ^-^



6. 중간 중간 물어봅니다. 아까 Infra 탭에서 Primary Key 와 Relation 에 대한걸 물어보네요... Yes 를 누릅니다.



7. 쨔-잔~!! ERD 가 뽑아져 나왔네요! ^-^




자, 바로 이어서 Logical 을 Comment 로 바꾸는거 나갑니다~! ^-^



* ERD Comment 를  Logical 로 변경하기

8. Logical 로 변경할 테이블을 선택 합니다. 
(선택하면 아래처럼 빨간 네모 박스처럼 선택된 테이블은 윗대가리가 까맣네요! ^-^)
전체 선택 할꺼면 Ctrl + A



9. 다음엔 무슨작업을 할꺼냐면... 중요!!!!!!!!

Logical 변경 값에 따라 Physical 값도 같이 변경 되기 때문에 둘을 분리 하는 작업입니다.
위에서 테이블을 선택 한 다음 마우스 오른쪽을 누른 후 Harden Physical Names 를 선택합니다.

그럼 Logical 이름과 Physical 이름이 따로 가게 됩니다 ^^



상태 창에 다음과 같은  Action 이 쭈욱~ 떨어지게 됩니다 ^^



10. 뭐가 변경되었는지 궁금하신지 보여 드리겠습니다. 

Model - Columns.. 를 선택합니다.



11. Rename  을 선택합니다.



12. Harden Physical Names 을 선택 하기 이전에는 다음과 같이 Attrivute* 이렇게 " * " 표시 모양이 있는데 Harden Physical Names  을 선택한 후에는 " * " 표시가 없습니다 ^^

변경전 >>


변경후>>


13. Physical 을 Logical 로 변경 합니다 (단축기는 Ctrl + Up & Down)



14.  Model - Domain Dictionary .. 를 선택합니다.



15. 
Edit Mode  : Logical 
Name Inherited by Attribute:* : %ColumnComment 
로 설정 합니다.

Name Inherited by Attrbute 의 값은 Macro Toolbox 를 누르면 선택이 가능합니다.



Attribute Macro  에 보면, 



%ColumnComment 를 선택 한 후 Insert Macro 한 후 Close  합니다.



16.  다음과 같이 %ColumnComment 만을 남겨두고 %AttDomain 이 남아 있다면 지우고 OK 를 누릅니다. ^^



17. Model - Attributes.. 를 선택 합니다. (계속 Logical 모드임)



18. 메시지가 뜨네요~! ^^ 확인을 누릅니다.



19. Reset 을 누릅니다.




20. 

Resetting Attribute : Reset all attributes in model  (ERD 전체 변경)
Select Properties to Reset : Name

을 선택 한 후 OK 를 누릅니다.

Only attribute PAGE_COMPONENT_ID 라고 되어 있는건 
: (해당 컬럼 값의 속성만 변경 - PAGE_COMPONENT_ID 컬럼 )
All attribute of entity PAGE_COMPONENT 라고 되어 있는건 
: (해당 테이블에 속해있는 속성만 변경 - PAGE_COMPONENT 테이블)



21. 테이블 내에서 중복되는 이름(Comment) 에 대해서 나옵니다. 
이때 Help 는 누르면 자세한 도움말을 볼 수 있습니다. (Ok, cancle, rename 에 관하여 영문 설명이 나옵니다)
(중복 안되게 변경해 주면 됩니다)



22. 이름으로 변경 된 화면을 감상 하실수 있습니다 ^-^ OK 를 눌러주세요!



23. Logical 일때의 모습입니다.



24. Physical 일때의 모습입니다.



테이블 명은 안바뀌는게 조금 아숩네요 -ㅅ-
혹, 테이블 명도 Comment 로 변경되는걸아시는분은 댓글 남겨 주세요~! ^_^

Comment 로 변경 안되더라도, 생 작업으로 변경 해 보도록 하겠습니다.


25. Model - Attribute 를 선택합니다.
Entity 에 변경할 테이블을 선택 한 후 Entity 이름 옆의 .... 를 누릅니다.



26. Name 이 있죠?!
저 Name 을 한글로 변경 한 후 OK 누르시면 됩니다 ^-^




출처: http://joke00.tistory.com/168 [Smile virus]




Harden Logical Names를 선택해 준 이유는 아래도 써 놨듯이, 이 작업을 수행하지 않으면, Column의 Comment가 변경되면 논리명도 자동으로 변경되기 때문입니다. ERwin r7에서는 다행이 이 기능을 지원하기 때문에 이후의 Comment 변경이 논리명에 영향을 주지 않도록 할 수 있습니다.

논리명과 물리명에 대한 수정 작업을 위해서는 다신 논리모델과 물리모델에서 전체를 선택하고 Unharden Logical Name과 Unharden Physical Name을 선택하면 됩니다 ^^

출처: http://joke00.tistory.com/168 [Smile virus]

'Database > ERWIN자료' 카테고리의 다른 글

ERWin Reverse ER  (1) 2018.01.25
Posted by 하이주니