DBA Maestro - Helpful DBA tips

Have you ever needed to recreate/drop a database link for another user/schema owner in an Oracle database?  Here's how I did it.  --  recreate Database link CREATE PROCEDURE schema_owner.create_db_lnk AS BEGIN     EXECUTE IMMEDIATE 'CREATE DATABASE LINK COGNOS.WORLD '             ||'CONNECT TO CRUD IDENTIFIED BY "password123" '             ||'USING ''(description=(address=(protocol=tcp)(host=host-scan)(port=1521))(connect_data=(service_name=test.artisdba.com)))'''; END create_db_lnk; / exec schema_owner.create_db_lnk; drop procedure schema_owner.create_db_lnk; --  Drop database link CREATE PROCEDURE schema_owner.drop_db_lnk AS BEGIN      EXECUTE IMMEDIATE 'DROP DATABASE LINK COGNOS.WORLD'; END drp_db_lnk; / exec schema_owner.drop_db_lnk; drop procedure schema_owner.drop_db_lnk;

Here is a script that I use to dynamically generate the SQL to recreate tablespaces in another database.  I essentially want all tablespaces that are non-system related.  You can model this script by whatever you need to use the package DBMS_METADATA.GET_DDL in your DBA life. -- @gen_ddl_tbsp.sql spool tbsp_ddl_gen.sql set lines 120 head off feed off pages 0 long  1000000 select 'set lines 120 pages 0 long 1000000' from dual; select 'spool tbsp_ddl.sql' from dual; select distinct 'SELECT DBMS_METADATA.GET_DDL(' || '''' || 'TABLESPACE' || '''' || ',' || '''' || tablespace_name || '''' ||') || ' ||'''' ||' /' ||'''' ||' FROM DUAL;' from dba_tablespaces where tablespace_name in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','USERS','SYSAUX','UNDOTBS1','UN

I was in the middle of configuring OGG for a client and came across an issue when getting to the portion of installing support for sequences.  This script creates some procedures to be used by OGG processes.   I hope this helps someone as it took me some digging around to figure this one out.  Enjoy! SQL> @sequence Please enter the name of a schema for the GoldenGate database objects: GGADMIN Setting schema name to GGADMIN SP2-0804: Procedure created with compilation warnings SP2-0804: Procedure created with compilation warnings UPDATE_SEQUENCE STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- 58/14      PLW-06009: procedure "UPDATESEQUENCE" OTHERS handler does not end            in RAISE or RAISE_APPLICATION_ERROR GETSEQFLUSH Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors SEQTRACE Line/pos   Er