Oracle Magazine Ask Tom Columns, the Whole List ↗

Dating back to January 2001.

Comments Off | Filed in Oracle | Tags: database

Interesting Bug

Michael posted a comment on Martin’s blog, that I previously quoted, about a “hack” to add a comment on a default value definition. Here is an example of how it works (tested in 11.2.0.1):

  SQL> CREATE TABLE t (
    2     x VARCHAR2 (10) DEFAULT 'Hi' -- There. This will be stored too.
    3  );

  Table created.

Notice the comment after the default value. The comment would be ignored, right? think again:

  SQL> COLUMN column_name FORMAT a14
  SQL> COLUMN data_default FORMAT a40
  SQL> SELECT column_name, data_default
    2    FROM user_tab_columns
    3   WHERE table_name = 'T';

  COLUMN_NAME    DATA_DEFAULT
  -------------- ----------------------------------------
  X              'Hi' -- There. This will be stored too.

  1 row selected.

Well, basically this is bug 8546537:

When trying to add a COMMENT to a table, if the ‘)’ is on a new line, then the COMMENT is successfuly created when an error should be raised. The COMMENT also makes it to the Data Dictionary. If the ‘)’ is on the same line, an error is raised.

Interesting!

Comments Off | Filed in Oracle | Tags: database

Database Skills and Titles

Kenneth Downs:

There are three common job titles in the database area, which are Database Administrator (DBA), Database Programmer, and Database Architect. These titles tend to be somewhat variable from shop-to-shop, but generally the “Architect” term indicates the highest level of skill combined with considerable management responsibilities. The “Programmer” term is somewhere below that, but the “DBA” is extremely variable. I have seen shops where a person was called a DBA and filled a relatively constrained role closer to IT or operations (routine tasks, no real programming) and other shops where a person with the DBA title was basically the Architect.

There is also the Database “Engineer” which I believe encompasses the three terms that Kenneth mentions. After all, a title is just a title. What defines your professional life is your experience.

via The Database Programmer: Database Skills.

2 Comments | Filed in Oracle | Tags: database, skill

Oracle Database Limits You May Not Know About

The following is a list of database limits which are divided into four categories in addition to the PL/SQL compiler limits. It is an aggregation of 5 separate web pages from the Oracle Database 11g Release 2 documentation library. I put them all here on one page for convenience.

Datatype Limits

Datatypes	Limit	Comments
`BFILE`	Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments	The maximum number of `BFILEs` is limited by the value of the `SESSION_MAX_OPEN_FILES` initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
`BLOB`	Maximum 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 maximum number of columns per table (that is, 1000).
`CHAR`	Maximum size: 2000 bytes	None
`CHAR VARYING`	Maximum size: 4000 bytes	None
`CLOB`	Maximum 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 maximum number of columns per table (that is, 1000).
Literals (characters or numbers in SQL or PL/SQL – more)	Maximum size: 4000 characters	None
`LONG`	Maximum size: 2 GB – 1	Only one `LONG` column is allowed per table.
`NCHAR`	Maximum size: 2000 bytes	None
`NCHAR VARYING`	Maximum size: 4000 bytes	None
`NCLOB`	Maximum 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 maximum number of columns per table (that is, 1000).
`NUMBER`	999…(38 9′s) x10¹²⁵ maximum value -999…(38 9′s) x10¹²⁵ minimum 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`	Maximum size: 2000 bytes	None
`VARCHAR`	Maximum size: 4000 bytes	None
`VARCHAR2`	Maximum size: 4000 bytes	None

Logical Database Limits

Item	Type of Limit	Limit Value
`GROUP BY` clause	Maximum length	The `GROUP BY` expression and all of the nondistinct aggregate functions (for example, `SUM`, `AVG`) must fit within a single database block.
Indexes	Maximum per table	Unlimited
Indexes	Total size of indexed column	75% of the database block size minus some overhead
Columns	Per table	1000 columns maximum
Columns	Per index (or clustered index)	32 columns maximum
Columns	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
Partitions	Maximum number of columns in partition key	16 columns
Partitions	Maximum number of partitions allowed per table or index	1024K – 1
Rows	Maximum number per table	Unlimited
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.
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
Tables	Maximum per database	Unlimited

Physical Database Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	`2048` bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than `32` KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 2²² – 1 blocks
Controlfiles	Number of control files	`1` minimum; `2` or more (on separate devices) strongly recommended
Controlfiles	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`
Database files	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 (more)	Maximum per dictionary managed tablespace	`4` GB * physical block size (with K/M modifier); `4` GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	`2` GB * physical block size (with K/M modifier); `2` GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 2²² or `4` MB blocks
`MAXEXTENTS`	Default value	Derived from tablespace default storage or `DB_BLOCK_SIZE` initialization parameter
`MAXEXTENTS`	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
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	`4 MB`
Redo Log File Size	Maximum Size	Operating system limit; typically `2` GB
Tablespaces	Maximum number per database	`64 K` Number of tablespaces cannot exceed the number of database 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 ( 2³² ) blocks. The maximum 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 (2²²) blocks.
External Tables file	Maximum size	Dependent on the operating system.An external table can be composed of multiple files.

Process and Runtime Limits

