Skip navigation.

SQL & PL/SQL

SQL and PL/SQL Articles

Tuning Goldilocks SQL

Tuning Goldilocks SQL

... then Goldilocks went into the bears' Data Centre and there were 3 Oracle databases. The first was a Data Warehouse. Goldilocks checked the AWR, but all the SQLs were to-o-o-o-o-o-o-o big; they all used full scans, hash joins, bitmap-index combining, partition pruning and parallelism and couldn't be tuned any more. So Goldilocks went to the second Oracle database. It was an OLTP system with hundreds of concurrent users. Goldilocks fired up SQL Tuning Advisor, but all the SQLs were to-o-o-o-o-o-o-o small; they used unique index scans and cluster-joins and couldn't be tuned any more. So Goldilocks went to the third Oracle database. It was an Operational Data Store with a rolling 3 month retention. Goldilocks found SQLs that were joining a million rows with Nested Loops joins, buffer cache hit ratios of 50%, and under-utilised disk. She smiled, opened up Tom Kyte's Expert Oracle eBook on her second monitor and got to work. This database was ju-u-u-u-u-u-u-u-st right ...

Word-Based Password Generator

During my experiences with different environments, I have been tasked with maintaining passwords for different information systems. This includes operating system accounts (root, oracle, administrator) and Database accounts (sys, system, dbsnmp).

It can be sometimes difficult to remember many different passwords. I have seen some people overcome this by documenting the passwords, sometimes just in a plaintext file, sometimes encrypted, sometimes just on a ‘Post-It’ Note under the keyboard.

I try and retain the passwords in memory.

Using Regular Expressions in Oracle

Everyday most of us deal with multiple string functions in Sql. May it be for truncating a string, searching for a substring or locating the presence of special characters.

The regexp functions available in Oracle 10g can help us achieve the above tasks in a simpler and faster way.

ORA-31223: DBMS_LDAP: cannot open more than 63 LDAP server connections

Kevin Meade's picture

Working with LDAP has made me appreciate the maturity of the Oracle RDBMS. That said, LDAP is pretty popular it seems. To that end my cohort in crime Dave Smith and I (Kevin Meade) have been tasked with many a work request to update LDAP entries related to database data. In integrating our databases and LDAP via the DBMS_LDAP package we came across this error. A quick Internet search revealed lots of people with the same error but no answers. It turns out that the error is exactly what it says it is, but that finding the reason for it is another matter. Here we discuss what we think the error means and the three most likely ways to get it.

RECYCLEBIN in Oracle

arun_kumar_a's picture

What is Recycle Bin
Oracle has introduced "Recycle Bin" Feature Oracle 10g to store all the dropped objects.
If any table in Oracle 10g is dropped then any associated objects to this table such as indexes,
constraints and other dependant objects are simply renamed with a prefix of BIN$$.

Why Recycle Bin
A user drops a very important table--accidentally, of course--and it needs to be revived as soon as possible.

Move all database objects from one tablespace to another

Sometime is necessary to move all database objects from one tablespace to another.

Basically are tables, indexes and lobs.

This script permit move database objects from one user and/or tablespace to another:

[code]
set echo off
set heading off

var tbs_source varchar2;
var tbs_dest varchar2;
var schema_user varchar2;

-- '' if it isn't relevant.
exec :tbs_source := 'SOURCE_TBS';
-- '' if it isn't relevant.
exec :schema_user := 'SOURCE_USER';
exec :tbs_dest := 'DEST_TBS';

select 'Transporting tablespace ' || :tbs_source || ' or user ' || :schema_user || ' to tables

Random Password Generator with a template

Original blog post in here.

Give the password template to this function and get random password. It acts like dbms_random.string() function but takes more than one character as a parameter.

Here is a scenario (template)
- First char must be UPPERCASE
- Second and third one must be NUMBER
- Make fourth a NON-ALPHANUMERIC character
- Fifth one must be LOWERCASE
- Sixth is a NUMBER again
- Seventh is any character

Killing users sessions in oracle

KILLING USERS SESSIONS IN ORACLE
Author JP Vijaykumar Oracle DBA
Date Apr 14th 2010

A detailed discussion on redo, undo and Oracle's read consistency are
beyond the scope of this document.

A detailed discussion on setup, troubleshooting of user connections
in MTS environment is beyond the scope of this document.

Before embarking on a killing spree of Oracle sessions, let us explore
the options, limitations, dos and don'ts.

Dynamic SQL - for newbies

Hello,

I have been dealing with dynamic SQL for some time. And that is something that is still a mystery for many newcomers (and experienced Oracle guys as well).
Here I am going to tell how you can use dynamic SQL. How, but more important is "why", because when creating a comment you should never describe "how" - you always should describe "why".

TRUNCATE

SQL> rem
SQL> rem Ejemplo con TRUNCATE:
SQL> rem
SQL> rem Para empezar hay que crear una tabla:
SQL> rem
SQL> create table truncate_example as select * from dba_tables
2 /

Table created.

SQL> rem La tabla tiene muchas líneas:
SQL> rem
SQL> select count(*) from truncate_example
2 /

COUNT(*)
----------
2522

SQL> rem Y 10 pedazos en el disco:
SQL> rem