The Weekend DBA

Big Data

2011-07-28T22:55:00.000-07:00

The data revolution is upon us. More data than ever before is being generated and stored. Every smart phone user litters the data-sphere with snippets from the multitude of sensors built in to their devices. More and more manufacturers are building in sensors to the most mundane of devices. RFID abounds.We are slowly being submerged in a sea of data.The problems, as always are; how much of this

The hacker word

2011-07-26T14:57:00.000-07:00

Why does the word hacker have so many meanings?An article recently published goes through the origins of the word.Personally I prefer the positive connotations - where ingenuity and resourcefulness are gainfully employed.Oh well........

Other key non-database files

2009-08-10T06:16:00.000-07:00

Although there are only three types of file which technically make up an Oracle database, there are other disk files floating around the place which are extremely important tot he correct operation of an Oracle server. They are not considered to be technically part of the database, though, because if they ever get deleted or damaged, no-one will know about it and you won't be told about it.

The Database

2009-08-09T06:06:00.000-07:00

Whilst an Oracle instance is made up of many different and various memory areas together with countless different background processes, each doing a specific task, an Oracle database consists of just three types of physical disk file. Theya re all binary files (so don't go opening them in vi or notepad!) and have an internal structure that only an Oracle instance can make sense of. Taking each of

Summary of the Oracle Instance

2009-08-08T06:04:00.000-07:00

When you run Oracle, you cause a large chunk of memory to be reserved for Oracle's use. That chunk of memory is called the System Global Area (SGA). It is composed internally of several discrete 'pools' of memory, such as the Buffer Cache and the Shared Pool. Each 'pool' is used to store a particular sort of data for particular purposes -sometimes, 'your' data, so you can see rows from a table;

Processes or Threads?

2009-08-07T06:02:00.000-07:00

I've been busy describing DBWn and LGWR as "background processes", but in fact they might not be processes at all. If you are running Oracle on Windows, for example, then they are technically 'threads' within the main process, not independent processes in their own right. On Unix and Linux, however, they are genuinely independent processes and can be listed just like you can list the existence of

The Background Processes

2009-08-06T06:00:00.000-07:00

A memory area, like the SGA, is useless unless something (or some many things) can write data into the memory area and read data out of it. You need, in short, a set of processes which know how to interact with the various memory areas which make up an Oracle SGA. There are indeed several such processes, and they are collectively known as background processes, because they run and do their work

SGA Summary

2009-08-05T05:49:00.000-07:00

When the Oracle program runs, it grabs a large chunk of memory in which it can do its work. That 'chunk', in its entirety, is known as the System Global Area, or SGA.The SGA is not a monolithic chunk of memory, however: it has internal structure, and is internally chopped up into discrete areas of memory which perform specialised tasks. These sub-areas are known as the Shared Pool (which caches

The Streams Pool

2009-08-04T05:48:00.000-07:00

New in 10g, a special area of memory within the SGA is now dedicated to allowing a database to replicate itself to a different location (think of a head office database which needs to be 'copied' up to a branch office out in the boondocks). If you don't have a need to have your database replicate itself, you don't need a Streams Pool. Before 9i, replication took whatever memory it needed from the

The Java Pool

2009-08-03T05:48:00.000-07:00

Since Oracle 8i, it has been possible to write procedures, functions and other code which gets stored inside the database in the Java programming language. It has since turned out that you would be certifiably insane to do so but nevertheless, the capability remains. (Java is better utilised "in the middle tier" -that is, in an application server, rather than in the backend database itself). If

The Large Pool

2009-08-02T05:47:00.000-07:00

The Large Pool is an optional component of the SGA, though these days it is so useful to have that I doubt many production databases would not be using one: optional it may be in strict right, but I suspect most DBAs these days consider it functionally compulsory. Whenever you parallelise an operation in Oracle, communication between the individual parallel slaves requires memory access. That

The Log Buffer

2009-08-01T05:46:00.000-07:00

It is impossible to generalise, but if there is such a thing as a 'typical' Oracle database, you might measure the size of the Shared Pool and the Buffer Cache in the several hundred megabytes range, or bigger. In comparison, most Log Buffers anywhere in the World would not be much bigger than, tops, 10M or so. The Log Buffer is thus a relatively tiny area of memory in which a record of the

The Buffer Cache

2009-07-31T05:45:00.000-07:00

The Buffer Cache is a memory area in which the data physically stored in the database is read and modified. When you want to see the employee records in the EMP table, they are first read from disk and loaded into the Buffer Cache. Why not simply fetch the rows off disk and straight back to you and your client PC? Someone else might want to read the EMP records just after you. If we loaded the

The Shared Pool

2009-07-30T05:44:00.000-07:00

When you issue a query such as select * from emp, the database must work out what that means. Do we have a table called EMP? What are its constituent columns? Where, physically, on disk, does the table live? Are there any indexes built on the table which might help us get the results back more quickly? Do you, the user, have rights to query the table? The process of answering all these sorts of

Oracle Server Components

2009-07-29T05:43:00.000-07:00

What is an Oracle Server? When you edit a document in Word (or whatever word processor you happen to prefer!), you don't type documents directly into a disk file, because doing so would be painfully slow. Instead, documents are initially 'typed into' and created in memory -your computer's RAM- because that works at nanosecond speed and is thus very fast and responsive to work

Simple Block Layout

2009-07-28T05:37:00.000-07:00

So, with this overhead, free space, and (wow) actual data, how is it all stored in the data block?SIMPLE BLOCK LAYOUTThe data block header (fixed) is at the beginning of the block. A small tail is at the end of the block (the tail is for block consistency checking). Everything else (variable header and data rows) fits between the beginning and the end.Variable header info grows from the top down

Concurrency - Interested Transaction List

2009-07-27T05:34:00.000-07:00

Concurrency really just means how many people can be doing things at the same time in the same place. It directly affects scalability.Many RDBMS products use a form of centralized "lock manager" to keep track of who's doing what to what. That is a valid solution, but as the number of concurrent users rises (not people just logged on to the database, but the people actively doing things) then a

Parts of the Database (5) - Data Blocks

2009-07-26T05:31:00.000-07:00

Data Blocks If you could only pick one thing in Oracle to know well, you should pick the DATA BLOCK. It is the key to everything worth doing in Oracle.If you can understand the data block, everything else in the Oracle RDBMS is easy. EVERYTHING.Another name for an Oracle data block is a "logical block", but you won't hear many people call it that

Parts of the Database (4)

2009-07-25T05:30:00.000-07:00

What are Datafiles Every Oracle database has one or more physical datafiles. The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database. A datafile can be associated with only one database. Datafiles can have certain characteristics set to let them automatically extend when

Parts of the Database (3)

2009-07-24T05:29:00.000-07:00

What are Tablespaces A tablespace is made from at least one datafile. Many datafiles can be within a tablespace. This is a logical grouping of datafiles. A datafile cannot be split between tablespaces or shared and is exclusively used by a tablespace. There are two types of tablespace permanent and temporary. A permanent tablespace contains

Parts of the Database (2)

2009-07-23T05:28:00.000-07:00

Segments and Extents Ok, now that we have a basic understanding of datafiles and tablespaces, we are one step closer to discussing data blocks and something I haven't mentioned called "headers".But before we get there, we need to bridge the gap between D&T and blocks.That bridge comes in the form of two logical constructs that build off of the block

Parts of the Database (1)

2009-07-22T05:27:00.000-07:00

Parts of the Database Why Oracle Works the Way it Does Now, the smallest unit Oracle really messes with is the "data block". I'm going to skip over them for now because I think they'll make more sense after we discuss datafiles and tablespaces.You'll remember (because I asked you to) that Oracle

The Database

2009-07-21T05:26:00.001-07:00

The Database The database consists of two parts the database and the instance. So what is the difference? To understand the difference between a database and an instance, we need to know the basic definition of each and the relationship between them. Definition: A database is a set of files stored on disk. An instance is a collection of

DataGuard

2009-07-16T05:07:00.001-07:00

Data GuardData Guard is a mechanism for having a copy of one database mirror the activities happening on the main site. This mechanism is particularly applicable where recovery from a backup is unacceptable with regards to time and effort. This solution allows a quick 'failover' from machine to machine with the minimum of effort.There are various levels of data transfer between live and data

Oracle and OS

2009-07-16T04:09:00.000-07:00

Oracle Database Architecture.-----------------------------Choosing the correct database architecture (hardware platform, operating system) is critical to the success of any new Oracle database application. Architecture decisions were simple when the mainframe was the only architecture available. But architecture selection is not as clear-cut now that Microsoft Windows platforms, UNIX and LINUX

Killing a session in Oracle

2009-07-15T13:31:00.000-07:00

Sessions in oracle can be killed with the command: SQL> alter system kill session 'sid,serial#'; The value for sid and serial# can be query'd using the following query: select * from v$sessionorselect sid, serial#, osuser, programfrom v$session; After this command, the indicated session is marked for kill. When it's possible the session will be killed. Sometimes this can take a while (for

The Oracle decode function

2009-07-14T13:33:00.000-07:00

The decode function can be used in SQL for and IF-THEN-ELSE construction. It's an alternative for the CASE statement which was introduced in Oracle 8.Syntax:decode( expression , compare_value, return_value, [,compare, return_value] ... [,default_return_value] )with:expression is the value to evaluatecompare_value is the value that can match the evaluated valuereturn_value is the value that is

Oracle Database Admin

2009-07-13T05:10:00.000-07:00

Oracle Database ObjectsThe following sections provide some interesting information on Oracle database objects (tablespaces, tables, indexes). To keep the reading lively, I’ll intersperse some tuning information and hints, tips and tricks in the discussion. This section is not an all-encompassing guide to administering Oracle objects, rather it is a collection of information that I felt that was

DBWR_IO_SLAVES vs DB_WRITER_PROCESSES

2009-07-12T05:20:00.000-07:00

DBWR_IO_SLAVES vs DB_WRITER_PROCESSESQuestions about multiple DBWR processes have plagued DBAs since Oracle7. You configured multiple DBWR process in Oracle7 by setting the parameter DB_WRITERS. In Oracle7, multiple DBWR processes were actually slave processes that were unable to perform asynchronous I/O calls on their own. The algorithm used by the Oracle7 DBWR caused it to incur waits when the

Dictionary Objects you should know

2009-07-09T14:34:00.000-07:00

Oracle Dictionary View Objects. USER_TAB_COMMENTS Stores comment information on tables. USER_COL_COMMENTS Stores comment information for columns. USER_CONSTRAINTS CONTRAINT_TYPE column will list the first character of the first word that best describes each type of constraint. ALL_CONSTRAINTS CONTRAINT_TYPE column will list the first character of the first word that best describes

Bitmap Join Indexes

2009-07-08T01:03:00.000-07:00

Bitmap Join Indexes Oracle8i supported bitmap indexes on a single table. 9i adds bitmap join indexes, built on two tables, to optimize access for a specific join condition: CREATE BITMAP INDEX my_bit_join_ixON emp_table (e.job)FROM emp_table e, dept_table dWHERE e.deptno = d.deptno ; Restrictions: Can not reference an IOT, temporary table, or a self-join Only one table can be updated

SPFILE

2009-07-07T14:41:00.000-07:00

Online Configuration Parameter Updating Oracle keeps initialization parameters in its parameter file (PFILE). Now Oracle also has a binary file equivalent to the PFILE called the server parameter file (SPFILE). Unlike the PFILE, you can not edit the SPFILE, because its contents are binary. To create an SPFILE: CREATE SPFILE FROM PFILE ; To view settings within the SPFILE, query the

Flashback

2009-07-01T14:01:00.000-07:00

Oracle Flashback Oracle Flashback allows you to query the database as of a specific time or System Change Number (SCN) in the past. It can be useful in recovering from a recent logical or user error – just use Flashback to query the data prior to an accidental data deletion, for example. To use Flashback you must have already: Set the initialization parameter UNDO_MANAGEMENT = AUTO (This

Backup and Recovery (RMAN)

2009-06-26T01:11:00.000-07:00

Backup and Recovery Enhancements Recovery Manager (RMAN) Enhancements Block Media Recovery (BMR) By default, RMAN’s backup/recovery method operates on the datafile level. 9i now allows you to recover individual data blocks, called block media recovery (BMR). BMR is quicker than full file recovery where you have a small number of corrupt blocks that need to be recovered. Plus you do not have

Backup and Recovery

2009-06-23T14:52:00.000-07:00

Backup and Recovery Enhancements Trial Recovery The new 9i Trial Recovery feature does not write changes to files, just to the data buffers. Errors are written to the Alert Log. Trial Recovery allows you to test out recovery and find any corrupt blocks without failing at a real recovery and leaving files in an inconsistent state. Use the keyword TEST for a trial recovery: RECOVER DATABASE .

LogMiner

2009-06-22T03:50:00.000-07:00

LogMiner Enhancements 9i improves LogMiner in a variety of ways. The purpose of LogMiner is to allow you to view entries in the redo log (the log that records database changes). LogMiner now has a GUI called the LogMiner Viewer. Use this new GUI in viewing the contents of the redo log files via the view V$LOGMNR_CONTENTS. LogMiner Viewer has Display Options that allow you to specify what

Oracle DataGuard

2009-06-17T14:47:00.000-07:00

Data Guard Oracle’s Standby Database feature has been upgraded and renamed to Data Guard in 9i. Data Guard allows you to set up a standby database (locally or at a remote site) and keep it either completely or approximately “in sync” with the primary database by automatically shipping and applying redo logs to the standby database. This provides for quick disaster and/or off-site recovery

Resumable Space Allocation

2009-06-03T09:45:00.000-07:00

Resumable Space Allocation Resumable space allocation suspends a long-running operation in event of a space allocation error so that you can fix the problem. Then Oracle automatically resumes the long-running operation. Operations that are resumable are: Queries that run out of temporary sort space DML – INSERT, UPDATE, and DELETE statements DDL – CREATE TABLE AS SELECT, ALTER TABLE, CREATE

INSTR

2009-05-20T13:58:00.000-07:00

Oracle INSTR functionThe Oracle function instr returns an integer indicating the position of the character in string that is the first character of this occurrence. This function has the following syntax: instr(string, substring [,position [,occurrence]])with:string: the string that is searched.substring: the substring which we are looking for in the stringposition: The position from which we

External Tables (again)

2009-05-06T13:10:00.000-07:00

External Tables External tables are operating system flat files (stored outside of Oracle), but whose definition is maintained in Oracle’s data dictionary. External tables appear as read-only tables to Oracle applications. They’re useful for reference to flat files that you don’t want to load into Oracle tables (perhaps the flat files are big and rarely accessed, for example). Here are the steps

Oracle Greatest Function

2009-05-05T06:31:00.000-07:00

Oracle/PLSQL: Greatest FunctionHere is what the documentation says :GREATEST- GREATEST returns the greatest of the list of expressions. All expressions after the first are implicitly converted to the datatype of the first expression before the comparison. Oracle compares the exprsessions using nonpadded comparison semantics. Character comparison is based on the value of the character in the

RMAN Commands

2009-05-03T02:09:00.000-07:00

RMAN RMAN is short for Recovery Manager. When the databases use the controlfile as catalog. Following is a short list of the most common commands. To connect to RMAN:>> rman target / show full backups:>> list backup of database; show backups:>> list backup summary; show the archivelogs:>> list archivelog all; show the datafiles:>> report schema; show RMAN settings:>> show all; remove a backup:>>

Oracle Text Indexing

2009-05-01T01:56:00.000-07:00

Text Indexes Oracle Text Indexing There is a mechanism in the Oracle database to do more than just index a column. These are called domain indexes, one of which is a text index. (Others include Spatial and XML). Limitations we Know About. The limitations of the ordinary character column index are all too apparent to many database developers. The current indexes allow for a match from the first

External Tables in Oracle

2009-04-30T01:54:00.001-07:00

External Tables Uploading Daily Files.If you can load a file using SQLLDR you can now load the same file using external tables. Caveat - as long as the file is on the database server.You will first need to prove that you can load the file to the database using SQLLDR.Then, as DBA, create a directory in the database and grant read and write to your user. You need write access to the directory,

Generating XML direct from the Database

2009-04-30T01:44:00.000-07:00

Generating XML direct from the Database There are several ways to generate XML directly from the database using SQL.DBMS_XMLGENThe first method demands minimal knowledge for the maximum output. It is limited in what it does and you are limited inwhat you can can influence.Firstly create a table to store your result.CREATE TABLE temp_xml_table(result CLOB);The procedure then follows the following

Navigating XML in Oracle

2009-04-28T03:17:00.000-07:00

A few code snippets for navigating and extracting dynamically from XML in PL/SQL.--The executable section of the traverse_and_display procedure. BEGIN -- get all elements nodes := xmldom.getElementsByTagName (doc, '*'); -- loop through elements FOR node_index IN 0 .. xmldom.getLength (nodes) - 1 LOOP one_node := xmldom.item (nodes, node_index); display_element (one_node);

Temp Tablespace

2009-04-25T14:59:00.000-07:00

Default Temporary Tablespaces If you CREATE USER and forget to include a TEMPORARY TABLESPACE clause, Oracle uses the SYSTEM tablespace for that user’s sorts. This hurts performance. 9i addresses this by allowing you to specify a system-wide default temporary tablespace. Specify the DEFAULT TEMPORARY TABLESPACE on the CREATE DATABASE statement. Or, define the new temporary tablespace by the

Automated UNDO Management

2009-04-02T09:01:00.000-07:00

Automated Undo Management (AUM) 9i’s new feature Automated Undo Management (AUM) relieves you of the traditional, labor-intensive task of sizing and managing rollbacks. To use AUM, create a tablespace that will be used for rollbacks (the UNDO tablespace). Then start the instance with these two new 9i initialization parameters set to: UNDO_MANAGEMENT = AUTO UNDO_TABLESPACE =

Multiple Block Sizes

2009-03-28T15:02:00.000-07:00

Multiple Block Sizes 9i supports multiple blocksizes within one database. The original block size specified is called the default blocksize or the standard blocksize. An Oracle database can have up to four additional blocksizes. Oracle’s allowable blocksizes are 2, 4, 8, 16, and 32 Kilobytes. The SYSTEM and temporary tablespaces must be of the standard blocksize, and all partitions of a

9i New for RMAN

2009-03-15T06:24:00.000-07:00

Replication Enhancements in 9i

2009-03-14T13:11:00.000-07:00

Replication Enhancements 9i allows you to add a new Master site to a Master Group without quiescing the Master Group. The new Master site must be configured for multi-master replication, you can only add one new Master site at a time, the Master Group must be replicating in asynchronous mode, and all links must be configured for parallel propagation. To ensure transactions are propagated in

SQL Language Enhancements in 9i

2009-02-06T13:05:00.000-08:00

SQL Language Enhancements 9i includes SQL language enhancements to conform to the latest international SQL standard, referred to as SQL-99, as well as some other miscellaneous improvements. Joins SQL statements that perform joins typically combine rows from two or more tables or views. The Cartesian product is a join that has no WHERE clause, so it produces all combinations of rows from the

Performance Enhancements from 8i to 9i

2009-02-05T13:03:00.000-08:00

Performance Enhancements Index Monitoring You can now monitor an index to determine if it’s being used and collect usage statistics. To turn on monitoring: ALTER INDEX my_index MONITORING USAGE; To turn off monitoring: ALTER INDEX my_index NOMONITORING USAGE; Query the dictionary table V$OBJECT_USAGE to view the index utilization data. Skip Scan Index Access Oracle can now use skip scan

Workspace Management

2009-02-03T13:00:00.000-08:00

Workspace Management Workspace management is new in 9i. This feature allows you to version-enable tables, so that different users can have their own private versions of tables. Oracle internally keeps track of changes to different workspaces and you must resolve any update conflicts prior to merging them. Here are the steps to using workspace management: Version-enable one or more tables

Automatic Segment Space Management

2009-01-17T12:58:00.000-08:00

Automatic Segment Space Management Prior to 9i, you had to manage space manually by the PCTFREE, PCTUSED, and FREELIST GROUPS parameters. 9i makes these parameters obsolete because its automatic segment space management uses bitmaps instead of freelists. To use this feature, the tablespace must be locally-managed and you must use the SEGMENT SPACE MANAGEMENT clause: CREATE TABLESPACE

Memory Management

2009-01-16T12:59:00.000-08:00

Memory Management 9i can now dynamically manage the sizing of many of its internal memory work areas. Set the new initialization parameter PGA_AGGREGATE_TARGET to a value indicating total memory for the PGA’s *AREA parameters, then you can set new parameter WORKAREA_SIZE_POLICY to AUTO. The default value for PGA_AGGREGATE_TARGET is 0 – you must set it to some other value first, prior to changing

Bitmap Join Indexes

2009-01-13T12:56:00.000-08:00

Automated Undo Management (AUM)

2009-01-12T12:55:00.000-08:00

Online Table Redefinition

2009-01-11T06:23:00.000-08:00

Online Table Redefinition 9i allows you to redefine a table online. Users can issue DML against the table during most of the online redefinition. For online table redefinition, the table: Must have a primary key, and source and target tables for redefinition must have the same primary key column Can not contain BFILE or LONG columns, user-defined types, or be part of a cluster or be an overflow

Rebuilding Indexes Online

2009-01-10T06:20:00.000-08:00

New Online Operations Rebuilding Indexes Online 8i introduced the online building of indexes. 9i extends this feature to the online rebuilding of reverse key indexes, function-based indexes, and key-compressed indexes on tables and index-organized tables (IOTs). 9i does not support online index rebuilding for bitmap indexes or partitioned local and global indexes. While the index is being

Fast-Start Time-Based Recovery Limit

2009-01-07T06:18:00.000-08:00

Fast-Start Time-Based Recovery LimitYou can specify the target mean time to recover (MTTR) for instance recovery by setting initialization parameter FAST_START_MTTR_TARGET to a value between 0 and 3,600 seconds. Set it dynamically by:ALTER SYSTEM SET FAST_START_MTTR_TARGET = number_of_seconds ;A lower value results in more frequent checkpointing by Oracle but a faster instance recovery. You no

Default Temporary Tablespaces

2009-01-06T06:26:00.000-08:00

My Database Has an Archiver Error Whatever Shall I Do?

2009-01-03T15:28:00.000-08:00

When the database is complaining that the disk is full due to an archiver error, and there is no-one to get to do the work for you then you can follow the following steps to clear the problem down.1. df -h This shows you the mount points and their sizes and usage.2. rman target / This connects you to the rman (Recovery MANager) repository - in this case the database controlfile.3. For a quick

Virtual Private Databases (VPDs)

2008-12-10T06:57:00.000-08:00

The Virtual Private Database (VPD) concept allows multiple users and applications to access a common shared database server while logically separating their data based on security policies. Thus each set of users or applications appears to have its own “virtual private database.”Create security policies that are associated with tables or views. (Associating security policies with tables and views

Database Security

2008-02-29T04:24:00.000-08:00

Database SecurityPassword ManagementComponents of password mgmt:Account LockingPassword Aging and ExpirationPassword HistoryPassword Complexity VerificationSpecify the max number of failed login attempts and time to keep acct locked by using the CREATE PROFILE command.Password Settings that can be used in a profile:FAILED_LOGIN_ATTEMPTS - number of unsuccessful login attempts before locking out

SQLPlus, PL/SQL, and National language Support

2008-02-28T04:23:00.000-08:00

SQLPlus, PL/SQL, and National language SupportSQL*Plus for Database ManagementSERVER MANAGER is being phased out. Future releases will not support SERVER MANAGER. Start using SQL*Plus for all database administration tasks. CONNECT INTERNAL is also going away; can use CONNECT / AS SYSDBA as a possible alternative.Can use SQLPLUS /nolog to enter SQL*Plus for DBA mgmt tasks and not be connected to a

Features of Net8

2008-02-27T04:22:00.000-08:00

Features of Net8Net8 is easier to configure and admin by using the new Net8 Easy Config and Net8 Assistant tools.Clients access a database via a service name instead of a SID. Allows access to multiple services made available by a single database or a single service that spans multiple instances.Database instance registration is a new feature of 8i that allows database instances to register

Recovery Manager

2008-02-26T04:18:00.000-08:00

Recovery ManagerMiscellaneous Availability and Recoverability FeaturesDuplexed & Multiple Archive LogsMultiplexing allows you to place archive redo-logs in more than one location.Can specify up to 5 locations for local destination and 1 location for remote destination. Use the following init parm to specify locations:LOG_ARCHIVE_DEST_n = ‘LOCATION= /dir_name/’where dir-name is a valid directory

Miscellaneous Manageability Features

2008-02-25T04:16:00.000-08:00

Miscellaneous Manageability FeaturesNew Database LimitsMaximum database size: 512 petabytes (petabyte is 2 to the 50th power of bytes)Maximum number of tablespaces: ~2 billionMaximum number of datafiles per tablespace: 1022Maximum number of partitions per table or index: 64,000Maximum number of columns per table: 1000Maximum columns per index: 32New maximum size for CHAR: 2000 bytesNew maximum

Database Resource Manager(DRM)

2008-02-24T04:12:00.000-08:00

Database Resource Manager(DRM)Using the DRM, you can allocate CPU time percentages to different applications and users. You could assign a DSS a lower priority than an OLTP system, and vice versa. In essence, you have the ability to configure your production environment based upon various application priorities.DRM can partition the CPU to a maximum of eight levels. Uses percentages to specify

Tablespace Management

2008-02-23T04:11:00.000-08:00

Tablespace ManagementExtended ROWID’s and Tablespace SizingThe new Oracle 8 ROWID format is 10 bytes and has a different format from the Oracle 7 ROWID. The Oracle 8 ROWID type is known as extended and the Oracle 7 ROWID type is known as restricted.The restricted ROWID format is BRF: 1) dB block number, 2) row in dB block, and 3) datafile number.The extended ROWID format is OFBR: 1) data object

