Query to View TEMPFILE and TEMP TABLESPACE Sizes
With the article “Query to View TEMPFILE and TEMP TABLESPACE Sizes,” you can locate the TEMP TABLESPACE.
Welcome to scriptdba.com and, more specifically, to the article discussing the Query for TEMP TABLESPACE sizes!
Additionally, you can identify the associated temp files and their respective sizes. Therefore, we can add temp files or modify an existing temp file.
The TEMP tablespace is dedicated to sorting statements executed by users.
The selected columns are:
TABLESPACE_NAME: The name of the tablespace
FILE_NAME: The names of the temp files
BYTES: The size of the temp files converted to
MBMAXBYTES: The maximum size achievable by the temp files
AUTOEXTENSIBLE: Indicator if the temp file is auto-extendible
STATUS: The status of the temp file
The data is retrieved from the tables:
DBA_TEMP_FILES and DBA_TABLESPACES
Finally, they are related by the JOIN clause a.tablespace_name = b.tablespace_name
Query to View TEMPFILE and TEMP TABLESPACE Sizes:
Enough talk, let's get to our query. After all, that's why you're here, right? Good work...
col file_name for a69
col tablespace_name for a20
set lines 200
set pages 999
select a.tablespace_name,
--a.file_id,
a.file_name,
a.bytes/(1024*1024) MB_ATT,
a.MAXBYTES/(1024*1024) MB_MAX,substr(a.AUTOEXTENSIBLE,1,1) AUTOEXT, b.status
from dba_temp_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
order by a.file_name;Stay tuned! Not just Query for TEMP TABLESPACE sizes
To view my guides on CRS Commands click here!
At this LINK, instead, you’ll find the SQL queries.
Stay updated on our activities by joining our community (make sure to like our pages, let’s connect, and stay updated on each of our new guides!):
Facebook
Youtube
Linkedin
For those who haven’t done so yet, we recommend saving this site among your bookmarks, so you’ll always have our solutions to the most common database problems at your fingertips, especially Oracle databases.
0 Comments