Various tips

Update obj$
    
update sys.obj$ o
set o.ctime = o.ctime - (30*6)
, o.mtime = o.mtime -(30*6)
, o.stime = o.stime -(30*6)
where trunc(o.ctime) = trunc(sysdate)
or trunc(o.mtime) = trunc(sysdate)
or trunc(o.stime) = trunc(sysdate)
where owner='SYS';

#Taken from note 304593.1
update dependency$ set p_timestamp =
(select stime from obj$
where obj#=p_obj#)
where (select stime from obj$
where obj#=p_obj#)!=p_timestamp and
(select type# from obj$ where obj#=p_obj#)=29
and (select owner# from obj$ where obj#=p_obj#)=0;

commit;
alter system flush shared_pool;

alter session set nls_date_format = 'yyyy/mm/dd';

alter table xp_stat_log modify (log_level number)

select s.* 
    , 'drop ' || decode(s.owner, 'PUBLIC', 'PUBLIC ', '') ||'synonym '||
    decode(s.owner, 'PUBLIC', '', s.owner||'.')||synonym_name||';' drop_script
    , 'create ' || decode(s.owner, 'PUBLIC', 'PUBLIC ', '') ||'synonym '||
    decode(s.owner, 'PUBLIC', '', s.owner||'.')||synonym_name||' for '||table_owner||'.'||table_name||
    decode(s.db_link, null, '', '@'||s.db_link)||';' creation_script
    from dba_synonyms s, dba_db_links l
    where l.db_link (+)= s.db_link;

Test db links
select distinct 'tnsping ' || host from dba_db_links

Drop materialized view log on aldo.pippo;

where aldo.pippo is the real master table

CREATE ROLE "VIEW_SESSIONS" NOT  IDENTIFIED;

grant select on SESSION_PRIVS to view_sessions;

grant select on v_$lock to view_sessions;

grant select on v_$locked_object to view_sessions;

grant select on v_$open_cursor to view_sessions;

grant select on v_$process to view_sessions;

grant select on v_$rollname to view_sessions;

grant select on v_$sess_io to view_sessions;

grant select on v_$session to view_sessions;

grant select on V_$SQLTEXT_WITH_NEWLINES to view_sessions;

grant select on v_$transaction to view_sessions;

Enable client trace using:

TRACE_LEVEL_CLIENT=16
LOG_DIRECTORY_CLIENT=/app/oracle/product/9.2.0/network/trace

ALTER SESSION SET max_dump_file_size = UNLIMITED;
ALTER SYSTEM SET EVENTS 'immediate trace name systemstate level 10';

select distinct owner from dba_tables

select 'analyze table ' || owner||'.'||table_name || ' validate structure;' 
from dba_tables
where not owner in ('SYS', 'SYSTEM', 'OUTLN', 'PERFSTAT')

select distinct owner from dba_indexes

select 'analyze index ' || owner||'.'||index_name || ' validate structure;' 
from dba_indexes
where not owner in ('SYS', 'SYSTEM', 'OUTLN', 'PERFSTAT')

Enable parallel (tested on 9i)
leave all "parallel" parameters to default, except for:
#Deprecated on 10g
parallel_automatic_tuning = TRUE
parallel_adaptive_multi_user = TRUE

See also
PARALLEL_THREADS_PER_CPU
PARALLEL_MAX_SERVERS
PARALLEL_MIN_SERVERS, default 0

INSTANCE_GROUPS, Rac only INSTANCE_GROUPS
alter session set PARALLEL_INSTANCE_GROUP

alter session FORCE PARALLEL or PARALLEL value for table and indexes

Query and monitor
select * from v$pq_sysstat
select * from v$pq_sesstat

select value/1024/1024/1024 from v$osstat where stat_name = 'PHYSICAL_MEMORY_BYTES'

Flashback query
    
select * from 
aldo.persone
AS OF timestamp to_date('05/12/2005 01:40', 'dd/mm/yyyy hh:mi')

Case insensitive database

9i
Avoid duplicates on a column
create unique index upper_v on p ( upper(v));

http://www.dba-oracle.com/t_oracle10g_release_2_case_insensitive_searches.htm

object dependencies
@?/rdbms/admin/utldtree.sql
exec deptree_fill('PACKAGE','crm', 'tts');
select * from ideptree;

Masquerade chars
, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
de_db_name, 'A', 'E'), 'I', 'O'), 'U', 'B'), 'C', 'D'), 'E', 'F'), 'G', 'H'), 'I', 'L'), 'M', 'N'), 'O', 'P'), 'Q', 'R'), 'S', 'T'), 'U', 'V') n_name

Dump a column value
#do not use for ANYDATA, value may change
select dump(table_name), rawtohex(table_name) from dba_tables

Wait events
Scattered read: full table scan
Sequential read: index scan
Log file sync: the client is waiting for its stuff being written to the redo log

Concept: Data flow
Block change __
Block change __|
Bloch change __|
               |
               |
          Redo  Buffer
               |
               |_Commit - Log Buffer 1/3 full - 1MB - 3 secs
               |
            Redo log -> Undo -> Buffer Cache -> Data

Maintenance Cleanup Script
[cleanup.sh]
rm -rf /oradata/al12/archive/*
rm -rf /app/oracle/diag/rdbms/al12/al12/trace/*
rm -rf /app/oracle/diag/rdbms/al12/al12/trace/*
rm -rf /app/oracle/diag/rdbms/al12/al12/cdump/*
rm -rf /oradata/al12/admin/adump/*
rm -rf /app/oracle/diag/rdbms/al12/al12/incident/*

#Generated with:
select 'rm -rf ' || value || '/*' from v$parameter
where 
(
  (upper(name) like 'LOG_ARCHIVE_DEST' escape '!'
  or upper(name) like 'LOG_ARCHIVE_DEST!__' escape '!'
  or upper(name) like 'LOG_ARCHIVE_DEST!___' escape '!')
  or name in ('background_dump_dest', 'user_dump_dest', 'core_dump_dest', 'audit_file_dest')
)
and not value is null

#and a couple of manual entries...