-- DATA 这里仅提供查询锁和解锁。有时，锁是正常的，所以杀掉正锁着的进程有一定的风险性。具体步骤如下：-- 1.0 查看 holder的进程SELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0) ORDER BY id1, request;-- 2.0 看 lock的表SELECT DOB.OBJECT_NAME, VSS.CLIENT_IDENTIFIER, LO.SESSION_ID || ',' || VSS.SERIAL#, LO.LOCKED_MODE, VSS.SERIAL#, VSS.ACTION, VSS.OSUSER, VSS.LOGON_TIME, VSS.PROCESS, VPS.SPID, VSS.* FROM V$LOCKED_OBJECT LO, DBA_OBJECTS DOB, V$SESSION VSS, V$PROCESS VPS WHERE LO.OBJECT_ID = DOB.OBJECT_ID AND LO.SESSION_ID = VSS.SID AND VSS.PADDR = VPS.ADDR and VSS.SID = ; -- 值去 1.0 sess 字段 holder: 后的数字 -- 3.0 查看状态 SID SERIAL select * from v$session where sid = ;-- 值去 1.0 sess 字段 holder: 后的数字 -- 4.0 如果状态是 INACTIVE 等，酌情kill ALTER SYSTEM KILL SESSION ' , ';-- 分别取 3.0 SID SERIAL
-- undoSELECT a.tablespace_name as tablespace_name, to_char(b.total / 1024 / 1024, 999999.99) as Total, to_char((b.total - a.free) / 1024 / 1024, 999999.99) as Used, to_char(a.free / 1024 / 1024, 999999.99) as Free, to_char(round((total - free) / total, 4) * 100, 999.99) as Used_Rate 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
-- tempSELECT A.tablespace_name tablespace, D.mb_total, SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts# = C.ts# GROUP BY B.name, C.block_size) D WHERE A.tablespace_name = D.nameGROUP by A.tablespace_name, D.mb_total;
增大表空间
这里仅是改成数据文件自动扩展格式，每个数据文件最大存储32G.
select * from dba_data_files where tablespace_name = 'XXX’; -- 表空间名称alter database DATAFILE XX autoextend on next 100M maxsize unlimited; -- 扩展为自动扩展，扩展到最大 32G