Oracle: using substitution variables in sqlplus

Get the value:


ACCEPT my_password CHAR PROMPT 'Password: ' HIDE
ACCEPT birthday DATE FORMAT 'dd/mm/yyyy' DEFAULT '01/01/1950' PROMPT 'Enter birthday date: '

Declaring the variable

DEFINE the_answer = 42

Undefine the variable

UNDEFINE the_answer

How to remember the result of the query

column the_date new_value the_rundate noprint;
select to_char(sysdate, 'DDMMYYYY_HH24MI') the_date from dual;

select '&the_rundate' from dual ;

Save the variables to the file

store set myvars.txt create
store set myvars.txt replace
store set myvars.txt append

Assign several values to the variable

DEFINE my_list = " 'the Life', 'the Universe', 'and Everything'"

select *
from Book
where answer in ( &my_list );

Oracle: file needs recovery (offline mode)


select d.file# f#, d.name, d.status, h.status
from v$datafile d, v$datafile_header h
where d.file# = h.file#
and (d.status not in ('SYSTEM','ONLINE') or h.status != 'ONLINE' );

If there are such files, the recovery is necessary:

  1. restore the file from the backup
  2. recover datafile ‘&the_file_name’ ;
  3. alter database datafile ‘&the_file_name’ online;

Another possibility (if there are a lot of files):

  1. restore the files from the backup
  2. recover tablespace ‘&tbs_name’ ;
  3. alter tablespace ‘&tbs_name’ online;

Oracle: password function

Find the name of the current password function


SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';

Change the password function for the profile:


ALTER PROFILE &profile. LIMIT PASSWORD_VERIFY_FUNCTION &function_name.;

Example of the password function:


CREATE OR REPLACE FUNCTION dummy_func
(USERNAME VARCHAR2, PASSWORD VARCHAR2, OLD_PASSWORD VARCHAR2)
RETURN BOOLEAN IS
BEGIN
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password is the same as the username');
END IF;
RETURN(TRUE);
END;

Oracle: PERFSTAT.STATS$MUTEX_SLEEP_PK violated

There is a known problem with Oracle statspack report in 10g, which could cost You at least one lost statspack snapshot.

The following message is written into the alert log:


ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at “PERFSTAT.STATSPACK”, line 5264
ORA-06512: at “PERFSTAT.STATSPACK”, line 104
ORA-06512: at line 1

There is a Note 382993.1 in Metalink, which describes this bug.

The workaround is simple:

alter table perfstat.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;

create index perfstat.STATS$MUTEX_SLEEP_PK on
STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);

… and check the Metalink for the news, if the bug was fixed.

[amazon-product]007222360X[/amazon-product]

Oracle: read the entries from alert log

Sometimes it’s necessary to read the information from the alert log together with the timestamp of the error.

The very simple Unix-shell script helps to do this:


echo
echo 'Enter # of lines:'
read NUM
tail -$NUM alert_${ORACLE_SID}.log | awk '
BEGIN {prev="" ; ret=1 }
/^(...-|Error)/ { if ( prev !~ /^(...-|Error)/ ) { print "" ; print prev;} print $0;ret=0}
{prev=$0}
END { exit ret } '

This code could be assigned to some hotkey in Your favorite telnet application, so You just need to press something like “Alt+A” to see the quick review of the recent Oracle errors.

Example:

Enter # of lines:
1000

Sep 01 19:42:03 2009
Errors in fileARC0: Archiving not possible: error count exceeded

Sep 01 19:42:06 2009
Errors in file testsrv_arc0_23486.trc:
ORA-16038: log 1 sequence# 112 cannot be archived
ORA-19502: write error on file “”, blockno (blocksize=)
ORA-00312: online log 1 thread 1: ‘redo_g1_m1.log’