DBA常用SQL总结梳理

1、影响系统性能的前10大SQL

SELECT * FROM 
( 
 SELECT PARSING_USER_ID 
 EXECUTIONS, 
 SORTS, 
 COMMAND_TYPE, 
 DISK_READS, 
 sql_text 
 FROM v$sqlarea 
 ORDER BY disk_reads DESC 
) 
WHERE ROWNUM<10 ;

 2、循环提交SQL脚本

declare
    -- a counter
    g_counter     number(10) := 1;
    -- every 1000 rows ,to commit
    g_commit     number(10);
begin
    loop
    
    insert into t values(g_counter,'arcerzhang',sysdate);
    
    --every 1000 rows , to commit;
    
    g_commit := mod(g_counter,1000);
    
    if g_commit = 0 then
        commit;
        dbms_output.put_line(g_commit);
    end if;
    
    g_counter := g_counter + 1;
    --exit conditions
    exit when g_counter > 10000;
    
    end loop;
end;

/

 3、查看表空间名称及大小

col 表空间名称 for a30
set linesize 200
SELECT UPPER(F.TABLESPACE_NAME) "表空间名称",       
       D.TOT_GROOTTE_MB "表空间大小",       
       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 "空闲空间",       
       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;

4、查看数据库引起锁表的语句

SELECT A.USERNAME,
       
       A.MACHINE,
       
       A.PROGRAM,
       
       A.SID,
       
       A.SERIAL#,
       
       A.STATUS,
       
       C.PIECE,
       
       C.SQL_TEXT

  FROM V$SESSION A,
       
       V$SQLTEXT C

 WHERE A.SID IN (SELECT DISTINCT T2.SID
                 
                   FROM V$LOCKED_OBJECT T1,
                        
                        V$SESSION T2
                 
                  WHERE T1.SESSION_ID = T2.SID)
      
   AND A.SQL_ADDRESS = C.ADDRESS(+)

 ORDER BY C.PIECE;

5、查看数据库锁的情况

SELECT OBJECT_ID, SESSION_ID, LOCKED_MODE FROM V$LOCKED_OBJECT;

SELECT T2.USERNAME,
       
       T2.SID,
       
       T2.SERIAL#,
       
       T2.LOGON_TIME

  FROM V$LOCKED_OBJECT T1, V$SESSION T2

 WHERE T1.SESSION_ID = T2.SID

 ORDER BY T2.LOGON_TIME;

6、查看被锁的表

SELECT P.SPID,
       
       A.SERIAL#,
       
       C.OBJECT_NAME,
       
       B.SESSION_ID,
       
       B.ORACLE_USERNAME,
       
       B.OS_USER_NAME

  FROM V$PROCESS P,
       
       V$SESSION A,
       
       V$LOCKED_OBJECT B,
       
       ALL_OBJECTS C

 WHERE P.ADDR = A.PADDR
      
   AND A.PROCESS = B.PROCESS
      
   AND C.OBJECT_ID = B.OBJECT_ID;

7、常用SQL总结

--杀掉进程
ALTER SYSTEM KILL SESSION 'sid,serial#';
--查看连接数
SELECT COUNT (*) FROM v$session;
--查看并发连接数
SELECT COUNT(*) FROM V$SESSION WHERE STATUS='ACTIVE';
--查看连接的进程
SELECT SID, SERIAL#, USERNAME, OSUSER FROM V$SESSION;


--查看数据库使用的裸设备

  SELECT *

    FROM dba_data_files

ORDER BY file_name;

 

  SELECT *

    FROM dba_temp_files

ORDER BY file_name;

 

  SELECT *

    FROM v$controlfile

ORDER BY file_name;

 

  SELECT *

    FROM v$logfile;

 

--具体的方法是查询dba_data_files，dba_temp_files，v$controlfile和v$logfile看这四类文件具体占用的裸设备

 

--查询所有用户表使用大小的前三十名
select * from (select segment_name,bytes from dba_segments where owner = USER order by bytes desc ) where rownum <= 30;
 
--以下的SQL语句列出当前数据库建立的会话情况:
select sid,serial#,username,program,machine,status from v$session;
--如果DBA要手工断开某个会话，则执行:
alter system kill session 'SID,SERIAL#';
--注意，上例中SID为1到7(USERNAME列为空)的会话，是Oracle的后台进程，不要对这些会话进行任何操作。
--查询表空间有那些表:
select table_name from all_tables where tablespace_name= 'TEMP';

 8、查看数据库属性

col PROPERTY_NAME for a30
col PROPERTY_VALUE for a50
col DESCRIPTION for a60
set linesize 200
select * from database_properties;