- 查看表空间的使用情况
--查看表空间的使用情况
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
-
查询连接Session
a.查询所有连接会话SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT Null
b.查询锁表线程
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
c.kill掉对应锁表连接
alter system kill session 'SID,SERIAL#'
d.如果c不能解决,查询进程号,去服务器上kill进程
select spid, osuser, s.program --spid是进程号 from v$session s,v$process p where s.paddr=p.addr and s.sid=267;--267是上一步的sid --到数据库服务器上去kill这个进程 -- kill -9 xxxx(进程号//-9表示强行终止)
-
查询当前用户的所有表
select * from all_tables where owner='TEST';
--用户名大写
- 显示表字段信息
select *
from user_tab_columns
where Table_Name='CUSTOMER' --换成自己表名,大写
order by column_name