oracle常用查询sql

查询表空间

sql">--- 查询表空间信息
select * from v$tablespace;--- 查看每个表空间的数据文件
desc dba_data_files;--- 查看详细数据文件
select file_name,tablespace_name from dba_data_files;select * from dba_data_files;   --describes database files  数据文件信息
select * from dba_temp_files;   --describes all temporary files (tempfiles) in the database 临时数据文件信息
select * from dba_free_space;   --describes the free extents in all tablespaces in the database 数据库中所有表空间中的空闲扩展区
select * from dba_segments;     --describes the storage allocated for all segments in the database 数据库中的所有段分配的存储--查询表空间使用情况
SELECT Upper(F.TABLESPACE_NAME)         "表空间名",D.TOT_GROOTTE_MB                 "表空间大小(M)",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                    "空闲空间(M)",F.MAX_BYTES                      "最大块(M)"
FROM   (SELECT TABLESPACE_NAME,Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTESFROM   SYS.DBA_FREE_SPACEGROUP  BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MBFROM   SYS.DBA_DATA_FILES DDGROUP  BY DD.TABLESPACE_NAME) D
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME--查询表空间的空闲扩展区
select tablespace_name, count(*) AS extends,round(sum(bytes) / 1024 / 1024, 2) AS 大小/MB
,sum(blocks) AS blocks 
from dba_free_space group BY tablespace_name;--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB 
from dba_data_files group by tablespace_name;
--查询表空间使用率 （有问题不要用）
SELECT total.tablespace_name,Round(total.MB, 2)           AS   总量/MB,Round(total.MB - free.MB, 2) AS  已使用/MB,Round(( 1 - free.MB / total.MB ) * 100, 2) || '%'                  AS  使用率
FROM   (SELECT tablespace_name,Sum(bytes) / 1024 / 1024 AS MBFROM   dba_free_spaceGROUP  BY tablespace_name) free,(SELECT tablespace_name,Sum(bytes) / 1024 / 1024 AS MBFROM   dba_data_filesGROUP  BY tablespace_name) total
WHERE  free.tablespace_name = total.tablespace_name;

查看总空间占用

sql">select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files

查询索引

sql">oracle中表的索引信息存在 user_indexes 和 user_ind_columns 两张表里面，  
其中user_indexes 系统视图存放是索引的名称以及该索引是否是唯一索引等信息，user_ind_columns 统视图存放的是索引名称，对应的表和列等sql示例：
select * from all_indexes where table_name='ACM_NETWORK_OPERATION';
select * from user_ind_columns where table_name='ACM_NETWORK_OPERATION';--- 查看索引个数和类别:
select * from user_indexes where table='表名' ;--- 查看索引被索引的字段:select * from user_ind_columns where index_name=upper('&index_name');我们可以通过类似下面的语句来查看一个表的索引的基本情况：select user_ind_columns.index_name,user_ind_columns.column_name,user_ind_columns.column_position,user_indexes.uniquenessfrom user_ind_columns,user_indexeswhere user_ind_columns.index_name = user_indexes.index_nameand user_ind_columns.table_name = ‘你想要查询的表名字’;

查看临时表空间

sql">--- 查看表空间情况
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

查看分区

sql">--- 查看所有分区
SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME FROM USER_TAB_PARTITIONS

查看有没有锁表

sql">**--以下几个为相关表  
SELECT * FROM v$lock;  
SELECT * FROM v$sqlarea;  
SELECT * FROM v$session;  
SELECT * FROM v$process ;  
SELECT * FROM v$locked_object;  
SELECT * FROM all_objects;  
SELECT * FROM v$session_wait;--查看被锁的表  
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;--查看那个用户那个进程照成死锁  
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;--查看连接的进程  
SELECT sid, serial#, username, osuser FROM v$session;**--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode**  SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,  
s.terminal, s.logon_time, l.type  
FROM v$session s, v$lock l  
WHERE s.sid = l.sid  
AND s.username IS NOT NULL  
ORDER BY sid;这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，  
任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。--杀掉进程 sid,serial#  
alter system kill session'210,11562';

查看存储水位

sql">SQL> exec dbms_stats.gather_table_stats(ownname='SCHEMA_NAME',tabname= 'TABLE_NAME');
-- 确定碎片程度/* Formatted on 2017/9/21 14:14:37 (QP5 v5.240.12305.39476) */
SELECT table_name,
ROUND ( (blocks * 8), 2) "高水位空间 k",
ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",
ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",
ROUND (
(  blocks * 8
- (num_rows * avg_row_len / 1024)
- blocks * 8 * 10 / 100),
2)
"浪费空间 k"
FROM user_tables
WHERE temporary = 'N'
ORDER BY 5 DESC;

附加

