노스웨일즈 김씨 시조의 블로그

Alter table <테이블명> rename column <기존 컬럼명> to <바뀔 컬럼명>;

예전에는 안됐는데....

ㅋㅋㅋ 생각해보니 내가 Oracle 8i 때 많이 쓰고 그 다음 부터는 그닥 많이 쓰지 않았으니 그사이에 이런 기능 추가 되는건 당연하지 ... 벌써 11g 인데

본사에서 받은 dump file import 후 Report 화면에 데이터가 하나도 나오지 않아 확인해 보니 reporting 계정의 function / procedure들이 모두 invalid 상태이다.

EnterpriseManager에서 해당 function 을 일일이 compile을 시켜도 모두

PL/SQL: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

이런 오류만 떨어 진다..

헌데 해당 Table을 SQLPLUS를 통해 확인해 보면 정상적으로 Access 가능하다.

( 계정 별로 Table을 생성한 후 서로 다른 계정 테이블을 Synonym 으로 연결해서 쓰는 구조.. )

뭔가 본사에서 export를 잘 못 하거나 아님 내가 import를 잘 못하거나 두개 중 하나일텐데...

http://database.sarang.net/?inc=read&aid=30895&criteria=oracle&subcrit=&id=&limit=20&keyword=function+ORA-00942&page=1

이 글을 보다 보니 답이 나온다.

import하다가 문제가 좀 생겨서

primary 계정이랑 reporting 계정을 삭제하고 다시 생성했는데

primary 계정 import 시 grant 관련 처리가 끝난 후에 reporting 계정이 생성되어

grant 관련 option이 적용되지 않아서 생긴 문제 였다.

결국 두개 계정 다시 drop 후 ( 물론 primary만 drop 해도 되기는 했지만 )

다시 하나씩 계정 생성 후 import하니 잘 된다.

결국 결론은 SQLPLUS에서 접속이 되는 거랑

function 내에서 해당 테이블을 참조하는 권한은 조금씩 다르다는 결론을 내릴 수 있을 것 같다.

본사에서 샘플 데이터베이스를 받아서 import를 시킬려고 하는데 계속

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully

오류가 발생한다.
FTP로 파일 다운 받을 때 ASCII TYPE으로 받아서 그런가 했더니

알고 보니 expdp tool을 이용해서 받은 파일이다.

인터넷으로 뒤져 보니 10g 부터 기존 exp/imp tool의 단점을 보안하기 위해 나온 tool이다.

기존 exp/imp는 client 중심의 tool이었는데 expdp/impdp는 server 중심의 tool이다.

뭐.. exp/imp 단점을 보안 했다고 하는데 솔직히 어떤 단점을 말하는 건지 모르겠다.
하긴 oracle에서 단점이라고 얘기하는 걸 보면 뭔가 문제가 있었나.^^

예제를 보면 exp/imp를 GUI 환경에서 사용할 수 있도록 해 주기 위해 새로 만들어낸 tool이 아닌가 싶다.

server 중심이다 보니 사용하는 방법이 조금 복잡하다.

exp/imp는 일단 command 한번으로 모든 작업을 처리 했으나

expdp/impdp는 일단 working 디렉토리를 db에 등록한 후 expdp/impdp를 사용해야 한다.

1. working directory를 database에 등록한다.
sqlplus>create directory datadir1 as '/home/oracle/wkdir';
2. expdp/impdp를 사용할 사용자 계정에 working directory에 대한 read, write 권한을 추가한다.
sqlplus>grant read,write on directory datadir1 to sh;
3. expdp를 실행한다.
shell>> expdp 계정/패스워드 directory=datadir1 schema=계정 dumpfile=XXX.dmp logfile=YYY.log
해당 계정의  데이터를 XXX.dmp 파일로 dump 수행한다.

imp 시...
1. working directory를 database에 등록한다.
   ( 위와 동일한 방법으로 working directory를 등록한다. )
2. working directory에 대한 read, write 권한을 추가한다.
3. impdp를 실행한다.
shell>> impdp 계정/패스워드 directory=datadir1 dumpfile=XXX.dmp logfile=CCC.log


참고 자료 : http://www.oracle.com/technology/obe/obe10gdb/storage/datapump/datapump.htm

Oracle에서 많이 만들다가 이번에 만들려고 하니 생각이 잘 안난다는...-.-

특히 Mysql은 어떻게 해야 할지 머리 굴리다가

대충 아래와 같이 돌려서..^^

create table copy_t ( num int);

insert into copy_t ( num )
SELECT rnum
FROM ( SELECT @RNUM := @RNUM + 1 AS rnum
            FROM ( SELECT @RNUM:=0 ) R, 
                       XXX ( 아무 테이블이나 ROW가 많은 테이블 ) ) as dt;


create table copy_ymd ( ymd_date date, ymd_char char(8));

insert into copy_ymd ( ymd_date )
SELECT ADDDATE('2009-03-01', num )
FROM copy_t;

update copy_ymd SET ymd_char = REPLACE(concat(str_to_date(ymd_date,'%Y-%m-%D'),''), '-','');

Report 내에 Pagination 기능을 추가하면서 MySQL과 Oracle 모두를 하나의 소스로 지원하려다 보니 Stored Procedure를 개발하게 되었다.

그러던 중 Output에 Rownum을 출력 해 줘야 하는 issue가 있어서 Output을 바꾸려고 했는데 우리의 인터넷을 보니 MySQL에서 Rownum을 구현할 수 있는 로직이 나와 있었다.


