TABLESPACE USAGE

Посмотреть, как табличные пространства связаны с файлами данных можно так:
(DBA_FREE_SPACE, позволяет определить, сколько свободного места осталось в нашем табличном пространстве.) 


BREAK ON tablespace_name SKIP 2
COMPUTE SUM OF allocated_bytes, free_bytes ON tablespace_name
COLUMN allocated_bytes FORMAT 9,999,999,999
COLUMN free_bytes FORMAT 9,999,999,999

SELECT a.tablespace_name,
       a.file_name,
       a.bytes allocated_bytes,
       b.free_bytes
 FROM dba_data_files a,
     (SELECT file_id, SUM(bytes) free_bytes
      FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
ORDER BY a.tablespace_name;



Информацию об использовании пространства в ТП
без учета autoextensible можно посмотреть так:

SELECT /* + RULE */
 df.tablespace_name "Tablespace",
 df.bytes / (1024 * 1024) "Size (MB)",
 SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
 Nvl(Round(SUM(fs.bytes) * 100 / df.bytes), 1) "% Free",
 Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name(+) = df.tablespace_name
 GROUP BY df.tablespace_name, df.bytes
UNION ALL
SELECT /* + RULE */
 df.tablespace_name tspace,
 fs.bytes / (1024 * 1024),
 SUM(df.bytes_free) / (1024 * 1024),
 Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
 Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name, bytes_free, bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name, bytes_free, bytes_used) df
 WHERE fs.tablespace_name(+) = df.tablespace_name
 GROUP BY df.tablespace_name, fs.bytes, df.bytes_free, df.bytes_used
 ORDER BY 5 DESC;

Или так:

SELECT /*+ first_rows */ d.tablespace_name "TS NAME",
NVL(a.bytes / 1024 / 1024, 0) "size MB",
NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 "Used MB",
NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "Used %",
a.autoext "Autoextend",
NVL(f.bytes, 0) / 1024 / 1024 "Free MB",
d.status "STAT",
a.count "# of datafiles",
d.contents "TS type",
d.extent_management "EXT MGMT",
d.segment_space_management "Seg Space MGMT"
FROM sys.dba_tablespaces d,
    
     (select tablespace_name,
             sum(bytes) bytes,
             count(file_id) count,
             decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
       from dba_data_files group by tablespace_name) a,
    
     (select tablespace_name,
             sum(bytes) bytes
       from dba_free_space
       group by tablespace_name) f
       WHERE d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = f.tablespace_name(+)
       AND NOT d.contents = 'UNDO'
       AND NOT (d.extent_management = 'LOCAL'
       AND d.contents = 'TEMPORARY')
       AND d.tablespace_name like '%%'
    
       UNION ALL
    
       SELECT d.tablespace_name, NVL(a.bytes / 1024 / 1024, 0),
       NVL(t.bytes, 0)/1024/1024, NVL(t.bytes / a.bytes * 100, 0),
       a.autoext,
       (NVL(a.bytes ,0)/1024/1024 - NVL(t.bytes, 0)/1024/1024),
       d.status,
       a.count,
       d.contents,
       d.extent_management,
       d.segment_space_management
       FROM sys.dba_tablespaces d,
            (select tablespace_name,
                    sum(bytes) bytes,
                    count(file_id) count,
                    decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
                    from dba_temp_files
             group by tablespace_name) a,
             (select ss.tablespace_name ,
                     sum((ss.used_blocks*ts.blocksize)) bytes
                     from gv$sort_segment ss, sys.ts$ ts
                     where ss.tablespace_name = ts.name
                     group by ss.tablespace_name) t
                     WHERE d.tablespace_name = a.tablespace_name(+)
                     AND d.tablespace_name = t.tablespace_name(+)
                     AND d.extent_management = 'LOCAL'
                     AND d.contents = 'TEMPORARY'
                     and d.tablespace_name like '%%'
             
              UNION ALL
             
              SELECT d.tablespace_name,
                            NVL(a.bytes / 1024 / 1024, 0),
                            NVL(u.bytes, 0) / 1024 / 1024,
                            NVL(u.bytes / a.bytes * 100, 0),
                            a.autoext,
                            NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024,
                            d.status,
                            a.count,
                            d.contents,
                            d.extent_management,
                            d.segment_space_management
                         FROM sys.dba_tablespaces d,
                              (SELECT tablespace_name, SUM(bytes) bytes,
                               COUNT(file_id) count,
                               decode(sum(decode(autoextensible, 'NO', 0, 1)),0, 'NO', 'YES') autoext
                                 FROM dba_data_files
                                 GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes
                                                                  FROM (SELECT tablespace_name,sum (bytes) bytes,status
                                                                        from dba_undo_extents
                                                                        WHERE status ='ACTIVE'
                                                                        group by tablespace_name,status

UNION ALL

SELECT tablespace_name,
sum(bytes) bytes,
status from dba_undo_extents
WHERE status ='UNEXPIRED'
group by tablespace_name,status )

group by tablespace_name ) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.contents = 'UNDO'
AND d.tablespace_name LIKE '%%' ORDER BY 1

