表空间

--查看表空间语句
select 'tdjf' db_name,
          a.tbs_name tbs_name,
          a.tbs_size tbs_size,
          (a.tbs_size - b.tbs_free)  used,
          b.tbs_free tbs_free,
         round((a.tbs_size - b.tbs_free) / a.tbs_size * 100, 2) range,
         sysdate check_time
     from (select tablespace_name  tbs_name, sum(bytes) tbs_size from dba_data_files group by tablespace_name ) a,
          (select tablespace_name  tbs_name, sum(bytes)  tbs_free from dba_free_space group by tablespace_name) b
where a.tbs_name = b.tbs_name;

--数据库链接数
select 'tdjf1'  db_name, count(*) num,sysdate from v$session ;
select * from v$session;

 

--oracle 进程
select * from v$process;
--查锁
SELECT /*+ rule */ S.INST_ID,s.username,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser,l.*,s.*,o.*
FROM gv$session s,gv$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL

 

查看当前用户每个表占用空间的大小：
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

查看每个表空间占用空间的大小：
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name

查看表实际占用大小
 select   sum(bytes)   from   user_segments   where   segment_name='TABLE_NAME';

查看oracle版本
select banner from sys.v_$version;