ITPub博客

首页 > 数据库 > Oracle > oracle表空间的整理

oracle表空间的整理

原创 Oracle 作者:czxin788 时间:2020-03-31 13:55:28 0 删除 编辑

--查看表空间使用情况

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;




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28916011/viewspace-2683544/,如需转载,请注明出处,否则将追究法律责任。

全部评论

注册时间:2014-06-03

  • 博文量
    201
  • 访问量
    641581