Partitioning

Introduction

Oracle Partitioning, first introduced in Oracle 8.0 in 1997, is one of the most important and successful functionalities of the Oracle DB, improving the performance, manageability, and availability for tens of thousands of applications. 

11gR2 introduces the 9th generation of partitioning, enabling customers to model even more business scenarios and making partitioning easier to use, enabling “Partitioning for the masses”.

Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces called partitions using single or set of columns called partitioning key. For ex, orders table can be partitioned based on order_date as partitioning key. Each piece of such a DB object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics. 

Basics of Partitioning

• Partitioning addresses key issues in supporting very large tables and indexes by decomposing them into smaller and more manageable pieces called partitions, which are entirely transparent to an application.
• SQL queries and DML statements do not need to be modified to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individual partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects.
• Each partition of a TABLE / INDEX must have the same logical attributes, such as column names, data types, and constraints, but each partition can have separate physical attributes, such as compression enabled or disabled, physical storage settings, and tablespaces.
• Database objects - tables, indexes, and index-organized tables - are partitioned using a 'partitioning key', a set of columns that determine in which partition a given row will reside.
• However, the DBA can manage and store each monthly partition individually, potentially using different storage tiers, applying table compression to the older data, or store complete ranges of older data in read only tablespaces.
• From the perspective of a DBA, a partitioned object has multiple pieces that can be managed either collectively / individually. This gives the DBA considerable flexibility in managing a partitioned object. 
• From the perspective of the Application, a partitioned table is identical to a non-partitioned table; no modifications are necessary when accessing a partitioned table using SQL DML commands.
• Note: All partitions of a partitioned object must reside in tablespaces of a single block size.

Partitioning Key: Each row in a partitioned table is unambiguously assigned to a single partition. Partitioning key consists of one or more columns that determine the partition where each row is stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition with the partitioning key.

Partitioned Tables: Any table can be partitioned into a million separate partitions except those tables containing columns with LONG / LONG RAW data types. You can, however, use tables containing columns with CLOB / BLOB data types.

Partitioned Index-Organized Tables: They are very useful for providing improved performance, manageability, and availability for index-organized tables. For partitioning an index-organized table:

• Partition columns must be a subset of the primary key columns.
• Secondary indexes can be partitioned (both locally and globally).
• OVERFLOW data segments are always equi-partitioned with the table partitions.

Exactly, when to use partitioning is a rather subjective decision. Some general guidelines that Oracle and I suggest are listed below.

When to Partition a Table

• Tables greater than 2 GB should always be considered as candidates for partitioning.
• Tables containing historical data, in which new data is added into the newest partition. A typical ex. is a historical table where only the current month's data is updatable and the other 11 months are read only.
• When the contents of a table need to be distributed across different types of storage devices.

When to Partition an Index

• Avoid rebuilding the entire index when data is removed.
• Perform maintenance on parts of the data without invalidating the entire index.
• Reduce the impact of index skew caused by an index on a column with a monotonically increasing value

Benefits of Partitioning

• It enables the DB objects to be managed and accessed at a finer level of granularity. 
• It improves the performance of certain queries or maintenance operations by an order of magnitude. 
• It can greatly reduce the total cost of data ownership, using a “tiered archiving” approach of keeping older relevant information still online on low cost storage devices.
• It enables an efficient and simple, yet very powerful approach when considering Information Lifecycle Management (ILM) for large environments.
• It also enables DB designers and DBA's to tackle some of the toughest problems posed by cutting-edge applications. 
• It is a key tool for building multi-terabyte systems or systems with extremely high availability requirements.
• It can provide tremendous benefit to a wide variety of applications by improving Performance, Manageability, and Availability. 

Partitioning for 

• Availability: Partitioned DB objects provide partition independence. This characteristic of partition independence can be an important part of a high-availability strategy.
• Manageability: It allows tables and indexes to be partitioned into smaller, more manageable units, providing DBA with the ability to pursue a "divide and conquer" approach to data management.
• Performance: By limiting the amount of data to be examined / operated on, partitioning provides a number of performance benefits. These features include: Partitioning Pruning and Partition-wise Joins.

