Oracle - 2/5 - MemoSoup

Oracle: check the existance of logon/logoff triggers

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

Oracle: redo log switches by date

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

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

Oracle: plan of the running query

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

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 bin

purge recyclebin;

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

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

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

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

Meta

Tags