15 06 2015

• Export

만약 scott의 ROLE에 EXP_FULL_DATABASE  ROLE이 있다고 하면

에러(IMP-00013: DBA만이 다른 DBA가 엑스포트한 파일을 임포트할 수 있습니다)발생합니다.

이겨우는 sys로 로그인후

권한 회수후 export하세요

• Import

에서 ignore가 가장 중요합니다. 데이타만 들어가게 하는 옵션입니다.

테이블스페이스가 다른경우

• 데이타이관

1.Import

이렇게 하면 index.sql에 스크립트가 생성된다.

이걸로 Index.sql스크립트를 먼저 만든다.

그다음에 위와같이  데이타만 import하게 하면된다.

2.순서
문자셋;KO16KSC5601 =>한글을 2byte로 인식
문자세;UTF8 or AL32UTF8 는 한글을 3byte로 인식

1) 캐릭터셋 KO16KSC5601의 데이타 Export
2) Import 할 DB의 NLS_LENGTH_SEMANTICS 파라메터 변경
ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR SCOPE=BOTH ;
또는
CREATE TABLE TABLE_NAME(VARCHAR2(100 CHAR))와 같이
DDL 스크립트의 문자 데이터 타입의 단위설정에 CHAR를 지정(기본 Byte 단위)
3) 목표 DB의 Oracle 계정 imp 명령에서 SHOW=Y 옵션을 이용하여 DDL 스크립트 추출
imp dbuser/1234 file=’/DMP/database.dmp’ log=’/DMP/database_sql.log’ fromuser=dbuser SHOW=Y
또는
토드등의 툴에서 DDL 스크립트 추출
2의 단계를 거치지않을경우 DDL문의 Byte값을 1/3만큼 조정한다.

4) 목표 DB에 DDL 스크립트를 이용하여 테이블 생성(스크립트의 문자 타입에 CHAR 단위 지정)
5) 목표 DB에 imp 명령에서 IGNORE=Y 옵션을 이용하여 데이터 import 진행
imp dbuser/1234 file=’/DMP/database.dmp’ log=’/DMP/database.log’ fromuser=dbuser IGNORE=Y

++++DDL 스크립트 생성 상세 설명+++++

1) export를 할때 데이터는 만들지 않고 schema image만 뽑아냄

exp scott/tiger file=exp.dmp compress=n rows=n

2) 1번 스텝과 같이 해서 성공한 export dump파일을 이용하여 create script를 만드는 방법
이 방법은 import라는 유틸리티를 사용하여 작업을 합니다.

imp scott/tiger file=exp.dmp indexfile=ddl.sql full=y

3) 요렇게 하면 ddl.sql이라는게 생성됩니다.
vi나 편집기로 여시면 REM이라는 코멘트로 앞부분이 막혀 있을 겁니다.
이걸 모두 없애시면 그야말로 DBMS내에 있는 형태 그대로 만들어진 DDL Scripts입니다.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

(Oracle ) Difference between utf8 and al32utf8(2)
http://ndba.egloos.com/2918827

Tuesday, October 23, 2007
Difference between UTF8 and AL32UTF8
UTF8 and AL32UTF8 are encodings of the Unicode character set and include all the characters in all modern languages. UTF8 and AL32UTF8 allow Oracle Applications to be run from one database instance using any combination of supported languages. The advantage of AL32UTF8 over UTF8 is in the handling of supplementary characters, which are increasingly used in certain languages. AL16UTF16 is the current default database character set for Oracle databases 10g and 11g and Oracle E-Business Suite R12.

If you happen to create a fresh Apps Production instance, be sure to select the characterset as AL16UTF16, if all the clients and servers connecting to your instance are 9i and above. This is because 8i databases have trouble connecting to AL32UTF8 databases as per metalink note 237593.1

Caution:

AL32UTF8 is the Oracle Database character set that is appropriate for XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which supports all valid XML characters.
Do not confuse Oracle Database database character set UTF8 (no hyphen) with database character set AL32UTF8 or with character encoding UTF-8. Database character set UTF8 has been superseded by AL32UTF8. Do not use UTF8 for XML data. UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML characters. AL32UTF8 has no such limitation.

Using database character set UTF8 for XML data could cause a fatal error or affect security negatively. If a character that is not supported by the database character set appears in an input-document element name, a replacement character (usually a question mark) is substituted for it. This will terminate parsing and raise an exception

AL16UTF16 and AL32UTF8 support the same set of Unicode characters. The difference is the number of bytes required to encode any particular character. In general (ignoring a handful of special case characters normal people basically never encounter)

In general, AL32UTF8 uses 1 byte to encode most Western European characters, 2 bytes to encode most Eastern European characters, and 3 bytes to encode most Asian characters. In general, AL16UTF16 uses 2 bytes to encode every character. Obviously, that has some rather large impacts on data storage requirements if you are storing primarily Western European vs primarily Asian characters. It also has impacts on performance if you are constantly searching the string for a particular substring (which tends to be faster for fixed width character sets).

AL16UTF16 is the default NLS characterset. Secondly UTF-16 encoding is the 16-bit encoding of Unicode whereas UTF-8 is the 8-bit encoding of Unicode.

The benefits of UTF-16 over UTF-8 are as follows:

– More compact storage for Asian scripts because most of the commonly used Asian characters are represented in two bytes.

– Better compatibility with Java and Microsoft clients

Though I would recommend you to leave it unchanged unless you are using NVARHCAR2, NCLOB columns in your database. You can search Metalink for the impact.

Exp, Imp, 이관

CentOS 설치후 작업 Nginx install on CentOS