1. 查看锁表语句及删除会话进程
在进行Oracle开发时,经常需要查询某些表是否被锁死了,这时候就需要通过语句查询了~~~
查看锁表sql:
SELECT s.SID
,s.SERIAL#
,s.USERNAME 数据库用户名
,b.OS_USER_NAME 操作用户名
,q.SQL_TEXT SQL内容
FROM v$session s
join v$locked_object b on s.SID = b.SESSION_ID
join v$sql q on s.SQL_ID = q.SQL_ID
;
删除会话进程:
alter system kill session ‘SID,SERIAL#’;
2. 查看临时表空间状态
SELECT TABLESPACE_NAME AS TABLESPACE_NAME --临时表空间名称
,FILE_NAME AS FILE_NAME --文件名
,BLOCKS AS BLOCKS --临时表空间文件包含的BLOCK数量,一个BLOCK大小为:8192bytes(即8KB)
,BLOCKS*8/1024/1024 AS "FILE_SIZE(G)" --用BLOCK数据换算文件大小
,STATUS AS STATUS --状态(OFFLINE|ONLINE)
,AUTOEXTENSIBLE AS AUTOEXTENSIBLE
,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024,
MAXBYTES/1024/1024/1024)
AS "MAX_SIZE(G)"
,INCREMENT_BY AS "INCREMENT_BY" --增量
,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE"
FROM DBA_TEMP_FILES;
3.查找消耗临时表空间资源比较多的SQL
SELECT se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text,
p.VALUE
FROM v$sort_usage su
join v$parameter p on p.name = 'db_block_size'
join v$session se on su.session_addr = se.saddr
join v$sql s on s.hash_value = su.sqlhash and s.address = su.sqladdr
ORDER BY se.username, se.sid
;
4.查看表空间使用情况
SELECT A.TABLESPACE_NAME
,ROUND(A.max_space,2) "MAX_SPACE(GB)"
,ROUND(B.free_space,2) "FREE_SPACE(GB)"
,ROUND(A.max_space-B.free_space,2) "USED_SPACE(GB)"
,ROUND(1-B.free_space/A.max_space,4)*100||'%' USED_RATE
FROM (SELECT d.TABLESPACE_NAME,sum(d.BYTES)/1024/1024/1024 max_space
FROM dba_data_files d
group by d.TABLESPACE_NAME
) A
LEFT JOIN (SELECT f.TABLESPACE_NAME,sum(f.BYTES)/1024/1024/1024 free_space
FROM dba_free_space f
group by f.TABLESPACE_NAME
) B ON A.TABLESPACE_NAME = B.TABLESPACE_NAME
;
5.索引
1)位图索引:
CREATE BITMAP INDEX INDEX_NAME ON TABLE_NAME(COLUMN);
2)分区表本地索引(数据应用一般只看某个或者某几个分区的数据,创建本地索引速度很快):
CREATE INDEX INDEX_NAME ON TABLE_NAME(COL1,COL2,…) LOCAL;
查看分区索引信息:
select *
From user_part_indexes
where index_name = 'IDX_ALG_CPV_LOCAL';
3)删除索引:
DROP INDEX INDEX_NAME;
注:a.分区上的位图索引只能为LOCAL索引,不能为GLOBAL全局索引。
b.在创建索引时如果不显式指定global或local,则默认是global。
4)并行创建索引:
CREATE INDEX INDEX_NAME on TABLE_NAME ( COL1,COL2,...) LOCAL PARALLEL 16; --本地索引
ALTER INDEX INDEX_NAME NOPARALLEL; --索引建完之后要取消并行 否则会容易埋坑
6.收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => '数据库用户名',
tabname => '表名',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE
);
END;
7.从Oracle迁移到MySQL:批量创建索引
背景:数据仓库用的是Oracle,但是项目的数据库是MySQL8的,开始做数据迁移 时没有同步索引,所以需要将索引从Oracle迁移到MySQL8,但是两种数据库的语法有差异,在Oracle也找不到建索引的字典,只能根据需求拼接了。
SELECT B.INDEX_NAME,B.TABLE_NAME,B.COLUMN_NAME,B.INDEX_TYPE,B.UNIQUENESS
,CASE WHEN B.UNIQUENESS = 'UNIQUE' THEN 'CREATE UNIQUE INDEX '||B.INDEX_NAME||' ON '||B.TABLE_NAME||'('||B.COLUMN_NAME||');'
WHEN B.INDEX_TYPE = 'BITMAP' THEN 'CREATE INDEX '||B.INDEX_NAME||' ON ' ||B.TABLE_NAME||'('||B.COLUMN_NAME||') USING BTREE;'
WHEN B.INDEX_TYPE = 'NORMAL' THEN 'CREATE INDEX '||B.INDEX_NAME||' ON '||B.TABLE_NAME||'('||B.COLUMN_NAME||');'
ELSE NULL END INDEX_SQL
FROM (SELECT A.INDEX_NAME,A.TABLE_NAME,A.INDEX_TYPE,A.UNIQUENESS,TO_CHAR(WM_CONCAT(A.COLUMN_NAME)) COLUMN_NAME
FROM (
SELECT T.INDEX_NAME,T.TABLE_NAME,T.COLUMN_NAME,T.COLUMN_POSITION,T2.INDEX_TYPE,T2.UNIQUENESS
FROM USER_IND_COLUMNS T
JOIN DM_WAYS2GW_TABLES T1 ON T.TABLE_NAME = T1.TABLENAME AND T1.SOURCE = 2
LEFT JOIN USER_INDEXES T2 ON T.INDEX_NAME = T2.INDEX_NAME
ORDER BY T.TABLE_NAME,T.INDEX_NAME,T.COLUMN_POSITION
) A
GROUP BY A.INDEX_NAME,A.TABLE_NAME,A.INDEX_TYPE,A.UNIQUENESS
) B
;
其中:
(1)DM_WAYS2GW_TABLES保存的是需要同步的表,可根据需求自行调整。
(2)创建位图索引的语法比较特殊,需要留意。