oracle database用一段时间以后，硬盘空间会不够，更甚者硬盘会used 100%。(oracle表空间设置为自动扩展)。此时oracle会出现各种莫名其妙的错误，为保持oracle数据库的稳定，我们需要实时查看硬盘空间。 那么怎样缩小一些不用的表空间，以释放硬盘空间？
oracle常用的此类命令Alter database datafile(tempfile)，当然也可从toad等UI工具去操作。
ALTER DATABASE TEMPFILE '/opt/oracle/oradata/gctwp101/temp02.dbf' RESIZE 1229M;
如果盲目去操作，oracle会报ora-03297错误。表示在所定义的空间之后有数据存在，不能收缩。我们不得不查出{BANNED}{BANNED}{BANNED}最佳佳佳后的数据所处的位置， 然后表空间设置到稍比这位置大一点的大小。
1. 查该数据文件中数据处在{BANNED}{BANNED}{BANNED}最佳佳佳大位置
select max(block_id) from dba_extents where file_id=15;< /FONT>
max(block_id)
383497
查询file_id,也可在toad工具tablespace直接看到。
select file#,name from v$datafile;
2. 查出{BANNED}{BANNED}{BANNED}最佳佳佳大块位置
select 383497*8/1024 from dual;
2996.0703125
这说明该文件中{BANNED}{BANNED}{BANNED}最佳佳佳大使用块位于2996M与3000M之间，
3. 修改表空间(也可用toad工具界面修改)
ALTER DATABASE TEMPFILE '/opt/oracle/oradata/gctwp101/temp02.dbf' RESIZE 3000M;
--------
set verify off
col value format a20
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
set heading off feedback off termout off
spool /tmp/tmp_shrink_data_files.sql
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
spool off;
set heading on feedback on termout on
------
SELECT a.tablespace_name,
file_name,
c.VALUE / 1024 "Blk. size(Kb)",
CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "smallest(Mb) - HWM",
CEIL (blocks * c.VALUE / 1024 / 1024) "currsize(Mb)",
CEIL (blocks * c.VALUE / 1024 / 1024) - CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "savings(Mb)"
FROM dba_data_files a,
(SELECT file_id, MAX (block_id + blocks - 1) hwm
FROM dba_extents
GROUP BY file_id) b,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size') c
WHERE a.file_id = b.file_id(+)
and a.status !='INVALID'
order by 6;
-----To select Tablespace status before housekeep table 7777777(save the result to a file.txt )
SELECT data_file_count.tablesapce_name_var tablespace_name,data_file_count.data_file_size "Tablespace_Size(MB)",nvl(data_file_used.used_size,0) "Used(MB)",data_file_count.data_file_size-nvl(data_file_used.used_size,0) "Free(MB)",
to_char(round(nvl(data_file_used.used_size,0)/data_file_count.data_file_size*100,2),'90.99')||'%' "Used(%)"
FROM
(
SELECT C.tablesapce_name_var,SUM(C.datafile_size_var) AS data_file_size
FROM
(
SELECT
T.tablespace_name tablesapce_name_var,
decode(T.autoextensible,
'NO',round(SUM(T.BYTES)/(1024*1024),2),
'YES',round(SUM(T.maxbytes)/(1024*1024),2)
) datafile_size_var
FROM dba_data_files T
GROUP BY T.tablespace_name,T.autoextensible
) C
GROUP BY C.tablesapce_name_var
) data_file_count
,
(
SELECT b.tablespace_name tablesapce_name_var2,round(SUM(b.BYTES)/(1024*1024),2) used_size
FROM dba_segments b
GROUP BY b.tablespace_name
) data_file_used
WHERE data_file_used.tablesapce_name_var2(+)=data_file_count.tablesapce_name_var
ORDER BY 5 DESC;
-----To select ASM diskgroup status before housekeep table 7777777(save the result to a file.txt)
select name,total_mb,free_mb,round(((total_mb - free_mb)/total_mb)*100,2) "Used(%)" from v$asm_diskgroup order by 4;
-----To alter session parallel
set define off;
alter session set db_securefile=ignore;
alter session force parallel DML parallel 32;
alter session force parallel DDL parallel 32;
---------------Switch the user of the connected session---------------
alter session set current_schema=7777777;
---------------View currently connected username---------------
select SCHEMANAME from v$session where sid in (select distinct(sid) from v$mystat);
-----1. To ensure current table 7777777 uesed size(MB)
select segment_name,segment_type,round(SUM(BYTES)/(1024*1024),2) used_size_MB from dba_segments where owner='7777777' and segment_name='7777777' group by segment_name,segment_type order by 3 desc ;
-----2. To ensure current table 7777777 uesed size(MB)
select table_owner||'.'||table_name,partition_name,high_value,tablespace_name from dba_tab_partitions where table_NAME='7777777' and table_owner='7777777' ;
-----3. truncate 7777777 image partition(just keeep this current month data)
alter table 7777777 truncate partition SYS_P9425 drop storage;
alter table 7777777 drop partition SYS_P9425 update INDEXES ;
alter table 7777777 truncate partition SYS_P10130 drop storage;
alter table 7777777 drop partition SYS_P10130 update INDEXES ;
alter table 7777777 truncate partition SYS_P11265 drop storage;
alter table 7777777 drop partition SYS_P11265 update INDEXES ;
alter table 7777777 truncate partition SYS_P12449 drop storage;
alter table 7777777 drop partition SYS_P12449 update INDEXES ;
-----3.1 drop primary key
--alter table 7777777 drop constraint PK_7777777;
--drop INDEX PK_7777777 ;
-----3.2 shrink table storeage
ALTER TABLE 7777777 ENABLE ROW MOVEMENT;
ALTER TABLE 7777777 SHRINK SPACE COMPACT;
ALTER TABLE 7777777 SHRINK SPACE;
ALTER TABLE 7777777 disable ROW MOVEMENT;
-----3.2 shrink table storeage
alter table 7777777 deallocate unused keep 0;
-----3.3 rebuid primary key
--alter table 7777777 add constraint PK_7777777 primary key("CARD_NUMBER", "MTI", "MANAGE_TYPE", "INTERNAL_STAN", "SRC_DBSERV_ID", "DST_DBSERV_ID", "INTERNAL_TRANSMISSION_TIME");
-----4.resize datafile which relate with table 7777777(save the result to a file.txt)
SELECT
T.tablespace_name tablesapce_name_var,
t.file_id,
decode(T.autoextensible,
'NO',round(SUM(T.BYTES)/(1024*1024),2),
'YES',round(SUM(T.maxbytes)/(1024*1024),2)
) datafile_size_var
FROM dba_data_files T
where tablespace_name in (select tablespace_name from dba_tab_partitions where table_NAME='7777777' and table_owner='7777777')
GROUP BY T.tablespace_name,T.autoextensible,t.file_id
order by T.tablespace_name;
-----5.resize datafile which relate with table 7777777
select 'alter database datafile '||c.file_id||' resize 1024m;'
from
(
SELECT
T.tablespace_name tablesapce_name_var,
t.file_id,
decode(T.autoextensible,
'NO',round(SUM(T.BYTES)/(1024*1024),2),
'YES',round(SUM(T.maxbytes)/(1024*1024),2)
) datafile_size_var
FROM dba_data_files T
where tablespace_name in (select tablespace_name from dba_tab_partitions where table_NAME='7777777' and table_owner='7777777')
GROUP BY T.tablespace_name,T.autoextensible,t.file_id
order by T.tablespace_name
) C
where c.datafile_size_var>5120;
a="
select 'alter database datafile '||c.file_id||' resize 1024m;'
from
(
SELECT
T.tablespace_name tablesapce_name_var,
t.file_id,
decode(T.autoextensible,
'NO',round(SUM(T.BYTES)/(1024*1024),2),
'YES',round(SUM(T.maxbytes)/(1024*1024),2)
) datafile_size_var
FROM dba_data_files T
where tablespace_name in (select tablespace_name from dba_tables where table_NAME='7777777' and owner='7777777')
GROUP BY T.tablespace_name,T.autoextensible,t.file_id
order by T.tablespace_name
) C
where c.datafile_size_var>5120;
"
-----To select Tablespace status after housekeep table 7777777(save the result to a file.txt)
SELECT data_file_count.tablesapce_name_var tablespace_name,data_file_count.data_file_size "Tablespace_Size(MB)",nvl(data_file_used.used_size,0) "Used(MB)",data_file_count.data_file_size-nvl(data_file_used.used_size,0) "Free(MB)",
to_char(round(nvl(data_file_used.used_size,0)/data_file_count.data_file_size*100,2),'90.99')||'%' "Used(%)"
FROM
(
SELECT C.tablesapce_name_var,SUM(C.datafile_size_var) AS data_file_size
FROM
(
SELECT
T.tablespace_name tablesapce_name_var,
decode(T.autoextensible,
'NO',round(SUM(T.BYTES)/(1024*1024),2),
'YES',round(SUM(T.maxbytes)/(1024*1024),2)
) datafile_size_var
FROM dba_data_files T
GROUP BY T.tablespace_name,T.autoextensible
) C
GROUP BY C.tablesapce_name_var
) data_file_count
,
(
SELECT b.tablespace_name tablesapce_name_var2,round(SUM(b.BYTES)/(1024*1024),2) used_size
FROM dba_segments b
GROUP BY b.tablespace_name
) data_file_used
WHERE data_file_used.tablesapce_name_var2(+)=data_file_count.tablesapce_name_var
ORDER BY 5 DESC;
-----To select ASM diskgroup status after housekeep table 7777777(save the result to a file.txt)
select name,total_mb,free_mb,round(((total_mb - free_mb)/total_mb)*100,2) "Used(%)" from v$asm_diskgroup order by 4;
----------ASM reblanceUSED(%)
select nvl(a.name,'[candidate]'),b.path,b.name,b.total_mb,(b.total_mb - b.free_mb) used_mb,
case b.total_mb
when 0 then 0
else round ((1-(b.free_mb/b.total_mb))*100,2)
end pct_used
from v$asm_diskgroup a right outer join v$asm_disk b using(group_number)
where
a.name not like '%CAND%'
order by a.name,b.failgroup,b.path;
