缩减表空间碎片

Shrink 操作需满足表空间是本地管理和自动段空间管理（10g、11g默认就是这样），以下情况不能用shrink：
IOT索引组织表
用rowid创建的物化视图的基表
带有函数索引的表
SECUREFILE 大对象
压缩表
未分配段空间的空表不能shrink
表资源被占用时不能shrink

相比alter table move，Shrink的进步是：
1.可在线执行
2.可使用参数cascade，同时收缩表上的索引
3.执行后不会导致索引失效
4.可避免alter table move执行过程中占用很多变空间（如果表10G大小，那alter table move差不多还得需要10G空间才能执行）。
------------------------------------------------------------
--调用整理碎片的过程，第2个参数传递空值表示按用户，然后ALTER database datafile '' RESIZE
CALL PRO_OPTI('USER_NAME','');
--查看ROW_MOVEMENT是否开启
SELECT T.OWNER,T.TABLE_NAME,T.ROW_MOVEMENT,'ALTER TABLE '||T.OWNER||'.'||T.TABLE_NAME||' DISABLE ROW MOVEMENT;' FROM DBA_TABLES T
WHERE T.OWNER='USER_NAME'
AND T.TABLE_NAME=DECODE('',NULL,T.TABLE_NAME,'TABLE_NAME')
AND T.TABLE_NAME IN (select A.segment_name from SYS.dba_segments a where a.owner = T.OWNER);

--为procedure赋予权限
grant select on dba_segments to user_name;
grant select on DBA_TABLES to user_name;
--备份row movement
CREATE TABLE SRP.TAB_ROW_MOVEMENT AS SELECT T.OWNER,T.TABLE_NAME,T.ROW_MOVEMENT FROM DBA_TABLES T WHERE T.OWNER='user_name' ORDER BY T.TABLE_NAME;

