ORACLE Tip2011. 5. 19. 03:10

테이블에 CLOB 타입이 포함된 dmp 파일을 import 하고자 할때.


다음과 같은 오류가 발생할수 있다.

IMP-00017: following statement failed with ORACLE error 959:
...
...
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TS_ICMS_DATA' does not exist

import의 경우 컬럼에 CLOB 데이터타입이 포함되어 있을경우는 export받은 테이블의 테이블스페이스 정보와 import 하려고 하는 테이블스페이스가 같아야 한다.

따라서 다음과 같이 테이블 스페이스를 추가한다.

# tablespace 생성
CREATE TABLESPACE TS_ICMS_DATA
DATAFILE '/export/home/oracle/data/ICMSDATA1.dbf' SIZE 1000M;

# 사용자에게 테이블스페이스 지정
CREATE USER test_user IDENTIFIED BY test_pwd DEFAULT TABLESPACE ts_icms_data TEMPORARY TABLESPACE tmp;

# 권한부여
GRANT CONNECT, RESOURCE TO test_user;
Posted by 항아리고미
ORACLE Tip2011. 5. 19. 02:42

Oracle 10g export 받은 데이터를 9i에 import 할때.


두 DB간 버전이 틀리면 다음과 같은 오류가 발생함.

IMP-00010(not a valid export file, header failed verification)

이경우 10g의 데이터를 9i에 넣을수가 없기 때문에
9i에서 10g로 접속하여 파일을 export받으면 된다.

방법은 9i 서버의 tnsnames.ora 파일에 10g 서버의 정보를 넣은후
9i 서버에서 아래와 같이 실행하면 된다.

exp userid/passwd@10g_tns_info

example : 9i 서버에서 10g로 접속해 특정 테이블(T_MS_ALBUM_TMP) 를 받는 예제.

exp 10g_userid/10g_passwd@DMO_DEV_DB
Export: Release 9.2.0.6.0 - Production on Thu May 19 01:24:53 2011
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Enter array fetch buffer size: 4096 >
Export file: expdat.dmp > T_MS_ALBUM_TMP.dmp
(2)U(sers), or (3)T(ables): (2)U > 3
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > T_MS_ALBUM_TMP
. . exporting table                 T_MS_ALBUM_TMP       228780 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully with warnings.


9i 로 export를 받았으니 import는 일반적인 방법으로 사용하면 된다.

example : 9i 서버에서 특정 테이블(T_MS_ALBUM_TMP) 를 import하는 예제

imp 9i_userid/9i_passwd file=T_MS_ALBUM_TMP.dmp tables=T_MS_ALBUM_TMP commit=y ignore=y buffer=100000 log=T_MS_ALBUM_TMP.log
Import: Release 9.2.0.6.0 - Production on Thu May 19 01:43:08 2011
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by MLBDMO, not by you
import done in AL32UTF8 character set and UTF8 NCHAR character set
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing MLBDMO's objects into WIMS
. . importing table               "T_MS_ALBUM_TMP"       228780 rows imported
Import terminated successfully without warnings.
Posted by 항아리고미
ORACLE Tip2011. 4. 14. 17:44

SQL로더 TIP


1. Field in data file exceeds maximum length 오류 발생시

하나의 컬럼에 로드하는 데이터의 길이가 255Byte를 넘어갈경우
지정된 컬럼의 크기가 255Byte를 넘었더라도 위와 같은 오류가 발생한다.

이경우 Control 파일의 해당 컬럼에 다음과 같이 컬럼의 길이를 넣어주면 된다.

MV_INFO CHAR(4000)

이때 VARCHAR로 넣지 말아야 한다.


2. default 값 넣기. 
로드하는 테이블의 특정 컬럼에 default 값을 넣고자 할때..
" " 안에 SQL 함수를 써주면 된다. 

아래의 예제는 INPUT_FILE_NAME 컬럼에 특정값을 넣고자 하는 경우다.
..
 INPUT_FILE_NAME "NVL(TRIM(:INPUT_FILE_NAME), 'iCMS_F02_F08_BAR_utf8.txt')"
..

