Oracle Version 10g comes along with a new feature called Recyclebin. It is what it’s name is promising you – a bin collecting all the dropped objects and data for you. This recylebin is a prerequisite in order to have FLASHBACK DROP functionality and therefore provided to every Oracle user.

SQL> desc recyclebin
 Name                                      Null?    Type
 —————————————– ——– ————
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 ORIGINAL_NAME                                      VARCHAR2(32)
 OPERATION                                          VARCHAR2(9)
 TYPE                                               VARCHAR2(25)
 TS_NAME                                            VARCHAR2(30)
 CREATETIME                                         VARCHAR2(19)
 DROPTIME                                           VARCHAR2(19)
 DROPSCN                                            NUMBER
 PARTITION_NAME                                     VARCHAR2(32)
 CAN_UNDROP                                         VARCHAR2(3)
 CAN_PURGE                                          VARCHAR2(3)
 RELATED                                   NOT NULL NUMBER
 BASE_OBJECT                               NOT NULL NUMBER
 PURGE_OBJECT                              NOT NULL NUMBER
 SPACE                                              NUMBER

Perhaps important to know is that the recyclebin is a public synonym and it is based on the view user_recyclebin which in turn is based on sys.recyclebin$ table and does not work for SYS objects.

Comparing behavior as in 8i / 9i, there is no issues with DROPping the table behaviour wise in 10g . The space is not released immediately and is accounted for within the same tablespace / schema after the drop, but there is NO recycling of the objects when dropping a tablespace or user.

 SQL> DROP TABLE EMP;
Table dropped.

SQL> SELECT owner,original_name,operation,type
  2  FROM dba_recyclebin
  3  /

OWNER                          ORIGINAL_NAME                    OPERATION TYPE
—————————— ——————————– ——— ——
SCOTT                          EMP                              DROP      TABLE

Note: We can also create a new table with the same name at this point.

Freeing used space though:
In order to completely remove the table from the DB and to release the space the new PURGE command is used.

From SCOTT user:
SQL> PURGE TABLE EMP;
Table purged.

SQL> SHOW recyclebin
SQL>

But we can also DROP and PURGE a table with a single command

From SCOTT user:
SQL> DROP TABLE EMP PURGE;
Table dropped.

SQL> SELECT *
  2  FROM recyclebin
  3  /
no rows selected

So there will be no need to PURGE the table out of Recylebin later on and space is directly marked as free again.

Recylebin is for sure a usefully new feature of Oracle 10g, but rarely some Oracle user is used working with it today.  Based on your own or customers needs it might appear meaningful to deactive it though.

As for on Oracle 10gR2 recyclebin is an initialization parameter and by default its ON, but you can disable recyclebin by using the following commands:

SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;

Or you are going to change the initialization values within SPFILE or INIT.ORA permanently.

But be aware about  the dropped objects, when recyclebin was ON will remain in the recyclebin even if you set the recyclebin parameter to OFF for your session or the even the whole system.

For freeing this allocated space also, you can use one of the following two commands:

From SCOTT user:
PURGE recyclebin; (Purge all objects in Recyclebin)

From DBA user:
PURGE dba_recyclebin; (Purge all objects / only SYSDBA can)

Being forced to work with MySQL Databases, not every Web Developer is familiar with databases in general.

Now the good news is that when working in a LAMP (Linux, Apache, MySQL and PHP) environment you do not really need to have deep knowledge about relational databases to succeed.

Most tools like CMS do a full automated setup for you and the only knowledge it needs to have starting with them, is the server and database name and finally the login credentials (username/password) to access it.

Just later on the one or other maintenance work will occur and you will feel the need of having some nice and neat database interface to work with. Knowing about this need today I will recommend you a few tools and you should feel free to choose one or each of them based on your platform and taste.

The first tool I will recommend you is called phpMyAdmin. It is written in PHP and as a so called browser tool it works platform independent and flawless in all common browsers. Very often being offered by hosting providers it is mostly already part of your hosting package and easy to access and use. Coming along with all necessary options like browsing and navigating through your databases, it also offers a SQL interface and the often necessary export/import options. Personally it is my favorite tool to work with.

Once you are familiar with phpMyAdmin, phpMyBackupPro is another very similar tool you might find it worth to look at. Also browser based it is mainly written for interactive exports and imports, but can get also use for scheduled automatic backups.

For Windows platform only I would like to recommend you a tool called MyDB Studio. Coming along with a well designed interface it offers all features you need and the license it needs is given away for free in case you only use it for private and non commercial interests. It just needs you to give them a valid email address and the key is emailed to you quickly after.

And now finally and last in case you are such a Macintosh guru, I would like to recommend you CocoaMySQL. It does a similar great job like the others and can get used right away after the installation has taken place. What I like is the console view of is, showing you all command issued during the past and looking at it you might even learn some SQL (Structured Query Language).

Working with Oracle, using TEMPORARY tablespaces with tempfiles is best practice today. Unfortunately there are two situations in which you may encounter trouble as for the tempfiles are missing and sort operations do no longer succeed.

Curiously and based on the fact that Oracle does not record checkpoint information in tempfiles, Oracle can even start up a database with a missing tempfile.

DBW0 will write to a trace file indicating the tempfile is not found, but the database opens normally and later on only sort operations will fail with some of the Oracle Errors ORA-01116, ORA-01157 and ORA-25153.

Such situation can be caused by simply the OS file related to a tempfile cannot be not found anymore or the logical tempfiles attached to the TEMPORARY tablespace do not exist anymore.

This can happen in case the tempfile was located on a disk that crashed, had a bad controller, or some other type of media failure, a recovery or cloning of the database was done before and/or finally and purely caused by human error.

Solution : Drop the logical tempfile and add a new one

SQL> alter database tempfile ‘/oracle/oradata/SID/tempfile.tmp’ drop;

Database altered.

SQL> select tablespace_name, file_name from dba_temp_files;

no rows selected.

SQL> alter tablespace temp add tempfile ‘/oracle/oradata/SID/tempfile.tmp’ size 100m;

Tablespace altered.