Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle 11g maintenance partitions - Adding Partitions     - Oracle 12c detailing the new features (Database)

- Oracle EBS R12 for Linux installation (Database)

- Install Kali Linux via PXE network (Linux)

- Ubuntu Linux use MAC binding against ARP attacks (Linux)

- Linux Log Clear (Linux)

- Python configuration tortuous road of third-party libraries Numpy and matplotlib (Programming)

- CentOS install Redis (Database)

- CoreOS Linux introduces Kubernetes kubelet (Server)

- Use NTFS-3G to mount NTFS partitions under Linux U disk and removable hard disk (Linux)

- open V switch port mirror in OpenStack neutron (Server)

- 32-bit and 64-bit integer in PHP and MySQL range (Database)

- PCM audio under Linux (Linux)

- Gentoo: !!! existing preserved libs problem (Linux)

- Android screen rotation processing and ProgressDialog the best AsyncTask (Programming)

- Based Corosync + Pacemaker + DRBD + LNMP Web server to achieve high availability cluster (Server)

- Linux kernel log --dmesg (Linux)

- When a software RAID data recovery and reconstruction of how failure (Linux)

- Spring classic face questions Share (Programming)

- Oracle user password problem (Database)

- Java transient keyword (Programming)

 
         
  Oracle 11g maintenance partitions - Adding Partitions
     
  Add Date : 2018-11-21      
         
         
         
  Adding Partitions

This section describes how to manually add a new partition to the partition table and explains why partitions can not be added to most of partitions specified index.

Add a partition to the range partitioned table

Use ALTER TABLE ... ADD PARTITION statement to add a new partition to the "high" end (the last partition table 'HighValue'). To add a partition at the beginning or in the middle of the table, use the SPLIT PARTITION clause.

For example, consider such a table, sales, in addition to containing the current month's data, but also before the 12 month data. January 1, 1999, you add a partition month, TSX stored in the table space.

ALTER TABLE sales

      ADD PARTITION jan99 VALUES LESS THAN ('01 -FEB-1999 ')

      TABLESPACE tsx;

In addition, the scope of the partition table and the relevant local and global indexes are still available.

Adding a partition to hash partitioned table

When you add a partition to a hash-partitioned table, the database will have an existing partition (selected by the database) All data lines in accordance with the hash function rearranges the data to populate the new partition. Therefore, if the table is not empty, then add a hash partitions can be time consuming.

The following statement shows the addition of a hash-partitioned table to scubagear two ways. Select the first statement to add a hash, the partition name is automatically generated by the system and stored in the default partition table space. The second statement is also adding a partition, however, explicitly specify the partition name p_named and table space name gear5.

ALTER TABLE scubagear ADD PARTITION;

ALTER TABLE scubagear

      ADD PARTITION p_named TABLESPACE gear5;

Index may be marked as UNUSABLE, the following table:

Table Type

Index Operations

Ordinary table (heap tables)

Unless you specify UPDATE INDEXES clause in the ALTER TABLE statement:

For new partitions and redistribute existing partitions of local indexes, it is identified as UNUSABLE, to be rebuilt.

All global indexes, or all partitions of partitioned global indexes, are marked as UNUSABLE, to be rebuilt.

Index Organized Tables

For a local index, and treatment as ordinary table.

All global indexes remain available;

Add a partition to the list partition tables

The following statement shows how to add a new partition to the list partitioned table, in the example, specify the new partition and storage properties NOLOGGING attributes.

ALTER TABLE q1_sales_by_region

  ADD PARTITION q1_nonmainland VALUES ( 'HI', 'PR')

      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3

      NOLOGGING;

All values set description new partitions within the other can not exist in the partition table.

If the partition table contains a list of default partition, then you can not give it to add a new partition, but you can split the default partition. By doing so, you not only effectively creating a new partition you specify a value, and the second partition reserved for the default partition.

And partition table listing the relevant local and global indexes remain available.

Adding a partition to a partitioned table space

You can not add a partition to the partition table space explicitly, unless you lock the district of Xi'an, which will trigger the partition is created. The interval when the data is inserted, the database will automatically create an interval partition. Under normal circumstances, only the partition exchange load scenario, you need to explicitly create interval partitions.

To change the future interval partitions, we can use the SET INTERVAL clause in ALTER TABLE. This clause will change more than the current highest boundary of all materialized interval partitions interval.

You can also use the SET INTERVAL clause to migrate an existing range partitioned table or partition table to meet the range of interval-partitioned table or a composite interval partition table. If you want to prevent the creation of a new partition, effectively revert back to a range partitioned table, specify a null value to SET INTERVAL clause. Interval partitions that have been created will be converted to range partitioning, HighValue corresponding to the current maximum.

If you want to increase the interval range of dates, you must ensure that your new partition in the corresponding border. For example, the maximum interval partition boundaries of your daily interval partitioned table transactions is January 30, 2007, you want to modify the interval of one month, then the following statement will cause an error:

ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL (1, 'MONTH');