Oracle additionally provides a comprehensive set of SQL commands for managing partitioning tables. These include commands for adding new partitions, dropping, splitting, moving, merging, truncating, and optionally compressing partitions.

Partitioning Pruning / Partition Elimination

• It is the simplest and also the most substantial means to improve performance using partitioning. Partition pruning can often improve query performance by several orders of magnitude.
• In Partition Pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list. As a result, Oracle DB performs operations only on those partitions that are relevant to the SQL statement. Partition pruning dramatically reduces the amount of data retrieved from disk and shortens processing time, thus improving query performance and optimizing resource utilization.
• Partition pruning works with all of Oracle's other performance features. Oracle will utilize partition pruning in conjunction with any indexing technique, join technique, or parallel access method. 

 Partition-wise Joins

• It breaks a large join into smaller joins that occur between each of the partitions, completing the overall join in less time. It can be applied when two tables are being joined together, and at least one of these tables is partitioned on the join key or when a reference partitioned table is joined with its parent table.
• It reduces query response time by minimizing the amount of data exchanged among parallel execution servers when joins execute in parallel. This significantly reduces response time and improves the use of both CPU and memory resources. Partition-wise joins can be Full or Partial. Oracle DB decides which type of join to use.
• It break a large join into smaller joins of “identical” data sets for the joined tables. “Identical” here is defined as covering exactly the same set of partitioning key values on both sides of the join, thus ensuring that only a join of these 'identical' data sets will produce a result and that other data sets do not have to be considered.
• Oracle is using either the fact of already (physical) equi-partitioned tables for the join or is transparently redistributing (= “repartitioning”) one table at runtime to create equi-partitioned data sets matching the partitioning of the other table, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.

Evolution of Partitioning in Oracle

Oracle Version #	Core functionality	Performance	Manageability
8	Range partitioning Global range indexes	“Static” partition pruning	Basic maintenance operations: add, drop, exchange
8i	Hash partitioning Composite range-hash partitioning	Partition-wise joins “Dynamic” pruning	Merge operation
9i	List partitioning		Global index maintenance
9i R2	Composite range-list partitioning	Fast partition split
10g	Global hash indexes		Local index maintenance
10g R2	1M partitions per table	“Multi-dimensional” pruning	Fast drop table
11g	More composite choices REF partitioning Virtual column partitioning		Interval partitioning Partition Advisor

Partitioning Strategies in 11g R2

Each partitioning strategy has different advantages and design considerations. Thus, each strategy is more appropriate for a particular situation.

Basic Partitioning Strategies		Partitioning Extensions
Single-level partitioning Range Hash List	Composite partitioning Range-Range Range-Hash Range-List Hash-Range Hash-Hash Hash-List List-Range List-Hash List-List	Manageability Extensions Interval Partitioning Interval Interval-Range Interval-List Interval-Hash Partition Advisor Partitioning Key Extensions REF Partitioning / Partition by Reference Virtual column-based Partitioning

Partitioning Strategies	Data Distribution	Sample Business Case
Single-level partitioning	Based on consecutive ranges of values Based on unordered lists of values Based on a hash algorithm	Orders table range partitioned by order_date Orders table list partitioned by country Orders table range partitioned by customer_id
Composite partitioning	Based on a combination of two of the above mentioned basic techniques of Range, List, Hash, and Interval	Orders table is range partitioned by order_date and sub_partitioned by hash on customer_id range on shipment_date
Interval partitioning	An extension to Range Partition. Defined by an interval, providing equi-width ranges. With exception of the first partition all partitions are automatically created on-demand when matching data arrives.	Orders table partitioned by order_date with a predefined daily interval, starting with '01-Jan-2012'
REF Partitioning or Partition by Reference	Partitioning for a child table is inherited from the parent table through a primary key - foreign key relationship. The partitioning keys are not stored in actual columns in the child table.	(parent) Orders table range partitioned by order_date and inherits the partitioning technique to (child) order lines table. Column order_date is only present in the parent orders table
Virtual column-based partitioning	Defined by one of the above mentioned partition techniques and the partitioning key is based on a virtual column. Virtual columns are not stored on disk and only exist as metadata.	Orders table has a virtual column that derives the sales region based on the first three digits of the customer account number. The orders table is then list partitioned by sales region.

