Oracle query tablespace

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,AUTO EXTEND, BIG_SMALL_FILE, BLOCK_SIZE” in Oracle.

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

Thought the article, you can “Check the Oracle query tablespace
for SIZE, AUTO EXTEND BIG_SMALL_FILE BLOCK_SIZE” as above. I hope will this your helpful.

,

About HuuPV

My name is Huu. I love technology and especially Devops Skill such as Docker, vagrant, git so forth. I likes open-sources. so I created DevopsRoles.com site to share the knowledge that I have learned. My Job: IT system administrator. Hobbies: summoners war game, gossip.
View all posts by HuuPV →

Leave a Reply

Your email address will not be published. Required fields are marked *

Comment moderation is enabled. Your comment may take some time to appear.

This site uses Akismet to reduce spam. Learn how your comment data is processed.