'DB/ORACLE'에 해당되는 글 16건

SUBSTRB

- 특정 문자열의 BYTE 단위로 일부만 추출 

ex)

SELECT 'TEST테스트', 

        SUBSTRB('TEST테스트', 1, 4), 

        SUBSTRB('TEST테스트', 2, 4), 

        SUBSTRB('TEST테스트', 3) 

FROM DUAL; 

ALTER TABLE TEMP_TEST ADD (F VARCHAR(10));

-- 추가된 컬럼에는 기본값으로 널값이 들어갑

ALTER TABLE TEMP_TEST ADD (F VARCHAR(10) DEFAULT 'AA');

-- 추가된 컬럼에는 기본값으로 'AA'값이 들어감

ALTER TABLE TEMP_TEST ADD (G VARCHAR(10) NULL);

-- NULL이란 키워드를 사용하면 에러발생

 

ALTER TABLE TABLE_NAME ADD (COLUMN_NAME DATA_TYPE NOT NULL);

-- 해당 테이블에 로우가 없다면 정상실행

-- 해당 테이블에 로우가 있다면 에러발생

 

ALTER TABLE TEMP_TEST ADD (E VARCHAR(10) NOT NULL);

-- 현재 입력된 로우가 없다면 정상실행 로우가 있다면 에러발생

-- 위의 명령이 아래상황 일때는 실행됨

CREATE TABLE TEMP_TEST1

(A CHAR(2), B CHAR(2) NOT NULL);

COMMIT;

ALTER TABLE TEMP_TEST1 ADD C CHAR(2) NOT NULL;

SELECT * FROM TEMP_TEST1;

 

ALTER TABLE T_RA020 ADD RES_CD CHAR(2);

-- 기존에 로우가 있다면 추가한 컬럼값은 기본값이 널값으로 들어감

ALTER TABLE T_RA020 ADD POS_CD CHAR(2) DEFAILT '11';

-- 기존에 로우가 있다면 추가한 컬럼값은 기본값이 '11'값으로 들어감

 

 

 

컬럼 수정

 

ALTER TABLE T_KA031 MODIFY (FILE_NM_C CHAR(200) NOT NULL);

-- 기존에 있는 값중에 널값이 있다면 에러발생

-- 기존에 있는 값중에 널값이 없다면 정상실행

-- file_nm_c의 컬럼이 null인데 위의 명령어 쓰면 에러발생 아래의 명령어 써야 정상실행

ALTER TABLE t_bc016 MODIFY etc_remark CHAR(100);

 

ALTER TABLE T_KA031 MODIFY FILE_NM_C CHAR(200) NOT NULL;

-- 위의 명령과 같은 역할을 한다.

ALTER TABLE T_KA031 MODIFY FILE_NM_C CHAR(200) NULL;

-- 에러발생

ALTER TABLE t_ba060 MODIFY (dcsn_idea lvarchar(1000) NOT NULL);

-- 1000바이로 수정

 

char(300)이면 memo컬럼으로 변경됨

 

※ 인포믹스에서는 ALTER 명령후에 COMMIT 처리를 꼭 해야한다.

※ 인포믹스에서는 NOT NULL 제약조건을 안쓰면 기본적으로 NULL로 설정된다.

 

ALTER TABLE 명령시 "could not open database table" 에러출력 대처방법

 

테이블에 ALTER TABLE 명령시 "could not open database table" 에러가 발생하면 현재 

테이블을 티맥스 서비스 파일에서 물고 있거나 제우스 웹서버에서 물고 있는중이므로 

티맥스 서버와 제우스 웹서버를 내렸다가 실행후 다시 올리면 된다.

SELECT ORG_CD,
       UPP_ORG_CD,
       ORG_NM, 
       TO_NUMBER(ORG_CD,'XXXXXXXXXXXXXXX') AS ORG_CD_INT, 
       TO_NUMBER(UPP_ORG_CD,'XXXXXXXXXXXXXXX') AS UPP_ORG_CD_INT 
  FROM ecm006ct

alter system set DEFERRED_SEGMENT_CREATION=FALSE scope=both;

Oracle 11g R2에는 디스크 공간을 확보하는 새로운 기능이 있다.

새로운 파라미터인 "DEFERRED_SEGMENT_CREATION"이 추가되었는데 초기값은 "TRUE"로 되어 있다.

이 파라미터는 exp를 통해 스키마를 export할 때 영향을 준다.

문제는 일반적인 exp 명령어를 이용해 export하면 빈 테이블들은 백업되지 않는다는 것이다.

Data Pump(expdp)를 이용한 export는 문제가 없다.

이 기능을 비활성화 시키기 위해 아래와 같이 파라미터의 값을 변경하면된다.

