Oracle Tablespace Solution Encyclopedia ____oracle

Oracle table space is not enough, there are generally two reasons: first, the original table space is too small, not from the growth; second, the table space has grown, and the table space is large enough, for these two reasons respectively have their own solutions.

"Check Cause"

1, view the table in that table space   select tablespace_name,table_name from User_talbes where table_name= ' test '; 2, get user's default table space   select  username,  default_tablespace  from  dba_users where username= ' mxh '; 3, view all files in tablespace   select * from Dba_data_files where tablespace_name= ' users '; 4, view tablespace usage:  select TBS table space name,   sum (totalm) Total size m,  sum (USEDM) has used space M,  sum (REMAINEDM) remaining space m,   sum (USEDM)/sum (Totalm) *100 percent used,   sum (REMAINEDM)/sum (Totalm) *100 remaining percent   from (   select b.file_id id,  b.tablespace_name tbs,  b.file_name name,  b.bytes /1024/1024 totalm,   (B.bytes-sum (NVL (a.bytes,0))/1024/1024 usedm,  sum (NVL (a.bytes,0)/1024/ 1024) remainedm,  sum (NVL (a.bytes,0)/(b.bytes) *100),   (SUM (NVL (a.bytes,0))/(b.bytes) * ))   from dba_free_space a,dba_data_files b  where a.file_id = b.file_id &nbSp GROUP by B.tablespace_name,b.file_name,b.file_id,b.bytes  order by b.tablespace_name  )    group by tbs 

"Solution-cause one"

Just set the table space to large enough and set it to grow from.

1, expand the table space alterdatabase datafile ' D:\ORACLE\PRODUCT\ORADATA\TEST\USERS01. DBF ' resize 50m;

Note: 50m, is the size of the table space, can be increased according to actual needs, but the maximum must not exceed 32G

2, automatic growth alterdatabase datafile ' D:\ORACLE\PRODUCT\ORADATA\TEST\USERS01. DBF ' autoextend onnext 50m maxsize 500m;

"Solution-reason Two"

Because the data files in the tablespace are already large enough (up to 32G), it is not possible to simply increase the size of the table space.

This time, we can increase the table space data file, so that the size of the table space into 64G.

ALTER tablespace AAA

ADD datafile ' E:\APP\ORACLE11GR2\ORADATA\ORCL\aaa_DATA02. DBF '

SIZE 32767M;