分区表删除分区、分区对应的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数据库会使全局索引失效。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2144543/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2144543/

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
要创建分区表,需要执行以下步骤: 1. 创建分区表所需的表空间 在Oracle中,分区表需要至少一个表空间来存储数据。如果您已经有一个表空间,请跳过此步骤。如果没有,请使用以下命令创建一个表空间: CREATE TABLESPACE tablespace_name DATAFILE 'file_name.dbf' SIZE size_in_MB AUTOEXTEND ON; 其中,tablespace_name是您要创建的表空间的名称,file_name.dbf是要创建的数据文件的名称,size_in_MB是数据文件的大小(以MB为单位)。 2. 创建分区表 在使用CREATE TABLE命令创建表时,需要指定分区策略。以下是一个示例: CREATE TABLE partitioned_table ( column1 datatype1, column2 datatype2, ... columnN datatypeN ) PARTITION BY RANGE (column1) ( PARTITION partition_name1 VALUES LESS THAN (value1), PARTITION partition_name2 VALUES LESS THAN (value2), ... PARTITION partition_nameN VALUES LESS THAN (valueN) ) TABLESPACE tablespace_name; 在上面的命令中,partitioned_table是要创建的表的名称,column1是用于分区的列的名称和数据类型,partition_name和value是每个分区的名称和分区值。例如,如果您要根据日期对表进行分区,则可以使用以下命令: CREATE TABLE sales ( sale_date DATE, product_name VARCHAR2(50), sale_amount NUMBER(10,2) ) PARTITION BY RANGE (sale_date) ( PARTITION sales_jan VALUES LESS THAN (TO_DATE('01-FEB-2020', 'DD-MON-YYYY')) TABLESPACE sales_jan, PARTITION sales_feb VALUES LESS THAN (TO_DATE('01-MAR-2020', 'DD-MON-YYYY')) TABLESPACE sales_feb, PARTITION sales_mar VALUES LESS THAN (TO_DATE('01-APR-2020', 'DD-MON-YYYY')) TABLESPACE sales_mar, PARTITION sales_apr VALUES LESS THAN (MAXVALUE) TABLESPACE sales_apr ); 在上面的命令中,我们根据销售日期对表进行了分区,每个分区都使用了不同的表空间。 3. 创建分区索引 在创建分区表后,您可能还需要创建分区索引来提高查询性能。以下是一个示例: CREATE INDEX sales_date_idx ON sales (sale_date) LOCAL ( PARTITION sales_jan, PARTITION sales_feb, PARTITION sales_mar, PARTITION sales_apr ); 在上面的命令中,我们创建了一个本地分区索引,该索引仅覆盖指定的分区。 完成上述步骤后,您已经成功地创建了一个分区表

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值