Oracle 数据库日常运维SQL

1.查看sql_text
select a.PROGRAM,b.spid,c.sql_text,c.sql_id,c.sql_text
from V$SESSION a ,V$PROCESS b ,v$sqlarea c
where a.PADDR =b.addr
and a.sql_hash_value = c.HASH_VALUE
and a.username is not null;

2.查看是否有被锁对象及锁模式(
locked_mode
0:none
1:null 空
2:Row-S行共享(RS):共享表锁,sub share
3:Row-X行独占(RX):用于行的修改,sub exclusive
4:Share共享锁(S):阻止其他DML操作
5:S/Row-X共享行独占(SRX):阻止其他事物操作,Share/sub exclusive
6:exclusive独占(X):独立访问使用
数字越大锁级别越高,影响的操作越多
)
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

3.查看被锁对象的sid,serial#
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;

4.查看历史回话
select * from V$ACTIVE_SESSION_HISTORY

5.查看当前正在执行的sql
select a.INST_ID,a.SQL_TEXT,a.SQL_ID,b.*
from GV$SQLAREA a,GV$SESSION b
where a.ADDRESS = b.SQL_ADDRESS
and a.INST_ID = b.INST_ID;

6.查看进程正在执行的sql
select b.EVENT,b.BLOCKING_SESSION,b.BLOCKING_INSTANCE,a.SPID,c.SQL_TEXT,c.*
from GV$PROCESS a,GV$SESSION b,GV$SQLAREA c
where a.ADDR = b.PADDR
and a.INST_ID = b.INST_ID
and c.ADDRESS = b.SQL_ADDRESS
and b.BLOCKING_SESSION is not null
and a.spid = '2149';

7.查看表空间使用情况
select a.tablespace_name "表空间名",
total/1024/1024/1024 "表空间使用大小(G)",
free/1024/1024/1024 "表空间剩余大小(G)",
(total - free)/1024/1024/1024 "表空间使用大小(G)",
round((total - free)/total,4)*100 "表空间使用率%"
from (
select tablespace_name,sum(bytes) free from DBA_FREE_SPACE group by tablespace_name
) a,
(
select tablespace_name,sum(bytes) total from DBA_DATA_FILES group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name;

8.查看指定表空间下表大小(从大到小)
select SEGMENT_name,tablespace_name,bytes/1024/1024/1024 gb
from DBA_SEGMENTS
where tablespace_name = 'TBS_FDM_DATA'
and segment_type = 'TABLE'
order by bytes desc;

9.查看数据库名
select name from V$DATABASE;

10.查看实例名
select instance_name from V$INSTANCE;

11.查看ORACLE的并发连接数
select count(1) from v$session where status = 'ACTIVE';

12.查看哪些用户有sysdba或sysoper系统权限
select * from V$PWFILE_USERS;

13.查看数据文件存放路径
select name from V$DATAFILE;

14.查看指定用户下的表
select * from all_tables where owner = 'BRH_DM';

15.删除表中的重复数据只留下一条
delete from 表名 WHERE (**) IN (
SELECT ** FROM 表名 group by ** having count(**) >1
) and rowid not in (
select min(rowid) from 表名 GROUP BY ** HAVING COUNT(1) > 1
);

16.查询分区表中各分区数据量
select t.TABLE_NAME,t.PARTITION_NAME,t.NUM_ROWS,t.LAST_ANALYZED
from DBA_TAB_PARTITIONS t 
where t.table_name = 'T002_F_BCS_CURM_BAK';

17.查看某张表已经存在的索引以及类型
select b.UNIQUENESS,a.INDEX_NAME,a.table_name,a.COLUMN_NAME
from ALL_IND_COLUMNS a,ALL_INDEXES b
where a.INDEX_NAME = b.INDEX_NAME
and a.TABLE_NAME = UPPER('T002_F_FMS_T5_CUST_VOL')
order by a.TABLE_NAME,a.INDEX_NAME,a.COLUMN_POSITION;

18.查询数据库等待时间和实际执行时间的相对百分比
select * from v$sysmetric a
where a.METRIC_NAME in ('Database CPU Time Ratio','Database WAIT Time Ratio')
AND A.INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM V$SYSMETRIC);

19.查询数据库表空间是否是自增长模式
SELECT T.TABLESPACE_NAME,T.FILE_NAME,T.AUTOEXTENSIBLE FROM DBA_DATA_FILES T
WHERE T.AUTOEXTENSIBLE = 'YES';

20.通过sid查询spid

select spid from v$process where addr in (select paddr from v$session where sid=xxx);