StoryErpStoryErp

오라클을 설치 하였을시에.. 

언어설정이 잘못되어지면 프로그램 구동시 한글이 꺠지는 현상이 발생하게 됩니다. 

그럴시에는 DB의 언어를 바꾸어야 되는데요. 

일반적으로는 레지스트리의 언어 변경 방법을 마니 쓰게 됩니다. 

하지만 DB 생성시에 언어를 정확하게 바꾸기 위해서는... 쿼리로 DB를 설정 하는 방법이 가장 좋은 방법입니다. 

언어별 UPDATE 치는 방법을 소개 하겠습니다. 

update sys.props$ set value$='KO16KSC5601' where name='NLS_CHARACTERSET'; 

update sys.props$ set value$='KO16KSC5601' where name='NLS_NCHAR_CHARACTERSET'; 

update sys.props$ set value$='AMERICAN_AMERICA.KO16KSC5601' where name='NLS_LANGUAGE'; 

현재 저장된 nls 파라미터 보기 
select * from v$nls_parameters; 

DB 종료 후 

STARTUP MOUNT; 
ALTER SYSTEM ENABLE RESTRICTED SESSION; 
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
ALTER DATABASE OPEN; 

-- ko16ksc5601 
update sys.props$ set value$='KO16KSC5601' where name='NLS_CHARACTERSET'; 
update sys.props$ set value$='KO16KSC5601' where name='NLS_NCHAR_CHARACTERSET'; 
update sys.props$ set value$='AMERICAN_AMERICA.KO16KSC5601' where name='NLS_LANGUAGE'; 

or 

update sys.props$ set value$='KOREAN_KOREA.KO16KSC5601.KO16KSC5601' where name='NLS_LANGUAGE'; 

update sys.props$ set value$='us7ascii' where name='NLS_CHARACTERSET'; 
update sys.props$ set value$='utf8' where name='NLS_NCHAR_CHARACTERSET'; 
update sys.props$ set value$='AMERICAN_AMERICA.US7ASCII' where name='NLS_LANGUAGE'; 

-- ko16mswin949 
update sys.props$ set value$='KO16MSWIN949' where name='NLS_CHARACTERSET'; 
update sys.props$ set value$='KO16MSWIN949' where name='NLS_NCHAR_CHARACTERSET'; 
update sys.props$ set value$='KOREAN_KOREA.KO16MSWIN949' where name='NLS_LANGUAGE'; 

-- UTF8 
update sys.props$ set value$='UTF8' where name='NLS_CHARACTERSET'; 
update sys.props$ set value$='UTF8' where name='NLS_NCHAR_CHARACTERSET'; 
update sys.props$ set value$='KOREAN_KOREA.UTF8' where name='NLS_LANGUAGE'; 

1. 새 컬럼 추가 
    alter table 테이블이름 
    add (컬럼이름 데이터타입 [default 표현식] [, 다른 컬럼 정의...]); 

    ex) alter table any_table add ( any_column1 int not null, any_column2 int); 

- 추가된 컬럼은 항상 마지막 순서에 놓인다.(컬럼 순서를 변경할 수 없다.) 
- 추가된 데이터는 각 행에 널값으로 삽입된다. 
 테이블 내에 레코드가 존재한다면 낫널인 컬럼을 추가할 수 없다. 
 빈 테이블일 경우 낫 널을 부여할 수 있다. 

2. 존재하는 컬럼 수정 
    alter table 테이블이름 
    modify (컬럼이름 데이터타입 [default 표현식] [, 다른 컬럼 정의...]); 

    ex) alter table any_table modify (any_column1 varchar2(20)); 

- 타입, 사이즈, 기본값을 변경 가능 
- 디폴트 값을 변경했을 경우 이후 삽입되는 데이터에 대해서만 적용된다. 

가이드라인 
- 숫자형 사이즈 증가 가능 
- 문자형 데이터 사이즈 증가 가능 
- 사이즈 줄일 수 있는 경우 
        모든 값이 널일 경우 
        레코드가 없을 경우 
        줄이고자 하는 사이즈가 현재 들어가있는 값들보다 작을 경우 
- 컬럼의 모든 값이 널 값일 경우 컬럼의 타입을 변경 가능하다. 
        char를 varchar2 형태로 바꿀 때는 타입 변경 가능. 
- cahr to varchar2, varchar2 to char 변환은 컬럼이 널값만 가지고 있거나 사이즈 변경이 없을 경우만. 
- 디폴트 값은 다음 입력부터 반영 

3. 컬럼 드랍 
    alter table 테이블이름 
    drop (컬럼이름); 

    ex) alter table any_table drop (any_column1); 

- 컬럼에 데이터 존재 유무에 무관하게 드랍 가능 
- 한번에 한 컬럼만 삭제 가능하다. 
- 테이블은 적어도 한개의 컬럼은 가지고 있어야 한다. 
- 드랍하고 나면 복구 불가능 
- 제약의 일부이거나 인덱스의 일부일 경우 삭제할 수 없다. 
 cascade옵션을 추가한다. 
- 레코드가 많은 경우 드랍 칼럼은 옳지 않다. 락을 피하기 위해 유저가 적을때 unused를 쓰고 드랍하자. 

4. set unused 옵션 

- 더이상 사용하지 않는 컬럼에 대해 set unused를 사용한다. 

    alter table 테이블 이름 
    set unused (컬럼 이름); 
or 
    alter table 테이블 이름 
    set unused column (컬럼 이름);    

then 

    alter table 테이블이름 
    drop unused columns; 

