ORACLE 常用SQL汇总

1.排序

2.日期

3.函数

4.慢sql（需要admin权限）

5.存储

6.锁

1.排序

默认排序，rownum，order by

1.oracle中的默认排序（SQL语句不带order by功能）是按照存储结构排序的，即使你是自增主键，按时间顺序先后插入，但是查出来的结果不一定依次递增。

2.rownum是根据where条件搜索出来的虚列，如果加上order by会有问题。如下sql加上rownum<3的条件，会导致搜索出来的结果不是按照排序的前3条

3.但是可以这样使用rownum和order by，外面多套一层

select * from(
select rownum,a.* from T_CREDIT_DATA_SOURCE_PERSON_TYPE a order by INTERFACE_CODE asc
) where rownum < 3;

2.日期

数据类型Date

TO_CHAR(ENDTIME,'yyyy-mm-dd hh24:mi:ss')

to_timestamp('2022-02-27 00:00:00','YYYY-MM-DD HH24:mi:ss')

日期直接相加减，返回结果是以天为单位的number类型

也可以直接比较，比如created_dt >=sysdate-1，表示一天前

3.函数

regexp_replace：内容替换
		SELECT regexp_replace('01234abcde56789','[0-9]','#') AS new_str FROM dual;
		结果：#####abcde#####
		
		
case_when:
partition:
COALESCE:返回第一个非null值，COALESCE(COUNT(CASE WHEN DEFAULT_NUM>=1 THEN PD01_ID END),0)
NVL：与coalesce类似

4.慢sql

执行时间最慢的50条sql语句

select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;

5.存储

表空间的使用率

select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%"
from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name

6.锁

select*from v$locked_object a,dba_objects b where b.object_id=a.object_id;//锁表
select * FROM dba_ddl_locks where name =upper('SP_SY_WMSQQD_CHECK');//锁对象
select object_id,session_id,locked_mode from v$locked_object;//锁行