truncate partitions

Use the following to truncate specific partitions

alter table ... truncate partition ...

add new partitions

Use the following to add new date partitions ...

alter table ... add partition ... values less than (to_date(...))
...

outer joins

An outer join is essentially a join that grabs all intersecting records from two tables and all the records from one of joining tables.

select ... from t1 a, t2 b
where a.col1 = b.col1(+);

This will grab all intersecting records as well as all records from t1.

execute immediate exceptions

Use the following to generate some exceptions for execute immediate

SQL> BEGIN
EXECUTE IMMEDIATE 'garbage';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK );
RAISE;
END;


where the dbms_output statement can be used instead of some logging procedure.

The output would be something like ...

BEGIN
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 6

adding quotes in dynamic sql

Use the following to add a quote when writing dynamic sql. Useful when you can't remember ascii codes.

SELECT 'select ... '|| ' where username like ''A%'' ;'

The output looks something like the following ...
select ... where username like '%A' ;

hiding CODE

Use the following to hide pl/sql code in the database.

wrap iname="sql in file" oname="sql out file"
compile in the database.

Works an absolute trick !!!

database upgrade

Below are some steps when upgrading / patching a database:

1. Install SOFTWARE in new OH
2. BACKUP database
3. SHUTDOWN DB
4. Update ORATAB for database so that it is pointing to new OH.
5. RUN oraenv for DB and check that env parameters are pointing to the new OH.
6. COPY network and database parameter files are copied to new OH.
7. RUN PREUpgrade Steps
8. RUN UPGRADE STEPS
9. RUN POST UPGRADE STEPS (backup configuration, monitoring, etc)
10. BACKUP Database.