[Oracle] ROWNUM을 이용하여 MySQL에서 제공하는 LIMIT을 구현해봅시다.

오라클(Oracle) 2008. 7. 24. 18:56
 
이 망할 놈의 오라클은 LIMIT이 지원되지 않습니다.
LIMIT이 얼마나 편한데!!!
오라클에서 LIMIT과 같은 기능이 필요했습니다.

ROWNUM이라는 것을 알게 되었는데, SELECT를 하게 된 결과에 자동으로 처음부터 끝까지 1부터 숫자를 매겨주는 듯했습니다. 아, 그래서 만약에 10번째꺼부터 20번째꺼를 추출해봐야지 라고 이런 쿼리를 날려봤습니다.

[code]SELECT * FROM BIZLIST WHERE ROWNUM >= 10 AND ROWNUM <= 20[/code]
이렇게 하니까 아무것도 안나오더라구요.
이게 안돌아가는 이유는 ROWNUM이라는 놈이 가상 칼럼이랍니다. 그래서 만약 칼럼이 SELECT될 때 10개가 출력되야하는 상황이라면 첫번째꺼 출력하고 ROWNUM을 붙이고, 두번째꺼 출력하고 ROWNUM을 붙이고, 그러다보니 저 조건에 맞지 않게 됩니다.

첫번째 출력될 놈은 ROWNUM이 1인데, 저 조건에 부합하지 않습니다. 그래서 출력이 안되고, 10번째 출력될 놈도 출력하려고 보니 다 조건에 맞지않아요. 그러다보니 차례대로 다 안나옵니다-_-;

즉 조건에 부합한 놈이 출력되고 난다음에 ROWNUM을 붙여줍니다.
그래서 ROWNUM이 10을 1로 바꿔버리면 ROWNUM이 1인놈부터 20개까지는 출력이 되는것이죠.

제가 설명을 좀 못하니 여기를 참조 하세요 ^^
http://www.oracle.com/technology/global/kr/oramag/oracle/06-sep/o56asktom.html

저걸 악(?) 이용하면 LIMIT처럼 구현할 수 있습니다-_-;
[code]SELECT * FROM (SELECT ROWNUM RNUM, BIZLIST.* FROM BIZLIST WHERE STATUS = 'A') A
WHERE A.RNUM BETWEEN 10 AND 20[/code]
쿼리를 잘 보시면 A테이블을 서브쿼리를 날려서 만들어줍니다. 즉 우리가 10~20까지 추출해야할 데이터를 다 추출해서 A라는 테이블을 만듭니다. 여기서 A테이블을 보면 ROWNUM도 칼럼에 추가를 시킵니다. RNUM이라는 이름으로!
그다음 이 추출된 모든 것이 보면 ROWNUM도 다 붙어있습니다. 그래서 이걸 다시 SELECT해서 RNUM이 10과 20사이인 것을 추출하게 하면 되는것이죠.
A테이블에서 WHERE절은 원하는 조건이니 없어도 되는 것이고-_-;
별 것도 아닌데 거창하게 써 놓은 이유는 제가 이것 때문에 반나절을 고생해서 입니다-_-;
아놔....ㅠㅠ

 
Posted by 머드초보
,

[유틸리티] Oracle SQL Developer 설치 및 사용후기.

유틸리티 활용하기 2008. 7. 22. 09:24
 
기존에 Golden32나 Toad for Oracle을 썼었습니다.
그런데 오라클사에서도 자기들만의 GUI Tools같은 것을 만들었을 것이라는 생각에 찾아보니 Oracle SQL Developer라는 놈이 있더군요.

MySQL에서 제공하는 MySQL Administrator, MySQL Query Browser가 있는데, 자사에서 만드니 더 잘 만드는 것같아요. 뭐 단점이라면 포트가 막혀있는 곳에 접속이 안되는거?-_-; 그럴 땐 phpmyadmin을....-_-;

http://www.oracle.com/technology/software/products/sql/index.html
이곳에서 받을 수 있습니다.

뭐 암튼, 오라클에서 제공하는 SQL Developer라는 놈을 설치해봅시다.
설치랄 것도 없습니다. 그냥 압축풀고 실행하면 됩니다-_-;
JAVA로 만들어져서 JDK가 설치된 피씨거나, JDK가 포함된 버전을 받아서 실행하면 됩니다.

전 JDK가 있어서 없는 버전을 받았습니다. 그러면 처음 실행할 때 java.exe파일이 어디에 있냐고 물어봅니다. 가볍게 설치된 경로를 선택해서 java.exe파일을 선택해줍니다.

File -> New -> Database Connection 하고, 설정에 입력값들이 있는데 맞게 입력합니다.
Username, Password, Hostname, Port 뭐 이런거 입력하고, Save하고, Connect하면 접속이 됩니다.

