常用dml和query開并行
--開并行
ALTER SESSION FORCE PARALLEL DML PARALLEL 16;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 16;
--關并行
ALTER SESSION DISABLE PARALLEL DML ;
ALTER SESSION DISABLE PARALLEL QUERY;
索引開并行
drop index IDX_SB_SBZT_LRRQ;
create index IDX_SB_SBZT_LRRQ on SB_SBZT (LRRQ, CWLX_DM, SWJG_DM)
tablespace TS_GS_SB_IDX
pctfree 20
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
) parallel 6;
alter index IDX_SB_SBZT_LRRQ noparallel;
查看ASM
-- 查看asm的各個disk group是否超過80%
-- name：name of the disk group
-- total_mb：total capacity of the disk group(in megabytes)
-- free_mb：unused capacity of the disk group(in megabytes)
select t.name,t.TOTAL_MB /1024, round(100 * (t.TOTAL_MB - t.FREE_MB) / t.TOTAL_MB, 2)||'%'
from v$asm_diskgroup t;
查看資料庫表空間
--查詢資料表空間情況
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024,
'99999999.999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),
'990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '99999999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
order by "Used %" desc;
判斷欄位中是否包含小寫字母
--gs_cx查詢納稅人識別號這個欄位中包含小寫字母的并且做過工資薪金稅費種認定的戶數：upper函式
select count(*) from hx_dj.dj_nsrxx a,hx_dj.dj_nsrxx b,hx_rd.rd_sfzrdxxb r
where a.djxh=b.djxh
and a.djxh=r.djxh
and r.zspm_dm='101060100'
and upper(a.nsrsbh) <> a.nsrsbh
and a.nsrsbh like '3%';
判斷欄位中是否包含大寫字母
--gs_cx查詢納稅人識別號這個欄位中包含大寫字母的并且做過工資薪金稅費種認定的企業戶數：lower函式
select count(*) from hx_dj.dj_nsrxx a,hx_dj.dj_nsrxx b,hx_rd.rd_sfzrdxxb r
where a.djxh=b.djxh
and a.djxh=r.djxh
and r.zspm_dm='101060100'
and lower(a.nsrsbh) <> a.nsrsbh
and a.nsrsbh like '3%';
Lengthb和length
select length('新奧投資基金管理（北京）有限公司') from dual;
select lengthb('新奧投資基金管理（北京）有限公司') from dual;
--utf-8：一個中文對三個位元組
按時間段統計資料量
--sb_kjgrsdsbgb 按五分鐘統計數量
select tmp.newTime, count(tmp.newTime)
from (select a.jylsh,
to_char(a.lrrq, 'yyyy-mm-dd hh24:mi') oldTime,
case
when substr(to_char(a.lrrq, 'mi'), 2, 1) < 5 then
to_char(lrrq, 'yyyy-mm-dd HH24') || ':' ||
substr(to_char(a.lrrq, 'mi'), 1, 1) || 0
else
to_char(lrrq, 'yyyy-mm-dd HH24') || ':' ||
substr(to_char(a.lrrq, 'mi'), 1, 1) || 5
end as newTime
from gs_cxtj.sb_kjgrsdsbgb a
where a.lrrq > date '2016-11-16'
order by newTime desc) tmp
group by tmp.newtime
order by tmp.newtime desc;
Cursor大資料量批量插入
declare
cnt integer := 0;
cursor cur_1 is
select * from gs_cl.py_dj_nsrxx;
begin
for icur_1 in cur_1 loop
insert into xtxj_zdgsxx values (icur_1.DJXH,icur_1.ZGSWJ_DM,icur_1.SWJGMC,icur_1.SJTB_SJ,icur_1.YXBZ,icur_1.XGSJ);
cnt := cnt + 1;
if cnt >= 5000 then
commit;
cnt := 0;
end if;
end loop;
commit;
exception
when others then
rollback;
end;
/
decode的使用
--不同渠道扣款數量
select decode(substr(jywysbh, 1, 6),
'JSDS.N',
'網廳',
'SB0603',
'客戶端',
'大廳') "渠道來源",
count(case when a.lrrq>to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') then djxh end)"非0申報當天扣款量",
count(*) "非0申報扣款數量"
from sb_sbzt a
where a.lrrq > date '2016-10-01'
and a.yrkse_1 > 0
and cwlx_dm = 'Y'
and se = yrkse_1
group by substr(jywysbh, 1, 6)
分組資料中每組取前幾條資料
--先得到已分組結果集
select t.nsrsbh, t.nsrmc, t.zgswj_dm, swjg.swjgmc
from dj_nsrxx t, rd_sfzrdxxb sfz, dm_gy_swjg swjg
where t.djxh = sfz.djxh
and t.zgswj_dm = swjg.swjg_dm
and t.shxydm is null
and t.nsrzt_dm = '03'
and sfz.zspm_dm = '101060100'
and sfz.rdyxqz > date'2016-08-31'
and substr(t.zgswj_dm, 1, 5) || '000000' in
(select b.swjg_dm
from dm_gy_swjg a, dm_gy_swjg b
where substr(a.swjg_dm, 1, 5) || '000000' = b.swjg_dm
and b.swjg_dm not in
('23200000000', '23299000000', '00000000000')
group by b.swjg_dm)
--按稅務機關分類查詢兩條記錄
select f.ROWN 行號,
f.djxh 登記序號,
f.nsrsbh 納稅人識別號,
f.nsrmc 納稅人名稱,
f.zgswj_dm 主管稅務機關代碼,
f.swjgmc 主管稅務機關名稱,
substr(f.zgswj_dm, 1, 5) || '000000' 地市稅務局名稱
from (SELECT ROW_NUMBER() OVER(PARTITION BY substr(res.zgswj_dm, 1, 5) || '000000' ORDER BY res.zgswj_dm) as ROWN,
res.*
FROM （select t.djxh, t.nsrsbh, t.nsrmc, t.zgswj_dm, swjg.swjgmc
from dj_nsrxx t, rd_sfzrdxxb sfz, dm_gy_swjg swjg
where t.djxh = sfz.djxh
and t.zgswj_dm = swjg.swjg_dm
and t.shxydm is null
and t.nsrzt_dm = '03'
and t.nsrmc like '%公司'
and length(t.nsrsbh) = 15
and sfz.zspm_dm = '101060100'
and sfz.rdyxqz > date '2016-08-31' ） res) f
WHERE f.ROWN <= 20
order by f.zgswj_dm
case和when的使用
select * from (select decode(substr(jywysbh, 1, 4),
'JSDS',
'網廳',
'',
'大廳',
'客戶端') "渠道來源",
count(case
when a.lrrq >
to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') and
a.yrkse_1 > 0 and se = yrkse_1 then
yzpzxh
end) "非0申報當天扣款量",
count(case
when a.lrrq >
to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') and
se > 0 then
yzpzxh
end) " 當天非0申報量",
count(case
when a.lrrq >
to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') then
yzpzxh
end) " 當天申報量",
count(case
when a.yrkse_1 > 0 and se = yrkse_1 then
yzpzxh
end) "非0申報本月扣款量",
count(case
when se > 0 then
yzpzxh
end) "本月非0申報量",
count(case
when 1 = 1 then
yzpzxh
end) "本月總申報量"
from sb_sbzt a
where a.lrrq >
to_date(to_char(sysdate, 'yyyy-mm') || '01', 'yyyy-mm-dd')
and cwlx_dm = 'Y'
group by substr(a.jywysbh, 1, 4))
查詢性能最差和最耗時的SQL
--性能最差的SQL
select *
from (select sql_text, disk_reads, buffer_gets, rows_processed
from v$sqlarea b
order by disk_reads desc)
where rownum <= 10;
--最耗時的SQL
select *
from (select a.SQL_TEXT, a.CPU_TIME, a.PARSING_SCHEMA_NAME
from v$sql a
order by cpu_time desc)
where rownum <= 10;
創建資料庫表空間
create tablespace TS_GS_ZM_SSWSZMJLMX_2016_DATA
datafile 'D:\APP\CLG\ORADATA\ORCL\TS_GS_ZM_SSWSZMJLMX_2016_DATA.dbf'
size 50M
AUTOEXTEND ON NEXT 50M;
資料庫鎖表查詢及處理
--以下SQL適用于single instance
----Oracle資料庫操作中，會用到鎖表查詢以及解鎖和kill行程等操作
--(1)鎖表查詢的代碼有以下的形式：
select count(*) from v$locked_object;
select * from v$locked_object;
--(2)查看哪個表被鎖
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--(3)查看是哪個session引起的
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--(4)殺掉對應行程
alter system kill session'587,295';--command下執行，其中587為sid,295為serial#.
--以下SQL適用于RAC和Single instance，查詢完成之后即可kill會話或者直接在服務器上kill行程
select o.owner,
o.object_name,
l.locked_mode,
s.username,
s.sid,
s.serial#,
s.logon_time,
p.spid,
s.inst_id
from gv$locked_object l, dba_objects o, gv$session s, gv$process p
where l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
修改資料庫密碼為永不失效
select * from dba_profiles t where t.PROFILE='DEFAULT' and t.RESOURCE_NAME = 'PASSWORD_LIFE_TIME';
alter profile default limit password_life_time unlimited;
記得幫我點贊哦！
精心整理了計算機各個方向的從入門、進階、實戰的視頻課程和電子書，按照目錄合理分類，總能找到你需要的學習資料，還在等什么？快去關注下載吧！！！
念念不忘，必有回響，小伙伴們幫我點個贊吧，非常感謝，
我是職場亮哥，YY高級軟體工程師、四年作業經驗，拒絕咸魚爭當龍頭的斜杠程式員，
聽我說，進步多，程式人生一把梭
如果有幸能幫到你，請幫我點個【贊】，給個關注，如果能順帶評論給個鼓勵，將不勝感激，
職場亮哥文章串列：更多文章
本人所有文章、回答都與著作權保護平臺有合作，著作權歸職場亮哥所有，未經授權，轉載必究！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/174434.html
標籤：Oracle