Posted by 항아리고미
ORACLE Tip2010. 9. 1. 11:43

Returning a 'table' from a PL/SQL function

oracle function 기능중 table로 결과값을 리턴받고 싶을때를 위해 찾아봤더니.. 너무나도 깔끔하고 아름다운 글이 있었다.. ^^

With collections, it is possible to return a table from a pl/sql function.
First, we need to create a new object type that contains the fields that are going to be returned:
create or replace type t_col as object (
  i number,
  n varchar2(30)
);
/

Then, out of this new type, a nested table type must be created.
create or replace type t_nested_table as table of t_col;
/

Now, we're ready to actually create the function:
create or replace function return_table return t_nested_table as
  v_ret   t_nested_table;
begin
  v_ret  := t_nested_table();

  v_ret.extend;
  v_ret(v_ret.count) := t_col(1, 'one');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(2, 'two');

  v_ret.extend;
  v_ret(v_ret.count) := t_col(3, 'three');

  return v_ret;
end return_table;
/

Here's how the function is used:
select * from table(return_table);

     1 one
     2 two
     3 three
Posted by 항아리고미
ORACLE Tip2010. 5. 17. 15:36

테이블의 특정 데이터만을 EXPORT 받고자 할때.


우선 exp에서 사용할 수 있는 옵션에 어떤것이 있는가 살펴보자.

exp help=y
Export: Release 10.2.0.4.0 - Production on Mon May 17 15:31:50 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
     Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)
OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
VOLSIZE              number of bytes to write to each tape volume
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export
Export terminated successfully without warnings.

위의 도움말을 보면 QUERY 라는 옵션이 있다.


- Example:
1.SCOTT.Emp table의 ename 이 JAME과 비슷한 이름의 data 를 export ..
exp scott/tiger query=\"where ename like \'JAME%\'\" tables=emp file=exp.dmp log=exp.log


2. employee와 cust table에서 new york 주의 data 만 export ..
exp scott/tiger query=\"where st=\'NY\'\" tables=(employee,cust) file=exp.dmp log=exp.log

query 문장에서 UNIX reserved characters( ", ', ,< .. 등) 를 사용하는 경우에는 escape ('\') 을 반드시 사용해야 한다.
예)query=\"where JOB = \'SALESMAN\' and salary \< 1600\"

더 중요한 것은 command line에서 export option을 사용할때는 반드시 escape 이 있어야 하나
parfile을 사용할때는 eacape이 불필요하다.

