오류 사항
ORA-01688: unable to extend table <schema>.<table> partition <parts> by <number> in tablespace <tablespace>-------------------------------------------------------------------------------------------------------------
출처: https://community.oracle.com/thread/521373
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 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 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
해결 방안
SQL> ALTER TABLESPACE <tablespace_name> ADD DATAFILE
2 <file_path> SIZE 10240M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
