Oracle « Previous Entries Next Entries »

Oracle: disable all constraints referencing the table

Wednesday, March 5th, 2008

begin
FOR cur IN (SELECT fk.owner, fk.constraint_name , fk.table_name
        FROM all_constraints fk, all_constraints pk
        WHERE fk.CONSTRAINT_TYPE = ‘R’ AND
        pk.owner = ‘&which_owner’ AND
        fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
           AND pk.TABLE_NAME = ‘&which_table’
        ) loop
    [...]

Oracle: info about the corrupted block

Monday, February 25th, 2008

SELECT * FROM dba_extents
WHERE  file_id = &file_id
AND    &block_id BETWEEN block_id AND block_id + blocks - 1;

Oracle: using recycling bin

Monday, February 18th, 2008

Information about the objects in recycle bin:

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

or
show recyclebin
Restore

flashback TABLE mytable TO before DROP;

Clear recycle bin
purge recyclebin;

Oracle: source text for the view, package etc

Friday, January 25th, 2008

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 text of the views

SET long 5000
col text FOR a80
SELECT text FROM dba_views WHERE view_name = [...]

Oracle: flashback usage

Friday, January 18th, 2008

– To save the flashback information for the last 30 minutes:
ALTER SYSTEM SET UNDO_RETENTION = 1800;
–
SELECT * FROM some_table AS OF TIMESTAMP (SYSDATE – INTERVAL ‘5‘ MINUTE);
SELECT * FROM some_table AS OF TIMESTAMP (’2008-01-18 06:31:58′, ‘YYYY-MM-DD HH24:MI:SSS’);
SELECT * FROM some_table AS OF SCN 364583948;
– dbms_flashback could be also used
EXECUTE dbms_flashback.enable_at_time (‘18-JAN-08 11:00:00‘);

Oracle: information about ASM

Wednesday, January 16th, 2008

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 FROM v$asm_diskgroup;

Oracle: file needs recovery (offline mode)

Tuesday, January 15th, 2008

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

Oracle: logon trigger

Sunday, November 4th, 2007

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)
);

Oracle: password function

Sunday, November 4th, 2007

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

Saturday, November 3rd, 2007

col opname for a40
col units for a10

SELECT  sid
,opname
,sofar
,totalwork
,units
,elapsed_seconds
,time_remaining
FROM v$session_longops
WHERE sofar != totalwork;

« Previous Entries Next Entries »
Keep on coding