Useful tips for Oracle database
The following sql codes were used to refer to occasionally. It’s good to think of it as a note because it’s hard to remember all the subtle differences among various kinds of codes.
Decode, max
SELECT A.BIZ_DATE,
MAX(DECODE(A.EXCHANGE, 'EXCH1', A.CNT, 0)) AS EXCH1,
MAX(DECODE(A.EXCHANGE, 'EXCH2', A.CNT, 0)) AS EXCH2,
MAX(DECODE(A.EXCHANGE, 'EXCH3', A.CNT, 0)) AS EXCH3,
MAX(DECODE(A.EXCHANGE, 'EXCH4', A.CNT, 0)) AS EXCH4,
MAX(DECODE(A.EXCHANGE, 'EXCH5', A.CNT, 0)) AS EXCH5,
MAX(DECODE(A.EXCHANGE, 'EXCH6', A.CNT, 0)) AS EXCH6
FROM (SELECT C.EXCH_EINM AS EXCHANGE,
SUBSTR(A.BUSIN_DATE, 1, 6) AS BIZ_DATE,
COUNT(*) AS CNT
FROM TBL1 A,
TBL2 B,
TBL3 C
WHERE A.FD_ITEM_CD = B.FD_ITEM_CD
AND B.EXCH_CD = C.EXCH_CD
AND A.BUSIN_DATE >= '20140101'
GROUP BY C.EXCH_EINM,
SUBSTR(A.BUSIN_DATE, 1, 6)) A
GROUP BY BIZ_DATE
ORDER BY BIZ_DATE;
sqlplus options
set echo on/off
set line 120
set linesize 120
set pages 500
set pagesize 500
set pause on/off
set trace on/off
set head on/off
set heading on/off
set verify on/off
set feedback on/off
set time on/off
set timing on/off
set newpage 0
set space 3
set serveroutput on/off
set trim on/off
set trims on/off
set numwidth on/off
set termout on/off
set recsep on/off
set pagesize 25 heading on freedback on termout on
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
alter session set NLS_NUMERIC_CHARACTERS = ", ";
alter session set NLS_NUMERIC_CHARACTERS = "dg";
define _editor=vi
column column_name foramt a30
column linesize 300
Find
-- Find rows containing non-alphanumeric (cf. non-ASCII characters)
select * from
(
select nvl(REGEXP_SUBSTR(etc, '[^,]+',1,1),48) as etc0,
nvl(REGEXP_SUBSTR(etc, '[^,]+',1,2),48) as etc1,
nvl(REGEXP_SUBSTR(etc, '[^,]+',1,3),48) as etc2,
nvl(REGEXP_SUBSTR(etc, '[^,]+',1,4),48) as etc3,
nvl(REGEXP_SUBSTR(etc, '[^,]+',1,5),48) as etc4,
nvl(REGEXP_SUBSTR(etc, '[^,]+',1,6),48) as etc5,
nvl(REGEXP_SUBSTR(etc, '[^,]+',1,7),48) as etc6,
nvl(REGEXP_SUBSTR(etc, '[^,]+',1,8),48) as etc7,
etc,
user_id
from ( select substr(dump(user_id),14) etc, user_id from com.com_c2t101c where ACT_SECT ='A')
)
where ((etc0 not between 48 and 57) and (etc0 not between 65 and 90) and (etc0 not between 97 and 122)) or
((etc1 not between 48 and 57) and (etc1 not between 65 and 90) and (etc1 not between 97 and 122)) or
((etc2 not between 48 and 57) and (etc2 not between 65 and 90) and (etc2 not between 97 and 122)) or
((etc3 not between 48 and 57) and (etc3 not between 65 and 90) and (etc3 not between 97 and 122)) or
((etc4 not between 48 and 57) and (etc4 not between 65 and 90) and (etc4 not between 97 and 122)) or
((etc5 not between 48 and 57) and (etc5 not between 65 and 90) and (etc5 not between 97 and 122)) or
((etc6 not between 48 and 57) and (etc6 not between 65 and 90) and (etc6 not between 97 and 122)) or
((etc7 not between 48 and 57) and (etc7 not between 65 and 90) and (etc7 not between 97 and 122))
;
-- Remove number, find column that contains numbers
select length(translate('ab123', '0123456789'||'ab123', '0123456789')),
length('ab123')
from dual;
Date format
select to_char(to_date(NOTI_DAY, 'yyyymmdd'), 'dd Mon YYYY')
from NEWPMS.IT_NOTIC ;
select to_char(to_date(NOTI_DAY, 'yyyymmdd'), 'dd Month YYYY')
from NEWPMS.IT_NOTIC ;
-- Change format
select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';
alter system set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
-- Microseconds
select TO_CHAR(SYSTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS FF') from dual;
-- Day
select decode(TO_CHAR(TO_DATE('20080412', 'YYYYMMDD'),'D'),1,'Sun',2,'Mon',3,'Tue',4,'Wed',5,'Thu',6,'Fri',7,'Sat','') from dual;
-- Month (Short name: Mon)
SELECT TO_CHAR(SYSDATE, 'DD MON YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'DD MONTH YYYY') FROM DUAL;
-- dump oracle sysdate
select dump(sysdate) from dual;
DUMP(SYSDATE)
--------------------------------------------------------------------------------
Typ=13 Len=8: 7,216,4,22,10,28,44,0
Join and Update
-- example #1
UPDATE TEMP2 A
SET BB = (
SELECT RANK_
FROM (SELECT AA,CC,RANK() OVER (PARTITION BY AA ORDER BY CC) AS RANK_
FROM TEMP2)
WHERE A.AA=AA
AND A.CC=CC)
WHERE A.AA='A1';
select * from temp2;
AA BB CC
-- -- --
a1 45 01
a1 10 02
a1 12 03
a1 38 04
a1 21 05
b1 12 01
b1 14 02
b1 17 03
b1 26 04
c1 33 01
c1 76 02
c1 99 03
select * from temp2;
AA BB CC
-- -- --
a1 1 01
a1 2 02
a1 3 03
a1 4 04
a1 5 05
b1 12 01
b1 14 02
b1 17 03
b1 26 04
c1 33 01
c1 76 02
c1 99 03
-- Example #2
UPDATE (
SELECT STRT_DAY,
END_DAY,
STAG_1STRT_DAY,
STAG_1END_DAY
FROM IT_ENTP_PLAN_MST A, IT_ENTP_DEV_SCHD B
WHERE SUBSTR(A.SEQ,1,4) ='2012' AND A.SEQ = B.SEQ
)
SET STRT_DAY=STAG_1STRT_DAY, END_DAY=STAG_1END_DAY;
-- Check the result
SELECT A.SEQ,STRT_DAY,
STAG_1STRT_DAY,
END_DAY,
STAG_1END_DAY
FROM IT_ENTP_PLAN_MST A, IT_ENTP_DEV_SCHD B
WHERE SUBSTR(A.SEQ,1,4) ='2012' AND A.SEQ = B.SEQ;
DBMS_JOB
-- Create test procedure
CREATE OR REPLACE PROCEDURE P_JOB
AS
V_NUM NUMBER(3) ;
BEGIN
SELECT NVL(MAX(ID),0) + 1
INTO V_NUM
FROM TEST_JOB ;
INSERT INTO TEST_JOB
VALUES ( V_NUM , SYSDATE) ;
COMMIT ;
END ;
/
-- Run procedure
DECLARE
V_JOB NUMBER(5); -- This is the job no. (Required)
BEGIN
DBMS_JOB.SUBMIT( V_JOB , 'P_JOB;' , SYSDATE , 'SYSDATE + 1/86400' ) ;
DBMS_JOB.RUN( V_JOB ) ;
DBMS_OUTPUT.PUT_LINE( V_JOB ) ;
--DBMS_JOB.REMOVE( V_JOB );
END ;
/
-- Rmove job
DBMS_JOB.REMOVE( V_JOB );
-- select job
select * from user_jobs;
Examples
SYSDATE + 1/24/6
SYSDATE + 1/144
-> Every 10 minutes
SYSDATE + 1/24/60
SYSDATE + 1/1440
-> Every minute
TRUNC(SYSDATE) + 1 + 2/24
-> Everyday 2:00 a.m. (From tomorror)
TRUNC(SYSDATE) + 23/24
-> Today 11:00 p.m
Tablespace
col owner format a10
col "size(M)" format 999,999,999
col "count" format 999,999
select owner, tablespace_name, segment_type, count(*) "count",round(sum(bytes/1024/1024)) "size(M)"
from dba_segments
where owner not like 'SYS%'
group by owner,tablespace_name,segment_type
order by owner,tablespace_name,segment_type
/
/* select tablespace size */
select tablespace_name, segment_type, count(*) "count", round(sum(bytes/1024/1024)) "size(M)"
from user_segments
group by tablespace_name,segment_type
order by tablespace_name,segment_type;
select tablespace_name, block_size,
Making dummy records
WITH DUMMY_T AS (
SELECT '07' AS DT FROM DUAL UNION ALL
SELECT '08' AS DT FROM DUAL UNION ALL
SELECT '09' AS DT FROM DUAL UNION ALL
SELECT '10' AS DT FROM DUAL UNION ALL
SELECT '11' AS DT FROM DUAL UNION ALL
SELECT '12' AS DT FROM DUAL UNION ALL
SELECT '13' AS DT FROM DUAL UNION ALL
SELECT '14' AS DT FROM DUAL UNION ALL
SELECT '15' AS DT FROM DUAL UNION ALL
SELECT '16' AS DT FROM DUAL UNION ALL
SELECT '17' AS DT FROM DUAL UNION ALL
SELECT '18' AS DT FROM DUAL UNION ALL
SELECT '19' AS DT FROM DUAL UNION ALL
SELECT '20' AS DT FROM DUAL)
SELECT * FROM DUMMY_T;
Retrieve table information such as column name
SELECT COLUMN_ID NO,
A.OWNER,
A.TABLE_NAME,
A.COLUMN_NAME,
DATA_TYPE|| DECODE(DATA_TYPE, 'NUMBER', DECODE(DATA_PRECISION, NULL, '' , DECODE(DATA_SCALE, 0, '('||DATA_PRECISION||')', '('||TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)||')')), 'FLOAT', DECODE(DATA_PRECISION, NULL, '' , DECODE(DATA_SCALE, NULL, '('||DATA_PRECISION||')', '('||TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)||')')), '('||DATA_LENGTH||')') DATA_TYPE,
DECODE(NULLABLE, 'N', 'NOT NULL', '') NULLABLE,
DATA_DEFAULT "DEFAULT",
B.COMMENTS
FROM ALL_TAB_COLUMNS A,
ALL_COL_COMMENTS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.OWNER IN ('your_db_id')
AND A.TABLE_NAME NOT LIKE 'BIN%'
AND A.TABLE_NAME LIKE UPPER('your_tablename')
ORDER BY A.OWNER, A.TABLE_NAME, A.COLUMN_ID;
SELECT A.OWNER,
A.TABLE_NAME,
B.COLUMN_NAME
FROM ALL_CONSTRAINTS A,
ALL_CONS_COLUMNS B
WHERE A.TABLE_NAME=B.TABLE_NAME
AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
AND A.CONSTRAINT_TYPE = 'P'
AND A.OWNER IN ('TRD');
SYS_CONNECT_BY_PATH
SELECT A
, MAX(SYS_CONNECT_BY_PATH(B, ',')) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , 'AA' B FROM DUAL
UNION ALL
SELECT 1 A , 'BB' B FROM DUAL
UNION ALL
SELECT 1 A , 'CC' B FROM DUAL
UNION ALL
SELECT 2 A , 'DD' B FROM DUAL
UNION ALL
SELECT 2 A , 'EE' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
GROUP BY A;
Level, connect by
--Oracle 10g requires 'connect by' when you use 'level'
--Oracle 8i : printed 1, Oracle 10g : printed from 1 to 10
select level as rnum from dual connect by level<=10;
--Print 1 month
select to_char(to_date('21001201', 'yyyymmdd') + level -1, 'yyyymmdd') mon
from dual connect by level < 31 order by 1;
--Tree
select level from (
select * from dual connect by level <=2)
connect by level <=3;
Leave a comment