Oracle数据开发常用SQL

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）创建位图索引的语法比较特殊，需要留意。