查表空间使用率情况
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 %"
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
;
表空间扩容
df -h 查磁盘
服务器数据文件路径:/oradata/abcd/
alter tablespace FDLSC add datafile '/oradata/abcd/ABCD.dbf' size 30000m;
表大小 ,碎片
体要查询的表的名称就可以
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.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
,ROUND ( (num_rows * avg_row_len / 1024)/1024/1024, 2) C3
,ROUND ( (blocks * 10 / 100) * 8/1024/1024, 2) C4
,ROUND (( blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100)/1024/1024, 2) C5
FROM user_tables
WHERE temporary = 'N'
and table_name = 'abd'
)
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
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
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
;
alter system kill session 'sid,serial';
alter system kill session '3277,21721';
ps -ef |grep XX.sh
kill -9 230224(两个数字中的第一个)
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)