ORA-14767: Can not specify this interval with existing high bounds

You must create another to February 1, 2007 for the daily interval partition boundaries, so as to successfully modify the interval.

LOCK TABLE transactions PARTITION FOR (TO_DATE ('31 -JAN-2007 ',' dd-MON-yyyy ') IN SHARE MODE;

ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL (1, 'MONTH');

Interval partition table of the first partition is actually a range partition, you can split a range partition, add more partitions in the partition table space.

If you want to disable transactions table space partitions, use the following statement:

ALTER TABLE transactions SET INTERVAL ();

Add a partition to hash composite partitioned table

You can partition and sub-partition hash partitions to add two levels.

Adding a Partition

To a [range | list | interval] - hash composite partitioned table to add a partition as described above. For an interval - hash partitioned table, interval partitions are created automatically. You can specify the number of SUBPARTITIONS clause to specify the sub-district, or specify SUBPARTITION specific clause to name the child partitions. If no SUBPARTITIONS SUBPARTITION clause or clauses, then the partition will inherit the default attributes table level. For an interval - hash partitioned table, you can only add to the range or sub-district has materialized interval partitions.

This example to a range - range partitioning q1_2000 added a hash-partitioned table sales, first quarter of 2000 of the partition to store data. Among them, there are eight sub-partition table stored in tbs5 space. Child partitions can not explicitly use table compression. In this case, the child inherits the partition partition-level compression and attributes stored in compressed form.

ALTER TABLE sales ADD PARTITION q1_2000

      VALUES LESS THAN (2000, 04, 01) COMPRESS

      SUBPARTITIONS 8 STORE IN tbs5;

Adding a child partition

You can use the ALTER TABLE MODIFY PARTITION ... ADD SUBPARTITION statement clause to a [range | interval | list] - hash composite partitioned table to add a hash subpartition. The database will be based on a hash function for a partition of the same sub-partition has been re-hash all of the data rows are arranged, and in which part of the data to populate the new child partitions. For an interval - hash partitioned table, you can partition the range or has materialized interval partition add child partitions.

In the following example, a new hash is stored in the sub-partition us_loc5 us1 table space is added to the partition table locations_us range diving in.

ALTER TABLE diving MODIFY PARTITION locations_us

      ADD SUBPARTITION us_locs5 TABLESPACE us1;

Unless you specify UPDATE INDEXES clause, otherwise, must be rebuilt, and new sub-partitions and re-arrange the hash sub-partitions associated index sub-partitions.

Add a partition to the list composite partitioned table

You can partition and sub-partition the list are two levels to add partitions.

Adding a Partition

To a [range | list | interval] - list composite partitioned table to add a partition as described above. For an interval - hash partitioned table, interval partitions are created automatically. You can SUBPARTITION clause to name the child partitions and provides a list of values. If no SUBPARTITION clause, sub-sub-partition partition will inherit the template. If no subpartition template, then the default will create a separate sub-partitions.

As the example shown, the scope 4-28-- list partitioned table quarterly_regional_sales add a new partition and specify some new physical properties for the other property is not specified the default inherit the property sheet.

Example 4-28 to range - add partition list partition tables

ALTER TABLE quarterly_regional_sales

  ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE ( '1-APR-2000', 'DD-MON-YYYY'))

      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING

        (

          SUBPARTITION q1_2000_northwest VALUES ( 'OR', 'WA'),

          SUBPARTITION q1_2000_southwest VALUES ( 'AZ', 'UT', 'NM'),

          SUBPARTITION q1_2000_northeast VALUES ( 'NY', 'VM', 'NJ'),

          SUBPARTITION q1_2000_southeast VALUES ( 'FL', 'GA'),

          SUBPARTITION q1_2000_northcentral VALUES ( 'SD', 'WI'),

          SUBPARTITION q1_2000_southcentral VALUES ( 'OK', 'TX')

        );

Adding a child partition

You can use the ALTER TABLE statement MODIFY PARTITION ... ADD SUBPARTITION clause to a [range | list | interval] - list composite partitioned table to add a list of sub-partition. For an interval - list the partition table, you can partition the range or has materialized interval partition add child partitions.

In the following example, the range - a collection of sub-district list partitioned table quarterly_regional_sales of a new sub-district, sub-district are stored in the new table space ts2.

ALTER TABLE quarterly_regional_sales

  MODIFY PARTITION q1_1999

      ADD SUBPARTITION q1_1999_south

        VALUES ( 'AR', 'MS', 'AL') tablespace ts2;

Add a partition to a range of composite partitioned table

You can partition and sub-partition the list are two levels to add partitions.

Adding a Partition

To a [range | list | interval] - range composite partitioned table to add a partition as described above. For an interval - hash partitioned table, interval partitions are created automatically. You can SUBPARTITION clause to name the child partitions and provides a list of values. If no SUBPARTITION clause, sub-partition partition will stepson template. If there is no sub-partition template, it will create a separate sub-district, a maximum value of MAXVALUE.