Parallel DDL, Parallel DML, and Parallel Queries

2008-02-22T04:09:00.000-08:00

Parallel DDL, Parallel DML, and Parallel QueriesOracle 8i supports the following parallel operations:table scan, nested loop join, sort merge join, hash join, “not in”, group by,select distinct, union and union all, aggregation, PL/SQL functions called from SQL,order by, create table as select, create index, rebuild index, rebuild index partition,move partition, split partition, update, delete,

Composite Partitioning

2008-02-21T04:07:00.000-08:00

Composite PartitioningComposite partitions are hash-method subpartitions of a range-method-partitioned table. Good for historical data and striping. Provides benefits of regular range-partitioned tables such as better manageability as well as including the parallelism and data placement benefits of hash partitioning.Composite-partitioned tables and indexes are logical structures. Their data is

Partition Maintenance Operations

2008-02-20T04:05:00.000-08:00

Partition Maintenance OperationsPartition Management CommandsMoving PartitionsUse the ALTER TABLE command with the MOVE PARTITION clause to move a partition to a different tablespace. If the partition contains data, all global and local index partitions are marked as unusable. Either rebuild them or drop and re-create the indexes after moving a table partition.Adding PartitionsUse the ALTER TABLE

Basic Partitioning Concepts

2008-02-19T04:03:00.000-08:00

