表空间管理

--查看表空间使用情况
                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>