command line에는 query 내에 single(')나 double quotes(") 를 사용한다면 반드시 double quotes(") 를 사용하여
query 문을 묶어야 한다.그러나 query 내에서 single ('')나 double quotes(") 를 사용하지 않는다면 single quotes (')을 사용하여 query 문을 수행할 수도 있다

발췌 : http://kr.forums.oracle.com/forums/thread.jspa?messageID=1698906

Posted by 항아리고미
ORACLE Tip2010. 5. 10. 14:01

Cost Based Push Predicate


잘 되던 쿼리문이 어느날 다음과 같은 오류를 던졌습니다.
ORA-00600: 내부 오류 코드, 인수 : [kkocxj : pjpCtx], [], [], [], [], [], [], []

찾아보니 다음과 연관이 있엇습니다.

"실행 계획 이상과 Cost Based Push Predicate"

9i 버전에서 정상적으로 Push Predicate이 이루어지는 쿼리가 10g에서는 Push Predicate가
발생하지 않을 수 있다. 통계 정보가 정확하다면 오라클의 대부분의 경우 최적의 판단을 하지만,
간혹 특정 쿼리의 경우에는 Push Predicate이 이루어지지 않음으로써 비효율적인 실행 계획으로
바뀔 수 있다. 이런 경우에는 _OPTIMIZER_PUSH_PRED_COST_BASED 파라미터 값을 FALSE로
변경하거나 OPT_PARAM 힌트를 이용해서 해당 파라미터를 Statement 레벨에서 변경해야 한다.

alter session set "_optimizer_push_pred_cost_based" = false;
(or) alter system set "_optimizer_push_pred_cost_based" = false;

 

Posted by 항아리고미
ORACLE Tip2009. 10. 22. 03:28

두개의 테이블을 조인해서 UPDATE 하는 경우.


그동안 두개의 테이블을 조인해서 UPDATE 할때 너무나 원시적인 방법을 사용했었다.

BEGIN
FOR xx IN (SELECT A, B, C, D FROM TAB_A) LOOP
UPDATE TAB_B
SET A = xx.A
, D = xx.D
WHERE B = xx.B
AND C = xx.C;
END LOOP;
END;

COMMIT;

TAB_B가 작은 테이블일 경우는 그닥 문제가 없지만, 조금 큰 테이블인경우는..  답이 없다..

오늘 ORACLE의 좋은기능을 하나 발견했다.

UPDATE (
    SELECT xx.A xx_A, xx.D xx_D, yy.A yy_A, yy.D yy_D
    FROM TAB_A xx, TAB_B yy
    WHERE xx.B = yy.B
    AND xx.C = yy.C
)
SET yy_A = xx_A
, yy_D = xx_D;

COMMIT;

이렇게 쓸경우 oracle에서 다음과 같은 오류가 나오는 경우가 있다.

ORA-01779: cannot modify a column which maps to a non key-preserved table

그럴경우에는 다음과 같은 힌트를 사용하면 된다.

UPDATE /*+ bypass_ujvc */
(
    SELECT xx.A xx_A, xx.D xx_D, yy.A yy_A, yy.D yy_D
    FROM TAB_A xx, TAB_B yy
    WHERE xx.B = yy.B
    AND xx.C = yy.C
)
SET yy_A = xx_A
, yy_D = xx_D;

COMMIT;



Posted by 항아리고미
ORACLE Tip2009. 10. 1. 03:29

v$session_longops

Toad에는 시간이 다소 걸리는 쿼리를 실행했을때 어느정도의 시간이 남아있는지 대략적으로 알수 있는 기능이 있다.
바로 session monitor

간혹 Toad의 session monitor와 유사한 기능으로 세션의 처리 시간이나 남은 시간을 콘솔에서 확인하고 싶을때가 있다.
그럴때 유용하게 쓸수 있는 TIP이다.

col message format a70
col OPNAME format a40
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

select start_time,TIME_REMAINING,ELAPSED_SECONDS,MESSAGE from v$session_longops
where TIME_REMAINING > 0;

select sid,serial#,opname,start_time,trunc((sofar/totalwork)*100) percent_complete from v$session_longops
where TIME_REMAINING > 0;

select l.sid,l.serial#,s.module,s.sql_hash_value,l.opname,l.start_time,
time_remaining + elapsed_seconds time_sc,trunc((l.sofar/l.totalwork)*100) compl
from v$session_longops l,v$session s
where l.sid=s.sid and l.serial#=s.serial# and l.TIME_REMAINING > 0;



 

Posted by 항아리고미
ORACLE Tip2009. 8. 28. 20:06

V$SQL BIND CAPTURE


V$SQL_BIND_CAPTURE 뷰는 SQL 커서에서 Bind Variable이 사용된 경우 해당 Bind Variable의 이름과 유형, 값 등을 캡쳐한 정보를 제공한다.
(10g이상에서 제공)

컬럼

이름

유형

설명

ADDRESS

8)

Parent Cursor의 Address

HASH_VALUE

NUMBER

Parent Cursor의 Hash Value.

SQL_ID

VARCHAR2(13)

Unique SQL ID. V$SQL의 SQL_ID 컬럼과 조인 가능하다.

CHILD_ADDRESS

8)

Child Cursor의 Address

CHILD_NUMBER

NUMBER

Child Cursor의 번호.

NAME

VARCHAR2(30)

Bind Variable의 이름. 예: :name

POSITION

NUMBER

SQL 문장에서 Bind Variable의 위치. 1부터 시작한다.

DUP_POSITION

NUMBER

Bind Variable의 이름이 중복되는 경우 최초 Bind Variable의 위치를 가리킨다.

DATATYPE

NUMBER

Bind Variable의 데이터 유형. 오라클 내부적으로 사용되는 숫자값이다.

