오라클DBMS/SQL,PL/SQL 썸네일형 리스트형 [오라클] LOB TABLESPACE MOVE 쿼리 생성 select 'alter table OWNER.' || TABLE_NAME || ' move lob(' || PATH# || ') store as (tablespace MOVETS);'from (select table_name,substr(max(sys_connect_by_path(column_name,',')),2) as path#from (select table_name,column_name,row_number() over(partition by table_name order by column_name) as ordfrom DBA_TAB_COLUMNSwhere TABLE_NAME in (select table_namefrom DBA_TABLESwhere tablespace_name = 'ORIGTS'.. 더보기 [오라클/PLSQL] 테이블 이름이 변경되는 DML문장 declare cursor cur_tables is select procedure_name,TABLE_NAME,OWNER, OWNER ||'.' || TABLE_NAME as full_name from DTABLE begin open CUR_TABLES; LOOP FETCH CUR_TABLES into V_PROCEDURE_NAME,V_TABLE_NAME,V_OWNER,V_FULL_NAME; exit when cur_tables%NOTFOUND; -- EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML'; -- EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE'; V_STR := 'insert /*.. 더보기 오라클 테이블과 관련 인덱스 일괄 수행 문장 생성 SQL 그 때 그 때 생각해서 하기 귀찮으니 적어두기select 'alter ' || decode(grp_no,'01','table ' || table_name ,'00','index ' || index_name) || ' move 'from (select a.table_name,b.index_name,grouping(a.table_name) || grouping(b.index_name) as grp_no -- table과 index를 구분하게 함from user_tables a , user_indexes bwhere a.table_name = b.table_namegroup by rollup(a.table_name,b.index_name) -- table과 index를 결합한곳에서 table 부분만 따로 생성.. 더보기 [오라클] 쿼리가 느려요? DB_LINK 테이블 서브쿼리로 JOIN SELECT A.TYPE_CD, A.TYPE_NAME, B.FORM_CD, B.FORM_NAME, 0 AS CHK FROM TF_APP_TYPE_CD A LEFT OUTER JOIN TF_APP_FORM_CD B ON A.TYPE_CD = B.TYPE_CD WHERE 1 = 1 AND A.USE_YN = '1' AND B.USE_YN = '1' AND A.TYPE_CD || B.FORM_CD NOT IN ( SELECT SUB.TYPE_CD || SUB.FORM_CD FROM TF_APP_FIN_CHR_SETUP SUB LEFT OUTER JOIN VW_FIN_DEPT_LEVEL SUB2 ON SUB.DEPT_CD = SUB2.DEPT_CD WHERE 1 = 1 AND SUB2.DEPT_CD IS NOT.. 더보기 [UPDATE] JOIN-VIEW를 이용한 UPDATE시 KEY 대입이 안된다면 update /*+bypass_ujvc */ ( select a.mrt a_mrt,b.mrt b_mrt from table a , table b where a.dt in ('20111011','20111012','20111013','20111014','20111017') and b.dt = '20110923' and a.cd = b.cd AND b.KNCD NOT IN ('30','40') AND a.KNCD NOT IN ('30','40') ) set a_mrt = b_mrt ; key는 dt,cd 이나 key 끼리 조인이 되지 않으면 /*bypass_ujvc */ 힌트로 해결 출처 : http://oracleebs.co.kr/?p=25 더보기 이전 1 2 3 다음