ORACLE常用SQL总结

ORACLE常用SQL总结

1、统计信息

1. 收集统计信息

   dbms_stats.gather_schema_stats('LDS');
   

2. 查看单个表的统计信息情况

    SELECT T.OWNER,T.TABLE_NAME,T.LAST_ANALYZED,T.NUM_ROWS 
      FROM ALL_TABLES T 
     WHERE T.OWNER = 'LDS' 
       AND T.TABLE_NAME LIKE '%S052%' 
   ORDER BY T.NUM_ROWS DESC;
   

3. 统计信息自动收集情况

   SELECT * FROM DBA_SCHEDULER_WINDOWS;
   

2、获取oracle数据字典

select a.owner,b.comments,a.Table_Name ,c.comments,a.column_name
      ,replace(d.constraint_type,'P','Y') PK
       ,case when a.DATA_TYPE = 'DATE' or a.DATA_TYPE = 'FLOAT' or a.DATA_TYPE = 'CLOB' or a.DATA_TYPE = 'TIMESTAMP(6)' then a.DATA_TYPE 
             when a.DATA_TYPE = 'NUMBER' AND a.data_scale <> 0 then a.DATA_TYPE || '(' || a.data_precision ||  ',' || a.data_scale || ')'
             when a.DATA_TYPE = 'NUMBER' AND a.data_scale = 0 then a.DATA_TYPE || '(' || a.data_precision || ')'
             when a.DATA_TYPE = 'VARCHAR2' AND a.char_used = 'C' then a.DATA_TYPE || '(' || a.char_length || ' CHAR)'            
             else a.data_type || '(' || a.data_length || ')'
       end DATA_TYPE
from ALL_TAB_COLS a 
left join ALL_TAB_COMMENTS b 
   on a.owner=b.owner 
   and a.table_name=b.table_name
left join all_col_comments c 
   on a.owner=c.owner 
   and a.table_name=c.table_name 
   and a.column_name=c.column_name
left join (
     select tt1.owner,tt1.constraint_name,tt1.table_name,tt1.CONSTRAINT_TYPE,tt2.column_name
     from all_constraints tt1
     left join all_cons_columns tt2
       on tt1.owner = tt2.owner
      AND tt1.CONSTRAINT_NAME = tt2.CONSTRAINT_NAME
     where tt1.CONSTRAINT_TYPE = 'P'
) d
on a.owner=d.owner
AND a.TABLE_NAME = d.table_name
and a.COLUMN_NAME = d.COLUMN_NAME
where a.owner='schame名称' 
AND  a.table_name = '表名'
order by a.table_name,a.column_id;

3、查找上一次的DML操作

--将内存中的一些修改信息刷新到系统表中
exec dbms_stats.flush_database_monitoring_info
select * from user_tab_modifications 

4、查询oracle死锁

select session_id from v$locked_object;
select sid,serial#,username,osuser from v$session where sid = '';
alter system kill session ',';

5、异常抛出

RAISE_APPLICATION_ERROR(-20003,''异常编码：'' || SQLCODE || ''    异常描述：'' || SUBSTR(SQLERRM, 1, 200));

6、查看表空间使用情况

（1）对指定表空间不做限额

ALTER USER 用户名 QUOTA UNLIMITED on 表空间;

（2）制定用户对表空间限额

ALTER USER 用户名 QUOTA 30G on 表空间;

（3）查看空闲表空间

  SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 AS FREE_SPACE 
    FROM DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME 
ORDER BY TABLESPACE_NAME;

（4）查看临时表空间大小

  SELECT T.TABLESPACE_NAME,ROUND(SUM(BYTES/1024/1024/1024)) 
    FROM DBA_TABLESPACES T ,DBA_TEMP_FILES D
   WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME ORDER BY T.TABLESPACE_NAME;

（5）查看表空间创建语句

SELECT DBMS_METADATA.GET_DDL('TABLESPACE','TS_EDW_ADM_ADS_DATA_IDX') 
  FROM DUAL;

（6）查询表空间信息

select t1.tablespace_name                                 "表空间"
      ,t1.file_name as                                    "物理文件名"
      ,t1.bytes/1024/1024/1024                            "表空间容量（G）"
      ,t2.free_bytes/1024/1024/1024                       "空余表空间容量(G)"
      ,(t1.bytes - t2.free_bytes)/1024/1024/1024          "表空间已使用容量"
      ,round(t2.free_bytes/t1.bytes*100,3)                "表空间空闲率"
      ,round((t1.bytes - t2.free_bytes)/t1.bytes*100,3)   "表空间使用率"
      ,t1.MAXBYTES                                        "自增最大容量(M)"
     from dba_data_files t1
left join (
         select TABLESPACE_NAME,file_id,sum(bytes) as free_bytes 
           from dba_free_space 
       group by TABLESPACE_NAME,file_id
     ) t2
     on t1.TABLESPACE_NAME = t2.TABLESPACE_NAME
     and t1.FILE_ID = t2.FILE_ID
     where t1.TABLESPACE_NAME = '表空间名称';

7、用户锁定位

   SELECT /*+ RULE */ S.USERNAME
            ,DECODE(L.TYPE,'TM','TABLE LOCK','TX','ROW LOCK',NULL) LOCK_LEVEL
            ,O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE
            ,S.SID,S.SERIAL#
            ,S.TERMINAL,S.MACHINE,S.PROGRAM,S.OSUSER,'ALTER SYSTEM KILL SESSION '''||S.SID||','||S.SERIAL#||''';'
     FROM V$SESSION S
LEFT JOIN V$LOCK L
       ON S.SID = L.SID
LEFT JOIN DBA_OBJECTS O
       ON L.ID1 = O.OBJECT_ID
    WHERE S.USERNAME IS NOT NULL
      AND S.USERNAME ='XYD';

8、sql锁定位

SELECT b.username,B.SID,B.SERIAL#
        ,b.status,b.schemaname,b.osuser,b.MACHINE,b.terminal,b.SQL_EXEC_START,c.SQL_TEXT
        ,'alter system kill session '''||B.sid||','||B.serial#||''';' 
  FROM V$PROCESS A ,V$SESSION B ,V$SQLAREA C
 WHERE A.ADDR = B.PADDR
   AND B.SQL_HASH_VALUE = C.HASH_VALUE
   AND UPPER(C.SQL_TEXT) LIKE '%SQL语句模糊匹配%';

9、设置session并发

ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;
ALTER SESSION FORCE PARALLEL DML PARALLEL 8;

10、设置缓冲区大小

DBMS_OUTPUT.ENABLE(buffer_size=>null);

11、查看正在执行的脚本执行速度

select
    se.SID,
    opname,
    trunc(sofar / nullif(totalwork,0) * 100 , 2) || '%' as pct_work,
    elapsed_seconds   elapsep,
    round(elapsed_seconds * (totalwork - sofar) / nullif(sofar,0) ) remain_time,
    sql_text
from 
    v$session_longops sl,
    v$sqlarea         sa,
    v$session         se
where sl.SQL_HASH_VALUE = sa.hash_value
  and sl.SID = se.SID
  and sofar != totalwork
order by  start_time

12、查看执行计划

--查看正在执行的SQL(TIME_REMAINING >0表示正在运行)
SELECT T.SQL_ID,T.* FROM GV$SESSION_LONGOPS T WHERE TIME_REMAINING >0;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID'));
--查看慢SQL执行计划
SELECT * FROM DBMS_XPLAN.DISPLAY_AWR('SQL_ID');