Basic Partitioning ConceptsBenefits of Table and Index Partitioning and General Partition RulesImproved query performance due to distributed data and less I/O contention.Better manageability for large tables. Can be deleted or loaded in smaller pieces.Better backup and recovery performance with more options available than having to backup or recover an entire table.Better availability of data

Manage Large Objects

2008-02-18T04:02:00.000-08:00

Manage Large ObjectsLONG’s vs. LOB’sThere are four types of large object types, or LOBs: CLOB, NCLOB, BLOB and BFILE.While CLOBs, NCLOBs and BLOBs are stored in tablespaces, BFILEs are pointers to binary files stored outside the actual database. Because of this BFILEs are read-only.Tables can contain only one LONG column and many LOB columns.A table containing a LONG can not be partitioned, with

Defining Object Relational Features

2008-02-17T03:58:00.000-08:00

Defining Object Relational FeaturesAn object relational database contains the major aspects of a relational database, and provides for those of an object oriented(OO) database. Relational aspects are structures, operations and integrity rules. OO databases provide for user-defined objects that contain both structures (attributes) and methods.Oracle Object concepts include abstract data types,

Indexes and Indexed-Organized tables

2008-02-16T03:54:00.000-08:00

Indexes and Indexed-Organized tablesBitmap Indexes (BI)ALTER TABLE statement no longer invalidates a BI.New ALTER TABLE statement RECORDS_PER_BLOCK clause that restricts the quantity of rows that can be stored in each dB block.MINIMIZE RECORDS_PER_BLOCK – Calculates the max number of records in any of the current dB blocks and sets that as a max that can be inserted into any new blocks. Improves