set unused 옵션도 복구가 불가능하다. 
딕셔너리의 USER_UNUSED_COL_TABS 에 저장된다. 

5. 제약 추가 

- 제약을 추가하거나 드랍할 수 있지만 존재하는 제약을 수정할 수는 없다. 
- 제약을 활성화, 비활성화 할 수 있다. 
- 낫널 제약을 추가할 때는 반드시 modify를 함께 사용한다. 

    alter table 테이블명 
    add [constraint 제약명] 
    타입 (컬럼명); 

- 낫널은 테이블이 비어있거나 해당 컬럼의 모든 레코드가 값을 가질 경우 가능하다. 

6. on delete cascade 


    alter table Emp2 
    add constraint emp_dt_fk 
    foreign key (Department_id) 
    references departments on delete cascade); 

    on delete cascade 
- 부모 테이블의 레코드가 삭제되면 같이 삭제될것 

7. 제약 드랍 

    alter table emp2 
    drop constraint emp_mgr_fk; 


    - pk를 참조하는 fk도 함께 삭제하기 위해 cascade를 사용한다. 
    alter table dept2 
    drop primary key cascade; 


!! 제약을 드랍하기 위해 제약 이름을 USER_CONSTRAINRS와 USER_CONS_COLUMNS에서 확인할 수 있다. 

8. 제약 비활성화/활성화 

제약 비활성화 

- disable 절을 이용해 비활성화 
- cascade 옵션으로 종속적인 constraints도 함께 비활성화 

    alter table emp2 
    disable  constraint emp_dt_fk; 

- pk나 uk를 비활성화 할 경우 내부적으로 uk를 삭제해버린다. 


제약 활성화 

    alter table emp2 
    enable  constraint emp_dt_fk; 

- 활성화할 경우 모든 데이터에 대해 제약 조건 검사를 한다. 
- pk, uk가 활성화 될 경우 uk가 자동으로 생성된다. 
- enable은 create table, alter table에 모두 사용할 수 있다. 
- cascade 로 비활성화된 포린 키는 활성화되지 않는다. 
- uk, pk를 활성화하기 위해 create index 권한이 필요하다.

한글을 지원하는 캐릭터셋 
1.KO16KSC5601 
2.KO16MSWIN949 
3.UTF8 
4.AL32UTF8 

A. 작업 전 만약을 위해 DB전체를 백업 받아둔다. character set 을 잘못 변경하면 DB를 open 할수 없을 수 있다. 
B. 오라클 캐릭터셋 확인쿼리 
  SELECT parameter,value FROM nls_database_parameters WHERE parameter like '%CHAR%'; 
-------------------------------------- 
C. 아래는 OTN에서 발췌한 내용 
8i(AMERICAN_AMERICA.US7ASCII)는 version 8.1.6 이상이라고 가정하겠습니다. 아래의 내용 중의 일부분은 Oracle database release 8.1.6 이상에서만 적용될 수 있습니다. 
기본 작업 절차 
-------------- 
(1) CLOB data에 대한 unloading/exporting. 
(2) 8i(AMERICAN_AMERICA.US7ASCII)에서 "ALTER DATABASE CHARACTER SET" command set을 통한 8i(AMERICAN_AMERICA.KO16KSC5601)로의 변환. 
(3) 8i(AMERICAN_AMERICA.KO16KSC5601)에서 NLS_LANG 환경변수 변경(KOREAN_KOREA.KO16KSC5601)을 통한 8i(KOREAN_KOREA.KO16KSC5601)로의 변환. 
(4) 8i(KOREAN_KOREA.KO16KSC5601)에서 exporting 후 9i(KOREAN_KOREA.KO16KSC5601)에서 importing. 
(5) CLOB data에 대한 loading/importing. 
과정(2)의 세부 절차 
------------------ 
$ sqlplus /nolog 
SQL> conn sys/pw as sysdba 
SQL> shutdown immediate 
SQL> startup mount 
SQL> alter system enable restricted session; 
SQL> alter system set JOB_QUEUE_PROCESSES=0; 
SQL> alter system set AQ_TM_PROCESSES=0; 
SQL> alter database open; 
SQL> alter database character set KO16KSC5601; 
SQL> alter database national character set KO16KSC5601; 
SQL> shutdown immediate 
SQL> startup open 
  
* 
ERROR at line 1: 
ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists 
## 에러가 발생할 경우 아래 스크립트로 실행한다. 
SQL> alter database  character set internal_use ko16ksc5601; 
  
CF) DB내의 CLOB 찾기 
CLOB 또는 NCHAR Datatype의 컬럼들을 export 받고 
CLOB, NCHAR이 포함된 Table을 Drop하고 
Alter database character set ... 하고 
다시 Import하라고 되어 있는데 별 효용없는것 같음. 
sys, system으로 접속하여 
SELECT * 
FROM dba_lobs 
Partitioning했다면 DBA_PART_LOBS까지 참조 

## export

형식 :  EXP KEYWORD=값 또는 KEYWORD=(값1,값2,...,값N)

예    :  EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
           또는 T1이 분할된 테이블인 경우 TABLES=(T1:P1,T1:P2)

예 ) exp scott/tiger file=emp.dmp log=emp.log tables=emp direct=y
예 ) exp system/oracle file=full.dmp full=y indexes=n triggers=n
예 ) exp system/oracle file=scott.dmp owner=scott rows=n

option

수많은 exp 옵션들... 다 외울순 없다..

exp help=y

하게 되면 친절히 설명해주니 기억이 나지 않을시 참고 하도록 하자

