--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;

?

--2、oracle是否開啟自動擴(kuò)展?

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;

?

--3、oracle中查看表是否被鎖定:

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、殺死進(jìn)程語句：

--alter system kill session 'sid, serial#'

alter system kill session '2838, 58765'; --(sid, srial#)?

?

--5、Oracle遞歸查詢(樹型查詢)

--prior放的左右位置決定了檢索是自底向上還是自頂向下. 左邊是自上而下（找子節(jié)點(diǎn)），右邊是自下而上（找父節(jié)點(diǎn)）

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';?

--查找死循環(huán)

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、同一字段用逗號分隔(列轉(zhuǎn)行)：?

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、復(fù)制表結(jié)構(gòu)及數(shù)據(jù)

create table_name as select * from Source_table where 1=1;

復(fù)制表結(jié)構(gòu)?

create table_name as select * from Source_table where 1 <> 1;

?

--9、oracle 排序: dense_rank() over(order by t.df desc)?

?

--10、根據(jù)表名查詢過程名

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正在執(zhí)行的sql語句及執(zhí)行該語句的用戶

SELECT b.sid oracleID, ?

? ? ? ?b.username Oracle用戶, ?

? ? ? ?b.serial#, ?

? ? ? ?spid 操作系統(tǒng)ID, ?

? ? ? ?paddr, ?

? ? ? ?sql_text 正在執(zhí)行的SQL, ?

? ? ? ?b.machine 計(jì)算機(jī)名 ?

FROM v$process a, v$session b, v$sqlarea c ?

WHERE a.addr = b.paddr ?

? ?AND b.sql_hash_value = c.hash_value;

? ?

--12、查看正在執(zhí)行sql的發(fā)起者的發(fā)放程序

SELECT A.serial#,OSUSER 電腦登錄身份,

? ? ? ?PROGRAM 發(fā)起請求的程序, ?

? ? ? ?USERNAME 登錄系統(tǒng)的用戶名, ?

? ? ? ?SCHEMANAME, ?

? ? ? ?B.Cpu_Time 花費(fèi)cpu的時間, ?

? ? ? ?STATUS, ?

? ? ? ?B.SQL_TEXT 執(zhí)行的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當(dāng)前的被鎖對象

SELECT l.session_id sid, ?

? ? ? ?s.serial#, ?

? ? ? ?l.locked_mode 鎖模式, ?

? ? ? ?l.oracle_username 登錄用戶, ?

? ? ? ?l.os_user_name 登錄機(jī)器用戶名, ?

? ? ? ?s.machine 機(jī)器名, ?

? ? ? ?s.terminal 終端用戶名, ?

? ? ? ?o.object_name 被鎖對象名, ?

? ? ? ?s.logon_time 登錄數(shù)據(jù)庫時間 ?

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 操作系統(tǒng)ID, ?

? paddr, ?

? sql_text 正在執(zhí)行的SQL, ?

? b.machine 計(jì)算機(jī)名 ?

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、查看臨時表分區(qū)

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、查詢表的字段個數(shù)

select count(1) from user_col_comments where table_name = upper( 'tbl_sys_user');

?

--20、字段用逗號隔開轉(zhuǎn)成一行

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、刪除重復(fù)數(shù)據(jù)

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、分區(qū)、子分區(qū)信息

SELECT * FROM USER_TAB_PARTITIONS;

SELECT * FROM USER_TAB_SUBPARTITIONS;?

?

--23、存在2017年分區(qū)但不存在2018年分區(qū)

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 博客 ，全文地址請點(diǎn)擊：https://blog.csdn.net/xulei_19850322/article/details/4233445?utm_source=copy?

?

?

TBL???詳細(xì)X 網(wǎng)絡(luò)釋義 TBL: 總膽紅素 TBL TSP: 表示法六 ARP tbl: 動態(tài)ARP表信息

轉(zhuǎn)載于:https://www.cnblogs.com/ViokingJava/p/9453039.html

總結(jié)

以上是生活随笔為你收集整理的Oracle常用sql（持续更新）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。