exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');
суббота, 12 сентября 2009 г.
Oracle DBMS_STATS.GATHER_SCHEMA_STATS
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname, estimate_percent, block_sample, method_opt, degree, granularity,
cascade, stattab, statid, options, statown ,no_invalidate, gather_temp, gather_fixed);
Code examples:
exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT'
,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 25);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');
Database Proactive Bundle Patch 12.1.0.2
Patch 20141343 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.4 (Jan2015)
20075921 DB_BP Both DB Homes and Grid Home 19769479 OCW_PSU Both DB Homes and Grid Home 19769473 ACFS_PSU Only Grid Home 19872484 DBWLM_PSU Only Grid Home
Patch 20698050 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.7 (Apr2015)
20594149 DB_BP 12.1.0.2.7 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 20299022 OCW_PSU 12.1.0.2.3 Both DB Homes and Grid Home 20299018 ACFS_PSU 12.1.0.2.3 Only Grid Home 19872484 DBWLM_PSU 12.1.0.2.3 Only Grid Home
Patch 21188742 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.10 (Jul2015)
21125181 DB_BP 12.1.0.2.10 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 20831113 OCW_PSU 12.1.0.2.4 Both DB Homes and Grid Home 20299018 ACFS_PSU 12.1.0.2.4 Only Grid Home 19872484 DBWLM_PSU 12.1.0.2.4 Only Grid Home
Patch 21744410 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.13 (Oct2015)
21694919 DB_BP 12.1.0.2.13 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 21867380 OCW_PSU 12.1.0.2.5 Both DB Homes and Grid Home 21359761 ACFS_PSU 12.1.0.2.5 Only Grid Home 21436941 DBWLM_PSU 12.1.0.2.5 Only Grid Home
Patch 22243551 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.160119 (Jan2016)
21949015 DB_BP 12.1.0.2.160119 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 22329617 OCW_PSU 12.1.0.2.160119 Both DB Homes and Grid Home 21948341 ACFS_PSU 12.1.0.2.160119 Only Grid Home 21436941 DBWLM_PSU 12.1.0.2.5 Only Grid Home
Patch 22899531 - Database Proactive Patch 12.1.0.2.160419 (Apr2016)
22806133 DB_BP 12.1.0.2.160419 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 23006522 OCW_PSU 12.1.0.2.160419 Both DB Homes and Grid Home 22502518 ACFS_PSU 12.1.0.2.160419 Only Grid Home 21436941 DBWLM_PSU 12.1.0.2.5 Only Grid Home
Patch 23273686 - Database Proactive Patch 12.1.0.2.160719 (Jul2016)
23144544 DB_BP 12.1.0.2.160719 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 23520664 OCW_PSU 12.1.0.2.160719 Both DB Homes and Grid Home 23054341 ACFS_PSU 12.1.0.2.160719 Only Grid Home 21436941 DBWLM_PSU 12.1.0.2.5 Only Grid Home
Patch 24448103 - Database Proactive Patch 12.1.0.2.161018 (Oct2016)
24340679 DB_BP 12.1.0.2.161018 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 24846605 OCW_PSU 12.1.0.2.161018 Both DB Homes and Grid Home 24007012 ACFS_PSU 12.1.0.2.161018Foot1 Only Grid Home 21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.
Patch 24968615 - Database Proactive Bundle Patch 12.1.0.2.170117
24732088 DB_BP 12.1.0.2.170117 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 25101514 OCW_PSU 12.1.0.2.170117 Both DB Homes and Grid Home 24828643 ACFS_PSU 12.1.0.2.170117Foot1 Only Grid Home 21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.
Patch 25433352 - Database Proactive Bundle Patch 12.1.0.2.170418
25397136 DB_BP 12.1.0.2.170418 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 25481150 OCW_PSU 12.1.0.2.170418 Both DB Homes and Grid Home 25363750 ACFS_PSU 12.1.0.2.170418Foot1 Only Grid Home 21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.
Patch 26022196 - Database Proactive Bundle Patch 12.1.0.2.170718
25869760 DB_BP 12.1.0.2.170718 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 25869825 OCW_PSU 12.1.0.2.170718 Both DB Homes and Grid Home 25869830 ACFS_PSU 12.1.0.2.170718Foot1 Only Grid Home 21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.
Patch 26610322 - Database Proactive Bundle Patch 12.1.0.2.170814
26609798 DB_BP 12.1.0.2.170814 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 26609945 OCW_PSU 12.1.0.2.170814 Both DB Homes and Grid Home 25869830 ACFS_PSU 12.1.0.2.170718Foot1 Only Grid Home 21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.
Patch 26635880 - Database Proactive Bundle Patch 12.1.0.2.171017
26717470 DB_BP 12.1.0.2.171017 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 26392192 OCW_PSU 12.1.0.2.171017 Both DB Homes and Grid Home 26392164 ACFS_PSU 12.1.0.2.171017Foot1 Only Grid Home 21436941 DBWLM_PSU 12.1.0.2.5Footref1 Only Grid Home Footnote1 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.
Patch 27010930 - Database Proactive Bundle Patch 12.1.0.2.180116
26925263 DB_BP 12.1.0.2.180116 Only DB Home for non-Oracle RAC setup. Both DB Homes and Grid Home for Oracle RAC setup. 26925218 OCW_PSU 12.1.0.2.180116 Both DB Homes and Grid Home 26910974 ACFS_PSU 12.1.0.2.180116Foot2 Only Grid Home 26983807 DBWLM_PSU 12.1.0.2.180116Footref2 Only Grid Home Footnote2 For ACFS and DBWLM these subpatches are not applicable to the HP-UX Itanium and Linux on IBM System z platforms.
Oracle Grid Infrastructure Patch Set Update 11.2.0.4
Patch 19955028 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.5 (Jan2015) (Includes Database PSU 11.2.0.4.5)
19769489 DB_PSU Both DB Homes and Grid Home 19769476 OCW_PSU Both DB Homes and Grid Home 19769469 ACFS_PSU Only Grid Home
Patch 20485808 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.6 (Apr2015) (Includes Database PSU 11.2.0.4.6)
20299013 DB_PSU 11.2.0.4.6 (INCLUDES CPUAPR2015) Both DB Homes and Grid Home 20420937 OCW_PSU 11.2.0.4.6 Both DB Homes and Grid Home 20299019 ACFS_PSU 11.2.0.4.6 Only Grid Home
Patch 20996923 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.7 (Jul2015) (Includes Database PSU 11.2.0.4.7)
20760982 DB_PSU 11.2.0.4.7 (INCLUDES CPUJUL2015) Both DB Homes and Grid Home 20831122 OCW_PSU 11.2.0.4.7 Both DB Homes and Grid Home 20299019 ACFS_PSU 11.2.0.4.6 Only Grid Home
Patch 21523375 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.8 (Oct2015) (Includes Database PSU 11.2.0.4.8)
21352635 DB_PSU 11.2.0.4.8 (INCLUDES CPUOCT2015) Both DB Homes and Grid Home 21352649 OCW_PSU 11.2.0.4.8 Both DB Homes and Grid Home 21352642 ACFS_PSU 11.2.0.4.8 Only Grid Home
Patch 22191577 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.160119 (Jan2016) (Includes Database PSU 11.2.0.4.160119)
21948347 DB_PSU 11.2.0.4.160119 (INCLUDES CPUJAN2016) Both DB Homes and Grid Home 21948348 OCW_PSU 11.2.0.4.160119 Both DB Homes and Grid Home 21948355 ACFS_PSU 11.2.0.4.160119 Only Grid Home
Patch 22646198 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.160419 (Apr2016) (Includes Database PSU 11.2.0.4.160419)
22502456 DB_PSU 11.2.0.4.160419 (INCLUDES CPUAPR2016) Both DB Homes and Grid Home 22502549 OCW_PSU 11.2.0.4.160419 Both DB Homes and Grid Home 22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home
Patch 23274134 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.160719 (Jul2016) (Includes Database PSU 11.2.0.4.160719)
23054359 DB_PSU 11.2.0.4.160719 (INCLUDES CPUJUL2016) Both DB Homes and Grid Home 23054319 OCW_PSU 11.2.0.4.160719 Both DB Homes and Grid Home 22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home
Patch 24436338 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.161018 (Oct2016) (Includes Database PSU 11.2.0.4.161018)
24006111 DB_PSU 11.2.0.4.161018 (INCLUDES CPUOCT2016) Both DB Homes and Grid Home 23054319 OCW_PSU 11.2.0.4.160719 Both DB Homes and Grid Home 22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home
Patch 25476126 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.170418 (Apr2017) (Includes Database PSU 11.2.0.4.170418)
24732075 DB_PSU 11.2.0.4.170418 (INCLUDES CPUAPR2017) Both DB Homes and Grid Home 23054319 OCW_PSU 11.2.0.4.160719 Both DB Homes and Grid Home 22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home
Patch 26030799 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.170718 (Jul2017) (Includes Database PSU 11.2.0.4.170718)
25869727 DB_PSU 11.2.0.4.170718 (INCLUDES CPUJUL2017) Both DB Homes and Grid Home 25920335 OCW_PSU 11.2.0.4.170718 Both DB Homes and Grid Home 22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home
Patch 26610246 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.170814 (Jul2017) (Includes Database PSU 11.2.0.4.170814)
26609445 DB_PSU 11.2.0.4.170814 (INCLUDES CPUJUL2017) Both DB Homes and Grid Home 26609929 OCW_PSU 11.2.0.4.170814 Both DB Homes and Grid Home 22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home
Patch 26635745 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.171017 (Includes Database PSU 11.2.0.4.171017)
26392168 DB_PSU 11.2.0.4.171017 (INCLUDES CPUOCT2017) Both DB Homes and Grid Home 26609929 OCW_PSU 11.2.0.4.170814 Both DB Homes and Grid Home 22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home
Patch 27107360 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.180116 (Includes Database PSU 11.2.0.4.180116)
26925576 DB_PSU 11.2.0.4.180116 (INCLUDES CPUOCT2017) Both DB Homes and Grid Home 26609929 OCW_PSU 11.2.0.4.170814 Both DB Homes and Grid Home 22502505 ACFS_PSU 11.2.0.4.160419 Only Grid Home
четверг, 3 сентября 2009 г.
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
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';
Размер табличных пространств в блоках
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;
Для версии 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;
В блоках:
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;
(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)
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)
Подписаться на:
Сообщения (Atom)
