Oracle DBA常用SQL语句

一、查询执行最慢的sql

Select *
  From (Select Sa.Sql_Text,
               Sa.Sql_Fulltext,
               Sa.Executions "执行次数",
               Round(Sa.Elapsed_Time / 1000000, 2) "总执行时间",
               Round(Sa.Elapsed_Time / 1000000 / Sa.Executions, 2) "平均执行时间",
               Sa.Command_Type,
               Sa.Parsing_User_Id "用户ID",
               u.Username "用户名",
               Sa.Hash_Value
          From V$sqlarea Sa
          Left Join All_Users u
            On Sa.Parsing_User_Id = u.User_Id
         Where Sa.Executions > 0
         Order By (Sa.Elapsed_Time / Sa.Executions) Desc)
 Where Rownum <= 50;

二、查询次数最多的 sql

Select *
  From (Select s.Sql_Text,
               s.Executions "执行次数",
               s.Parsing_User_Id "用户名",
               Rank() Over(Order By Executions Desc) Exec_Rank
          From V$sql s
          Left Join All_Users u
            On u.User_Id = s.Parsing_User_Id) t
 Where Exec_Rank <= 100;

三、查看表空间使用情况

Select Dbf.Tablespace_Name,
       Dbf.Totalspace "总量(M)",
       Dbf.Totalblocks As 总块数,
       Dfs.Freespace "剩余总量(M)",
       Dfs.Freeblocks "剩余块数",
       (Dfs.Freespace / Dbf.Totalspace) * 100 "空闲比例"
  From (Select t.Tablespace_Name,
               Sum(t.Bytes) / 1024 / 1024 Totalspace,
               Sum(t.Blocks) Totalblocks
          From Dba_Data_Files t
         Group By t.Tablespace_Name) Dbf,
       (Select Tt.Tablespace_Name,
               Sum(Tt.Bytes) / 1024 / 1024 Freespace,
               Sum(Tt.Blocks) Freeblocks
          From Dba_Free_Space Tt
         Group By Tt.Tablespace_Name) Dfs
 Where Trim(Dbf.Tablespace_Name) = Trim(Dfs.Tablespace_Name);
 

四、增加表空间

select * from dba_data_files;--查询表空间文件所在路径
alter tablespace users add datafile  '/db/oradata/gnnt/users08.dbf' size 30000M autoextend on;

五、查询表的大小

SELECT 
SEGMENT_NAME TABLENAME
,(BYTES/1024/1024) MB
,RANK() OVER (PARTITION BY NULL ORDER BY BYTES DESC) RANK_ID  //根据表大小进行排序
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE='TABLE'

六、查询oracle是否为集群？RAC还是HA？

# sqlplus / as sysdba;
# show parameters cluster_database;
如果显示结果为true，则是集群，否则是单机。

crs_stat 可以运行就是RAC，不然就是HA了