Summary Management

2008-02-15T03:48:00.000-08:00

Summary ManagementDimension Tables – Describe business entities. Lookup or cross-reference tables. Examples include categorical information such as item-lists or department lists.Fact Tables – Describe business transactions. Detail tables. Include measures that are character or numeric columns of a fact table. A measure can be simple, computed or multi-table. Simple is a single column in a fact

New SQL Keywords for Computing Totals

2008-02-14T03:45:00.000-08:00

New SQL Keywords for Computing TotalsROLLUP – addition to the GROUP BY clause. Will return a single superaggregate row summary line for each group specified. It is considered an aggregate operator. Can produce subtotals and a grand total.Usage is:GROUP BY ROLLUP (f1...fn), where f is a field or fields referenced in the SELECT portion of a SQL statement.Example:SELECT empno,COUNT(*) "emp

Top-N SQL queries

2008-02-13T03:45:00.000-08:00

Top-N SQL queriesThese type of queries force the ROWNUM condition in a SQL statement to be applied after ordering of the result set. This is achieved by creating a SQL query that contains the ROWNUM condition and a subquery that contains an ORDER BY clause. Top-N SQL queries are more efficient and faster because Oracle avoids sorting all of the rows in the table at once. A feature of Top-N SQL

DBMS_STATS Package