Single-Level Partitioning: A table is defined by specifying one of the following data distribution methodologies, using one or more columns as the partitioning key:

1. Range: It maps data to partitions based on ranges of values of the partitioning key that you establish for each partition. It is the most common type of partitioning and is often used with dates.
2. List: It enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.
3. Hash: It maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. Hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.

Composite Partitioning: It is a combination of the basic data distribution methods; a table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. It supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning.

1. Range-Range: It enables logical range partitioning along two dimensions; for example, partition by order_date and range subpartition by shipping_date.
2. Range-Hash: It partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning.
3. Range-List: It partitions data using the range method, and within each partition, subpartitions it using the list method. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.
4. List-Range: It enables logical range subpartitioning within a given list partitioning strategy; for example, list partition by country_id and range subpartition by order_date.
5. List-Hash: It enables hash subpartitioning of a list-partitioned object; for example, to enable partition-wise joins.
6. List-List: It enables logical list partitioning along two dimensions; for example, list partition by country_id and list subpartition by sales_channel.

Manageability Extensions: The following extensions significantly enhance the manageability of partitioned tables

Interval Partitioning: It is an extension of range partitioning which instructs the DB to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. Range partitioning key value determines the high value of the range partitions, which is called the transition point, and DB creates interval partitions for data with values that are beyond that transition point. 

   When using interval partitioning, consider the following restrictions:

• You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
• Interval partitioning is not supported for index-organized tables.
• You cannot create a domain index on an interval-partitioned table.

   You can create single-level interval partitioned tables and the following composite partitioned tables:

• Interval-Range
• Interval-Hash
• Interval-List

Partition Advisor: It is part of the SQL Access Advisor. Beginning with 11gR2, the SQL Access Advisor has been enhanced to generate partitioning recommendations, in addition to the ones it already provides for indexes, materialized views, and materialized view logs. 

• It can recommend a partitioning strategy for a table based on a supplied workload of SQL statements which can be supplied by the SQL Cache, a SQL Tuning set, or be defined by the user.
• Recommendations generated by the SQL Access Advisor - either for Partitioning only or holistically - will show the anticipated performance gains that will result if they are implemented. The generated script can either be implemented manually or submitted onto a queue within Oracle Enterprise Manager.
• With the extension of partitioning advice, customers not only can get recommendation specifically for partitioning but also a more comprehensive holistic recommendation of SQL Access Advisor, improving the collective performance of SQL statements overall. 
• The Partition Advisor, integrated into the SQL Access Advisor, is part of Oracle's Tuning Pack, an extra licensable option. It can be used from within Enterprise Manager or via a command line interface.

Partitioning Key Extensions: The following extensions extend the flexibility in defining partitioning keys

Reference partitioning: It enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints.

• The benefit of this extension is that tables with a parent-child relationship can be logically equi-partitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, thus making application development easier and less error-prone.
• All basic partitioning strategies are available for reference partitioning. Interval partitioning cannot be used with reference partitioning.

Virtual Column-Based partitioning: In previous releases of Oracle DB, a table could only be partitioned if the partitioning key physically existed in the table.

• Virtual columns remove that restriction and enable the partitioning key to be defined by an expression, using one or more existing columns of a table. The expression is stored as metadata only.
• Virtual column-based partitioning is supported with all basic partitioning strategies, including reference partitioning, and interval and interval-* composite partitioning.

Partitioned Indexes

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for DW or DSS applications. Also, whenever possible, try to use local indexes because they are easier to manage.

Types of Partitioned Indexes

