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

This article is about the database partition table how to delete partitions, partition corresponding tablespace, datafile content. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

SQL of Truncate partition

ALTER TABLE table_name TRUNCATE PARTITION p1 DROP STORAGE UPDATE GLOBAL INDEXES

SQL of Drop partition

ALTER TABLE table_name DROP PARTITION p1 UPDATE GLOBAL INDEXES

In a practical case, you need to delete the partition 6 months ago, and delete the tablespace and data files corresponding to the partition.

ALTER TABLE ESB_MSG_LOG DROP PARTITION ESBLOG201607 UPDATE GLOBAL INDEXES

Select * from dba_segments where tablespace_name like 'ESBLOG201607%'; (no result indicates that tablespaces can be deleted)

Two methods of deleting table space and data file corresponding to partition

1. This method is relatively secure. Once the tablespace is not empty, drop will report an error. If drop is successful, rm it on OS.

Drop tablespace ESBLOG201607

Rm-f / u01/app/oracle/oradata/payroll/ESBLOG201607.dbf

2. If there is a DATAGUARD, it is recommended that the main library use this method.

Drop tablespace ESBLOG201607 INCLUDING CONTENTS and DATAFILES

INCLUDING CONTENTS

Specify INCLUDING CONTENTS to drop all the contents of the tablespace. You must specify this clause to drop a tablespace that contains any database objects. If you omit this clause, and the tablespace is not empty, then the database returns an error and does not drop the tablespace.

Specify INCLUDING CONTENTS to delete all contents of the tablespace. You must specify this clause to delete tablespaces that contain any database objects.

If this clause is omitted and the tablespace is not empty, the database returns an error and the tablespace is not deleted.

AND DATAFILES

When you specify INCLUDING CONTENTS, the AND DATAFILES clause lets you instruct the database to delete the associated operating system files as well. Oracle Database writes a message to the alert log for each operating system file deleted. This clause is not needed for Oracle Managed Files, because they are removed from the system even if you do not specify AND DATAFILES.

When INCLUDING CONTENTS is specified, the AND DATAFILES clause allows you to instruct the database to delete the associated operating system files. When each operating system file is deleted, the Oracle database writes a message to the alert log. OMF does not need this clause because the file is deleted from the system even if AND DATAFILES is not specified.

You can issue the SQL DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES statement on the primary database to delete the datafiles on both the primary and standby databases.

Truncate_partition_subpart

Specify TRUNCATE PARTITION to remove all rows from the partition identified by partition_extended_name or, if the table is composite partitioned, all rows from the subpartitions of that partition. Specify TRUNCATE SUBPARTITION to remove all rows from an individual subpartition. If table is index organized, then Oracle Database also truncates any corresponding mapping table partitions and overflow area partitions.

If the partition or subpartition to be truncated contains data, then you must first disable any referential integrity constraints on the table. Alternatively, you can delete the rows and then truncate the partition.

If table contains any LOB columns, then the LOB data and LOB index segments for this partition are also truncated. If table is composite partitioned, then the LOB data and LOB index segments for the subpartitions of the partition are truncated.

If table contains any equipartitioned nested tables, then you cannot truncate the parent partition unless its corresponding nested table partition is empty.

If a domain index is defined on table, then the index must not be marked IN_PROGRESS or FAILED, and the index partition corresponding to the table partition being truncated must not be marked IN_PROGRESS.

For each partition or subpartition truncated, Oracle Database also truncates corresponding local index partitions and subpartitions. If those index partitions or subpartitions are marked UNUSABLE, then the database truncates them and resets the UNUSABLE marker to VALID.

You can update global indexes on table during this operation using the update_global_index_clause or the update_all_indexes_clause. If you specify the parallel_clause with one of these clauses, then the database parallelizes the index update, not the truncate operation.

DROP STORAGE

Specify DROP STORAGE to deallocate all space from the deleted rows, except the space allocated by the MINEXTENTS parameter. This space can subsequently be used by other objects in the tablespace.

DROP ALL STORAGE

