db_writer_processes

I wrote the first part of this example in 2012.
 
The database writer copies data blocks from the buffer cache onto disk. The db_writer_processes initialization parameter determines how many processes will do this task. Its default value is 1 or cpu_count / 8, whichever is greater. I found an Oracle 9 database on a Tru64 server with cpu_count set to 1:
 
SQL> l
  1  select value from v$parameter
  2* where name (more...)

LOGICAL_READS_PER_SESSION

One of the resources you can limit in a profile is called logical_reads_per_session. According to the Oracle documentation, it is used to:
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk. I decided to try it out in an Oracle 11.1 database.
 
I created a profile called for_andrew, which would limit logical_reads_per_session to 10000:

SQL> conn / as sysdba
Connected.
SQL> create profile (more...)

ALTER USER Hangs on row cache lock

I created the following UNIX shell script called loop1.ksh:

Oracle 11.1 > cat loop1.ksh
#!/bin/bash
export ORACLE_SID=PQEDPT1
export ORAENV_ASK=NO
. oraenv
sqlplus / as sysdba << eof
grant create session to andrew
identified by reid1
/
exit
eof
echo "User created"
./loop2.ksh > loop2.log1 &
./loop2.ksh > loop2.log2 &
./loop2.ksh > loop2.log3 &
./loop2.ksh > loop2.log4 &
./loop2.ksh > loop2.log5 &

ORA-01166

We have some jobs which copy the datafiles from 1 database to another then recreate the control file to give the output database a new name. Some of these jobs are intelligent i.e. they query the input database to dynamically create the SQL to do the rename. This particular job is not. Part of it is shown below and you can see that the MAXDATAFILES parameter was set to 120:

STARTUP NOMOUNT
CREATE CONTROLFILE (more...)

Setting NUMWIDTH in PL/SQL Developer

This post is based on a problem I was asked to look at recently.
 
A colleague was using PL/SQL Developer to compare two tables in an Oracle 11 database. He was matching the rows on a key column then checking the corresponding values from a different column and reporting them if they did not match. His query returned almost 2000 rows but they appeared to have equal, not different values. Five people looked at (more...)

DBMS_STATS Causes ORA-00600 [15851]

Uncategorized
| Apr 3, 2013
I noticed this in an Oracle 11.2.0.1.0 database. DBMS_STATS failed with an ORA-00600 and the first argument was [15851]. On investigation, it seemed to have something to do with the fact that the table had a function based index:
 
SQL> SELECT COUNT(*) FROM DBA_INDEXES