During a quite evening of my last on-call bout I was alerted from our monitors that the UNDO tablespace was running out of free space. Thought of adding of a new data file and be done with it; When I checked the current allocation for this tablespace it was already at 40G - couldn't believe what I was seeing. The undo_retention was set to 7200 and max query length in v$undostat was not that high. One column that did caught my eye was the tuned_undoretention, its value was way very high.

Database tables are structured in columns and rows. However, some data lends itself to switching row data as column data for expository purposes. The pivot operation in SQL allows the developer to arrange row data as column fields. For example, if there are two customers who have both visited a store exactly four times, and you want to compare the amount of money spent by each customer on each visit, you can implement the pivot operation.

When importing table that contained custom type, you will encounter an oracle error as shown below:
Import of table containing object type(s) fails with IMP-00061 IMP-00063

cause:
When you import a table that references a type, but a type of that name
already exists in the database. Import attempts to verify that the pre-existing
type is in fact the type used by the table(rather than a different type that
just happens to have the same name).

RMAN is recommended for backups/restore of oracle databases.It is a very powerful tool and has variety of useful features.There is 'checksyntax' feature in RMAN which allows one to check RMAN scripts syntactically without executing it.This feature is available from 10G release 2.It proves to be useful for checking scriptsfor live databases where one doesnt have leisure to execute scripts any time and any times!!

bash-3.00$ vi rman_bkp.rman

RUN {
ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/data/backup/Hotbkp_Full_%D_%d_%s_%p'; BACKUP database; RELEASE CHANNEL c1;
allocate channel c2 type dis

Here’s an alternative to the union statement for creating a collection of values using the Oracle SQL Model construct in 10g.

[code]--Using SQL Model clause to return a list of items.

SELECT col_1 Product, col_2 Price, col_3 Description
FROM (SELECT -1 col_id,
RPAD ('X', 30) col_1,
TO_NUMBER(RPAD ('1', 3)) col_2,
RPAD ('X', 40) col_3
--Change RPAD number to reflect length of column value.
FROM DUAL)
WHERE col_id <> -1
MODEL
DI

Make Your SQL Statements Readable

Even though readability doesn't affect the actual performance of SQL statements, good programming practice calls for readable code. Readability is especially important if you have multiple conditions in the WHERE clause.

According to the Enterprise System Spectator's website Oracle is busy laying off a large number of employees. According to the article, 8000 people or 10-15% of the company's workforce will lose their jobs today. Apparently similar layoffs are happening at SAP, Infor and possibly several other companies.

Several Oracle employees confirmed these layoffs at LayoffBlog.com.

Answer: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways.

I've been working on way to capture changed rows using ora_rowscn for part of an ETL routine. Here are some details of what I've tested and found.

By default, the ora_rowscn pseudo column reports the scn at block level. This means that rows sharing the same block will have the same ora_rowscn, and if one row were to be changed, then scn would be altered for both blocks.

Pro Oracle Spatial for Oracle Database 11g shows how to take advantage of Oracle Databases built–in feature set for working with location–based data. A great deal of the information used in business today is associated with location in some way, and analysis of that data is becoming ever more important in todays mobile and highly connected world. In Pro Oracle Spatial for Oracle Database 11g, authors Ravi Kothuri and Albert Godfrind address