2008-02-12T03:44:00.000-08:00

DBMS_STATS PackageThe procedures in this package gather and manage statistics. Procedures are:GATHER_ INDEX_STATS - get index stats.GATHER_TABLE_STATS - get table, column, and index stats.GATHER_SCHEMA_STATS - get stats for all objects in the schema.GATHER_DATABASE_STATS - get stats for all objects in the database.EXPORT_SCHEMA_STATS - extracts and saves the current stats.IMPORT_SCHEMA_STATS -

Optimizer and Query Improvements

2008-02-11T03:41:00.000-08:00

Optimizer and Query ImprovementsOptimizer Plan Equivalence and Stored PlansStored plans are also known as stored outlines. Execution plans are generated from the outlines and used to provide stability for SQL execution performance despite changes to system configuration or statistics values. A stored plan is mostly a set of hints that cause a SQL statement to use the same execution plan each time

Memory Management

2008-02-10T03:36:00.000-08:00

Memory ManagementThe Large PoolOptional area within the SGA providing large amounts of session memory for MTS and Oracle XA interface, I/O server processes, and backup and restore operations.MTS improved performance due to more SQL stored in cache and less shrinking and growing of the SQL cache.Large pool is able to fulfil the large memory requests of backup and restore operations and I/O server

Java in the Database