• Local Indexes: A local index is an index on a partitioned table that is coupled with the underlying partitioned table, 'inheriting' the partitioning strategy from the table. Consequently, each partition of a local index corresponds to one - and only one - partition of the underlying table. The coupling enables optimized partition maintenance; for example, when a table partition is dropped, Oracle simply has to drop the corresponding index partition as well. No costly index maintenance is required. Local indexes are most common in DW environments.
• Global Partitioned Indexes: A global partitioned index is an index on a partitioned or non-partitioned table that is partitioned using a different partitioning-key / partitioning strategy than the table. Global-partitioned indexes can be partitioned using range or hash partitioning and are uncoupled from the underlying table. For ex, a table could be range-partitioned by month and have 12 partitions, while an index on that table could be hash-partitioned using a different partitioning key and have a different number of partitions. Global partitioned indexes are more common for OLTP than for DW environments.
• Global Non-Partitioned Indexes: A global non-partitioned index is essentially identical to an index on a non-partitioned table. The index structure is not partitioned and uncoupled from the underlying table. In DW environments, the most common usage of global non-partitioned indexes is to enforce PK constraints. OLTP environments on the other hand mostly rely on global non-partitioned indexes.

Deciding on the Type of Partitioned Index to use: Consider the following guidelines as shown below

• If the table partitioning column is a subset of the index keys, then use a local index. If this is the case, then you are finished. If this is not the case, then continue to guideline 2.
• If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.
• If your priority is manageability, then use a local index. If this is the case, then you are finished. If this is not the case, continue to guideline 4.
• If the application is an OLTP type and users need quick response times, and then use a global index. If the application is a DSS type and users are more interested in throughput, and then use a local index.

Miscellaneous Information about Creating Indexes on Partitioned Tables

• You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.
• Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.

Partitioned Indexes on Composite Partitions

Here are a few points to remember when using partitioned indexes on composite partitions:

• Subpartitioned indexes are always local and stored with the table subpartition by default.
• Tablespaces can be specified at either index or index subpartition levels.

Global temporary tables

Global temporary tables are types of DB tables which can privately store data, persistently for a session or transaction. The data flushes out at defined instant automatically. They often find their application in the situations where data fetch and passage is not possible in single stretch.
Note that only table data is session specific, but physically table is available in all sessions.

Syntax
The default create statement is the equivalent of adding the clause ON COMMIT DELETE ROWS. An alternative is to specify ON COMMIT PRESERVE ROWS.

CREATE GLOBAL TEMPORARY TABLE
(
        [COLUMN DEFINTION]
)       ON COMMIT [DELETE | PRESERVE] ROWS;

ON COMMIT DELETE ROWS sets the life of the data contained by the table to a single TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is executed. These are Transaction-specific Temporary tables.
 
ON COMMIT PRESERVE ROWS restricts the life of the data to a single SESSION. Data is preserved in the table for a session only. These are Session-specific Temporary tables.

Important points about Global temporary tables

1. In Oracle temporary table and global temporary table are synonymous. You cannot create a temp table without the keyword “global”
2. GTT data is private to a session. Although there is a single table definition, each session uses a GTT as if it was privately owned. Truncating data in a GTT will not affect other users sessions.
3. Depending on the table definition, data in a GTT will either be removed or retained after a commit. However it is always removed when the session terminates even if the session ends abnormally.
4. Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the DB session.
5. The table and any associated indexes are stored in the users temporary tablespace. Starting with 11g Oracle introduced the option to specify the temp tablespace for a GTT (but not its indexes).
6. Views can be created against temporary tables and combinations of temporary and permanent tables.
7. Foreign key constraints are not applicable in case 7. of Temporary tables
8. Temporary tables can have triggers associated with them.
9. Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
10. Putting data in a temporary table is more efficient than placing this data in a permanent table. This is primarily due to less redo activity when a session is applying DML to temporary tables. DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Oracle writes data for temporary tables into temporary segments and thus doesn’t require redo log entries. Oracle writes rollback data for the temporary table into the rollback segments (also known as the undo log). Even though redo log generation for temporary tables will be lower than permanent tables, it’s not entirely eliminated because Oracle must log the changes made to these rollback segments. To summarize – “log generation should be approximately half of the log generation (or less) for permanent tables.”
11. Temporary tables cannot be partitioned.
12. If you use a Global Temporary Table in your application code, watch where and how you run GATHER_TABLE_STATS on it.
13. If your GTT has been defined as ON COMMIT DELETE ROWS, the

                      GATHER_TABLE_STATS call will result in rows being deleted. This is because the
                      GATHER_TABLE_STATS issues an implicit commit.

  14.    If your GTT has been defined as ON COMMIT PRESERVE ROWS, the

                       GATHER_TABLE_STATS will not delete rows in the table.

