maximum data file size of Oracle Databa

What is the Maximum Datafile Size Limit In an Oracle Database? (文档 ID 804733.1)

转到底部

---

In this Document Goal   Solution   From the Database Perspective   From the Operating System Perspective   References APPLIES TO: Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2] Information in this document applies to any platform. ***Checked for relevance on 5-Nov-2012*** GOAL What is the maximum file size of an Oracle database datafile? The datafile is restricted by the either the Oracle database or the operating system where the database exists.  The value will be decided by which ever is the lower of the two. SOLUTION From the Database Perspective The Oracle database has a restriction on the number of database blocks which can be allocated in a single datafile. Therefore, the maximum file size limit depends on the database block size (DB_BLOCK_SIZE ). Note: In Oracle Database 10g, a new type of tablespace was introduced called a BIGFILE tablespace. The BIGFILE syntax must be specified during the tablespace creation as such: CREATE BIGFILE TABLESPACE my_ts; The BIGFILE tablespace can ONLY have a SINGLE datafile. By creating a tablespace using this syntax, Oracle increases the maximum number of blocks in a datafile from the 4 Million blocks to a maximum of 4 Billion. Refer to Oracle Database Reference 10g Release 2 (10.2) http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits002.htm#i287915 Physical Database Limits Example:  Maximum datafile size for a SmallFile Tablespace based on 10gR2 documentation would be: Block Size    Maximum Datafile File Size -----------     --------------------------- 2k                4194303 * 2k     = 8 GB 4k                4194303 * 4k     = 16 GB 8k                4194303 * 8k     = 32 GB 16k              4194303 * 16k   = 64 GB 32k              4194303 * 32k   = 128 GB Maximum datafile size for a BigFile Tablespace based on 10gR2 documentation would be: Block Size    Maximum Datafile File Size -----------     --------------------------- 2k                 4294967295 * 2k     = 8 TB 4k                 4294967295 * 4k     = 16 TB 8k                 4294967295 * 8k     = 32 TB 16k               4294967295 * 16k   = 64 TB 32k               4294967295 * 32k   = 128 TB Refer to these sources to confirm settings for higher versions: Oracle Database Reference 11g Release 1 (11.1) http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/limits002.htm#i287915 Physical Database Limits Oracle Database Reference 11g Release 2 (11.2) http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/limits002.htm#REFRN0042 Physical Database Limits From the Operating System Perspective The file size limitation at the OS level is platform specific and the details are found in  -- Oracle Database Administrator's Reference for UNIX-Based Operating Systems  -- Oracle Database Platform Guide for Microsoft Windows  The documentation serves as a guideline and you should confirm the file size limitation with your OS vendor. Example for Unix Platforms: Oracle Database Administrator's Reference 10g Release 2 (10.2) for UNIX-Based Operating Systems http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/appg_db_lmts.htm#sthref795 Table H-2 File Size Limits Note: Release Notes for the specific platform should be reviewed for potential documentation issues. Example: Oracle Database Release Notes 10g Release 2 (10.2) for Linux x86 http://download.oracle.com/docs/cd/B19306_01/relnotes.102/b15659/toc.htm#BAJEEIHH 6.18 Incorrect Control File Size Limits Table H-2, "File Size Limits" of Appendix H, "Database Limits" in Oracle Database Administrator's Reference for UNIX-Based Operating Systems Guide, states incorrect value of 20000 database blocks as control file size. The correct value is 25000 control file blocks with a block size of 4096 bytes.   Example for Windows Platforms: Oracle Database Platform Guide 10g Release 2 (10.2) for Microsoft Windows (x64) http://download.oracle.com/docs/cd/B19306_01/win.102/b15688/specs.htm#sthref626 Calculating Database Limits Table 13-5 Maximum File Sizes Refer to the respective Oracle on-line documentation for your platform and database version. For ASM related file size restrictions please refer to the following note:   ASM - Scalability and Limits (Doc ID 370921.1)