'Oracle/Oracle Tip' 카테고리의 글 목록

(1) 테스트 테이블 생성

create table split_table as
select 'a/b/c/d/e' name
from dual;

(2) REGEXP_SUBSTR

select regexp_substr(name,'[^/]+',1,1) regexp_substr_name1,
regexp_substr(name,'[^/]+',1,2) regexp_substr_name2,
regexp_substr(name,'[^/]+',1,3) regexp_substr_name3,
regexp_substr(name,'[^/]+',1,4) regexp_substr_name4,
regexp_substr(name,'[^/]+',1,5) regexp_substr_name5
from split_table;

결과

a

b

c

d

e

(3) REGEXP_REPLACE

select regexp_replace(name,'[^/]+') regexp_replace_name1,
regexp_replace(name,'[^/]+','*') regexp_replace_name2
from split_table;

결과

////

*/*/*/*/*

(4) 세로로 나열

select regexp_substr(name,'[^/]+',1,rownum) splite_name
from split_table
connect by rownum <= length(regexp_replace(name, '[^/]+')) +1;

결과

a

b

c

d

e

참고 : https://good4uprin.tistory.com/11

'Oracle > Oracle Tip' 카테고리의 다른 글

sqlplus 에서 ed(edit) 시 vi편집기 사용. (0)	2011.11.08
Range 파티션 테이블 및 테이블 스페이스 삭제 script (0)	2011.04.14
long형 replace (0)	2011.01.24
LONG과 CLOB 에 대한 데이터 이전. (0)	2011.01.04
tablespace 생성 스크립트 (0)	2010.04.30

리눅스(우분투)에서 sqlplus를 접속 후 작업시 ed 또는 edit 를 쳐서 쿼리를 수정하는 경우가 있다.
그러나 vi가 실행은 된 것 같은데 기존 쿼리는 온데 간데없고 이상한 문구나 '?' 만 표시가 되어있다.
q를 눌러 나가게 되면 기존쿼리가 출력이 되면서 나간다.

해결 방법은..

$ORALCE_HOME/sqlplus/admin/glogin.sql 파일을 열어서 맨 아래줄에 define_editor=vi 를 입력한다.

다시 해보면 정상적으로 편집기가 실행된다.

저작자표시 비영리 변경금지

'Oracle > Oracle Tip' 카테고리의 다른 글

split - REGEX_SUBSTR, REGEX_REPLACE (0)	2022.04.22
Range 파티션 테이블 및 테이블 스페이스 삭제 script (0)	2011.04.14
long형 replace (0)	2011.01.24
LONG과 CLOB 에 대한 데이터 이전. (0)	2011.01.04
tablespace 생성 스크립트 (0)	2010.04.30

대용량 파티션 테이블에 일정기간 데이터만을 보유할 경우

부분적으로 파티션 테이블 및 테이블 스페이스를 삭제를 해야하는 경우가 있다.

아래 쿼리들은 Range 파티션 테이블 일때 해당 되는 쿼리이다.

partition_name을 날짜로 지정해 주었을 때만 해당기간에 대해서 날짜를 가져와서 출력할 수 있다.

쿼리 실행시 주의할 점은 전부 쿼리를 실행시켜서 script가 출력되게 하여야 한다는 것이다.

무슨말이냐면 아래 쿼리들은 전부 dba_tab_partitions에서 가져온다.

그러므로 파티션 테이블 삭제 스크립트만 돌리고 다음 쿼리(테이블스페이스 삭제 쿼리)를 실행시키고자 하면 데이터가 안나온다.

1. 파티션 테이블 부분 삭제 script 생성

select 'alter table '||table_owner||'.'||table_name||' drop partition '||partition_name||';' script

from dba_tab_partitions

where table_owner = 'SCOTT'

and table_name in ('테이블명')

and partition_name>= '20110201' and partition_name<= '20110228'

order by table_name, partition_name

;

subpartition 일때 parttiton 쿼리로 돌리면 엄한 테이블스페이스 및 데이터파일을 삭제할 수가 있으니 유념하자.

2. TABLESPACE 및 DATAFILE 삭제 script 생성

select 'drop tablespace '||tablespace_name||' including contents and datafiles cascade constraints;' script

from dba_tab_partitions

where table_owner = 'SCOTT'

and table_name in ('테이블명')

and partition_name>= '20110201' and partition_name<= '20110228'

order by tablespace_name

;

3. subpartition table 일때 TABLESPACE 및 DATAFILE 삭제 script 생성

select 'drop tablespace '||tablespace_name||' including contents and datafiles cascade constraints;' script

from dba_tab_subpartitions

where table_owner = 'SCOTT'

and table_name in ('테이블명')

and partition_name>= '20110201' and partition_name<= '20110228'

order by tablespace_name

;

4. datafile 삭제 여부 확인(오라클 버그로 간혹 데이터파일이 삭제가 되지 않을 때가 있다. OS 단에서 확인을 해보자)

select c.table_name, b.name tablespace_name, a.name datafile, 'ls '||a.name script

from v$datafile a, v$tablespace b,

(

select partition_name, table_name, tablespace_name

from dba_tab_partitions

where table_name in ('테이블명')

) c

where a.ts#=b.ts#

and b.name=c.tablespace_name

and partition_name >= '20110201' and partition_name <= '20110228'

group by c.table_name, b.name, a.name

;

'Oracle > Oracle Tip' 카테고리의 다른 글

split - REGEX_SUBSTR, REGEX_REPLACE (0)	2022.04.22
sqlplus 에서 ed(edit) 시 vi편집기 사용. (0)	2011.11.08
long형 replace (0)	2011.01.24
LONG과 CLOB 에 대한 데이터 이전. (0)	2011.01.04
tablespace 생성 스크립트 (0)	2010.04.30

-- 테이블 포맷

SQL> desc long_replace

Name Null? Type

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

LONGTYPE LONG

ID VARCHAR2(20)

CLOBTYPE CLOB

long형 타입의 컬럼은 replace가 되질 않는다. 아래와 같은 메세지를 뿌리게 된다.

SQL> update long_replace set longtype = replace(longtype,'기능','기능불가');

update long_replace set longtype = replace(longtype,'기능','기능불가')

*

ERROR at line 1:

ORA-00932: 일관성 없는 데이터 유형: NUMBER이(가) 필요하지만 LONG임

방법 :

1. long형 포맷이 아닌 clob형 포맷으로 temp성 테이블을 하나 더 만든다.

2. clob형은 replace가 가능하기때문에 temp성 테이블을 업데이트 시킨다.

3. 실질적인 long형 포맷이 있는 테이블에 temp성 테이블과 비교하여 업데이트를 시킨다.

4. 완료.

SQL> create table temp_replace as select to_lob(longtype) longtype, id, clobtype from long_replace;

Table created.

SQL> update temp_replace set longtype=replace(longtype,'기능','기능불가');

1 row updated.

SQL> update long_replace a

2 set longtype=(select longtype from temp_replace b where a.id=b.id);

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from long_replace; --> 업데이트가 되었는지 확인.

'Oracle > Oracle Tip' 카테고리의 다른 글

sqlplus 에서 ed(edit) 시 vi편집기 사용. (0)	2011.11.08
Range 파티션 테이블 및 테이블 스페이스 삭제 script (0)	2011.04.14
LONG과 CLOB 에 대한 데이터 이전. (0)	2011.01.04
tablespace 생성 스크립트 (0)	2010.04.30
테이블 read only 설정 (0)	2010.01.27

CLOB 같은 경우는 일반적으로 CTAS 가 가능하다.

그러나 LONG형은 불가능하다. 예전에는 export/import를 통하여 데이터를 이전시키는 경우가 있었다.

아래와 같은 명령어로 인하여 COPY가 가능하게 되었다.

copy from scott/tiger@prod create test_long2 using select * from test_long;

scott/tiger@prod LONG형 테이블을 가져올 데이터 베이스의 계정을 말한다.

그러나 이 명령어는 CLOB 포맷의 컬럼은 COPY가 불가능하다.

다음과 같은 메세지가 보여지게 된다.

SQL> scott/tiger@prod

Connected.

SQL> create table test_long

2 (a varchar2(20),

3 b number,

4 c number(10,2),

5 d clob,

6 e long)

7 /

Table created.

SQL> insert into test_long values('aaa',1,2.5,'asdfasdfsadf','asdfasdfasdfasdf');

1 row created.

SQL> commit;

Commit complete.

SQL> conn hr/hr@prod

Connected.

SQL> copy from scott/tiger@prod create test_long2 using select * from test_long;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

CPY-0012: Datatype cannot be copied

SQL> copy from scott/tiger@prod create test_long2 using select a,b,c,e from test_long; --CLOB형 컬럼을 뺐음.

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

Table TEST_LONG2 created.

1 rows selected from scott@prod.

1 rows inserted into TEST_LONG2.

1 rows committed into TEST_LONG2 at DEFAULT HOST connection.

SQL>

※ 주의해야 할 점.

set long [최대길이수지정]

해주지않으면 지정된 수만큼만 출력되어져 데이터가 짤려서 들어가게 된다.

'Oracle > Oracle Tip' 카테고리의 다른 글

Range 파티션 테이블 및 테이블 스페이스 삭제 script (0)	2011.04.14
long형 replace (0)	2011.01.24
tablespace 생성 스크립트 (0)	2010.04.30
테이블 read only 설정 (0)	2010.01.27
1년치 날짜 생성. (0)	2010.01.14

테이블 스페이스 생성 스크립트 입니다.

디비이관 작업할 때 유용하게 쓰일 것 같네요.

select tablespace_name, dbms_metadata.get_ddl('TABLESPACE',tablespace_name)||';' script

from dba_tablespaces

where tablespace_name not in ('TEMP','USERS','UNDOTBS1','SYSTEM','SYSAUX');

아래와 같이 스크립트가 생성된다.

CREATE TABLESPACE "aaa" DATAFILE

'/oracle/oradata/Disk4/aaa.dbf' SIZE 104857600

AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M

NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

;

주의 할 점.

위 스크립트를 그대로 실행시켰을경우. tablespcae 이름이 쌍따옴표를 포함한 "AAA" 으로 등록이 되어지는데

dba_tablespaces를 보면 AAA로 보여진다.

추후 drop 을 할려고 무심코 아래와 같은 쿼리문을 날렸는데 에러를 발생시키면 원인 찾기가 참 힘들어 지겠군요.

drop tablespace aaa including contents and datafiles;

ORA-00959: 테이블스페이스 'AAA'이(가) 존재하지 않습니다.

select tablespace_name from dba_tablespaces where tablespace_name = 'aaa';

TABLESPACE_NAME

aaa

변경및 수정할때에도 아래와 같이 지워야 하네요.

drop tablespace "aaa" including contents and datafiles;

--나의 의견.

쌍따옴표를 제거한 후 실행시키는 것이 관리하기에 편하다고 생각합니다.

유용한 스크립트이지만 하나하나 뜯어보고 불필요한 부분에 대해서는 편집이 필요하다고 생각되어집니다.

저는 위 쿼리보다는 아래 쿼리를 사용하고 있습니다.

위 쿼리는 속도도 많이 느리고, 쌍따옴표의 포함 등등 저에게는 좀 안맞는 쿼리인듯 싶네요.

아래 쿼리가 무식하고 더 불편하긴 하지만 저는 나름 편집해서 사용하고 있습니다.(특성에 따라 쿼리를 수정해줘야해서..)

select

'CREATE TABLESPACE '||tablespace_name||

'DATAFILE

'''||data1||''' SIZE 33554432 REUSE AUTOEXTEND ON NEXT 16777216 MAXSIZE 16384M,

'''||data2||''' SIZE 33554432 REUSE AUTOEXTEND ON NEXT 16777216 MAXSIZE 16384M,

'''||data3||''' SIZE 33554432 REUSE AUTOEXTEND ON NEXT 16777216 MAXSIZE 16384M,

'''||data4||''' SIZE 33554432 REUSE AUTOEXTEND ON NEXT 16777216 MAXSIZE 16384M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16777216 SEGMENT SPACE MANAGEMENT AUTO;' script

from

(

select tablespace_name,

max(case when rank=1 then file_name end) data1,

max(case when rank=2 then file_name end) data2,

max(case when rank=3 then file_name end) data3,

max(case when rank=4 then file_name end) data4

from

(

select file_name, tablespace_name, row_number() over(partition by tablespace_name order by file_name) rank

from dba_data_files b

where tablespace_name not in ('SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS')

)

group by tablespace_name

)

;

'Oracle > Oracle Tip' 카테고리의 다른 글

long형 replace (0)	2011.01.24
LONG과 CLOB 에 대한 데이터 이전. (0)	2011.01.04
테이블 read only 설정 (0)	2010.01.27
1년치 날짜 생성. (0)	2010.01.14
유용 update문 (0)	2009.12.09

[출처 : PRODBA 의 가을바람님의 글을 보고 테스트를 해보았습니다.]

1. 트리거의 이용

SQL> create or replace trigger emp_sal_read_only --트리거 이름

2 before insert or update or delete

3 on emp --원하는 테이블명

4 begin

5 raise_application_error

6 (-20001,'Table EMP is read only, You cannot make changes to the data.');

7 end;

8 /

Trigger created.

SQL> delete from emp;

delete from emp

*

ERROR at line 1:

ORA-20001: Table EMP is read only, You cannot make changes to the data.

ORA-06512: "SCOTT.EMP_SAL_READ_ONLY", 줄 2에서

ORA-04088: 트리거 'SCOTT.EMP_SAL_READ_ONLY'의 수행시 오류

SQL> drop trigger emp_sal_read_only;

Trigger dropped.

SQL> delete from emp;

14 rows deleted.

SQL> rollback;

Rollback complete.

SQL>

2. read only tablespace 를 이용

SQL> select * from tab

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

AA TABLE

SALES TABLE

DEPT_TEST TABLE

EMP_TEST TABLE

NUM_TEST TABLE

9 rows selected.

SQL> create tablespace temp_readonly

2 datafile '/oracle/oradata/PROD/Disk5/temp_readonly.dbf' size 5M reuse

3 autoextend on;

Tablespace created.

SQL> alter table emp_test move tablespace temp_readonly;

Table altered.

SQL> alter tablespace temp_readonly read only;

Tablespace altered.

SQL> delete from emp_test;

delete from emp_test

*

ERROR at line 1:

ORA-00372: 파일 7는 지금 수정될 수 없습니다

ORA-01110: 7 데이터 파일: '/oracle/oradata/PROD/Disk5/temp_readonly.dbf'

SQL> drop tablespace temp_readonly including contents and datafiles cascade constraints;

Tablespace dropped.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

AA TABLE

SALES TABLE

DEPT_TEST TABLE

NUM_TEST TABLE

8 rows selected. --> emp_test가 사라졌네요..잉ㅜㅜ 조심하세요!!

나의 의견 :

1. 트리거를 이용한 방법은 새롭게 알게된 것이며, 테스트 해 본 결과 유용하게 쓰일 것 같습니다.

2. 테이블을 옮기고 테이블스페이스를 다시 수정하고 번거로운 작업있긴하지만,

이것 또한 테이블을 read only하는데 유용한 정보입니다.

위 내용과 무관하지만 테이블스페이스를 삭제하면 그 안에 데이터도 사라진다는 것 또한 잊지마세요!!

'Oracle > Oracle Tip' 카테고리의 다른 글

long형 replace (0)	2011.01.24
LONG과 CLOB 에 대한 데이터 이전. (0)	2011.01.04
tablespace 생성 스크립트 (0)	2010.04.30
1년치 날짜 생성. (0)	2010.01.14
유용 update문 (0)	2009.12.09

[출처] : http://cafe.naver.com/prodba/16202

PRODBA 카페에서 지구인 님께서 올려주신 것.

SELECT TARGET_DATE T_DATE, TO_CHAR(TARGET_DATE, 'DAY') T_DAY

FROM

(

SELECT ADD_MONTHS(TO_DATE(LEVEL, 'DDD'), 0) TARGET_DATE

FROM DUAL

CONNECT BY LEVEL<=365

)

;

0 을 조절해주면서 년을 수정해 줄수 있습니다.

Ex. 12 이면 2011년, 24이면 2012년...

'Oracle > Oracle Tip' 카테고리의 다른 글

long형 replace (0)	2011.01.24
LONG과 CLOB 에 대한 데이터 이전. (0)	2011.01.04
tablespace 생성 스크립트 (0)	2010.04.30
테이블 read only 설정 (0)	2010.01.27
유용 update문 (0)	2009.12.09

테스트 테이블 확인.

SQL> select deptno, max(sal) from emp group by deptno;

   DEPTNO MAX(SAL)
---------- ----------
   30 2850
   20 3000
   10 5000

SQL> select * from dept_test;

    DEPTNO DNAME LOC SAL
---------- ------------------- ----------- ----------
   10 ACCOUNTING NEW YORK
   20 RESEARCH DALLAS
   30 SALES CHICAGO 2850
   40 OPERATIONS BOSTON
   -1 DBA Seoul
   40 SALES CHICAGO
   50 SALES CHICAGO 1111

7 rows selected.

무심코 지나쳐 버릴 것 같은 update문을 재연해 보았습니다.
아래와 같은 쿼리문을 가끔 날려서 실수를 범하는 경우가 있어서 간단하게 정리를 해보았습니다.

SQL> update dept_test a
   2   set sal = (select max(sal) from emp b where a.deptno=b.deptno group by deptno);

7 rows updated.

SQL> select * from dept_test;

   DEPTNO DNAME LOC SAL
---------- ------------------- ----------- ----------
   10 ACCOUNTING NEW YORK 5000
   20 RESEARCH DALLAS 3000
   30 SALES CHICAGO 2850
   40 OPERATIONS BOSTON
   -1 DBA Seoul
   40 SALES CHICAGO
   50 SALES CHICAGO

7 rows selected.   --> 위 update 조건에 맞지 않는 것은 null값으로 처리가 되네요.

SQL> rollback;

Rollback complete.

<해결방안>

아래 사항들은 가끔 제가 사용하는 것입니다. 방법은 여러가지가 있겠죠^^;
(오래전에 구글링으로 찾아서 update시 유용하게 사용하고 있어서 정리를 합니다.)

SQL> update dept_test a
   2 set sal = (select max(sal) from emp b where a.deptno=b.deptno group by deptno)
   3 where deptno in (select deptno from emp group by deptno);

3 rows updated.

SQL> select * from dept_test;

  DEPTNO DNAME LOC SAL
---------- ------------------- ----------- ----------
   10 ACCOUNTING NEW YORK 5000
   20 RESEARCH DALLAS 3000
   30 SALES CHICAGO 2850
   40 OPERATIONS BOSTON
   -1 DBA Seoul
   40 SALES CHICAGO
   50 SALES CHICAGO 1111

7 rows selected.
      --> 테이블을 2번 읽어야 하고 데이터가 많을 경우 시간이 많이 걸리는 단점이 있어요.
   예전에 한도끝도 없이 기다렸다가 결국에는 취소를 시켰다는..ㅠㅠ

SQL> rollback;

Rollback complete.

SQL> update dept_test a
   2 set sal = nvl((select max(sal)
   3 from emp b where a.deptno=b.deptno group by deptno), a.sal);

7 rows updated.   --> 업데이트 할 값이 null 이라면 원래 값을 넣어줌.

SQL> select * from dept_test;

    DEPTNO DNAME LOC SAL
---------- ------------------- ----------- ----------
   10 ACCOUNTING NEW YORK 5000
   20 RESEARCH DALLAS 3000
   30 SALES CHICAGO 2850
   40 OPERATIONS BOSTON
   -1 DBA Seoul
   40 SALES CHICAGO
   50 SALES CHICAGO 1111

7 rows selected. --> 불필요한 로우까지 업데이트를 하는 단점이 있습니다.

SQL> rollback;

Rollback complete.

SQL> update (select a.sal a_sal, b.sal b_sal
  2 from dept_test a, (select deptno, max(sal) sal from emp group by deptno) b
  3 where a.deptno=b.deptno)
  4 set a_sal=b_sal;
set a_sal=b_sal
   *
ERROR at line 4:
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다 --> 음..에러가 나네요

SQL> update /+bypass_ujvc/(select a.sal a_sal, b.sal b_sal   --> 힌트절 사용
  2 from dept_test a, (select deptno, max(sal) sal from emp group by deptno) b
  3 where a.deptno=b.deptno)
  4* set a_sal=b_sal
SQL> /

3 rows updated.

SQL> select * from dept_test;

    DEPTNO DNAME LOC SAL
---------- ------------------- ----------- ----------
   10 ACCOUNTING NEW YORK 5000
   20 RESEARCH DALLAS 3000
   30 SALES CHICAGO 2850
   40 OPERATIONS BOSTON
   -1 DBA Seoul
   40 SALES CHICAGO
   50 SALES CHICAGO 1111

7 rows selected. --> 위 3개 쿼리중 실용성있는 쿼리문이라고 합니다.(그냥 그렇다네요ㅋㅋ)

SQL>

<결론>
가끔 나의 짧은 지식으로 검증도 해보지 않고, 무턱대고 commit을 실행 시키는 실수로 인해서 감당할 수 없는 큰 일이 발생할 수가 있습니다. 다른 것도 꼼꼼히 살펴봐야하겠지만 특히나 update 문은 신중을 기해야 할 것입니다. 검증은 중요한 것이니깐요^^
위 쿼리문 중 마지막 쿼리문이 가장 낫다고는 하였지만 어느 것 하나 정답은 없다고 생각합니다.

'Oracle > Oracle Tip' 카테고리의 다른 글

long형 replace (0)	2011.01.24
LONG과 CLOB 에 대한 데이터 이전. (0)	2011.01.04
tablespace 생성 스크립트 (0)	2010.04.30
테이블 read only 설정 (0)	2010.01.27
1년치 날짜 생성. (0)	2010.01.14

개발은 너무해