cursor cache

An article at orafaq on cursors

Main point is that

OPEN_CURSORS

parameters limits the number of currently opened cursors by each session. If this number is exceeded the session will get en error. And

session_cached_cursors

is something totally different and independent from open_cursors. The idea behind the cursor cache is to hold "popular" cursors opened. If a cursor is being parsed every time it's opened, it causes a latch contention and concurrency starts to suffer. So to protect the database from a badly written code, you can cache the cursors. The number of cached cursors is set by the session_cached_cursors. The value is per-session. It can be set system-wide (needs a restart) or by ALTER SESSION. As I have understood, cursor cache is a part of PGA, so it will not effect the shared pool if this parameter is increased.

export/import through gzip

1. create a pipe:

$ mknod gzpipe p

2. start gzip

$ gzip -c < gzpipe > exp.gz &

3. run exp

$ exp scott/tiger file=gzpipe ... 

import would then be:

$ gunzip -c < exp.gz > gzpipe &
$ imp scott/tiger file=gzpipe ... 

Unfortunately piping cannot be used with 10g datapump. Here's what oracle whitepaper says:

Can I use gzip with Data Pump?
Because Data Pump uses parallel operations to achieve its high performance, you
cannot pipe the output of Data Pump export through gzip. Starting in Oracle
Database 11g, the COMPRESSION parameter can be used to compress a Data Pump
dump file as it is being created. The COMPRESSION parameter is available as part
of the Advanced Compression Option for Oracle Database 11g.

How to change database SID

dbnewid is an oracle utility which enables you to change sid of a database.

You can change either the DBNAME or DBID or both.

dbnewid does not change the global name of the database. to do that the following command must be run:

ALTER DATABASE RENAME GLOBAL_NAME TO [name].[domain];

Also you must manually change the DB_NAME instance parameter and recreate the password file. And if you changed DBID, database must be opened using RESETLOGS parameter.

SHUTDOWN IMMEDIATE
STARTUP MOUNT

1. changing only database name:

% nid TARGET=SYS/oracle@test_db DBNAME=test_db SETNAME=YES

2. changing only dbid:

% nid TARGET=SYS/oracle@test_db

3. changing both:

% nid TARGET=SYS/oracle@test DBNAME=test_db