scott/tiger                           : db 유저/password
full                                       : 해당 db 전체 데이터 추출 여부(기본값 N)
buffer                                   : 작업 단위의 크기 설정
owner                                  :  db 유저별 오브젝트 추출 설정
file                                       : 추출한 데이터를 저장할 파일 이름 설정
tables                                  : 데이터를 추출할 대상 테이블 설정
compress                            :  익스텐트 통합 여부를 지정(기본값 Y)
grants                                  : 오브젝트 권한 설정에 대한 정보 추출 여부(기본값 Y)
indexes                                : 인덱스 스크립트 추출 여부(기본값 Y)
direct                                   : 직접 경로로 export 수행 여부(기본값 N)
triggers                                  : 트리거 정보 추출 여부(기본값 Y)
log                                       : 로그를 저장할 파일 지정
rows                                     : 테이블의 데이터 추출 여부(기본값 Y)
consistent                             : 대상 테이블의 읽기 일관성 지정(기본값 N)
constraints                            : 제약 조건의 추출 여부(기본값 Y)
parfile                                 :  파라메터 파일을 export시 적용
INCTYPE                               : 증분 엑스포트 유형
RECORD                               : 증분 엑스포트 추적(기본값 Y)

OBJECT_CONSISTENT            : 객체 엑스포트 동안 읽기 전용으로 설정된 트랜잭션(N)
FEEDBACK                            : 매 x행마다 진행 상황 표시(0)
FILESIZE                             : 각 덤프 파일의 최대 크기
FLASHBACK_SCN                   : 세션 스냅샷을 되돌리는 설정에 사용된 SCN
FLASHBACK_TIME                  : 지정된 시간에 가장 가까운 SCN을 가져오는 데 사용된 시간
QUERY                                 : 테이블의 부분 집합을 엑스포트하는 데 사용된 select 절
RESUMABLE                          : 공간 관련 오류가 발생하면 일시 중지됨(N)
RESUMABLE_NAME                : 재개 가능한 명령문을 식별하는 데 사용된 텍스트 문자열
RESUMABLE_TIMEOUT            : RESUMABLE 대기 시간
TTS_FULL_CHECK                   : TTS에 대한 전체 또는 부분 종속성 검사 수행
TABLESPACES                     : 엑스포트할 테이블스페이스 목록
TRANSPORT_TABLESPACE : 이동 가능한 테이블스페이스 메타 데이터 엑스포트(N)
TEMPLATE                             : iAS 모드 엑스포트를 호출하는 템플리트 이름

** compress 옵션을 Y로 설정 하여 사용하면 여러개의 익스텐트가 하나의 큰 익스텐트로 통합된다
   실제 운영에서는 익스텐트를 통합하는것은 좋지 않으므로 반드시 compress 옵션을 N로 설정 한다.
** direct 옵션을 사용하면 SGA를 사용하지 않고 export/import 하게 되며 
   추출 및 적재 작업시 보다 빠른 속도를 보장 받는다.
** comsistent 옵션을 Y로 설정하면 export를 수행한 시점의 데이터를 추출한다.
   export 중 변경된 데이터는 언두 데이터를 이용해서 이전 값을 추출한다.
   따라서 'snap shot too old' 에러가 발생하기 쉽다.

## export 작업시 주의 사항

** full, ower 및 tables 옵션은 동시 사용 불가
** direct=Y인 경우 consistent=y로 설정 불가
** full=Y로 expor를 수행하기 위해서는 exp_full_database 권한 필요

----- 추출 파일의 크기를 확인하는 SQL

SQL> select sum(bytes)/1024/1024
     from dba_segments
     where segment_name='TEST';

export 작업을 수행 하면 실제 테이블의 크기보다 작게 생성된다.
위 SQL을 수행하여 나온 크기만큼 디스크의 크기를 확보하면 export 작업을 수행하는데 충분하다.
또한 추출 파일에는 해당 테이블의 인덱스를 생성할 수 있는 정의만 저장하므로 별도의 디스크공간은 필요하지 않다.
하지만 import 시에는 실제 인덱스를 생성하므로 인덱스 테이블스페이스에 여유 공간을 확보해야한다.

## import

-- import 순서 : 테이블 생성 > 데이터 적재 > B*트리 인덱스 생성 > 제약조건 > 트리거 및 비트맵 인덱스 생성

예 ) imp scott/tiger file=emp.dmp log=emp.log tables=emp
예 ) system/manager file=full.dmp log=full.log full=y
예 ) system/manager file=scott.dmp fromuser=scott touser=mike

option

scott/tiger                         : db 유저 / password
full                                     : 전체 db에 대해 import 여부(기본값 N)
buffer                                 : 작업 단위의 크기 지정
fromuser                       : export한 테이블의 소유자 지정
touser                                : import되는 테이블의 소유자 지정
tables                                : import 대상 테이블 지정
ignore                                : import 대상 테이블이 존재할 경우 에러 발생 여부(기본값 N)
grants                                 : 권한 적재 여부 지정 (기본값 Y)
indexes                             : index 생성 여부(기본값 Y)
commit                               : import 수행중 커밋 수행여부, 
                                           Y로 지정할 경우 buffer 옵션 단위로 커밋 수행(기본값 N)
rows                                   : 테이블의 데이터 적재 여부(기본값 Y)
log                                     : 로그를 저장할 파일 지정
constrainsts                      : 제약 조건 적재 여부(기본값 Y)
parfile                                : 파라메터 파일을 지정하여 import 작업시 적용