2008-02-09T03:35:00.000-08:00

Java in the DatabaseOracle Java ComponentsOracle JServer (Aurora JVM) – Java Virtual Machine that runs in the Oracle Server address space. Is JDK 1.1.6 compliant. Provides a runtime environment for Java objects. Main components of the Oracle JVM are:Library Manager - Loads Java source, class, and resource files into the database.Memory Manager – Takes care of garbage collectionCompiler – Creates

Oracle Security and Net8

2008-02-08T03:33:00.000-08:00

Oracle Security and Net8Virtual Private Database Feature – Provides row-level security across all applications by using a security policy attached to a table or view.Fine-Grained Access Control - Security rules are evaluated at SQL statement parse time when accessing a base table or view in a select or DML statement.Application Context – Is set and reset by a designated package. Use the CREATE

Oracle Parallel Server

2008-02-07T03:32:00.000-08:00

Oracle Parallel ServerCache Fusion – A new diskless ping architecture. Improves inter-instance communication.Increased efficiency of parallel cache management.Easier OPS System administration via extended functionality of Oradebug.Enhancements to OPS Management include a new OEM Parallel server manger applet and the new OPS assistant for installation and creation of OPS dB’s.Users can control

System Management

2008-02-06T03:31:00.000-08:00

System ManagementDatabase Resource Manager – Allows for the database to control and limit the O/S resources given to a particular user or group of users.Can specify multiple remote archive destinations for archive log files.Automatically start multiple archive processes at database startup.Automated Standby Databases – Enhancements that will automatically apply archive logs to standby dB.Any

