Oracle常用sql（持续更新）
--1、oracle查看表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M) ",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M) ",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M) ",
F.MAX_BYTES "最大块(M) "
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
SELECT T.TABLESPACE_NAME,
D.FILE_NAME,
D.AUTOEXTENSIBLE,
D.BYTES,
D.MAXBYTES,
D.STATUS
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;
SELECT s.USERNAME,s.STATUS, object_name, machine, s.sid, s.serial#
from gv$locked_object i, dba_objects o, gv$session s
where i.object_id = o.object_id
and i.session_id = s.sid;
--4、杀死进程语句：
--alter system kill session 'sid, serial#'
alter system kill session '2838, 58765'; --(sid, srial#)
--5、Oracle递归查询(树型查询)
--prior放的左右位置决定了检索是自底向上还是自顶向下. 左边是自上而下（找子节点），右边是自下而上（找父节点）
SELECT T.AREA_CODE, T.AREA_NAME, T.AREA_LEVEL
FROM TBL_SYS_AREA_CODE T
CONNECT BY PRIOR T.AREA_CODE = T.PARENT_AREA_CODE
START WITH T.PARENT_AREA_CODE = 'xxxx';
SELECT T.AREA_CODE, T.AREA_NAME, T.AREA_LEVEL
FROM TBL_SYS_AREA_CODE T
CONNECT BY T.AREA_CODE = PRIOR T.PARENT_AREA_CODE
START WITH T.Area_Name = 'xxxx';
--查找死循环
select * from table_t t where t.id=t.p_id;
select *
from (select id, p_id, CONNECT_BY_ISCYCLE CYCLED
from table_t
connect by nocycle prior id = p_id
start with id = 'xxx') a
where a.CYCLED > 0;
--6、同一字段用逗号分隔(列转行)：
SELECT LISTAGG(CASE
WHEN FUNC_CHECKDISABILITYCARD(S.AAD003) = 0 THEN
S.AAD003
END) WITHIN GROUP(ORDER BY S.AHH002) AS C022_DESC
FROM AA01_2014 T, AH02_2014 S
WHERE T.STATUS <> 0
AND S.STATUS <> 0
AND T.AAA001 = S.AAA001
AND T.AAH009 <> '-1'
AND T.BASENUM_FLAG = '2016'
AND T.AAH009 IS NOT NULL
GROUP BY S.AAA001;
--7、mysql逗号分隔
select Group_concat(id SEPARATOR ',') from tT;
--8、复制表结构及数据
create table_name as select * from Source_table where 1=1;
复制表结构
create table_name as select * from Source_table where 1 <> 1;
--9、oracle 排序: dense_rank() over(order by t.df desc)
--10、根据表名查询过程名
SELECT name,
type,
referenced_owner,
referenced_name,
referenced_type
FROM user_dependencies
WHERE referenced_name = upper('tb_poor_tpysdf')
AND type = 'PROCEDURE';
SELECT DISTINCT NAME
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND UPPER(TEXT) LIKE UPPER('%insert into TBL_DA_POOR_VILLAGE_INFO%');
--11、查询Oracle正在执行的sql语句及执行该语句的用户
SELECT b.sid oracleID,
b.username Oracle用户,
b.serial#,
spid 操作系统ID,
paddr,
sql_text 正在执行的SQL,
b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value;
--12、查看正在执行sql的发起者的发放程序
SELECT A.serial#,OSUSER 电脑登录身份,
PROGRAM 发起请求的程序,
USERNAME 登录系统的用户名,
SCHEMANAME,
B.Cpu_Time 花费cpu的时间,
STATUS,
B.SQL_TEXT 执行的sql
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC
--13、查出oracle当前的被锁对象
SELECT l.session_id sid,
s.serial#,
l.locked_mode 锁模式,
l.oracle_username 登录用户,
l.os_user_name 登录机器用户名,
s.machine 机器名,
s.terminal 终端用户名,
o.object_name 被锁对象名,
s.logon_time 登录数据库时间
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial#;
--14、
SELECT b.sid oracleID,
b.username 登录Oracle用户名,
b.serial#,
spid 操作系统ID,
paddr,
sql_text 正在执行的SQL,
b.machine 计算机名
FROM v$process a,
v$session b,
v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value;
--15、查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
--16、查看临时表空间
select *
from (Select a.tablespace_name,
to_char(a.bytes / 1024 / 1024, '99,999.999') total_bytes,
to_char(b.bytes / 1024 / 1024, '99,999.999') free_bytes,
to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
'99,999.999') use_bytes,
to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' use
from (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) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes,
to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes,
to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,
to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
from (select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) c,
(select tablespace_name, sum(bytes_cached) bytes_used
from v$temp_extent_pool
group by tablespace_name) d
where c.tablespace_name = d.tablespace_name)
order by tablespace_name ;
--17、表空间大小以及使用率
SELECT A.TABLESPACE_NAME "表空间名",
ROUND(TOTAL / 1024 / 1024 / 1024, 4) 表空间大小GB,
ROUND(FREE / 1024 / 1024 / 1024, 4) 表空间剩余大小GB,
ROUND((TOTAL - FREE) / 1024 / 1024 / 1024, 4) 表空间使用大小GB,
ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 5 DESC;
--18、查看临时表分区
SELECT *
FROM (SELECT USERNAME,
SESSION_ADDR,
SQL_ID,
CONTENTS,
SEGTYPE,
BLOCKS * 8 / 1024 / 1024 GB
FROM V$SORT_USAGE
ORDER BY BLOCKS DESC)
WHERE ROWNUM <= 200;
--19、查询表的字段个数
select count(1) from user_col_comments where table_name = upper( 'tbl_sys_user');
--20、字段用逗号隔开转成一行
SELECT LISTAGG(COLUMN_NAME, ',') WITHIN GROUP(ORDER BY COLUMN_ID) AS COLUMN_ORDER
FROM DBA_TAB_COLUMNS
WHERE OWNER = UPPER('hndajzfp')
AND TABLE_NAME = UPPER('tbl_fpxm_poor_jyfp')
ORDER BY COLUMN_ID;
--20、删除重复数据
UPDATE TBL_POOR_FAMILY_SIGN S
SET S.STATE = '0'
WHERE (S.USER_ID, SUBSTR(S.CREATE_TIME, 0, 10)) IN
(SELECT T.USER_ID, SUBSTR(T.CREATE_TIME, 0, 10)
FROM TBL_POOR_FAMILY_SIGN T
WHERE T.CREATE_TIME IS NOT NULL
AND T.STATE != '0'
GROUP BY SUBSTR(T.CREATE_TIME, 0, 10), T.USER_ID
HAVING COUNT(T.USER_ID) > 1)
AND ROWID NOT IN (SELECT MIN(ROWID)
FROM TBL_POOR_FAMILY_SIGN T
WHERE T.CREATE_TIME IS NOT NULL
AND T.STATE != '0'
GROUP BY SUBSTR(T.CREATE_TIME, 0, 10), T.USER_ID
HAVING COUNT(T.USER_ID) > 1);
--21、回车符换行符替换为空
SELECT translate(t.xjbfdw,chr(13)||chr(10),',') FROM tbl_hyfp_wsfp t
--22、分区、子分区信息
SELECT * FROM USER_TAB_PARTITIONS;
SELECT * FROM USER_TAB_SUBPARTITIONS;
--23、存在2017年分区但不存在2018年分区
SELECT S.TABLE_NAME
FROM (SELECT MAX(INSTR(T.PARTITION_NAME, '2018')) EXISTPAR2018,
MAX(INSTR(T.PARTITION_NAME, '2017')) EXISTPAR2017,
T.TABLE_NAME
FROM USER_TAB_PARTITIONS T
GROUP BY T.TABLE_NAME) S
WHERE S.EXISTPAR2018 = 0
AND EXISTPAR2017 > 0;
--24、逗号分割
select regexp_substr(t.BFRY_RY_TYPE, '[^,]+', 1, rownum)
from TBL_JCXX_BFRY t
where t.BFRY_PHONE = '18740760464'
connect by rownum <= length(regexp_replace(t.BFRY_RY_TYPE, '[^,]', null));
--25、字符串相似度
select *
from (select
s.USER_TYPE,
t.BFRY_RY_TYPE,
SYS.UTL_MATCH.edit_distance_similarity(s.USER_ID,t.BFRY_RY_TYPE) 相似度
from TBL_JCXX_BFRY t,TBL_SYS_USER s where t.BFRY_RY_TYPE=s.USER_ID)
order by 相似度 desc;
--26、interval 用法
select sysdate - interval '10' day as "10天前",
sysdate - interval '10' hour as "10小时前",
sysdate - interval '10' minute as "10分钟前",
sysdate - interval '10' second as "10秒钟前",
sysdate - 10 as "10天前",
sysdate - 10 / 24 as "10小时前",
sysdate - 10 / (24 * 60) as "10分钟前",
sysdate - 10 / (24 * 3600) as "10秒钟前"
from dual; --------------------- 本文来自 xulei_19850322 的CSDN 博客 ，全文地址请点击：https://blog.csdn.net/xulei_19850322/article/details/4233445?utm_source=copy