sql">/* 获取表：*/
select table_name from user_tables; --当前用户的表      
select table_name from all_tables; --所有用户的表  
select table_name from dba_tables; --包括系统表
--表字段信息
select * from all_tab_columns a where a.TABLE_NAME='T_X27_USER';
--表注释信息
select * from user_tab_comments a  where a.table_name='T_X27_USER';
--表字段注释信息
select * from user_col_comments a where a.table_name='T_X27_USER';
--表分区信息
--1,分区表信息
-- （1）显示数据库所有分区表的信息
select * from DBA_PART_TABLES a where a.owner=upper('') and a.table_name=upper('');
-- （2）显示当前用户可访问的所有分区表信息
select * from ALL_PART_TABLES  a where a.owner=upper('') and a.table_name=upper('');
-- （3）显示当前用户所有分区表的信息
select * from USER_PART_TABLES  a where a.table_name=upper('');
--2，分区表的分区列信息
-- （1）显示当前用户所有分区表的分区列信息
select * from USER_PART_KEY_COLUMNS a where a.name=upper('') and a.object_type='TABLE';
-- （2）显示当前用户可访问的所有分区表的分区列信息
select * from ALL_PART_KEY_COLUMNS  a where a.owner=upper('etl') and a.name=upper('') and a.object_type='TABLE';
--（3）显示分区列 显示数据库所有分区表的分区列信息
select * from DBA_PART_KEY_COLUMNS a where a.owner=upper('etl') and a.name=upper('') and a.object_type='TABLE';
-- 3,分区表的名字、归属表空间以及表的详细分区情况
select * from user_tab_partitions a where a.table_name=upper('');
-- 4,查看组合表的子分区信息以及子分区列信息情况
-- （1）显示当前用户所有组合分区表的子分区信息
select * from USER_TAB_SUBPARTITIONS;
-- （2）显示当前用户可访问的所有组合分区表的子分区信息
select * from ALL_TAB_SUBPARTITIONS; 
-- （3）显示当前用户可访问的所有组合分区表的子分区信息 
select * from ALL_TAB_SUBPARTITIONS ;
-- （4）显示当前用户所有分区表的子分区列信息
select * from USER_SUBPART_KEY_COLUMNS;
-- （5）显示当前用户可访问的所有分区表的子分区列信息
select * from ALL_SUBPART_KEY_COLUMNS; 
-- （6）显示子分区列 显示数据库所有分区表的子分区列信息 
select * from DBA_SUBPART_KEY_COLUMNS;
--表包含的索引
select * from user_indexes where table_name=upper('T_X27_USER');
--索引的具体信息：根据索引名查看索引包含的字段
select * from user_ind_columns where index_name = 'UK_T_X27_USER_USERID';
--表的唯一约束条件
select * from user_constraints where constraint_type='U' and owner='ETL' and table_name='T_X27_USER'; 
--表外键
select * from user_constraints where constraint_type='R' and owner='ETL' and table_name='T_X27_USER'; 
--表外键以及约束条件字段组成信息
select * from user_cons_columns where owner='ETL' and table_name='T_X27_USER';

flashback闪回

sql">ALTER SYSTEM SET recyclebin = ON;     开启系统回收站
ALTER SESSION SET recyclebin = ON;    开启当前连接回收站
ALTER SYSTEM SET recyclebin = OFF;    关闭系统回收站
ALTER SESSION SET recyclebin = OFF;    关闭当前连接回收站查询数据字典：
select * from dba_recyclebin; 查看我们的user_test是否在回收站中flashback table tableName to before drop rename to new_tableName;> show recycle; 查看回收站.  
> purge recyclebin; 清空回收站.  
> purge table t2 ; 清空回收站中t2的表.SELECT Value FROM V$parameter WHERE Name = 'recyclebin';
你可以启动或者关闭回收站里的每个会话（session）和系统（system），代码如下：1. ALTER SYSTEM SET recyclebin = ON;      3. ALTER SESSION SET recyclebin = ON;      5. ALTER SYSTEM SET recyclebin = OFF;      7. ALTER SESSION SET recyclebin = OFF;   你可以使用下面的任意一个语句来获得回收站中的对象：1. SELECT * FROM RECYCLEBIN;      3. SELECT * FROM USER_RECYCLEBIN;      5. SELECT * FROM DBA_RECYCLEBIN;**清空回收站**这里的清空包含两种情况，第一你可以有条件的清空；第二是全部清空。我们先来看看有条件的清空该如何做：a.清空一个特定的表：1. PURGE TABLE <<Table_NAME>>; b.清空一个特定的索引：1. PURGE INDEX <<Index_NAME>>; c.清空与该表空间有关联的对象：1. PURGE TABLESPACE<<Table_NAME>>; d.清空一个特定用户的表空间对象：1. PURGE TABLESPACE<<Table_NAME>> USER <<User_Name>>; e.清空回收站：1. PURGE RECYCLEBIN; f.当一个表被删除（drop）时就直接从回收站中清空1. DROP TABLE <<Table_Name>> PURGE;

awr生成

sql">### 自动创建快照开始压测后执行exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();可以通过dba_hist_wr_control查看当前的配置情况
select * from dba_hist_wr_control;修改配置，每隔30分钟收集一次，保存1天execute dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>14000);
关闭AWR自动收集SQL>exec dbms_workload_repository.modify_snapshot_settings (interval=>0,retention=>24*60);除了自动创建快照，也可以手工创建快照select dbms_workload_repository.create_snapshot() from dual;### 生成AWR报告在sqlplus或者plsql使用命令，${ORACLE_HOME}是Oracle的安装路径@/${ORACLE_HOME}/.../RDBMS/ADMIN/awrrpt.sql@?/rdbms/admin/awrrpt/awrrpt.sql
@?/rdbms/admin/awrrpt; 本实例AWR包括: 
@?/rdbms/admin/awrrpti; RAC中选择实例号  
@?/rdbms/admin/awrddrpt; AWR 比对报告  
@?/RDBMS/admin/awrgrpt; RAC全局AWR报告

