An Oracle tablespace is a logical storage container within the database. It organizes and manages data efficiently, storing tables, indexes, and other objects. It helps with performance optimization and simplifies backup and recovery. Tablespaces use data files to store data and metadata. They offer flexibility in storage characteristics and allocation, including different block sizes and storage quotas. Overall, tablespaces are crucial for data organization and management in Oracle databases.
Nice script for tablespace usage checking:
set lines 223 pages 999;
select tablespace_name,round((USED_SPACE*8)/1024)CURRENT_SIZE_MB,round((TABLESPACE_SIZE*8)/1024) MAX_SIZE_MB,
round(USED_PERCENT,2) USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS where USED_PERCENT>80
order by USED_PERCENT;
or
select tablespace_name,round((USED_SPACE*8)/1024)CURRENT_SIZE_MB,round((TABLESPACE_SIZE*8)/1024) MAX_SIZE_MB,
round(USED_PERCENT,2) USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS where tablespace_name like '%TEMP%' order by USED_PERCENT;
Create Tablespace script:
SELECT 'CREATE TABLESPACE ' || TABLESPACE_NAME || ' DATAFILE SIZE 500M;' FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT IN ('SYSTEM','UNDO1','SYSAUX','TEMP1','TOOLS');
SELECT 'DROP TABLESPACE ' || TABLESPACE_NAME || ';' FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT IN ('SYSTEM','UNDO1','SYSAUX','TEMP1','TOOLS' );
or
CREATE TABLESPACE "MARS" DATAFILE '+DATA' size 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Query to tablespace autoextend:
set lines 200 pages 600
col file_name for a70
select file_name,autoextensible,ceil(maxbytes/(1024*1024)) maxsize_mb, ceil(bytes/(1024*1024)) cursize_mb from dba_data_files where tablespace_name='MARS';
To check if tablespace is having bigfile:
SELECT tablespace_name, bigfile FROM dba_tablespaces where tablespace_name='MARS';
Adding datafile:
alter tablespace PUB add datafile '/u01/app/oracle/oradata/mkeustg1/pub02.dbf' size 1G autoextend on maxsize unlimited;
or
ALTER TABLESPACE MARS ADD DATAFILE '+DATA1' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
To check users default tablespace:
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where USERNAME='SCOTT';
Temp Tablespace:
select file_name,autoextensible,ceil(maxbytes/(1024*1024)) maxsize_mb, ceil(bytes/(1024*1024)) cursize_mb from dba_temp_files where tablespace_name='TEMP';
Resize Temp tablespace:
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON ;
To find the temp tablespace size:
SELECT A.tablespace_name tablespace, D.gb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 gb_used,
D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 gb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 /1024 gb_total
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.gb_total;
To find what is occupied in the temp:
select * from v$temp_extent_pool;
Temp Tablespace usage by session:
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;
To Chk is sorting is going on
Select tablespace_name,USED_BLOCKS,FREE_BLOCKS,MAX_SORT_BLOCKS from v$sort_segment;
Select EXTENT_SIZE,CURRENT_USERS,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS from v$sort_segment;
Shrink TEMP Tablespace to the smallest possible size:
ALTER TABLESPACE temp SHRINK SPACE;
ALTER TABLESPACE temp01 SHRINK SPACE KEEP 50M;
To check the space available in all tablespace:
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
Output:
Tablespace Used MB Free MB Total MB Pct. Free
---------------------- ----------- ----------- ----------- ----------
SYSAUX 2,786 814 3,600 23
To check the space available in that particular tablespace
select c.tablespace_name,
round(decode(d.autoext,NULL,decode(e.free,NULL,0,e.free)/(1024*1024),(f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz)-f.totsz+decode(e.free,NULL,0,e.free))/ (1024*1024))) free_mb,
round(f.totsz/(1024*1024)) Total_mb,
round((f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz))/(1024*1024)) maxsize_mb,
decode(d.autoext, NULL, 'NO','YES') ae,
round(decode(d.autoext,NULL,(f.totsz-decode(e.free,NULL,0,e.free))/f.totsz*100,(f.totsz-decode(e.free,NULL,0,e.free))/(f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz))*100)) as Used_pct
from dba_tablespaces c,
(select a.tablespace_name, count(b.autoextensible) autoext
from dba_tablespaces a, dba_data_files b
where a.tablespace_name = b.tablespace_name(+) and b.autoextensible = 'YES'
group by a.TABLESPACE_NAME
having count(b.autoextensible) > 0) d,
(select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name) e,
(select df1.tablespace_name, sum(df1.bytes) totsz, sum(df1.maxbytes) maxsz
from dba_data_files df1, dba_tablespaces ts1
where ts1.tablespace_name = df1.tablespace_name
group by df1.tablespace_name) f,
(select df2.tablespace_name, sum(df2.bytes) nonauto_sz
from dba_data_files df2, dba_tablespaces ts2
where ts2.tablespace_name = df2.tablespace_name and df2.autoextensible = 'NO'
group by df2.tablespace_name) g
where c.tablespace_name = d.tablespace_name(+) and
c.tablespace_name = e.tablespace_name(+) and
c.tablespace_name = f.tablespace_name and
c.tablespace_name = g.tablespace_name(+) and c.tablespace_name like '%UNDO%' order by c.tablespace_name;
Calculate how much undo tablespace we need exactly
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';
Solution:
Increasing UNDO tablespace size is not the solution for this.
Run Undo advisory
Increase UNDO Retension Period
Enable Undo Retention Guarantee
Run this Select Query during non-business hours where heavy transactions are not happening.
Tune the Query
Query to find tablespace growth in last week:
select b.tsname tablespace_name , MAX(b.used_size_Gb) cur_used_size_Gb , round(AVG(inc_used_size_Gb),2)avg_increas_Gb
from ( SELECT a.days,a.tsname , used_size_Gb , used_size_Gb - LAG (used_size_Gb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_Gb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024*1024),2)) used_size_Gb
from dba_hist_tbspc_space_usage tsu , dba_hist_tablespace_stat ts ,dba_hist_snapshot sp, dba_tablespaces dt where tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;
Resize.sh (Script)
#!/bin/csh
$ORACLE_HOME/bin/sqlplus -S aradmin/Gu1nn3ss@ITSMLDP4 << EOF
Spool Resize.lst
@Resize.sql
spool off
exit
EOF
Hostname: $ nohup Resize.sh & (Press enter)
Enjoy the work and do like me if you like this article!!!