Oracle « MemoSoup

Oracle: using recycling bin

Information about the objects in recycle bin:

SELECT object_name,
original_name, TYPE,
can_undrop , can_purge ,
droptime
FROM recyclebin

show recyclebin

Restore

flashback TABLE mytable TO before DROP;

Clear recycle [...]

Oracle: source text for the view, package and other objects

The best method is to use dbms_metadata package:

SELECT dbms_metadata.get_ddl(UPPER(’&OBJ_TYPE’),UPPER(’&OBJ_NAME’),UPPER(’&OWNER’))
FROM dual;

Additional methods could be also used:
Source text of the package

SELECT text
FROM dba_source
WHERE upper(name) LIKE upper(’&which_object’)
ORDER BY line ;

Use $ORACLE_HOME/bin/wrap utility to encrypt the package (there is no unwrap)
(Well, actually, there is unwrap – just look in the search machine for the words unwrap10 or rewrap…)

Source [...]

Oracle: information about ASM

Get the information about ASM disks:

SELECT GROUP_NUMBER, DISK_NUMBER, TOTAL_MB/1024 GB, NAME FROM v$asm_disk;

Get the information about ASM diskgroups (including used space and the free space):

SELECT GROUP_NUMBER, NAME, TOTAL_MB/1024 TOTAL_GB, FREE_MB/1024 FREE_GB [...]

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:

restore the file from the backup
recover datafile ‘&the_file_name’ ;
alter database datafile ‘&the_file_name’ online;

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

restore the files from the backup
recover tablespace [...]

Oracle: logon trigger

Create the table to save logon information:

CREATE TABLE logonaudittable (
event VARCHAR2(10),
sid NUMBER,
serial# NUMBER,
TIMESTAMP DATE,
username VARCHAR2(30),
osuserid VARCHAR2(30),
machinename VARCHAR2(64)
);

Continue reading Oracle: logon trigger

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 [...]

Oracle: long running queries

col opname FOR a40
col units FOR a10

SELECT sid
,opname
,sofar
,totalwork
,units
,elapsed_seconds
,time_remaining
FROM v$session_longops
WHERE sofar [...]

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 [...]

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} [...]

Oracle: export/import using compressed file

Export to the compressed file

/etc/mknod my_own_pipe p
gzip -c -9 < my_own_pipe > mydump.gz &
exp username/password …… file=my_own_pipe
rm -f my_own_pipe

Import from the compressed file:

/etc/mknod my_own_pipe p
gunzip -c mydump.gz > my_own_pipe &
imp username/password ……. file=my_own_pipe
rm [...]