purge sql что это
Purge sql что это
Use the PURGE statement to:
Remove a table or index from your recycle bin and release all of the space associated with the object
Remove part or all of a dropped tablespace or tablespace set from the recycle bin
Remove the entire recycle bin
You cannot roll back a PURGE statement, nor can you recover an object after it is purged.
To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary view. You can use the RECYCLEBIN synonym instead. The following two statements return the same rows:
Oracle Database Administrator’s Guide for information on the recycle bin and naming conventions for objects in the recycle bin
FLASHBACK TABLE for information on retrieving dropped tables from the recycle bin
Oracle Database Reference for information on using the RECYCLEBIN initialization parameter to control whether dropped tables go into the recycle bin
To purge a table, the table must reside in your own schema or you must have the DROP ANY TABLE system privilege, or you must have the SYSDBA system privilege.
To purge an index, the index must reside in your own schema or you must have the DROP ANY INDEX system privilege, or you must have the SYSDBA system privilege.
To purge a tablespace or tablespace set, you must have the DROP TABLESPACE system privilege, or you must have the SYSDBA system privilege.
To purge a tablespace set, you must also be connected to a shard catalog database as an SDB user.
To perform the PURGE DBA_RECYCLEBIN operation, you must have the SYSDBA or PURGE DBA_RECYCLEBIN system privilege.
Specify the name of the table or index in the recycle bin that you want to purge. You can specify either the original user-specified name or the system-generated name Oracle Database assigned to the object when it was dropped.
If you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.
System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, then the database purges that specified object.
When the database purges a table, all table partitions, LOBs and LOB partitions, indexes, and other dependent objects of that table are also purged.
TABLESPACE or TABLESPACE SET
Use this clause to purge all the objects residing in the specified tablespace or tablespace set from the recycle bin.
Use this clause to reclaim space in a tablespace or tablespace set for a specified user. This operation is useful when a particular user is running low on disk quota for the specified tablespace or tablespace set.
Use this clause to purge the current user’s recycle bin. Oracle Database will remove all objects from the user’s recycle bin and release all space associated with objects in the recycle bin.
This clause is valid only if you have the SYSDBA or PURGE DBA_RECYCLEBIN system privilege. It lets you remove all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user. This operation is useful, for example, before backward migration.
Remove a File From Your Recycle Bin: Example
The following statement removes the table test from the recycle bin. If more than one version of test resides in the recycle bin, then Oracle Database removes the version that has been there the longest:
To determine system-generated name of the table you want removed from your recycle bin, issue a SELECT statement on your recycle bin. Using that object name, you can remove the table by issuing a statement similar to the following statement. (The system-generated name will differ from the one shown in the example.)
Remove the Contents of Your Recycle Bin: Example
To remove the entire contents of your recycle bin, issue the following statement:
DROP TABLE
Use the DROP TABLE statement to move a table or object table to the recycle bin or to remove the table and all its data from the database entirely.
Unless you specify the PURGE clause, the DROP TABLE statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user’s space quota.
For an external table, this statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.
When you drop a table that is part of a cluster, the table is moved to the recycle bin. However, if you subsequently drop the cluster, the table is purged from the recycle bin and can no longer be recovered with a FLASHBACK TABLE operation.
Dropping a table invalidates dependent objects and removes object privileges on the table. If you want to re-create the table, then you must regrant object privileges on the table, re-create the indexes, integrity constraints, and triggers for the table, and respecify its storage parameters. Truncating has none of these effects. Therefore, removing rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table.
CREATE TABLE and ALTER TABLE for information on creating and modifying tables
TRUNCATE and DELETE for information on removing data from a table
FLASHBACK TABLE for information on retrieving a dropped table from the recycle bin
The table must be in your own schema or you must have the DROP ANY TABLE system privilege.
Description of the illustration drop_table.gif
Specify the name of the table to be dropped. Oracle Database automatically performs the following operations:
All rows from the table are dropped.
All table indexes and domain indexes are dropped, as well as any triggers defined on the table, regardless of who created them or whose schema contains them. If table is partitioned, then any corresponding local index partitions are also dropped.
All the storage tables of nested tables and LOBs of table are dropped.
When you drop a range-, hash-, or list-partitioned table, then the database drops all the table partitions. If you drop a composite-partitioned table, then all the partitions and subpartitions are also dropped.
Oracle Database Administrator’s Guide for more information on dropping partitioned tables.
For an index-organized table, any mapping tables defined on the index-organized table are dropped.
For a domain index, the appropriate drop routines are invoked. Please refer to Oracle Data Cartridge Developer’s Guide for more information on these routines.
If any statistic types are associated with the table, then the database disassociates the statistics types with the FORCE clause and removes any user-defined statistics collected with the statistics type.
ASSOCIATE STATISTICS and DISASSOCIATE STATISTICS for more information on statistics type associations
If the table is not part of a cluster, then the database returns all data blocks allocated to the table and its indexes to the tablespaces containing the table and its indexes.
To drop a cluster and all its the tables, use the DROP CLUSTER statement with the INCLUDING TABLES clause to avoid dropping each table individually. See DROP CLUSTER.
If the table is a base table for a view, a container or master table of a materialized view, or if it is referenced in a stored procedure, function, or package, then the database invalidates these dependent objects but does not drop them. You cannot use these objects unless you re-create the table or drop and re-create the objects so that they no longer depend on the table.
If you choose to re-create the table, then it must contain all the columns selected by the subqueries originally used to define the materialized views and all the columns referenced in the stored procedures, functions, or packages. Any users previously granted object privileges on the views, stored procedures, functions, or packages need not be regranted these privileges.
If the table is a master table for a materialized view, then the materialized view can still be queried, but it cannot be refreshed unless the table is re-created so that it contains all the columns selected by the defining query of the materialized view.
If the table has a materialized view log, then the database drops this log and any other direct-path INSERT refresh information associated with the table.
Specify CASCADE CONSTRAINTS to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, then the database returns an error and does not drop the table.
You cannot roll back a DROP TABLE statement with the PURGE clause, nor can you recover the table if you have dropped it with the PURGE clause.
Using this clause is equivalent to first dropping the table and then purging it from the recycle bin. This clause lets you save one step in the process. It also provides enhanced security if you want to prevent sensitive material from appearing in the recycle bin.
Oracle Database Administrator’s Guide for information on the recycle bin and naming conventions for objects in the recycle bin
PURGE
Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.
You cannot roll back a PURGE statement, nor can you recover an object after it is purged.
To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary review. You can use the RECYCLEBIN synonym instead. The following two statements return the same rows:
Oracle Database Administrator’s Guide for information on the recycle bin and naming conventions for objects in the recycle bin
FLASHBACK TABLE for information on retrieving dropped tables from the recycle bin
Description of the illustration purge.gif
Specify the name of the table or index in the recycle bin that you want to purge. You can specify either the original user-specified name or the system-generated name Oracle Database assigned to the object when it was dropped.
If you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.
System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, then the database purges that specified object.
When the database purges a table, all table partitions, LOBs and LOB partitions, indexes, and other dependent objects of that table are also purged.
Use this clause to purge the current user’s recycle bin. Oracle Database will remove all objects from the user’s recycle bin and release all space associated with objects in the recycle bin.
This clause is valid only if you have SYSDBA system privilege. It lets you remove all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user. This operation is useful, for example, before backward migration.
Use this clause to purge all the objects residing in the specified tablespace from the recycle bin.
USER user Use this clause to reclaim space in a tablespace for a specified user. This operation is useful when a particular user is running low on disk quota for the specified tablespace.
Remove a File From Your Recycle Bin: Example The following statement removes the table test from the recycle bin. If more than one version of test resides in the recycle bin, Oracle Database removes the version that has been there the longest:
To determine system-generated name of the table you want removed from your recycle bin, issue a SELECT statement on your recycle bin. Using that object name, you can remove the table by issuing a statement similar to the following statement. (The system-generated name will differ from the one shown in the example.)
Remove the Contents of Your Recycle Bin: Example To remove the entire contents of your recycle bin, issue the following statement:
Разница Между Drop И Drop Purge В Oracle
Я использую базу данных Oracle, и я немного смущен командами Drop и Purge. На самом деле для меня оба делают одно и то же. Удаляет таблицу со схемой из базы данных. В чем основное различие между этими двумя понятиями?
6 ответов
обычно таблица перемещается в корзину (начиная с Oracle 10g), если она удалена. Однако, если модификатор очистки также указан, таблица неустранимо (полностью) удаляется из базы данных.
Oracle Database 10g представляет новую функцию для удаления таблиц. Когда вы отбросьте таблицу, база данных не сразу освобождает пространство, связанное с таблица. Скорее, база данных переименовывает таблицу и помещает ее в корзину, где она может позже будет восстановлен с помощью инструкции FLASHBACK TABLE, если вы обнаружите, что по ошибке уронил таблицу. Если вы хотите немедленно освободить пространство, связанное с таблицей в то время выполните инструкцию DROP TABLE, затем включите пункт о чистке выглядит следующим образом. Падение таблица сотрудников чистка; Укажите очистить, только если вы хотите удалить таблицу и освободить место ассоциируется с ним за один шаг. Если указать PURGE, то база данных не будет поместите таблицу и зависимые от нее объекты в корзину. Примечание: нельзя откатить инструкцию DROP TABLE с очисткой предложение, и вы не можете восстановить таблицу, если вы удалите ее с предложением PURGE. Этот функция не была доступна в предыдущих версиях.
в основном с oracle10, все упавшие таблицы попадают в корзину, откуда их можно снять. С purge вы в основном пропускаете часть корзины, и вы бросаете таблицу без возможности отменить действие.
эта команда удаляет таблицу с именем table_name, но внутренне она перемещается в корзину oracle (которая аналогична удалению любого файла / папки с помощью удалить ключ на ОС Windows).
преимущества:
1. При необходимости мы сможем восстановить вышеприведенную удаленную таблицу.
недостатки:
1. Все еще занимает некоторое количество памяти.
эта команда полностью удаляет имя таблицы table_name из база данных (которая похожа на удаление любого файла / папки с помощью Shift + удалить ключ на ОС windows).
преимущества и недостатки будут вице-стихом выше.
приведенная ниже инструкция отбросит таблицу и поместит ее в корзину.
падение таблицы emp_new;
приведенная ниже инструкция отбросит таблицу и также очистит ее от корзины.
падение таблицы emp_new очистки;
падение table имя_таблицы продувки;
используя этот запрос, что произошло:- Обычно таблица перемещается в корзину (начиная с Oracle 10g), если она удалена.Но все еще занимал память в базе данных, поэтому, чтобы освободить память для другого объекта, мы можем использовать предложение The Purge.
PURGE
Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.
You cannot roll back a PURGE statement, nor can you recover an object after it is purged.
To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary view. You can use the RECYCLEBIN synonym instead. The following two statements return the same rows:
Oracle Database Administrator’s Guide for information on the recycle bin and naming conventions for objects in the recycle bin
FLASHBACK TABLE for information on retrieving dropped tables from the recycle bin
Oracle Database Reference for information on using the RECYCLEBIN initialization parameter to control whether dropped tables go into the recycle bin
Description of the illustration »purge.gif»
Specify the name of the table or index in the recycle bin that you want to purge. You can specify either the original user-specified name or the system-generated name Oracle Database assigned to the object when it was dropped.
If you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.
System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, then the database purges that specified object.
When the database purges a table, all table partitions, LOBs and LOB partitions, indexes, and other dependent objects of that table are also purged.
Use this clause to purge the current user’s recycle bin. Oracle Database will remove all objects from the user’s recycle bin and release all space associated with objects in the recycle bin.
This clause is valid only if you have SYSDBA system privilege. It lets you remove all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user. This operation is useful, for example, before backward migration.
Use this clause to purge all the objects residing in the specified tablespace from the recycle bin.
USER user Use this clause to reclaim space in a tablespace for a specified user. This operation is useful when a particular user is running low on disk quota for the specified tablespace.
Remove a File From Your Recycle Bin: Example The following statement removes the table test from the recycle bin. If more than one version of test resides in the recycle bin, then Oracle Database removes the version that has been there the longest:
To determine system-generated name of the table you want removed from your recycle bin, issue a SELECT statement on your recycle bin. Using that object name, you can remove the table by issuing a statement similar to the following statement. (The system-generated name will differ from the one shown in the example.)
Remove the Contents of Your Recycle Bin: Example To remove the entire contents of your recycle bin, issue the following statement: