SQL - 2/6 - MemoSoup

Oracle: wait statisics by block class

This query helps to get information about the wait statistics (in fact, this query is contained in GV_$WAITSTAT)

SELECT inst_id, DECODE (indx, 1,’data block’, 2,’sort block’, 3,’save undo block’, 4,’segment header’, 5,’save undo header’, 6,’free list’, 7,’extent map’, 8,’1st level bmb’, 9,’2nd level bmb’, 10,’3rd level bmb’, 11,’bitmap block’, 12,’bitmap index block’, 13,’file header block’, [...]

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 DBMS_METADATA for getting table structure

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

Some other values of the type field for dbms_metadata.get_ddl function:

INDEX DB_LINK PACKAGE

(See also “Oracle® Database PL/SQL Packages and Types Reference“)

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: disable all constraints referencing the table

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 EXECUTE IMMEDIATE ‘ALTER TABLE ‘||cur.owner||’.'||cur.table_name||
‘ MODIFY CONSTRAINT ‘||cur.constraint_name||’ DISABLE’;
END LOOP;
END;

Oracle: info about the corrupted block

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

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;

Meta

Tags