oracle| Skills | data | Database in an Oracle database, DBAs can make possible adjustment decisions by observing a certain table or view to see how the current space is being used.
I. Free space in the table space
By observing the free space of the table space, it can be used to determine whether the space allocated to a table space is too much or not enough. Take a look at the following statement
SQL > select a.file_id "Fileno", A.tablespace_name
"Tablespace_name",
2 a.bytes "bytes", A.bytes-sum (NVL (b.bytes,0)) "Used",
3 sum (NVL (b.bytes,0)) "Free",
4 sum (NVL (b.bytes,0))/a.bytes*100 "%free"
5 from Dba_data_files A, dba_free_space b
6 where a.file_id=b.file_id (+)
7 Group BY A.tablespace_name,
8 a.file_id,a.bytes ORDER by A.tablespace_name;
Can see that in the Fileno 12 table space RBS, only 0.19% of the allocated space is not used, this proportion is too small, and in the system and temp table space, up to 80% of the space is not utilized, for production databases, this table space is somewhat high.
With regard to the management of free space, there are some suggestions as follows:
Using the export and import commands to unload and load tablespaces frees up a lot of space to ease the need to add additional data files.
If the proportion of free space in a table space that contains a table with high insert (insert) and update (update) activity drops below 15%, add more space to the table space.
For a table space that is basically static table data, if you have more than 20% free space, you can consider reducing the amount of file space that is allocated to it.
It is more difficult to reduce the amount of space in the system table space, because the database is rebuilt.
The expansion of two tables and indexes
A. In order to prevent the table or index from being excessively extended and to adjust the database in a timely manner, users should always observe the objects concerned.
We can assume that a table or index with an extended region greater than 5 is overly extensible (overextended). Take a look at the following statement:
By observing, the DBA can discover the problem in time and handle it accordingly.
We can use export to unload the table, then delete the table, and then use the Import command to load the table so that the discontinuous areas can be merged into a contiguous space.
B. If the user wants to optimize the table's space settings, for example, you need to change the initial parameter of the table EMP, use the following method:
1. Use the Indexfile parameter when performing IMP commands when the EMP table is unloaded and deleted:
IMP Userid=scott/tiger file=emp.dmp indexfile=emp.sql Oracle writes table and index creation information to the specified file, rather than writing the data back.
2. Open Emp.sql File:
REM CREATE TABLE "SCOTT". " EMP "(" EMPNO "
Number (4, 0), "ename"
REM VARCHAR2 (Ten), "JOB" VARCHAR2 (9),
"MGR" number (4, 0), "HireDate" DATE,
REM "SAL" number (7, 2), "COMM" number
(7, 2), "DEPTNO" number (2, 0))
REM PCTFREE pctused Initrans 1
Maxtrans 255 LOGGING STORAGE (INITIAL
REM 10240 NEXT 10240 minextents 1 maxextents
121 Pctincrease Freelists
REM 1 freelist GROUPS 1 buffer_pool DEFAULT)
Tablespace "User_data";
Rem... Rows
Edit it, remove the "REM" information, find the initial parameter, and change it as needed.
3. Perform emp.sql in Sql*plus.
4. Loading data:
MP Userid=scott/tiger Ignore=y File=emp.dmp
It is important to note that the Ignore parameter must be set to Y.
C. You can use the following statement to observe the maximum expansion of the table or index distance, "Unuse" for the maximum extension of the distance, in the User_extents table, the extent_id is from 0 to record the number.
SQL >select a.table_name "table_name", max
(a.max_extents) "Maxextents",
2 Max (b.extent_id) +1 "in use", max
(a.max_extents)-(Max (b.extent_id) +1) "Unuse"
3 from User_tables A, user_extents b
4where A.table_name=b.segment_name
5 GROUP BY A.table_name Order by 4;
Ten rows selected.
We can estimate the real number of adjacent free spaces by the result of the command. For each row, with the starting Quick ID (block_id) plus the number of free blocks (BLOCKS), if it is equal to the Block ID (block_id) for the next line, the two lines are contiguous. As in the second and third lines of the example above, the 133719+126=133845, and the 1338456+130!=135275, have a contiguous space of 126+130=256 blocks starting from block_id 133719.
In the background of an Oracle database, System Monitor (Smon) periodically merges contiguous blocks of free space to get larger contiguous blocks. The DBA can use SQL commands to do this work:
Alter Tablespace Tablespace_name COALESCE;
Oracle Space management has an important effect on the performance of database, and its management method is worthy of our careful exploration.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