Учесть autoextensible можно так:

set lines 132
set pages 105
set pause off
set echo off
set feedb on

column "TOTAL ALLOC (MB)"      format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)"             format 9,999,990.00
column "FREE (MB)"             format 9,999,990.00
column "% USED"                format 990.00

select
   a.tablespace_name,
   a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
   a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
   nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
   (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from
   (select
      tablespace_name,
      sum(bytes) physical_bytes,
      sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
    from
      dba_data_files
    group by
      tablespace_name ) a,
   (select
      tablespace_name,
      sum(bytes) tot_used
    from
      dba_segments
    group by
      tablespace_name ) b
where
   a.tablespace_name = b.tablespace_name (+)
and
   a.tablespace_name not in
   (select distinct
       tablespace_name
    from
       dba_temp_files)
and
   a.tablespace_name not like 'UNDO%'
order by 1;

Или так:

set pages 999
set lines 400

SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;



Иногда может наблюдаться замедление работы запросов при обращении к DBA_FREE_SPACE, а также появление чрезмерного ожидания операций ввода-вывода.
Для решения этой проблемы нужно:

Удалить содержимое корзины

connect / as sysdba
select count(*) from dba_recyclebin;
purge dba_recyclebin;

Собрать статистику для X$KTFBUE

connect / as sysdba
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SYS', TABNAME => 'X$KTFBUE',ESTIMATE_PERCENT=>100);

Проверяем:

column owner format a6
column table_name format a10
column last_anal format a10
SELECT owner,  table_name,  last_analyzed FROM dba_tab_statistics WHERE table_name='X$KTFBUE';

Начиная с версии 11g можно воспользоваться следующим представлением:

Размер табличных пространств в блоках
select * from DBA_TABLESPACE_USAGE_METRICS;

Переводим блоки в Гигабайты

SELECT tbm.TABLESPACE_NAME,
   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_FREE_SIZE_GB,
   tbm.USED_PERCENT
FROM dba_tablespace_usage_metrics tbm
     join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME
--WHERE tbm.tablespace_name = 'tablespace_name'

В мегабайтах

SELECT a.tablespace_name,
  ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
  ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
  ROUND(a.used_percent, 2) AS "USED_PERCENT"
  FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
  DBA_TABLESPACES b
  ON a.tablespace_name = b.tablespace_name;

Можно связать с представлением dba_tablespace_thresholds:

Для версии 11gR1:

SELECT m.tablespace_name,
    round(max(m.used_percent),1) PERCM,
    round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
    round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
    round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
    round(max(m.used_space*t.block_size/1024/1024),1) USED,
    round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
    round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,   
    count(distinct d.file_id) DBF_NO,
    max(to_number(tt.warning_value)) WARN,
    max(to_number(tt.critical_value)) CRIT,
    max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM  dba_tablespace_usage_metrics m,
      dba_tablespaces t,
      dba_data_files d,
      dba_thresholds tt
WHERE m.tablespace_name=t.tablespace_name
  AND d.tablespace_name=t.tablespace_name
  AND tt.metrics_name='Tablespace Space Usage'
  AND tt.object_name is null
GROUP BY m.tablespace_name
ORDER BY 2 desc;


Для версии 11gR2 и выше:

SELECT m.tablespace_name,
    round(max(m.used_percent),1) PERCM,
    round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
    round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
    round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
    round(max(m.used_space*t.block_size/1024/1024),1) USED,
    round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
    round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,    
    count(distinct d.file_id) DBF_NO,
    max(to_number(tt.warning_value)) WARN,
    max(to_number(tt.critical_value)) CRIT,
    max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM  dba_tablespace_usage_metrics m,
      dba_tablespaces t,
      dba_data_files d,
      dba_tablespace_thresholds tt
WHERE m.tablespace_name=t.tablespace_name
  AND d.tablespace_name=t.tablespace_name
  AND tt.tablespace_name=d.tablespace_name
  AND tt.metrics_name='Tablespace Space Usage'
GROUP BY m.tablespace_name
ORDER BY 2 desc;

Исторические данные по росту ТП из AWR

В блоках:

select TABLESPACE_ID, NAME, TABLESPACE_SIZE, TABLESPACE_USEDSIZE, RTIME
from DBA_HIST_TBSPC_SPACE_USAGE, v$tablespace
where TABLESPACE_ID=TS#
order by RTIME, dba_hist_tbspc_space_usage.tablespace_id;

В мегабайтах:

select a.TABLESPACE_ID,
       c.NAME,
       ROUND((a.TABLESPACE_USEDSIZE * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
       ROUND((a.TABLESPACE_SIZE * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
       a.RTIME
from   DBA_HIST_TBSPC_SPACE_USAGE a,
       DBA_TABLESPACES b,
       v$tablespace c
where  a.TABLESPACE_ID=c.TS#
   and c.name = b.tablespace_name
order by a.RTIME, a.tablespace_id;

Исторические данные роста базы данных:

select SUM(ROUND((a.TABLESPACE_SIZE * b.block_size)/1024/1024, 2)) AS "DATABASE_SIZE(MB)",
       a.RTIME
from   DBA_HIST_TBSPC_SPACE_USAGE a,
       DBA_TABLESPACES b,
       v$tablespace c
where  a.TABLESPACE_ID=c.TS#
   and c.name = b.tablespace_name
group by a.RTIME
order by a.RTIME;

Только это будет без  redo_size from sys.v_$log  и controlfile_size from v$controlfile





А общий размер базы будет таким:

select
( select sum(bytes)/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) "Size in MB"
from
dual


Или в гигабайтах:

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

Создание табличных пространств:

Файловая система:

create tablespace TEST
 datafile '/u02/oradata/TESTDB/test_01.dbf' size 128M autoextend on next 128M maxsize unlimited,
          '/u02/oradata/TESTDB/test_02.dbf' size 128M autoextend on next 128M maxsize unlimited,
          '/u02/oradata/TESTDB/test_03.dbf' size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;


ASM:

create tablespace TEST
 datafile  '+DATA' size 128M autoextend on next 128M maxsize unlimited,
           '+DATA' size 128M autoextend on next 128M maxsize unlimited,
           '+DATA' size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;


OMF:

create tablespace TEST
 datafile  size 128M autoextend on next 128M maxsize unlimited,
           size 128M autoextend on next 128M maxsize unlimited,
           size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

Просмотр информации о табличном пространстве:

select
        file_name "Filename",
        tablespace_name "Tablespace",
        round(bytes/1024/1024 ,2) "Current Size (MB)",
        autoextensible "Autoextend",
        round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
        round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
where tablespace_name = 'TEST';

Добавление файлов данных в табличное пространство:

Файловая система:

alter tablespace TEST add datafile '/u02/oradata/TESTDB/test_04.dbf' size 128M autoextend on next 128M maxsize unlimited;


ASM:

alter tablespace TEST add datafile '+DATA' size 128M autoextend on next 128M maxsize unlimited;


OMF:

alter tablespace TEST add datafile size 128M autoextend on next 128M maxsize unlimited;

Удаление файлов данных из табличных пространств:

Файловая система:
alter tablespace TEST drop datafile '/u02/oradata/TESTDB/test_04.dbf'


ASM:

alter tablespace TEST drop datafile '+DATA/test_df4.f';


OMF:

alter tablespace TEST drop datafile 'C:\APP\ORACLE\ORADATA\TESTDB\DATAFILE\O1_MF_TEST_FC98Q9LO_.DBF';

Удаление табличных пространств:

drop tablespace TEST;
drop tablespace TEST including contents and datafiles;

Подробная информация о табличных пространствах:

set lines 556
set trimout on
set space 1
set tab off

set pagesize 10000 linesize 300 tab off

col tablespace_name format A22              heading "Tablespace"
col ts_type         format A13              heading "TS Type"
col segments        format 999999           heading "Segments"
col files           format 9999
col allocated_mb    format 9,999,990.000    heading "Allocated Size|(Mb)"
col used_mb         format 9,999,990.000    heading "Used Space|(Mb)"
col Free_mb         format 999,990.000      heading "Free Space|(Mb)"
col used_pct        format 999              heading "Used|%"
col max_ext_mb      format 99,999,990.000   heading "Max Size|(Mb)"
col max_free_mb     format 9,999,990.000    heading "Max Free|(Mb)"
col max_used_pct    format 999              heading "Max Used|(%)"

BREAK ON REPORT
COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE   ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb ON REPORT

WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name),
     tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name),
     tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),
     ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name)
SELECT d.tablespace_name,
       d.status,
       DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
       a.cnt files, 
       NVL(s.segcnt,0) segments,
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
       ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB,
       ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB,
       ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct,
       ROUND(a.maxbytes / 1024 / 1024, 3)  max_ext_mb,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND d.tablespace_name = m.tablespace_name(+)
   AND d.tablespace_name = s.tablespace_name(+)
   AND NOT d.contents = 'UNDO'
   AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' )
UNION ALL
-- TEMP TS
SELECT d.tablespace_name,
       d.status,
       DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
       a.cnt,
       0,
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
       ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB,
       ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,
       ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
       ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.tablespace_name = m.tablespace_name(+)
   AND d.extent_management = 'LOCAL'
   AND d.contents = 'TEMPORARY' 
UNION ALL
-- UNDO TS
SELECT d.tablespace_name,
       d.status,
       DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
       a.cnt,
       NVL(s.segcnt,0) segments,
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
       ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB,
       ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB,
       ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct,
       ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND d.contents = 'UNDO'
ORDER BY 1
/

prompt * Tablespace (TS) types:
prompt .  - LM/DM     - Local/Dictionary Managed
prompt .  - SYS/UNI   - SYStem/UNIform Extent Management (LM only)
prompt .  - ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only)