Partitioning Enhancements

2008-02-05T03:30:00.000-08:00

Partitioning EnhancementsNew MERGE PARTITIONS option as part of the ALTER TABLE clause. – merge 2 adjacent partitions of a table if the range method was used.Updateable Partition Keys – New option as part of the CREATE or ALTER TABLE command ENABLE ROW MOVEMENT allows a row to automatically migrate to a different partition if the partition key value is updated and should now exist in a different

Constraints

2008-02-04T04:25:00.000-08:00

ConstraintsNew Constraint Clause, Properties and StatesConstraints can be modified via that ALTER TABLE...MODIFY CONSTRAINT clause.New constraint property values RELY/NORELY. Controls whether an enabled constraint will be enforced. Used by the query rewrite function of materialized views.RELY - Enables an existing constraint without enforcement. In order for the RELY constraint property to be

Database Features

2008-02-04T03:27:00.000-08:00

Database FeaturesExtensibility Framework FeaturesUser Defined Operators and Extensible Indexing – Can use user-defined operators in SQL statements. New Domain indexes.Extensible optimizer – User specified statistics collection functions, selectivity functions and cost functions.Cartridge Services – Unifies integration and incorporates former Oracle data cartridge optionsObject Relational

Data Warehousing and VLDB Support

2008-02-03T03:26:00.000-08:00