SHOW                                 : 파일 목차만 목록 (기본값 N)
RECORDLENGTH IO               : 레코드의 길이
INCTYPE                              : 증분 임포트 유형
DESTROY                             : 테이블스페이스 데이터 파일 겹쳐쓰기(기본값 N)
INDEXFILE                          : 지정된 파일에 테이블/인덱스 정보 쓰기
SKIP_UNUSABLE_INDEXES     : 사용되지 않은 인덱스들의 유지를 건너뜁니다 (기본값 N)
FEEDBACK                           : 매 x행마다 진행 상황 표시(기본값 0)
TOID_NOVALIDATE                : 지정된 유형 ID 검증 건너뛰기
FILESIZE                            : 각 덤프 파일의 최대 크기
STATISTICS                          : 미리 계산된 통계 임포트(항상)
RESUMABLE                         : 공간 관련 오류가 발생하면 일시 중지됨(기본값 N)
RESUMABLE_NAME               : 재개 가능한 명령문을 식별하는 데 사용되는 텍스트 문자열
RESUMABLE_TIMEOUT          : RESUMABLE 대기 시간
COMPILE                              : 프로시저, 패키지 및 함수 컴파일(기본값 Y)
STREAMS_CONFIGURATION   : 일반 스트림 메타 데이터 임포트(기본값 Y)
STREAMS_INSTANTIATION      : 스트림 인스턴스화 메타 데이터 임포트(기본값 N)

아래의 키워드만이 이동가능한 테이블스페이스에 적용할 수 있다.

TRANSPORT_TABLESPACE  : 이동가능한 테이블스페이스 메타데이터을 임포트합니다 (기본값 N)
TABLESPACES                     : 데이터베이스안에 테이블스페이스가 이동되었습니다
DATAFILES                            : 데이터베이스안에 데이터 파일들이 이동되었습니다
TTS_OWNERS                          : 이동가능한 테이블스페이스 설정에 데이터를 소우하고 있는 사용자

## import 작업시 주의 사항

** full 옵션과 tables 옵션은 동시 사용 불가
** full=y 및 touser 옵션을 사용하기 위해서는 imp_fyll_database 권한 필요
** 적은 양의 데이터 import 경우를 제외하곤 commit 옵션을 Y로 설정 권장.
** export 시와 동일한 tablespace가 존재하지 않을 경우 유저 default tablespace로 import 된다
   system 유저로 import 하게 되면 system tablespace에 일반 유저 세그먼트가 생성 될수 있으므로
   반드시 export시 tablespace와 동일한 이름의 tablespace를 생성한 후 작업을 수행해야한다.

------------------------- exp/imp 실습 --------------------------

실습 준비 사항

SQL> create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST1.DBF' size 1m;

SQL> alter tablespace test add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST2.DBF' size 1m;

SQL> alter tablespace test add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST3.DBF' size 1m;

SQL> create user orcl identified by orcl
  2  default tablespace test;

SQL> grant connect, resource, select any table to orcl;

SQL> grant exp_full_database, imp_full_database to orcl;            // exp, imp database 사용시

SQL> create table orcl.test as select * from scott.emp;

------------------------- exp/imp database --------------------------

## exp 백업의 이름을 잘 정해야 한다. 해당 백업 파일을 언제 누가 보든 쉽게 이해할수있도록 룰을 잘 정해야한다.
## exp의 경우 datapump와 다르게 디렉토리 지정이 없다.
## exp 할 대상 경로로 이동해 실행하거나, 
## file=[ 경로명 ]\test.dmp , log=[ 경로명 ]\test.log 와 같이 직접 지정해야한다.

D:\test>exp orcl/orcl full=y log=test.log file=test.dmp

엑스포트가 경고 없이 정상적으로 종료되었습니다.

D:\test>dir

2009-04-15  오후 01:12        82,259,968 test.dmp
2009-04-15  오후 01:12            61,816 test.log

D:\test>imp orcl/orcl full=y file=testfull.dmp
.
.
IMP-00015: 객체가 이미 존재하므로 다음 명령이 실패했습니다
 "CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE  'D:\ORACLE\PRODU"
 "CT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' SIZE 62914560       AUTOEXTEND ON NEX"
 "T 5242880  MAXSIZE 32767M EXTENT MANAGEMENT LOCAL "
.
.

########## 완료시 경고성 완료가 될수 있다. 이유? 당연히 DB가 멀쩡히 있기 때문이다.
########## 작업중 err를 발생할 수도 있다.. 걱정마라 system 관련 parameter 또는 동적 뷰다.
########## system 관련 데이터까지 모두 날릴순 없지 않은가..
########## 그럼 어떨때 export/import database를 쓸까??
########## 마이그레이션 작업할때 쓴다!!!

D:\test>imp orcl/orcl full=y file=testfull.dmp ignore=y

엑스포트 파일은 직접 경로를 거쳐 EXPORT:V10.02.01 에 의해 생성되었습니다
KO16MSWIN949 문자집합과 AL16UTF16 NCHAR 문자 집합에 임포트가 완성되었습니다
. SYSTEM 객체를 SYSTEM(으)로 임포트하는 중입니다
. OLAPSYS 객체를 OLAPSYS(으)로 임포트하는 중입니다
.
.

-------------------------- exp/imp transport_tablespace --------------------

같은 환경을 가진 두개 이상의 분산 DB 환경에서 어떤 DB가 가진 여러개의 데이터 파일을 다른 DB로 이동 시키는 기능.

