当前位置：首页 > article >正文

oracle常用查询sql

article 2024/11/6 12:17:54

查询表空间

--- 查询表空间信息
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_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




--查询表空间的空闲扩展区
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 MB
        FROM   dba_free_space
        GROUP  BY tablespace_name) free,
       (SELECT tablespace_name,
               Sum(bytes) / 1024 / 1024 AS MB
        FROM   dba_data_files
        GROUP  BY tablespace_name) total
WHERE  free.tablespace_name = total.tablespace_name;

查看总空间占用

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

查询索引

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.uniqueness

from user_ind_columns,user_indexes

where user_ind_columns.index_name = user_indexes.index_name

and user_ind_columns.table_name = ‘你想要查询的表名字’;

查看临时表空间

--- 查看表空间情况
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(+)

查看分区

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

查看有没有锁表

**--以下几个为相关表  
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> 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;

附加

/* 获取表：*/
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闪回

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生成

### 自动创建快照

开始压测后执行

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报告

--- 查询回滚段状态
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环境、历史库】

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_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 5 desc;

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

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环境、历史库】

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：

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：

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表空间的相关状态占用大小

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：查看归档生成量

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;