Oracle database quick reference

Similar to the Unix/Linux quick reference post, this one lists multiple snippets about the Oracle database. Feel free to drop a comment with your own Oracle "gems".

# how to list all triggers affecting a table
select * from all_triggers where triggering_event like '%INSERT%' and table_name='PUT_TABLE_NAME_HERE';

# if you get error ORA-24170 when dropping an user see the article below
drop user myuser cascade;
ORA-24170: MYUSER.SOME_QUEUE_NAME is created by AQ, cannot be dropped directly
read: http://forums.oracle.com/forums/thread.jspa?messageID=1184946

# set password for sys/system oracle users
ALTER USER SYSTEM IDENTIFIED BY password;
ALTER USER SYS IDENTIFIED BY password;

# create a full export of an Oracle database:
exp system/password file=database.dmp full=y statistics=none

# import an Oracle database dump file:
imp USERNAME/PASSWORD@ORACLE_SID file=database.dmp full=y
imp USERNAME/PASSWORD@ORACLE_SID file=database.dmp fromuser=ORIGINAL_USER touser=NEW_USER grants=n

# run Oracle imp as a SYSDBA:
imp \'/ as sysdba\' file=database.dmp

# purge and disable Oracle 10g recyclebin tables:
-- login as sysdba
purge dba_recyclebin;
alter system set recyclebin = OFF scope=both;

# list tables with a field with a given name:
select * from all_tab_columns where column_name like '%PUT_A_COLUMN_NAME_HERE%';

# rebuild all Oracle indexes for performance tuning:
select 'ALTER INDEX ' || OWNER || '.' || OBJECT_NAME || ' REBUILD;' from dba_objects where status = 'VALID' and object_type in ('INDEX')

# improve indexes performance:
select 'analyze table ' || OWNER || '.' || object_name || ' compute statistics;' from dba_objects where object_type in ('TABLE');

# Oracle database performance optimizer - run it every time we have significant changes on a database schema
sqlplus /nolog
connect system/
begin
dbms_stats.gather_schema_stats (
ownname=>'PUT_SCHEMA_NAME_HERE',
degree=>3);
end;
/
quit;

# compile all invalid objects in Oracle:
The best way to compile all database objects that are invalid is to use a script in the $ORACLE_HOME/rdbms/admin directory named utlrp.sql. This script finds all objects in the data dictionary that are invalid and compiles them. This script is typically mentioned in patch notes but you can use it any time a schema change occurs.
I also included other alternative approaches just in case.

1. best way:
sqlplus system/password
@$ORACLE_HOME/rdbms/admin/utlrp.sql

2. by schema:
select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;' from dba_objects where status = 'INVALID' and object_type in ('PACKAGE','FUNCTION','PROCEDURE') and owner='PUT_SCHEMA_NAME_HERE'
union
select 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;' from dba_objects where status = 'INVALID' and object_type in ('PACKAGE BODY') and owner='PUT_SCHEMA_NAME_HERE';
set pages 50000
spo compile.sql
/
spo off
@compile.sql

3. using the utl_recomp APIs:
connect sys/password as sysdba;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
quit;

# how to enable table shrink in a Oracle 10g database:

alter table mytable enable row movement;
alter table mytable shrink space;

# how to shrink multiple schemas in a Oracle 10g database:

select 'ALTER TABLE ' || OWNER || '.' || OBJECT_NAME || ' ENABLE ROW MOVEMENT;' from dba_objects where object_type in ('TABLE') and owner in ('MY_SCHEMA1','MY_SCHEMA2','MY_SCHEMA3');

select 'ALTER TABLE ' || OWNER || '.' || OBJECT_NAME || ' SHRINK SPACE CASCADE;' from dba_objects where object_type in ('TABLE') and owner in ('MY_SCHEMA1','MY_SCHEMA2','MY_SCHEMA3');

# how to shrink datafiles:
select 'ALTER TABLE ''' || OWNER || '''.''' || TABLE_NAME || ''' SHRINK SPACE COMPACT' from all_tables;

SELECT VALUE FROM V$PARAMETER WHERE upper(NAME) = 'DB_BLOCK_SIZE';

SELECT 'ALTER DATABASE DATAFILE ''' || FILE_NAME || ''' RESIZE ' || CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) || 'M;' SHRINK_DATAFILES FROM DBA_DATA_FILES DBADF,
(SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS*&&BLKSIZE/1024/1024)- CEIL((NVL(HWM,1)* &&BLKSIZE)/1024/1024 ) > 0 ;

# how to find which objects have extents at the end of a datafile.
Relocating these objects makes shrinking of relevant datafile possible.

select *
from (
select owner, segment_name,
segment_type, block_id
from dba_extents
where file_id =
( select file_id
from dba_data_files
where file_name = &FILE )
order by block_id desc
)
where rownum <= 5;