查表空间使用率情况

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) fWHERE 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 twhere 1=1and 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;-- 按schemabegin  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 dwhere b.object_id = a.object_idand a.session_id = c.sidand c.paddr=d.addrorder 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.shkill -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)