Shulou(Shulou.com)06/01 Report--

Oracle Recycle Bin recyclebin is a new feature of 10g. When we drop table cube_scope [purge], if purge is not specified, the system only renames the table to the name that begins with BIN$, and modifies the relevant data in the data dictionary. The physical space occupied by the table is not really recycled. At this time, the space occupied is still the original table space, when the table space is not enough. ORACLE will automatically clean up the space occupied by the objects in the Recycle Bin one by one according to DROPSCN#. 10g turns on the Recycle Bin function by default.

First, how to check whether the Recycle Bin function is enabled?

SQL > show parameter recyclebin

NAME TYPE VALUE

-

Recyclebin string on

On: indicates the recycle bin function enabled in the tablespace. It is recommended to enable this function for all data.

Note: this parameter can be set to session level or system level. It can take effect without restart.

Second, how to delete and release the occupied space directly without going through the Recycle Bin?

SQL > drop table cube_scope purge

Note: this command is equivalent to truncate+drop operation, which is generally not recommended!

3. How to restore the object in the Recycle Bin recyclebin?

SQL > flashback table cube_scope to before drop

The table name can be the dba_recyclebin.object_name of the Recycle Bin system or dba_recyclebin.original_name

But here comes the problem. I have rebuilt a new table with the backup DDL statement. At this time, using this command to restore will obviously report an error. What to do at this time? it can only be restored to an alias. The specific operation command is

SQL > flashback table cube_scope to before drop rename to cube_scope_old

Now that the data in the table before deletion has been restored, it can only be inserted into cube_scope from the data in cube_scope_old.

SQL > insert into cube_scope select * from cube_scope_old t

Successfully recovered the data, can we call it a day? No, what else did you forget to do? Think about it?

Note: if you drop the table drop, then the index is also dropped by drop, and you get the table back in this way, but what about your index? Where are your restrictions? Be sure to rebuild the index on the table after the table is restored (remember). The loss of the index will affect performance at most, and the loss of constraints may cause confusion of business data (be sure to pay attention).

4. How to manually clear the objects in the Recycle Bin?

SQL > purge table orabpel.cube_scope_old-- clear specific objects

Note: if the DBA user is manipulating other user data, add the user name when clearing the table in the Recycle Bin, otherwise the report will not be in the Recycle Bin

SQL > purge tablespace ORAPEL-- clears the specified tablespace object

SQL > purge tablespace ORAPEL user orabpel-- deletes all objects under the specified user in the tablespace

SQL > purge recyclebin-- empty the entire Recycle Bin

5. Why does show recyclebin have no data?

First of all, we need to understand that recyclebin is synonymous with user_recyclebin, so your current login user is system.

Show recyclebin has no data.

6. How to identify the same object in recyclebin if it is deleted multiple times?

Each deleted object in dba_recyclebin will be named with BIN$, and corresponding dropscn, createtime, and droptime can be located according to these objects, and then restored.

VII. Principle of ORACLE space utilization

1. Use the unused space of an existing tablespace

two。 If there is no free space, check the Recycle Bin. For the objects in the Recycle Bin, according to the principle of first-in, first-out, for the objects that are deleted first.

Oracle is the first to be deleted from the Recycle Bin when space is insufficient to meet the needs of newly allocated space.

3. If there are no objects to clean up in the Recycle Bin, check if the tablespace is self-expanding, if so, expand the tablespace, and then assign a new empty

Between

4. If the tablespace is not self-expanding, or can no longer self-expand (reach the maximum limit), the direct report space is insufficient and the program terminates

8. Will all the objects dropped by DROP pass through the Recycle Bin?

The following drop do not put related objects into the Recycle Bin recyclebin

* drop tablespace: clears all objects in the recyclebin that belong to the tablespace

* drop user: clears all objects in the recyclebin that belong to the user

* drop cluster: clears all member objects in the recyclebin that belong to the cluster

* drop type: clears all dependent type objects in the recyclebin

Also need to pay attention to a situation, the object in the tablespace should have enough space, otherwise even if the drop dropped through the recyclebin due to insufficient space oracle will be deleted automatically (remember)!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.