oracle日常巡检语句

--检查Oracle所有数据文件状态
select name,status from v$datafile;
输出结果中“STATUS”应该都为“ONLINE”。
select file_name,status from dba_data_files; 
输出结果中“STATUS”应该都为“AVAILABLE”。

--检查Oracle在线日志状态
select group#,status,type,member from v$logfile; 

输出结果应该有3条以上（包含3条）记录，
“STATUS”应该为非“INVALID”，非“DELETED”。
注：“STATUS”显示为空表示正常。

--批量查杀锁
select 'alter system kill session '||chr(39)|| t2.sid||','||t2.serial#||chr(39)||';'
from gv$locked_object t1,gv$session t2 
where t1.session_id=t2.sid 
--and t2.MACHINE='epi-platform2'
order by t2.logon_time;

----查看TM/TX锁争用
select s.EVENT#,s.EVENT,s.* from v$session s
--where s.SID='52'
order by s.sid;

select v.event,v.sid,v.P1,v.p2,v.p3 from v$session_wait v
where v.EVENT='enq: TM - contention';

select v.event,v.sid,v.P1,v.p2,v.p3 from v$session_wait v
where v.EVENT='enq: TX - row lock contention';

--查看死锁是否存在
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object);

Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序

--查看死锁的语句
select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));

--查看死锁的进程
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

--查看表空间的使用情况
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
ORDER  BY 1

--查看Oracle的连接数
select count(*) from v$session
union all
--当前的数据库连接数
select count(*) from v$process
union all
--查看Oracle的并发连接数
select count(*) from v$session where status='ACTIVE';

--查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;

--查询指定用户的下的所有表
select * from dba_tables where owner='EPI_SHANDONG_NEW';

--修改密码
ALTER USER system IDENTIFIED BY shensusd;

--查询表空间当前用户
select username,default_tablespace from user_users;

--查询数据库的实例名
select instance_name from v$instance;


--解锁用户
alter user EPI_SHANDONG_NEW account unlock;
--查看用户是否锁掉了
select username,account_status from dba_users where username='EPI_SHANDONG_NEW';

--查询数据库参数
SQL> show parameter db;

--查看数据泵导出路径
select * from dba_directories;

--仅仅导出表结构
SQL> expdp SJC_SD/SJC_SD@2019 directory=dump dumpfile=a.dump

--仅仅导入表结构
SQL> impdp system/shensusd directory=impdp dumpfile=dp_EPI_SHANDONG_NEW20191126.dmp  transform=OID:N


--查询用户
select * from dba_users;


--删除用户
drop user phctsp_sd_n cascade;

--查询表空间创建命令
select 'Create TableSpace '||tablespace_name|| ' datafile ''d:\app\Administrator\oradata\orcl\'||tablespace_name||'.dbf'' size 50m autoextend on;'
from user_tab_partitions  
order by tablespace_name; 

user_tab_partitions：可查看分区表的名字、归属表空间以及表的详细分区情况。 
user_part_tables：可查看用户所有的分区表，以及分区方式

--查看日志路径
SQL>  show parameter dump_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/rac/rac1/trace
core_dump_dest                       string      /u01/app/oracle/diag/rdbms/rac/rac1/cdump
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/rac/rac1/trace


--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;

--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;

--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;

--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;

--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

--删除用户
SQL> drop user SD_SWZPM cascade;

--创建归档日志
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orclo/redo04.log') size 50M;                                               

 ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orclo/redo05.log') size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orclo/redo06.log') size 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orclo/redo07.log') size 50M;

--查询死锁
select object_name,machine,s.sid,s.serial# 
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;
--杀死会话
alter system kill session '1142,39701';