Simple Example
1 CREATE GLOBAL TEMPORARY TABLE
2 (
3 [COLUMN DEFINTION]
4 ) ON COMMIT [DELETE | PRESERVE] ROWS;
01 -- Create a simple GTT with default options:
02
03 CREATE GLOBAL TEMPORARY TABLE
04 temp_gtt (id NUMBER(20)) ;
05
06 -- Insert some data and do a SELECT
07
08 BEGIN
09 FOR i in 1..10 LOOP
10 INSERT INTO temp_gtt values (i) ;
11 END LOOP ;
12 END ;
13
14 SELECT count(*) FROM temp_gtt;
15
16 -- result:
17
18 COUNT(*)
19 ---------------
20 10
21
22 ‐‐Do a commit and run the query again
23
24 COMMIT;
25
26 SELECT count(*) FROM temp_gtt;
27
28 ‐‐result:
29
30 COUNT(*)
31 -------------
32 0

Restrictions to Consider when Creating Tables

Here are some restrictions that may affect your table planning and usage:

• Tables containing object types cannot be imported into a pre-Oracle8 DB.
• You cannot merge an exported table into a preexisting table having the same name in a different schema.
• You cannot move types and extent tables to a different schema when the original data still exists in the DB.
• Oracle DB has a limit on the total number of columns that a table (or attributes that an object type) can have.

Further, when you create a table that contains user-defined type data, the DB maps columns of user-defined type to relational columns for storing the user-defined type data. This causes additional relational columns to be created. This results in "hidden" relational columns that are not visible in a DESCRIBE table statement and are not returned by a SELECT * statement. Therefore, when you create an object table, or a relational table with columns of REF, varray, nested table, or object type, be aware that the total number of columns that the DB actually creates for the table can be more than those you specify.

Database Limits

The limits of values associated with DB functions and objects. Limits exist on several levels in the DB. There is usually a hard-coded limit in the DB that cannot be exceeded. This value may be further restricted for any given OS. DB limits are divided into four categories:

• Datatype Limits
• Physical DB Limits
• Logical DB Limits
• Process and Runtime Limits

 

Datatype Limits

Datatypes	Limit	Comments
BFILE	Max size: 4 GB Max size of a file name: 255 characters Max size of a directory name: 30 characters Max number of open BFILEs: see Comments	Max number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the max number of open files the OS will allow.
BLOB	Max size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the max number of columns per table (that is, 1000 note1).
CHAR	Max size: 2000 bytes	None
CHAR VARYING	Max size: 4000 bytes	None
CLOB	Max size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the max number of columns per table (that is, 1000 note1).
Literals (characters or numbers in SQL or PL/SQL)	Max size: 4000 characters	None
LONG	Max size: 2 GB - 1	Only one LONG column is allowed per table.
NCHAR	Max size: 2000 bytes	None
NCHAR VARYING	Max size: 4000 bytes	None
NCLOB	Max size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	Number of LOB columns per table is limited only by the max number of columns per table (that is, 1000 note1).
NUMBER	999...(38 9's) x 10125 max value -999...(38 9's) x10125 min value	Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
RAW	Max size: 2000 bytes	None
VARCHAR	Max size: 4000 bytes	None
VARCHAR2	Max size: 4000 bytes	None

