? Oracle8 partition management? 1? Subregion Overview 2? Subregional Advantages 3? Partition Management? ?? Division table The expansion of the partition table ?? Remove unnecessary partitions??? Other action?? ?? View partition information? ?? EXPORT partition ?? import partition? ----? 1, partition overview:? ----? In order to simplify the management of the database, Oracle8 launches the partition option. The partition is separated in several different tablespaces, and the method of creating a division is used to support an infinite expansion, giving the large table in the physical level. Split a large table into a smaller partition can improve the maintenance, backup, recovery, transaction and query performance of the table. Oracle8 can be recommended for the current social security and a large daily business data in the telecommunications industry. ? ----? Second, the advantages of the partition:? ----? 1 ?, enhance usability: If a partition of the table cannot be used due to the system failure, the rest of the table can still be used;? --- -? 2 ?, reduced closing time: If the system fault only affects some partitions of the table, only this part of the partition needs to be repaired, so the time is less time to repair the whole big table;? ----? 3 ?, maintenance Easily: If you need a rebuild table, independent management of each partition is much easier than managing a single big table;? ----? 4?, Equalize I / O: You can assign different partitions to different disks to balance i / O Improve performance;? ----? 5?, Improve performance: the query, increase, modification operation of the big table can be decomposed to different partitions of the table to perform parallel, 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.
? ----? Three, partition management:? ---? 1? 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 the various partitions of the table: CREATE? TABLESPACE? TS_SALE1999Q1? DataFile? ' / 11/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? (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? LESS? THAN? (To_date ('1999-04-01', 'YYYY-mm-dd') TABLESPACE? TS_SALE1999Q1, Partition? Sales 1999_Q2 Values? Less? Than? (To_date ('1999-07-01', 'YYYY-mm-dd') TABLESPACE? TS_SALE1999Q2, Partition? 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? 40M, the operation is as follows. Step1, build 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, such as method.
Step2, add table space for the table: ALTER? TABLE? SALES ADD? Partition? Sales2000_q1 value? Less? Than? (To_date ('2000-04-01', 'YYYY-MM-DD') TABLESPACE? TS_SALE2000Q1;? Other partitions Sales2000_Q1, Sales2000_Q1, Sales2000_Q1, such as the method. ----? 3?, delete unnecessary partitions:? ----? Company stipulate that sales must be stored online within two years. By 2001, DBA must be 1999 Data Backup (see 5, Export Partition), delete the 1999 partition, use space for later data. This is cycled, always two years of sales data online.? Step1, Drop? Partition: ALTER? TABLE? Sales Drop? part; alter? table? sales drop? part? sales1999_q2; alter? table? sales drop? part? sales1999_q3; alter? table? sales drop? part? sales1999_q4; step2, using the operating system tool to delete the above table The file occupied by 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:? ----? partition Other operations include truncate, divide the existing partitions into multiple partitions, swap partitions, renames, and establish indexes for partitioning indexes. DBA can be used according to appropriate conditions.
• Hereinafter, only the split division (split), for example, the company's sales detail data in the fourth quarter of 1999 (because the National Day, Welcome Millennium, He returns), DBA recommends the fourth quarter The partition is divided into two partitions, and each partition is put two months of data, the operation is as follows:? Step1, press (1) to establish two partitioned table space Ts_sales1999q4p1, ts_sales1999q4p2; step2, add two partition Sales1999_Q4_P1, Sales1999_q4_p2; 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? Wheree ? Segment_name = 'Sales'; segment_na? Partition_? Segment_type? TableSpace? ----------? ------------? ----------- ----? -------------- Sales? Sales1999_Q1? Table? Partition? Ts_sales1999q1 sales? Sales1999_Q2? Table? Partition? Ts_sales1999q2 sales? Sales1999_Q3? Table? Partition? Ts_sales1999 q3 sales? Sales1999_q4? Sales1999_q4? TABLE? PARTITION? TS_SALES1999Q4 SALES? SALES2000_Q1? TABLE? PARTITION? TS_SALES1999Q1 SALES? SALES2000_Q2? TABLE? PARTITION? TS_SALES1999Q2 SALES? SALES2000_Q3? TABLE? PARTITION? TS_SALES1999Q3 SALES? SALES2000_Q4? TABLE? PARTITION? TS_SALES1999 Q4 ----? 5?, Export partition:? ----? Oracle8 Export® tool can be partitioned and exported in the table, for example in 2001, DBA must export in 1999, and the operation is as follows :??? oracle $ exp sales / sales_password tables = sales:??? sales1999_q1 rows = Y file = sales1999_q1.dmp oracle $ exp sales / sales_password tables = sales:??? sales1999_q2 rows = Y file = sales1999_q2.dmp oracle $ • EXP? SALES / SALES_PASSWORD? TABLES = SALES: SALES1999_Q3? ROWS = y File = Sales1999_Q3.dmp Oracle $? EXP? SALES / SALES_PASSWORD? TABLES = SALES: SALES1999_Q4? ROWS = y File = Sales1999_q4.dmp ----? 6 ?
