Google
Web tarasdom.com

 

Oracle information


Books about Oracle
Excellent book about Oracle design

Standard healthcheck queries:
select 'alter ' || 
 decode(lower(object_type), 'package body','package',lower(object_type)) || 
	' ' || owner || '."' || object_name || '"' ||
 decode(object_type,'PACKAGE BODY', ' compile body;',' compile;') INVALID_OBJECTS 
from DBA_OBJECTS 
where STATUS = 'INVALID';
select distinct status from dba_indexes ;
select distinct status from dba_ind_partitions;
select 'ALTER INDEX '||
	 table_owner || '.' || index_name || ' rebuild online ;'
from dba_indexes where status = 'UNUSABLE' 
union
select 'ALTER INDEX ' || 
	index_owner || '.' || index_name || ' rebuild partition ' || 
	partition_name || ' online ;'
from dba_ind_partitions where status = 'UNUSABLE';
SELECT name, 
 unrecoverable_change# , 
 unrecoverable_time 
FROM v$datafile 
order by 2;

Tablespaces in backup mode

select d.tablespace_name, b.time 
from dba_data_files d, v$backup b
where d.file_id = b.FILE# 
and b.STATUS = 'ACTIVE' ;

Hidden Oracle Parameters
The following query shows the hidden Oracle parameters (both session and instance level)
col "Parameter" for a50
col "Session Value" for a15
col "Instance Value" for a15
select a.ksppinm "Parameter", 
 b.ksppstvl "Session Value", 
 c.ksppstvl "Instance Value",
decode(a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file', a.ksppity) "Type",
a.ksppdesc description
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
and a.ksppinm like '%¶m%'
order by a.ksppinm;

Object search
If You are looking for some specific Oracle object, but do not remember exactly its name, this query could help:
col SHORT_OBJECT_NAME for a30
select
 substr( ob.OBJECT_NAME,1,30)"SHORT_OBJECT_NAME",
 substr( ob.OWNER,1,15)"SHORT_OWNER" ,
 ob.OBJECT_TYPE,ob.CREATED
 from dba_objects ob
 where object_name like upper(%&template%) escape '\'
order by 1,2;

The summary size of the database
col MB format 999,999,999.99
BREAK ON report 
COMPUTE sum LABEL "Total size:" of MB ON report
select TABLESPACE_NAME, sum( BYTES/1024/1024 ) MB
from dba_data_files 
group by TABLESPACE_NAME 
union
select TABLESPACE_NAME, sum( BYTES/1024/1024 ) MB
from dba_temp_files 
group by TABLESPACE_NAME ;

Source text for triggers,views,synonyms

Views:
select text
from dba_source
where upper(name) like upper('&which_object')
order by line ;
set long 5000
select text 
from dba_views 
where view_name = upper('&which_view');
SELECT view_definition 
FROM v$fixed_view_definition 
WHERE view_name='&which_view';
Synonyms:
select TABLE_OWNER || '.' || TABLE_NAME || 
	decode ( db_link , null , '' , '@' || db_link ) SYNONYM_OBJECT 
from dba_synonyms 
where SYNONYM_NAME = '&which_synonym';
Triggers:
select 
 'create or replace trigger "' || trigger_name || '"'
 || chr(10)|| decode( substr( trigger_type, 1, 1 ), 'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) || chr(10) || 
 triggering_event || chr(10) || 'ON "' || table_owner || '"."' || table_name 
 || '"' || chr(10) || decode( instr( trigger_type, 'EACH ROW' ), 0, null, 'FOR EACH ROW' ) || chr(10) , 
 trigger_body 
from user_triggers 
where trigger_name = upper('&which_trigger') 
Use $ORACLE_HOME/bin/wrap utility to encrypt the package.

There is no unwrap - see the statement in Oracle Metalink:

Note 341504.1:
How to unwrap the wrapped code or re-create in a unwrap form
You can’t unwrap the wrapped source code. This is the main functionality of it. Otherwise your users will be able to reverse-engineer the wrapped application to the original source code. All you need to do is to keep a copy of the unwrapped application.


Links:
Oracle Quick Reference Guide it's very helpful PDF document, containing Oracle commands for the versions 8i and 9i.

©
Page was modified 04.09.2007