Specify DROP ALL STORAGE to deallocate all space from the deleted rows, including the space allocated by the MINEXTENTS parameter. All segments for the partition or subpartition, as well as all segments for its dependent objects, will be deallocated.

Specify TRUNCATE PARTITION to remove all rows from the partition identified by partition_extended_name, or all rows in that partition subpartition if the table is a composite partition. Specify TRUNCATE SUBPARTITION to delete all rows from a single subpartition. If the table is indexed, the Oracle database also truncates any corresponding mapped table partitions and overflow partitions.

If the partition or subpartition you want to truncate contains data, you must first disable any referential integrity constraints on the table. Alternatively, you can delete the row and then truncate the partition.

If the table contains any LOB columns, the LOB data and LOB index segments for this partition will also be truncated. If the table is compound partitioned, the LOB data and LOB index segments of the child partition of the partition will be truncated.

If the table contains any evenly divided nested tables, the parent partition cannot be truncated unless the corresponding nested table partition is empty.

If a domain index is defined on a table, the index cannot be marked as IN_PROGRESS or FAILED, and the index partition corresponding to the table partition is truncated and cannot be marked as IN_PROGRESS.

For each partition or subpartition truncated, the Oracle database also truncates the corresponding local index partition and subpartition. If these index partitions or subpartitions are marked as UNUSABLE, the database truncates them and resets the UNUSABLE tag to VALID.

You can use update_global_index_clause or update_all_indexes_clause to update the global index on the table during this operation. If you specify parallel_clause using one of these clauses, the database parallelizes index updates rather than truncation.

DROP storage

Specifies that DROP STORAGE frees all space from the deleted row, except for the space allocated by the MINEXTENTS parameter. This space can then be used by other objects in the tablespace.

DROP all Stora

Specify DROP ALL STORAGE to free all space from the deleted row, including the space allocated by the MINEXTENTS parameter. All segments of a partition or subpartition and all segments of its dependent objects are released.

Drop_table_partition

The drop_table_partition clause removes the partition identified by partition_extended_name, and the data in that partition, from a partitioned table. If you want to drop a partition but keep its data in the table, then you must merge the partition into one of the adjacent partitions.

If table has LOB columns, then Oracle Database also drops the LOB data and LOB index partitions and any subpartitions corresponding to partition.

If table has equipartitioned nested table columns, then Oracle Database also drops the nested table partitions corresponding to the table partition being dropped.

If table is index organized and has a mapping table defined on it, then the database drops the corresponding mapping table partition as well.

Oracle Database drops local index partitions and subpartitions corresponding to the dropped partition, even if they are marked UNUSABLE.

You can update indexes on table during this operation using the update_index_clauses. If you specify the parallel_clause with the update_index_clauses, then the database parallelizes the index update, not the drop operation.

If you drop a range partition and later insert a row that would have belonged to the dropped partition, then the database stores the row in the next higher partition. However, if that partition is the highest partition, then the insert will fail, because the range of values represented by the dropped partition is no longer valid for the table.

Restrictions on Dropping Table Partitions

Dropping table partitions is subject to the following restrictions:

You cannot drop a partition of a hash-partitioned table. Instead, use the coalesce_table_partition clause.

If table contains only one partition, then you cannot drop that partition. Instead, drop the table.

If you update global indexes using the update_index_clauses, then you can specify only the UPDATE INDEXES keywords but not the subclause.

The drop_table_ partition clause removes the partition identified by partition_extended_name and the data in that partition from the partition table. If you want to delete a partition but keep its data in the table, you must merge the partition into one of the adjacent partitions.

If the table has LOB columns, the Oracle database also discards the LOB data and LOB index partitions corresponding to the partition, as well as any subpartitions.

If the table has equally nested table columns, the Oracle database also deletes the nested table partition corresponding to the table partition to be deleted.

If the table is indexed and a mapping table is defined on it, the database also discards the corresponding mapping table partition.

Local index partitions and subpartitions corresponding to deleted partitions are deleted even if the Oracle database is marked as the UNUSABLE,Oracle database.

