set echo off
set feedback off
set pagesize 66
set linesize 80
set newpage 0
set pause off
clear columns
ttitle center 'Tablespace Space Usage'
col tsname form a25 head 'Tablespace' just c
col nfrags form 999,990 head 'Free|Frags' just c
col mxfrag form 999,999,990 head 'Largest|Frag (KB)' just c
col totsiz form 999,999,990 head 'Total|(MB)' just c
col avasiz form 999,999,990 head 'Available|(KB)' just c
col pctusd form 9,990 head 'PCT|Used' just c
break on report
compute sum of nfrags mxfrag totsiz totsiz avasiz on report
select total.tablespace_name tsname,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1024,0) mxfrag,
total.bytes/1024/1024 totsiz,
nvl(sum(free.bytes)/1024,0) avasiz,
(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) total,
dba_free_space free
where total.tablespace_name = free.tablespace_name(+)
group by total.tablespace_name,
total.bytes
order by pctusd desc
/
clear columns
ttitle off
set feedback on
set echo on
set pagesize 2000 set linesize 400 SELECT 'alter database datafile '''|| file_name || ''' resize ' || ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100 || 'm;' FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a JOIN v$parameter p ON p.Name='db_block_size') a LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b ON a.file_id = b.file_id WHERE ceil( blocks*(a.BlockSize)/1024/1024) - ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) > 100 /* Minimum MB it must shrink by to be considered. */;
clear columns
set lines 180
set pages 66
set markup html on spool on
spool PCSEE_numberOFExtentsLOB.html
col num_extents form 999,999,999,990
select /*+ RESULT_CACHE */ owner, segment_name, segment_type, count(*) num_extents
from dba_extents
where owner not in ('SYS','SYSTEM','DBSNMP')
and segment_type not in ('TABLE','INDEX')
group by owner, segment_name, segment_type
having count(*) > 9
order by segment_type desc, num_extents desc, owner, segment_name
/
spool off
spool autoextend.lst set head off set lines 300 set pages 0 select 'alter database datafile '''||file_name||''' autoextend on next 2g maxsize 30g;'||chr(10) from dba_data_files; spool off
SELECT TO_CHAR(creation_time, 'YYYY Month') "Month", ROUND(SUM(bytes)/1024/1024/1024) "Growth in GBytes" FROM sys.v_$datafile GROUP BY TO_CHAR(creation_time, 'YYYY Month') ORDER BY TO_DATE(TO_CHAR(creation_time, 'YYYY Month'),'YYYY Month')
SELECT TO_CHAR(end_interval_time,'dd-mm-yyyy') snap_time, ROUND(SUM(tablespace_size) * 8192 / 1024 / 1024 / 1024, 1) size_gb, ROUND(SUM(tablespace_usedsize) * 8192 / 1024 / 1024 / 1024, 1) usedsize_gb FROM sys.dba_hist_tbspc_space_usage tsu, sys.dba_hist_snapshot s WHERE tsu.snap_id = s.snap_id AND s.snap_id IN (SELECT snap_id FROM sys.dba_hist_snapshot WHERE TO_CHAR(end_interval_time, 'HH24') = '00' ) GROUP BY end_interval_time ORDER BY end_interval_time;
Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying. Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries.
This is done by setting the UNDO_RETENTION parameter. The default is 300 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.
Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy. All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.
However it is worth to tune the following important parameters
You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:
Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time!
SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
UNDO_SIZE
----------
209715200
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
3.12166667
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';
DB_BLOCK_SIZE [Byte]
--------------------
4096
209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]
Using Inline Views, you can do all in one query!
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401
Calculate Needed UNDO Size for given Database Activity If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity: Again, all in one query:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
NEEDED UNDO SIZE [MByte]
------------------------
131.695313
The previous query may return a "NEEDED UNDO SIZE" that is less than the "ACTUAL UNDO SIZE". If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.
SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size, a.inst_id as Instance, a.sid||','||a.serial# AS sid_serial, NVL(a.username, '(oracle)') AS username, a.program, a.status, a.sql_id FROM gv$session a, gv$sort_usage b, gv$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr AND a.inst_id=b.inst_id AND a.inst_id=p.inst_id ORDER BY temp_size desc;
select a.tablespace_name tablespace, d.TEMP_TOTAL_MB, sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB, d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB from v$sort_segment a, ( select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB from v$tablespace b, v$tempfile c where b.ts#= c.ts# group by b.name, c.block_size ) d where a.tablespace_name = d.name group by a.tablespace_name, d.TEMP_TOTAL_MB;
SELECT * FROM dba_temp_free_space;
Shrink TEMP Tablespace using alter tablespace command SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 50M;. Shrink TEMPFILE using alter tablespace command SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/TEST11G/temp01.dbf' KEEP 40M; Shrink TEMP Tablespace to the smallest possible size: SQL> ALTER TABLESPACE temp SHRINK SPACE;
select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks from v$tempseg_usage u, v$sql s where s.sql_id = u.sql_id;
select a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) MBS, round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED from dba_segments a, dba_tables b where a.owner=b.owner and a.owner not like 'SYS%' and a.segment_name = b.table_name and a.segment_type='TABLE' group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) having round(bytes/1024/1024,0) >100 order by round(bytes/1024/1024,0) desc ;HOME