DBA Maestro - Helpful DBA tips

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 recently came across situation where I needed to recreate a public database link in a non-prod database environment and application team didn't know the password required for this particular user being used for the db link. I did the following to obtain the DDL out of production: set long 999999 select dbms_metadata.get_ddl('DB_LINK', 'LINKNAME', 'PUBLIC') from dual; I had to do the same for a user that didn't get created that I wanted to quickly create myself without using exp/imp. select dbms_metadata.get_ddl('USER','&USERNAME') from dual;