trace query

use the following to trace another session:

dbms_system.set_bool_param_in_session(sid, serial#, 'timed_statistics',true);
dbms_system.set_sql_trace_in_session(sid,serial#,truefalse);

note: There are plenty of other options, and the above are just defaults.

changing tab stops in unix editors

ensure the following is in the $HOME/.exrc file

set tabstop=4
set shiftwidth=4

log in again (the .exrc must be run as part of the shell when logging in)
be carefull when setting this up as it could change the formatting of all the sripts .

counting cpu's

On SUN Servers, use the following to work out the number of cpus:

expr `mpstat wc -l` - 1

where:

mpstat gives information about cpu's and heading info,
wc-l gives the number of lines in the output,
expr lets you use some maths to remove the heading in the count of number of cpu's.

add / create redo logs

use the following to view current redo log file information
select * from v$log ;

use the following to drop a redo log (if required)
alter database drop logfile group ...

use the following to add a new redo log
alter database add logfile group ... '...' size ...m reuse;

when trying to drop a logfile the following error occurs

ERROR at line 1:ORA-01624: log 2 needed for crash recovery of instance CSHMNT
ORA-00312: online log 2 thread 1: '/db/cshmnt/redolog/001/redo02.log'

Try : alter system checkpoint
This should checkpoint the database and make all redolog files INACTIVE, allowing to drop redo log files.

putting standby database into readonly

1. cancel standby recover mode.
SQL> alter database recover managed standby database cancel;

2. put db into readonly mode
SQL> recover standby database until cancel;

NOTE:
It is possible that the standby database was configured to shutdown the standby database instance when managed recovery mode is cancelled. If this is the case, the command above will shutdown the standby instance and you will need to mount the standby database in standby mode before attempting to put the standby database in READ ONLY mode:

SQL> startup nomount
SQL> alter database mount standby database;
SQL> recover standby database until cancel;

pl/sql and roles

permissions granted through roles will fail when accessed through packages. See the note and link below, but these permissions fail because roles are turned off during compilation due to performance and security reasons.

Oracle requires that permissions to non-owned objects in a stored procedure be granted directly to the user. Roles are temporarily turned off during compilation, and the user has no access to anything granted through them. This is done for performance and security reasons. Roles can be dynamically activated and deactivated via the SET ROLE command, and it would be a large overhead for Oracle to constantly check which roles and permissions are currently active.

http://articles.techrepublic.com.com/5100-10878_11-6183799.html

set roles

Some applications use the set role function within the application so the tables for a specific query can only be granted via the application.

However the set role function is all encommpassing, meaning that if an active role is not included in the set role function, then that current role will become non-current.

To test how a database session would work without roles, run : set role none;
this is very useful when testing how commands will work within procedures etc.

current database privileges

To find out what privileges a user currently uses:

login as the user

select * from session_roles -- gives the current roles the user can use.
select * from session_privs -- gives the current privs the user can use.

at

use the unix at command to run a unix script at a given time.

To schedule a script via at:

echo "some command"at
where time could be hh:mm[ampm]

at -l shows the id that are currently scheduled
at -r [jobid] will remove a particulat job from the schedule.

note: The -r option will only work upto the point where the job has been scheduled.

locked statistics

you can lock statistics. I'm guessing this means that the stats for this table will never change (until at least you lock them).

to lock statistics try:

dbms_stats.lock_table_stats(owner, tablename);

there should be additional parameters to lock column and index statistics as well.

Use the dba_tab_statistics view to verify what tables have been locked.

slow query

use the following when testing something that requires a slow query.

http://www.jlcomp.demon.co.uk/deterministic.html

create or replace function wait_row(
i_v in varchar2,
i_sec in number default 5 ) return varchar2
deterministic
parallel_enable as
begin
sys.dbms_lock.sleep(i_sec);
return i_v;
end;
/

buffer busy waits

http://www.jlcomp.demon.co.uk/bbw.html

ORA-27300: OS system dependent operation:semget failed with status: 28

Found this error when creating a 10.2.0.3 database on solaris 9.

This error can occur on any unix host when starting an oracle database. The error is saying that the semiphore parameters are not set correctly.

In cases like this, the first parameter to look for is SEMMNS

Essentially this parameter tells how many os processes shared memory components can use on the server. So the important oracle database parameter to look at is process.

Use the following calulation to work out what SEMMNS should be set to:

SEMMNS = sum(oracle processes) + num oracle databases + system requirements

This all suggests when setting up a new server you should have an idea of the number of databases running on the server or set the SEMMNS parameter to a fairly high number.

vnc setup

Use the following to set up xwindows using vnc, putty

1. Make sure vnc server has been started on unix host.
If it hasn't then run:
- vncserver :serverid

2. Ensure the putty client has been setup with tunnels option completed.
Create / Update a profile, goto ConnectSSHTunnels
port should be 590serverid and host should be localhost.

note: This is only required if protocol that is being used is ssh.

Start putty client.

3. run xhost +

The xwindows window should now start. Run whatever oracle xwindows program you want.

If the window fails to start then run:
- vncserver -kill :serverid

PUSH_SUBQ hint

Found the following in a 10G database.

insert into ... select /*+ PUSH_SUBQ */ * from ... type query, only a portion of the rows would be returned.

removing the hint, all expected rows would be inserted into the table.

A definite trick for young players, as the insert statement never returned any errors.

group by and having options

The having command with the group by command is very useful when wanting specific rows, based on the group by construct to be removed.

For example, to work what values of a unique key is forcing the create unique index type command to fail, use something like:

select column, count(*) from table
group by column
having count(*) > 1;

will cause only occurances to display that have more than two rows, i.e.: the key values that is causing the create unique index to fail.

v$parameter2

This view is slightly different to v$parameter in that it seperates out the values for specific parameters into seperate rows.

Some examples of parameters where this may occur are :
- control_files
- utl_file_dir

Useful when wanting to query specific values, say a particular utl_file_dir or control file.
For this to work, the seperate values for these parameters must be seperated by commas.

i.e.:
alter system set utl_file_dir=
'/u01/ora_utl_file/hpac/',
'/home/sequel/cms/test/socrates/output/',
'/u01/ora_utl_file/contt/' scope=spfile ;