Tablespace Utilization Script for Tablespace Space
Used % more than 80 %
Oracle Tablespace Utilization Script (including
AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)
1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.
1. Check the database details.
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;
2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.
1. Check the database details.
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;
2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
3. Check the details of the
datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 50000 lines 32767
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name
TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024
Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where
df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;
Note:- If required, can get
the DDL of a tablespace as below.
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select
dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
Tablespace Utilization Script in Oracle
How to check oracle tablespace
report
1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.
1. Check the database details.
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;
2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
Tablespace Utilization Script (including AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)
---------------------------------------------------------------------------------------------------------------------
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
3. Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 50000 lines 32767
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;
Note:- If required, can get the DDL of a tablespace as below.
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.
1. Check the database details.
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;
2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
Tablespace Utilization Script (including AUTOEXTEND) for generating report of more than 80 % used tablespaces (IN GB)
---------------------------------------------------------------------------------------------------------------------
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
3. Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 50000 lines 32767
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;
Note:- If required, can get the DDL of a tablespace as below.
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
Tablespace Scripts
When a tablespace is getting filled up, action need
to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
$df -h (Linux,AIX)
$df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;
Tablespace Utilization Script
-----------------------------
set pages 9999 lines 300
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(MB)" form 99999999.99
col Current_size heading "Current|Size(MB)" form 99999999.99
col Used_size heading "Used|Size(MB)" form 99999999.99
col Available_size heading "Available|Size(MB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024 Allocated_size
,a.cur_size/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
Tablespace Utilization Script (Detailed)
-----------------------------
set pages 9999 lines 300
set heading off;
set feedback off;
col tablespace_name for a30
select 'Database Name : ' || a.name , 'Host : ' || b.host_name from v$database a,gv$instance b;
select ' ' from dual;
set heading on;
set feedback on;
break on report
compute sum of "TOTAL SIZE in MB" on report
compute sum of "ALLOCATED SIZE in MB" on report
compute sum of "USED %" on report
compute sum of "FREE %" on report
compute sum of "SPACE_NEED_ON_OS_FOR_MAXSIZE" on report
select a.tablespace_name,
round(nvl(a.total,0)) "TOTAL SIZE in MB",
round(nvl(a.asize,0)) "ALLOCATED SIZE in MB",
round(nvl(a.asize-nvl(f.free,0),0)) "USED",
round(nvl(a.total-a.asize+f.free,0)) "FREE",
nvl(f.maxfree,0) "MAX_FREE",
round(((a.total-nvl(a.total-a.asize+f.free,0))/a.total)*100) "USED %",
round((nvl(a.total-a.asize+f.free,0)/a.total)*100) "FREE %",
round(a.total - a.asize) "SPACE_NEED_ON_OS_FOR_MAXSIZE"
from (select tablespace_name, sum(bytes)/1024/1024 "ASIZE",sum(case when maxbytes > bytes
then maxbytes else bytes end)/1024/1024 total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free,round(max(bytes)/1024/1024) maxfree
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by round((nvl(a.total-a.asize+f.free,0)/a.total)*100)
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name, file_name, bytes/1024/1024 SIZE_MB, autoextensible,
maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files
where tablespace_name='&tablespace_name' order by 1,2;
Database file sizes and locations
---------------------------------
set pages 9999 lines 300
set VERIFY OFF FEEDBACK OFF
COLUMN file_name FORMAT A51 HEADING 'File Name'
COLUMN tablespace_name FORMAT A15 HEADING 'Tablespace'
COLUMN meg FORMAT 99,999.90 HEADING 'Megabytes'
COLUMN status FORMAT A10 HEADING 'Status'
COLUMN autoextensible FORMAT A3 HEADING 'Auto Extend'
COLUMN maxmeg FORMAT 99,999 HEADING 'Max|Megabytes'
COLUMN Increment_by FORMAT 9,999 HEADING 'Inc|By'
SPOOL ${ORACLE_SID}_datafile.lst
BREAK ON tablespace_name SKIP 1 ON REPORT
COMPUTE SUM OF meg ON tablespace_name
COMPUTE SUM OF meg ON REPORT
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_data_files
UNION
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_temp_files
ORDER BY tablespace_name
/
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TABLESPACE DDL
--------------
set pagesize 0
set long 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile
--------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
------------------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace
--------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 4G;
If AUTOEXTEND ON
----------------
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;
To Create a new tablespace
-----------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 4096m;
If AUTOEXTEND ON
----------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;
To Create a new tablespace with multipple datafiles
----------------------------------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename_01.dbf' size 4096m;
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename_02.dbf' size 4096m;
Schemas in a Tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "Size in MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a Tablespace
-----------------------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
Total space allocated by Owner
------------------------------
set pages 9999 lines 300
col owner format a15
col segment_name format a30
col segment_type format a15
col tablespace_name format a20
col mb format 999,999,999
select owner
, segment_name
, segment_type
, tablespace_name
, mb
from (
select owner
, segment_name
, segment_type
, tablespace_name
, bytes / 1024 / 1024 "SIZE in MB"
from dba_segments
order by bytes desc
)
/
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
$df -h (Linux,AIX)
$df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;
Tablespace Utilization Script
-----------------------------
set pages 9999 lines 300
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(MB)" form 99999999.99
col Current_size heading "Current|Size(MB)" form 99999999.99
col Used_size heading "Used|Size(MB)" form 99999999.99
col Available_size heading "Available|Size(MB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024 Allocated_size
,a.cur_size/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
Tablespace Utilization Script (Detailed)
-----------------------------
set pages 9999 lines 300
set heading off;
set feedback off;
col tablespace_name for a30
select 'Database Name : ' || a.name , 'Host : ' || b.host_name from v$database a,gv$instance b;
select ' ' from dual;
set heading on;
set feedback on;
break on report
compute sum of "TOTAL SIZE in MB" on report
compute sum of "ALLOCATED SIZE in MB" on report
compute sum of "USED %" on report
compute sum of "FREE %" on report
compute sum of "SPACE_NEED_ON_OS_FOR_MAXSIZE" on report
select a.tablespace_name,
round(nvl(a.total,0)) "TOTAL SIZE in MB",
round(nvl(a.asize,0)) "ALLOCATED SIZE in MB",
round(nvl(a.asize-nvl(f.free,0),0)) "USED",
round(nvl(a.total-a.asize+f.free,0)) "FREE",
nvl(f.maxfree,0) "MAX_FREE",
round(((a.total-nvl(a.total-a.asize+f.free,0))/a.total)*100) "USED %",
round((nvl(a.total-a.asize+f.free,0)/a.total)*100) "FREE %",
round(a.total - a.asize) "SPACE_NEED_ON_OS_FOR_MAXSIZE"
from (select tablespace_name, sum(bytes)/1024/1024 "ASIZE",sum(case when maxbytes > bytes
then maxbytes else bytes end)/1024/1024 total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free,round(max(bytes)/1024/1024) maxfree
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by round((nvl(a.total-a.asize+f.free,0)/a.total)*100)
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name, file_name, bytes/1024/1024 SIZE_MB, autoextensible,
maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files
where tablespace_name='&tablespace_name' order by 1,2;
Database file sizes and locations
---------------------------------
set pages 9999 lines 300
set VERIFY OFF FEEDBACK OFF
COLUMN file_name FORMAT A51 HEADING 'File Name'
COLUMN tablespace_name FORMAT A15 HEADING 'Tablespace'
COLUMN meg FORMAT 99,999.90 HEADING 'Megabytes'
COLUMN status FORMAT A10 HEADING 'Status'
COLUMN autoextensible FORMAT A3 HEADING 'Auto Extend'
COLUMN maxmeg FORMAT 99,999 HEADING 'Max|Megabytes'
COLUMN Increment_by FORMAT 9,999 HEADING 'Inc|By'
SPOOL ${ORACLE_SID}_datafile.lst
BREAK ON tablespace_name SKIP 1 ON REPORT
COMPUTE SUM OF meg ON tablespace_name
COMPUTE SUM OF meg ON REPORT
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_data_files
UNION
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_temp_files
ORDER BY tablespace_name
/
SPOOL OFF
SET VERIFY ON FEEDBACK ON
TABLESPACE DDL
--------------
set pagesize 0
set long 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile
--------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
------------------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace
--------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 4G;
If AUTOEXTEND ON
----------------
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;
To Create a new tablespace
-----------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 4096m;
If AUTOEXTEND ON
----------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;
To Create a new tablespace with multipple datafiles
----------------------------------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename_01.dbf' size 4096m;
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename_02.dbf' size 4096m;
Schemas in a Tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "Size in MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a Tablespace
-----------------------------------------
set pages 9999 lines 300
col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
Total space allocated by Owner
------------------------------
set pages 9999 lines 300
col owner format a15
col segment_name format a30
col segment_type format a15
col tablespace_name format a20
col mb format 999,999,999
select owner
, segment_name
, segment_type
, tablespace_name
, mb
from (
select owner
, segment_name
, segment_type
, tablespace_name
, bytes / 1024 / 1024 "SIZE in MB"
from dba_segments
order by bytes desc
)
/
ASM TABLESPACE Scripts
http://files.meetup.com/1729503/1729503/All_about_ASM.pdf
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Script (In Detail)
---------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;
ASM Disk Database Files Script
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
--------------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
----------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace (ASM)
-------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
Schemas in a tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Script (In Detail)
---------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;
ASM Disk Database Files Script
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
--------------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
----------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace (ASM)
-------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
Schemas in a tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
UNDO TABLESPACE
In Undo Segments there are three types of extents,
they are
Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.
-----------------------------------
Check the overall status for undos.
-----------------------------------
SQL> select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB
from dba_undo_extents group by tablespace_name, status;
TBS STATUS GB
UNDO_TDS EXPIRED 5.95526123046875
UNDO_TDS UNEXPIRED 0.0001220703125
--------------------------
SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat;
UNDO_BLK_PER_SEC
1241.68
----------------------
Undo Blocks per Second
----------------------
SQL> 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_MB UNDO_RETENTION_SEC OPTIMAL_UNDO_RETENTION_SEC
------------------- ------------------ --------------------------
6108 900 630
----------------------
Optimal Undo Retention
----------------------
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ACTUAL_UNDO_SIZE_MB UNDO_RETENTION_SEC<]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO'
tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from
SIZE_MB FREE_MB NEEDED_UNDO_SIZE
------------------- ------------------ -----------------
6108 900 8730.5625
------------------------------------------------------
Calculate Needed UNDO Size for given Database Activity
------------------------------------------------------
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
TBS SIZE_MB FREE_MB %USED
--- ------- ------- -----
UNDO_TDS 6108 10 99
----------------------------------
EXTRACTING THE ACTUAL DDL OF UNDO:
----------------------------------
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','UNDO_TDS') FROM DUAL;
-----------------------
ALTERING UNDO TBS SIZE:
-----------------------
ALTER DATABASE DATAFILE '/UNDO_TDS_001.dbf' AUTOEXTEND ON NEXT 8192 MAXSIZE 5120M
ALTER DATABASE DATAFILE '/UNDO_TDS_002.dbf' AUTOEXTEND ON NEXT 8192 MAXSIZE 5120M
------------------------------
Monitoring:
===========
Undo information can be queried using the following views:
V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace : Note:460481.1
=============
Note:460481.1
=============
Troubleshooting Steps
When transactions hit a database and they need undo space the allocation happens in the following sequence:
1. Allocate an extent in an undo segment which has no active transaction. Oracle tries to distribute transactions over all undo segments.
2. If no undo segment found then oracle tries to online an off-line undo segment and use it.
3. If no undo segments to online, then we create a new undo segment and use it.
4. If space does not permit creation of undo segment, then we try to reuse an expired extent from the existing undo segments.
For a running transaction associated with undo segment/ extent, if it needs more undo space then:
1. If the current extent has more free blocks then use the next free block that is all ready allocated to the extent.
2. If the current extent does not have free blocks and if the next extent of the segment has expired then wrap in the the next extent and return the first block.
3. If the next extent has not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.
4. If there is no free extent available then steal from an offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment. Return the first free block of the extent.
5. Steal from online undo segment. Deallocate the extent from the online undo segment and add it to the current undo segment. Return the first free block of the extent.
NOTE: Bug 2900863 indicates this steps is broken in some cases. This is fixed in Server patchset version 9.2.0.5 and above, and in Server release 10g.
6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment then return the block.
7. Otherwise try to reuse unexpired extents from own undo segment. If all extents are currently busy(they contains uncommitted information) go to the step 8. Otherwise wrap into the next extent.
8. Randomly steal unexpired extents from offline undo segments. If this fails then try to online undo segments for reuse.
9. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace '%s'
Error: ORA-30036 (ORA-30036)
Text: unable to extend segment by %s in undo tablespace '%s'
---------------------------------------------------------------------------
Cause: the specified undo tablespace has no more space available.
Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.
This Error states that we need to add more space to Undo Tablespace, which might not be case always.
Now theoretically we know when a ora-30036 error would be reported. Now to practically confirm a genuine case of ora-30036 where we need to add more space to the undo datafile, we need to look for following information
- No/ very small free space in the undo tablespace.
select sum(bytes) from dba_free_space where tablespace_name='UNDOTBS1';
6398345216
-->(5.95GB)
select sum(bytes) from dba_data_files where tablespace_name='UNDOTBS1';
6398345216
-->(5.95GB)
Replace 'UNDOTBS1' with the undo tablespace name and check if there is free space available or not.
- Undo tablespace datafile is not autoextensible.
select autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
- No/ very less expired extents.
SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
EXPIRED 3932160 60
UNEXPIRED 2293760 20
--> after sometime
Suppose that there are no expired extents and we see only Unexpired extents and Active extents then this is most likely Undo sizing issue. In this case, check if Undo Tablespace is correctly sized. Else try reducing value for UNDO_RETENTION
The following query calculates the number of bytes needed:
SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));
*(UR) UNDO_RETENTION in seconds
*(UPS) Number of undo data blocks generated per second
*(DBS) Overhead varies based on extent and file size (db_block_size)
Refer to below note for more explanation on this
Note 262066.1 - How To Size UNDO Tablespace For Automatic Undo Management
With Oracle database 10g, you can use the Undo Advisor feature to get Undo sizing Recommendations
In case the EXPIRED extents are still there, then it means that the EXPIRED extents are not being reused. These Expired extents should have been reused and instead we are getting ORA-30036 error.
This could be because of Unpublished Bug 5442919 which is fixed in 10.2.0.4 ( and 11g ). In case all the below conditions are satisfied, then you need to apply the patch
* System managed undo (undo_management=auto in init.ora)
*Undo tablespace consists of all non-autoextend datafiles
select autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
*DML fails with this error, also written to the alert log
"ORA-30036: unable to extend segment by <n> in undo tablespace '<ts>'"
This msg is repeated for the same us# in the alert log:
"Failure to extend rollback segment <us#>"
* Instance uptime longer than 1 hour
Many (eg 1000) offline undo segments:
select count(*) from dba_rollback_segs where status='OFFLINE';
22
*No free space in the undo tablespace:
select sum(bytes) from dba_free_space where tablespace_name='UNDO_TDS';
6398345216
-->(5.95GB)
select sum(bytes) from dba_data_files where tablespace_name='UNDO_TDS';
6398345216
-->(5.95GB)
*Plenty of expired and/or unexpired space in the undo ts
select sum(bytes) "UNEXPIRED BYTES" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='UNEXPIRED';
select sum(bytes) "EXPIRED BYTES" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='EXPIRED';
Final updates:
As per the oracle doc, expired extents are not been used and hence its a bug.
Referred links:
---------------
http://www.dbvisit.com/forums/showthread.php?t=241
http://www.oracle-base.com/articles/9i/automatic-undo-management.php
http://www.oracle-dba-online.com/managing_the_undo_tablespace.htm
http://www.dbas-oracle.com/2011/04/how-to-shrink-datafile-of-undo.html
https://forums.oracle.com/forums/thread.jspa?threadID=988325
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6894817116500
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1354906470493
http://blog.mydream.com.hk/howto/how-to-determine-undo-usage-in-oracle
https://forums.oracle.com/forums/thread.jspa?threadID=662211
http://www.dba-oracle.com/sf_ora_30036_unable_to_extend_segment_by_string_in_undo_tablespace_string.htm
Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.
-----------------------------------
Check the overall status for undos.
-----------------------------------
SQL> select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB
from dba_undo_extents group by tablespace_name, status;
TBS STATUS GB
UNDO_TDS EXPIRED 5.95526123046875
UNDO_TDS UNEXPIRED 0.0001220703125
--------------------------
SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat;
UNDO_BLK_PER_SEC
1241.68
----------------------
Undo Blocks per Second
----------------------
SQL> 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_MB UNDO_RETENTION_SEC OPTIMAL_UNDO_RETENTION_SEC
------------------- ------------------ --------------------------
6108 900 630
----------------------
Optimal Undo Retention
----------------------
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ACTUAL_UNDO_SIZE_MB UNDO_RETENTION_SEC<]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO'
tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from
SIZE_MB FREE_MB NEEDED_UNDO_SIZE
------------------- ------------------ -----------------
6108 900 8730.5625
------------------------------------------------------
Calculate Needed UNDO Size for given Database Activity
------------------------------------------------------
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || ' **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
TBS SIZE_MB FREE_MB %USED
--- ------- ------- -----
UNDO_TDS 6108 10 99
----------------------------------
EXTRACTING THE ACTUAL DDL OF UNDO:
----------------------------------
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','UNDO_TDS') FROM DUAL;
-----------------------
ALTERING UNDO TBS SIZE:
-----------------------
ALTER DATABASE DATAFILE '/UNDO_TDS_001.dbf' AUTOEXTEND ON NEXT 8192 MAXSIZE 5120M
ALTER DATABASE DATAFILE '/UNDO_TDS_002.dbf' AUTOEXTEND ON NEXT 8192 MAXSIZE 5120M
------------------------------
Monitoring:
===========
Undo information can be queried using the following views:
V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace : Note:460481.1
=============
Note:460481.1
=============
Troubleshooting Steps
When transactions hit a database and they need undo space the allocation happens in the following sequence:
1. Allocate an extent in an undo segment which has no active transaction. Oracle tries to distribute transactions over all undo segments.
2. If no undo segment found then oracle tries to online an off-line undo segment and use it.
3. If no undo segments to online, then we create a new undo segment and use it.
4. If space does not permit creation of undo segment, then we try to reuse an expired extent from the existing undo segments.
For a running transaction associated with undo segment/ extent, if it needs more undo space then:
1. If the current extent has more free blocks then use the next free block that is all ready allocated to the extent.
2. If the current extent does not have free blocks and if the next extent of the segment has expired then wrap in the the next extent and return the first block.
3. If the next extent has not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.
4. If there is no free extent available then steal from an offline undo segment. Deallocate the extent from the offline undo segment and add it to the current undo segment. Return the first free block of the extent.
5. Steal from online undo segment. Deallocate the extent from the online undo segment and add it to the current undo segment. Return the first free block of the extent.
NOTE: Bug 2900863 indicates this steps is broken in some cases. This is fixed in Server patchset version 9.2.0.5 and above, and in Server release 10g.
6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment then return the block.
7. Otherwise try to reuse unexpired extents from own undo segment. If all extents are currently busy(they contains uncommitted information) go to the step 8. Otherwise wrap into the next extent.
8. Randomly steal unexpired extents from offline undo segments. If this fails then try to online undo segments for reuse.
9. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace '%s'
Error: ORA-30036 (ORA-30036)
Text: unable to extend segment by %s in undo tablespace '%s'
---------------------------------------------------------------------------
Cause: the specified undo tablespace has no more space available.
Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.
This Error states that we need to add more space to Undo Tablespace, which might not be case always.
Now theoretically we know when a ora-30036 error would be reported. Now to practically confirm a genuine case of ora-30036 where we need to add more space to the undo datafile, we need to look for following information
- No/ very small free space in the undo tablespace.
select sum(bytes) from dba_free_space where tablespace_name='UNDOTBS1';
6398345216
-->(5.95GB)
select sum(bytes) from dba_data_files where tablespace_name='UNDOTBS1';
6398345216
-->(5.95GB)
Replace 'UNDOTBS1' with the undo tablespace name and check if there is free space available or not.
- Undo tablespace datafile is not autoextensible.
select autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
- No/ very less expired extents.
SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
EXPIRED 3932160 60
UNEXPIRED 2293760 20
--> after sometime
Suppose that there are no expired extents and we see only Unexpired extents and Active extents then this is most likely Undo sizing issue. In this case, check if Undo Tablespace is correctly sized. Else try reducing value for UNDO_RETENTION
The following query calculates the number of bytes needed:
SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));
*(UR) UNDO_RETENTION in seconds
*(UPS) Number of undo data blocks generated per second
*(DBS) Overhead varies based on extent and file size (db_block_size)
Refer to below note for more explanation on this
Note 262066.1 - How To Size UNDO Tablespace For Automatic Undo Management
With Oracle database 10g, you can use the Undo Advisor feature to get Undo sizing Recommendations
In case the EXPIRED extents are still there, then it means that the EXPIRED extents are not being reused. These Expired extents should have been reused and instead we are getting ORA-30036 error.
This could be because of Unpublished Bug 5442919 which is fixed in 10.2.0.4 ( and 11g ). In case all the below conditions are satisfied, then you need to apply the patch
* System managed undo (undo_management=auto in init.ora)
*Undo tablespace consists of all non-autoextend datafiles
select autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
*DML fails with this error, also written to the alert log
"ORA-30036: unable to extend segment by <n> in undo tablespace '<ts>'"
This msg is repeated for the same us# in the alert log:
"Failure to extend rollback segment <us#>"
* Instance uptime longer than 1 hour
Many (eg 1000) offline undo segments:
select count(*) from dba_rollback_segs where status='OFFLINE';
22
*No free space in the undo tablespace:
select sum(bytes) from dba_free_space where tablespace_name='UNDO_TDS';
6398345216
-->(5.95GB)
select sum(bytes) from dba_data_files where tablespace_name='UNDO_TDS';
6398345216
-->(5.95GB)
*Plenty of expired and/or unexpired space in the undo ts
select sum(bytes) "UNEXPIRED BYTES" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='UNEXPIRED';
select sum(bytes) "EXPIRED BYTES" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='EXPIRED';
Final updates:
As per the oracle doc, expired extents are not been used and hence its a bug.
Referred links:
---------------
http://www.dbvisit.com/forums/showthread.php?t=241
http://www.oracle-base.com/articles/9i/automatic-undo-management.php
http://www.oracle-dba-online.com/managing_the_undo_tablespace.htm
http://www.dbas-oracle.com/2011/04/how-to-shrink-datafile-of-undo.html
https://forums.oracle.com/forums/thread.jspa?threadID=988325
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6894817116500
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1354906470493
http://blog.mydream.com.hk/howto/how-to-determine-undo-usage-in-oracle
https://forums.oracle.com/forums/thread.jspa?threadID=662211
http://www.dba-oracle.com/sf_ora_30036_unable_to_extend_segment_by_string_in_undo_tablespace_string.htm
set pages 999 lines 300
select file_id,FILE_NAME,(BYTES/1024/1024) "Size MB",tablespace_name from dba_data_files where tablespace_name like '%%';
alter database datafile <no.> resize 1024m;
or
SET LONG 90000
select DBMS_METADATA.GET_DDL('TABLESPACE','tablespacename') FROM DUAL;
CREATE TABLESPACE "tablespacename" DATAFILE '/path/filename' SIZE 1024 AUTOEXTEND ON NEXT 512 MAXSIZE 512
ALTER DATABASE DATAFILE <no.> AUTOEXTEND ON NEXT 512 MAXSIZE 3000M;
No comments:
Post a Comment