• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to check datafile Size in Oracle, Location, Free space, Max Size

How to check datafile Size in Oracle, Location, Free space, Max Size

October 1, 2022 by techgoeasy Leave a Comment

Here in this article, we will check how to check datafiles  in the Oracle database, how to check datafile size in oracle,How to check datafile location in Oracle, how to check datafiles in tablespace

Table of Contents

  • How to check datafiles in Oracle/How to check datafile location in Oracle
  • How to list datafiles in tablespace in oracle/how to check datafiles in tablespace
  • How to check datafile Size in Oracle
  • how to find number of datafiles in Oracle
  • what is the maximum datafile size limit in oracle database 12c
  • What is the maximum number of datafiles in Oracle
  • how to check datafile status in oracle
  • how to check offline datafile in oracle

How to check datafiles in Oracle/How to check datafile location in Oracle

select file_name, bytes/1024/1024 from dba_data_files ;

With CDB Databases, you need to connect to individual PDB and execute the query to check the tablespace. If you want to gather all the tablespace across all the PDBs and Root containers, then you need to fire the below query connected to the root container

select file_name, con_id from cdb_data_files;

How to list datafiles in tablespace in oracle/how to check datafiles in tablespace

select file_name from dba_data_files where tablespace_name like '%&tablespace%';

How to check datafile Size in Oracle

 select file_name, bytes/1024/1024 from dba_data_files  where file_name like '%&file%';

how to find number of datafiles in Oracle

select count(*) from dba_data_files ;

With CDB Databases, you need to connect to individual PDB and execute the query to check the tablespace. If you want to gather all the tablespace across all the PDBs and Root containers, then you need to fire the below query connected to the root container

select count(*), con_id from cdb_data_files group by con_id;

what is the maximum datafile size limit in oracle database 12c

For a Small file with normal tablespace, we can have 4 Million blocks. So maximum tablespace size as per blocks size will be

2K block,8G
4K block, 16 G
8 K block, 32 G
16K blocks, 64 G
32 K blocks, 128 G

For big file normal tablespace, we can have 4 Billion blocks. So maximum tablespace size as per blocks size will be

2K block,8TB
4K block, 16 TB
8 K block, 32 TB
16K blocks, 64 TB
32 K blocks, 128 TB

What is the maximum number of datafiles in Oracle

max per tablespace = OS-dependent, usually 1022
max per database = 65533, sometimes less on different OS
It is also controlled by the parameter db_files

how to check datafile status in oracle

select file_name, status from dba_data_files ;

With CDB Databases, you need to connect to individual PDB and execute the query to check the tablespace. If you want to gather all the tablespace across all the PDBs and Root containers, then you need to fire the below query connected to the root container

select file_name, con_id,status from cdb_data_files;

how to check offline datafile in oracle

Select file#,file_name,status from v$datafile where status = 'OFFLINE';

Related Articles
alter tablespace add datafile: Check out this post on How to add a datafile to tablespace in Oracle, add tempfile to temporary tablespace, how to add a datafile in ASM
How to check Tablespace in Oracle: Learn about How to Check Tablespace in Oracle, tablespace free space, tablespace growth information, tablespace size , associated datafiles , tablespace size
how to change default tablespace in oracle: Default tablespace are the tablespace where the objects are created when no tablespace name is specified by users. Find out how to check default tablespace
How to check temp tablespace in Oracle: This article is about temp tablespace in Oracle, resize the tempfile, drop the tempfile, find the temp usage by Session
shrink datafile in Oracle: Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us