oracle ORA-00060死锁查询、表空间扩容

最新推荐文章于 2024-09-09 20:17:18 发布

转载最新推荐文章于 2024-09-09 20:17:18 发布 · 219 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/maxmoore/p/8867353.html

文章标签：

#数据库

本文提供了一系列Oracle数据库管理技巧，包括如何查看被锁住的表和会话、解决死锁问题、检查表空间的使用情况及扩容方法等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--查看被锁住的表
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 * from dba_waiters;

--查看引起死锁的会话
select sess.sid,
   sess.serial#,
   lo.oracle_username,
   lo.os_user_name,
   ao.object_name,
   lo.locked_mode
   from v$locked_object lo,
   dba_objects ao,
   v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid
order by ao.object_name ;
--释放锁或者杀掉ORACLE进程
alter system kill session sid,.serial#

--查看表空间大小和使用率
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from 
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

--某张表对应的数据库实例和表空间
select owner,table_name,tablespace_name from dba_tables where table_name='BILLS_BREAK';
--表空间下所表
select table_name from dba_tables where tablespace_name='USERS';

--表空间扩容
--1.先查询表空间在物理磁盘上存放的位置
SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files where tablespace_name='USERS' ORDER BY tablespace_name ; 
--2.改变数据文件的大小
alter database datafile '/oracle/app/oradata/mytablespace/my_01.dbf' resize 256M;
--3.验证
select bytes/1024/1024, tablespace_name from dba_data_files where tablespace_name='USERS';