Item	Type of Limit	Limit Value
Instances per database	Maximum number of cluster database instances per database	Operating system-dependent
Locks	Row-level	Unlimited
Locks	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
I/O Slave Processes	Maximum per Backup session	15
Sessions	Maximum per instance	`32` KB; limited by the `PROCESSES` and `SESSIONS` initialization parameters
Global Cache Service Processes	Maximum per instance	`10`
Shared Servers	Maximum per instance	Unlimited within constraints set by the `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

PL/SQL Compiler Limits

Item	Limit
bind variables passed to a program unit	32768
exception handlers in a program unit	65536
fields in a record	65536
levels of block nesting	255
levels of record nesting	32
levels of subquery nesting	254
levels of label nesting	98
levels of nested collections	no predefined limit
magnitude of a `PLS_INTEGER` or `BINARY_INTEGER`value	-2147483648..2147483647
number of formal parameters in an explicit cursor, function, or procedure	65536
objects referenced by a program unit	65536
precision of a `FLOAT` value (binary digits)	126
precision of a `NUMBER` value (decimal digits)	38
precision of a `REAL` value (binary digits)	63
size of an identifier (characters)	30
size of a string literal (bytes)	32767
size of a `CHAR` value (bytes)	32767
size of a `LONG` value (bytes)	32760
size of a `LONG` `RAW` value (bytes)	32760
size of a `RAW` value (bytes)	32767
size of a `VARCHAR2` value (bytes)	32767
size of an `NCHAR` value (bytes)	32767
size of an `NVARCHAR2` value (bytes)	32767
size of a `BFILE` value (bytes)	4G * value of DB_BLOCK_SIZE parameter
size of a `BLOB` value (bytes)	4G * value of DB_BLOCK_SIZE parameter
size of a `CLOB` value (bytes)	4G * value of DB_BLOCK_SIZE parameter
size of an `NCLOB` value (bytes)	4G * value of DB_BLOCK_SIZE parameter

Sources and Resources:

http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/limits001.htm

9 Comments | Filed in Oracle | Tags: database, limits, pl/sql, sql

Rewriting SQL Queries for Performance in 9 Minutes (Video)

Here is another video from Stephane Faroult about how you can improve query performance:

3 Comments | Filed in Oracle | Tags: database, performance, sql, video

Oracle Database Architecture in Less than 10 Minutes (Video)

Here is a video from Stephane Faroult about Oracle database architecture. He describes the video as “a pretty reckless description of what happens when you start Oracle and when you execute a statement”. You also get to listen to his unique English accent. Watch:

3 Comments | Filed in Oracle | Tags: architecture, database, sql, video

5 Recent Oracle Security Related Documents

Oracle Applications 11i Encrypted Password String Disclosure (PDF): An undisclosed security vulnerability exists in Oracle Applications 11i that may allow an unauthenticated, internal attacker to obtain Oracle Applications’ user account encrypted password strings, which in turn can be decrypted using previously published information. An attacker can potentially obtain either any user’s password or the Oracle Applications’ main database account password (APPS).
Building an Audit Trail in an Oracle Applications Environment (PDF): Sarbanes-Oxleyâ€™s section 404 requires a companyâ€™s key systems be audited. However, many companies have “unauditable” systems and donâ€™t even know it. This paper explores methods by which companies can create an auditable system by implementing various levels of audit trails in Oracle Applications.
Dissecting the Redo Logs (PDF): This paper delves into the guts of the undocumented binary format of the redo logs and shows the forensics examiner, if there is evidence to be found, how to find it and how it can be integrated into a time line of events. It also explores how an attacker can attempt to cover their tracks and how to spot this.
Locating dropped objects (PDF): This paper shows, even when an object has been dropped and purged from the system there will be, in the vast majority of cases, fragments left â€œlying aroundâ€ which can be sewn together to build an accurate picture of what the actions the attacker took. Perhaps, depending upon how quickly an investigation takes place from the incident in question, even all data pertaining to the dropped object or objects may still be found.
Isolating Evidence of Attacks Against the Authentication Mechanism (PDF): This paper looks at attacks against the authentication mechanism and evidence to check whether a logon attempt was successful or not. It also looks at other attacks leveled at the authentication process including SID guessing, user enumeration and brute forcing of passwords over the network. Moreover, the paper looks at the differences between a logon attempt via the FTP and Web services provided with the XML Database and directly with the RDBMS itself.

1 Comment | Filed in Oracle, Security | Tags: database, EBS, Security

Saving/Downloading files to/from Oracle using ColdFusion

Here is how to store and download Excel files to/from an Oracle database using ColdFusion.

34 Comments | Filed in ColdFusion, Oracle | Tags: database, lob

Another semi-open source database

In addition to this list, here is another Java based open source database for you: H2. Well, it is not really open source, it is free to use and distribute, and the source code is included. But currently does not use a traditional open source (GPL) license. (via).

Comments Off | Filed in Technology | Tags: database, h2, open-source

Open source databases

I stumbled upon an article about open source databases. I’ve always thought that MySQL and PostgreSQL were the only open source databases out there, but it turns out that there are quite a few more.

Here is a list of open source databases that are mentioned in the article: Continue reading…

1 Comment | Filed in Oracle, Technology | Tags: database, MySQL, open-source, PostgreSQL