SQLSaturday Open for Registration
On the weekend of May 22, 2010, the Portland Development community is coming together in a way never experienced before.
Using the University of Portland campus, SQLSaturday, Portland Code Camp, and Portland Bar Camp are combining and coordinating efforts to bring 800-1000 regional technology professionals together for the opportunity to immerse themselves in seminars, presentations, group exploration, and networking. Participants will be able to engage in their preferred technology, as well as to 'sample' other options.
Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleagues know about the event.
>> Click here to register.
Most sessions will be one hour and fifteen minutes (1:15), and a few 'deep dive' sessions will be longer (2.5 hours). The 'deep dive' sessions will be scheduled for 7:00 PM (after the snacks and refreshments.) Also, throughout the day, there may be opportunities for 30 minute 'SQL snack' sessions.
>> Click here to submit your presentation abstract.
I encourage all Oracle SQL and PL/SQL enthusiasts living in the Pacific Northwest to present in and/or attend this not-to-be-missed event.
SQL Injection Prevention Cheat Sheet
This article provides a set of simple techniques for preventing SQL Injection vulnerabilities. These techniques can be used with practically any kind of programming language with any type of database. There are other types of databases, like XML databases, which can have similar problems (e.g., XPath and XQuery injection) and these techniques can be used to protect them as well.More…
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) x10125 maximum value -999…(38 9′s) x10125 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 222 – 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 222 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 ( 232 ) 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 (222) 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_INTEGERvalue |
-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:
- Datatype Limits
- PL/SQL Program Limits
- Process and Runtime Limits
- Logical Database Limits
- Physical Database Limits
Yahoo Launches SQL Interface to Twitter
The new YQL set of tables for Twitter enables any developer to use simple SQL-like queries to retrieve and post Twitter data. For simple user queries, getting a user’s twitter profile data is as simple as something like “SELECT * FROM twitter.status WHERE id=’8036408424?;“.More…
Excel – Charting the Results of Oracle Analytic Functions
This is a somewhat complicated example that builds a couple of sample tables, uses a SQL statement with the Oracle analytic function LEAD submitted through ADO in an Excel macro, and then presents the information on an Excel worksheet. When the user clicks one of three buttons on the Excel worksheet, an Excel macro executes that then build charts using disconnected row sources – a disconnected ADO recordset is used to sort the data categories before pushing that data into the charts that are built on the fly.More…
36K “Oracle lang:sql” Scripts
In case you're bored, here are 36,000+ Oracle SQL scripts available for your reading pleasure through Google code search.
2 Comments | Filed in Google, Oracle | Tags: search, sqlRewriting 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, videoOracle 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, videoSQL Indexing Video
Another nice video from Stephane Faroult about database indexing. Stephane’s videos are always interesting.
3 Comments | Filed in Oracle | Tags: sql, videoSQL Function Spotlight: TRIM
The SQL function TRIM has been around since Oracle 8i and maybe earlier. TRIM enables you to trim characters from a character string. The following examples demonstrate its usage and show you a few little known features of this simple function.
Remove leading and trailing blank spaces:
SQL> SELECT TRIM (' mystring ') FROM dual;
TRIM('MY
--------
mystring
Remove any leading characters equal to ‘x’:
SQL> SELECT TRIM (LEADING 'x' FROM 'xxmystringxx') FROM dual;
TRIM(LEADI
----------
mystringxx
Remove any trailing characters equal to ‘x’:
SQL> SELECT TRIM (TRAILING 'x' FROM 'xxmystringxx') FROM dual;
TRIM(TRAIL
----------
xxmystring
Removes leading and trailing characters equal to ‘x’:
SQL> SELECT TRIM (BOTH 'x' FROM 'xxmystringxx') FROM dual;
TRIM(BOT
--------
mystring
Removes leading and trailing characters equal to ‘x’ (Same as BOTH):
SQL> SELECT TRIM ('x' FROM 'xxmystringxx') FROM dual;
TRIM('X'
--------
mystring
My usage of this function has mostly been to trim blanks from both ends of a string. How about you?
Related functions: RTRIM and LTRIM.
6 Comments | Filed in Oracle | Tags: function, sql, trim
