Ever Dropped an Oracle Table and Wish You Hadn’t?

If you’ve ever dreamed of going to a recycle bin like you can on Windows, and “undropping” an Oracle database table… Oracle has made your dream come true!

Introduced with Oracle 10g, Oracle’s recycle bin works a lot like what you’re used to in Windows. When this feature is enabled (the default setting), dropped tables don’t actually get deleted. Instead, they “land” in the recycle bin and can be restored.

The recycle bin is actually a data dictionary table containing data about dropped objects. What actually happens when recycling is enabled is that Oracle renames each dropped table and associated objects (indexes, LOB segments, triggers, etc.) with a system-generated name that starts with “BIN$.” The table data is still available and you can query it like any other table.

To “undrop” a dropped table that’s “in the recycle bin,” you perform an operation known as a “flashback drop.” The command syntax is: FLASHBACK TABLE <name> TO BEFORE DROP. This command just renames the BIN$<name> table back to its original name. (You’ll find an excellent overview of Oracle 10g’s Flashback features here.)

The downside of the recycle bin is that dropped tables are really only renamed. Their table segments are still taking up space in your tablespace, which still counts against your user tablespace quotas. To recover the space associated with dropped tables you need to explicitly “purge” unwanted tables and associated objects from the recycle bin.

Fortunately, purging is pretty easy and flexible. If you have the SYSDBA privilege, you can purge everything from all the recycle bins with PURGE DBA_RECYCLEBIN. Or you can purge just the user recycle bin with PURGE RECYCLEBIN. You can even purge “recycled” objects by schema and user with PURGE TABLESPACE <tablespace> or PURGE USER <user>. Users have access in the recycle bin only to those objects that they themselves dropped.

It’s normal to end up with multiple versions of a table in the recycle bin. In these situations, Oracle always restores the newest version. To restore an earlier version, you can simply refer to it by its unique, BIN$-prepended name. Alternatively, you can use FLASHBACK multiple times until you restore the version you want.

The RECYCLEBIN initialization parameter turns the recycle bin feature on or off at the system or session level. Turning recycling off doesn’t prohibit you from restoring objects that were in the recycle bin before you disabled it.

If you want to keep the recycle feature enabled but bypass it when dropping specific tables, just add the PURGE clause to the DROP TABLE statement; e.g.:

SQL> DROP TABLE TABLE_XYZ PURGE;

If you’re not already using the recycle bin feature, why not turn it on and get it working for you today? There’s really nothing to lose—literally!

For expert advice on how to fine-tune use of Oracle’s recycle bin for your environment, schedule a free consultation with Buda Consulting.

For more information:

Compliance 101 for Oracle DBAs

Regulatory compliance issues are top-of-mind for today’s senior executives. New laws and industry regulations are changing how organizations acquire, store, manage, retain and dispose of data. Every Oracle DBA should be aware of these changes because of their sweeping impacts on the DBA job role.

Compliance goes hand-in-hand with security because regulations often mandate that organizations be able to attest or even prove that data—and therefore databases—are secure and controlled. In this context, Oracle DBAs are directly involved in implementing and managing the policies and technologies that support compliance.

What are some of the key regulations that impact Oracle DBAs? Here in the US, one of the most prevalent is Sarbanes-Oxley (SOX), aka the U.S. Public Accounting Reform and Investor Protection Act of 2002. SOX is meant to reduce fraud and improve financial reporting. Its impact on IT is sweeping. In particular, it holds the CFO responsible to guarantee the processes used to produce financial reports, which invariably involve software accessing data stored in databases via processes maintained by DBAs.

For healthcare organizations the major regulatory worry is HIPAA, the Health Insurance Portability and Accountability Act. HIPAA mandates security measures for patients’ personal health information (PHI)—to the extent that an organization must be able to document every time a PHI data element was viewed. HIPAA audits often focus on the processes that drive exception logs and reports. Database auditing is critical in this regard.

Here are some typical Oracle DBA tasks that directly relate to compliance:

  • Data quality and metadata management. Ensuring data quality is key to regulatory compliance. If data or metadata aren’t accurate, how can the right data elements be subject to the appropriate regulatory controls?
  • Database auditing. As mentioned above, robust database audit capabilities can be essential for compliance with HIPAA and other regulations and policies that mandate tracking database usage. What data was accessed when and by whom? Database audit software can tell you. Database auditing is also vital for overall information security and detection of security breaches, especially against internal threats.
  • Data masking and obfuscation. Data masking practices are generally used to render original data suitable for testing purposes. It looks and functions consistently with the original data, but no longer constitutes personally identifiable information or credit card data, etc. for regulatory purposes. It’s also important for protecting sensitive data from staff (e.g., third-party contractors) working in non-production environments.
  • Database archiving and long-term data retention. Regulations often mandate what data must be stored for what period of time. This is also important for legal/eDiscovery purposes.
  • Database recovery. Database recovery is also a compliance issue, because it relates to database integrity and availability. If data is lost and can’t be recovered, that can be as problematic as a security breach from a regulatory perspective.

If you’re not sure whether your Oracle database policies, procedures and controls are adequate to support regulatory compliance, Buda Consulting can help. Contact us to discuss a database security assessment to identify areas of noncompliance and provide whatever assistance you need to address them.