视图杂记4-CSDN博客

When you specify WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates

To avoid the overhead of this validation activity, issue the following statement for each constraint before doing the exchange partition operation:
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name KEEP INDEX
Then, enable the constraints after the exchange.
If you specify WITHOUT VALIDATION, then you must ensure that the data to be exchanged belongs in the partition you exchange.

ALTER TABLE stocks
EXCHANGE PARTITION p3 WITH TABLE stock_table_3;

CREATE TABLE t1 (i NUMBER, j NUMBER)
     PARTITION BY HASH(i)
       (PARTITION p1, PARTITION p2);
CREATE TABLE t2 (i NUMBER, j NUMBER)
     PARTITION BY RANGE(j)
     SUBPARTITION BY HASH(i)
        (PARTITION p1 VALUES LESS THAN (10)
            SUBPARTITION t2_pls1
            SUBPARTITION t2_pls2,
         PARTITION p2 VALUES LESS THAN (20)
            SUBPARTITION t2_p2s1
            SUBPARTITION t2_p2s2));
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1
     WITH VALIDATION;

SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME
FROM DBA_TAB_SUBPARTITIONS
WHERE TABLE_NAME='QUARTERLY_REGIONAL_SALES'
ORDER BY PARTITION_NAME;

ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;

•USER_TABLES, ALL_TABLES, DBA_TABLES
•USER_PART_TABLES, ALL_PART_TABLES, DBA_PART_TABLES
•USER_OBJECT_TABLES, ALL_OBJECT_TABLES, DBA_OBJECT_TABLES

DBA_PART_TABLES
ALL_PART_TABLES
USER_PART_TABLES
DBA view displays partitioning information for all partitioned tables in the database. ALL view displays partitioning information for all partitioned tables accessible to the user. USER view is restricted to partitioning information for partitioned tables owned by the user.

DBA_TAB_PARTITIONS
ALL_TAB_PARTITIONS
USER_TAB_PARTITIONS
Display partition-level partitioning information, partition storage parameters, and partition statistics generated by the DBMS_STATS package or the ANALYZE statement.

DBA_TAB_SUBPARTITIONS
ALL_TAB_SUBPARTITIONS
USER_TAB_SUBPARTITIONS
Display subpartition-level partitioning information, subpartition storage parameters, and subpartition statistics generated by the DBMS_STATS package or the ANALYZE statement.

DBA_PART_KEY_COLUMNS
ALL_PART_KEY_COLUMNS
USER_PART_KEY_COLUMNS
Display the partitioning key columns for partitioned tables.

DBA_SUBPART_KEY_COLUMNS
ALL_SUBPART_KEY_COLUMNS
USER_SUBPART_KEY_COLUMNS
Display the subpartitioning key columns for composite-partitioned tables (and local indexes on composite-partitioned tables).

DBA_PART_COL_STATISTICS
ALL_PART_COL_STATISTICS
USER_PART_COL_STATISTICS
Display column statistics and histogram information for the partitions of tables.

DBA_SUBPART_COL_STATISTICS
ALL_SUBPART_COL_STATISTICS
USER_SUBPART_COL_STATISTICS
Display column statistics and histogram information for subpartitions of tables.

DBA_PART_HISTOGRAMS
ALL_PART_HISTOGRAMS
USER_PART_HISTOGRAMS
Display the histogram data (end-points for each histogram) for histograms on table partitions.

DBA_SUBPART_HISTOGRAMS
ALL_SUBPART_HISTOGRAMS
USER_SUBPART_HISTOGRAMS
Display the histogram data (end-points for each histogram) for histograms on table subpartitions.

DBA_PART_INDEXES
ALL_PART_INDEXES
USER_PART_INDEXES
Display partitioning information for partitioned indexes.

DBA_IND_PARTITIONS
ALL_IND_PARTITIONS
USER_IND_PARTITIONS
Display the following for index partitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the DBMS_STATS package or the ANALYZE statement.

DBA_IND_SUBPARTITIONS
ALL_IND_SUBPARTITIONS
USER_IND_SUBPARTITIONS
Display the following information for index subpartitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the DBMS_STATS package or the ANALYZE statement.

DBA_SUBPARTITION_TEMPLATES
ALL_SUBPARTITION_TEMPLATES
USER_SUBPARTITION_TEMPLATES
Display information about existing subpartition templates

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

转载于:http://blog.itpub.net/22308399/viewspace-750705/