The range - range partitioned table shipments to add a new range partition p_2007_jan, the new partition will be used to store data in January 2007. In this example, the new partition contains three sub-district, sub-partition partition will inherit the attributes and levels of compression stored in compressed form.

Example 4-29 to range - range partitioning add a partition table

ALTER TABLE shipments

  ADD PARTITION p_2007_jan

      VALUES LESS THAN (TO_DATE ('01 -FEB-2007 ',' dd-MON-yyyy ')) COMPRESS

      (SUBPARTITION p07_jan_e VALUES LESS THAN (TO_DATE ('15 -FEB-2007 ',' dd-MON-yyyy '))

      , SUBPARTITION p07_jan_a VALUES LESS THAN (TO_DATE ('01 -MAR-2007 ',' dd-MON-yyyy '))

      , SUBPARTITION p07_jan_l VALUES LESS THAN (TO_DATE ('01 -APR-2007 ',' dd-MON-yyyy '))

      );

Adding a child partition

You can use the ALTER TABLE statement MODIFY PARTITION ... ADD SUBPARTITION clause to a [range | list | interval] - range composite partitioned table to add a range of sub-partition. For an interval - range partitioned table, you can partition the range or has materialized interval partition add child partitions.

The following example, add a new range partition table to shipments, including all order_date field values in January 2007 and delivery_date field value is greater than or equal data April 1, 2007 of.

ALTER TABLE shipments

  MODIFY PARTITION p_2007_jan

      ADD SUBPARTITION p07_jan_vl VALUES LESS THAN (MAXVALUE);

Add a reference to the partition or sub-partition partition

Like and child partitions can be added to the range partitioned table, hash-partitioned table, partition table or list in line with the partition table, we can add a partition or sub-partition table in the definition of a reference to the parent partition. Add operations automatically cascade to all references to the partition table. When you add a partition or sub-partition to the main table, you can DEPENDENT TABLES clause to set the properties for the relevant table.

Adding index partition

You can not explicitly add a partition to a local index, on the contrary, only when you add a partition to the base table, a new partition will be added to the local index. Specifically, when you issue the ALTER TABLE statement to add a partition to a table containing the local index, a matching partition will also add to the local index. The new database will specify the name of the index partition and default physical storage attributes, however, after the ADD PARTITION operation is completed you can re-name it.

You can ADD PARTITION operation, to specify a table space for the new index partition by first modifying the default attribute index. For example, imagine there is a local index q1_sales_by_region_locix, belonging to the list of partition table q1_sales_by_region. If, before the new zoning q1_nonmainland, as described in "Adding a partition to list the partition table" as shown, you can execute the following statement, then the associated index partition will be created in tbs_4 table space.

ALTER INDEX q1_sales_by_region_locix

  MODIFY DEFAULT ATTRIBUTES TABLESPACE tbs_4;
 

Otherwise, after adding a partition, you must use the following statement to the index partition to tbs_4 table space.

ALTER INDEX q1_sales_by_region_locix

  REBUILD PARTITION q1_nonmainland TABLESPACE tbs_4;

You can use the ALTER INDEX ... ADD PARTITION statement to add a partition to the global hash-partitioned indexes. Database will add a new hash partition, and all entries of the index sub-partition of an existing re-hash sorted by hash function, and some of these data into the new partition index. The following sentence is added to the index partition index p5 hgidx.

ALTER INDEX hgidx ADD PARTITION p5;
 

You can not add a partition to the global range partitioned indexes, the highest value since the partition boundary is usually MAXVALUE. To add a new highest partition, use the ALTER INDEX ... SPLIT PARTITION statement.
     
         
         
         
  More:      
 
- Java proxy use and proxy principle (Programming)
- Repair Raspbian Encountered a section with no Package (Linux)
- Ease of use "Explain Shell" script to understand Shell command (Linux)
- Autojump: an advanced cd command in the Linux file system fast navigation (Linux)
- CoreOS use register mirror to build private warehouse (Linux)
- Transfer MySQL database to MariaDB (Database)
- How to Create a file can not be changed under Linux (Linux)
- LVM management parameters commonly used commands explained in detail (Linux)
- Upgrading to MySQL 5.7.9 MySQL 5.6.23 (Database)
- Machine Learning: Classification of the curse of dimensionality (Programming)
- To install PXE + Kickstart under CentOS 6.x operating system (Linux)
- mysqldump issue a note (Database)
- lack of SWAP space during installation of Oracle (Database)
- Ubuntu install Eclipse for C / C ++ and related configuration (Linux)
- Linux using DenyHosts prevents ssh cracks (Linux)
- Git bulk delete remote tag (Linux)
- Common Linux system performance monitoring command (Linux)
- Python in yield (Programming)
- Docker Private Registry Installation Guide at CentOS6.X (Linux)
- Linux system started to learn: the Linux syslog (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.