Oracle « Previous Entries

Oracle: check the existance of logon/logoff triggers

Friday, July 25th, 2008

SELECT DECODE((COUNT(trigger_name)),0,’LOGON trigger missing’, ‘Number of logon triggers: ‘ || COUNT(trigger_name) ) “INFO”
FROM sys.dba_triggers
WHERE TRIGGERING_EVENT LIKE ‘LOGON%’ AND status=’ENABLED’ AND owner=’SYS’
UNION
SELECT DECODE((COUNT(trigger_name)),0,’LOGOFF trigger missing’,”, ‘Number of logoff triggers:’ || COUNT(trigger_name)) “INFO”
FROM sys.dba_triggers
WHERE TRIGGERING_EVENT LIKE ‘LOGOFF%’ AND status=’ENABLED’ AND owner=’SYS’

The field TRIGGERING_EVENT could have the spaces at the end! Very clever…

Oracle: redo log switches by date

Thursday, May 8th, 2008

The following script help to find, how often the redo logs were switched.
It calculates the number by date and by hour.

Oracle: usage of the tablespaces (permanent and temporary)

Friday, March 28th, 2008

SET pagesize 10000
SET COLSEP ‘|’
SET VERIFY off
SET serveroutput ON SIZE 1000000
BREAK ON report
COLUMN tablespace_name format a30 heading ‘TABLESPACE’
COLUMN sizegb   format 9999999999D9 heading ‘SIZE-Gb’
COLUMN usedproc format 999D99 heading ‘USED-%’
COLUMN status format a10 heading ‘STATUS’
COMPUTE SUM LABEL ‘Total size:’ OF sizegb ON report
SELECT b.tablespace_name ,
       b.bytes/1024/1024/1024 AS sizegb ,
       NVL(100-((a.bytes/b.bytes)*100), 100) usedproc,
    [...]

Oracle: plan of the running query

Friday, March 28th, 2008

col object_name FOR a40
SELECT operation,     
 options,     
 object_name, 
 partition_id
FROM   v$sql_plan
WHERE address IN
 ( SELECT sql_address FROM v$session WHERE sid = &sid.)
ORDER BY id;

Oracle: using DBMS_METADATA for getting table structure

Monday, March 17th, 2008

SET LONG 5000
SELECT dbms_metadata.get_ddl(’TABLE’,'EMP’,'SCOTT’);

Oracle: using substitution variables in sqlplus

Monday, March 17th, 2008

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

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

« Previous Entries
Keep on coding