Shulou(Shulou.com)06/01 Report--

The storage settings of Oracle database can be divided into three levels: at the global scope, at the tablespace level, and at the segment level.

With the growth of data, some tablespaces may be running out, while others may be idle for a long time, which requires adjusting the size of tablespaces in the following ways:

1. Set to automatic growth; 2. Use the ALTER command to adjust manually; 3. Expand the tablespace by adding Datafile; 4. Directly increase the tablespace

The method of ALTERR TABLESPACE is aimed at the changes in datadictionary, which is not recommended. If you are interested, you can consult the relevant content in the online documentation.

First, automatic growth, using AUTOEXTEND

Can only be automatically increased, can be set when creating a tablespace or database, any statement with DATAFILE, you can set NEXT and Max value.

The volume of tablespace is stored in the table DBA_DATA_FILES

DBA_FREE_SPACE stores the volume of tablespace that has been used

/ * demonstrate how to set the tablespace to grow automatically * /

= query remaining tablespace =

[oracle@localhostnotes] $vim tablespace_usage.sql

SELECT a.tablespace_name, a.bytes bytes_used, b.largest, round ((a.bytesb.bytes) / a.bytes) * 100SELECT tablespace_name 2) percent_usedFROM (SELECT tablespace_name, sum (bytes) bytes FROMdba_ data_files GROUP BYtablespace_name) a, (SELECT tablespace_name, sum (bytes) bytes, max (bytes) largest FROMdba_free_space GROUP BYtablespace_name) bWHERE a.tablespace_name = b.tablespace_nameORDER BY ((a.bytesb.bytes) / a.bytes) DESC -- tables and related values of the remaining tablespace size are not directly provided in the database,-- so you can search for some query statements of the remaining tablespace through the network.

SQL > @ notes/tablespace_usage.sql

TABLESPACE_NAME BYTES_USED LARGEST PERCENT_USED---SYSTEM 723517440 3145728 99.52SYSAUX 671088640 34603008 94.73USERS 5242880 458752 88.75EXAMPLE 104857600 19726336 78.44UNDOTBS1 57671680 29360128 32.27WILEY 20971520 19660800 5.63muri-you can see that there is more space left in the table space wiley-and SYSTEM Table spaces such as SYSAUX and Tablespace are running out. Therefore, it is necessary to expand its capacity by 6 rows selected.

= expand wiley tablespace =

SQL > alter tablespace wiley add datafile

2'/ oracle/oradata/orcl/wiley2.dbf' size 20m

3 autoextend on next 10M maxsize100M

Tablespace altered.

= View the remaining tablespaces again =

SQL > @ notes/tablespace_usage.sql

TABLESPACE_NAME BYTES_USED LARGEST PERCENT_USED---SYSTEM 723517440 3145728 99.52SYSAUX 671088640 34603008 94.73USERS 5242880 458752 88.75EXAMPLE 104857600 19726336 78.44UNDOTBS1 57671680 29360128 32.27WILEY 41943040 19922944 5.31 rows selected-it can be seen that the volume of tablespace wiley has increased by 20m 6 rows selected.

= check whether the autoextend property of the tablespace is enable =

SQL > desc dba_data_files