SELECT *
FROM ( SELECT @RNUM := @RNUM + 1 AS NO
            FROM ( SELECT @RNUM:= 0 ) R,
                       ......

결국 한 DB에서 되는 기능은 어떻게서든 다른 DB에서도 구현될 수 있다는. ...ㅋㅋㅋㅋ

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

물론 운영 서버에는 쓰면 안되겠지.^^

뭐.. 나야 현재 테스트 환경이니깐^^

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' AUTOEXTEND OFF;

위에는 AutoExtend 환경을 끄는 SQL

이번에 본사 Database를 dump 받아 서 import 시키는 중에 자꾸 size 오류가 나서
file 사용량을 보고 싶으나.... Toad가 없는 관계로 query를 인터넷으로 찾아 보니
아래와 같이 나온다.


SELECT b.file_name "FILE_NAME", -- DataFile Name
       b.tablespace_name "TABLESPACE_NAME", -- TableSpace Name
       b.bytes / 1024 / 1024 "TOTAL SIZE(MB)", -- 총 Bytes
       ((b.bytes - sum(nvl(a.bytes,0)))) / 1024 / 1024 "USED(MB)", -- 사용한 용량
       (sum(nvl(a.bytes,0))) / 1024 "FREE SIZE(KB)", -- 남은 용량
       (sum(nvl(a.bytes,0)) / (b.bytes)) * 100 "FREE %", -- 남은 %
       round((b.bytes / 1024 / 1024) - ((((b.bytes - sum(nvl(a.bytes,0)))) / 1024 / 1024) + 150)) ForFree,
       'alter database datafile '''||b.file_name||''' resize '||round((((b.bytes - sum(nvl(a.bytes,0)))) / 1024 / 1024) + 150)||'M;' str
FROM  DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.file_id(+) = b.file_id
  AND (b.tablespace_name like 'CSR%' or b.tablespace_name like 'IRIS%') -- CSR이나 IRIS만 선택
GROUP BY b.tablespace_name, b.file_name, b.bytes
ORDER BY b.tablespace_name;

그동안 Oracle을 4년 넘게 사용 안하다 보니 다 까먹은 것 같다.-.-

흠.. 이번에 DBGuide 교육 당첨되서 다시 한번 좀 써 봤으면 좋겠는데..^^

시스템에서 에러 메시지를 출력해 준다.

내용은... Client module을 업그레이드 하라고..

( 그동안 설정 잘 해 놓고 쓰고 있었는데 개발 서버 DB가 맛가는 바람에 다시 설정중에 발생했다. )

예전에는 mysql database의 user 테이블에 저장된 password를 직접 update하였는데

Internet 뒤지다 보니

SET PASSWORD FOR 'database_user'@'localhost' = OLD_PASSWORD('password');

라는 Query가 있다.

뭐.. 해보니 이게더 편한것 같긴 하다...

뭐 물론 user 테이블을 update하는게 Multi process할때는 좋을 것 같긴 하지만..>^^

뭐.... 그냥 개발하면 이런 Case가 없겠지만...

Package 제품이다 보니 사용자쪽에서 Varchar2로 선언된 Field에 Numeric Data를 집어 넣은 Case가 발생하였다.

Mysql에서는 조건절에 일반 numeric data처럼 ''를 사용하지 않으면 잘 실행되었는데..

( 즉.. SELECT ... FROM Campaign WHERE insertionOrder > 6 )

Oracle에서는 위와 같이 하면... "ORA-01722: invalid number" 오류가 발생한다.

예전에는 됐던것 같은데.... 하고 생각해보니 내가 이렇게 썼던 기억은 없는것 같다.^^

물론 ''를 줄 경우에는 character 비교가 되니 10 같은 값은 numeric으로 비교하면 6보다 커도 character로 비교되면 6보다 작아 나오지 않는 다는...-.-

염대리님하고 상의를 해본 결과 아래와 같은 Query가 나왔다.

select insertionOrder from Campaign where decode(length(translate(insertionOrder, 'A1234567890','A')),null, to_number(insertionOrder), 0) > 6

위에 파란색으로 표시된 부분은 Numeric value일 경우 null을 Return해 주는 Query이다.

( translate function(A,B,C) 는 A 문자열내에 있는 B character들을 C character들로 치환 해 주는 function이다. 즉 위의 경우는 A->A , 1 ~ 9 , 0-> 치환 없음 이 되므로 translate 실행 결과 숫자 데이터들은  null(확실하지는 않지만 length 결과가 null이 되는 걸로 봐서는 null이 맞을 듯 싶다.)이 되면서 이를 통해 numeric data 여부를 판단할 수 있는 것이다. 

염대리님이 찾아 낸 또다른 Tip은

select * from ( select * from Campaign where LOWER(insertionOrder) = UPPER(insertionOrder)) where insertionOrder > 6

인데 애석하게도 우리 System은 Character insensitive 환경을 구성하여

일단 Character 값을 LOWER(insertionOrder) = UPPER(insertionOrder) 로는 제거할 수 없다는...-.-


( 그나저나 어떤 설정으로 Character insensitive하게 구성했는지.. 

  아직 까진 그 option을 본적이 없는데...

  그러고 보니 "_" 두개 이상일경우에 like 검색 잘 되지 않던 Issue도 option으로 해결 한것 같은데.. 

  흠.. 언젠가 본사 쪽 사람이랑 얘기할 기회가 생기면 이 부분에 대해 한번 물어 봐야 겠다.^^ )