Setup Oracle's SGA size

I always work with Oracle databases on my laptop. I prefer to install them myself so I can tune it to run without using all my memory. Also, instead of installing Oracle directly on my laptop I always do so on virtual machines because this way it is easier to share my environments with my colleagues and to setup individual sandboxes for each project I am working on.

When I have to work with databases installed by others I found I usually have to decrease how much memory is assigned to Oracle. The scripts below describe how to do just that.

• update Oracle's SGA size:

-- show current SGA size
select sum(value)/1024/1024 from v$sga;

-- set SGA target size to 256MB
alter system set sga_target = 256M scope=both;

-- list buffer size
select current_size from v$buffer_pool;

-- list all pools
select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

-- additional Oracle memory settings:
-- make sure you don't setup SGA to the same or higher size than MEMORY
alter system set memory_max_target = 512M scope=spfile;
alter system set memory_target = 512M scope=spfile;
alter system set sga_max_size = 256M scope=spfile;
alter system set sga_target = 256M scope=spfile;
-- you will need to restart Oracle for the changes to take effect (e.g. dbshut and dbstart)

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;