How to purge objects from the recycle bin

sqlplus / as sysdba
purge dba_recyclebin; --> purges all recyclebin objects, globally
purge tablespace user_data user scott; --> purge only scott's Objects from the user_data tablespace
purge tablespace user_data; --> purge all recyclebin Objects from the user_data tablespace

conn scott/tiger
purge recyclebin; --> purges scott's recycle bin only

The next statement purges the specified object from the recycle bin. The first example refers directly to the system-generated name of the object, the second is refering to the original name of the object.

purge table "BIN$W/n28BqBgRbgU64JAQoz3A==$0";
purge table emp; 

Automatic startup and shutdown script for Oracle databases on the Linux Platform

On most installations of the Oracle database, it is desirable to add a script for automatic startup and shutdown.
This is how to do it.
The instructions holds true for both 11g and 12c.

1. Edit the /etc/oratab file, so that the entry for your database has a Y at the end:

testdb01:/u01/oracle/product/db/112:Y

2. cd /etc/init.d

3. create a file called dbora, and add the following lines to it. Note that you should enter the name of your ORACLE_HOME path and the oracle software installation owner as values for ORA_HOME and ORA_OWNER, respectively:

#! /bin/sh 
# description: Oracle auto start-stop script.
#
# Set ORACLE_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORACLE_HOME.

ORA_HOME=/u01/oracle/product/db/112
ORA_OWNER=oracle

case "$1" in
'start')
    # Start the Oracle databases:
    # The following command assumes that the oracle login
    # will not prompt the user for any values
    # Remove "&" if you don't want startup as a background process.
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
    touch /var/lock/subsys/dbora
    ;;

'stop')
    # Stop the Oracle databases:
    # The following command assumes that the oracle login
    # will not prompt the user for any values
    su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME" &
    rm -f /var/lock/subsys/dbora
    ;;
esac

4. Set permissions:

# chgrp dba dbora
# chmod 750 dbora

5. Create symlinks to the different run level script directories:

ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora

Verify the symlinks. They should all point back to /etc/init.d/dbora

ls -la /etc/rc.d/rc0.d/K01dbora
ls -la /etc/rc.d/rc3.d/S99dbora
ls -la /etc/rc.d/rc5.d/S99dbora

What about the listener?
According to Oracle, as long as it has the default name of "LISTENER", you don't need to add it to your startup/shutdown script. If you use a different name, you must add the following line to the /etc/init.d/dbora script:

$ORACLE_HOME/bin/lsnrctl {start|stop} listener_name

The original instructions from Oracle can be found here

How to compare the number of objects per schema in two databases

After a migration of a database to a new server, or after any kind of replication, you would like to get some verification that your objects were created.

A potentially very useful query could help you with this task.

You need a database link from the target database back to the source database

create public database link proddb01_old 
connect to system
identified by mysecretpassword
using 'myproddb01';

Verify that the link works as intended:

SQL> select d.name,i.host_name from v$database d, v$instance i;

NAME      HOST_NAME
--------- -------------------------------
PRODDB01 myserver01.mydomain.com

Save the following in a script, and execute it from the target database via sqlplus:

set lines 200
col count_new format 99999
col count_old format 99999
col "object owner" format a30
col "Result" format a20
set pages 100

prompt ============================================
prompt compare users and their object count
prompt ============================================

WITH newprod_users AS(
 SELECT target.owner AS new_owner, count(*) AS count_new
 FROM   dba_objects target
 GROUP BY owner
 ORDER BY 1
),
oldprod_users AS (
 SELECT source.owner AS old_owner, count(*) AS count_old
 FROM   dba_objects@proddb01_old  source
 GROUP BY owner
 ORDER BY 1)
SELECT newprod_users.new_owner "object owner", 
       newprod_users.count_new "NEW PROD",  
       oldprod_users.count_old "OLD PROD",
       DECODE(newprod_users.count_new,
              oldprod_users.count_old,'Num rows identical','Num row differs') "Result"
FROM   newprod_users JOIN oldprod_users
ON     newprod_users.new_owner = oldprod_users.old_owner
ORDER BY "Result" DESC;

Example output:

============================================
compare number of users
============================================

object owner                     NEW PROD  OLD_PROD Result
------------------------------ ---------- ---------- --------------------

USER1                               1329       1329 Num rows identical
USER2                                  2          2 Num rows identical
USER3                               1750       1750 Num rows identical
USER4                                409        389 Num row differs
USER5                                961       1167 Num row differs
USER6                                251        256 Num row differs

The report can be easily modified to produce for example a semi-colon separated list, which can in turn be pulled into an MS Excel file if desirable.