一、分区操作
--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'
select * from ALL_PART_TABLES
select * from USER_PART_TABLES
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='T_OV_QUOTATIONVEHICLE'
select * from USER_PART_KEY_COLUMNS
alter table T_OV_QUOTATIONVEHICLE add partition QV20160930 VALUES LESS THAN (TO_DATE('2016-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
---删除一个分区
alter table T_OV_QUOTATIONVEHICLE drop partition QV20160930
---递归查询(自下向上)
select t.dept_level as degree,
eu.emp_code as employeeCode,
eu.emp_name as employeeName
from (select ou.unified_code, ou.dept_level
from t_org_department_uu ou
where ou.dept_level > 4
start with ou.dept_code = 'W011302030604'
connect by prior ou.parent_org_code = ou.dept_code) t
left join T_ORG_EMPLOYEE_UU eu
on eu.unifield_code = t.unified_code
where eu.active = 'Y'
and (eu.position like '%经理'
or eu.position like '%总监' )
order by t.dept_level
二、awr报告自动生成时(Oracle AWR 手动配置)
----手工创建snap
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
----AWR配置都是通过dbms_workload_repository包进行配置
----调整AWR产生snapshot的频率和保留策略,如:如将收集间隔时间改为30 分钟一次。并且保留5天时间(注:单位都是为分钟)
:
SQL>exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);
---查看当前的AWR保存策略
SQL> select snap_interval,retention from dba_hist_wr_control;
SNAP_INTERVAL RETENTION
-------------------- --------------------
+00000 01:00:00.0 +00008 00:00:00.0
以上确认了默认的快照间隔为1小时 ,且保留时间为8天
---关闭AWR,把interval设为0则关闭自动捕捉快照
http://www.blogjava.net/gdufo/archive/2013/03/15/396511.html
三、查询锁表记录
SELECT 'ALTER SYSTEM KILL SESSION ''' || 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;
四、查询clob类型包含字符串
SELECT *
FROM VEHICLE.T_BASE_INTERFACELOG PC
WHERE PC.INTERFACETYPE = 'CRM_W'
AND DBMS_LOB.INSTR(PC.Content, '平高集团有限公司', 1, 1) > 0;