Oracle common maintenance query

Time:2022-5-2

Check deadlock

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;

Force deadlock release

alter system kill session '1500,6160';

reference resourceswww.jb51.net/article/85039.htm

Check the SQL of deadlock and the SQL executed before deadlock according to Sid

--Deadlock time
select sql_text from v$sqlarea a,v$session b where a.SQL_ID=b.SQL_ID and (b.SID=47 or b.SID=3847);
--Before deadlock
select sql_text from v$sqlarea a,v$session b where a.SQL_ID=b.PREV_SQL_ID and (b.SID=47 or b.SID=3847);

According to serial # query session

select * from v$session a where a.SERIAL# =28343

Query the total number of current connections

--Both SQL are OK
select count(*) from v$session;
select count(*) from v$process;

Query the number of connections of each machine to the database

select machine ,count(machine) from v$session group by machine;

Query concurrent connections

select count(*) from v$session where status='ACTIVE';

View the number of connections of different users

select username,count(username) from v$session 
where username is not null group by username;

View which users are currently using data

SELECT osuser, a.username,
cpu_time/executions/1000000||'s', sql_fulltext,machine 
from v$session a, v$sqlarea b
where a.sql_address =b.address 
order by cpu_time/executions desc;

Query tablespace list

select tablespace_name, file_name, autoextensible
from dba_data_files
where tablespace_name in (
SELECT tablespace_name FROM dba_free_space 
GROUP BY tablespace_name);

Query the utilization rate of each table space

select a.tablespace_name,
       round((a.maxbytes / 1024 / 1024), 2) "sun MB",
       round((a.bytes / 1024 / 1024), 2) "datafile MB",
       round(((a.bytes - b.bytes) / 1024 / 1024), 2) "used MB",
       round(((a.maxbytes - a.bytes + b.bytes) / 1024 / 1024), 2) "free MB",
       round(((a.bytes - b.bytes) / a.maxbytes * 100), 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes, sum(maxbytes) maxbytes
          from dba_data_files
         where maxbytes != 0
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.maxbytes) desc;

Query the usage rate of the data file to which the tablespace belongs

Select bytes / 1024 / 1024 / 1024 as currently used_ GB,
Maxbytes / 1024 / 1024 / 1024 as extended maximum_ GB,
Maxblocks / 1024 / 1024 as size per expansion_ MB,
file_ Name as data file_ name,
tablespace_ Name as table space
from dba_data_files;

This work adoptsCC agreement, reprint must indicate the author and the link to this article

:kissing_ closed_ eyes: