Oracle8 partition management
1 partition overview
2 points
3 partition management
Establishment of partition table
Dispensing of partition tables
Delete unnecessary partitions
Partitioning other operations
View partition information
Export partition
IMPORT partition
---- One, partition overview:
---- In order to simplify the management of the database, Oracle8 launches the partition option. The partition is separated on a number of different tablespaces.
The method of creating an infinite expansion is supported by means of a divided manner, giving a large table in the physical level. Separate large tables into smaller points
The area can improve the maintenance, backup, recovery, transaction and query performance of the table. A large number of daily business data in the current social security and telecommunications industry,
Oracle8 can be recommended.
---- Second, the advantages of the partition:
---- 1, enhanced availability: If a partition of the table cannot be used due to system failure, the rest of the table can still be used;
---- 2, Reduce Off Time: If the system trouble only affects part of the table, then only this partition needs to be repaired.
Therefore, it is less than the time to repair the entire big table;
---- 3, easy to maintain: If you need rebuilding tables, independent management of each partition is much easier than managing a single big table;
---- 4, Balance I / O: You can assign different partitions of the table to different disks to balance the I / O improve performance;
---- 5, improve performance: to decompose the different partitions of the table to different partitions of the table to perform different partitions of the table.
It can make the running speed faster;
---- 6, the partition is transparent to the user, and the end user does not feel the existence of the partition.
---- Third, partition management:
---- 1, the establishment of the partition table:
---- a large sales record of a company, DBA recommends that the data per quarter is placed in one partition,
The following demonstration is the company's 1999 data (assuming 30m data per month), the operation is as follows:
Step1, the tablespace of each partition of the table:
Create TableSpace TS_SALE1999Q1
DataFile '/u1/oradata/sales/sales1999_q1.dat'
Size 100M
Default Storage (Initial 30M Next 30M
Minextents 3 pctincrease 0)
CREATE TABLESPACE TS_SALE1999Q2
DataFile '/u1/oradata/sales/sales1999_q2.dat'
Size 100M
Default Storage (Initial 30M Next 30M
Minextents 3 pctincrease 0)
Create TableSpace TS_SALE1999Q3
DataFile '/u1/oradata/sales/sales1999_q3.dat'
Size 100M
Default Storage (Initial 30M Next 30M
Minextents 3 pctincrease 0)
Create TableSpace TS_SALE1999Q4
DataFile '/u1/oradata/sales/sales1999_q4.dat'
Size 100M
Default Storage (Initial 30M Next 30M
Minextents 3 pctincrease 0)
STEP2, establish a partition-based table:
Create Table Sales
(Invoice_NO Number,
...
Sale_date Date Not Null
Partition by Range (Sale_Date)
(Partition Sales1999_Q1
VALUES LESS THAN (TO_DATE
('1999-04-01', 'YYYY-MM-DD') TABLESPACE TS_SALE1999Q1,
Partition Sales1999_Q2
VALUES LESS THAN (TO_DATE
('1999-07-01', 'YYYY-MM-DD')
TableSpace TS_SALE1999Q2,
Partition Sales1999_Q3
VALUES LESS THAN (TO_DATE
('1999-10-01', 'YYYY-MM-DD')
TableSpace TS_SALE1999Q3,
Partition Sales1999_Q4
VALUES LESS THAN (TO_DATE
('2000-01-01', 'YYYY-MM-DD')
TABLESPACE TS_SALE1999Q4
;
return
---- 2, expansion of the partition table:
---- By the end of 1999, the DBA should join the table space in the table, which is also a table space per quarter.
Since the company's business is thriving, it is expected that each partition is 40m, the operation is as follows.
Step1, set a table space:
Create TableSpace TS_SALE2000Q1
DataFile '/u1/oradata/sales/sales2000_q1.dat'
Size 130M
Default Storage (Initial 40M NEXT 40M
Minextents 3 pctincrease 0)
Other table space TS_SALE2000Q2, TS_SALE2000Q3,
Ts_sales2000q4 is like a law.
Step2, add table space for the table:
Alter Table Sales
Add Partition Sales2000_Q1
VALUES LESS THAN (TO_DATE
('2000-04-01', 'YYYY-MM-DD')
TABLESPACE TS_SALE2000Q1;
Other partition sales2000_q1, sales2000_q1,
Sales2000_Q1 is like a law.
---- 3, delete unnecessary partitions:
---- Company stipulates that the sales details must be saved for two years. By 2001, DBA must backup 1999 data.
(See 5, Export Partitions in the backup method), delete the 1999 partition, use the space for later data. This loop, always maintain two years of sales data online.
Step1, Drop partition:
Alter Table Sales
DROP PARTION SALES1999_Q1;
Alter Table Sales
DROP PARTION SALES1999_Q2;
Alter Table Sales
DROP PARTION SALES1999_Q3;
Alter Table Sales
DROP PARTION SALES1999_Q4;
STEP2, using the tool tool to delete the files occupied by the above table space
(The table space is based on the naked equipment; the UNIX system is an example:
Oracle $ rm /u1/oradata/sales/sales1999_q1.dat
Oracle $ rm /u1/oradata/sales/sales1999_q2.dat
Oracle $ rm /u1/oradata/sales/sales1999_q3.dat
Oracle $ rm /u1/oradata/sales/sales1999_q4.dat
---- 4, other operations of the partition:
---- Other operations of the partition include truncate, divide the existing partitions into multiple partitions (Split),
Exchanged, rename, establish an index for partitioning, and so on. DBA can be used in accordance with appropriate conditions. ---- The following only describe the split partition (Split), for example, the company's sales detail data increased in the fourth quarter of 1999
(Because Qing National Day, Welcome Millennium, He returns), DBA recommends dividing the divided partition in the fourth quarter into two partitions.
Each partition is put two months of data, the operation is as follows:
STEP1, the method of (1) is established to establish two partitioned table space Ts_sales1999q4p1,
Ts_sales1999q4p2;
Step2, add two partitions1999_q4_p1, sales1999_q4_p2 to the table;
Step3, split partition:
Alter Table Sales
Split partiton sales1999_q4
At to_date ('1999-11-01', 'YYYY-MM-DD')
INTO (Partition Sales1999_Q4_P1, Partition Sales1999_Q4_P2)
---- 5, view partition information:
---- DBA To view the partition information of the table, view the data dictionary USER_EXTENTS, the operation is as follows:
SVRMGRL> Select * from user_extents where segment_name = 'sales';
Segment_na partition_ segment_type tablespace
---------- --------------------------- -
Sales Sales1999_Q1 Table Partition TS_SAAS1999Q1
Sales Sales1999_Q2 Table Partition TS_SAAS1999Q2
Sales Sales1999_Q3 Table Partition TS_SAAS1999Q3
Sales Sales1999_Q4 Table Partition TS_SAAS1999Q4
Sales Sales2000_Q1 Table Partition TS_SAAS1999Q1
Sales Sales2000_Q2 Table Partition TS_SAles1999Q2
Sales Sales2000_Q3 Table Partition TS_SAles1999Q3
Sales Sales2000_Q4 Table Partition TS_SAAS1999Q4
---- 5, Export partition:
---- Oracle8 Export tools can be partitioned and exported in the table, for example, to 2001, DBA must
The 1999 data is exported according to the partition, the operation is as follows:
Oracle $ ev sales / sales_password tables = sales:
Sales1999_q1 rows = y
FILE = Sales1999_Q1.dmp
Oracle $ ev sales / sales_password tables = sales:
Sales1999_q2 rows = y
FILE = Sales1999_q2.dmp
Oracle $ ev sales / sales_password tables = sales:
Sales1999_q3 rows = y
FILE = Sales1999_Q3.dmp
Oracle $ ev sales / sales_password tables = sales:
Sales1999_q4 rows = y
FILE = Sales1999_q4.dmp
---- 6, Import Partition: ---- Oracle8 Import Tools can be partitioned and imported in the table, for example in 2001, users want to see 1999
Data, DBA must import 1999 data, enable it online, operate as follows: Step1, the 1999 four
Table space and corresponding partition, reference (2); Step2, import data:
Oracle $ IMP SALES / SALES_PASSWORD FILE = Sales1999_q1.dmp
Tables = (Sales: Sales1999_Q1) ignore = y
Oracle $ IMP SALES / SALES_PASSWORD FILE = SALES1999_Q2.DMP
Tables = (Sales: Sales1999_Q2) ignore = y
Oracle $ IMP SALES / SALES_PASSWORD FILE = Sales1999_q3.dmp
Tables = (Sales: Sales1999_Q3) ignore = y
Oracle $ IMP SALES / SALES_PASSWORD FILE = Sales1999_q4.dmp
Tables = (Sales: Sales1999_Q4) ignore = y
