1.表空间大小：dba_data_files | dba_temp_files (在12c版本中，在cdb和pdb中查询是不一样的）
select TABLESPACE_NAME,BYTES/1024/1024 from dba_data_files;
或者： select TABLESPACE_NAME,sum(user_bytes/1024/1024) from dba_data_files group by tablespace_name union select TABLESPACE_NAME,sum(user_bytes/1024/1024) from dba_temp_files group by tablespace_name; 2.数据对象的大小 dba_segments： SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name in ('ACCESS$','ACLMV$'); SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 -------------------- ------------------ --------------- ACCESS$ TABLE 4 ACLMV$ TABLE .0625 3.当前会话查询 v$session | v$sql 查询sqlid，等待事件,sql_text select sid,status,sql_id from v$session; select sql_text from v$sql where sql_id='d5r4qfqtf3ym6'; SQL> select sid,status,a.sql_id,sql_text,lockwait from v$session a,v$sql b where a.sql_id=b.sql_id; SID STATUS SQL_ID SQL_TEXT ---------- -------- ------------- ------------------------------ 32 ACTIVE f3yfg50ga0r8n select obj# from obj$ where da taobj# = :1 71 ACTIVE ftunhrvf7p4bx select sid,status,a.sql_id,sql _text from v$session a,v$sql b where a.sql_id=b.sql_id 4.查会话对应的进程号,当某个会话hang住，并且杀不掉时，查出进程号，操作系统下kill -9 杀掉 select spid from v$process where addr=(select paddr from v$session where sid=71); kill -9 73082; ->会话not connected to ORACLE 5. 会话的阻塞 v$lock select sid,lmode,type,request,block from v$lock; select sid,lmode,type,request,block from v$lock where type in ('TX','TM') order by 1,3;