기본적으로 오라클에는 특수기호에 대해서 검사를 하고 대체할 수 있도록 정의를 해놓은 것이 있는데 이 중에 특수기호 '&'도 포함이 되어있다. 

따라서 insert into table1 values ( 'a & b'); 라는 문장을 실행하면 SQLDeveloper에서는 대체문자를 설정하라고 팝업창이 뜬다.

처음에는 이게 뭐지? 하고서 그냥 엔터를 쳤었는데 나중에 보니 그렇게 엔터쳤던 것 때문에 insert할 때 &가 공백으로 바뀌어져서 내용이 들어가있었다.

그런데 이렇게 특수기호가 들어간 문장을 수 백개 insert해야 한다면....???

일일히 다 대체문자를 입력할 수가 없다!!!

이럴 때 유용하게 쓸 수 있는게

SQL> set define off;

이다.

위 명령어는 현재 세션에 대해서 "특수기호 대체 검사"를 하지 않도록 해준다. 

How to increase the number of processes at database level in Oracle?

1.  Log on to the database as system administrator

•  In SQL Plus enter: "connect / as sysdba"

2. Set the number of processes to be desired value <num_processes>

• In SQL Plus "alter system set processes=<num_processes> scope=spfile"

3. Shutdown the database

• In SQL Plus "shutdown immediate"

4. Startup the database 

• In SQL Plus "startup"

5. Check the changes have taken effect.

• In SQL Plus "show parameter sessions" and "show parameter processes"

​출처 : http://knowledgebase.progress.com/articles/Article/P164971

CentOS 6에 오라클 11g 설치시에 설정해주는 커널 파라미터 의미

ㅇ 커널 파라미터 설정

/etc/sysctl.conf 파일

fs.aio-max-nr = 1048576
fs.file-max = 6815744
#kernel.shmall = 2097152
#kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

아래 링크를 참조하시길 바랍니다.

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/5/html-single/Tuning_and_Optimizing_Red_Hat_Enterprise_Linux_for_Oracle_9i_and_10g_Databases/#sect-Oracle_9i_and_10g_Tuning_Guide-Setting_Semaphores-Setting_Semaphore_Parameters

Red Hat Enterprise Linux Oracle Tuning Guide

access.redhat.com

https://docs.oracle.com/cd/E11882_01/install.112/e24326/toc.htm#BHCCADGD

Oracle® Database Quick Installation Guide

docs.oracle.com

select 

owner, segment_name, segment_type, sum(bytes)/1024/1024 as MB

from 

dba_segments

where

owner='{username in uppercase}' 

group by owner, segment_name, segment_type order by MB

ORA-01157: cannot identify/lock data file

데이타 파일을 실수로 삭제해버렸다가 DB재시작하게되면 아래처럼 오류가 발생하게 된다.

내가 아닌 누군가가 dbf생성했다가 drop명령을 쓰지않고 unix 콘솔에서 dbf파일을 삭제하게 되면 발생하게되는 오류인데 생각보다 쉽게 해결할 수 있습니다.

ERROR at line 1:
ORA-01157: cannot identify/lock data file 29 - see DBWR trace file
ORA-01110: data file 29:
'/opt/oracle/product/oracle9i/dbs/C:oracleproduct10.2.0oradatapentahoptho_ts.dbf
'

from above datafile name you have realized that its a kinda jerk :s someone has made a datafile with no sense and then he/she have removed the file by O.S command, but he/she did'nt updated database about it !

So during the test when we were starting this database it came untill mount stage and then got stuck !!! i.e.

Problem:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 29 - see DBWR trace file
ORA-01110: data file 29:
'/opt/oracle/product/oracle9i/dbs/C:oracleproduct10.2.0oradatapentahoptho_ts.dbf
'

so to fix it, i did following:

Solution:


SQL>alter database datafile 29 OFFLINE DROP;
SQL>alter database open;


출처 : http://nayyares.blogspot.kr/2009/08/ora-01157-cannot-identifylock-data-file.html

여러가지 이유로 테이블을 삭제할 상황이 있다.