DATATYPE_STRING

VARCHAR2(15)

Bind Variable의 데이터 유형에 대한 인식 가능한 이름. 예:NUMBER, VARCHAR2

CHARACTER_SID

NUMBER

National character set identifier

PRECISION

NUMBER

Precision (for numeric binds)

SCALE

NUMBER

Scale (for numeric binds)

MAX_LENGTH

NUMBER

최대 Bind 길이

WAS_CAPTURED

VARCHAR2(3)

Bind 값에 대한 Capture가 이루어졌는지의 여부 (YES) 또는 (NO)

LAST_CAPTURED

DATE

Bind 값에 대한 Capture가 이루어진 가장 최근 시간

VALUE_STRING

VARCHAR2(4000)

Bind 값에 대한 String 표현

VALUE_ANYDATA

ANYDATA

Bind 값에 대한 Sys.AnyData에 의한 표현


Bind Capture가 이루어지는 시점
QL 문장이 Hard Parse되는 시점에 Bind Variable이 사용되고 Bind 값이 부여된 경우 
Bind Capture가 이루어진 이후, Bind 값이 변경된 경우. 단, 오라클의 성능상의 오버헤드를 최소화기 위해 15분 이상의 간격을 두고 Capture를 수행한다.
즉, Bind Capture된 값이 항상 최신의 값을 나타내는 것이 아니라는 것에 주의해야 한다.

Bind 값 알아내기
Oracle 10g 이전에는 특정 SQL 문장에서 사용 중인 Bind 값을 알아내고자 할 때는 Processstate Dump를 수행하거나 SQL Trace, 혹은 Audit를 수행했어야 했다. 하지만, V$SQL_BIND_CAPTURE 뷰를 사용하면 간단한 조회만으로 Bind 값을 알아낼 수 있게 되었다. 각 툴이 제공하는 값의 종류는 다음과 같이 정리할 수 있다.

  Processstate Dump: 특정 Session(Process)가 "현재" 수행 중인 SQL 문장에서 사용되는 Bind 값의 목록
  SQL Trace: 특정 Session이나 System 전체가 수행한 모든 SQL 문장에서 사용된 Bind 값
  V$SQL_BIND_CAPTURE: SQL 문장별로 최근에 사용된 Bind 값
  Audit: 특정 테이블 또는 전체 오브젝트에 대한 Select/Insert/Update/Delete에 대한 SQL 문장과 Bind 값

Bind 값의 이력
V$SQL_BIND_CAPTURE 뷰의 History 버전인 DBA_HIST_SQLBIND 뷰를 통해 과거 특정 시점에 특정 SQL 문장에서 사용된 Bind 값을 확인할 수 있다.

예제

SQL> CREATE TABLE bind_test(id INT);
SQL> var x number;
SQL> EXEC :x := 1;
SQL> select * from bind_test where id = :x';

SQL> SELECT name, position, was_captured,
 to_char(last_captured,'yyyy/mm/dd hh24:mi:ss'), value_string
   FROM v$sql_bind_capture
   WHERE sql_id = (SELECT sql_id FROM v$sql WHERE sql_text =
   ('select * from bind_test where id = :x')
  
NAME POSITION WAS_CAPTURED LAST_CAPTURED  VALUE_STRING
---- -------- ---------- --------------  ------------
:X 1  YES  2007/10/13 00:16:19 1

 

관련정보
1. _OPTIM_PEEK_USER_BINDS 파라미터
2. V$SQL_BIND_DATA 뷰
3. V$SQL_BIND_METADATA 뷰
4. DBA_HIST_SQLBIND


외부참조
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2114.htm

출처 : 엑셈

Posted by 항아리고미
ORACLE Tip2009. 3. 10. 13:40

view 소스 script 알아내는 쿼리.


sqlplus 상에서 다음과 같이 실행하면 됩니다.

set long 10000
set pagesize 0
 
select text from user_views where view_name = 'VIEW이름'

소스가 짤려서 출력되지 않을경우에는
long 값을 지정하는 부분의 수치를 올려주면 됩니다.
ex) set long 20000
Posted by 항아리고미

티스토리툴바