本文分类:
Oracle

授权

GRANT ALL PRIVILEGES ON XXXDATA.BASE_INFO TO XXXOPR;
REVOKE ALL PRIVILEGES ON XXXDATA.BASE_INFO FROM XXXOPR;
GRANT SELECT, UPDATE, INSERT, DELETE ON XXXDATA.BASE_INFO TO R_XXXDATA_DML;
REVOKE SELECT, UPDATE, INSERT, DELETE ON XXXDATA.BASE_INFO TO R_XXXDATA_DML;

索引

参考《create index注意n如果是大表建立索引,切记加上ONLINE参数》

CREATE INDEX XXXDATA.IDX_INFO_DEPT_ENDTIME
    ON XXXDATA.XXX_RENEWAL_INFO (DEPARTMENT_CODE, DATE_INSURANCE_END)
    PARALLEL 8
    INITRANS 16
    ONLINE;

ALTER INDEX XXXDATA.IDX_INFO_DEPT_ENDTIME NOPARALLEL ONLINE;

查询 SQL 执行情况

SELECT * FROM V$SQL WHERE SQL_ID = 'xxx';
SELECT * FROM V$SQLTEXT WHERE SQL_ID = 'xxx';

查询绑定变量的值

  SELECT sn.end_interval_time, sb.name, sb.value_string
    FROM dba_hist_sqlbind sb, dba_hist_snapshot sn
   WHERE sb.sql_id='xxxx'
     AND sb.was_captured='YES'
     AND sn.snap_id=sb.snap_id
ORDER BY sb.snap_id, sb.name, sb.position;

查询 for update 锁住的表

SELECT object_name, machine, s.sid, s.serial#
  FROM gv$locked_object l, dba_objects o, gv$session s
 WHERE l.object_id = o.object_id
   AND l.session_id = s.sid;

查询表及表注释

SELECT t.owner, t.table_name, a.comments
  FROM all_tables t, all_tab_comments a
 WHERE t.table_name = a.table_name
   AND t.table_name like 'EDR_%'

字符串连接

SELECT c1, wm_concat(id)
  FROM t_demo
GROUP BY c1

使用 Hint 指定索引

-- 语法
/*+INDEX(TABLE_NAME INDEX_NAME)*/

-- 例如:
SELECT /*+INDEX(T IDX_NAME) */ * FROM T_USER T WHERE T.NAME='hiwzc';
本文来自 [时光记 - 王智超的个人空间](www.hiwzc.com),转载请注明出处。