Code and actual operations for Oracle to change the tablespace size

This article uses the actual operating code of Oracle to change the tablespace size to introduce the actual operation steps of Oracle to change the tablespace size. At the same time, this article also gives a detailed introduction to the specific operation steps of the user space limit, I hope you will gain some benefits after visiting this article.

 
 

  
  
sys@TEST1>alter tablespace test_16k offline; 
 
 

The tablespace has been changed.

 
 

  
  
sys@TEST1>alter database datafile '
d:\oracle\product\10.2.0\oradata\test1\test6k.dbf' 
autoextend on next 50m maxsize 200m; 
 
 

The database has been changed.

 
 

  
  
sys@TEST1>alter database datafile '
d:\oracle\product\10.2.0\oradata\test1\test.dbf' resize 10m; 
 
 

The database has been changed.

 
 

  
  
sys@TEST1>alter user mxl quota 200m on test_16k; 
 
 

The user has changed.

Summary:

1. Oracle changes a large tablespace, but the modified datafile is not tablespace.

2. Do not make low-level spelling mistakes.

3. error messages are important. Do not take them for granted.

Appendix:

Iii. Suggestions on Disk Quota Management.

1. When creating a tablespace object, the following error message is displayed: "The tablespace space exceeds the limit. This situation is generally caused by many situations. When I encounter this problem, the general solution is to first check the user's disk quota to see if it meets the space requirements.

If the user does not have disk quota management, or the disk quota meets the specified requirements, we recommend that you check the tablespace size to see if it is caused by insufficient tablespace. If the problem persists in the first two steps, check the table space management method. If the data dictionary management mode is used, change it to the local management mode to solve the problem.

2. If the database contains data from different offices or branches, I suggest you change the tablespace size in Oracle to a disk limit. This effectively prevents the entire database from crashing due to insufficient hard disk space. If you manage the disk quota for a user, it is only a failure in the database operation of a certain office (a user. This is also an effective way to improve the availability of Oracle databases.

The table space and the table space quota are different.

It is estimated that oracle will allocate a default tablespace quota to each user by default. The specific proportion is to be queried, but this proportion must be far less than 100%.

Therefore, a tablespace allocated with MB may not be able to store MB of data.

The solution is as follows:

View the quota of user tablespace

 
 

  
  
select * from user_ts_quotas; 
 
 

This is the max_bytes field.

-1 indicates that there is no limit.

Do not control the table space quota for users:

 
 

  
  
Grant unlimited tablespace to *** (User );
 
 

This method is global.

Or

 
 

  
  
Alter user *** (user Name) quota unlimited on *** (tablespace );
 
 

This method is applicable to specific tablespaces.

It can be allocated or recycled:

 
 

  
  
Revoke unlimited tablespace from *** (User)
 
 

Or

 
 

  
  
alter user *** quota 0 on ***  
 
 

The above content introduces how to change the tablespace size in Oracle. I hope you will get some benefits.