SQL> alter system set DEFERRED_SEGMENT_CREATION=FALSE scope=both;

이 기능을 적용한 후에 생성된 빈 테이블을은 백업이 잘되지만 이미 생성되어 있던 테이블들은 적용되지 않으므로 

tablespace를 이동하거나 테이블을 재생성해야 한다.

이제, 파라미터 변경 후에 생성된 테이블들은 exp를 통해서도 잘 백업되어진다.

권한 확인
SELECT *
  FROM USER_TAB_PRIVS;
  
 비밀번호 강제 변경 
 sqlplus "/as sysdba"

ALTER USER 아이디 IDENTIFIED BY 암호;

SELECT A.SESSION_ID SID,
B.SERIAL# SERIAL_NO,
A.OS_USER_NAME OS_USER_NAME,
A.ORACLE_USERNAME ORACLE_USERNAME,
B.STATUS STATUS
FROM V$LOCKED_OBJECT A, V$SESSION B
WHERE A.SESSION_ID = B.SID;

SELECT *
  FROM V$LOCKED_OBJECT;

ALTER SYSTEM KILL SESSION 'SID, SERIAL_ID';

※DBA_FREE_SPACE, DBA_DATA_FILES 데이터 사전 

SELECT b.file_name "FILE_NAME", -- DataFile Name 
       b.tablespace_name "TABLESPACE_NAME", -- TableSpace Name 
       b.bytes / 1024 "TOTAL SIZE(KB)", -- 총 Bytes 
       ((b.bytes - sum(nvl(a.bytes,0)))) / 1024 "USED(KB)", -- 사용한 용량 
       (sum(nvl(a.bytes,0))) / 1024 "FREE SIZE(KB)", -- 남은 용량 
       ROUND((sum(nvl(a.bytes,0)) / (b.bytes)) * 100, 2) "FREE %" -- 남은 % 
  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.tablespace_name

오라클 impdp, expdp 사용법

* 초기작업 *
- sys계정접속
create directory ccw_dir as 'D:\ccw_dbdump';  (디렉토리 생성)
grant read,write on directory to ccw_test;    (해당 user 디렉토리접근 권한부여)

1. expdp 

expdp 유저아이디/유저패스워드@SID dumpfile=덤프명.dmp logfile=로그명.log directory=디렉토리이름(data_pump_dir) version=10.2.0.1.0

2.  impdp

impdp 유저아이디/유저패스워드 dumpfile=덤프명.dmp REMAP_SCHEMA=exp유저:imp유저 REMAP_TABLESPACE=exp테이블스페이스명:imp테이블스페이스명

* 명령어 *
expdp CCW_TEST/CCW_TEST@ORCL dumpfile=ccw_20150910.dmp log_file=ccw_20150910.log directory=ccw_dir

impdp CCW_TEST/CCW_TEST dumpfile=ccw_20150910.dmp REMAP_SCHEMA=CCW_TEST:CCW_TEST REMAP_TABLESPACE=CCW_TAB_TBS:TSDT_CCW


참조url :http://koeiking11.tistory.com/189

Oracle DataBase 문자셋과 언어셋 확인 및 변경하기

Oracle DataBase 문자셋과 언어셋 확인하기
[ 문자셋 확인 ]
   SELECT name, value$
   FROM sys.props$ 
   WHERE name = 'NLS_CHARACTERSET'; 

   SELECT name, value$
   FROM sys.props$ 
   WHERE name = 'NLS_NCHAR_CHARACTERSET'; 

[ 언어셋 확인 ]
   SELECT name, value$
   FROM sys.props$ 
   WHERE name = 'NLS_LANGUAGE'; 

Oracle DataBase 문자셋과 언어셋 변경하기
- 문자셋 변경 : Oracle DataBase 관리자로 접속하여 NLS_CHARACTERSET, NCHAR의 CHARACTERSET에 원하는 문자셋을 변경할 수 있다.
- 언어셋 변경 : 문자셋과 마찬가지로 오라클 데이터베이스 관리자로 접속하여 NLS_LANGUAGE에 원하는 언어셋을 변경할 수 있다.

[ 문자셋 변경 ]
   UPDATE sys.props$
   SET value$ = '[ 변경을 원하는 문자셋 (KO16KSC5601, AL32UTF8 등)]'
   WHERE name = 'NLS_CHARACTERSET';

   UPDATE sys.props$
   SET value$ = '[ 변경을 원하는 문자셋 (KO16KSC5601, AL32UTF8 등)]'
   WHERE name = 'NLS_NCHAR_CHARACTERSET';

[ 언어셋 변경 ]
   UPDATE sys.props$
   SET value$ = '[ 변경을 원하는 문자셋 (AMERICAN_AMERICA.KO16KSC5601, AMERICAN_AMERICA.AL32UTF8등)]'
   WHERE name = 'NLS_LANGUAGE'';