사용하면서 느낀건데...뭔가 굉장히 불안정해요-_-;
30만건데이터를 파일로 추출하려고 하는데 메모리가 딸려서 다운이 되버렸어요.
지금은 또 그냥 알 수 없이 다운이 됐네요.
아직 좀 더 개선이 필요한 듯 합니다.

가장 맘에 드는 건 sql쿼리 만들 때 개발자들이 좋아라하는 Ctrl + Space를 지원합니다-_-;
사용자 삽입 이미지
또 뭐가 좋은지 확인해봐야지-_-;
 
Posted by 머드초보
,

[Oracle] 오라클 클라이언트 설치 후 GOLDEN32에서 한글이 깨짐현상해결.

오라클(Oracle) 2008. 7. 16. 17:57
 
Toad나 GOLDEN32를 사용하기 위해서는 오라클 클라이언트를 설치해야하는데 이걸 설치하고 GOLDEN32로 접속해서 쿼리를 날려서 검색하면 한글이 나와야 하는 부분이 ???로 나오게 됩니다.

사용자 삽입 이미지

원래 2008년 7월 12일 이라고 나와야하는데-_-; 안 나옵니다.
이게 서버쪽 케릭터셋이랑 클라이언트 케릭터셋이 안맞아서 그런데요. 서버는 한글이 된다면 KOREAN_KOREA.KO16KSC5601이걸로 되어있을껍니다-_-; 저도 오라클은 잘 모르지만 이게 맞는 것 같습니다.
그래서 클라이언트쪽에도 맞춰줘야합니다.

윈도우에 설치하셨다면 NLS_LANG을 추가하면 됩니다. 어디다가 하냐면 레지스트리에다가 합니다.
시작 -> 실행 -> regedit 실행

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
여기로 찾아가서 ORACLE에 오른쪽 버튼 누르고, 새로만들기 -> 문자열값 하고
NLS_LANG이라고 씁시다.
더블클릭해서 값을 KOREAN_KOREA.KO16KSC5601이라고 합시다.

시도는 안해봤는데 환경변수로 지정해도 될 듯 합니다.
NLS_LANG=KOREAN_KOREA.KO16KSC5601



사용자 삽입 이미지
오....한글이 나와요!!!!


 
Posted by 머드초보
,

[Oracle] TDE(Transparent Database Encryption) 투명한 데이터베이스암호화 체험후기

오라클(Oracle) 2008. 7. 16. 17:15
 
오라클 10G R2부터 TDE라는 기능을 제공합니다.
(참고로 Enterprise 이상의 버전에서만 됩니다. Oracle XE를 설치했는데 안되서 삽질했었습니다-_-)

말그대로 Transparent(투명한) Database Encryption(데이터베이스 암호화) 입니다-_-;
투명하다는 얘기는 암호화가 투명하게 되었다는 얘기인데요.
암호화가 되어서 DATABASE에 저장이 되지만, 암호화가 됐는지 확인 하는 방법은 며느리도 모릅니다.
해당 계정의 사용자는 데이터를 다 볼 수 있어야하니깐요 ^^
데이터베이스를 도난당했을 때 해당계정의 비밀번호를 모르는 이상 데이터베이스를 확인 할 수 없는 것 같습니다.
즉, SQL Injection같이 Application 단에서 발생하는 해킹은....소용없다는 얘기죠.

장점이 있다면 어플리케이션을 변경하지 않아도, 데이터베이스를 암호화 할 수 있습니다.

어쨌든 암호화가 필요해서 체험해봤습니다.
우선 ORACLE 10g이 필요하죠.

전자지갑을 생성해야합니다.
cd $ORACLE_HOME/network/admin
sqlnet.ora파일을 편집해서 아래와 같은 내용을 넣습니다.
ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
   (DIRECTORY=/export/home/oracle/oracle/product/10.2.0/db_2/)))

전자지갑 저장소를 설정하는 듯하네요.

이제 오라클에 접속해봅시다.
-bash-3.00$ sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Jul 16 16:57:08 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

SQL> connect / as sysdba
Connected.
SQL> alter system set key identified by "welcome1";

System altered.

SQL>

이게 마스터키를 생성하는 부분인데요. 마스터키는 단 한번만 생성되어야 한다고 합니다. 다시 생성하게 되면 기존에 암호화 되어있던 데이터를 다시 암호화 해야한다는군요. 무슨 얘기지-_-;

저거대로 따라할라니까 힘들어서-_-; 그냥 insert해서 로그에 안찍히는 것만 보여주도록 해봅시다-_-;
Oracle LogMiner라는 놈을 이용해서 로그를 볼 수 있는데 이놈은 암호화된 데이터를 지원하지 않습니다. 그래서 암호화된 데이터는 보여지지가 않습니다.

테이블을 생성해봅시다.

connect oe/oe
create table cust_payment_info 
  (first_name varchar2(11), 
  last_name varchar2(10), 
  order_number number(5), 
  credit_card_number varchar2(16) ENCRYPT NO SALT,
  active_card varchar2(3));

