表空间相关

临时表空间相关

--临时表空间的使用率
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES_USED)/(1024*1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

--清理临时段
select ts#, name FROM v$tablespace;
select ts#, name from sys.ts$ ;
--下面的30是ts#
alter session set events 
'immediate trace name DROP_SEGMENTS level 30';

--查看临时表空间的使用
select
inst_id,
username,
session_num,
b.sql_id,
tablespace,
segtype,
sum(blocks)*8/1024/1024 size_in_gb,
b.SQL_TEXT
FROM
gV$TEMPSEG_USAGE a,v$sql  b
WHERE a.SQL_ID=b.SQL_ID(+) 
group by
inst_id,
username,
session_num,
b.sql_id,
tablespace,
segtype,b.SQL_TEXT
ORDER BY SIZE_IN_GB DESC

--查看临时表空间的使用
select a.username,
       a.sql_id,
       a.SEGTYPE,a.TABLESPACE,c.SQL_TEXT,
       sum(b.BYTES_USED)/1024/1024/1024||'G',
 sum(b.BYTES_FREE)/1024/1024/1024  from  
 V$TEMPSEG_USAGE  a  join  
V$TEMP_SPACE_HEADER b on   a.TABLESPACE=b.tablespace_name
        left join v$sql  c on a.SQL_ID=c.SQL_ID
        
       group by a.username,
       a.sql_id,
       a.SEGTYPE,a.TABLESPACE,c.SQL_TEXT

表空间查询

--表空间查询

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 4 DESC, 5 ASC;

收缩表空间

--收缩表空间-查询水位线
select
 tablespace_name,
 file_id,
 file_name DATA_FILE_NAME,
 Allocated_MBYTES,
 High_Water_Mark_MBYTES,
 FREE_MBYTES,
 trunc((FREE_MBYTES / Allocated_MBYTES) * 100, 2) "% Free",
 trunc(Allocated_MBYTES - High_Water_Mark_MBYTES, 2) Resizeble
  from (
        select 
         ddf.tablespace_name tablespace_name,
          ddf.file_id file_id,
          ddf.file_name file_name,
          ddf.bytes / 1024 / 1024 Allocated_MBYTES,
          trunc((ex.hwm * (dt.block_size)) / 1024 / 1024, 2) High_Water_Mark_MBYTES,
          FREE_MBYTES
          from dba_data_files ddf,
                dba_tablespaces dt,
                (
                 select file_id, sum(bytes / 1024 / 1024) FREE_MBYTES
                   from dba_free_space
                  group by file_id
                 ) free,
                (
                 select file_id, max(block_id + blocks) hwm
                   from dba_extents
                  group by file_id
                 ) ex
         where ddf.file_id = ex.file_id
           and ddf.tablespace_name = dt.tablespace_name
           and ddf.file_id = free.file_id(+)
         order by ddf.tablespace_name, ddf.file_id
        )
        where tablespace_name='RPT'
        ;
   --2收缩表空间     
        
select 'alter database datafile ''' || a.file_name || ''' resize ' ||
round(a.filesize - (a.filesize - c.hwmsize - 100) * 0.8) || 'M;',
a.filesize || 'M' as "数据文件的总大小",
c.hwmsize || 'M' as "数据文件的实用大小"
from (select file_id, file_name, round(bytes / 1024 / 1024) as filesize
from dba_data_files) a,
(select file_id, round(max(block_id) * 8 / 1024) as HWMsize
from dba_extents
group by file_id) c
where a.file_id = c.file_id
and a.filesize - c.hwmsize > 100
;       
        
--查看占用段
select segment_name, block_id from dba_extents where tablespace_name='RPT' order by block_id desc
--降低表的水位线
alter table 表 shrink space; 
ALTER DATABASE DATAFILE '/data/t_data05.dbf' RESIZE 63M;

long类型字段转换

--带'<','>'无法转换
--xml提取
 with xml as (
select dbms_xmlgen.getxmltype('select table_name, partition_name, high_value from dba_tab_partitions where table_name = '''||tabName||'''') as x
from dual
)
select
extractValue(rws.object_value, '/ROW/PARTITION_NAME')  partition_name
from xml x,
table(xmlsequence(extract(x.x, '/ROWSET/ROW'))) rws
where to_char(substr(extractValue(rws.object_value, '/ROW/HIGH_VALUE'),11,10)) >=to_char(add_months(trunc(sysdate,'mm'),-1),'yyyy-mm-dd')
and  to_char(substr(extractValue(rws.object_value, '/ROW/HIGH_VALUE'),11,10))<=to_char(add_months(sysdate-1,+1),'yyyy-mm-dd')
 ORDER BY substr(extractValue(rws.object_value, '/ROW/HIGH_VALUE'),11,10)