Drop table로 하나하나 지우기에 너무 양이 많을때 테이블 전체를 한번에 삭제하는 방법이 있다.

그 방법을 아래에 소개하겠다.

먼저 sql에 접속한 후

SQL> Begin

2    for c in (select table_name from user_tables) loop

3    execute immediate ('drop table '||c.table_name||' cascade constraints');

4    end loop;

5    End;

6    . <- 쩜 찍어야 합니다.

엔터

이렇게 입력하면

SQL>

다시 이렇게 아무것도 안뜨는 명령어가 뜬다.

SQL> run

을 실행한다.

실행하면 아래 구문이 나올것이다.

1    Begin

2    for c in (select table_name from user_tables) loop

3    execute immediate ('drop table '||c.table_name||' cascade constraints');

4    end loop;

5    End;

PL/SQL procedure successfully completed.<--메시지가 나오면 성공

그 후에

SQL> purge recyclebin; 

Recyclebin purged.<===휴지통이 비워졌다는 문구가 나온다.

그후 select * from tab;을 해보면 깨끗히 지워진 테이블을 볼 수 있을것이다.

출처 : http://munjee.tistory.com/16

일반 사용자 계정으로 제약사항 목록을 조회하려면 아래 쿼리를 사용하면 됩니다.

select * from user_constraints;

여러개의 테이블을 한꺼번에 삭제하고 싶은데 like 조건절을 쓸 수가 없다.

뭐 결국 테이블 하나하나를 각각 삭제해줘야 한다는 건데....그렇다고 테이블이 수 십개가 넘는데 이걸 다 손으로 타이핑하기란 짜증하는 일이 아닐 수 없다.

이럴 때 다음 쿼리로 특정 사용자의 전체 테이블을 삭제 할 수 있는 쿼리를 얻어올 수 있다.

SELECT 'DROP TABLE ' || TABLE_NAME || ';' FROM ALL_TABLES WHERE OWNER = 'user1';

조건절은 마음대로 바꿀 수 있고 위에서 사용한 ALL_TABLES는 DBA권한이 있어야 접근이 가능하다.

따라서 일반 사용자이면서 자신이 소유한 테이블을 삭제할 때는

ALL_TABLES 대신 USER_TABLES 에서 조회한 테이블명을 사용하면 된다.

SELECT 'DROP TABLE ' || TABLE_NAME || ';' FROM USER_TABLES WHERE OWNER = 'user2';

테이블을 삭제하려면
SQL > drop table 테이블명;

하면 됩니다. 그런데 사실 이게 물리적으로 공간을 다시 내주는 것이 아닙니다.
오라클의 drop table관련 문서를 보면 테이블을 drop할 때 purge라는 옵션을 주지 않으면 휴지통으로 넣기만 하고 물리적인 공간은 그대로 가지고 있게 됩니다. 윈도우의 휴지통 개념과 동일하다고 보면 됩니다.

오라클이 10g에서 새롭게 추가한 '휴지통' 이라는 기능.

테이블을 DROP 하더라도 언제던지 복원할 수 있게 하고자 하는 것이고요,
윈도우의 휴지통과 같다고 보시면 됩니다.

휴지통에 들은 테이블을 조회.
SQL> show recyclebin;

휴지통의 모든 내용이 비워집니다.
SQL> purge recyclebin;

삭제된 테이블을 되살리고 싶다면
SQL> flashback table 테이블명 to before drop;

만약, 특정 테이블을 휴지통에 남기지 않고 모두 삭제하려면..
SQL> drop table 테이블명 purge;

purge문 없이 그냥 drop 한 후에는
SQL> purge table 테이블명;

instr함수는 어떤 스트링의 특정위치에서 시작해서 특정 케릭터가 위치한 곳까지의 위치를 계산해서 반환해주는 함수입니다.

Java의 split 메소드와 같은 기능을 구현할때 오라클의 substr함수와 함께 같이 사용될 수 있죠.

오라클 문서에서 설명하는 instr함수에 대한 정의 및 문법은 아래와 같습니다.