Table created.
자세히 보면 credit_card_number 부분에만 ENCRYPT NO SALT라는 것을 적용했네요.
이렇게 해버리면 테이블 구조만 바꾸면서 암호화를 할 수 있습니다. 이미 만들어진 어플리케이션은 손대지 않아도 됩니다. 8i에서 제공하는 방법이 찾아보니까 있었는데 그건 키테이블을 따로 만들어서 데이터를 암호화시킨 상태에서 저장해버립니다. 나중에 데이터를 가져올 때 복호화 하고 그런식으로 하더라구요. 그거나 이거나 암호화해서 저장되는 법은 똑같습니다. 

데이터를 insert해봅시다.
예제에는 조낸 많이 삽입하는데 조낸 귀찮으니까 1개만 삽입해봅시다-_-;
SQL> insert into cust_payment_info values
  2    ('Jon', 'Oldfield', 10001, '5446959708812985','YES');

1 row created.

SQL> 

또 예제에서는 뭔가 삽질을 하는데 귀찮으니까 바로 로그확인에 들어갑시다-_-;
oradata에 있는 redo01~3으로 설정해주어야합니다.
SQL> connect / as sysdba;
Connected.
SQL> alter database add supplemental log data;

Database altered.

SQL> REM select member as LOG_FILE_LOCATION from v$logfile;
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE ('/export/home/oracle/oradata/orcl/redo03.log', DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE ('/export/home/oracle/oradata/orcl/redo02.log', DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE ('/export/home/oracle/oradata/orcl/redo01.log', DBMS_LOGMNR.ADDFILE);

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR (options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

PL/SQL procedure successfully completed.

SQL> select sql_redo from v$logmnr_contents where
  2  table_name = 'CUST_PAYMENT_INFO' and operation='INSERT';

SQL_REDO
--------------------------------------------------------------------------------
insert into "OE"."CUST_PAYMENT_INFO"("FIRST_NAME","LAST_NAME","ORDER_NUMBER","CR
EDIT_CARD_NUMBER","ACTIVE_CARD") values ('Jon','Oldfield','10001',Unsupported Ty
pe,'YES');


SQL> 

결과에 암호화된 칼럼은 Unsupported Type이라고 뜨는군요.
잘은 모르지만 암호화가 된 듯 합니다.

암튼 손쉽게 암호화 할 수 있게 해놨군요. 
어플리케이션에 손대지 않는 방법을 찾다가 이짓까지 삽질해보는군요.

            





  





  



  



  










 






					

					

						Posted by 머드초보
					


					

					

							,
							
					


					

					

				

			
                    
                
                    
				

					

						
[Oracle XE] Oracle Apex(Application Express) 원격에서 접속하기.

						오라클(Oracle) 
						2008. 7. 3. 22:16
						
					


					







 






						            



            
Oracle XE에서는 기본적으로 APEX를 제공합니다.
웹으로 db를 컨트롤할 수 있는 웹애플리케이션입니다. phpmyadmin같은 거죠.
그런데 이 apex가 디폴트로는 설치된 컴퓨터에서만 접속이 가능합니다.
http://www.oracle.com/technology/global/kr/products/database/application_express/html/apex_and_xe.html
여기에서 5번 항목을 보면, 
5. Oracle APEX는 기본적으로 Oracle Database XE가 설치된 컴퓨터에서만 접근이 가능합니다. 관리자는 Database Home Page에서 원격 사용자의 접근을 활성화할 수 있습니다.
라고 되어있습니다. 
저기서 원격 사용자의 접근을 활성화하는 것은 apex에서 해야한다는 얘기인데....-_-;
X-Window가 안깔린 리눅스에서 설치를 해버리니 apex에 접속할 수가 없습니다.
리눅스에서 netstat -an 해보면
tcp        0      0 127.0.0.1:8081          0.0.0.0:*               LISTEN
이렇게 되어있습니다. 
즉 로컬에서만 접속되도록 8081이 오픈이 되어있네요.

리눅스에 웹브라우저가 설치가 되어있다면, system계정으로 로그인해서 
관리 -> http엑세스관리 -> "로컬 서버와 원격 클라이언트에서 사용할 수 있음" 선택 확인.
하면 됩니다. 

리눅스에 웹브라우저가 없다면 sqlplus로 직접 접속해서 설정을 변경할 수 있습니다.
root@ubuntu:~# sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on 목 7월 3 22:11:29 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

사용자명 입력: system
암호 입력: 

다음에 접속됨:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> 
저렇게 설정하면 외부에서 접근이 가능합니다.

netstat -an하면 
tcp        0      0 0.0.0.0:8081            0.0.0.0:*               LISTEN
아이피가 변경이 되어있네요 ^^


            





  





  



  



  










 






					

					

						Posted by 머드초보
					


					

					

							,
							
					


					

					

				

			
                    
                

			
				
			
		

		


		
		


		
	





  
티스토리툴바