数据库表空间使用情况查看

1.数据库表空间使用情况

select tablespace_name,
       round(used_space *
             (select value from v$parameter where name = 'db_block_size') /
             power(2, 30),
             2) used_gb,
       round(tablespace_size *
             (select value from v$parameter where name = 'db_block_size') /
             power(2, 30)) maxsize_gb,
       round(used_percent, 2) as "PCT%"
  from dba_tablespace_usage_metrics;

2.占用空间较大的表查询

select segment_name, bytes from user_segments where segment_type = 'TABLE';

3.fdmee可清的表汇总
snp_sess_task、snp_sess_task_log、SNP_SESS_STEP_LV、SNP_VAR_SESS、SNP_STEP_LOG、SNP_SESS_STEP、SNP_SESSION
清理表的对应sess_no在SNP_SESSION的sess_end值在两个月之前的即可。

4.hec可清的表汇总
ssc_logs、sys_runtime_request_record、sys_runtime_request_detail

5.数据库锁查询

select a_s.owner,
  a_s.object_name,
  a_s.object_type,
  vn.sid,
  vn.serial#,
  vs.spid "os_pid",
  vn.process "client_pid",
  vn.username,
  vn.osuser,
  vn.machine "hostname" ,
  vn.terminal,
  vn.program,
  to_char(vn.logon_time,'yyyy-mm-dd hh24:mi:ss')"login_time",
  'alter system kill session '''||vn.sid||','||vn.serial#||''';' "oracke_kill",
  'kill -9 '|| vs.spid "os_kill"
from all_objects a_s,
  v$locked_object v_t,
  v$session vn,
  v$process vs
where a_s.object_id=v_t.object_id
and v_t.session_id =vn.sid
and vs.addr=vn.paddr
and vn.username not in('SYSMAN','SYS');