Tablespaces and Datafiles Status in Oracle: In this article we will discuss about How to check Tablespaces and Datafiles Status in Oracle.
What is an Oracle Tablespace:
Oracle divides a database into one or more logical storage units called tablespaces. Each tablespace consists of one or more files called datafiles. A datafile physically stores the data objects of the database such as tables and indexes on disk. In other words, Oracle logically stores data in the tablespaces and physically stores data in datafiles associated with the corresponding tablespaces.
Default tablespaces in Oracle:
Oracle comes with the following default tablespaces: SYSTEM, SYSAUX, USERS, UNDOTBS1, and TEMP. The SYSTEM and SYSAUX tablespaces store system-generated objects such as data dictionary tables. And you should not store any object in these tablespaces. The USERS tablespace is helpful for ad-hoc users. The UNDOTBS1 holds the undo data. The TEMP is the temporary tablespace which is used for storing intermediate results of sorting, hashing, and large object processing operations.
Query to check all tablespace size and freespace :
set lines 200 pages 100
select df.tablespace_name "Tablespace",
nvl(totalusedspace,0) "Used MB",
(df.totalspace - nvl(tu.totalusedspace,0)) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - nvl(tu.totalusedspace,0))/ df.totalspace),2)
"Pct. Free",
round(100 * ( (df.totalspace - (df.totalspace - nvl(tu.totalusedspace,0)))/ df.totalspace),2)
"Pct. Used",
nvl(fs.free_space,0) extendable_free_space
, round(maxspace,2) maxspace
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
, sum(maxbytes)/1024/1024 maxspace
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
,
(
select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name
) fs
where df.tablespace_name = tu.tablespace_name(+)
AND df.tablespace_name = fs.tablespace_name(+)
ORDER BY "Pct. Free";
select df.tablespace_name "Tablespace",
nvl(totalusedspace,0) "Used MB",
(df.totalspace - nvl(tu.totalusedspace,0)) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - nvl(tu.totalusedspace,0))/ df.totalspace),2)
"Pct. Free",
round(100 * ( (df.totalspace - (df.totalspace - nvl(tu.totalusedspace,0)))/ df.totalspace),2)
"Pct. Used",
nvl(fs.free_space,0) extendable_free_space
, round(maxspace,2) maxspace
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
, sum(maxbytes)/1024/1024 maxspace
from dba_temp_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
,
(
select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name
) fs
where df.tablespace_name = tu.tablespace_name(+)
AND df.tablespace_name = fs.tablespace_name(+)
ORDER BY "Pct. Free";Output :
Tablespace Used MB Free MB Total MB Pct. Free Pct. Used EXTENDABLE_FREE_SPACE MAXSPACE
------------------------------ ---------- ---------- ---------- ---------- ---------- --------------------- ----------
SYSTEM 997 27 1024 2.64 97.36 26.44 32767.98
SYSAUX 729 295 1024 28.81 71.19 294.44 32767.98
UNDOTBS1 79 945 1024 92.29 7.71 943.75 32767.98
ENCRYPT_DATA 0 100 100 100 0 98.94 0
NOENCRYPT_DATA 0 100 100 100 0 98.94 0
USERS 0 1024 1024 100 0 1023 32767.98
6 rows selected.
Tablespace Used MB Free MB Total MB Pct. Free Pct. Used EXTENDABLE_FREE_SPACE MAXSPACE
------------------------------ ---------- ---------- ---------- ---------- ---------- --------------------- ----------
TEMP 0 1024 1024 100 0 0 32767.98
It will give you the following informations:
- TOTAL_MB: Total size of the tablespace in MB
- MAXSPACE: max size of all datafiles (in MB) if autoextend is in use
- USED_MB: amount of MB used in all datafiles
- FREE_MB: amount of free MB in all datafile
- Pct. Free: percent of space free
- Pct. Used: percent of space used
Listing the Datafiles and Associated Tablespaces of a Database:
set lines 200 pages 100
col FILE_NAME for a60
select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "Size In MB", STATUS, AUTOEXTENSIBLE, ONLINE_STATUS from DBA_DATA_FILES;
Output:
FILE_NAME TABLESPACE_NAME Size In MB STATUS AUT ONLINE_
------------------------------------------------------------ ------------------------------ ---------- --------- --- -------
/u01/app/oracle/oradata/ORCL/system01.dbf SYSTEM 1024 AVAILABLE YES SYSTEM
/u01/app/oracle/oradata/ORCL/sysaux01.dbf SYSAUX 1024 AVAILABLE YES ONLINE
/u01/app/oracle/oradata/ORCL/undotbs01.dbf UNDOTBS1 1024 AVAILABLE YES ONLINE
/u01/app/oracle/oradata/ORCL/users01.dbf USERS 1024 AVAILABLE YES ONLINE
/u01/app/oracle/oradata/ORCL/noencrypt_data.dbf NOENCRYPT_DATA 100 AVAILABLE NO ONLINE
/u01/app/oracle/oradata/ORCL/encrypt_data.dbf ENCRYPT_DATA 100 AVAILABLE NO ONLINE
6 rows selected.
Listing the Temp Tablespaces and Tempfiles Status :
set lines 200 pages 100
col FILE_NAME for a60
select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "Size In MB", STATUS, AUTOEXTENSIBLE from DBA_TEMP_FILES;Output:
FILE_NAME TABLESPACE_NAME Size In MB STATUS AUT
------------------------------------------------------------ ------------------------------ ---------- ------- ---
/u01/app/oracle/oradata/ORCL/temp01.dbf TEMP 1024 ONLINE YES
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.