Table

How Big a Table Can Be

Posted on
Maximum Size Limit of a Table According to Oracle Logical Database Limits, tables can have at most 1000 columns and no limit on rows and space, so theoretically, there’s no space limit on a table. But not so fast, we should consider other factors. Since one ordinary table can only be stored in one tablespace, […]
Space

How Big a Database Can Be

Posted on
Create Tablespace in a Database
Maximum Size Limit of a Database After knowing how big a data file can be and how big a tablespace can be in previous posts, we can continue to calculate how big a database can be. According to the physical database limits, we can have at most 65533 data files or 64000 tablespaces in a […]
Tablespace

How Big a Tablespace Can Be

Posted on
Add DataFile in a Tablespace
Maximum Size Limit of a Tablespace Basically, at least one data file should be specified in the tablespace creation, thereafter we can add more data files to the tablespace. After knowing how big a data file can be in the previous post, we can continue to calculate how big a tablespace can be. For a […]
Data File

How Big a Data File Can Be

Posted on
DB Blocks Auto Extend in a Data File
Maximum Size Limit of a Data File According to Oracle Database Physical Database Limits, we can have approximate 222 database blocks for a data file in a smallfile tablespace (default) and 232 database blocks for a data file in a bigfile tablespace. In fact, the exact allowable database blocks in a data file should be […]
DDL

You Cannot Resize a DataFile in “ALTER TABLESPACE” Except …

Posted on
According to SQL syntax of Oracle database, you can’t resize the data files by using ALTER TABLESPACE except the tablespace was created as BIGFILE, but we know most of tablespaces are created as SMALLFILE. So if you need to resize a normal data file, you’d better to use ALTER DATABASE. SQL> alter database datafile ‘/oracle/oradata/user01.dbf’ […]