一、存放多大数据量数据：
下面根据官方文档提供的相关限制,大概估算出来oracle数据库最多可以存储的数据量
Physical Database Limits(11.2)
Item | Type of Limit | Limit Value |
Database Block Size | Minimum | 2048 bytes; must be a multiple of operating system physical block size |
Database Block Size | Maximum | Operating system dependent; never more than 32 KB |
Database Blocks | Minimum in initial extent of a segment | 2 blocks |
Database Blocks | Maximum per datafile | Platform dependent; typically 222 - 1 blocks |
Controlfiles | Number of control files | 1 minimum; 2 or more (on separate devices) strongly recommended |
Controlfiles | Size of a control file | Dependent on operating system and database creation options; maximum of25,000 x (database block size) |
Database files | Maximum per tablespace | Operating system dependent; usually 1022 |
Database files | Maximum per database | 65533 May be less on some operating systems Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance |
Database extents | Maximum per dictionary managed tablespace | 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier) |
Database extents | Maximum per locally managed (uniform) tablespace | 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier) |
Database file size | Maximum | Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks |
MAXEXTENTS | Default value | Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter |
MAXEXTENTS | Maximum | Unlimited |
Redo Log Files | Maximum number of logfiles | Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement Control file can be resized to allow more entries; ultimately an operating system limit |
Redo Log Files | Maximum number of logfiles per group | Unlimited |
Redo Log File Size | Minimum size | 4 MB |
Redo Log File Size | Maximum Size | Operating system limit; typically 2 GB |
Tablespaces | Maximum number per database | 64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file |
Bigfile Tablespaces | Number of blocks | A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks. |
Smallfile (traditional) Tablespaces | Number of blocks | A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks. |
External Tables file | Maximum size | Dependent on the operating system. An external table can be composed of multiple files. |
通过这里的相关限制可以大概的技术出来oracle数据库在传统数据文件和大数据文件情况下最大大小分别是:
传统数据文件(Smallfile)
32*1024(数据块大小)* (222–1)(一个数据文件的数据块数)*65533
(数据库中最多数据文件个数)= 9006784790495232(byte)/1024/1024/1024/1024=8191.6P
如果按照我们常用的block_size=8k,那么我们的数据库可以存储大小为2047.9P
大数据文件(Bigfile)
32*1024(数据块大小)* (232– 1)(一个数据文件的数据块数)*65533
(数据库中最多数据文件个数) = 9222949822242324480 (byte)/1024/1024/1024/1024 =8589541374P
如果按照我们常用的block_size=8k,那么我们的数据库可以存储大小为2147385343.5P
二、存放多少行数据
看看oracle里面的rowid的组成就知道能存多少记录了，rowid占10个字节，前32位为objectid,
中10位为相对文件号，中后22位为块号，最后16位为记录号，可见rowid支持的最大的块号为2^22个块，每个块最大为2^16条记录，理论上一个表可以有 < 4G 个 分区，一个分区在一个表空间可以有1022 个文件
（一个tablespace 是1022 个datafile ），一个 table 最多文档上说的是 64k-1 个分区？？一个文件大小决定于 os 和 db block size 联合决定，取os支持文件大小 和 4m blocks 大小的小者
oracle的伪列，rowid就是唯一标志记录物理位置的一个id，rowid的是基于64位编码的18个字符显示（数据对象编号(6) +文件编号(3) +块编号(6)+行编号(3)=18位）
select rownum,rowid,表中任意一列字段名称 from 表名;在oracle中每个表都有rowid和rownum两个列 里面的值是oracle自动生成的 用于标记每一列1、rowid是一个伪列，是用来确保表中行的唯一性，它并不能指示出行的物理位置，但可以用来定位行。2、rowid是存储在索引中的一组既定的值（当行确定后）。我们可以像表中普通的列一样将它选出来。3、利用rowid是访问表中一行的最快方式。4、rowid需要10个字节来存储，显示为18位的字符串。rowid的组成结构为： data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串)，如：AAAADeAABAAAAZSAAA 5、我们可以借助oracle提供的包dbms_rowid，来对rowid进行解析从而获取关于行的相关信息