SQL-Tools
Table of contents
这里主要放一些实用的工具SQL，按不同的数据库分类。
ORACLE
正在执行的SQL
--正在执行的sql
SELECT A.USERNAME,B.SQL_ID,B.SQL_TEXT,B.SQL_FULLTEXT FROM V$SESSION A,V$SQLAREA B
WHERE A.SQL_ADDRESS=B.ADDRESS AND A.SQL_HASH_VALUE=B.HASH_VALUE;
--正在执行的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
查锁表
--查锁表
SELECT B.OWNER,B.OBJECT_NAME,A.SESSION_ID,A.LOCKED_MODE FROM V$LOCKED_OBJECT A,DBA_OBJECTS B WHERE B.OBJECT_ID = A.OBJECT_ID;
并行加索引,加主键
--并行加索引
CREATE UNIQUE INDEX PK MONTHLYREPORT_TD_ID ONAMS MONTHLYREPORT_TD(ID）TABLESPACE AMS_INDEXPARALLEL 32;
ALTER INDEX PK MONTHLYREPORT_TD_ID NOPARALLEL;
--将索引设置为主键
ALTER TABLE AMS MONTHLYREPORT TD ADD CONSTRAINT PK MONTHLYREPORT TD ID PRIMARY KEY (ID) USING INDEX PK MONTHLYREPORT TD ID;
重建序列
SELECT 'DROP SEQUENCE'||SEQUENCE_NAME|| '; CREATE SEQUENCE ' || SEQUENCE_NAME || ' MINVALUE ' || MIN_VALUE||' MAXVALUE '|| MAX_VALUE
||' START WITH ' || LAST_NUMBER|| ' INCREMENT BY ' ||INCREMENT_BY||
(CASE WHEN CACHE_SIZE= 0 THEN ' NOCACHE ' ELSE ' CACHE ' || CACHE_SIZE END)
|| (CASE WHEN CYCLE_FLAG ='Y' THEN ' CYCLE' ELSE '' END) || ';'
FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='AMS';
生成 INSERT INTO SELECT 语句
-- 生成INSERT INTO SELECT 语句
SELECT 'INSERT INTO'||X.TABLE_NAME ||'_ZZY（'|| COLUMN_NAMES||'）SELECT'|| COLUMN_NAMES ||'FROM'||X.TABLE_NAME AS MY_SQL
FROM(
SELECT T.TABLE_NAME, LISTAGG(T.COLUMN_NAME,',')WITHIN GROUP (ORDER BY T.COLUMN_ID) AS COLUMN_NAMES
FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME in ('MM_DEFAULTSET_TC')
GROUP BY T.TABLE_NAME) X;
生成两张表比较内容差异的查询语句
--快速生成SELECT列拼接语句，比较内容的时候要自己手工加 ORDER BY字段
SELECT 'SELECT ' || COLUMN_NAMES || ' FROM ' || X.TABLE_NAME AS MY_SQL
FROM (SELECT T.TABLE_NAME, LISTAGG(T.COLUMN_NAME, '||'',''||') WITHIN GROUP (ORDER BY T.COLUMN_ID) AS COLUMN_NAMES
FROM USER_TAB_COLUMNS T WHERE 1=1 AND T.TABLE_NAME in ('MM_DEFAULTSET_TC')
GROUP BY T.TABLE_NAME) X;
查询逻辑读大于50M的SQL
--查询逻辑读大于50M
SELECT *
FROM (SELECT SQL_ID,
SQL_FULLTEXT,
CASE
WHEN EXECUTIONS = 0 THEN (BUFFER_GETS * 8 / 1024)
ELSE (BUFFER_GETS / EXECUTIONS * 8 / 1024) END MB,
PARSING_SCHEMA_NAME,
SERVICE,
MODULE,
ACTION,
OBJECT_STATUS
FROM V$SQL
WHERE FIRST_LOAD_TIME > '2024-08-25/00:00:00'
AND FIRST_LOAD_TIME < '2024-09-26/00:00:00'
AND SERVICE = 'AMS')
WHERE MB > 50
ORDER BY MB DESC;
查全表+表存储
--可同时查询表-索引的存储大小
SELECT M.TABLE_NAME,
CASE WHEN EXISTS(SELECT 1 FROM DBA_TAB_PARTITIONS A WHERE A.TABLE_NAME = M.TABLE_NAME)
THEN 'PATITON'
ELSE 'F' END AS TABLE_PT,
TABLE_GB,
INDEX_GB,
TABLE_GB + INDEX_GB AS TOTAL_GB
FROM (
--行转列START
SELECT TABLE_NAME, TABLE_GB, INDEX_GB
FROM (
--可查单表START
SELECT O.TABLE_NAME, O.SEGMENT_TYPE, ROUND(SUM(GB), 4) AS OGB
FROM (
SELECT CASE WHEN INSTR(T.SEGMENT_TYPE, 'TABLE') > 0 THEN T.SEGMENT_NAME
ELSE (SELECT DISTINCT I.TABLE_NAME FROM DBA_INDEXES I WHERE I.INDEX_NAME = T.SEGMENT_NAME) END AS TABLE_NAME,
CASE WHEN INSTR(T.SEGMENT_TYPE, 'TABLE') > 0 THEN 'TABLE' ELSE 'INDEX' END AS SEGMENT_TYPE,
T.BYTES / 1024 / 1024 / 1024 AS GB
FROM DBA_SEGMENTS T WHERE T.OWNER = 'BVIS'
AND T.SEGMENT_NAME NOT LIKE 'BIN%'
AND T.SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION', 'INDEX', 'INDEXPARTITION',
'INDEXSUBPARTITION')
) O
WHERE 1 = 1 --AND O.TABLE_NAME = 'T_TEST_SOURCE'
GROUP BY O.TABLE_NAME, O.SEGMENT_TYPE
ORDER BY O.TABLE_NAME, O.SEGMENT_TYPE DESC
--可查单表END
) X PIVOT (SUM(OGB) FOR SEGMENT_TYPE IN ('TABLE' AS TABLE_GB,'INDEX' AS INDEX_GB))
--行转列END
) M
WHERE M.TABLE_GB > 1;
查数据库（表）的水位
--查表的水位
SELECT OWNER,
TABLE_NAME,
ROUND(BLOCKS * 8192 / 1024 / 1024, 2) "TOTAL_SIZE(M)",
ROUND(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024, 2) "USED_SIZE(M)",
ROUND(((BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024)), 2) "WASTED _SIZE(M) ",
ROUND(ROUND(((BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024)), 2) /
ROUND(BLOCKS * 81922 / .1024 / 1024, 2), 2) * 100 || '%' WASTED_PERCENT,
LAST_ANALYZED,
NUM_ROWS
FROM DBA_TABLES
WHERE ROUND(BLOCKS * 8192 / 1024 / 1024, 2) > 1000
AND ROUND(ROUND(((BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024)),
2) / ROUND(BLOCKS * 8192 / 1024 / 1024, 2), 2) * 100 > 1
AND ROUND(BLOCKS * 8192 / 1024 / 1024, 2) > 10
--AND TABLE NAME='T CRMS POL EXPIRED TMP3'
ORDER BY 6 DESC;
查分区表相关信息
SELECT P.TABLE_NAME,
P.PARTITIONING_TYPE AS PART_TYPE,
K.COLUMN_NAME AS PART_COLUMN_NAME,
P.SUBPARTITIONING_TYPE AS SUBPART_TYPE,
SK.COLUMN_NAME AS SUBPART_COLUMN_NAME,
P.PARTITION_COUNT AS PARTITION_COUNT
FROM USER_PART_TABLES P
INNER JOIN USER_PART_KEY_COLUMNS K ON P.TABLE_NAME = k.name
LEFT JOIN USER_SUBPART_KEY_COLUMNS SK ON K.NAME = SK.NAME
WHERE 1 = 1 -- AND X.OWNER=VOWNER
AND P.TABLE_NAME NOT LIKE 'BIN'
ORDER BY P.PARTITIONING_TYPE, P.TABLE_NAME;
OB 查询
查询Leader 节点分布
--查询LEADER节点的分布情况
SELECT C.SVR_IP,C.ROLE,C.STATUS,COUNT(*)
FROM OCEANBASE._ALL_VIRTUAL_CLOG_STAT C, OCEANBASE._ALL_VIRTUAL_META_TABLE MT
WHERE C.TABLE_ID=MT.TABLE_ID AND MT.TENANT_ID=1004
GROUP BY C.SVR_IP,C.ROLE,C.STATUS;
快速生成 create-table ddl
SELECT 'CREATE TABLE ' || T.TABLE_NAME || ' (' FROM USER_TABLES T WHERE T.TABLE_NAME = 'MM_CLAIM_TD'
UNION ALL
SELECT C.COLUMN_NAME || ' ' || C.DATA_TYPE || ' ' || C.NULLABLE || ' DEFAULT ' || C.DATA_DEFAULT || ','
FROM (
SELECT TC.TABLE_NAME,
TC.COLUMN_NAME,
CASE
WHEN TC.DATA_TYPE = 'DATE' THEN TC.DATA_TYPE
WHEN TC.DATA_TYPE = 'NUMBER' THEN (CASE
WHEN TC.DATA_PRECISION IS NOT NULL
THEN TC.DATA_TYPE || '(' || TC.DATA_PRECISION || ',' || TC.DATA_SCALE || ')'
ELSE TC.DATA_TYPE END)
WHEN TC.CHARACTER_SET_NAME IS NOT NULL THEN TC.DATA_TYPE || '(' || TC.CHAR_LENGTH || ')'
ELSE TC.DATA_TYPE || '(' || TC.DATA_LENGTH || ')' END DATA_TYPE,
CASE TC.NULLABLE WHEN 'N' THEN ' NOT NULL ' END NULLABLE,
TC.DATA_DEFAULT
FROM USER_TAB_COLUMNS TC
WHERE TC.TABLE_NAME = 'MM_CLAIM_TD'
ORDER BY TC.TABLE_NAME, TC.COLUMN_ID) C
UNION ALL
SELECT 'CONSTRAINT ' || P.CONSTRAINT_NAME || ' PRIMARY KEY (' || P.COLUMN_NAME || ')'
FROM (
SELECT CU.TABLE_NAME, CU.CONSTRAINT_NAME,
LISTAGG(CU.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY CU.TABLE_NAME,CU.COLUMN_NAME) AS COLUMN_NAME
FROM USER_CONS_COLUMNS CU
INNER JOIN USER_CONSTRAINTS AU ON CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME
WHERE CU.OWNER = 'AMS'
AND AU.CONSTRAINT_TYPE = 'P'
AND CU.TABLE_NAME = 'AMS_ACCOUNTIMPDATA_DETAIL_TD'
GROUP BY CU.TABLE_NAME, CU.CONSTRAINT_NAME) P
UNION ALL
SELECT ')' FROM DUAL
UNION ALL
SELECT 'PARTITION BY LIST (' || PK.COLUMN_NAME || ') '
FROM (
SELECT PK.NAME, PK.COLUMN_NAME, PK.OBJECT_TYPE, PK.COLUMN_POSITION
FROM USER_PART_KEY_COLUMNS PK
WHERE PK.NAME = 'MM_CLAIM_TD') PK
UNION ALL
SELECT CASE P.PARTITION_NAME
WHEN 'SUBCOMPANY_0' THEN '(PARTITION ' || P.PARTITION_NAME || ' VALUES (' || P.HIGH_VALUE || '),'
WHEN 'SUBCOMPANY_WU' THEN 'PARTITION ' || P.PARTITION_NAME || ' VALUES (' || P.HIGH_VALUE || '));'
ELSE 'PARTITION ' || P.PARTITION_NAME || ' VALUES (' || P.HIGH_VALUE || ')' END
FROM (
SELECT PT.TABLE_NAME, PT.PARTITION_NAME, PT.HIGH_VALUE, PT.PARTITION_POSITION
FROM USER_TAB_PARTITIONS PT
WHERE PT.TABLE_NAME = 'MM_CLAIM_TD'
ORDER BY PT.PARTITION_POSITION) P
UNION ALL
SELECT 'COMMENT ON TABLE ' || T.TABLE_NAME || ' IS ''' || T.COMMENTS || ''';'
FROM USER_TAB_COMMENTS T
WHERE T.TABLE_NAME = 'MM_CLAIM_TD'
UNION ALL
SELECT 'COMMENT ON COLUMN ' || CC.TABLE_NAME || '.' || CC.COLUMN_NAME || ' IS ''' || CC.COMMENTS || ''';'
FROM (
SELECT T.TABLE_NAME, T.COLUMN_NAME, C.COMMENTS
FROM USER_TAB_COLUMNS T
INNER JOIN USER_COL_COMMENTS C ON T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME
WHERE T.TABLE_NAME = 'MM_CLAIM_TD'
ORDER BY T.COLUMN_ID
) CC