江苏龙网

  1. 主页 > 生活百科 > >

Oracle常用语句( 三 )

Oracle


JAVA.sql.BatchUpdateException: ORA-00001: 违反唯一约束条件 (TEST.PK_R_FR_VAT_LOCK) --问题
t_R_FR_VAT_LOCK--表名
select max(length(c_iden)) from t_R_FR_VAT_LOCK --查询约束条件最大几位
select max(c_iden) from t_R_FR_VAT_LOCK where length(c_iden)=6 --查询约束条件最大序列
select
Sequ_r_Fr_Vat_Lock.nextval from dual;--查询约束条件即将插入的序列，如果大于等于约束条件最大序列，就会报唯一性约束条件错误
alter sequence Sequ_r_Fr_Vat_Lock increment by 1000000; --不可以直接修改的，但是可以间接修改 。
select
Sequ_r_Fr_Vat_Lock.nextval from dual;--修改步进的值，然后查询一次，
alter sequence Sequ_r_Fr_Vat_Lock increment by 1; --然后再把步进修改回去，相当于修改了序列的当前值 。
--创建序列
create sequence sequ_d_ysskmtx3_set_temp002
increment by 1 -- 每次加几个
start with 1 -- 从1开始计数
nomaxvalue -- 不设置最大值
nocycle -- 一直累加，不循环
cache 10;
--删除序列
drop sequence sequ_d_ysskmtx3_set_temp002;
 
--查询序列
 
select sequ_d_ysskmtx3_set_temp002.nextval from dual;
--表信息收集语句
begin
dbms_stats.gather_table_stats(ownname => 'NEWGZDB',tabname => 'T_R_FR_ASTSTAT', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all indexed columns',cascade => true, degree => 8);
end;
 
--索引重建:
alter index IDX_R_FR_ASTSTAT rebuild partition PART_2019;
alter index IDX_R_FR_ASTSTAT rebuild partition PART_2020;
 
--全表收集
declare
begin
for cur in (select owner, table_name
from dba_tables
where READ_ONLY = 'NO'
and temporary = 'N'
and iot_name is null
and cluster_name is null
and status = 'VALID'
and tablespace_name not in ('USERS', 'SYSTEM', 'SYSAUX')
and owner='NEWGZDB'
order by 1, 2) loop
dbms_stats.gather_table_stats(ownname => cur.owner,
tabname => cur.table_name,
estimate_percent => 25,
cascade => true,
degree => 4,
force => true);
end loop;
end;
--当前执行sql语句
select a.SID,
a.SERIAL#,
a.USERNAME,
b.PARSE_CALLS,
b.PARSING_SCHEMA_NAME,
b.CPU_TIME/1000000,
b.ELAPSED_TIME/1000000,
b.DISK_READS,
b.DIRECT_WRITES,
b.BUFFER_GETS,
a.event,
b.sql_text,
b.SQL_FULLTEXT
from v$session a inner join v$sqlarea b
on a.SQL_HASH_VALUE = https://www.isolves.com/it/sjk/Oracle/2021-04-22/b. hash_value and b.PARSING_SCHEMA_NAME=upper('smsdb')
--物理读最高sql语句
select a.USERNAME,
a. USER_ID,
b.PARSE_CALLS,
b.PARSING_SCHEMA_NAME,
b.CPU_TIME/1000000,
b.ELAPSED_TIME/1000000,
b.DISK_READS,
b.DIRECT_WRITES,
b.BUFFER_GETS,
b.sql_text,
b.SQL_FULLTEXT
from dba_users a inner join v$sqlarea b
on a.USER_ID = b.PARSING_USER_ID and b.PARSING_SCHEMA_NAME=upper('smsdb') and disk_reads>1000000
--查询前10名执行最多次数SQL语句
select sql_text "SQL语句", executions "执行次数"
from (select sql_text,
executions,
rank() over
 
(order by executions desc) exec_rank
from v$sqlarea)
where exec_rank <= 10;
 
--查询前10名占用CPU最高的SQL语句
select sql_text "SQL语句",
c_t "SQL执行时间（秒）",executions "执行次数",cs "每次执行时间（秒）"　from (select sql_text,
cpu_time /1000000 c_t,executions,ceil(executions/(cpu_time/1000000))cs,
rank() over(order by cpu_time desc) top_time
from v$sqlarea) where top_time <= 10
--查询前10名执行时间最长SQL语句
select sql_text "SQL语句",
c_t "处理时间(秒)",executions "执行次数",cs "每次执行时间（秒）"
from (select sql_text,
ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,
rank() over(order by ELAPSED_TIME desc) top_time
from v$sqlarea) where top_time <= 10
--查询前10名最耗资源SQL语句
select sql_text "SQL语句",
DISK_READS "物理读次数",cs "每次执行时间（秒）"
from (select sql_text,
ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,DISK_READS,
rank() over(order by DISK_READS desc) top_disk
from v$sqlarea) where top_disk <= 10
 
--查询前10名最耗内存SQL语句
select sql_text "SQL语句",


推荐阅读


上一篇：肾炎忌口食物表,记住食物

下一篇：那些惊艳的算法—时间轮算法