Oracle 11g -- ORA-01691 表空间扩容

Oracle 表空间操作

表空间 – 名称 && 大小 查看

SQL> SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size   FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name;

TABLESPACE_NAME 		  TS_SIZE
------------------------------ ----------
TSP_OUTPADM			     7710
TSP_INPADM			      275
TSP_ORDADM			    31170
TSP_MEDADM			      700
UNDOTBS1			     2800
SYSAUX				      780
TSP_INSURANCE			    10750
TSP_ECONSTAT			       55
TSP_INTERFACE			       30
PDA_DATA			    23538
TSP_PHARMACY			    12920

TABLESPACE_NAME 		  TS_SIZE
------------------------------ ----------
TSP_ACCT			       10
TSP_EQUIPMENT			      140
USERS				     2928
TSP_TEMP			       45
TSP_MEDREC			     3430
TSP_COMPLEXSTAT 		     3150
SYSTEM				      770
TSP_BACK			       10
TSP_COMM			    23570
TSP_EXAM			     7170
TSP_SURGERY			      105

TABLESPACE_NAME 		  TS_SIZE
------------------------------ ----------
TSP_BLDBANK			       25
INDX				       41
TOOLS				    32767
TSP_LAB 			    14330
TSP_INPBILL			    33130
TSP_OUTPAOC			    25180
UNDOTBS2			    10469
RBS					5
TSP_OUTPBILL			    18360
TSP_WJJS			      303
TSP_CARADIGM			    30720

33 rows selected.

表空间 – 物理文件名称 && 大小 查看

SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;

查看指定表空间信息

SQL> SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) FROM dba_data_files WHERE tablespace_name='SYSTEM';

TS_NAME 	FILE_ID      	FILE_NAME                              	DB_SIZE
--------   ---------    	----------                            ----------
SYSTEM 		1  			/home/oracle/oradata/orcl/system01.dbf      770

修改指定表空间大小

SQL> alter tablespace SYSTEM add datafile '/home/oracle/oradata/orcl/system01_01.dbf' size 1024M autoextend on next 50M MAXSIZE UNLIMITED;

Tablespace altered.

SQL> SELECT tablespace_name as ts_name, file_id, file_name, round(bytes / (1024 * 1024), 0) as db_size FROM dba_data_files WHERE tablespace_name='SYSTEM';

TS_NAME 	FILE_ID      	FILE_NAME                              	DB_SIZE
--------   ---------    	----------                            ----------
SYSTEM 		1  			/home/oracle/oradata/orcl/system01.dbf      770
SYSTEM 		37 			/home/oracle/oradata/orcl/system01_01.dbf   1024

==================================================

Oracle 异常

ORA-01691: unable to extend lob segment SYSTEM.SYS_L0B0000148705C00002$$ by 8192 in tablespace TOOLS

登录 oracle 用户

[root@demo ~]# su - oracle
Last login: Fri Jul 12 09:47:20 CST 2019 on pts/0

通过 “sysdba” 登录 oracle

[oracle@demo ~]$ sqlplus / as sysdba

查看 – 所有表空间信息

SQL> select a.tablespace_name,a.bytes/1024/1024 "sum MB", (a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB", round (((a.bytes-b.bytes)/a.bytes)*100,2) "used%" from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(bytes) bytes,max (bytes) largest from dba_free_space group by tablespace_name)b where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc;

TABLESPACE_NAME 		   sum MB    used MB	free MB      used%
------------------------------ ---------- ---------- ---------- ----------
TOOLS				    32767 32717.9375	49.0625      99.85
TSP_OUTPAOC			    25180   24112.25	1067.75      95.76
TSP_PHARMACY			    12920 12301.0625   618.9375      95.21
PDA_DATA			    23538 22405.4375  1132.5625      95.19
TSP_ORDADM			    31170      29669	   1501      95.18
TSP_OUTPBILL			    18360 17458.5625   901.4375      95.09
TSP_LAB 			    14330   13600.25	 729.75      94.91
TSP_INSURANCE			    10750   10187.75	 562.25      94.77
TSP_MEDADM			      700   662.8125	37.1875      94.69
TSP_EXAM			     7170  6787.4375   382.5625      94.66
TSP_OUTPADM			     7710	7286	    424       94.5

TABLESPACE_NAME 		   sum MB    used MB	free MB      used%
------------------------------ ---------- ---------- ---------- ----------
TSP_INPBILL			    33130  31277.625   1852.375      94.41
TSP_COMPLEXSTAT 		     3150   2972.125	177.875      94.35
TSP_MEDREC			     3430  3224.3125   205.6875 	94
TSP_SURGERY			      105     98.625	  6.375      93.93
USERS				   2927.5   2741.625	185.875      93.65
SYSAUX				      780    723.875	 56.125       92.8
TSP_COMM			    23570 19885.6875  3684.3125      84.37
TSP_WJJS			      303   252.9375	50.0625      83.48
TSP_INPADM			      275    224.375	 50.625      81.59
TSP_ECONSTAT			       55     42.125	 12.875      76.59
TSP_TEMP			       45    34.1875	10.8125      75.97

TABLESPACE_NAME 		   sum MB    used MB	free MB      used%
------------------------------ ---------- ---------- ---------- ----------
TSP_EQUIPMENT			      140    87.6875	52.3125      62.63
TSP_BLDBANK			       25     15.625	  9.375       62.5
TSP_INTERFACE			       30     13.375	 16.625      44.58
SYSTEM				     1794     770.75	1023.25      42.96
TSP_CARADIGM			    30720  6943.8125 23776.1875       22.6
UNDOTBS1			     2800	 584	   2216      20.86
RBS					5	   1	      4 	20
INDX				    41.25	   7	  34.25      16.97
TSP_ACCT			       10      1.125	  8.875      11.25
TSP_BACK			       10	   1	      9 	10
UNDOTBS2			    10469	2.25   10466.75        .02

33 rows selected.

查看 TOOLS 表空间大小

SQL> SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) FROM dba_data_files WHERE tablespace_name='TOOLS';

为 TOOLS 表空间新增数据文件

SQL> alter tablespace TOOLS add datafile '/data/oracle/oradata/orcl/tools01_01.dbf' size 1024M autoextend on next 50M MAXSIZE UNLIMITED;

Tablespace altered.

参考

• ORA-01691：Lob 段无法通过8192（在表空间XXX中）扩展的解决方案
  
• ORA-01691: unable to extend lob segment 问题解决