EMCLI Using SSH Key Credentials

Setup Example Session

Note:

The procedure shown in this example assumes that you have a firm understanding of SSH setup procedures and user and host equivalence using public private key pair using SSH.

To generate, manage, or convert SSH authentication keys, you use the SSH-keygen utility available on UNIX systems. This utility SSH-keygen tool provides different options to create with different strengths RSA keys for SSH protocol version 1 and RSA or DSA keys for use by SSH protocol version 2.

Example 13-10 Setting Up SSH key-based Authentication

$ ssh-keygen -t rsa      

The command options instruct the utility to generate SSH keys (RSA key pair).

Generating public/private rsa key pair.
Enter file in which to save the key (/home/myhome/.ssh/id_rsa):  

The path specified is the standard path to the location where SSH keys are stored ($HOME/.ssh).

Enter passphrase (empty for no passphrase)

Important: passphrase is not supported for use with SSH keys in named credentials.

Enter same passphrase again: (empty for no passphrase)
Your identification has been saved in /home/admin1/.ssh/id_rsa.
Your public key has been saved in /home/admin1/.ssh/id_rsa.pub.
The key fingerprint is:
bb:da:59:7a:fc:24:c6:9a:ee:dd:af:da:1b:1b:ed:7f admin1@myhost2170474