note1: The absolute max number of columns in a table is 1000. However, when you create an object table (or a relational table with columns of object, nested table, varray, or REF type), Oracle maps the columns of the user-defined types to relational columns, creating in effect hidden columns that count toward the 1000-column limit. For details on how Oracle calculates the total number of columns in such a table, refer to Oracle DB Administrator's Guide.

Physical DB Limits

Item	Type of limit	Limit value
DB Block Size	Min	2048 bytes; must be a multiple of OS physical block size
DB Block Size	Max	OS dependent; never more than 32 KB
DB Blocks	Min in initial extent of a segment	2 blocks
DB Blocks	Max per datafile	Platform dependent; typically 222 - 1 blocks
Controlfiles	Number of control files	1 min; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on OS and DB creation options; max of 25,000 x (DB block size)
DB files	Max per tablespace	OS dependent; usually 1022
DB files	Max per DB	65533. May be less on some OS. Limited also by size of DB blocks and by the DB_FILES initialization parameter for a particular instance
DB extents	Max per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
DB extents	Max per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
DB file size	Max	OS dependent. Limited by max OS file size; typically 222 or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Max	Unlimited
Redo Log Files	Max number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DB statement. Control file can be resized to allow more entries; ultimately an OS limit
Redo Log Files	Max number of logfiles per group	Unlimited
Redo Log File Size	Min size	4 MB
Redo Log File Size	Max Size	OS limit; typically 2 GB
Tablespaces	Max number per DB	64 K. Number of tablespaces cannot exceed the number of DB 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 max 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	Max size	Dependent on the OS. An external table can be composed of multiple files.

Logical DB Limits

Item	Type of limit	Limit value
GROUP BY clause	Max length	GROUP BY expression and all of the non-distinct aggregate functions (for ex., SUM, AVG) must fit within a single DB block.
Indexes	Max per table	Unlimited
Indexes	Total size of indexed column	75% of the DB block size minus some overhead
Columns	Per table	1000 columns max
Columns	Per index (or clustered index)	32 columns max
Columns	Per bitmapped index	30 columns max
Constraints	Max per column	Unlimited
Subqueries	Max levels of subqueries in a SQL statement	Unlimited in the FROM clause of the top-level query 255 subqueries in the WHERE clause
Partitions	Max length of linear partitioning key	4 KB - overhead
Partitions	Max number of columns in partition key	16 columns
Partitions	Max number of partitions allowed per table or index	1024K - 1
Rows	Max number per table	Unlimited
Stored Packages	Max size	PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code.
Trigger Cascade Limit	Max value	OS-dependent, typically 32
Users and Roles	Max	2,147,483,638
Tables	Max per clustered table	32 tables
Tables	Max per DB	Unlimited

note: The limit on how long a SQL statement can be depends on many factors, including DB configuration, disk space, and memory. When an object instance exists in memory, there is no fixed limit on the number of attributes in the object. But the max total amount of memory consumed by an object instance is 4 GB. When an object instance is inserted into a table; the attributes are exploded into separate columns in the table, and the Oracle 1000-column limit applies.

Process & Runtime Limits

Item	Type of limit	Limit value
Instances per DB	max number of cluster DB instances per DB	OS-dependent
Locks	Row-level	Unlimited
Locks	Distributed Lock Manager	OS dependent
SGA size	max value	OS-dependent; typically 2 to 4 GB for 32-bit OSs, and > 4 GB for 64-bit OSs
Advanced Queuing Processes	max per instance	10
Job Queue Processes	max per instance	1000
I/O Slave Processes	max per background process (DBWR, LGWR, etc.)	15
I/O Slave Processes	max per Backup session	15
Sessions	max per instance	231; limited by the PROCESSES and SESSIONS initialization parameters
Global Cache Service Processes	max per instance	10
Shared Servers	max per instance	Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance
Dispatchers	max per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Parallel Execution Slaves	max per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Backup Sessions	max per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Services	max per instance	115