oracle数据库表空间，会话相关sql

oracle 数据库查询表空间相关sql
1.表空间大小：dba_data_files | dba_temp_files (在12c版本中，在cdb和pdb中查询是不一样的）
注意sql大小不同
查询表空间及对应的物理文件
col TABLESPACE_NAME for a20;
col file_name for a20;
SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; 
或者只查询表空间：
SQL> 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;

查看表空间名称和大小
SELECT t.tablespace_name, round(SUM(bytes / (1024*1024)), 0) ts_size 
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name;
1-2查看表空间使用情况
SELECT tablespace_name,SUM(bytes) / (1024 * 1024) AS free_space 

FROM dba_free_space GROUP BY tablespace_name; 

set linesize 300;
col tablespace_name for a15;
SELECT a.tablespace_name, 
a.bytes/1024/1024 total, 
b.bytes/1024/1024 used, 
c.bytes/1024/1024 free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name; 
2.数据对象的大小 dba_segments：
col segment_name for a20;

select segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name in ('ACCESS$','ACLMV$');

2-1查用户下表大小
select owner,segment_name,sum(bytes/1024/1024/1024),segment_type from dba_segments where owner in ('SYS') and segment_name not like 'BIN$%' GROUP BY owner,segment_name,segment_type order by 3;
2-2查看回滚段名称和大小
set linesize 999;
col segment_name for a10;
col tablespace_name for a10;
col status for a10;
col initialextent for a10;
SELECT segment_name, tablespace_name, r.status, 
(initial_extent / 1024) initialextent, (next_extent / 1024) nextextent, 
max_extents, v.curext curextent FROM dba_rollback_segs r, v$rollstat v 
WHERE r.segment_id = v.usn(+) ORDER BY segment_name; 
3.当前会话查询 v$session | v$sql  查询sqlid，等待事件
select sid,status,sql_id from v$session;
select sql_text from v$sql where sql_id='d5r4qfqtf3ym6';
select sid,status,a.sql_id,sql_text,lockwait 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;
SQL> select distinct type from v$lock;
6.查看数据库的创建日期和归档方式
SELECT created,log_mode FROM v$database;