msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Oracle Magazine Ask Tom Columns, the Whole List

Dating back to January 2001.

Comments Off | Filed in Oracle | Tags:


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 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: ,


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:

http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/limits001.htm
9 Comments | Filed in Oracle | Tags: , , ,


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: , , ,


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: , , ,


5 Recent Oracle Security Related Documents

  1. 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).

  2. 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.

  3. 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.

  4. 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.

  5. 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: , ,


Saving/Downloading files to/from Oracle using ColdFusion

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

Continue reading…

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


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: , ,


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: , , ,