The ssh-regkey utility has now generated two files in the .ssh directory.

$ ls   
id_rsa  id_rsa.pub

To permit access to the host without having SSH prompt for a password, copy the public key to the authorized_keys file on that system.

$ cp id_rsa.pub  authorized_keys   

From this point, all keys listed in that file are allowed access.
Next, perform a remote logon using SSH. The system will not prompt you for a password.

$ ssh myhost  
The authenticity of host 'myhost (10.229.147.184)' can't be established.
RSA key fingerprint is de:a0:2a:d5:23:f0:8a:72:98:74:2c:6f:bf:ad:5b:2b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'myhost,10.229.147.184' (RSA) to the list of known hosts.
Last login: Mon Aug 29 16:48:45 2012 from anotherhost.example.com
$

You are now ready to add the credential to Enterprise Manager.

1. From the Setup menu, select Security, then select Named Credentials.
2. On the Named Credentials page, click Create. The Create Credential page displays.
3. Enter a Credential Name. For example, SSHCRED1.
   Note: The SSHCRED1 credential set will be used in Section 13.5.2.2, "Setting Up Host Preferred Credentials Using SSH Key Credentials"
4. Select Host from the Authenticating Target Type drop-down menu.
5. Select SSH Key Credentials from the Credential Type drop-down menu as shown in the following figure.
   
