數據庫中分區表如何刪除分區、分區對應的tablespace、datafile

這篇文章給大家分享的是有關數據庫中分區表如何刪除分區、分區對應的tablespace、datafile的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

Truncate分區的SQL
ALTER TABLE table_name TRUNCATE PARTITION p1 DROP STORAGE UPDATE GLOBAL INDEXES;
Drop分區的SQL
ALTER TABLE table_name DROP PARTITION p1 UPDATE GLOBAL INDEXES;


實戰的一個案例，需要刪除6個月以前的分區，并刪除分區對應的表空間和數據文件
ALTER TABLE ESB_MSG_LOG DROP PARTITION ESBLOG201607 UPDATE GLOBAL INDEXES;
select * from dba_segments where tablespace_name like 'ESBLOG201607%';(沒結果說明表空間可以刪除)
刪除分區對應的表空間和數據文件的兩種方法
1、這種方式比較安全，一旦表空間非空，drop就會報錯，drop成功后再在OS上rm
Drop tablespace ESBLOG201607;
rm -f /u01/app/oracle/oradata/payroll/ESBLOG201607.dbf
2、如果有DATAGUARD，建議主庫使用這種方式
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.
指定INCLUDING CONTENTS以刪除表空間的所有內容。 您必須指定此子句來刪除包含任何數據庫對象的表空間。
如果省略此子句，并且表空間不為空，則數據庫返回錯誤，并且不會刪除表空間。

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.
當指定INCLUDING CONTENTS時，AND DATAFILES子句允許您指示數據庫刪除關聯的操作系統文件。 每個操作系統文件被刪除時，Oracle數據庫將向警報日志寫入一條消息。 OMF不需要此子句，因為即使沒有指定AND DATAFILES也會從系統中刪除該文件。

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.

指定TRUNCATE PARTITION以從partition_extended_name標識的分區中刪除所有行，如果表是復合分區，則該分區子分區中的所有行。 指定TRUNCATE SUBPARTITION以從單個子分區中刪除所有行。 如果表是索引組織的，則Oracle數據庫還會截斷任何相應的映射表分區和溢出區分區。

如果要截斷的分區或子分區包含數據，則必須首先禁用表上的任何引用完整性約束。 或者，您可以刪除行，然后截斷分區。

如果表包含任何LOB列，則此分區的LOB數據和LOB索引段也將被截斷。 如果表是復合分區的，則分區的子分區的LOB數據和LOB索引段將被截斷。

如果表包含任何均分的嵌套表，那么除非相應的嵌套表分區為空，否則不能截斷父分區。

如果域索引在表上定義，則索引不能被標記為IN_PROGRESS或FAILED，并且與表分區相對應的索引分區被截斷不能被標記為IN_PROGRESS。

對于截斷的每個分區或子分區，Oracle數據庫還會截斷相應的local索引分區和子分區。 如果這些索引分區或子分區標記為UNUSABLE，則數據庫將截斷它們，并將UNUSABLE標記重置為VALID。

您可以使用update_global_index_clause或update_all_indexes_clause在此操作期間更新表上的全局索引。 如果您使用這些子句之一指定parallel_clause，則數據庫會并行化索引更新，而不是截斷操作。

DROP存儲
指定DROP STORAGE可以從刪除的行中釋放所有空間，除了由MINEXTENTS參數分配的空間。 該空間隨后可以被表空間中的其他對象使用。

DROP所有存儲
指定DROP ALL STORAGE以從已刪除的行中釋放所有空間，包括由MINEXTENTS參數分配的空間。 分區或子分區的所有段以及其依賴對象的所有段將被釋放。



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.

drop_table_partition子句從分區表中刪除由partition_extended_name標識的分區以及該分區中的數據。 如果要刪除分區但將其數據保留在表中，則必須將分區合并到相鄰分區之一中。

如果表具有LOB列，則Oracle數據庫還會丟棄與分區對應的LOB數據和LOB索引分區以及任何子分區。

如果表具有等分嵌套表列，則Oracle數據庫還會刪除與要刪除的表分區相對應的嵌套表分區。

如果表是索引組織的，并在其上定義了一個映射表，那么數據庫也會丟棄相應的映射表分區。

即使Oracle數據庫標記為UNUSABLE，Oracle數據庫也會刪除對應于已刪除分區的本地索引分區和子分區。

您可以使用update_index_clauses在此操作期間更新表上的索引。 如果使用update_index_clauses指定parallel_clause，則數據庫會并行化索引更新，而不是刪除操作。

如果刪除范圍分區，然后插入屬于刪除分區的行，則數據庫將該行存儲在下一個較高分區中。 但是，如果該分區是最高分區，則插入將失敗，因為由刪除的分區表示的值的范圍對于表不再有效。

刪除表分區的限制
刪除表分區受以下限制：
您不能刪除散列分區表的分區。 而是使用coalesce_table_partition子句。
如果表僅包含一個分區，則不能刪除該分區。 而是刪除表。
如果您使用update_index_clauses更新全局索引，則可以僅指定UPDATE INDEXES關鍵字，但不能指定子條款。



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.

使用此子句從范圍，列表或哈希復合分區表中刪除范圍或列表子分區。 Oracle數據庫刪除丟棄的子分區中的任何行。

Oracle數據庫刪除任何local索引的相應子分區。 其他索引子分區不受影響。 任何全局索引都標記為UNUSABLE，除非您指定update_global_index_clause或update_all_indexes_clause。

刪除表子分區的限制
刪除表子分區受以下限制：
您不能刪除哈希子分區。 而是使用MODIFY PARTITION ... COALESCE SUBPARTITION語法。
如果一個分區只包含一個子分區，則不能刪除該子分區。 而是使用drop_table_partition子句
如果更新全局索引，則不能指定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.

使用此子句僅更新表上的全局索引。 Oracle數據庫標記UNUSABLE表上的所有本地索引。

UPDATE GLOBAL INDEXES
指定UPDATE GLOBAL INDEXES來更新表上定義的全局索引。

UPDATE GLOBAL INDEXES 的限制
如果全局索引是在LOB列上定義的全局域索引，則Oracle數據庫會標記域索引UNUSABLE而不是更新它。

INVALIDATE GLOBAL INDEXES
指定INVALIDATE GLOBAL INDEXES以使表上定義的全局索引無效。

如果既不指定INVALIDATE GLOBAL INDEXES也不指定UPDATE GLOBAL INDEXES ，則Oracle數據庫會使全局索引失效。

感謝各位的閱讀！關于“數據庫中分區表如何刪除分區、分區對應的tablespace、datafile”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！