GaramX oracle

oracle:comma

Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00

Comma select regexp_substr('pingpong,arrow,function', '[^,]+', 1, level) from dual connect by regexp_substr('pingpong,arrow,function', '[^,]+', 1, level) is not null;


select *
from emp
where ename in (
select regexp_substr('pingpong,arrow,function', '[^,]+', 1, level)



oracle:createtable
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Create Table


CREATE TABLE "ASLIB"."SALED_HIST" 
   (
 	"ID" NUMBER(18,0) GENERATED BY DEFAULT AS IDENTITY, 
 	DEL_USER_ID varchar2(30 byte),
 	DEL_DATE		varchar2(8 byte),
 	DID	NUMBER(18),
	"CSTMR_ID" VARCHAR2(5 BYTE), 
	"ITEM_ID" VARCHAR2(9 BYTE), 
	"COLOR_ID" VARCHAR2(4 BYTE), 
	"MGN_RATE" NUMBER(5,2), 
	"MGR_RATE" NUMBER(5,2), 
	"HAL_RATE" NUMBER(5,2), 
	"PAN_PRICE" NUMBER(7,0), 
	"SALEH_ID" NUMBER(18,0), 
	"ADD_DATE" VARCHAR2(8 BYTE), 
	"USER_ID" VARCHAR2(30 BYTE), 
	 CONSTRAINT "SALED_HIS…


oracle:dba
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
DBA

	*  H5 DB Import
	*  Oracle User관리
	*  Oracle DB Link
	*  DB Script생성

Data Pump Directory확인


SELECT DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';


1.DB 사용자 알아보기.


SELECT username, account_status FROM dba_users WHERE username='SCOTT';



-- 사용자 Role
SELECT * FROM user_role_privs;


oracle:dblink
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Oracle DB Link관리

	*  DBA

DBLINK 생성

DBLINK 생성 구문


SQL> CREATE [PUBLIC] DATABASE LINK link_name
         [CONNECT TO CURRENT_USER]
         [USING 'connect_string']
         
SQL> CREATE DATABASE LINK 링크이름
         CONNECT TO <연결하고자 하는 user>
         IDENTIFIED BY <연결하고자 하는 user password>
         USING '원격 db alias'
         
CREATE DATABASE LINK HWS
         CONNECT TO schema_name IDENTIFIED BY "schema_password"
         USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server_address)(PORT =…


oracle:find_source
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
소스검색


 
--프로시저 TEXT검색
SELECT *
FROM   USER_SOURCE
WHERE  1=1
AND TYPE = 'PROCEDURE' --패키지나 다른 부분을 검색 하고 싶다면 대문자로 입력 
AND    TEXT LIKE  '%REG_DT%' --여기에 찾고 싶은 텍스트 입력
ORDER BY NAME, LINE

--FUNCTION TEXT검색
SELECT *
FROM   USER_SOURCE
WHERE  1=1
AND TYPE = 'FUNCTION'
AND    TEXT LIKE  '%REG%'  
ORDER BY NAME, LINE


oracle:getddl
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
테이블정보 얻기

오라클 프로시져 리스트 조회

SELECT * 
   FROM USER_OBJECTS 
 WHERE OBJECT_TYPE = 'PROCEDURE'
      AND OBJECT_NAME LIKE '%PNA%'

 

오라클 컬럼 리스트 조회

SELECT * 
  FROM USER_TAB_COLUMNS


oracle:hashjoin
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Hash Join

해시 조인(Hash-Join)은 두 테이블 중 하나를 기준으로 비트맵 해시 테이블을 메모리에 올린 후 나머지 테이블을 스캔 하면서 해싱 테이블을 적용하여 메모리에 로딩된 테이블과 비교하여 매칭되는 데이터를 추출하는 방식의 조인이다.
RDBMS에 서 비용이 가장 많이 들어가는 Join 방법으로 주로 작은 Table과 큰 Table 의 Join 시 사용되어 지며 , Driving 조건과 상관없이 좋은 성능을 발휘할 수 있다.…


oracle:hint
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
ORACLE Hint 정리

A. Initialization Parameter중 OPTIMIZER_MODE 지정가능 값

1.ALL_ROWS

Goal : Best Throughput
용도 : 전체 RESOURCE 소비를 최소화 시키기 위한 힌트. Cost-Based 접근방식.


SELECT /*+ALL_ROWS */
EMPNO,ENAME
FROM EMP
WHERE EMPNO = 7655;


oracle:home
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Oracle Database



	*  DBA
	*  H5 DB Import
	*  Oracle User관리
	*  Oracle설치

Oracle관련자료들

	*  Maven Oracle JDBC
	*  Schedule
	*  Hint
	*  Merge Statement
	*  Trigger
	*  Hash Join
	*  Create Table
	*  RDS DB Links
	*  Table Space
	*  Trunc
	*  user_source 사용
	*  Split To Table
	*  Rollup
	*  WM_CONCAT is gone in 12c
	*  SQLLDR loader
	*  SHA256 Package
	*  TNS Names
	*  Functions
	*  테이블정보 얻기
	*  Use Comma
	*  Timestampe를 이용한복구
	*  XML SQL
	*  Update Join
	*  프로시져 소스검색
…


oracle:install
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
오라클설치

debian에서 설치


oracle:merge
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Merge Statement


MERGE INTO SALED T
          USING (
            SELECT c.cstmr_id, item_id, color_id, 24 mgn_rate, 0 mgr_rate,
                   hal_rate, a.CUR_PAN_PRICE pan_price, b.saleh_id
                 , to_char(sysdate, 'yyyymmdd') add_date, rUserCode user_id
              FROM TABLE(CAST(m_SALE_DTL_TBL AS SALE_DTL_TBL)) a,
                  (select id saleh_id
                    from saleh
                   where (pSaleSdate between sale_sdate and sale_edate
                     …


oracle:mvn_oracle
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Maven Oracle JDBC

oracle jdbc는 수동으로 repository에 등록해주어야 함.


mvn install:install-file -Dfile=C:\Users/taekg/ojdbc8.jar -DgroupId=com.oracle -DartifactId=ojdbc8 -Dversion=12.2.0 -Dpackaging=jar



		
			com.oracle
			ojdbc8
			12.2.0
		


oracle:rdsdblink
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
DB LINK 생성


create public database link DBLINK_PRD
connect to ADMIN
identified by "******"
using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ais-rds-prd.ciram51bv0wk.ap-northeast-2.rds.amazonaws.com)(PORT=51521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ASICSDB)))';


oracle:rollup
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Rollup


oracle:schedule
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Schedule
SELECT * FROM USER_SCHEDULER_JOBS; --등록된 job
SELECT * FROM USER_SCHEDULER_JOB_ARGS; --job의 arguments
SELECT * FROM USER_SCHEDULER_RUNNING_JOBS; --현재 running중인 job들의정보
SELECT * FROM USER_SCHEDULER_JOB_LOG; --job의 log
SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS; --job의수행된정보및Error 정보
SELECT * FROM USER_SCHEDULER_PROGRAMS; -- 등록된 Program
SELECT * FROM USER_SCHEDULER_PROGRAM_ARGS; -- 프로그램의 매게변수
SELECT * FROM USER_SCHEDULER_SCHEDULES; --등록된 스케쥴러…


oracle:sha256
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
ORACLE SHA256

SHA256 PACKAGE

GitHub : CruiserX/sha256_plsql

	*  간단한 사용법


select b.password as pwd, a.emp_no, f_shaencryptor(substr(CTZ_NO,7)) as f_java,
  sha256.encrypt(substr(ctz_no,7)) as f_pkg
  from vi_frm_phm_emp a
  join frm_user b
  on a.emp_no = b.login_id


oracle:split_to_table
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
String to Table

String으로부터 table얻기위한 방법

Table 얻기


SELECT RT_SEQ, RT_VALUE
  FROM TABLE(FT_SPLIT_ARRAY(av_str_mail ,'|'));


FT_SPLIT_ARRAY


CREATE OR REPLACE FUNCTION FT_SPLIT_ARRAY (
      av_str   IN VARCHAR2
    , av_split IN VARCHAR2
) RETURN PIPE_FT_SPLIT_ARRAY_TAB PIPELINED IS
 /*********************************************************************************
 PROCEDURE NAME : FT_SPLIT_ARRAY
 DESCRIPTION    : av_str 로 넘어온 문자열을 av_split으로 자른다음 문자열을 붙여서 반환한다.
                  an_num에 값이…


oracle:sqlldr
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
SQLLDR


LOAD DATA 
INFILE 'lob_test_data.txt'
   INTO TABLE lob_tab
   FIELDS TERMINATED BY ','
   (number_content    CHAR(10),
    varchar2_content  CHAR(100),
    date_content      DATE "DD-MON-YYYY" ":date_content",
    clob_filename     FILLER CHAR(100),
    clob_content      LOBFILE(clob_filename) TERMINATED BY EOF,
    blob_filename     FILLER CHAR(100),
    blob_content      LOBFILE(blob_filename) TERMINATED BY EOF)


oracle:tablespace
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Tablespace

할당할 수 있는 최대 EXTENT의 크기가 얼마인지 확인한다.


SELECT MAX(blocks), MAX(bytes)
  FROM DBA_FREE_SPACE
 WHERE TABLESPACE_NAME = 'TEMP';


Tablespace확인


SELECT * FROM DBA_TABLESPACES;


Tablespace생성


CREATE TABLESPACE H5DATA
    DATAFILE '/u01/app/oracle/oradata/XE/h5data.dbf' SIZE 1024M
    AUTOEXTEND ON NEXT 100M;


oracle:timestamp_recovery
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Timestamp를 이용한 복구


select *
from fdb_base_env
     as of timestamp (systimestamp - interval '10' minute);


oracle:tnsnames
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
tnsnames.ora



PEPPER_DEV =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.11)(PORT=1521))
    (CONNECT_DATA=
      (SERVER=DEDICATED)
      (SERVICE_NAME=ORCL)
    )
  )


oracle:trigger
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Trigger

Trigger 확인


--2 All Disabled Triggers:
select *
  from dba_triggers
 where status!='ENABLED';


Compund Trigger


CREATE OR REPLACE TRIGGER 트리거이름
  FOR 트리거링 이벤트 ON 테이블명
    COMPOUND TRIGGER
  -- 전역 변수 선언
  g_global_variable VARCHAR2(10);
  TYPE TYPE_SALED_TAB IS TABLE OF SALED%ROWTYPE;
  G_SALED_TAB TYPE_SALED_TAB := TYPE_SALED_TAB();
  type ty_bookings_hist is table of bookings_hist%rowtype
    index by pls_integer;
  coll_bookings_hist ty_bookings_hist;
  ctr pls_integer := 0;
  BEFO…


oracle:trunc
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
TRUNC


select sysdate
,trunc(sysdate)
,trunc(sysdate,'YEAR')
,trunc(sysdate,'MONTH') , trunc(sysdate, 'MM')
,trunc(sysdate,'DAY')
,trunc(123.4567,2)
,trunc(123.4567)
from dual


Result
18/03/09
18/03/09
18/01/01
18/03/01
18/03/04
123.45
123


oracle:update_join
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
Update Join

Oracle


-- 1. SubQuery 를 이용한 Update
UPDATE emp e
   SET e.dname = (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno)
 WHERE EXISTS (SELECT 0 FROM dept d WHERE d.deptno = e.deptno)
;
-- 2. Updatable Join View 이용
--    단, d.deptno 가 반드시 PK 이어야 함
--    10G 까지는 PK 아니더라도 힌트로 제어 가능(/*+ bypass_ujvc */)
UPDATE /*+ bypass_ujvc */
       (SELECT e.dname
             , d.dname AS dname_new
          FROM emp  e
             , dept d
         WHERE d.deptno = e.deptno
        )
   SET dnam…


oracle:user_source
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
user_source

select *
from user_source
where upper(text) like '%kkk%'


oracle:user
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
사용자관리

	*  DBA
	*  H5 DB Import


SELECT username, account_status
     , T1.*
  FROM dba_users T1
 WHERE username in ('HUNEL','HUNEL_RET', 'H551')
;


oracle:wm_concat
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
WM_CONCAT is gone 12c

Hi tom,
when i tried use this wm_concat() function in 12c i'm getting this error can you briefly explain?

ORA-00904: “WM_CONCAT”: invalid identifier
00904. 00000 - “%s: invalid identifier 
and we said...
WM_CONCAT is gone in 12c.


oracle:xml_sql
Anonymous (anonymous@undisclosed.example.com) — 2025-04-15T10:05:18+00:00
select xt.*
  from xmltable('/persons/person' parsing xmltype (:x_xml)
  columns
  empno varchar2(10) path 'empno',
  empnm varchar2(50) path 'emp_nm') xt



select *
  from xmltable('123,123')