ORACLE的SQL集锦
来源:互联网 发布:ug4数控车床编程 编辑:程序博客网 时间:2024/04/28 05:36
1. 一张自关联的表,查询的结果集要求先显示父数据,再显示其子数据。其中下面语句中的-1就是父数据
SELECT A.* FROM XT_NOSAFE_VINDI A START WITH A.PARENT_CODE='-1'CONNECT BY PRIOR A.FACTOR_CODE=A.PARENT_CODE ORDER SIBLINGS BY A.FACTOR_CODE
结果集:
1 粉尘类 0001 -1
2 游离二氧化硅粉尘 4001 0001
3 煤尘(包括煤矽尘) 4002 0001
4 石棉粉尘 4003 0001
5 其它粉尘 4004 0001
6 棉尘(包括亚麻、软大麻、黄麻粉尘) 4005 0001
7 放射性物质类 0002 -1
8 射线 3001A 0002
9 化学物质类 0003 -1
10 铅及其无机化合物 1001 0003
11 四乙基铅 1002 0003
12 汞及其无机化合物 1003 0003
13 铍及其无机化合物 1004 0003
14 镉及其无机化合物 1005 0003
15 铬及其无机化合物 1006 0003
2. WM_CONCAT以及LISTAGG用法,其中建议使用LISTAGG,其效率高。
SELECT A.DESMTD_ID, WM_CONCAT(A.RID) FROM BX_SELRST_BE A WHERE A.REPSUB_ID IN(201258,201279) GROUP BY A.DESMTD_ID SELECT A.DESMTD_ID, LISTAGG(A.RID,',') WITHIN GROUP (ORDER BY A.DESMTD_ID) ALL_FORMS FROM BX_SELRST_BE A WHERE A.REPSUB_ID IN(201258,201279) GROUP BY A.DESMTD_ID
3. 创建JOB
CREATE OR REPLACE PROCEDURE PRO_CHG_NETTAG(VAL_ IN VARCHAR2) ISBEGINUPDATE TS_SYSTEM_PARAM SET PARAM_VALUE=VAL_ WHERE PARAM_NAME='VERIFYCODE_NETTAG';COMMIT;END PRO_CHG_NETTAG;/--每天早上一点DECLARE JOB_CHG_NET1 NUMBER;BEGINDBMS_JOB.SUBMIT(JOB_CHG_NET1,'PRO_CHG_NETTAG(3);',TRUNC(SYSDATE+1)+1/24, 'TRUNC(SYSDATE+1)+1/24');END;/--每天早上7点DECLARE JOB_CHG_NET2 NUMBER;BEGINDBMS_JOB.SUBMIT(JOB_CHG_NET2,'PRO_CHG_NETTAG(1);',TRUNC(SYSDATE+1)+7/24, 'TRUNC(SYSDATE+1)+7/24');END;/
4.行转列
SELECT * FROM (SELECT ORGIDX, GDSIDX, STM_CNT FROM TD_JXC_CNTSTM_INFO) TPIVOT (SUM(STM_CNT) FOR GDSIDX IN (202,301,402,3)) ORDER BY ORGIDX; ORGIDX 202 301 402 31 1 0 0 13612 655480 43 655482 1000 720 1046 4 700099 21400 11080 10954 6455 700381 600 6 35197450 49000SELECT ORGIDX,GDSIDX, SUM(STM_CNT) FROM TD_JXC_CNTSTM_INFO WHERE GDSIDX IN (202,301,402,3)GROUP BY ORGIDX,GDSIDXORDER BY ORGIDX ORGIDX GDSIDX SUM(STM_CNT)1 1 3 13612 1 202 03 1 301 04 655480 3 45 655482 202 10006 655482 301 7207 655482 402 10468 700099 3 6459 700099 202 2140010 700099 301 1108011 700099 402 1095412 700381 202 60013 35197450 3 49000
5. 获取日期所在周
SELECT TO_CHAR(TO_DATE('2013-12-31','YYYY-MM-DD'), 'IYYY-IW') FROM DUAL
6. 根据某列分组,并获取其中rid最大的
MAX(A.IDX) OVER(PARTITION BY A.UNIT_ID)
7.限制允许IP访问设置
在ORACLE\PRODUCT\10.2.0\DB_1\NETWORK\ADMIN\SQLNET.ORA最后添加TCP.VALIDNODE_CHECKING=YESTCP.INVITED_NODES=(127.0.0.1,10.88.99.2,10.88.99.168,10.88.99.24,10.88.99.13) --可以访问的IP
8.ORACLE10G修改内存
ALTER SYSTEM SET SGA_MAX_SIZE=960M SCOPE=SPFILE;ALTER SYSTEM SET SGA_TARGET=960M SCOPE=SPFILE;ALTER SYSTEM SET PGA_AGGREGATE_TARGET=240M SCOPE=SPFILE;
9.编写SCHEDULER
-- 执行业务操作BEGINDBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'DX_NETTAG_JOB',JOB_TYPE => 'PLSQL_BLOCK',JOB_ACTION => 'BEGIN UPDATE TD_TASKLIST SET NET_TAG = 3 WHERE SYSTEM_MARK = 1; COMMIT; END;',START_DATE => SYSDATE,REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=10;BYHOUR=1,2,3,4,5,6',ENABLED => TRUE);END;/--启动JOBBEGINDBMS_SCHEDULER.ENABLE('DX_NETTAG_JOB');END;/-- 运行job BEGIN DBMS_SCHEDULER.RUN_JOB('DX_NETTAG_JOB',TRUE); -- TRUE代表同步执行 END; / --删除JOBBEGINDBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'DX_NETTAG_JOB',FORCE => TRUE); END;/-- 停止job BEGIN DBMS_SCHEDULER.STOP_JOB(JOB_NAME => 'DX_NETTAG_JOB',FORCE => TRUE); END; / -- 查询job SELECT * FROM USER_SCHEDULER_JOBS; SELECT * FROM DBA_SCHEDULER_JOBS; --查看数据SELECT T.*, T.ROWID FROM AAA T--更改DATE的格式ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'; ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS'; ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH:MI:SS.FF AM TZR'; --查看DATE的格式SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER LIKE '%_DATE_%' OR PARAMETER LIKE '%_TIMESTAMP_%'; --授权 sys GRANT CREATE JOB TO DXCDC; GRANT MANAGE SCHEDULER TO DXCDC;--测试JOB执行的时间SET SERVEROUTPUT ON SIZE 999999DECLARE L_START_DATE TIMESTAMP; L_NEXT_DATE TIMESTAMP; L_RETURN_DATE TIMESTAMP;BEGIN L_START_DATE := TRUNC(SYSTIMESTAMP); L_RETURN_DATE := L_START_DATE; FOR CTR IN 1 .. 50 LOOP DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=DAILY;BYHOUR=0,1,2,3,4,5,6,7,22,23', L_START_DATE, L_RETURN_DATE, L_NEXT_DATE); DBMS_OUTPUT.PUT_LINE('NEXT RUN ON: ' || TO_CHAR(L_NEXT_DATE, 'YYYY-MM-DD HH24:MI:SS')); L_RETURN_DATE := L_NEXT_DATE; END LOOP;END;/
10. 查询ORACLE限制
select count(*) from v$session; 42select * from v$license;SESSIONS_MAX:指定允许同时进行的并行用户会话的最大数量。达到该限制后, 只有具有 RESTRICTED SESSION 权限的用户才能连接到服务器。所有其他用户都会收到一条警告消息, 表明已达到系统最大容量的限制SESSIONS_WARNING:指定对并行用户会话数量的警告限制。达到该限制后, 其他用户仍然可以连接, 但一条消息将被写入 ALERT 文件。具有RESTRICTED SESSION 权限的用户将收到一条警告消息, 表明系统已接近最大容量。USERS_MAX:指定您可在该数据库中创建的用户的最大数量。并行会话使用许可和用户使用许可不应被同时启用。LICENSE_MAX_SESSIONS 或 LICENSE_MAX_USERS 或这两者的值应为零。SESSIONS_CURRENT 表示已经连接的 SESSION 数量(注:该值并不等于 v$session 中查询出来的数量,似乎并不包括 Oracle 本身连接的 SESSION,
11.生成执行计划步骤
(1). sys进入--创建 plan tableSQL> @?rdbms/admin/utlxplanSQL> create public synonym plan_table for plan_table;SQL> grant all on plan_table to public ;--创建plustrace 角色SQL> @?sqlplus/admin/plustrce --执行plustrce.sql--plustrace授予publicSQL> grant plustrace to public;(2).普通用户进入set autotrace traceonly;sqlcontent;或者 explain plan for sqlcontent;select * from table(dbms_xplan.display)
12.强制使用索引
SELECT /*+index_ffs(B idx_TD_JXC_PURMTB_4)*/ NVL(SUBSTR(Z.ZONE_GB, 0, 4), '合计') AS ZONE_CODE, NVL(SUM(DECODE(B.INXTYPEID, 1, 1, 17, 1, 0)), '0') AS RST1, NVL(SUM(DECODE(B.INXTYPEID, 1, B.IF_INTIME, 17, B.IF_INTIME, 0)), '0') AS RST2 FROM XT_ZONE Z LEFT JOIN XT_UNIT A ON A.ZONE_ID = Z.RID AND A.ORGIDX IS NULL LEFT JOIN TD_JXC_PURMTB B ON B.ORGIDX = A.RID AND B.INXDAY BETWEEN TO_DATE('2013-01-01', 'YYYY-MM-DD') AND TO_DATE('2013-12-31', 'YYYY-MM-DD') WHERE 1 = 1 AND Z.ZONE_GB LIKE '32%' GROUP BY ROLLUP(SUBSTR(Z.ZONE_GB, 0, 4))全表扫描SELECT /*+full(B) full(C)*/ NVL(SUBSTR(Z.ZONE_GB, 0, 4), '合计') AS ZONE_CODE, NVL(SUM(DECODE(B.INXTYPEID, 1, 1, 17, 1, 0)), '0') AS RST1, NVL(SUM(DECODE(B.INXTYPEID, 1, B.IF_INTIME, 17, B.IF_INTIME, 0)),'0') AS RST2 FROM XT_ZONE Z LEFT JOIN XT_UNIT A ON A.ZONE_ID = Z.RID AND A.ORGIDX IS NULL LEFT JOIN TD_JXC_PURSUB C ON C.ORGIDX = A.RID LEFT JOIN TD_JXC_PURMTB B ON C.MTBIDX = B.RID AND B.INXDAY BETWEEN TO_DATE('2013-01-01', 'YYYY-MM-DD') AND TO_DATE('2013-12-31', 'YYYY-MM-DD') WHERE 1 = 1 AND Z.ZONE_GB LIKE '32%' GROUP BY ROLLUP(SUBSTR(Z.ZONE_GB, 0, 4));
13.查看表占用的空间
select table_name,(blocks+empty_blocks)*8*1024 from user_tables where table_name in ('TD_JXC_PURMTB', 'TD_JXC_PURSUB')
14.索引管理
(1).移动索引的表空间ALTER INDEX INDEX_OWNER.INDEX_NAME REBUILD TABLESPACE NEW_TBS;(2).获得某个SCHEMA下移动索引表空间的所有语句SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE NEW_TBS;' FROM DBA_INDEXES WHERE TABLE_OWNER='SCHEMA_NAME' ORDER BY INDEX_NAME; (3).创建新表,指定唯一索引的表空间CREATE TABLE TMP( MON VARCHAR2(6) , IDNO VARCHAR2(10) , CONSTRAINT PK_NAME PRIMARY KEY ( MON, IDNO ) USING INDEX TABLESPACE INDEX_TBS, CONSTRAINT UK_NAME UNIQUE (MON) USING INDEX TABLESPACE INDEX_TBS
15.清空缓存
--sys用户--清空共享池:ALTER SYSTEM FLUSH SHARED_POOL;--清空BUFER ALTER SYSTEM FLUSH BUFFER_CACHE;ALTER SESSION SET RESULT_CACHE_MODE = MANUAL;
16查询ORACLE的CPU个数
Select * FROM V$OPTION where parameter like 'Parallel%';select * from V$pq_sesstat;
0 0
- ORACLE的SQL集锦
- oracle SQL集锦
- Oracle常用SQL集锦
- Oracle 精妙SQL语句集锦
- Oracle 精妙SQL语句集锦
- Oracle中SQL编写集锦
- 监控oracle性能的常用sql集锦(转载)
- ORACLE的基本语法集锦
- ORACLE的基本语法集锦
- ORACLE的基本语法集锦
- ORACLE的基本语法集锦
- ORACLE的基本语法集锦
- ORACLE的基本语法集锦
- ORACLE的基本语法集锦
- ORACLE的基本语法集锦
- 精妙的SQL语句集锦
- 精妙的SQL语句集锦
- Oracle集锦
- poj2817(N!的状压DP)
- 题目1116:加减乘除
- Eyou Mail System Remote Code Execution
- 软件质量评价标准
- Android入门
- ORACLE的SQL集锦
- zoj 3742 Bellywhite's Algorithm Homework
- hdu1157解题报告
- 简单的JDK环境配置
- 2013总结 && 2014展望
- 深入分析java web 技术内幕_笔记_三
- java android面试题分析总结
- cp: cannot stat `bootable/bootloader/lk/build-xxx/lk.bin': No such file or directory
- 关于操作系统的引论——简单的计算机系统介绍