标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,607)
- DB2 (22)
- MySQL (71)
- Oracle (1,471)
- Data Guard (50)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle 23ai (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (534)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- 收集统计信息报ORA-00600 16515问题解决
- rm -rf误删Oracle数据库恢复
- 分布式存储故障导致数据库无法启动故障处理
- read_me_recover_tn勒索恢复
- WINDOWS 下用dg broker搭建ADG(单机to单机)
- 存储故障后oracle报—ORA-01122/ORA-01207故障处理
- Oracle 23ai rm redo*.log恢复
- Oracle 发布计划—包含Oracle 23ai版本
- Oracle 23ai 变化之—-默认数据文件变为bigfile
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
月归档:二月 2011
oracle 中如何定位重要(消耗资源多)的SQL
1、查看值得怀疑的SQL
select substr(to_char(s.pct,'99.00'),2)||'%'load, s.executions executes, p.sql_text from(select address, disk_reads, executions, pct, rank()over(order by disk_reads desc) ranking from(select address, disk_reads, executions, 100*ratio_to_report(disk_reads)over() pct from sys.v_$sql where command_type!=47) where disk_reads>50*executions) s, sys.v_$sqltext p where s.ranking<=5 and p.address=s.address order by 1, s.address, p.piece;
2、查看消耗内存多的sql
select b.username, a. buffer_gets, a.executions, a.disk_reads / decode(a.executions, 0, 1, a.executions), a.sql_text SQL from v$sqlarea a, dba_users b where a.parsing_user_id = b.user_id and a.disk_reads > 10000 order by disk_reads desc;
3、查看逻辑读多的SQL
select* from(select buffer_gets, sql_text from v$sqlarea where buffer_gets>500000 order by buffer_gets desc) where rownum<=30;
4、查看执行次数多的SQL
select sql_text, executions from (select sql_text, executions from v$sqlarea order by executions desc) where rownum < 81;
5、查看读硬盘多的SQL
select sql_text, disk_reads from(select sql_text, disk_reads from v$sqlarea order by disk_reads desc) where rownum<21;
6、查看排序多的SQL
select sql_text, sorts from(select sql_text, sorts from v$sqlarea order by sorts desc) where rownum<21;
7、分析的次数太多,执行的次数太少,要用绑变量的方法来写sql
select substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs" from v$sqlarea where executions < 5 group by substr(sql_text, 1, 80) having count(*) > 30 order by 2;
发表在 Oracle性能优化
评论关闭
全角,半角互换
对于全角和半角互换,oracle 提供了两个函数to_multi_byte和to_single_byte函数
select to_multi_byte('1234') from dual; TO_MULTI_BYTE('1234') --------------------- 1234 select to_single_byte('1234') from dual; TO_SINGLE_BYTE('1234') -------------------------- 1234
发表在 Oracle 开发
评论关闭
在sqlplus中操作blob和clob
--create directory create directory ULTLOBDIR as 'd:' --create table create table blobtest(col1 BLOB); create table clobtest(col1 cLOB); --insert BLOB declare a_blob BLOB; bfile_name BFILE := BFILENAME('ULTLOBDIR','teslob.doc'); begin insert into blobtest values (empty_blob()) returning col1 into a_blob; dbms_lob.fileopen(bfile_name); dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name)); dbms_lob.fileclose(bfile_name); commit; end; --update BLOB declare a_blob BLOB; bfile_name BFILE := BFILENAME('ULTLOBDIR','log.txt'); begin update blobtest set col1=empty_blob() where rownum=1 returning col1 into a_blob; dbms_lob.fileopen(bfile_name); dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name)); dbms_lob.fileclose(bfile_name); commit; end; --insert CLOB declare a_clob CLOB; bfile_name BFILE := BFILENAME('ULTLOBDIR','teslob.doc'); begin insert into clobtest values (empty_clob()) returning col1 into a_clob; dbms_lob.fileopen(bfile_name); dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name)); dbms_lob.fileclose(bfile_name); commit; end; --update CLOB declare a_clob CLOB; bfile_name BFILE := BFILENAME('ULTLOBDIR','log.txt'); begin update clobtest set col1=empty_clob() where rownum=1 returning col1 into a_clob; dbms_lob.fileopen(bfile_name); dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name)); dbms_lob.fileclose(bfile_name); commit; end; --查询是否成功 select dbms_lob.getlength(col1) from blobtest; select dbms_lob.getlength(col1) from clobtest;
发表在 Oracle 开发
评论关闭