6. Ensure that the SSH private key/public key files have been copied to the host on which the browser is running.
7. From the Credential Properties region, click Browse for Public Key and Private Key to upload the generated public key/private key files.
8. Click Test and Save to verify the credentials and save them. The new named credential will appear as shown in the following figure.

Figure 13-7 Named Credential Using SSH Keys

13.5.2.2 Setting Up Host Preferred Credentials Using SSH Key Credentials

You can set up host preferred credentials to use SSH keys by creating a new credential set that uses the HostSSHCreds credential type. Enterprise Manager administrators then set up preferred credentials that use this credential set. Each Enterprise Manager target type can have one or more preferred credential sets of pre-defined credential types.
The following steps use EM CLI to create a host preferred credential set which supports SSH key credentials. This example assumes the existence of the named credential SSHCRED1 of type SSH Key Credentials created in the previous section.

1. Log into EM CLI as an Enterprise Manager Super Administrator.
2. Create a new credential set of type HostSSHCreds.
   

   $ emcli create_credential_set -set_name=HostSSHCredSet -target_type=host -supported_cred_types=HostSSHCreds
   
   Credential set "HostSSHCredSet" created successfully.
   

   Once the credential set is created, Enterprise Manger administrators can set up preferred credentials for this newly created credential set using either EM CLI or the Enterprise Manager console.
3. Set up Preferred Credentials for the newly created credential set. You can use EM CLI or the Enterprise Manger console. The following EM CLI example assumes a named credential called SSHCRED1 of type SSH Key Credentials has already been created.
   

   $ emcli set_preferred_credential -target_type=host -target_name=myhost.oracle.com -set_name=HostSSHCredSet -credential_name=SSHCRED1
   
   Successfully set preferred credentials for target myhost.oracle.com:host.
   

Once the credential set is created and preferred credentials have been set up, whenever the HostSSHCredSet credential set is used for any of the Enterprise Manager operation, that operation will use SSH credentials as defined in the named credential SSHCRED1. The following graphic shows the HostSSHCredSet credential set listed as a default preferred credential for host targets.

You can now set the preferred credentials of regular regular Enterprise Manager administrators to use the SSHCRED1 named credential by editing/creating an administrator and granting Named Credential resource privileges. The following graphic shows the manage privilege grants UI for named credentials.

Oracle ANALYZE INDEX

DECLARE
  vOwner   dba_indexes.owner% TYPE ;            /* Index Owner            */
  vIdxName dba_indexes.index_name% TYPE ;       /* Index Name             */
  vAnalyze VARCHAR2 ( 100 );                    /* String of Analyze Stmt */
  vCursor  NUMBER ;                             /* DBMS_SQL cursor        */
  vNumRows INTEGER ;                            /* DBMS_SQL return rows   */
  vHeight  index_stats.height% TYPE ;           /* Height of index tree   */
  vLfRows  index_stats.lf_rows% TYPE ;          /* Index Leaf Rows        */
  vDLfRows index_stats.del_lf_rows% TYPE ;      /* Deleted Leaf Rows      */
  vDLfPerc   NUMBER ;                           /* Del lf Percentage      */
  vMaxHeight NUMBER ;                           /* Max tree height        */
  vMaxDel    NUMBER ;                           /* Max del lf percentage  */
  CURSOR cGetIdx IS SELECT owner,index_name
     FROM dba_indexes
     --WHERE OWNER = 'OWS' and index_name in ('USAGE_APPR4')
     WHERE (OWNER, index_name) in ( select owner, segment_name from dba_segments where segment_type = 'INDEX' and owner = 'BSSADMIN')
     ;