Name Null? Type-FILE_NAME VARCHAR2 (513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2 (30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2 (9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2 (3)-indicates whether MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES can be increased NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2 (7)

SQL > col FILE_NAME format A40

SQL > col TABLESPACE_NAME format A20

SQL > select file_name, tablespace_name, autoextensible from dba_data_files

FILE_NAME TABLESPACE_NAME AUT---/ oracle/oradata/orcl/users01.dbf USERS YES/oracle/oradata/orcl/undotbs01. Dbf UNDOTBS1 YES/oracle/oradata/orcl/sysaux01.dbf SYSAUX YES/oracle/oradata/orcl/system01.dbf SYSTEM YES/oracle/oradata/orcl/example01.dbf EXAMPLE YES/oracle/oradata/orcl/mickey01.dbf MICKEY NO/oracle/oradata/orcl/wiley.dbf WILEY NO -- the first data file wiley of the tablespace wiley cannot grow automatically. / the second data file wiley2 of the oracle/oradata/orcl/wiley2.dbf WILEY YES-- tablespace wiley can automatically grow by 8 rows selected.

= set the datafile that was originally set to not grow automatically to grow automatically =

SQL > alter database datafile

2'/ oracle/oradata/orcl/wiley.dbf'

3 autoextend on next 10M maxsize100M

Database altered.

SQL > select file_name, tablespace_name,autoextensible from dba_data_files

FILE_NAME TABLESPACE_NAME AUT----/ oracle/oradata/orcl/users01.dbf USERS YES/oracle/oradata/orcl/undotbs01. Dbf UNDOTBS1 YES/oracle/oradata/orcl/sysaux01.dbf SYSAUX YES/oracle/oradata/orcl/system01.dbf SYSTEM YES/oracle/oradata/orcl/example01.dbf EXAMPLE YES/oracle/oradata/orcl/mickey01.dbf MICKEY NO/oracle/oradata/orcl/wiley.dbf WILEY YES-- has been changed to auto-grow. / oracle/oradata/orcl/wiley2.dbf WILEY YES 8 rows selected.

Use the ALTER command to adjust manually

If a data file originally used 100m, it is feasible to resize it to 200m now, but if a data file has already used 300m and then resize it to 200m, it will fail.

/ * demonstrate manual adjustment of tablespaces using the ALTER command * /

= query existing tablespace information =

SQL > select file_name, tablespace_name,bytes from dba_data_files

FILE_NAME TABLESPACE_NAME BYTES----/ oracle/oradata/orcl/users01.dbf USERS 5242880/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 57671680/oracle/oradata/orcl/sysaux01.dbf SYSAUX 587202560/oracle/oradata/orcl/system01.dbf SYSTEM 713031680/oracle/oradata/orcl/example01.dbf EXAMPLE 104857600/oracle/oradata/orcl/mickey01.dbf MICKEY 20971520/oracle/oradata/orcl/wiley. Dbf WILEY 20971520/oracle/oradata/orcl/wiley2.dbf WILEY 20971520 8 rows selected.

Note that the data file information for the temporary tablespace is stored in the dba_temp_files table.

= adjust the data file wiley2 of tablespace wiley to 10m =

SQL > alter database datafile

2'/ oracle/oradata/orcl/wiley2.dbf'

3 resize 10M

Database altered.

SQL > select file_name, tablespace_name,bytes from dba_data_files

FILE_NAME TABLESPACE_NAME BYTES----/ oracle/oradata/orcl/users01.dbf USERS 5242880/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 57671680/oracle/oradata/orcl/sysaux01.dbf SYSAUX 671088640/oracle/oradata/orcl/system01.dbf SYSTEM 723517440/oracle/oradata/orcl/example01.dbf EXAMPLE 104857600/oracle/oradata/orcl/wiley.dbf WILEY 20971520/oracle/oradata/orcl/wiley2 .dbf WILEY 10485760-the size of the data file wiley2 has been resized to 10m by 7 rows selected.

Note that reducing the size of data file will fail in many cases.

SQL > alter database datafile

2'/ oracle/oradata/orcl/example01.dbf'

3 resize 10M

Alter database datafile*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZEvalue-- prompts you to adjust the size less than the existing file size.

Third, expand the tablespace by adding data files.

In general, a disk consisting of 10 10G data file performs better than a 100G data file, because 100G disks cannot achieve concurrency.

ALTER TABLESPACE is used to add data file, not ALTER DATABASE. It is recommended to query online documentation before actual operation.

Fourth, directly add a new table space.

Before adjusting the size of the tablespace, you can query the size of the tablespace. Tablespace is actually a logical concept within the database, and the operating system cannot view its body through specific commands, but it can count its percentage of usage by querying the data dictionary.

Query the relevant chapters of the data dictionary in the online document REFERENCE to get the information about the tablespace.

The method of creating tablespaces directly has been described earlier and will not be repeated here.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.