The INSTR functions (INSTR, INSTRB, INSTRC, INSTR2, and INSTR4) searches a string for a substring using characters and returns the position in the string that is the first character of a specified occurrence of the substring. The functions vary in how they determine the position of the substring to return.

• INSTR calculates lengths using characters as defined by the input character set.

• INSTRB calculates lengths using bytes.

• INSTRC calculates lengths using Unicode complete characters.

• INSTR2 calculates lengths using UCS2 code points.

• INSTR4 calculates lengths using UCS4 code points.

반환값

A nonzero INTEGER when the search is successful or 0 (zero) when it is not.

문법 

{INSTR | INSTRB | INSTRC | INSTR2 | INSTR4} (string , substring [, position [, occurrence]])

그럼 간단한 예제를 한번 보시죠.

abc.def.ghi.jkl 이라는 패키지명이 있다고 해봅시다.

이 패키지명에서 abc.def ( 두번째 depth 까지)만 추출해내고 싶은데 그러려면 substr을 생각하실 수도 있겠죠.

select substr(packageNm, 1, 7) from ClassTable;

이렇게 하면 결과는 abc.def 가 나올 것입니다.

하지만 만약 패키지명이 3글자.3글자 형태가 아니면 어떻게 될까요?

a.b.c.d.e.f 라는 패키지명을 위처럼 자르면 a.b.c.d 라고 잘려서 나오겠죠?

두번째 depth가 아니라 7번째까지 출력이 되버리겠군요.

이때 instr 함수를 사용을 합니다.

select substr( packageNm, 1, instr( packageNm, '.', 1, 2) - 1 ) ) from ClassTable;

이렇게 함수를 써줍니다.

instr( packageNm, '.', 1, 2 ) 라는 부분은 packageNm이라는 스트링을 '.' (마침표)로 구분을 짓고 1번째 인덱스에서 시작해서 2번째 마침표가 나오는 인덱스를 반환을 합니다. 그러면 마침표가 있는 인덱스가 나오기 때문에 -1을 해준 값을 substr함수의 length에 넣어주면 두번째 depth까지 substr한 결과가 나오게 됩니다.

### 캐릭터셋 변경전 확인 사항 :

sqlplus '/as sysdba'

sql>select instance from v$thread;

INSTANCE
----------------
ora9i


oracle\ora92\network\admin\snmp_ro.ora 를 봐도 된다.
=> snmp.SID.Oracle = ORACLE




select parameter, value from nls_database_parameters where parameter like '%CHAR%';

########### 캐릭터셋 변경 ##########################

****  connect sys as sysdba;

NLS CHARACTERSET 변경방법 (DB REBUILD 없이)
Bulletin no : 10016

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

  데이타베이스의  CHARACTER SET은 데이타 딕셔너리 테이블인 sys.props$에
 들어 있다

   SQL>desc sys.props$
   Name                                Null?                  Type
   -------------------------------  -----------------      ---------------
   NAME                               NOT NULL             VARCHAR2(30)
   VALUE$                                                  VARCHAR2(2000)
   COMMENT$                                                VARCHAR2(2000)

   SQL>column c1 format a30
   SQL>select name c1, value$ c1 from sys.props$;

   C1                              C1
   -----------------------------   ------------------------------
   DICT.BASE                        2
   NLS_LANGUAGE                     AMERICAN
   NLS_TERRITORY                    AMERICA
   NLS_CURRENCY                     $
   NLS_ISO_CURRENCY                 AMERICA
   NLS_NUMERIC_CHARACTERS           .,
   NLS_DATE_FORMAT                  DD-MON-YY
   NLS_DATE_LANGUAGE                AMERICAN
   NLS_CHARACTERSET                 US7ASCII
   NLS_SORT                         BINARY
   GLOBAL_DB_NAME                   NLSV7.WORLD
   
  여기서 NLS_CHARACTERSET에 현재 DB의 CHARACTER SET이 들어 있는데 이 값을
 변경하여 DB의 CHARACTER SET을 변경할 수 있다. 여기서는 US7ASCII에서
 KO16KSC5601 로 옮기는 경우를 알아보자.

 우선 바꾸고자 하는 CHRACTER SET이 지원되는 지를 다음 명령으로 확인한다.

         select convert('a','WE8DEC','KO16MSWIN949') from dual; 

  만약 이 Select 문에서 ORA-01482 에러가 발생하면 지정한 CHARACTER SET이
 지원되지 않는  경우이며  에러가 발생하지 않으면 CHARACTER SET을 변경할 수
 있다.
 
  작업을 하기전에는 만약을 위해서 DB 전체를 백업 받아두도록 한다.
  CHARACTER SET 을 잘못 변경하면 DB 를 OPEN 할수가 없기 때문이다.
  ---------------------------------------------------------------