BEGIN
  /* Define maximums. This section can be customized. */
  vMaxHeight := 3 ;
  vMaxDel    := 20 ;
  /* For every index, validate structure */
  OPEN cGetIdx;
  LOOP
     FETCH cGetIdx INTO vOwner,vIdxName;
     EXIT WHEN cGetIdx% NOTFOUND ;
     /* Open DBMS_SQL cursor */
     vCursor := DBMS_SQL.OPEN_CURSOR;
     begin
       /* Set up dynamic string to validate structure */
       vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE' ;
       DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
       vNumRows := DBMS_SQL.EXECUTE(vCursor);
       /* Close DBMS_SQL cursor */
       DBMS_SQL.CLOSE_CURSOR(vCursor);
       /* Does index need rebuilding?  */
       /* If so, then generate command */
       SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
          FROM INDEX_STATS;
       IF vDLfRows = 0 THEN          /* handle case where div by zero */
          vDLfPerc := 0 ;
       ELSE
          vDLfPerc := (vDLfRows / vLfRows) * 100 ;
       END IF ;
       IF   (vDLfPerc > vMaxDel) THEN
          DBMS_OUTPUT.PUT_LINE( 'ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD online compute statistics;  -- ' ||vDLfPerc);
       END IF ;
     exception
       when others then
         DBMS_OUTPUT.PUT_LINE( '-- ' || SQLERRM ());
     end ;
  END LOOP ;
  CLOSE cGetIdx;
END ;

Oracle Resize, Drop TEMP and UNDO

TEMP

CREATE TEMPORARY TABLESPACE MYTEMPFILE
TEMPFILE D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01_01.DBF'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE MYTEMPFILE;

ALTER DATABASE TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' DROP INCLUDING DATAFILES;

ALTER TABLESPACE TEMP
ADD TEMPFILE ‘ D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF '
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 20000M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

DROP TABLESPACE MYTEMPFILE INCLUDING CONTENTS AND DATAFILES;


UNDO

create undo tablespace undotbs2 datafile
'D:\ORACLE\ORA9I\ORADATA\ORA9I\UNDOTBS02.DBF' SIZE 200M REUSE AUTOEXTEND ON;

alter system set undo_tablespace = undotbs2;

drop tablespace undotbs1 including contents and datafiles;

EMCLI

Сводка недоступна. Нажмите эту ссылку, чтобы открыть запись.

DBMS_SQLTUNE (настройка SQL)

Поиск наилучшего плана – SQL Tuning Advisor

Минимально необходимые привилегии
для непривилегированного пользователя:

CONNECT / AS SYSDBA
GRANT ADVISOR                 TO < USERNAME >;
GRANT SELECTCATALOG_ROLE      TO < USERNAME >;
GRANT EXECUTE on DBMS_SQLTUNE TO < USERNAME >;


Дополнительно

Можно запускать помощник с помощью встроенного скрипта:
$ORACLE_HOME/rdbms/admin/sqltrpt.sql

Основные шаги

Убедитесь, что оригинальный запрос находится в памяти (shared pool)
или AWR репозитории



Создать задание для SQL Tuning Advisor одним из 3х способов

/* Анализ исходного текста запроса */
DECLARE
my_task_name varchar2(30);
my_sqltext   clob;
BEGIN
my_sqltext := q'!!';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
          sql_text => my_sqltext,
         user_name => '< SCHEMA_NAME >',
             scope => 'COMPREHENSIVE',
        time_limit => 600,
         task_name => '< TASK_NAME >',
       description => '< TASK_DESCRIPTION >');
