Wednesday, December 23, 2009

Setting db_file_multiblock_read_count

A very nice procedure to see the effects on a full table scan query and to estimate the value for db_file_multiblock_read_count.

l_count PLS_INTEGER;
l_starting_time PLS_INTEGER;
l_ending_time PLS_INTEGER;
dbms_output.put_line('dbfmbrc seconds');
FOR l_dbfmbrc IN 1..32
EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count='||l_dbfmbrc;
l_starting_time := dbms_utility.get_time();
SELECT /*+ full(t) */ count(*) INTO l_count FROM big_table t;
l_ending_time := dbms_utility.get_time();
l_time := round((l_ending_time-l_starting_time)/100);
dbms_output.put_line(l_dbfmbrc||' '||l_time);

Table must be enough big in order not to be cached !!!

Taken by an excellent book (Apress:Troubleshooting Oracle Performance)

Monday, December 7, 2009


RMAN backup of archivelogs fails:

ORA-19588: archived log recid xxxx stamp xxxxxxxx is no longer valid

The rman backup job had been started TWICE.

The initial resync for each job identified the same set of logs to be backed up.
Both jobs were trying to backup and delete the SAME archivelog set, one of which failed eventually as the other job had already backed up and deleted the log.

Make sure that concurrent backups of the archivelogs are not run.

Friday, December 4, 2009

CTAS and column default values

Affecting 8i up to 10gR2

If you use CTAS ( Create Table As Select ) to copy a table, the new duplicate table does not contain the default values of the original table. As a result, if you use DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, it does not copy column's default values.

To implement the solution, please execute the following steps:

1) Do not use CTAS to copy the table, use another method.

2) Use CTAS and then modify columns giving them their default values where necessary.

3) If you want the column's default values to be copied when using DBMS_REDEFINITION, the table has to be pre-created with the default column values before getting re-organized with DBMS_REDEFINITION.

Manually purge scheduler jobs logs

From sqlplus as sys or system run :


ORA-00959: tablespace '_$deleted$0$0' does not exist

If this error occurs when you try to add a partition on partition table then all the table partitions had once been moved to another tablespace and the original tablespace has now been dropped or new tablespace where partitons now reside has been renamed to the original tablespace name.


alter table [table_name] modify default attributes tablespace [tablespace name];

Wednesday, December 2, 2009

Oracle Security Options

A nice FAQ for Oracle Security

Oracle 10g : View bind variables values without tracing

In 10g there is a new view called: V$SQL_BIND_CAPTURE

V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors.Each row in the view contains information for one bind variable defined in a cursor such as :

1) Reference to the cursor defining the bind variable
(hash_value, address) for the parent cursor and (hash_value, child_address) for the child cursor.
2) Bind metadata
Name, position, datatype, character set ID, precision, scale, and maximum length of the bind variable.

3) Bind data
One of the bind values used for the bind variable during a past execution of its associated SQL statement.

Bind values are not always captured for this view. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT datatypes) and when the bind variable is used in the WHERE or HAVING clauses of the SQL statement( This excludes update and insert statements).

You can query this view using sql_id or hash_value for an sql statement.


1) Find has_value for your statement

select sql_text,sql_id,hash_value from v$sqlarea
where lower(sql_text) like '%[my statement]%';

2) FInd bind variables values

SELECT name,datatype_string,was_captured,last_captured,value_string,value_anydata
FROM v$sql_bind_capture
WHERE hash_value='[hash_value_from_previous_sql]';