查表空间使用率情况

SELECT d.tablespace_name "Name", d.status "Status",
TO_CHAR (NVL (a.BYTES / 1024 / 1024 / 1024, 0), '99,999,990.90') "Total (G)",
TO_CHAR (NVL (f.BYTES / 1024 / 1024 / 1024, 0), '99,999,990.90') "Free (G)",
TO_CHAR (NVL (a.BYTES - NVL (f.BYTES, 0), 0) / 1024 / 1024 / 1024,'99999999.99') "Used (G)",
TO_CHAR (NVL ((a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100, 0),'990.00') "Used %"
-- ,(a.BYTES - NVL (f.BYTES, 0)) / a.BYTES * 100 "%"
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES) BYTES FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY')
AND (d.tablespace_name in('')
or d.tablespace_name like 'abd%'
or d.tablespace_name like 'ABCD%'
)
order by 1 desc
;

表空间扩容

-- 1、先查服务器磁盘空间是否足够
    df -h  查磁盘
  服务器数据文件路径：/oradata/abcd/
-- 2、表空间扩容举例
alter tablespace FDLSC add datafile '/oradata/abcd/ABCD.dbf' size 30000m;

表大小 ，碎片

-- 查询某个具体的表所占空间的大小，segment_name = 'TABLE_NAME'换成具
体要查询的表的名称就可以
select * from (
select OWNER,t.segment_name, t.segment_type
, TO_CHAR (sum(t.bytes / 1024 / 1024 / 1024),'99999999.99') "占用空间(G)"
from dba_segments t
where 1=1
and t.segment_type like '%TABLE%'
-- and (t.segment_name LIKE '%E_CBIT_D0506_BDYJXXB%' )
and t.OWNER in ('abc','')
group by OWNER, t.segment_name, t.segment_type

) order by 2 desc
;
-- 表碎片  ---------------------------------------------------------------------------------------------
WITH T AS (
SELECT table_name
 ,ROUND ( (blocks * 8) /1024/1024, 2) C2 --"高水位空间 G"
 ,ROUND ( (num_rows * avg_row_len / 1024)/1024/1024, 2) C3 --"真实使用空间 G",
 ,ROUND ( (blocks * 10 / 100) * 8/1024/1024, 2) C4 --"预留空间(pctfree) G",
 ,ROUND (( blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100)/1024/1024, 2) C5 --"浪费空间 k"
 FROM user_tables
 WHERE temporary = 'N'
and  table_name = 'abd'
--ORDER BY 5 DESC
)
SELECT T.table_name,t.c2 "高水位空间 G", t.c3 "真实使用空间 G",t.c4 "预留空间(pctfree) G", t.c5 "浪费空间 k"
,round(T.C5/T.C2,2) "碎片百分比 %" FROM T 
WHERE 1=1 
and C5 > 0 
--AND TABLE_NAME  like 'table_name' 
ORDER BY 2 desc,6 DESC;

手动收集统计信息 作用是优化执行计划

-- 按表 期中名称自己替换
begin
  dbms_stats.gather_table_stats(
  ownname =>'ABC',
  tabname =>'table_name',
  -- estimate_percent =>100,
  estimate_percent => dbms_stats.auto_sample_size,
  method_opt =>'FOR ALL COLUMNS SIZE AUTO',
  degree=>24,
  cascade=>TRUE);
end;
-- 按schema
begin
  dbms_stats.gather_schema_stats(
  ownname =>'ABC',
  options =>'GATHER AUTO',
  estimate_percent => dbms_stats.auto_sample_size,
  -- method_opt =>'FOR ALL COLUMNS SIZE AUTO',
  degree=>24,
  cascade=>TRUE);
end;

锁、解锁

select b.owner,b.object_name,a.session_id,a.locked_mode,c.username,c.sid,c.serial#,c.logon_time,d.spid,d.program
 from v$locked_object a, dba_objects b,v$session c,v$process d
where b.object_id = a.object_id
and a.session_id = c.sid
and c.paddr=d.addr
order by c.logon_time
;
--- 解锁 杀oracle进程
alter system kill session 'sid,serial';
alter system kill session '3277,21721';
--如果通过linux脚本调用oracle存储过程，会报错ora—00031 
--先kill linux 服务器脚本然后 
ps -ef |grep  XX.sh
kill -9 230224（两个数字中的第一个）
--然后停止oracle进程
kill -9 spid (oracle 执行sql后的spid，select spid,osuser,s.program from v$session s,v$process p 
              where s.paddr=p.addr and s.sid=4126)