------- 주의사항
## transport_tablespace를 사용하려면 해당 tablespace를 read only 시켜야 한다.
## tablespace내 하나의 특정 데이터 파일만 이동 할수 없다.
## 같은 환경을 가진 OS에서만 가능함.
## 오라클 8i 이상 사용 가능.
## DB_BLOCK_SIZE 파라메터의 값이 같아야 함.
## 같은 언어타입을 사용해야함.
## VARRAY 타입과 중첩 테이블 컬럼이 있는 테이블은 사용할수 없음.
## bit-map-index는 사용할수 없음.

## LOB 타입을 가진 테이블 또는 분할 테이블은 사용할수 있음.

SQL> conn orcl/orcl
연결되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

SQL> conn / as sysdba
연결되었습니다.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                                  ONLINE
UNDOTBS1                               ONLINE
SYSAUX                                  ONLINE
TEMP                                      ONLINE
USERS                                     ONLINE
EXAMPLE                                ONLINE
TEST                                       ONLINE

7 개의 행이 선택되었습니다.

SQL> alter tablespace test read only;         // export transport tablespace 하려면 
테이블스페이스가 변경되었습니다.              // tablespace를 read only로 변경해야한다

SQL> exit

-------------------- export transport tablespace 시작
D:\test>exp '/ as sysdba' file=aaa.dmp transport_tablespace=y tablespaces=test
.
.
엑스포트가 경고 없이 정상적으로 종료되었습니다.

D:\test>sqlplus / as sysdba

SQL> drop tablespace test including contents;

테이블스페이스가 삭제되었습니다.

SQL> select * from orcl.test;                   // tablespace가 삭제 되어서 table을 찾을수 없다.
select * from orcl.test
              *
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

SQL> exit

------------------------- aaa.dmp 파일을 이용한 tablespace import
D:\test>imp '/ as sysdba' file=aaa.dmp transport_tablespace=y tablespaces=test datafiles=D:\ORACLE\P
임포트가 경고 없이 정상적으로 종료되었습니다.

D:\test>sqlplus orcl/orcl

SQL> select count(*) from test;

  COUNT(*)
----------
        14

SQL> select tablespace_name, status from dba_tablespaces;          

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TEST                           READ ONLY

7 개의 행이 선택되었습니다.

SQL> alter tablespace test read write;               // import 완료 했으므로 다시 online으로 변경

테이블스페이스가 변경되었습니다.

------------------------------------- exp/imp table-----------------------

예 ) exp orcl/orcl file=test.dmp log=test.log tables=test
예 ) exp orcl/orcl file=test.dmp log=test.log tables=scott.emp
예 ) exp orcl/orcl file=test.dmp log=test.log tables=(scott.emp, hr.employees, scott.dept)

SQL> select count(*) from test;

  COUNT(*)
----------
        14

D:\test>exp orcl/orcl file=test.dmp log=test.log tables=ss
.
.
엑스포트가 경고 없이 정상적으로 종료되었습니다.

####### case 1) table을 drop 한 경우 #######

SQL> drop table test;

SQL> select count(*) from test;
select count(*) from test
                     *
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

D:\test>imp orcl/orcl file=table.dmp tables=test
임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

####### case 2) table을 delete 한 경우 #######

SQL> delete test;

14 행이 삭제되었습니다.

SQL> select * from test;

선택된 레코드가 없습니다.

SQL> commit;

커밋이 완료되었습니다.

D:\test>imp orcl/orcl file=table.dmp tables=test
.
.
 "CREATE TABLE "TEST" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARC"
 "HAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NU"
 "MBER(7, 2), "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA"
 "NS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFA"
 "ULT)                   LOGGING NOCOMPRESS"
경고와 함께 임포트가 정상 종료되었습니다.

// imp table을 할때 create table 구문이 들어가 있다.
// 즉, table의 row는 없더라도 같은 이름의 table이 있다면 import 되지 않는다.
// 이러한 경우 ignore=y 옵션을 사용한다.

D:\test>imp orcl/orcl file=table.dmp tables=test ignore=y

임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

####### case 2) data가 들어있는 table에 ignore 옵션을 사용할 경우 #######
SQL> select count(*) from test;

  COUNT(*)
----------
        14

D:\test>imp orcl/orcl file=table.dmp tables=test ignore=y

임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        28

// row수가 14개 더 늘어난걸 볼수있다.
// ignore 옵션은 해당 테이블에 중복 데이터가 있으면 해당 데이터를 덮어 쓰는게 아니다.

----------------------------------- exp/imp owner -----------------------

D:\test>exp '/ as sysdba' file=user.dmp log=user.log owner=orcl

엑스포트가 경고 없이 정상적으로 종료되었습니다.

SQL> drop user orcl cascade;                    // 해당 유저와 유저의 데이터까지 모두 삭제.

사용자가 삭제되었습니다.

SQL> create user orcl identified by orcl
  2  default tablespace test;

사용자가 생성되었습니다.

SQL> grant connect, resource to orcl;

권한이 부여되었습니다.

D:\test>imp '/ as sysdba' file=user.dmp fromuser=orcl touser=orcl

경고와 함께 임포트가 정상 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

------------------------------ exp/imp query -----------------------------

## export시 쿼리 문을 사용하여 해당 table내 원하는 데이터만 추출할수 있다.
   - 테이블 레벨의 export 명령어에서만 가능하다.
   - Direct 옵션과 함께 사용될 수 없다.
   - Nested 테이블을 갖는 테이블에는 적용할 수 없다.
   - Partition 테이블에 대한 export에도 적용가능하다.
   - Import 명령에는 적용되지 않는다.

D:\test>exp orcl/orcl file=dump.emp log=dump.log tables=orcl.test query='where empno=7788'

