After installing Oracle, frequently used SQL code to modify the table space

2010-10-10  来源:本站原创  分类:Database  人气:168 

Objectives:
Rollback segment is too small for the system, is intended to generate a new rollback segments,
Establishment of large, new table space (table space, index table space, table space for rollback, temporary table space)
Built two data table space, the two index table space, so the purpose of construction is based on practical applications,
Such as: application of the existing 10 users, each user is an independent subsystem (such as: Business Invoicing MIS system finance, receivables, inventory, personnel, general manager, etc.)
In particular, many large shopping malls in the cash register, while access to the process of many, often the process of simultaneous access to 50-100,
In this way, through the establishment of more than one user table space, index table space to each user, which were built in a different table space (more than one user table space on different physical disk),
Among users to reduce the I / O competition, read and write data and write-read the index of competition (user table space, index table space on separate physical disk)

Planning:
C: drive, NT system, Oracle system D: drive, the data table space 1 (3GB, automatic expansion), rollback table space 1 (1GB, automatic expansion)
E: drive, the data table space 2 (3GB, automatic expansion), rollback table space 2 (1GB, automatic expansion)
F: disk, the index table space 1 (2GB, automatic expansion), the temporary table space 1 (0.5GB, do not automatically expand)
G: drive, the index table space 2 (2GB, automatic expansion), the temporary table space 2 (0.5GB, do not automatically expand)

Note: This is just a simple planning, physical planning needs to be determined by the system to minimize the I / O competition to achieve:
1, first see what the system rollback segment and the state.

SQL> col owner format a20
SQL> col status format a10
SQL> col segment_name format a20
SQL> 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 /

Query to 18 records.

SQL> SELECT SEGMENT_NAME, OWNER,
2 TABLESPACE_NAME, SEGMENT_ID, FILE_ID, STATUS
3 FROM DBA_ROLLBACK_SEGS
4 /

Query to 18 records.

2, modify the code below, you can put the following code into a. Sql files, such as cg_sys.sql, then SQL> @ cg_sys.sql call execution.

- Note: all built on the hard disk beforehand oradata directory - modify the existing rollback segments, 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 of a data table space
- Receivables, inventory, ordering, 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.ora' size 512M,
'D: \ oradata \ user1_4.ora' size 512M,
'D: \ oradata \ user1_5.ora' size 512M,
'D: \ oradata \ user1_6.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
- Initial 128K, because the built in table space on the user, but the table was built in the user where the user-owned and
- Users inherit the data table storage parameters, the user's storage parameters table inheritance
- If the initial set is too large, such as: 5M, an empty table for each building will occupy the space 5M, even if a record is not - AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED, set up data files automatically expand, each augmentation 5M, the largest space is not limited - build data table space 2
- Price, personnel, billing, finance, general manager, contracts, statistical 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 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);

- Construction of the index table space 1
create tablespace INDEX_DATA1 datafile
'F: \ oradata \ index1_1.ora' size 512M,
'F: \ oradata \ index1_2.ora' size 512M,
'F: \ oradata \ index1_3.ora' size 512M,
'F: \ oradata \ index1_4.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);

- Construction of the index table space 2
create tablespace INDEX_DATA2 datafile
'G: \ oradata \ index2_1.ora' size 512M,
'G: \ oradata \ index2_2.ora' size 512M,
'G: \ oradata \ index2_3.ora' size 512M,
'G: \ oradata \ index2_4.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);

- Construction of a rollback tablespace
- Set the initial value of 40M (initial 40M), then each table space in the construction of a rollback,
- This rollback automatically inherit the rollback tablespace storage parameters, that is the default file is 40M
create tablespace ROLLBACK_DATA1 datafile
'D: \ oradata \ roll1_1.ora' size 512M,
'D: \ oradata \ roll1_2.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 40M next 5M pctincrease 0);

- Building rollback table space 2
create tablespace ROLLBACK_DATA2 datafile
'E: \ oradata \ roll2_1.ora' size 512M,
'E: \ oradata \ roll2_2.ora' size 512M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
default storage (initial 40M next 5M pctincrease 0);

- Construction of a temporary table space
create tablespace TEMPORARY_DATA1 datafile
'F: \ oradata \ temp1_1.ora' size 512M
default storage (initial 10M next 3M pctincrease 0);

- Construction of temporary table space 2
create tablespace TEMPORARY_DATA2 datafile
'G: \ oradata \ temp2_1.ora' size 512M
default storage (initial 10M next 3M pctincrease 0);

- To truly become a temporary alter tablespace TEMPORARY_DATA1 temporary;
alter tablespace TEMPORARY_DATA2 temporary;

- Create a new rollback segments, each as large rollback segments of different sizes does not make sense, the system is randomly selected.
- Build number, according to the number of concurrent access users,
- If you have 50-100 people a day use the Oracle system development management software, 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;
- Top 8 rollback table space built in 1, after eight in the rollback tablespace 2
create public rollback segment rb11 tablespace rollback_data2;
create public rollback segment rb12 tablespace rollback_data2;
create public rollback segment rb13 tablespace rollback_data2;
create pub

Article Reprinted from home network: http://www.bitscn.com/pdb/oracle/200604/18261.html

相关文章