반응형 Oracle(DB관련)77 Oracle Bulk SQL and Bulk Binding (bulk collect) Oracle Bulk SQL and Bulk Binding 대량의 데이터 처리시 loop문에서 1회씩 매번 수행되는 것을 단 1번만으로 처리 create or replace procedure sp_test as type ty_emp is table of emp%rowtype; ty_data ty_emp; type ty_name is varray(10) of varchar2(100); ty_ename ty_name := ty_name(); cursor cur_emp is select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp order by empno; begin ty_ename.extend(10); open cur_emp; loop -- .. 2018. 6. 12. Oracle table function Oracle table function 1. create object type create or replace type ty_obj as object ( col01 varchar2(100), col02 varchar2(100), col03 varchar2(100), col04 varchar2(100), col05 varchar2(100), col06 varchar2(100), col07 varchar2(100), col08 varchar2(100), col09 varchar2(100), col10 varchar2(100) ) 2. create table typecreate or replace type ty_tbl as table of ty_obj 3. create functioncreate or repl.. 2018. 6. 12. Identity Columns in Oracle Database 12c Identity Columns in Oracle Database 12c Release 1 (12.1) In previous releases of the Oracle database, there was no direct equivalent of the AutoNumber or Identity functionality of other database engines. Instead, this behaviour had to be implemented using a combination of sequences and triggers. Oracle 12c introduces two alternatives to this by providing identity columns and the ability to use s.. 2018. 5. 8. Invisible Columns in Oracle Database 12c Invisible Columns in Oracle Database 12c Release 1 (12.1) Invisible columns can be useful during application migrations. Making new columns invisible means they won't be seen by any existing applications, but can still be referenced by any new applications, making the online migration of applications much simpler. Invisible ColumnsInvisible Columns and Column OrderingMandatory Invisible Columns .. 2018. 5. 8. Oracle 10g Transaction Rollback Monitoring Oracle 10g Transaction Rollback Monitoring Oracle 10g 이전에 parallel transaction recovery 시 다음 두 개의 뷰로 monitor할 수 있었다. - v$fast_start_servers - v$fast_start_transactions. 그러나, 이 뷰들은 normal transaction rollback 이나 SMON에 의해 복구되어지는 transaction 들은 monitor 할 수 없었다. 10g 이후, transaction rollback monitoring을 통하여 이제는 realtime normal transaction rollback 과 SMON에 의한 transaction recovery 를 모니터 할 수 있다. 또한, tra.. 2018. 2. 5. oracle rollback (롤백 모니터링) oracle rollback (롤백 모니터링) 1.주요 테이블에 세션 KILL 현상 모니터링 delete from mt_pnt; 클라이언트 프로그램 닫음. 서버 세션 종료 안됨. 2.모니터링 세션 O_2 8217,42810 934568,1426068 SVCES:TMA ???? omstgdb oracle@omstgdb db file sequential read:[T]MT_PNT: 1 cqvdtymbzuc56 DELETE 21.3 67.0 0 alter system kill session '8217,42810 ' immediate; 3.세션 Kill 이후 -- front session 지연 O_1 6320,8827 3035690,1234 TMALL:FRO weblogic frontwas3 JDBC Thin .. 2018. 2. 5. Virtual Columns in Oracle Database 11g Release 1 Virtual Columns in Oracle Database 11g Release 1 /************ test ***************/create table tb_test(sup number,vat number,amt as (sup + vat)) -- Add/modify columns alter table tb_test add ch1 varchar2(1);alter table tb_test add ch2 varchar2(1);alter table tb_test add ch12 varchar2(2) as (ch1||ch2) CREATE TABLE employees ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), salary NU.. 2017. 12. 13. Data Magration to Oracle Data Magration to Oracle mssql, mysql, db2, taradata, sybase, infomix http://www.oracle.com/technetwork/database/migration/quickmigrate-085959.html https://www.oracle.com/kr/database/technologies/appdev/migration.html https://www.youtube.com/watch?v=4uC7XGPyelY 2017. 11. 9. Full Text Indexing using Oracle Text Full Text Indexing using Oracle TextOracle Text, previously know as interMedia Text and ConText, is an extensive full text indexing technology allowing you to efficiently query free text and produce document classification applications. In this article I'll only scratch the surface of this very complex feature.CONTEXT IndexesCTXCAT IndexesCTXRULE IndexesIndex MaintenanceThe examples in this arti.. 2017. 9. 15. oracle index 인덱스 테크닉 - 인덱스 테크닉의 종류 1. B*Tree 2. Reverse Key Index 3. Descending Index 4. IOT 5. B*Tree Cluster Index 6. BITMAP Index 7. Function Based Index 8. Domain Index FBI 사용하기 - FBI에서 가능한 기능 1. 대소문자와 무관한 검색 또는 정렬 2. 복잡한 방정식에 대한 검색 3. 독자적인 함수와 연산자를 구현하고 이들에 대해 검색함으로써 SQL 언어 확장 - B*Btree 인덱스는 NULL 엔트리를 포함하지 않음으로 아래에 소개하는 유용한 기능을 사용할 수 있음 1. 선택적으로 인덱스 생성하기 2. 선택적인 유일성 구현하기 선택적으로 인덱스 생성하기 - 테이블의 일부 행에만 인덱스.. 2017. 9. 15. 이전 1 2 3 4 5 6 7 8 다음 반응형