출처 : Tong - 시우세상님의 Oracle통

 

Oracle Character set 변경 자세히

####################################################
#### .bash_profile 환경변수
    export ORACLE_BASE=/home/oracle
    export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1
    export ORACLE_SID=ora10
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib
    #export LD_ASSUME_KERNEL=2.4.19
    export PATH=$PATH:$ORACLE_HOME/bin
    
    export NLS_LANG=KOREAN_KOREA.KO16KSC5601       #한글
    export NLS_LANG=KOREAN_KOREA.KO16MSWIN949    #한글(추천:지원캐릭터가 더 많음 -뷃,숖..)
    export NLS_LANG=AMERICAN_AMERICA.UTF8            #유니코드


    
####################################################
#### 캐릭터 셋 설정 확인

    SELECT NAME,VALUE$ FROM PROPS$ WHERE NAME ='NLS_LANGUAGE' OR NAME ='NLS_TERRITORY' OR NAME ='NLS_CHARACTERSET';


####################################################
#### 오라클 캐릭터 셋 변경

    update props$ set VALUE$='UTF-8' where name='NLS_CHARACTERSET';
    update props$ set VALUE$='KO16MSWIN949' where name='NLS_CHARACTERSET';
    update props$ set VALUE$='KO16KSC5601' where name='NLS_CHARACTERSET';
    
    update props$ set VALUE$='KOREAN' where name='NLS_LANGUAGE';
    update props$ set VALUE$='KOREA' where name='NLS_TERRITORY';
    


####################################################
#### 캐릭터 셋 변경후에 확인 사항(필수)

    에러 유형 : 
        EXP-00008: ORACLE 오류 6552가 발생했습니다
        ORA-06552: PL/SQL: Compilation unit analysis terminated
        ORA-06553: PLS-553: 알 수 없는 문자 집합 이름입니다


    Problem description
    ===================
    You receive the following error when (re)compiling or calling a piece of pl/sql:
    ORA-06550: line <num>, column <num>: ....
    or
    ORA-06552: PL/SQL: Compilation unit analysis terminated
    followed by
    ORA-06553: PLS-553: character set name is not recognized
  에러 원인 :  character set 이 섞여있음.

 

    캐릭터셋 확인 쿼리 : 
    select distinct(nls_charset_name(charsetid)) CHARACTERSET,
           decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
                         9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
                        96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
                       112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
       from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112);

    캐릭터셋 확인 쿼리 결과(잘못된 경우): varchar2가 2개 의 캐릭터셋이 설정되어있음.
        CHARACTERSET                            TYPES_USED_IN
        -----------------------------------------------------
        AL16UTF16                               NCHAR
        AL16UTF16                               NVARCHAR2
        AL16UTF16                               NCLOB
        US7ASCII                                CHAR
        US7ASCII                                VARCHAR2
        WE8DEC                                 VARCHAR2
        US7ASCII                                CLOB
        
        
    캐릭터셋 확인 쿼리 결과(정상인 경우): TYPES_USERD_IN 하나당 하나의 캐릭터셋
        CHARACTERSET                            TYPES_USED_IN
        -----------------------------------------------------
        AL16UTF16                               NCHAR
        AL16UTF16                               NVARCHAR2
        AL16UTF16                               NCLOB
        AL32UTF8                                CHAR
        AL32UTF8                                VARCHAR2
        AL32UTF8                                CLOB


    해결 방법 : 

        1. INIT.ORA 안에 있는 parallel_server parameter 가 false 거나 아예 세팅되어있지 않은지 확인.
           SQL>show parameter parallel_server

        2. sqlplus "/as sysdba"로 다음 쿼리 실행(기존 데이터 백업 필수)
           SHUTDOWN IMMEDIATE;
           STARTUP MOUNT;
           ALTER SYSTEM ENABLE RESTRICTED SESSION;
           ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
           ALTER SYSTEM SET AQ_TM_PROCESSES=0;
           ALTER DATABASE OPEN;
           COL VALUE NEW_VALUE CHARSET
           SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
           COL VALUE NEW_VALUE NCHARSET
           SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
   
           -- UTF8로 바꿀 경우(선택)
           ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
           ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
   
             -- 한글로 바꿀 경우(선택)
           ALTER DATABASE CHARACTER SET INTERNAL_USE KO16MSWIN949
           ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
           
           -- oracle reboot 2번.
           SHUTDOWN IMMEDIATE;
           STARTUP;
           SHUTDOWN IMMEDIATE;
           STARTUP;

        3. parallel_server parameter 수정한 경우 원복.

regexp_replace(a1.SCNCENM,'[가-힛]','')