엑스포트가 경고 없이 정상적으로 종료되었습니다.

------------------------------------ 참고 사항 -----------------------------------

## UNIX reserved characters( ", ', ,< .. 등)를 사용하는 경우에는 escape ('\') 을 반드시 사용해야 한다.

----- win
예 ) exp orcl/orcl file=dump.emp log=dump.log tables=orcl.test query=\"where ename like 'SCOT%'\"
----- UNIX
예 ) exp orcl/orcl file=dump.emp log=dump.log tables=orcl.test query=\"where ename like \'SCOT%\'\"

## command line에서 query 내에 '," 을사용하지 않는 다면 '나 " 으로 query option을 사용할수 있다
   query=\'where deptno = 20\'
   query=\"where deptno = 20\"
   query=\'where deptno \< 2\' 
   (이 경우  single 이나  double quotes 을 둘다 사용할수 있다.)

## command line에서 export option을 사용할때는 반드시 escape가 있어야 하나 
   parfile을 사용할때는 eacape이 불필요하다. 
  
parfile을 사용하는 경우에는 다음과 같이 단순하게  사용이 가능하다.
query='where deptno = 20'
query="where deptno = 20"
query='where deptno < 20'

---------------------------------------------------------------------------------

########### case 1) ########### 
SQL> drop table test;

D:\test>imp orcl/orcl file=dump.emp tables=test

임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
         1

########### case 2) ########### 
SQL> delete from test where empno=7788;

SQL> commit;

SQL> select count(*) from test;

  COUNT(*)
----------
        13

D:\test>imp orcl/orcl file=table.dmp tables=test ignore=y

임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

// 실제로 imp된 7788사원은 해당 테이블의 가장 마지막 로우에 import 된다.

## 그렇다면 table 단위로 emp 하고 query로 뽑아서 import 할수 있을까?
   -> 정답은 안된다. 궁금하면 해보시라.
   LRM-00101: 알 수 없는 'query' 매개변수 이름. 이런 메세지가 기다리고 있을것이다.

---------------------------------- exp/imp parfile 방식 --------------------

## parfile 안에 해당 exp/imp 설정을 작성한후 parfile을 이용해서 exp/imp를 할수있다.

########### exp case 1
file=full.dmp
log=full.log
full=y
direct=y
.
.

########### exp case 2
file=emp.dmp
log=emp.log
tables=scott.emp
direct=y
.
.

########### exp case 3
file=scott.dmp
log=scott.log
owner=scott
direct=y
.
.
########### exp case 4
file=query.dmp
log=query.log
tables=scott.emp
query="where ename like 'SCOT%'"
direct=y
.
.

---------------------------
// 윈도우 이기 때문에 노트 패드로 pa.par 파일을 작성 한뒤 실습 내용임

D:\test>more pa.par
file=table.dmp
log=table.log
tables=orcl.test
direct=y

D:\test>exp test/test parfile=pa.par

엑스포트가 경고 없이 정상적으로 종료되었습니다.

D:\test>more impa.par
file=table.dmp
tables=test

D:\test>imp orcl/orcl parfile=impa.par

임포트가 경고 없이 정상적으로 종료되었습니다.

SQL> select count(*) from test;

  COUNT(*)
----------
        14

--------------------------------------- DIALOG 방식 ---------------------

D:\test>exp

Export: Release 10.2.0.1.0 - Production on 수 4월 15 15:54:52 2009

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

사용자명: test
암호:

다음에 접속됨: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
배열 인출 버퍼 크기 입력: 4096 > 409

엑스포트 파일: EXPDAT.DMP > 111

(1)E(전체 데이터베이스), (2)U(사용자), 또는 (3)T(테이블): (2)U > 1

권한부여 엑스포트 (yes/no): yes > yes

테이블 데이터 엑스포트 (yes/no): yes > yes

확장 영역 압축 (yes/no): yes > yes

KO16MSWIN949 문자 설정과 AL16UTF16 NCHAR 문자 설정에서 엑스포트가 종료되었습니다

전체 데이터베이스를 엑스포트하려고 합니다 ...
테이블 정의 엑스포트 중
프로파일 엑스포트 중
사용자 정의 엑스포트 중

### Lock 확인 쿼리 
SELECT do.object_name, do.owner, do.object_type,do.owner, vo.xidusn, vo.session_id, 
vo.locked_mode 
FROM v$locked_object vo , dba_objects do 
WHERE vo.object_id = do.object_id ; 

####  어떤 object에 어떤 lock이 걸렸는지 확인 
SELECT  T1.object_name, DECODE(locked_mode, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE',  4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', 'UNKNOWN') lock_mode 
FROM  dba_objects T1, v$locked_object T2 
WHERE T1.object_id = T2.object_id; 

#### session 확인 
select * from v$session where status = 'ACTIVE' 

#### cursor 확인 
v$open_cursor 

#### 테이블의 lock 확인 
SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME 
FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C 
WHERE A.SID=B.SID 
AND B.ID1=C.OBJECT_ID 
AND B.TYPE='TM' 
AND C.OBJECT_NAME IN ('<테이블이름>'); 

/******************************************************************************* 
* LOCK 관련 
*******************************************************************************/ 
--V$LOCK 을 사용한 잠금 경합 모니터링 
SELECT s.username, s.sid, s.serial#, s.logon_time, 
  DECODE(l.type, 'TM', 'TABLE LOCK', 
        'TX', 'ROW LOCK', 
      NULL) "LOCK LEVEL", 
  o.owner, o.object_name, o.object_type 