Data Warehousing and VLDB SupportMaterialized Views – Provide faster data warehouse queries by creating a stored summary of pre-calculated results.Transportable Tablespaces – Allows you to move tablespaces between databases.Direct Path Load API – New OCI interface allows the creation of applications that can take advantage of the fast direct load which in the past was only available to the SQL*

Java Related

2008-02-02T03:25:00.000-08:00

Java RelatedOracle JServer – Java Virtual Machine that runs in the Oracle Server address space.JServer Accelerator Option – Makes Java code run faster by removing the overhead of the code interpreter (8.1.6 8i release 2)JDBC DriversSQLJ translatorJava Stored ProceduresSupport for Enterprise Java Beans (EJB’s)Support for CORBA ServicesJPublisher ( Fully functional in 8.1.6 )JDeveloperLoadjava/

Internet Content Management

2008-02-01T03:22:00.000-08:00

Internet Content ManagementWebDB – Allows easy creation of web pages that are linked to the Oracle database. Has an HTML-based interface and provides wizards for the completion of various development tasks.iFS (Internet File system) – A Java app that uses the Oracle8I Java Virtual Machine. Allows for “file system/network drive” type of interface to manage various types of files within the Oracle

Datafiles and Tablespaces

2008-01-04T03:28:00.000-08:00

Datafiles Every datafile has two associated file numbers:An absolute file number uniquely identifies a datafile in the database, Arelative file number uniquely identifies a datafile within a tablespaceAt least one datafile is required for the SYSTEM tablespace of a database. You can add datafiles to tablespaces, subject to the datafile limits: Operating system limit, Oracle system limit,

V$ Table Reference

2008-01-03T03:24:00.000-08:00

V$ Table ReferenceDATABASE BACKUPS, ARCHIVE FILES, AND RECOVERYV$ARCHIVEV$ARCHIVED_LOGV$ARCHIVE_DESTV$BACKUPV$BACKUP_CORRUPTIONV$BACKUP_DATAFILEV$BACKUP_DEVICEV$BACKUP_PIECEV$BACKUP_REDOLOGV$BACKUP_SETV$DELETED_OBJECTV$RECOVERY_FILE_STATUSV$RECOVERY_LOGV$RECOVERY_STATUSV$RECOVER_FILECACHE MANAGEMENTV$CACHEV$DB_OBJECT_CACHEV$LIBRARYCACHEV$ROWCACHEV$SUBCACHECONTROL FILESV$CONTROLFILEV$

9i Architecture

2008-01-02T03:12:00.000-08:00

The machine running your Oracle database has an Oracle database and an Oracle instance. The Oracle Database is the setof files containing the data. The Oracle Instance is the background processes and memory structures that act on the database.The instance is identified through an environment variable ORACLE_SID in the operating system. The instance can therefore only reference one database at any

Oracle date format

2007-08-17T13:36:00.000-07:00

With the functions to_char and to_date, a date format can be used.Example:select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;will return something like: 24/03/2006 14:36:43 Here is a list of all the formats that can be used:Format mask DescriptionCC CenturySCC Century BC prefixed with -YYYY Year with 4 numbersSYYY Year BC prefixed with -IYYY ISO Year with 4 numbersYY Year with 2

SUBSTR

2007-08-13T05:22:00.000-07:00

SUBSTR Returns a substring. For more information see Oracle substring SUBSTRB Returns a substring expressed in bytes instead of characters.SUBSTRC Returns a substring expressed in Unicode code points instead of characters.SUBSTR2 Returns a substring using USC2 code points.SUBSTR4 Returns a substring using USC4 code points.

SOUNDEX

2007-08-11T05:21:00.000-07:00

SOUNDEX returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English.Example: select * from emp where lastname SOUNDEX('SMITH');