Skip navigation.

Foreordain my Transaction to Fail

Kevin Meade's picture

I saw one of those really interesting pieces of code recently. A guy wanted to run his transaction and make it fail when it was all done. Normally one would put ROLLBACK at the end of the transaction in order to undo a transaction's work. But this guy did not want to do that. He wanted to keep his transaction code unchanged, commit at the end and everything. He had several reasons for this, among them being that he did not have access to all the code he was working with and thus could not put the ROLLBACK where it was needed, and indeed suspected (as we eventually found to be true), that somewhere in the code stream there was a commit being done without his permission thus splitting his transaction in ways he did not intend. So he wanted a way to FOREORDAIN (determine ahead of time) that his transaction would fail no matter even if it went to conclusion without error. For this he came up with a I think a clever hack. Seems to me this might have some use, if I can figure out what that use might be. So here is the cool solution.

Creating and Unpacking Delimited Strings in SQL

Kevin Meade's picture

Recently a friend asked me for this. I see it a lot on OraFaq as a question in the forums so here are the basics of working with delimited strings. I will show the various common methods for creating them and for unpacking them. Its not like I invented this stuff so I will also post some links for additional reading.

A Layman's Understanding of Star Schemas.

Kevin Meade's picture

Star Schemas are proliferating with warehouses these days. Many practitioners I have met in this space are a bit new to the concept of star schemas and as such keep falling back to old habits. But this is only hurting them. So I'll try to give my simplistic view of how it works in the hopes of granting some clarity on the practice of Star Modeling and overcoming our previous training to resist its concepts.

Interchange the Values of 2 Columns

You might face a situation where you need to interchange the values of two columns in an Oracle database table. This article will explore ways to achieve this.

The ever expanding UNDO tablespace.

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.

Info on PIVOT

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.

Performing a quick and efficient Performance Tuning audit

Checks to be performed at the machine level (note the example is Red Hat Linux)

run queue should be ideally not more than the number of CPU’s on the machine

At the maximum it should never be more than twice the number of CPU’s.

This is denoted by the column ‘r’ in the vmstat output shown below

vmstat – 5

face:/opt/oracle>vmstat 5
procs memory swap io system cpu
r b swpd free buff cache si so bi bo in cs us sy id wa
4 1 488700 245704 178276 12513572 0 1 10 17 48 1365 40 12 43 5

How to import table with custom type

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 checksyntax

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

Oracle SQL Model - An Alternative to UNION for Creating A list of Static Values

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