oracle表空間的整理

--檢視錶空間使用情況

SELECT UPPER(F.TABLESPACE_NAME)"表空間名",
　　D.TOT_GROOTTE_MB "表空間大小(M)",
　　D.TOT_GROOTTE_MB-F.TOTAL_BYTES "已使用空間(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')||'%'"使用比",
　　F.TOTAL_BYTES "空閒空間(M)",
　　F.MAX_BYTES "最大塊(M)"
　　FROM(SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,
　　ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME)F,
　　(SELECT DD.TABLESPACE_NAME,
　　ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME)D
　　WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME
　　ORDER BY 1

--檢查表空間資料檔案佔用

select
       b.file_name 物理檔名,
       b.tablespace_name 表空間,
       b.bytes/1024/1024大小M,
      (b.bytes-sum(nvl(a.bytes,0)))/1024/1024  已使用M,
      substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5)  利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;

-- 檢視錶空間高水位線的位置

    只有表空間高水位線上的才能回收。

SELECT
	'alter database datafile ''' || a.file_name || ''' resize ' || round( a.filesize - ( a.filesize - c.hwmsize - 100 ) * 0.8 ) || 'M;',
a.filesize,
c.hwmsize 
FROM
	( SELECT file_id, file_name, round( bytes / 1024 / 1024 ) filesize FROM dba_data_files ) a,
	( SELECT file_id, round( max( block_id ) * 8 / 1024 ) HWMsize FROM dba_extents GROUP BY file_id ) c 
WHERE
	a.file_id = c.file_id 
	AND a.filesize - c.hwmsize > 100

--檢視資料檔案裡面段的分佈情況

主要關注資料檔案的外緣存的是什麼

SELECT
	* 
FROM
	(
	SELECT
		owner,
		segment_name,
		segment_type,
		EXTENT_ID,
		file_id,
		block_id 
	FROM
		dba_extents 
	WHERE
		file_id = ( SELECT file_id FROM dba_data_files WHERE file_name = '/u01/app/oracle/oradata/orcl/users01.dbf' ) 
	ORDER BY
	block_id DESC 
	)

-- 檢視錶空間高水位線

SELECT
'alter database datafile ''' || a.file_name || ''' resize ' || round( a.filesize - ( a.filesize - c.hwmsize - 100 ) * 0.8 ) || 'M;',
a.filesize,
c.hwmsize 
FROM
( SELECT file_id, file_name, round( bytes / 1024 / 1024 ) filesize FROM dba_data_files ) a,
( SELECT file_id, round( max( block_id ) * 8 / 1024 ) HWMsize FROM dba_extents GROUP BY file_id ) c 
WHERE
a.file_id = c.file_id 
AND a.filesize - c.hwmsize > 100

--檢視錶初始化大小和實際大小

SELECT
	owner,
	TABLE_NAME,
	initial_extent / 1048576 表初始化大小_MB,
	num_rows * avg_row_len / 1048576 表實際大小_MB,
	tablespace_name 
FROM
	dba_tables 
WHERE
	owner = 'HIS_TEST' 
	AND initial_extent IS NOT NULL 
ORDER BY
	initial_extent DESC

-- 修改表初始化大小並移動到其他表空間

SELECT
	'alter table ' || owner || '.' || table_name || 'move tablespace ' || tablespace_name || 'storage(initial 64k next 32k);' 
FROM
	dba_tables 
WHERE
	owner = 'HIS_TEST' 
	AND initial_extent > 65536

--收集表統計資訊

exec dbms_stats.gather_table_stats(ownname=>'HIS_TEST',tabname=>'MW_OUTSHEETS_DETAILS');

--修改索引初始化大小並移動到其他表空間

SELECT
'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD tablespace  ' || tablespace_name || '  STORAGE(INITIAL 64K NEXT 32K);' 
FROM
dba_indexes 
WHERE
owner = 'HIS_TEST' 
AND initial_extent > 65536;

--修改分割槽表初始化大小並移動到其他表空間

SELECT
'ALTER table ' || table_owner || '.' || table_name || ' MOVE PARTITION ' || PARTITION_NAME || ' STORAGE(INITIAL 64K NEXT 32K);' 
FROM
DBA_tab_PARTITIONS 
WHERE
table_owner = 'HIS_TEST' 
AND initial_extent > 65536;

--修改分割槽索引初始化大小並移動到其他表空間

SELECT
'ALTER INDEX ' || index_owner || '.' || index_name || ' REBUILD PARTITION ' || PARTITION_NAME || ' STORAGE(INITIAL 64K NEXT 32K);' 
FROM
DBA_ind_PARTITIONS 
WHERE
index_owner = 'HIS_TEST' 
AND initial_extent > 65536;