FROM v$session s, v$lock l, dba_objects o 
WHERE s.sid = l.sid 
AND o.object_id = l.id1 
AND s.username IS NOT NULL    

--락이 걸린 세션 자세히 알아보기 
select a.sid, a.serial#,a.username,a.process,b.object_name, 
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK", 
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL", 
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS" 
from v$session a,dba_objects b, v$lock c 
where a.sid=c.sid and b.object_id=c.id1 
and c.type='TM' 

--락이 걸린 세션 간단히 알아보기 
select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait, 
      a.logon_time, a.process, a.osuser, a.terminal 
from v$session a, v$lock b, dba_objects c 
where a.sid = b.sid 
  and b.id1 = c.object_id 
  and b.type = 'TM'; 

select a.sid, a.serial#, a.username, a.process, b.object_name 
from v$session a , dba_objects b, v$lock c 
where a.sid=c.sid and b.object_id = c.id1 
and c.type = 'TM' 

--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우 
--아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다 
--kill -9 프로세스아이디 
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID", 
s.sid "SESSION ID", s.serial#, osuser "OS USER", 
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT" 
from v$process p, v$session s, v$access a 
where a.sid=s.sid and 
p.addr=s.paddr and 
s.username != 'SYS' 

--위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다 
ALTER SYSTEM KILL SESSION '11,39061' 

SELECT 
      a.sid,            -- SID 
      a.status,        -- 상태정보 
      a.process,      -- 프로세스정보 
      a.osuser,        -- 접속자의 OS 사용자 정보 
      b.sql_text,      -- sql 
      c.program      -- 접속 프로그램 
FROM v$session a, 
          v$sqlarea b, 
          v$process c 
WHERE a.sql_hash_value=b.hash_value 
AND a.sql_address=b.address 
AND a.paddr=c.addr 
AND a.status='ACTIVE';  -- 현재 상태가 ACTIVE인것

$ORACLE_HOME/dbs/init<SID>.ora 파일의 

processes 변수를 setting 해주시면 됩니다. 

processes = 200; 

만약 프로그램을 연동 하실때는 application에서 connection 

을 잘 닫아 주셔야 합니다. 동시접속자수를 초과했다는 

에러가 나면 이 parameter를 먼저 올리려고 하는데 실제로 

B to C,B toB 사이트에서는 동시 접속자(end user)가 

보통 5를 넘지 않습니다. 만약 100인데 차버리면 connection 

닫는 곳에서 정확히 handling(java는 finally절에서 

connection및 statement 를 null 

check해서 닫아줌) 해주시면 이런 에러가 나지 않습니다. 

<그리고,processes의 값을 늘려 주실때 solaris의 경우는 

세마포어 변수보다 processes가 클 수가 없으므로 만약 늘이려 

는 processes의 수가 세마포어 변수 보다 크다면 먼저 세마포 

어 변수(set eminfo_semmns=400)를 먼저 늘려 주셔야겠죠

오라클DB 사용시 IP나 컴퓨터 이름등 필요한 정보 얻어오기

select 
          SYS_CONTEXT('USERENV','TERMINAL') terminal, /* 클라이언트 컴퓨터명*/ 
          SYS_CONTEXT('USERENV','LANGUAGE') language, 
          SYS_CONTEXT('USERENV','SESSIONID') sessionid, 
          SYS_CONTEXT('USERENV','INSTANCE') instance, 
          SYS_CONTEXT('USERENV','ENTRYID') entryid, 
          SYS_CONTEXT('USERENV','ISDBA') isdba, 
          SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory, 
          SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency, 
          SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar, 
          SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format, 
          SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language, 
          SYS_CONTEXT('USERENV','NLS_SORT') nls_sort, 
          SYS_CONTEXT('USERENV','CURRENT_USER') current_user, 
          SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid, 
          SYS_CONTEXT('USERENV','SESSION_USER') session_user, 
          SYS_CONTEXT('USERENV','SESSION_USERID') session_userid, 
          SYS_CONTEXT('USERENV','PROXY_USER') proxy_user, 
          SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid, 
          SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain, 
          SYS_CONTEXT('USERENV','DB_NAME') db_name, 
          SYS_CONTEXT('USERENV','HOST') host, 
          SYS_CONTEXT('USERENV','OS_USER') os_user, 
          SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name, 
          SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address, /* 클라이언트 컴퓨터IP*/ 
          SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol, 
          SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id, 
          SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id, 
          SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') 
authentication_type, 
          SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') 
authentication_data, 
          SYS_CONTEXT('USERENV','CURRENT_SQL') current_sql, 
          SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') client_identifier, 
          SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') 
global_context_memory 
  from dual; 


SELECT SYS_CONTEXT('USERENV','SESSIONID') FROM DUAL ; 

select sid from v$session where audsid=userenv('sessionid') ; 

select sid, serial# from v$session where audsid=userenv('sessionid') ; 

SELECT SID, SERIAL# FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');

※ Oracle SID 확인 
SQL> select instance from v$thread; 
  
※ Oracle DB_NAME 확인 
SQL> select name from v$database; 
  
※ Oracle User 확인 
SQL> select * from all_users; 
  
※ 등록된 User 목록 보기 
SQL> select username, user_id from dba_users order by username; 
  
※ User가 소유한 모든 테이블 보기 
SQL> select table_name from user_tables; 
  
※ 사용자 정보 확인 
SQL> select username, default_tablespace,temporary_tablespace from dba_users; 
  
※ 오브젝트 조회 
SQL> select * from all_objects where object_name like '명'; 
  
※ 테이블 조회 
SQL> select * from all_tables where table_name like '명'; 
  
