一、存放多大數(shù)據(jù)量數(shù)據(jù)：
下面根據(jù)官方文檔提供的相關(guān)限制,大概估算出來oracle數(shù)據(jù)庫最多可以存儲(chǔ)的數(shù)據(jù)量
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.
通過這里的相關(guān)限制可以大概的技術(shù)出來oracle數(shù)據(jù)庫在傳統(tǒng)數(shù)據(jù)文件和大數(shù)據(jù)文件情況下最大大小分別是:
傳統(tǒng)數(shù)據(jù)文件(Smallfile)
32*1024(數(shù)據(jù)塊大小)* (222–1)(一個(gè)數(shù)據(jù)文件的數(shù)據(jù)塊數(shù))*65533
(數(shù)據(jù)庫中最多數(shù)據(jù)文件個(gè)數(shù))= 9006784790495232(byte)/1024/1024/1024/1024=8191.6P
如果按照我們常用的block_size=8k,那么我們的數(shù)據(jù)庫可以存儲(chǔ)大小為2047.9P
大數(shù)據(jù)文件(Bigfile)
32*1024(數(shù)據(jù)塊大小)* (232– 1)(一個(gè)數(shù)據(jù)文件的數(shù)據(jù)塊數(shù))*65533
(數(shù)據(jù)庫中最多數(shù)據(jù)文件個(gè)數(shù)) = 9222949822242324480 (byte)/1024/1024/1024/1024 =8589541374P
如果按照我們常用的block_size=8k,那么我們的數(shù)據(jù)庫可以存儲(chǔ)大小為2147385343.5P
二、存放多少行數(shù)據(jù)
看看oracle里面的rowid的組成就知道能存多少記錄了，rowid占10個(gè)字節(jié)，前32位為objectid,
中10位為相對(duì)文件號(hào)，中后22位為塊號(hào)，最后16位為記錄號(hào)，可見rowid支持的最大的塊號(hào)為2^22個(gè)塊，每個(gè)塊最大為2^16條記錄，
理論上一個(gè)表可以有 < 4G 個(gè) 分區(qū)，一個(gè)分區(qū)在一個(gè)表空間可以有1022 個(gè)文件
（一個(gè)tablespace 是1022 個(gè)datafile ），一個(gè) table 最多文檔上說的是 64k-1 個(gè)分區(qū)？？
一個(gè)文件大小決定于 os 和 db block size 聯(lián)合決定，取os支持文件大小 和 4m blocks 大小的小者
oracle的偽列，rowid就是唯一標(biāo)志記錄物理位置的一個(gè)id，rowid的是基于64位編碼的18個(gè)字符顯示（數(shù)據(jù)對(duì)象編號(hào)(6) +文件編號(hào)(3) +塊編號(hào)(6)+行編號(hào)(3)=18位）
select rownum,rowid,表中任意一列字段名稱 from 表名;
在oracle中每個(gè)表都有rowid和rownum兩個(gè)列 里面的值是oracle自動(dòng)生成的 用于標(biāo)記每一列
1、rowid是一個(gè)偽列，是用來確保表中行的唯一性，它并不能指示出行的物理位置，但可以用來定位行。
2、rowid是存儲(chǔ)在索引中的一組既定的值（當(dāng)行確定后）。我們可以像表中普通的列一樣將它選出來。
3、利用rowid是訪問表中一行的最快方式。
4、rowid需要10個(gè)字節(jié)來存儲(chǔ)，顯示為18位的字符串。
rowid的組成結(jié)構(gòu)為： data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串
)+row number(3位字符串)，如：
AAAADeAABAAAAZSAAA
5、我們可以借助oracle提供的包dbms_rowid，來對(duì)rowid進(jìn)行解析從而獲取關(guān)于行的相關(guān)信息