--查看表空间 并复制查询结果到文件中
select a.tablespace_name "表空间名",
round(b.total/1024/1024/1024,2) "表空间大小G",
round(b.maxzise/1024/1024/1024,2) "表空间最大大小G",
round(a.free/1024/1024/1024,2) "表空间剩余大小G",
round((b.total-a.free)/1024/1024/1024,2) "表空间使用大小G",
round((b.total-a.free)/total,4)*100 "使用率%"
from (select tablespace_name,sum(bytes) free from dba_free_space group by tablespace_name) a,
(select tablespace_name,sum(bytes) total,sum(case when maxbytes<bytes then bytes else maxbytes end) maxzise from dba_data_files group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by a.tablespace_name;

--让表空间释放物理存储空间
ALTER database datafile 'C:\app\Administrator\USER_NAME\TBS_TY01.dbf' RESIZE 524288000;
ALTER database datafile 'C:\app\Administrator\USER_NAME\TSDAT01.dbf' RESIZE 5G;

SELECT * FROM DBA_TAB_COLUMNS T WHERE T.OWNER='USER_NAME' AND T.TABLE_NAME LIKE 'REGEX%' AND T.COLUMN_NAME='DATADT';
SELECT COUNT(DISTINCT T.TABLE_NAME) FROM DBA_TAB_COLUMNS T WHERE T.OWNER='USER_NAME' AND T.TABLE_NAME LIKE 'REGEX%';

SELECT 'SELECT '''||T.TABLE_NAME||''' AS TABNAME,DATADT FROM '||T.OWNER||'.'||T.TABLE_NAME||' T GROUP BY T.DATADT ORDER BY T.DATADT DESC;' FROM DBA_TABLES T WHERE T.OWNER='USER_NAME' AND T.TABLE_NAME LIKE 'REGEX%';

------------------------------------------------------------
--修改表空间数据文件的大小,释放磁盘空间
ALTER database datafile 'C:\app\Administrator\USER_NAME\TSDAT01.dbf' RESIZE size;
--缩减临时表空间
alter tablespace temp_tablespace_name shrink space;
--对表更换表空间
alter table table_name move tablespace tablespace_name;
------------------------------------
--按照用户清理表空间碎片
--备份用户 exp无parallel,direct=y 直接路径导出
exp USER_NAME/USER_NAME@USER_NAME tables=S03_LOAN_BNW_S64_HIS_S feedback=100000 buffer=64000 file=d:\backup\S03_LOAN_BNW_S64_HIS_S.dmp log=d:\backup\S03_LOAN_BNW_S64_HIS_S.log

--查询确定需要优化的字段类型
select distinct t.DATA_TYPE from user_tab_columns t order by 1;

--查询表大小 含clob字段的表应该再加all_lobs中的段的大小
select segment_name, bytes/1024/1024 as "size(M)" from user_segments t where segment_type = 'TABLE' and substr(t.segment_name,1,4)<>'BIN$' order by 2 DESC;

--查询clob字段的大小 不能直接查询数据字典user_segments.bytes
select a.owner, a.segment_name,b.TABLE_NAME, a.segment_type,sum(a.bytes)/1024/1024 as "size(M)" from dba_segments a, all_lobs b
where a.segment_name = b.segment_name and a.owner in ('USER_NAME') group by a.owner, a.segment_name,b.TABLE_NAME, a.segment_type order by b.TABLE_NAME;

--查询是否有死锁或资源等待 kill -9在操作系统层面杀死进程
select s.sid,s.SERIAL#,p.SPID,o.object_name,machine,s.OSUSER,s.TERMINAL,s.ACTION,s.STATUS,s.STATE
,s.SERVICE_NAME,'alter system kill session '''||s.sid||','||s.SERIAL#||''';',
case when (select instr(t.BANNER,'Windows') as banner from v$version t where lower(t.BANNER) like '%windows%')>=0 then
'orakill '||(select instance_name from v$instance)||' '||p.SPID
else
'kill -9 '||p.SPID
end
from v$locked_object l,dba_objects o,v$session s,v$process p where l.OBJECT_ID=o.object_id and l.SESSION_ID=s.SID
and s.PADDR=p.ADDR;
------------------------------------------------------------
set heading off
set echo off
set feedback off
set pagesize 0
set trimspool on
set trimout on
SET LINESIZE 2500

--row_movement
spool E:\sql\optimize\row_movement.sql
select 'spool E:\sql\optimize\log\row_movement.log' from dual;
select 'alter table '||T.TABLE_NAME||' enable row movement;' from user_tables t where t.TABLESPACE_NAME is not null;
select 'spool off' from dual;
spool off;

--table 不缩减索引占用的表空间碎片
spool E:\sql\optimize\shrink_table.sql
select 'spool E:\sql\optimize\log\shrink_table.log' from dual;
select 'alter table '||T.TABLE_NAME||' shrink space;' from user_tables t where t.TABLESPACE_NAME is not null;
select 'spool off' from dual;
spool off;

--table 缩减索引占用的表空间碎片
spool E:\sql\optimize\shrink_table_index.sql
select 'spool E:\sql\optimize\log\shrink_table_index.log' from dual;
select 'alter table '||T.TABLE_NAME||' shrink space cascade;' from user_indexes t where t.index_type<>'LOB' and exists (select 1 from user_tables t1 where t1.TABLE_NAME=t.table_name and t1.TABLESPACE_NAME is not null);
select 'spool off' from dual;
spool off;

--clob 缩减clob字段占用的表空间碎片
spool E:\sql\optimize\shrink_clob.sql
select 'spool E:\sql\optimize\log\shrink_clob.log' from dual;
select 'alter table '||t.TABLE_NAME||' modify lob('||t.COLUMN_NAME||') (shrink space);' from user_tab_columns t where t.DATA_TYPE in ('CLOB');
select 'spool off' from dual;
spool off;

--恢复行迁移
spool E:\sql\optimize\row_movement_disable.sql
select 'spool E:\sql\optimize\log\row_movement_disable.log' from dual;
select 'alter table '||T.TABLE_NAME||' disable row movement;' from user_tables t;
select 'spool off' from dual;
spool off;

--行迁移是否开启
select t.ROW_MOVEMENT,t.TABLE_NAME from user_tables t where tablespace_name is not null order by 1;

--命令窗口cmd中执行
sqlplus USER_NAME/USER_NAME@USER_NAME
@E:\sql\optimize\row_movement.sql
@E:\sql\optimize\shrink_table.sql
@E:\sql\optimize\shrink_table_index.sql
@E:\sql\optimize\shrink_clob.sql
@E:\sql\optimize\row_movement_disable.sql
------------------------------------------------------------
--pl/sql整理空间碎片
CREATE OR REPLACE PROCEDURE PRO_OPTI(
V_OWNER VARCHAR2,
V_TABLE_NAME VARCHAR2
)
AS
TYPE TRec IS RECORD(
OWNER VARCHAR2(50),
TABLE_NAME VARCHAR2(50));
TYPE TArrTABLE IS TABLE OF TRec INDEX BY BINARY_INTEGER;
ArrTABLE TArrTABLE;
ArrTABLE1 TArrTABLE;
V_BLOCKS_BEFORE PLS_INTEGER;
V_BLOCKS_AFFTER PLS_INTEGER;
V_ALL_TABLE_NAME VARCHAR2(100);
CURSOR CUR_LSIT IS SELECT T.OWNER,T.TABLE_NAME FROM DBA_TABLES T
WHERE T.OWNER=V_OWNER
AND T.TABLE_NAME=DECODE(V_TABLE_NAME,NULL,T.TABLE_NAME,V_TABLE_NAME)
AND T.TABLE_NAME IN (select A.segment_name from SYS.dba_segments a where a.owner = T.OWNER);
POS NUMBER DEFAULT 1;
BEGIN
OPEN CUR_LSIT;
ArrTABLE := ArrTABLE1;
LOOP
FETCH CUR_LSIT INTO ArrTABLE(POS).OWNER,ArrTABLE(POS).TABLE_NAME;
EXIT WHEN CUR_LSIT%NOTFOUND;
POS:=POS+1;
END LOOP;
CLOSE CUR_LSIT;
FOR I IN 1 .. ArrTABLE.COUNT
LOOP
BEGIN
V_ALL_TABLE_NAME := ArrTABLE(I).OWNER || '.' || ArrTABLE(I).TABLE_NAME;
begin
select A.BLOCKS
INTO V_BLOCKS_BEFORE
from SYS.dba_segments a
where a.owner = ArrTABLE(I).OWNER
AND A.SEGMENT_NAME = ArrTABLE(I).TABLE_NAME;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE(SQLERRM||':'||'要整理的表' || V_ALL_TABLE_NAME || '不存在');
continue;
end;
EXECUTE IMMEDIATE ' alter table ' || V_ALL_TABLE_NAME ||
' enable row movement';
EXECUTE IMMEDIATE ' alter table ' || V_ALL_TABLE_NAME ||
' shrink space';
select A.BLOCKS
INTO V_BLOCKS_AFFTER
from SYS.dba_segments a
where a.owner = ArrTABLE(I).OWNER
AND A.SEGMENT_NAME = ArrTABLE(I).TABLE_NAME;
DBMS_OUTPUT.PUT_LINE('表' || V_ALL_TABLE_NAME || '整理前数据块<' ||
V_BLOCKS_BEFORE || '>整理后数据块<' ||
V_BLOCKS_AFFTER || '>');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM||':'||'整理表' || V_ALL_TABLE_NAME || '碎片失败');
END;
END LOOP;
END PRO_OPTI;

------------------------------------------------------------------

vi export_all.sh
#!/bin/sh
file_dir_s=/data/20160418/data/test
file_dir_t=/app/dep/data/S1
fftp -i -n<<!
open 1.1.1.1
user deptrans deptrans
binary
prompt off
passive
cd $file_dir_t
lcd $file_dir_s
mput *.txt
close
bye
!
echo 'ftp file transfer successfully!' >> export_all.log;

--2.for循环上传文件
vi export_test.sh
#!/bin/sh
file_dir_s=/data/20160418/data/test
file_dir_t=/app/dep/data/S1
for file_name in `ls $file_dir_s/*.txt |awk -F "/" '{print $6}'`
do
fftp -i -n<<!
open 1.1.1.1
user deptrans deptrans
binary
prompt off
passive
cd $file_dir_t
lcd $file_dir_s
put $file_name
close
bye
!
done;
echo 'ftp file transfer successfully!' >> export_test.log;