END;
/


/* Анализ запроса по SQL_ID из Cursor Cache */
DECLARE
my_task_name varchar2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
            sql_id => '< SQL_ID >',
             scope => 'COMPREHENSIVE',
        time_limit => 600,
         task_name => '< TASK_NAME >',
       description => '< TASK_DESCRIPTION >');
END;
/



/* Анализ запроса по SQL_ID из AWR репозитория */
DECLARE
my_task_name varchar2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
        begin_snap => < BEGIN_SNAP_ID >,
          end_snap => < END_SNAP_ID >,
            sql_id => '< SQL_ID >',
             scope => 'COMPREHENSIVE',
        time_limit => 600,
         task_name => '< TASK_NAME >',
       description => '< TASK_DESCRIPTION >');
END;
/


Запустить задание на исполнение
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => '< TASK_NAME >');

Просмотр рекомендаций

SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 1000
SET PAGESIZE 1000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('< TASK_NAME >') FROM DUAL;



Пример:

exec DBMS_SQLTUNE.DROP_TUNING_TASK('TSK_01');


DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := q'!SELECT * FROM DUAL!';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
                                               user_name => 'ANGORL',
                                                   scope => 'COMPREHENSIVE',
                                              time_limit => 600,
                                               task_name => 'TSK_01',
                                             description => 'test');
END;
/


exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TSK_01' );

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS
  WHERE task_id = (SELECT task_id FROM USER_ADVISOR_TASKS
                     WHERE task_name='TSK_01');

SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 10000
SET PAGESIZE 10000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TSK_01') FROM DUAL;



В рекоммендациях может быть предложено принять profile:

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 92.44%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>'TSK_01', task_owner => 'ANGOR', replace => TRUE);

И выведены для сравнения два плана выполнения запроса:

Оригинального

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 5227986114


И с применённым profile

2- Using SQL Profile
--------------------
Plan hash value: 6245447849

 


1. Создайте задачу:


Задача создается для стейтманта из AWR:

DECLARE
  my_task_name  VARCHAR2(100);
BEGIN
  my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 523,
                          end_snap    => 658,
                          sql_id      => '7h35uxf5uhmm1',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '7h35uxf5uhmm1_AWR_tuning_task',
                          description => 'Tuning task for statement 7h35uxf5uhmm1 in AWR');
  DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/


Задача создается по sql_id курсора из кэша:

DECLARE
  my_task_name  VARCHAR2(100);
BEGIN
  my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '7h35uxf5uhmm1',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => '7h35uxf5uhmm1_tuning_task',
                          description => 'Tuning task for statement 7h35uxf5uhmm1');
  DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/


Задача создается для операторов из STS:

DECLARE
  my_task_name  VARCHAR2(100);
BEGIN
  my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          sqlset_name => 'test_sql_tuning_set',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sqlset_tuning_task',
                          description => 'Tuning task for an SQL tuning set.');
  DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/


Можно указать SQL-оператор вручную:

DECLARE
  my_sql_text   VARCHAR2(500);
  my_task_name  VARCHAR2(100);
BEGIN
  my_sql_text := 'SELECT e.*, d.* ' ||
           'FROM   emp e JOIN dept d ON e.deptno = d.deptno ' ||
           'WHERE  NVL(empno, ''0'') = :empno';

  my_task_name := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => my_sql_text,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => 'ANGOR',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'emp_dept_tuning_task',
                          description => 'Tuning task for an EMP to DEPT join query.');
  DBMS_OUTPUT.put_line('my_task_name: ' || my_task_name);
END;
/

общее время на анализ ограничили до 60 секунд.


2. Выполните задачу:

BEGIN
     DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name  => 'my_sql_tuning_task' );
END;
/


3. Получите отчет по настройке:

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SET PAGESIZE 24
select  DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'my_sql_tuning_task' ) as recommendations from dual;


Мониторить выполнение задач по настройке SQL можно так:

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'ANGOR';


Приостановить и продолжить выполнение задачи можно так:

EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'my_sql_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'my_sql_tuning_task');


Завершить выполнение задачи можно так:

EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'my_sql_tuning_task');

                        
Сбросить задачу для ее повторного выполнения:

EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'my_sql_tuning_task');




Простой пример:


DECLARE
    my_task_name  VARCHAR2(30);
    my_sql_text  CLOB;
   
BEGIN
    my_sql_text := 'select sysdate
                             from dual';
   
    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                                 sql_text                   => my_sql_text,
--                                 bind_list                  => sql_binds(anydata.ConvertNumber(100)),
                                 user_name             => 'ANGOR',
                                 scope                    => 'COMPREHENSIVE',
                                 time_limit                => 60,
                                 task_name             => 'my_sql_tuning_task',
                                 description             => 'task to tune sql'
                                 );
END;
/



BEGIN
     DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name  => 'my_sql_tuning_task' );
END;
/



select  DBMS_SQLTUNE.REPORT_TUNING_TASK ( 'my_sql_tuning_task' ) as recommendations from dual;


GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task
Tuning Task Owner  : ANGOR
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 06/08/2014 16:59:14
Completed at       : 06/08/2014 16:59:16

-------------------------------------------------------------------------------
Schema Name: ANGOR
SQL ID     : 7ahprz34d71wv
SQL Text   : select sysdate
                                          from dual

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

-------------------------------------------------------------------------------



Использование инструмента SQLT (Doc ID 215187.1)

Закрепление существующего плана

Основные шаги

Если исходный запрос находится в памяти (shared pool)
и оптимизированный запрос находится в памяти (shared pool)
Перейдите в директорию sqlt/utl где установлен инструмент SQLT
Запустите скрипт coe_xfr_sql_profile.sql с ролью SYSDBA и предоставьте исходные данные:
SQL_ID исходного запроса [SQL_ID]
PLAN_HASH_VALUE оптимизированного запроса [OPT_PLAN_HASH_VALUE]

SQL> connect / as sysdba
SQL> @coe_xfr_sql_profile.sql  SQL_ID  OPT_PLAN_HASH_VALUE

Будет создан скрипт coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql

Если запрос использует литералы то можно модифицировать параметр
force=>TRUE (в скрипте) для применения плана ко всем запросам с литералами

Запустить скрипт coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql
из под пользователя с ролью SYSDBA для окончательной фиксации плана

SQL> connect / as sysdba
SQL> @coe_xfr_sql_profile_< SQL_ID >_< OPT_PLAN_HASH_VALUE >.sql

Проверить закрепление нового плана для исходного SQL_ID

select distinct p.name sql_profile_name, s.sql_id, p.category, p.status
from DBA_SQL_PROFILES p,
     DBA_HIST_SQLSTAT s
where p.name = s.sql_profile
  and s.sql_id in ('&SQL_ID');

Проверить использование нового SQL Profile для исходного SQL_ID:

col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20
select INST_ID,
       SQL_ID,
       CHILD_NUMBER,
       PLAN_HASH_VALUE,
       SQL_PROFILE,
       SQL_PATCH,
       SQL_PLAN_BASELINE,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME
from GV$SQL where SQL_ID in ('&SQL_ID');

Пример:

Создаем SQL Profile

Связываем sql_id  с лучшим plan_hash_value

@coe_xfr_sql_profile.sql

SQL> @coe_xfr_sql_profile.sql try7b95kv87a3 4809245315

SQL> @coe_xfr_sql_profile_try7b95kv87a3_4809245315.sql


Смотрим план:

select SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE FROM V$SQL where sql_id='&SQL_ID';

select * from table(dbms_xplan.display_cursor('try7b95kv87a3',null,'ADVANCED ALLSTATS LAST'));


Убеждаемся, что он использует новый PLAN_HASH_VALUE.


Если нет, то делаем flush the HASH VALUE:

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'try7b95kv87a3';

ADDRESS          HASH_VALUE
---------------- ----------
00000000A5D6BEE7 8756491057

SQL> exec DBMS_SHARED_POOL.PURGE ('00000000A5D6BEE7, 8756491057', 'C');

PL/SQL procedure successfully completed.

SQL>


SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'try7b95kv87a3';


select name from dba_sql_profiles;