You can use update_index_clauses to update the index on the table during this operation. If you specify parallel_clause using update_index_clauses, the database parallelizes index updates instead of delete operations.

If you delete a range partition and then insert a row that belongs to the deleted partition, the database stores the row in the next higher partition. However, if the partition is the highest partition, the insert will fail because the range of values represented by the deleted partition is no longer valid for the table.

Delete restrictions on table partitions

Deleting table partitions is subject to the following restrictions:

You cannot delete a partition of a hash partition table. Instead, you use the coalesce_table_ partitioning clause.

If the table contains only one partition, you cannot delete that partition. Instead, delete the table.

If you use update_index_clauses to update the global index, you can specify only the UPDATE INDEXES keyword, but not the subterms.

Drop_table_subpartition

Use this clause to drop a range or list subpartition from a range, list, or hash composite-partitioned table. Oracle Database deletes any rows in the dropped subpartition.

Oracle Database drops the corresponding subpartition of any local index. Other index subpartitions are not affected. Any global indexes are marked UNUSABLE unless you specify the update_global_index_clause or update_all_indexes_clause.

Restrictions on Dropping Table Subpartitions

Dropping table subpartitions is subject to the following restrictions:

You cannot drop a hash subpartition. Instead use the MODIFY PARTITION... COALESCE SUBPARTITION syntax.

If a partition contains only one subpartition, then you cannot drop that subpartition. Instead, use the drop_table_partition clause.

If you update the global indexes, then you cannot specify the optional subclause of the update_all_indexes_clause.

Use this clause to remove a range or list subpartition from a range, list, or hash composite partition table. The Oracle database deletes any rows in discarded subpartitions.

The Oracle database deletes the corresponding subpartition of any local index. Other index subpartitions are not affected. Any global index is marked as UNUSABLE unless you specify update_global_index_clause or update_all_indexes_clause.

Delete restrictions on table subpartitions

Deleting table subpartitions is subject to the following restrictions:

You cannot delete a hash subpartition. Instead, use MODIFY PARTITION... COALESCE SUBPARTITION syntax.

If a partition contains only one subpartition, you cannot delete the subpartition. Instead, use the drop_table_ partitioning clause

If you update the global index, you cannot specify optional sub-clauses for update_all_indexes_clause.

Update_all_indexes_clause

Use this clause to update all indexes on table.

Update_global_index_clause

Use this clause to update only global indexes on table. Oracle Database marks UNUSABLE all local indexes on table.

UPDATE GLOBAL INDEXES

Specify UPDATE GLOBAL INDEXES to update the global indexes defined on table.

Restriction on Updating Global Indexes

If the global index is a global domain index defined on a LOB column, then Oracle Database marks the domain index UNUSABLE instead of updating it.

INVALIDATE GLOBAL INDEXES

Specify INVALIDATE GLOBAL INDEXES to invalidate the global indexes defined on table.

If you specify neither, then Oracle Database invalidates the global indexes.

Restrictions on Invalidating Global Indexes

This clause is supported only for global indexes. It is not supported for index-organized tables. In addition, this clause updates only indexes that are USABLE and VALID. UNUSABLE indexes are left unusable, and INVALID global indexes are ignored.

Use this clause to update only the global index on the table. The Oracle database marks all local indexes on the UNUSABLE table.

UPDATE GLOBAL INDEXES

Specify UPDATE GLOBAL INDEXES to update the global index defined on the table.

Limitations of UPDATE GLOBAL INDEXES

If the global index is a global domain index defined on the LOB column, the Oracle database marks the domain index UNUSABLE instead of updating it.

INVALIDATE GLOBAL INDEXES

Specify INVALIDATE GLOBAL INDEXES to invalidate the global index defined on the table.

If you specify neither INVALIDATE GLOBAL INDEXES nor UPDATE GLOBAL INDEXES, the Oracle database invalidates the global index.

Thank you for reading! On "how to delete the partition table in the database, partition corresponding tablespace, datafile," this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

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.