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

1. Truncate and delete only delete data and do not delete the structure (definition) of the table, while the drop statement will delete the constraints on which the structure of the table is dependent (constrain), trigger (trigger), and index (index); stored procedures / functions that depend on the table will be retained, but become invalid state.

2. The delete statement is dml, and this operation will not take effect until the transaction is committed. If there is a corresponding trigger, it will be triggered when it is executed.

3. Truncate,drop is ddl, and the operation takes effect immediately. The original data is not put into rollback segment, cannot be rolled back, and the operation does not trigger trigger.

4. The delete statement does not affect the extent occupied by the table, the high waterline (high watermark) remains unchanged, and the truncate statement releases the data space by default, unless the use of reuse storage; truncate will reset the high waterline (back to the beginning), and the drop statement will release all the space occupied by the table.

5. Sentence execution speed, generally speaking: drop > truncate > delete

6. Be careful with drop and truncate, especially when there is no backup. If you want to delete some data rows with delete, please take the where clause with you.

7. If you want to keep the table and delete all the data, if it has nothing to do with the transaction, you can use truncate, if it is related to the transaction, or you want to trigger trigger, or use delete.

8. If you are defragmenting the table, you can use truncate to keep up with reuse stroage, and then import / insert the data again.

9. For tables referenced by FOREIGN KEY constraints, you cannot use TRUNCATE TABLE, but instead use the DELETE statement that does not have a WHERE clause.

In addition, can drop,delete,truncate be restored if there is no backup?

From an intuitive sense, it seems that there is no content in this problem, since it is already drop, or truncate, it is a ddl statement, the data should not come back, if it is delete, and do commit, the data should not come back.

For this question, at least from the perspective of Oracle, the answer is yes, and in some cases it is possible. Let's make an analysis:

The first is the drop operation. In Oracle, there is a Recycle Bin function by default, that is, after a table is drop, the table is not physically deleted, but simply changed to a very long name.

If the Recycle Bin is open (default)

SQL > show parameter recyclebin

NAME TYPE VALUE

Recyclebin string on

So whether the drop table can be restored at this time, or not, why, there are several main reasons to consider:

For example, if the tablespace where this table is located is short of resources, it is very likely that the data of this table in the Recycle Bin will be recycled and other data will be stored.

In other words, a user's quota is already insufficient, and it is likely that the use of recycle bin resources will be restricted at this time.

Or in some scenarios, the table you create is placed directly under the system tablespace, and even if there is enough space, it cannot be recovered directly.

So through these analyses, there are still many possibilities for drop operations, but the initial statement is possible rather than affirmative.

Let's take a look at the truncate operation, which is powerless, even from a data recovery point of view. But our answer is still possible, and it's time to talk about the flashback database feature.

For example, at some point in time, truncate has a very important table. We can try flashback database if circumstances permit.

Of course, you still have a system-level setting for this feature. By default, the flashback database feature is not enabled.

SQL > select flashback_on from v$database

FLASHBACK_ON

-

NO

You need to use alter database flashback on in the database mount phase to enable it, because the flashback log records some data changes at the database level, so it is bound to have a certain impact on database performance and resources. However, in the test environment, the informal environment can be used boldly.

For example, we are not sure about the time of truncate, we can keep flashback until we find a satisfactory result unknown.

If at some point we find that the data is what we expect, we can use exp to export the data, and then import the data when needed.

Finally, let's talk about delete operation, which from the Oracle level, there are more options.

The delete operation will use the resources of undo to generate the corresponding undo_sql. If we do commit after delete the data, we can consider the feature of flashback table:

Flashback table test to timestamp to_timestamp ('2015-08-01, 12-0-02, 27-8, 27, 5, 5, 5, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 12, 12, 12, 12, 12, 12, 12, 14, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12

We can also look up all the data at that point in time:

Select * from test as of timestamp to_timestamp ('2015-08-01 VLV 14-09RV 37-MMMMUDUD-hh34-MMMMUDUD-hh34-MMMUDVIDD-MMMMUDVIDD

Then create a table and import the queried data into it so that the data is restored.

The following are the study materials of oracle, which I hope will help you.

Http://www.wyzc.com/index.php?a=course&m=Index&c=channel&channelId=3&tg=3006123630

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.