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');
суббота, 12 сентября 2009 г.
Oracle DBMS_STATS.GATHER_SCHEMA_STATS
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)
четверг, 20 августа 2009 г.
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.$ ssh-keygen -t rsaThe 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@myhost2170474The ssh-regkey utility has now generated two files in the .ssh directory.
$ ls id_rsa id_rsa.pubTo 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_keysFrom 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 $
- From the Setup menu, select Security, then select Named Credentials.
- On the Named Credentials page, click Create. The Create Credential page displays.
- 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" - Select Host from the Authenticating Target Type drop-down menu.
- Select SSH Key Credentials from the Credential Type drop-down menu as shown in the following figure.
- Ensure that the SSH private key/public key files have been copied to the host on which the browser is running.
- From the Credential Properties region, click Browse for Public Key and Private Key to upload the generated public key/private key files.
- Click Test and Save to verify the credentials and save them. The new named credential will appear as shown in the following figure.
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.
- Log into EM CLI as an Enterprise Manager Super Administrator.
- 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. - 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.
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.
среда, 19 августа 2009 г.
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 ;
суббота, 15 августа 2009 г.
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;
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;
Подписаться на:
Сообщения (Atom)
