| Lesson 8 | New database limits |
| Objective | List database limits. |
Oracle Database Limits
In terms of the physical and logical limitations affecting all instances you create, no major changes occurred between Oracle8 and the Oracle8i database. The following table highlights some of the database limits that are an indication of the volume and activity that the Oracle database engine can handle.
| Database element | Limit |
| VARCHAR data type, VARCHAR2 data type, and PL/SQL literals | 4000 characters maximum. |
| Database files per database and tablespaces per database | 64K maximum. Your operating system may have a lower limit. In addition, you can set a lower limit by setting the DB_FILES initialization parameter. |
| Blocks per database file | Operating system dependent, but typically around 4 million blocks. |
| Bytes per block | Minimum of 2048 bytes, maximum of 32K. |
| Columns per table | 1000 maximum. |
| Constraints per column | Unlimited. |
| Rows per table | Unlimited. |
| Tables per database | Unlimited. |
| Users and roles per database | 2,147,483,638 maximum. |
| Concurrent sessions per instance | 32K. You can lower this limit by setting the PROCESSES and SESSIONS initialization parameters. |
4000 Byte Limit on Results of a SQL Operator
If you bind more than 4000 bytes of data to a BLOB or a CLOB, and the data consists of a SQL operator, then Oracle Database limits the size of the result to at most 4000 bytes. The following statement inserts only 4000 bytes because the result of LPAD is limited to 4000 bytes:
INSERT INTO print_media (ad_sourcetext)
VALUES (lpad('a', 5000, 'a'));
The following statement inserts only 2000 bytes because the result of LPAD is limited to 4000 bytes,
and the implicit hexadecimal to raw conversion converts it to 2000 bytes of RAW data:
INSERT INTO print_media (ad_photo)
VALUES (lpad('a', 5000, 'a'));
To illustrate how large an Oracle database can get, let's assume that your database has the maximum block size (32K) and creates the maximum number of database files (64K) which are each the maximum number of blocks (4 million).
This means your total database size is approximately 8,192,000GB. I think that qualifies as a VLDB (Very Large Database), don't you?
The next lesson is the module conclusion.
Data Conversion and Data Type Specification
During a conventional path load, data fields in the data file are converted into columns in the database (direct path loads are conceptually similar, but the implementation is different). There are two conversion steps:
- SQL*Loader uses the field specifications in the control file to interpret the format of the data file, parse the input data, and populate the bind arrays that correspond to a SQL INSERT statement using that data.
A bind array is an area in memory where SQL*Loader stores data that is to be loaded. When the bind array is full, the data is transmitted to the database. The bind array size is controlled by the SQL*Loader BINDSIZE and READSIZE parameters.
- The database accepts the data and executes the INSERT statement to store the data in the database.
Oracle Database uses the data type of the column to convert the data into its final, stored form. Keep in mind the distinction between a field in a data file and a column in the database.
Remember also that the field data types defined in a SQL*Loader control file are not the same as the column data types.