Oracle related

1. 权限传递：

系统权限传递：with admin option

对象权限传递：with grant option

 

2. 查看package的所有方法：

DESC DBMS_WORKLOAD_REPOSITORY;

 

3. 查看AWR setting

select * from dba_hist_wr_control;

 

4. package, procedure, function

function有返回值，有参数；

procedure无返回值，有参数；

package可包括function,procedure

package中只有各个方法的定义，package body中涉及具体的实现。

 

5. 块

DECLARE
  v_object_id NUMBER(4);
  v_object_type VARCHAR2(10);
BEGIN
  v_object_type := '&TYPE';
  SELECT OBJECT_ID INTO V_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'DBA_HIST_WR_CONTROL' AND OBJECT_TYPE = v_object_type;
  dbms_output.put_line(v_object_id);
END;

 

6. 使用sqlplus 的时候，密码中有特殊字符。

./sqlplus 'user/"#Ee_l!f832"'@SID

 

 7. Wait event

"DFS lock handle": (due to ordered)

"row cache lock": (due to cache size too small)

 

8. Select space usage for each tablespaces

SELECT A.TABLESPACE_NAME,TOTAL,FREE,(TOTAL-FREE) USDED,ROUND((TOTAL-FREE)/TOTAL,4)*100 "USAGE %"
FROM  (SELECT TABLESPACE_NAME,SUM(BYTES) FREE FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) A,
(SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME ORDER BY TABLESPACE_NAME;

 

9. 转义 单引号

SELECT CHR(39) FROM DUAL;

select Q'[']' from dual;

 

10. BULK COLLECT & FORALL

DECLARE
  TYPE TYPE_INVST_ACCT_NUM IS TABLE OF TMP_SH_ACCT.INVST_ACCT_NUM%TYPE INDEX BY BINARY_INTEGER;
  V_INVST_ACCT_NUM TYPE_INVST_ACCT_NUM;
BEGIN
  
  SELECT INVST_ACCT_NUM BULK COLLECT INTO V_INVST_ACCT_NUM FROM TMP_SH_ACCT WHERE FUNCTION_TYPE = 'GFIX_EXEC';

  FORALL i IN V_INVST_ACCT_NUM.first..V_INVST_ACCT_NUM.last
  EXECUTE IMMEDIATE 'BEGIN SC_PRODUCT_CODE_RANDOM(:1, 1);END;' USING V_INVST_ACCT_NUM(i);
  COMMIT;
END;
/

 

11. 最高效的删除重复记录方法 (因为使用了ROWID)例子：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

 

12. AWR report

SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY BEGIN_INTERVAL_TIME desc;
SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(2503047371,'1','110345','110346'));
SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_HTML(2503047371,'1','110345','110346','d72f1bx3vsv1t'));

 

 

13. Java calls function & stored procedure.

# Stored procedure
CallableStatement ctmt = conn.prepareCall("{call TESTP()}");
 
CallableStatement ctmt = conn.prepareCall("{call TESTP(?)}");
ctmt.registerOutParameter(1, Types.VARCHAR);
 
# Function
PreparedStatement stmt = conn.prepareStatement("SELECT TESTF() FROM dual");
 
CallableStatement ctmt = conn.prepareCall("{? = call TESTF}");
ctmt.registerOutParameter(1, Types.VARCHAR);