1.  다음의 Update문을 실행하여 CHARACTER SET을 변경한다.

   UPDATE sys.props$
   SET value$ = 'WE8DEC'
   WHERE name = 'NLS_CHARACTERSET';

  Update 시에 NLS_CHARACTERSET을 지원되지 않는 값으로 잘못 설정하거나
 실수로 콘트롤 문자 같은 것이 들어가게 되면 DB가 Shutdown 된 다음에는
 Startup 이 안 되므로 Update 후에 다음 명령으로 확인을 한 다음에  Commit을
 하도록 한다.

        select name, value$
        from sys.props$
        where value$ = 'KO16KSC5601';

 Select   제대로 출력되면 Commit 하고 Shutdown 했다가 Startup 하게 되면
 새로운 CHARACTER SET 값을 갖게 된다. SELECT가 안 되면 ROLLBACK하고 UPDATE
 부터 다시 하도록 한다.

2. 환경 변수 NLS_LANG 을 변경한다.

.profile ( or .cshrc) 에서

NLS_LANG=American_America.KO16KSC5601; export NLS_LANG

or

setenv NLS_LANG American_America.KO16KSC5601

출처 : http://develop.sunshiny.co.kr/201

SQL 커맨드라인에다가 2줄짜리 쿼리문을 한참 치고서 실행했더니 오타가 나서 다시 쳐야한다면???

OMG !!!!!!!!!!

이런 경우를 대비해서 오라클은 SQL 에디터를 제공해준다. 그런데 기본적으로 제공해주는게 뭔진 몰라도 불편하다.

좀더 편하게 하자면 우리가 유닉스 환경에서 자주 사용하는 vi 에디터를 SQL 기본 에디터로 설정해서 사용할 수 있는데

이 기본 에디터는 _editor 라는 변수안에 들어가있다.

SQL > define _editor = vi  

위 명령어를 실행하면 다음부터는 ed라는 명령어를 이용해서 방금 쳤던 명령어를 vi에디터 모드에서 다시 보여준다.

짱이다.

사용자계정이 SAMPLE 인 사용자의 권한을 조회하려면 아래와 같이 하시면 됩니다.

select privilege
from dba_sys_privs
where grantee='SAMPLE'
order by 1;

Oracle 10g Character set 변경(EUC-KR을 UTF8 변경) :
참고

kor 버전으로 받는다면 기본적으로 설치되는 버전은 KO16MSWIN949 일 경우가 많다.
한글을 지원하는 Character Set을 비교해보자.

1. KO16KSC5601
- 한글 지원상태 : 한글 2350자
- 지원버전 : 7.x
- 한글바이트 : 2바이트

2. KO16MSWIN949
- 한글 지원상태 : KO16KSC5601 + 확장 ( 총 11172자 )
- 지원버전 : 8.0.6 이상
- 한글바이트 : 2바이트

3. UTF8
- 한글 지원상태 : 한글 11172자
- 지원버전 : 8.0 이후
- 한글바이트 : 3바이트

4. AL32UTF8
- 한글 지원상태 : 한글 11172자
- 지원버전 : 9i Release 1 이상
- 한글바이트 : 3바이트

UTF8은 많은 문자를 지원하지만 한글을 3바이트 소모한다는 단점이 있다.
(못느낄 정도로 빠른 컴퓨터가 별 차이가 없을 듯함)
캐릭터셋이 어떻게 설정되어 있나 확인 쿼리는 다음과 같다ㅣ

