--查看表空间使用情况
SELECT
NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
, kbytes_alloc kbytes
, kbytes_alloc-NVL(kbytes_free,0) used
, NVL(kbytes_free,0) free
, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
, NVL(largest,0) largest
FROM ( SELECT SUM(bytes)/1024 Kbytes_free
, MAX(bytes)/1024 largest
, tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name
) a
, ( SELECT SUM(bytes)/1024 Kbytes_alloc
, tablespace_name
FROM sys.dba_data_files
GROUP BY tablespace_name
) b
WHERE a.tablespace_name (+) = b.tablespace_name
order by pct_used desc
/
--查询表空间包含的对象
SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024 From dba_segments t WHERE t.tablespace_name = 'TBS_DEFAULT'
GROUP BY t.owner,t.segment_name
ORDER BY SUM(bytes) desc;
--查询表空间包含的分区表对象
SELECT t.owner, t.segment_name,t.partition_name,SUM(bytes)/1024/1024 From dba_segments t WHERE t.tablespace_name = 'TBS_DEFAULT'
GROUP BY t.owner,t.segment_name,t.partition_name
ORDER BY SUM(bytes) desc;
--查询表空间包含的复合分区表对象
SELECT t.owner, t.segment_name,s.partition_name,SUM(bytes)/1024/1024 From dba_segments t ,dba_tab_subpartitions s
WHERE t.partition_name = s.subpartition_name AND t.segment_name = s.table_name
AND t.segment_name = 'TAB_DEFAULT'
GROUP BY t.owner,t.segment_name,s.partition_name
ORDER BY SUM(bytes) desc;
--删除表空间
SELECT 'drop tablespace '|| t.TABLESPACE_NAME ||' including contents and datafiles;'
FROM user_tablespaces t
WHERE t.TABLESPACE_NAME LIKE 'TBS_DEFAULT%';
SELECT 'TRUNCATE TABLE '||t.segment_name||' ;',SUM(bytes)/1024 From dba_segments t
WHERE t.owner = 'USER_DEFAULT'
AND t.segment_type = 'TABLE'
GROUP BY t.segment_name ORDER BY SUM(bytes)/1024 DESC;
SELECT 'alter tablespace '||t.tablespace_name||' coalesce;' From user_tablespaces t
WHERE t.TABLESPACE_NAME LIKE '%TBS_DEFAULT%';
select'alter table '||table_name||' enable row movement;','alter table '||table_name||' shrink space;'||chr(10)from user_tables;
--表空间RESIZE
SELECT 'alter database datafile '''||t.FILE_NAME||''' RESIZE 31G;',t.BYTES/1024/1024/1024 From dba_data_files t
WHERE t.TABLESPACE_NAME LIKE 'TBS_DEFAULT%' ;
--表空间新增数据文件
SELECT 'alter TABLESPACE "'||t.tablespace_name||'" add DATAFILE ''/app/oradata/tbs_index/'||t.tablespace_name||'_02.dbf'' SIZE 2000M;' From dba_tablespaces t
WHERE t.tablespace_name LIKE 'TBS_DEFAULT%';
--查看TEMP表空间谁在使用
SELECT SE.USERNAME,
SE.SID,
SE.SERIAL#,
SU.EXTENTS,
SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)) AS SPACE,
TABLESPACE,
SEGTYPE,
SQL_TEXT
FROM V$SORT_USAGE SU, V$PARAMETER P, V$SESSION SE, V$SQL S
WHERE P.NAME = 'db_block_size'
AND SU.SESSION_ADDR = SE.SADDR
AND S.HASH_VALUE = SU.SQLHASH
AND S.ADDRESS = SU.SQLADDR
ORDER BY SE.USERNAME, SE.SID;
--TEMP表空间管理
alter tablespace temp add tempfile '/u0/oradata/sm/temp02.dbf' size 3096M;
alter database tempfile '/ecapdata1/ecap/temp02.dbf' RESIZE 8G;
--查询表空间包含的对象及压缩状态
SELECT t.owner, t.segment_name,ta.PARTITIONED,ta.COMPRESSION,tp.compression,SUM(bytes)/1024/1024 From dba_segments t,dba_tables ta,dba_tab_partitions tp
WHERE t.tablespace_name = 'TBS_DEFAULT'
AND t.OWNER = ta.OWNER
AND tp.table_owner(+) = t.owner
AND tp.table_name(+) = t.segment_name
AND t.segment_name = ta.TABLE_NAME
GROUP BY t.owner,t.segment_name,ta.PARTITIONED,ta.COMPRESSION,tp.compression
ORDER BY SUM(bytes) DESC;
--查看表空间总使用率
SELECT
kbytes_alloc kbytes
, kbytes_alloc-NVL(kbytes_free,0) used
, NVL(kbytes_free,0) free
, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
, NVL(largest,0) largest
FROM ( SELECT SUM(bytes)/1024 Kbytes_free
, MAX(bytes)/1024 largest
FROM sys.dba_free_space
) a
, ( SELECT SUM(bytes)/1024 Kbytes_alloc
FROM sys.dba_data_files
) b
order by pct_used desc
/
--检测高水位
dbms_space.asa_recommendations();
10G:
--回收HWM
alter table &table shrink space
--重组该表中现有的行并回收HWM
alter table &table shrink space cascade;
*计算datafile可以resize收缩的空间.
<pre>
col name for a40
col resizecmd for a80
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,dba_data_files d ,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
AND a.FILE# = d.FILE_ID AND d.TABLESPACE_NAME = 'TBS_MESSAGE_LOG'
order by 5
/
</pre>
select username,user,session_num,tablespace,contents,segtype,blocks*8/1024 size_mb from v$tempseg_usage;
*计算tempfile可以resize的空间
<pre>
select 'alter database tempfile '''||a.name ||''' reize '||b.siz||'M;' from v$tempfile a,
(select tmsize.maxblk*bk.value/1024/1024 siz from
(select nvl(max(segblk#),128) maxblk from v$sort_usage) tmsize,
(select value From v$parameter where name = 'db_block_size') bk) b
/
</pre>
SELECT
NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
, kbytes_alloc kbytes
, kbytes_alloc-NVL(kbytes_free,0) used
, NVL(kbytes_free,0) free
, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
, NVL(largest,0) largest
FROM ( SELECT SUM(bytes)/1024 Kbytes_free
, MAX(bytes)/1024 largest
, tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name
) a
, ( SELECT SUM(bytes)/1024 Kbytes_alloc
, tablespace_name
FROM sys.dba_data_files
GROUP BY tablespace_name
) b
WHERE a.tablespace_name (+) = b.tablespace_name
order by pct_used desc
/
--查询表空间包含的对象
SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024 From dba_segments t WHERE t.tablespace_name = 'TBS_DEFAULT'
GROUP BY t.owner,t.segment_name
ORDER BY SUM(bytes) desc;
--查询表空间包含的分区表对象
SELECT t.owner, t.segment_name,t.partition_name,SUM(bytes)/1024/1024 From dba_segments t WHERE t.tablespace_name = 'TBS_DEFAULT'
GROUP BY t.owner,t.segment_name,t.partition_name
ORDER BY SUM(bytes) desc;
--查询表空间包含的复合分区表对象
SELECT t.owner, t.segment_name,s.partition_name,SUM(bytes)/1024/1024 From dba_segments t ,dba_tab_subpartitions s
WHERE t.partition_name = s.subpartition_name AND t.segment_name = s.table_name
AND t.segment_name = 'TAB_DEFAULT'
GROUP BY t.owner,t.segment_name,s.partition_name
ORDER BY SUM(bytes) desc;
--删除表空间
SELECT 'drop tablespace '|| t.TABLESPACE_NAME ||' including contents and datafiles;'
FROM user_tablespaces t
WHERE t.TABLESPACE_NAME LIKE 'TBS_DEFAULT%';
SELECT 'TRUNCATE TABLE '||t.segment_name||' ;',SUM(bytes)/1024 From dba_segments t
WHERE t.owner = 'USER_DEFAULT'
AND t.segment_type = 'TABLE'
GROUP BY t.segment_name ORDER BY SUM(bytes)/1024 DESC;
SELECT 'alter tablespace '||t.tablespace_name||' coalesce;' From user_tablespaces t
WHERE t.TABLESPACE_NAME LIKE '%TBS_DEFAULT%';
select'alter table '||table_name||' enable row movement;','alter table '||table_name||' shrink space;'||chr(10)from user_tables;
--表空间RESIZE
SELECT 'alter database datafile '''||t.FILE_NAME||''' RESIZE 31G;',t.BYTES/1024/1024/1024 From dba_data_files t
WHERE t.TABLESPACE_NAME LIKE 'TBS_DEFAULT%' ;
--表空间新增数据文件
SELECT 'alter TABLESPACE "'||t.tablespace_name||'" add DATAFILE ''/app/oradata/tbs_index/'||t.tablespace_name||'_02.dbf'' SIZE 2000M;' From dba_tablespaces t
WHERE t.tablespace_name LIKE 'TBS_DEFAULT%';
--查看TEMP表空间谁在使用
SELECT SE.USERNAME,
SE.SID,
SE.SERIAL#,
SU.EXTENTS,
SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)) AS SPACE,
TABLESPACE,
SEGTYPE,
SQL_TEXT
FROM V$SORT_USAGE SU, V$PARAMETER P, V$SESSION SE, V$SQL S
WHERE P.NAME = 'db_block_size'
AND SU.SESSION_ADDR = SE.SADDR
AND S.HASH_VALUE = SU.SQLHASH
AND S.ADDRESS = SU.SQLADDR
ORDER BY SE.USERNAME, SE.SID;
--TEMP表空间管理
alter tablespace temp add tempfile '/u0/oradata/sm/temp02.dbf' size 3096M;
alter database tempfile '/ecapdata1/ecap/temp02.dbf' RESIZE 8G;
--查询表空间包含的对象及压缩状态
SELECT t.owner, t.segment_name,ta.PARTITIONED,ta.COMPRESSION,tp.compression,SUM(bytes)/1024/1024 From dba_segments t,dba_tables ta,dba_tab_partitions tp
WHERE t.tablespace_name = 'TBS_DEFAULT'
AND t.OWNER = ta.OWNER
AND tp.table_owner(+) = t.owner
AND tp.table_name(+) = t.segment_name
AND t.segment_name = ta.TABLE_NAME
GROUP BY t.owner,t.segment_name,ta.PARTITIONED,ta.COMPRESSION,tp.compression
ORDER BY SUM(bytes) DESC;
--查看表空间总使用率
SELECT
kbytes_alloc kbytes
, kbytes_alloc-NVL(kbytes_free,0) used
, NVL(kbytes_free,0) free
, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
, NVL(largest,0) largest
FROM ( SELECT SUM(bytes)/1024 Kbytes_free
, MAX(bytes)/1024 largest
FROM sys.dba_free_space
) a
, ( SELECT SUM(bytes)/1024 Kbytes_alloc
FROM sys.dba_data_files
) b
order by pct_used desc
/
--检测高水位
dbms_space.asa_recommendations();
10G:
--回收HWM
alter table &table shrink space
--重组该表中现有的行并回收HWM
alter table &table shrink space cascade;
*计算datafile可以resize收缩的空间.
<pre>
col name for a40
col resizecmd for a80
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024 ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
'alter database datafile '''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,dba_data_files d ,
(select file_id,max(block_id+blocks-1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
AND a.FILE# = d.FILE_ID AND d.TABLESPACE_NAME = 'TBS_MESSAGE_LOG'
order by 5
/
</pre>
select username,user,session_num,tablespace,contents,segtype,blocks*8/1024 size_mb from v$tempseg_usage;
*计算tempfile可以resize的空间
<pre>
select 'alter database tempfile '''||a.name ||''' reize '||b.siz||'M;' from v$tempfile a,
(select tmsize.maxblk*bk.value/1024/1024 siz from
(select nvl(max(segblk#),128) maxblk from v$sort_usage) tmsize,
(select value From v$parameter where name = 'db_block_size') bk) b
/
</pre>