※ 시퀀스 정보 보기 
SQL> select * from user_sequences; 
  
※ 시노님 조회 
SQL> select * from all_synonyms where synonym_name='명'; 
  
※ 테이블 인덱스 정보 조회 
SQL> select * from all_ind_columns where table_name='테이블명'; 
  
※ 테이블의 컬럼 정보 조회 
SQL> select * from all_tab_columns where table_name='테이블명'; 
  
※ table comment 쿼리 
SQL> select * from all_tab_comments where table_name='테이블명'; 
  
※ column comment 쿼리 
SQL> select * from all_col_comments where table_name='테이블명'; 
  
※ TABLE 구조 보기 
SQL> DESC 테이블명; 
  
※ 테이블 존재 여부 확인 
SQL> select 테이블명 
          from USER_TABLES 
          where 테이블명='찾을 테이블 이름' 
                                                    ┗ 대문자 
  
※ 모든 유저 보기 
SQL> select username from all_users 
  

※ 테이블 제약조건 확인 
SQL> select table_name, constraint_name, constraint_type 

          from user_constraints 

          where table_name='STORM_CONTENT'; 



◈ 선택한 Row만큼만 보여주기 

SQL>SELECT name 
        FROM storm_board 
        WHERE rownum <= 10 
---> 이렇게 하면 데이터가 1000건이 있더라도, 1~10건만 보여주게 된다. 
  
  
◈ ’LIKE’ 연산자를 사용하여 case insensitive search를 할 수 있는 방법 
  
필드에 ’UPPER’ 함수를 사용하시면 됩니다 
SQL>SELECT name 
        FROM storm_board 
        WHERE UPPER(name) like ’%K%’ 


◈ 테이블에 새로운 컬럼의 추가 

SQL>ALTER TABLE table_name ADD ( column_name DATATYPE ); 
  
ex)SQL>ALTER TABLE storm_board ADD(ip_addr VARCAHR2(15) not null) 


◈ 테이블 컬럼의 삭제 

SQL>ALTER TABLE table_name DROP COLUMN column_name 

ex)SQL>ALTER TABLE storm_board DROP COLUMN ip_addr 
-->컬럼의 삭제는 오라클 8i버전부터 되는것으로 알고 있습니다.  
  
  
◈ SQLPLUS에서 SQL문의 실행 속도 알아보기 
  
SQL>SET TIMING ON 

을 한 후 SELECT문을 수행하면 경과 시간이 나옵니다. 
  
SQL> SELECT COUNT(name) FROM storm_board; 
  
COUNT(NAME) 
----------- 
        286 
  
경  과: 00:00:00.10 


◈ SQLPLUS에서 SELECT문 사용시 데이타만 출력되고 HEADER는 안나오게 하는 방법 
  
SQL>SET HEADING OFF 
한후 SELECT문을 수행하면 아래와 같은 결과가 나옵니다. 
  
SQL> SELECT empno, ename, mgr 
        FROM emp 
        WHERE rownum < 6; 

7369  SMITH            7902 
7499  ALLEN            7698 
7521  WARD            7698 
7566  JONES            7839 
7654  MARTIN          7698

-- 데이타 파일별 READ/WRITE 확인 쿼리 -- 
select f.phyrds, f.phywrts, d.name 
from v$datafile d, v$filestat f 
where d.file# = f.file#;  

-- 데이타 파일별 용량 확인 쿼리 -- 
select sysdate as check_time, 
      b.file_name as file_name, 
      b.tablespace_name as tablespace_name, 
      b.bytes/1024 as total_size_KB, 
      ((b.bytes - sum(nvl(a.bytes,0))))/1024 as user_kb, 
      (sum(nvl(a.bytes,0)))/1024 as free_size_kb, 
      trunc(((sum(nvl(a.bytes,0))/(b.bytes))*100),2) as free_percent 
 from dba_free_space a, dba_data_files b 
where a.file_id(+) = b.file_id 
group by b.tablespace_name, b.file_name, b.bytes 
order by b.file_name,b.tablespace_name 

-- 테이블스페이스 공간조회 스크립트 -- 
select a.tablespace_name, 
a.totbytes, 
a.totbytes - b.freebytes as usedbytes, 
b.freebytes, 
a.totblocks, 
a.totblocks - b.freeblocks as usedblocks, 
b.freeblocks 
from 
( 
select tablespace_name, 
sum(bytes) as totbytes, sum(blocks) as totblocks 
from dba_data_files 
group by tablespace_name 
) a, 
( 
select tablespace_name, 
sum(bytes) as freebytes, sum(blocks) as freeblocks 
from dba_free_space 
group by tablespace_name 
)B 
where a.tablespace_name = b.tablespace_name 
and a.tablespace_name like 'OWRA%' 

--현재 사용중인 tablespace 확인 쿼리 
select distinct tablespace_name from dba_segments; 

-- 테이블스페이스 중에 Object가 한개도 없는것을 찾는 쿼리. 
select tablespace_name 
from dba_tablespaces 
minus 
select distinct tablespace_name 
from dba_segments; 

-- 테이블스페이스의 Online/Offline 확인 
select tablespace_name, status, case when status = 'ONLINE' then '읽고쓰기 가능(DML가능)' else 'X' end as status_desc 
from dba_tablespaces; 

-- 현재의 TableSpace와 Datafile 확인 
select file_name, tablespace_name, bytes 
 from dba_data_files; 
  
-- 테이블별 점유용량 확인. 
select segment_name, bytes 
  from user_segments 
 where segment_type ='TABLE';