The database character set
can be find by using the following SQL statements.
|
Datatypes |
Datatype Limits |
|
VARCHAR2 |
Maximum size is 4000 |
|
NVARCHAR2 |
Maximum size is
determined by the national character set definition, with an
upper limit of 4000 bytes |
|
NUMBER( p,s) |
The precision p can
range from 1 to 38. The scale s can range from -84 to 127 |
|
LONG |
up to 2 gigabytes, or
231 -1 bytes |
|
DATE |
range from January 1,
4712 BC to December 31, 9999 AD |
|
TIMESTAMP(
fractional_seconds_precision) |
fractional_seconds_precisionis
the number of digits in the fractional part of the SECOND
datetime field. Accepted values of fractional_seconds_precision
are 0 to 9. The default is 6 |
|
TIMESTAMP(
fractional_seconds_precision) WITH TIME ZONE |
fractional_seconds_precisionis
the number of digits in the fractional part of the SECOND
datetime field. Accepted values are 0 to 9. The default is 6 |
|
TIMESTAMP(
fractional_seconds_precision)WITH LOCAL TIME ZONE |
same as
TIMESTAMP(fractional_seconds_precision) WITH TIME ZONE |
|
INTERVAL YEAR(
year_precision) TO MONTH |
year_precision is the
number of digits in the YEAR datetime field. Accepted values are
0 to 9. The default is 2 |
|
INTERVAL DAY (day_precision)
TO SECOND (fractional_seconds_precision) |
day_precision is the
maximum number of digits in the DAY datetime field. Accepted
values are 0 to 9. The default is 2 --
fractional_seconds_precision is the number of digits in the
fractional part of the SECOND field. Accepted values are 0 to 9.
The default is 6 |
|
RAW( size) |
Maximum size is 2000
bytes |
|
LONG RAW |
up to 2 gigabytes |
|
ROWID |
Base 64 string
representing the unique address of a row in its table |
|
UROWID [( size)] |
Base 64 string
representing the logical address of a row of an index-organized
table. The optional size is the size of a column of type UROWID.
The maximum size and default is 4000 bytes. |
|
CHAR( size)[BYTE
|CHAR] |
Fixed-length
character data of length size bytes.Maximum size is 2000 bytes.
Default and minimum size is 1 byte |
|
NCHAR( size) |
Fixed-length
character data of length size characters. Maximum size is
determined by the national character set definition, with an
upper limit of 2000 bytes. Default and minimum size is 1
character |
|
CLOB |
Maximum size is 4
gigabytes |
|
NCLOB |
Maximum size is 4
gigabytes |
|
BLOB |
Maximum size is 4
gigabytes |
|
BFILE |
Maximum size is 4
gigabytes |
|
|
|
|
|
Physical Database
Limits |
|
Database Block Size |
>>Minimum 2048
bytes; must be a multiple of operating system physical block
size >>Maximum Operating system dependent; never more than
32 KB |
|
Database Blocks |
>>Minimum in
initial extent of a segment. 2 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 >>Size of a control file Dependent on
operating system and database creation options; maximum of
20,000 x (database block size) |
|
Database files |
>>Maximum per
tablespace Operating system dependent; usually 1022
>>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 2 GB,
regardless of the maximum file size allowed by the operating
system |
|
Database file size |
>>Maximum
Operating system dependent. Limited by maximum operating system
file size; typically 222 or 4M blocks |
|
MAXEXTENTS |
>>Default value
Derived from tablespace default storage or DB_BLOCK_SIZE
initialization parameter >>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
>>Maximum number of logfiles per group Unlimited |
|
Redo Log File Size |
>>Minimum size
50 KB >>Maximum size Operating system limit; typically 2
GB |
|
Tablespaces |
>>Maximum
number per database 64 KB Number of tablespaces cannot exceed
the number of database files, as each tablespace must include at
leastone file |
|
|
|
|
|
Logical Database
Limits |
|
GROUP BY clause |
>>Maximum
length The GROUP BY expression and all of the nondistinct
aggregates functions (for example, SUM, AVG) must fit within a
single database block. |
|
Indexes |
>>Maximum per
table Unlimited >>total size of indexed column 75% of the
database block size minus some overhead |
|
Columns |
>>Per table
1000 columns maximum >>Per index (or clustered index) 32
columns maximum >>Per bitmapped index 30 columns maximum |
|
Constraints |
>>Maximum per
column Unlimited |
|
Subqueries |
>>Maximum
levels of subqueries in a SQL statement Unlimited in the FROM
clause of the top-level query; 255 subqueries in the WHERE
clause |
|
Partitions |
>>Maximum
length of linear partitioning key 4 KB - overhead
>>Maximum number of columns in partition key 16 columns
>>Maximum number of partitions allowed per table or index
64 K-1 partitions |
|
Rollback Segments |
>>Maximum
number per database No limit; limited within a session by
MAX_ROLLBACK_SEGMENTS initialization parameter |
|
Rows |
>>Maximum
number per table Unlimited |
|
SQL Statement Length |
>>Maximum
length of statements 64 KB maximum; particular tools may impose
lower limits |
|
Stored Packages |
>>Maximum 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. See Also: Your PL/SQL or Developer/2000
documentation for details |
|
Trigger Cascade Limit |
>>Maximum value
Operating system-dependent, typically 32 |
|
Users and Roles |
>>Maximum
2,147,483,638 |
|
Tables |
>>Maximum per
clustered table 32 tables >>Maximum per database Unlimited |
|
|
|
|
|
Process and Runtime
Limits |
|
Instances per
database |
>>Maximum
number of cluster database instances per database Operating
system-dependent |
|
Locks |
>>Row-level
Unlimited >>Distributed Lock Manager Operating system
dependent |
|
SGA size |
>>Maximum value
Operating system-dependent; typically 2 to 4 GB for 32-bit
operating systems, and > 4 GB for 64-bit operating systems |
|
Advanced Queuing
Processes |
>>Maximum per
instance 10 |
|
Job Queue Processes |
>>Maximum per
instance 1000 |
|
I/O Slave Processes |
>>Maximum per
background process (DBWR, LGWR, etc.) 15 >>Maximum per
Backup session 15 |
|
Sessions |
>>Maximum per
instance 32K; limited by PROCESSES and SESSIONS initialization
parameters |
|
Global Cache Service
Processes |
>>Maximum per
instance 10 |
|
Shared Servers |
>>Maximum per
instance Unlimited within constraints set by PROCESSES and
SESSIONS initialization parameters, for instance |
|
Dispatchers |
>>Maximum per
instance Unlimited within constraints set by PROCESSES and
SESSIONS initialization parameters, for instance |
|
Parallel Execution
Slaves |
>>Maximum per
instance Unlimited within constraints set by PROCESSES and
SESSIONS initialization parameters, for instance |
|
Backup Sessions |
>>Maximum per
instance Unlimited within constraints set by PROCESSES and
SESSIONS initialization parameters, for instance |
If you have a LONG datatype
and you want to get the size of column do the following:
3- Create the table, but
this time change the LONG datatype to CLOB datatype,
5- Then use
DBMS_LOB.GETLEGTH() function to return the column size with CLOB
datatype.