Configuration: Windows NT 4.0 Chinese version 5 10.2GB SCSI hard disk score: C: disk, D: disk, E: disk, F: disk, g: disk Oracle 8.0.4 for Windows NTNT installation in C: / Winnt, Oracle installation In C: / ORANT
Objective: Because the system's returns are too small, they are now intended to generate new returns, build big, new table space (data table space, index table space, rollback table space, temporary table space,) two Data table space, two index table spaces, so the purpose is based on practical applications, such as: existing application users, each user is a separate subsystem (eg, business importance memory MIS system finance, collection The stock, personnel, general manager, etc.), especially large shopping malls, there are many cash regions, and the access process is many, often reaching 50-100 processes simultaneously access, so that by establishing multiple user tablespaces, index table space, each Users are built in different tablespaces (multiple user tablespaces on different physical disks), reduce the I / O competition between users, read and write data and writing indexes (user table space, index Table space is also placed on different physical disks, respectively)
Plan: C: Disc, NT system, Oracle system D: disk, data table space 1 (3GB, automatic expansion), rollback table space 1 (1GB, automatic extension) E: disk, data table space 2 (3GB, automatic expansion ), Rollback table space 2 (1GB, automatic extension) F: disk, index table space 1 (2GB, automatic extension), temporary table space 1 (0.5GB, not automatic extension) G: disk, index table space 2 (2GB , Automatic expansion), temporary table space 2 (0.5GB, not automatically expand)
Note: This is just a simple plan, and actual planning should be determined according to system requirements, try to minimize I / O competition.
Implement: 1. First check the system and its returns and its status.
SQL> Col Owner Format A20SQL> Col STATUS FORMAT A10SQL> Col Segment_Name Format A20SQL> Col TableSpace_name Format A20
SQL> SELECT OWNER, Segment_name, TableSpace_name, SUM (BYTES) / 1024/1024 M 2 from DBA_SEGMENTS 3 where segment_type = 'rollback' 4 group by Owner, segment_name, tablespace_name 5 /
Owner segment_name tablespace_name M ------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------- --------- SYS RB1 ROLLBACK_DATA .09765625SYS RB10 ROLLBACK_DATA .09765625SYS RB11 ROLLBACK_DATA .09765625SYS RB12 ROLLBACK_DATA .09765625SYS RB13 ROLLBACK_DATA .09765625SYS RB14 ROLLBACK_DATA .09765625SYS RB15 ROLLBACK_DATA .09765625sys rb16 rollback_data .09765625sys rb2 rollback_data .09765625sys rb3 rollback_data .09765625sys rb4 rollback_data .09765625sys rb5 rollback_data. 09765625SYS RB6 ROLLBACK_DATA .09765625SYS RB7 ROLLBACK_DATA .09765625SYS RB8 ROLLBACK_DATA .09765625SYS RB9 ROLLBACK_DATA .09765625SYS RB_TEMP SYSTEM .24414063SYS SYSTEM SYSTEM .1953125 query to 18 records.
SQL> SELECT Segment_name, Owner, 2 TableSpace_name, segment_id, file_id, status 3 from dba_rollback_segs 4 /
Segment_name oowner tablespace_name segment_id file_id status -------------------------------------- ------------------------------------------------------------------------------ ------------------------ ------------------ System Sys System 0 1 ONLINERB_TEMP SYS SYSTEM 1 1 OFFLINERB1 PUBLIC ROLLBACK_DATA 2 3 ONLINERB2 PUBLIC ROLLBACK_DATA 3 3 ONLINERB3 PUBLIC ROLLBACK_DATA 4 3 ONLINERB4 PUBLIC ROLLBACK_DATA 5 3 ONLINERB5 PUBLIC ROLLBACK_DATA 6 3 ONLINERB6 PUBLIC ROLLBACK_DATA 7 3 OFFLINERB7 PUBLIC ROLLBACK_DATA 8 3 OFFLINERB8 PUBLIC ROLLBACK_DATA 9 3 OFFLINERB9 PUBLIC ROLLBACK_DATA 10 3 OFFLINERB10 PUBLIC ROLLBACK_DATA 11 3 OFFLINERB11 PUBLIC ROLLBACK_DATA 12 3 OFFLINERB12 PUBLIC ROLLBACK_DATA 13 3 OFFLINERB13 PUBLIC ROLLBACK_DATA 14 3 OFFLINERB14 PUBLIC ROLLBACK_DATA 15 3 OFFLINERB15 PUBLIC ROLLBACK_DATA 16 3 OFFLINERB16 PUBL
IC Rollback_Data 17 3 OFFLINE queries 18 records.
2. The modification code is as follows, you can store the following code into a .sql file, such as cg_sys.sql, then call it in SQL> @ cg_sys.sql.
- Note: It is necessary to build an ORADATA directory in each hard drive.
- modify an existing rollback, so that failure, offline alter rollback segment rb1 offline; alter rollback segment rb2 offline; alter rollback segment rb3 offline; alter rollback segment rb4 offline; alter rollback segment rb5 offline; alter rollback segment rb6 offline ; alter rollback segment rb7 offline; alter rollback segment rb8 offline; alter rollback segment rb9 offline; alter rollback segment rb10 offline; alter rollback segment rb11 offline; alter rollback segment rb12 offline; alter rollback segment rb13 offline; alter rollback segment rb14 offline; alter Rollback Segment RB15 OFFLINE; Alter Rollback Segment RB16 OFFLINE
- delete the original rollback drop rollback segment rb1; drop rollback segment rb2; drop rollback segment rb3; drop rollback segment rb4; drop rollback segment rb5; drop rollback segment rb6; drop rollback segment rb7; drop rollback segment rb8; drop rollback segment rb9; drop rollback segment rb10; drop rollback segment rb11; drop rollback segment rb12; drop rollback segment rb13; drop rollback segment rb14; drop rollback segment rb15; drop rollback segment rb16;
- Construction Data Sheet Space 1 - Collection, Inventory, Order, Remote Communication Create Tablespace User_Data1 DataFile'D: /oradata/User1_1.ora 'Size 512M,' D: / OraData/User1_2.ora 'Size 512M,' D : /oradata/user1_3.oradata/User1_3.ora 'Size 512M,' D: /oradata/user1_4.oradata/user1_5.ora 'size 512m,' d: /oradata/user1_6.ora 'size 512mautoExtend ON Next 5M MaxSize UnlimitedDefault Storage (Initial 128k NEXT 2M PCTINCREASE 0); - Initial 128k, because the user built on the tablespace, the table is built in the user, and the user has the storage parameters of the user inherited data table space. , The table inherits the user's storage parameters - if INITIAL is set, such as: 5M, then take a 5M space, even if a record is not --autoextend on next 5m maxsize unlimited, set a data file Automatic extension, each extended by 5M, maximum space is not limited - Building data table space 2 - Price, personnel, settlement, finance, general manager, contract, statistics create tablespace user_data2 datafile'e: /oradata/user2_1.ora 'size 512m,' E: /oradata/user2_2.ora 'size 512m,' E: /oradata/user2_3.ora 'size 512m,' E: / OraData/User2_4.ora 'size 512m,' E: / ORADATA / USER2_5 . Ora 'Size 512M,' E: /ORADATA/USER2_6.ORA 'SIZE 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDDEFAULT Storage (Initial 128k Next 2m Pctincrease 0);
- Construction Social Table Space 1CREATE TABLESPACE INDEX_DATA1 DATAFILE'F: / ORADATA/index1_1.ora 'size 512m,' f: /oradata/index1_2.ora 'size/index1_3.ora' size 512m, ' f: /oradata/index1_4.ora 'size 512mautoextend On Next 5M MaxSize UnlimitedDefault Storage (Initial 128k Next 2m Pctincrease 0);
- Construction Social Table Space 2create Tablespace Index_Data2 DataFile'g: /oradata/index2_1.ora 'size/index2_2.ora' size/index2_2.ora 'size/index2_3.ora' size 512m, ' g: /oradata/index2_4.ora 'size 512mautoextend On Next 5m MaxSize UnlimitedDefault Storage (Initial 128k Next 2m Pctincrease 0);
- Built back to the scroll table space 1 - Set the initial value of 40M (Initial 40M), then build a roll band everywhere in this table, - This split segment automatically inherits the storage parameters of this back scroll space, also i.e. the default file is 40Mcreate tablespace ROLLBACK_DATA1 datafile'd: /oradata/roll1_1.ora 'size 512M,' d: /oradata/roll1_2.ora 'size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0); - construction rollback table space 2create tablespace ROLLBACK_DATA2 datafile'e: /oradata/roll2_1.ora 'size 512M,' e: /oradata/roll2_2.ora 'size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);
- Construction Temporal Table Space 1CREATE TABLESPACE TEMPORY_DATA1 DATAFILE'F: / ORADATA/temp1_1.ora 'size 512mdefault Storage (Initial 10M next 3m Pctincrease 0);
- Construction Temporal Table Space 2Create TableSpace Temporary_data2 DataFile'g: /oradata/temp2_1.ora 'size 512mdefault Storage (Initial 10M Next 3m Pctincrease 0);
- Make it really a temporary Alter TableSpace Temporary_data1 Temporary; ALTER TABLESPACE TEMPORY_DATA2 TEMPORY;
- Establish a new rollback segment, each is the same, and there is no significance of the rollback segments of different sizes, and the system is randomly selected. - How many, according to concurrent access to users, - If your company has 50-100 people using Oracle system development management software, it should be more than 20
create public rollback segment rb01 tablespace rollback_data1; create public rollback segment rb02 tablespace rollback_data1; create public rollback segment rb03 tablespace rollback_data1; create public rollback segment rb04 tablespace rollback_data1; create public rollback segment rb05 tablespace rollback_data1; create public rollback segment rb06 tablespace rollback_data1; create public rollback segment rb07 tablespace rollback_data1; create public rollback segment rb08 tablespace rollback_data1; create public rollback segment rb09 tablespace rollback_data2; create public rollback segment rb10 tablespace rollback_data2; - 8 before rollback table built in the space 1, after 8 rollback tablespace 2create public rollback segment rb11 tablespace rollback_data2; create public rollback segment rb12 tablespace rollback_data2; create public rollback segment rb13 tablespace rollback_data2; create public rollback segment rb14 tablespace rollback_data2; create public rollback segment rb15 tablespace rollback_data2; create public rollback segment rb16 tablespac e rollback_data2; create public rollback segment rb17 tablespace rollback_data2; create public rollback segment rb18 tablespace rollback_data2; create public rollback segment rb19 tablespace rollback_data2; create public rollback segment rb20 tablespace rollback_data2;
- that the rollback online, i.e. effectively alter rollback segment rb01 online; alter rollback segment rb02 online; alter rollback segment rb03 online; alter rollback segment rb04 online; alter rollback segment rb05 online; alter rollback segment rb06 online; alter rollback segment rb07 online; alter rollback segment rb08 online; alter rollback segment rb09 online; alter rollback segment rb10 online; alter rollback segment rb11 online; alter rollback segment rb12 online; alter rollback segment rb13 online; alter rollback segment rb14 online; alter rollback segment rb15 online; alter rollback segment rb16 online; alter rollback segment rb17 online; alter rollback segment rb18 online; alter rollback segment rb19 online; alter rollback segment rb20 online; - and view the rollback state prior col segment format a30SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME , Segment_id, file_id, status from dba_rollback_segs;
- View data files and their tablespace, size, status col file_name format a40col tablespace_name format a20select file_name, file_id, tablespace_name, bytes, status from dba_data_files;
At this point, the table space is re-planned, and the relatively popular here, there is a lot of parameters worth setting, can set Oracle to the best realm, the table space is set, the following, the Oracle's memory area, Oracle is very interesting, the more memory, the more obvious effect, so it is necessary to adjust the SGA area, which is mainly to configure the ininoorcl.ora parameter file.