sql">--- 查询回滚段状态
set linesize 300  
col TABLESPACE_NAME for a20
SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M",COUNT(*) Undo_Extent_Num FROM DBA_UNDO_EXTENTS
where TABLESPACE_NAME='UNDOTBS1'  
GROUP BY TABLESPACE_NAME, STATUS order by TABLESPACE_NAME,STATUS;SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='UNDOTBS1';
SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='UNDOTBS2';

– 磁盘空间的检查【RAC两节点、历史库、备库】

df -g

例如：

–ASM磁盘空间的检查【RAC环境】

su - grid
asmcmd
lsdg

例如：

–RAC 集群状态检查

su - grid
crsctl status res -t

–查询表空间数据【RAC环境、历史库】

sql">set lin 300
set pagesize 300
col TS-name format a20
col f.tablespace_name format a20
col d.tot_grootte_mb format a20
col ts-per format a12
select upper(f.tablespace_name) "TS-name",d.tot_grootte_mb "TS-bytes(m)",d.tot_grootte_mb - f.total_bytes "TS-used (m)",f.total_bytes "TS-free(m)",to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,2),'990.99') "TS-per"from (select tablespace_name,round(sum(bytes) / (1024 * 1024), 2) total_bytes,round(max(bytes) / (1024 * 1024), 2) max_bytesfrom sys.dba_free_spacegroup by tablespace_name) f,(select dd.tablespace_name,round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mbfrom sys.dba_data_files ddgroup by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 5 desc;

–查询临时表空间【RAC环境、历史库】

sql">set lin 200
set pagesize 200
col TABLESPACE_NAME format a20
col "Tempfile name" format a40
Select f.tablespace_name,
d.file_name "Tempfile name",
round((f.bytes_free + f.bytes_used) / 1024 /1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) -nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" ,
round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2)"Used MB",
round((round(nvl(p.bytes_used, 0)/ 1024 /1024, 2)/round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2))*100,2) as"Used_Rate(%)"
from SYS.V_$TEMP_SPACE_HEADER f,DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) =d.file_id;

–查询备份：【RAC环境、历史库】

sql">set line 232
col input_type for a15
col status for a12
col input_gb for a10
col output_gb for a10
col start_time for a15
col end_time for a15
col time_taken for a10
select input_type,status,input_bytes_display input_gb,output_bytes_display output_gb,
to_char(start_time,'mm-dd-hh24:mi') start_time,to_char(end_time,'mm-dd-hh24:mi') end_time,time_taken_display time_taken
from v$rman_backup_job_details
where start_time >sysdate-7
order by 5 desc;

ADG同步检查：【RAC环境、备库单机】
主库查询语句1：

sql">set lin 200;
col OPEN_MODE format a15;
col PROTECTION_MODE format a20;
col DATABASE_ROLE format a20;
col SWITCHOVER_STATUS format a15;
select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;select thread#,max(sequence#) from v$archived_log group by thread#;

备注：
主库toad链接、可以直接在toad执行脚本检查

备库 172.161.2.53
备库查询语句1：

sql">set lin 200;
col OPEN_MODE format a15;
col PROTECTION_MODE format a20;
col DATABASE_ROLE format a20;
col SWITCHOVER_STATUS format a15;
select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
备库查询语句2：
select thread#,max(sequence#) from v$archived_log group by thread#;
备库查询语句3：
select thread#,low_sequence#,high_sequence# from v$archive_gap;
备库查询语句4：
select PROCESS,CLIENT_PROCESS,STATUS,SEQUENCE# from v$managed_standby;
备库查询语句5：
select thread#,sequence#,first_time,next_time,applied from v$archived_log where applied='NO';

备注：主要是APPLYING_LOG日志号是否是主库的对应下一个日志号即可。

其他检查及相关脚本：
语句1：
awr报告的收集
@?/rdbms/admin/awrrpt.sql

语句2：查看undo表空间的相关状态占用大小

sql">SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M",COUNT(*) Undo_Extent_Num FROM DBA_UNDO_EXTENTS
where TABLESPACE_NAME='UNDOTBS1'  
GROUP BY TABLESPACE_NAME, STATUS order by TABLESPACE_NAME,STATUS;

语句3：查看归档生成量

sql">select THREAD#,  logtime,  count(*),  round(sum(blocks * block_size) / 1024 / 1024) mbsize  from (select THREAD#,  trunc(first_time, 'dd') as logtime,  a.BLOCKS,  a.BLOCK_SIZE  from v$archived_log a  where a.DEST_ID = 1  and a.FIRST_TIME > trunc(sysdate - 7))  group by THREAD#, logtime  order by THREAD#, logtime desc;