Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to delete tables and Oracle Recycle Bin". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. The Drop Table statement of Oracle

First, let's look at the grammatical format of Oracle Drop Table.

Explain the parameters inside:

1schema

Schema represents the scheme name, which can be understood as the user name, and defaults to the table under the current user. For example, to delete the emp table under the scott user, drop table scott.emp

1purge

Purge is optional. If the Recycle Bin function is enabled (after oracle 10g, the Recycle Bin is enabled by default) without the purge option, it means that deleted tables are put into the Recycle Bin and the space is not recycled. The table can be flashed back using flashbask. With the purge option, it means to delete the table and free up space.

1cascade constraints

Optionally, if there are other tables associated with the primary key column or unique key column of the table to be deleted (the table to be deleted is the master table (parent table), and the slave table (child table) has a foreign key associated to this table), then an error will be reported directly with drop table. At this time, if you want to force the deletion of this table, you need to add the cascade constraints option.

2. Flash deletion: Flashback Drop

Using the flashback delete feature, you can undo the impact of DROP TABLE statements without resorting to traditional point-in-time recovery. This is implemented by the Recycle Bin, which can be queried through the DBA_RECYCLEBIN view.

Check whether the Recycle Bin function of the current database is enabled

12conn / as sysdbaselect value from v$parameter where name='recyclebin'

Execute drop table statement

123456789101112131415161718192021222324252728293031 log in to SQL > conn scott/tigerConnected using the scott user. View the table under the current user SQL > select * from tab; TNAME TABTYPE CLUSTERID- BONUS TABLEDEPT TABLEEMP TABLEEMP1 TABLESALGRADE TABLE deletes a table SQL > drop table emp1; Table dropped. View the table SQL > select * from tab; TNAME TABTYPE CLUSTERID--BIN$opkhRg3T0MPgVaUg TABLEq/kbkw==$0BONUS TABLEDEPT TABLEEMP TABLESALGRADE TABLE under the current user

Query the data of the table through the object name in the Recycle Bin

12345 the following method is wrong: select * from BIN$opkhRg3T0MPgVaUgq/kbkw==$0; requires double quotation marks: select * from "BIN$opkhRg3T0MPgVaUgq/kbkw==$0"

View the Recycle Bin

1show recyclebin

Grammatical format of flashback table

During a flashback table operation, the database acquires all report independent DML locks specified in the flashback list. These locks prevent any operation on the table when it is being restored to the previous state. Flashback table operations are performed in a single thing, regardless of the number of tables specified in the flashback table. Either all the tables have returned to their early state, or none of them have been restored. If the flashback table operation for any table fails, the entire statement fails. When the flashback operation is complete, the data in the table is the same as the point in time before the table. However, reference constraints cannot be restored without FLASHBACK TABLE to SCN or timestamps without saving rowid,FLASHBACK TABLE TO BEFORE DROP. The database cannot recover statistics for the earlier structure associated with the table. The current index of the table is restored and reflects the state of the restore point table. If the index does not exist at the restore point, the data is updated in the index to reflect the status of the restore point table. However, if the index is deleted between the current time and the restore point, it cannot be restored.

Perform flashback deletion

1234flashback table emp1 to before drop; can also perform a flashback through the name in the Recycle Bin: flashback table "BIN$opkhRg3T0MPgVaUgq/kbkw==$0" to before drop

Empty the Recycle Bin

This is the end of 1purge recyclebin; 's "how to delete tables and Oracle Recycle Bin". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.