Oracle Normal table Conversion partition table

1: View the table space situation
Select Tablespace_name,
FILE_ID,
file_name,
Round (Bytes/(1024 * 1024), 0) Total_space
From Dba_data_files
ORDER BY Tablespace_name




2: Create a table space
CREATE tablespace Gxpt_in_gxzy_data
LOGGING
DataFile '/ora_data/sfgxpt/gxpt_in_gxzy_data_001.dbf ' SIZE 10240M reuse Autoextend
On NEXT 2048M MAXSIZE Unlimited EXTENT MANAGEMENT
Local SEGMENT MANAGEMENT AUTO;


3: Convert a normal table to a partitioned table
Mode 1: No table space specified
CREATE table T_yj_part (ID, name, dept, Salery, postion, CreateDate) partition by range (CreateDate)
(partition t_yj_part_1 values less than (to_date (' 2016-01-29 ', ' yyyy-mm-dd ')),
Partition t_yj_part_2 values less than (to_date (' 2016-02-29 ', ' yyyy-mm-dd ')),
Partition T_yj_part_3 values less than (to_date (' 2016-03-29 ', ' yyyy-mm-dd ')),
Partition T_yj_part_4 values less than (to_date (' 2016-04-29 ', ' yyyy-mm-dd ')),
Partition t_yj_part_5 values less than (to_date (' 2016-05-29 ', ' yyyy-mm-dd ')),
Partition T_yj_part_6 values less than (to_date (' 2016-06-29 ', ' yyyy-mm-dd ')),
Partition t_yj_part_7 values less than (to_date (' 2016-07-29 ', ' yyyy-mm-dd ')),
Partition T_yj_part_8 values less than (to_date (' 2016-08-29 ', ' yyyy-mm-dd ')),
Partition T_yj_part_9 values less than (to_date (' 2016-09-29 ', ' yyyy-mm-dd '))
As select ID, name, dept, Salery, Postion, createdate from T_yj;
Mode 1: Specify table space
CREATE table T_yj_part (ID, name, dept, Salery, postion, CreateDate) partition by range (CreateDate)
(partition t_yj_part_1 values less than (to_date (' 2016-01-29 ', ' yyyy-mm-dd ')) tablespace Gxpt_fq_part1,
Partition t_yj_part_2 values less than (to_date (' 2016-02-29 ', ' yyyy-mm-dd ')) tablespace Gxpt_fq_part1,
Partition T_yj_part_3 values less than (to_date (' 2016-03-29 ', ' yyyy-mm-dd ')) tablespace Gxpt_fq_part1,
Partition T_yj_part_4 values less than (to_date (' 2016-04-29 ', ' yyyy-mm-dd ')) tablespace Gxpt_fq_part1,
Partition t_yj_part_5 values less than (to_date (' 2016-05-29 ', ' yyyy-mm-dd ')) tablespace Gxpt_fq_part1,
Partition T_yj_part_6 values less than (to_date (' 2016-06-29 ', ' yyyy-mm-dd ')) tablespace Gxpt_fq_part1,
Partition t_yj_part_7 values less than (to_date (' 2016-07-29 ', ' yyyy-mm-dd ')) tablespace Gxpt_fq_part2,
Partition T_yj_part_8 values less than (to_date (' 2016-08-29 ', ' yyyy-mm-dd ')) tablespace Gxpt_fq_part2,
Partition T_yj_part_9 values less than (to_date (' 2016-09-29 ', ' yyyy-mm-dd ')) tablespace gxpt_fq_part2)
As select ID, name, dept, Salery, Postion, createdate from T_yj;
4: Create the partition index
Partitioned indexes and Global indexes:
A: Partitioned indexing is the creation of a separate index on all of the zones, which can be automatically maintained, which does not affect the use of other partitioned indexes of the index when a drop or truncate a partition, that is, the index does not fail, is easier to maintain, but has a slight impact on query performance.
--Create/recreate indexes
Create index IDX_TA_C2 on TA (C2) local (partition p1,partition p2,partition p3,partition); or CREATE index idx_ta_c2 on TA (C2) local;
Also in the Create unique index idx_ta_c2 on TA (C2) Local, the system will report a ORA-14039 error because the partition column of the TA table is c1,oracle does not support the primary key column does not contain the partition column when the PK primary key is created on the partition table. Creating additional constraints (unique) is also not possible.
B: The global index is the creation of an index on the entire table, it can create its own partitions, can be different from the partition table partition, that is, it is a separate index. You need to create an index alter INDEX IDX_XX rebuild when you drop or truncate a partition, or ALTER TABLE table_name DROP PARTITION Partition_name update Global indexes, but it takes a long time to rebuild the index. You can see whether the index is valid by querying the user_indexes, user_part_indexes, and user_ind_partitions views.
Create index IDX_TA_C3 on Ta (C3);
Or divide the global index into multiple zones (note that the partitions of the partitioned table are different):
Create index idx_ta_c4 on TA (c4) global partition by Range (C4) (partition ip1 values less than (10000), partition IP2 values Less than (20000), partition IP3 values less than (MaxValue));
Note that the boot columns on the index are consistent with the range, otherwise there will be a ORA-14038 error.
Oracle automatically creates global indexes on primary keys
If you want to create a partitioned index on a column on a primary key, unless the primary key includes the partitioning key, the primary key is built on two or above.
Avoid the use of global indexes for easy maintenance When you frequently delete a table's partitions and update the data more frequently.
Show Oracle View partition table information
Display information for all partitioned tables in the database: Dba_part_tables
Displays all partition table information accessible to the current user: All_part_tables
Displays information for all partitioned tables for the current user: User_part_tables
Show Oracle View partition table information displays detailed partition information for all partitioned tables in the database: Dba_tab_partitions
Displays detailed partition information for all partitioned tables accessible to the current user: All_tab_partitions
Displays detailed partition information for all partitioned tables for the current user: User_tab_partitions
Display child partition information display sub-partition information for all combined partitioned tables in the database: Dba_tab_subpartitions
Displays child partition information for all combined partitioned tables accessible to the current user: All_tab_subpartitions
Displays child partition information for all grouped partitioned tables for the current user: User_tab_subpartitions
Display partition columns display partition column information for all partitioned tables in the database: Dba_part_key_columns
Displays the partition column information for all partitioned tables accessible to the current user: All_part_key_columns
Displays the partition column information for all the current user's partitioned tables: User_part_key_columns
Display sub-partition columns display sub-partition column information for all partitioned tables in the database: Dba_subpart_key_columns
Displays the child partition column information for all partitioned tables accessible to the current user: All_subpart_key_columns
Displays the child partition column information for all partitioned tables for the current user: User_subpart_key_columns
---------------------------------------------------------------------------------------------------
How to query all the partitioned tables in the Oracle database
SELECT * from User_tables a where a.partitioned= ' YES ' deletes a table of data that is TRUNCATE TABLE table_name;
Deleting the partition table data for one partition is ALTER TABLE TABLE_NAME TRUNCATE PARTITION P5;
If I want to empty the data of each partition in the partition table, I can use TRUNCATE TABLE table_name.
Or must be done from the beginning
ALTER TABLE TABLE_NAME TRUNCATE PARTITION p1;
ALTER TABLE TABLE_NAME TRUNCATE PARTITION p2;
ALTER TABLE TABLE_NAME TRUNCATE PARTITION P3;
ALTER TABLE TABLE_NAME TRUNCATE PARTITION P4;
ALTER TABLE TABLE_NAME TRUNCATE PARTITION P5;
ALTER TABLE TABLE_NAME TRUNCATE PARTITION P6;
Answer: TRUNCATE TABLE table_name