Oracle Datafiles Are Not Exactly Unlimited In Size - OCA Admin 1 Topic

By -


Datafiles are not exactly unlimited in size




Data files are not exactly unlimited in size, so the term "Unlimited" refers to the ceiling your datafile is able to reach, and it depends on the Oracle Block Size. To find the absolute maximum file size multiply block size by 4194303. This is the actual maximum size. You may want to read the Metalink Note:112011.1.

A datafile cannot be oversized, otherwise it could get corrupted. Let's say if your database is 8k blocks that means that one file can not exceed approximately 34GB (34,359,730,176 bytes) without having database corruption.

Sizing datafiles is a matter of manageability, it depends on your storage, the amount of space allocated in a single managed storage unit.

128G is the maximum datafile size in 10g, but considering the maximum number of datafiles a Database can have, it can make a database to potentially size 8E (exabytes = 8,388,608 T).

The maximum data file size is calculated by:
Maximum datafile size = db_block_size * maximum number of blocks

The maximum amount of data in an Oracle database is calculated by:
Maximum database size = maximum datafile size * maximum number of datafile

The maximum number of datafiles in Oracle9i and Oracle 10g Database is 65,536. However, the maximum number of blocks in a data file increase from 4,194,304 (4 million) blocks to 4,294,967,296 (4 billion) blocks.

The maximum amount of data for a 32K block size database is eight petabytes (8,192 Terabytes) in Oracle9i.

Maximum database size is 8Pb in Oracle9i & 10g (Small file Tablespaces).
Block Sz(Bytes)   Max Datafile Sz (Gb)   Max DB Sz (Tb)

 
   2,048                      8              512

   4,096                     16            1,024

   8,192                     32            2,048

  16,384                     64            4,096

  32,768                    128            8,192


The maximum database size is 8Eb in Oracle 10g (Big file tablespaces).
Block Sz (Bytes)   Max Datafile Sz (Gb)   Max DB Sz (Tb)

  
   2,048                  8,192          524,264

   4,096                 16,384        1,048,528

   8,192                 32,768        2,097,056

  16,384                 65,536        4,194,112

  32,768                131,072        8,388,224

Comments

  1. akswavein12 December 2020 at 14:23

    Hi there to everyone, the contents present at this web page are actually amazing for people knowledge, well, you can also visit Oracle 12c Performance Tuning Training for more Akswave related information and knowledge. Keep up the good work.

    ReplyDelete
  2. cloudshinepro.com31 March 2021 at 16:56

    I read your post and this blog is very good. You have provided good knowledge in this blog. This blog really impressed me. Thank you for sharing your knowledge with all of us. oracle fusion training in bangalore

    ReplyDelete
  3. tradeimexinfo31 July 2021 at 10:21

    The article you've shared here is fantastic because it provides a wealth of information about singapore import data that will be incredibly beneficial to me. Thank you for sharing that. Continue to post.

    ReplyDelete
  4. tradeimexinfo2 December 2021 at 20:49

    I always check this type of advisory post and I found your article which is related to my interest.Indonesia Export Data This is a great way to increase knowledge for us. Thanks for sharing an article like this.

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle Architecture - Explained In Detailed - Administration I

By -
Image
Figures shown in these notes are from Oracle® Database Concepts 11 g Release 2 Objectives These notes introduce the Oracle server architecture.   The architecture includes physical components, memory components, processes, and logical structures. Primary Architecture Components The figure shown above details the Oracle architecture. Oracle server :   An Oracle server includes an Oracle Instance and an Oracle database .   ·         An Oracle database includes several different types of files:   datafiles, control files, redo log files and archive redo log files.   The Oracle server also accesses parameter files and password files.   ·         This set of files has several purposes.   o    One is to enable system users to process SQL statements.   o    Another is to improve system performance.   o    Still another is to ensure the database can be recovered if there is a software/hardware failure. ·         The database server must manage large amo
Read more

Oracle Database SQL - Practise - Question - Scott Schema Examples

By -
Image
Oracle SQL Practice Questions for Students All Question are Based on Scott Schema Exhibits EMP TABLE Exhibits DEPT TABLE Exhibits SALGRADE TABLE Question 1) Display all the records in emp table? 2) Display all the records in emp table where employee belongs to deptno 10? 3) Display all the records in emp table where employee does not belong to deptno 30? 4) Display total number of records in Emp table? 5) Display emp table with salary descending order? 6) Display first five records in employee table? 7) Display all the records in emp table order by ascending deptno, descending salary? 8) Display all employees those who were joined in year 1981? 9) Display COMM in emp table. Display zero in place of null. 10) Display the records in emp table where MGR in 7698,7566 and sal should be greater then 1500 11) Display all employees where employees hired before 01-JAN-1981 12) Display all employees with
Read more

Step by Step - How to resolve redo log file corruption using ALTER DATABASE CLEAR UNARCHIVED LOGFILE command

By -
Image
Connect to sys using sqlplus in admin command prompt Check whether your database is in archive log mode or not. Run command ARCHIVE LOG LIST. It will show you the following details: It is always preferable to have your database in archive log mode. The reason being if your database is in archive log mode you can recover from all committed changes in the event of an OS or disk failure. This query will display the path of redo log files, their group and their status. It is always recommended to have a minimum of two members in one group. As you can see I have only 1 member in group 6 whose current status is INACTIVE. I intentionally have 1 member to generate a scenario for the sake of this practical. Now go to the specified path where the redo log member of group 6 is located and open it in a notepad. It will show some symbolic data. Delete couple of lines and save it the original location which is the one we got as a result of query. Why did we do the above step i.
Read more