In this tutorial, How do I use the Oracle query tablespace?
Oracle query tablespace
How to query to check tablespace size, free space, and Big file vs small file SYSTEM tablespace? The following statement as below
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS mb
FROM
( SELECT tablespace_name, bytes FROM dba_data_files
UNION ALL
SELECT tablespace_name,bytes FROM dba_temp_files
)
WHERE tablespace_name='SYSTEM'
GROUP BY tablespace_name;
The screen output terminal
TABLESPACE_NAME MB
-------------------- ----------------
SYSTEM 2,048
To query tablespace “SIZE
SELECT dt.tablespace_name tablespace_name,
SUBSTR(ddf.file_name,0, instr(ddf.file_name, '/', -1, 1) - 1) AS placement_directory,
SUBSTR(ddf.file_name, instr(ddf.file_name, '/', -1, 1) + 1) AS file_name,
ddf.bytes /1024/1024 AS mb,
ddf.autoextensible,
DECODE (dt.bigfile,'NO','SMALL','YES','BIG') AS BIG_SMALL_FILE,
dt.block_size block_size
FROM
(SELECT tablespace_name, file_name, bytes,autoextensible FROM dba_data_files
UNION
SELECT tablespace_name, file_name, bytes,autoextensible FROM dba_temp_files
) ddf,
(SELECT tablespace_name, block_size,bigfile FROM dba_tablespaces
) dt
WHERE dt.tablespace_name = ddf.tablespace_name
AND dt.tablespace_name = 'SYSTEM';
The screen output terminal
TABLESPACE_NAME
--------------------
PLACEMENT_DIRECTORY
------------------------------------------------------------------------------------------------------------------------------------
FILE_NAME MB AUTOEXTEN BIG_SMALL_FILE BLOCK_SIZE
---------------------------------------------------------------------- ---------------- --------- --------------- ----------
SYSTEM
/mnt_nfs/dbdata/mydata
system02.dbf 2,048 NO SMALL 9192
Conclusion
Through the article, you can “Check the Oracle query