SELECT * FROM sys.props$ where name='NLS_CHARACTERSET';

Oracle 10g Character Set 변경
SQLPLUS 접속후 (system 계정으로 로그인 혹시 모른다면 sqlplus /nolog; 후 conn /as sysdba; 로 접속한다)

C:\>sqlplus /nolog;
sql>conn /as sysdba;
변경하고자하는 캐릭터셋을 수정

sql>update sys.props$ set value$='UTF8' where name='NLS_CHARACTERSET';
sql>update sys.props$ set value$='UTF8' where name='NLS_NCHAR_CHARACTERSET';
sql>update sys.props$ set value$='KOREAN_KOREA.UTF8' where name='NLS_LANGUAGE';
sql>commit;
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 UTF8;
sql>shutdown immediate;
sql>startup;

출처 : http://www.garubi.com/11

테이블에 어떤 데이타를 insert, update, delete를 할 때는 한 명의 사용자만이 해당 테이블을 변경할 수 있도록 해준다.

그 런데 만약 A테이블에서 데이타를 읽어와서 이와 관련된 내용을 B테이블에 넣으려는데 B테이블에 FK로 A테이블의 PK가 걸려있다고 가정해보자. 나는 A테이블에서 pk가 1인 데이타를 읽어와서 B테이블에 업데이트를 해줘야하는데 데이타를 읽어와서 업데이트 하기 전에 누군가가 데이타 1을 지워버렸다면?? 내가 B테이블에 업데이트를 시도할 때 fk 오류가 날 것이다. pk가 사라졌으니 말이다.

자 이럴 때 사용할 수 있는 것이 select ... for update 이다.

for update는 읽어온 모든 rows와 그 rows와 관련된 인덱스 엔트리까지 lock을 한다. 이는 update문을 사용했을 때와 동일한 것이다. for update로 실행된 select문은 다른 모든 업데이트 관련 쿼리를 사용할 수 없다. 따라서 또 다른 select ... for update 문 역시 기존 세션에서 commit이나 rollback이 실행되기 전까지 기다렸다가 최신정보를 select하게 된다.

사용법은 단순하다.

SQL > select * from emp for update;

SQL FOREIGN KEY Constraint on ALTER TABLE

- 외래키 추가하기

MySQL / SQL Server / Oracle / MS Access:

- 외래키 이름 지정해주기

MySQL / SQL Server / Oracle / MS Access:

To DROP a FOREIGN KEY Constraint

- 외래키 삭제하기 

MySQL:

SQL Server / Oracle / MS Access:

프로젝트를 진행하다 보면 테이블 모델링을 수정해야 할 때가 있다. 이런 저런 추가요구가 들어오면 말이다.

그럴 때는 간단하게 SQL 한문장으로 후딱 해치워 버리면 된다.

§ 아래는 테이블에 하나의 컬럼을 추가로 만들어 주기위한 SQL문이다.

ALTER TABLE table_name ADD column_name datatype

§ 테이블에서 필요없는 컬럼을 삭제하려면 아래와 같은 명령어를 사용하면 된다.

SQL Server / MS Access:

My SQL / Oracle:

대량의 데이터를 처리해야하는 프로그램을 만들다보면 성능문제를 인식하지 않을 수 없다. JMeter를 이용하여 웹애플리케이션을 테스트 하면서 성능개선을 위해서 이것 저것 해보다가 오라클에서 제공하는 " Insert 문의 속도 "에 관한 문서를 발견하였다.  

아래 그 문서의 내용이 있으며 insert문의 속도와 관련된 사항들 및 어떻게 속도를 높일 수 있는지에 대하여 설명을 하고 있다. 

출처 : https://docs.oracle.com/cd/E17952_01/refman-5.1-en/insert-speed.html

Speed of INSERT Statements

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

This does not take into consideration the initial overhead to open tables, which is done once for each concurrently running query.

The size of the table slows down the insertion of indexes by log N, assuming B-tree indexes.

You can use the following methods to speed up inserts: