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