朋友生产数据库在向特定的一张表插入数据时报超出表空间data的空间限额如是执行下查看用户所用的表空间配额信息，查看用户所使用表空间的配额发现没有限制,因为max_bytes为 -1

SQL>  select * from dba_ts_quotas where username='data';
TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
data                           data                           8825732464         -1  107735992         -1 NO
 1 rows selected
SQL>
SQL> select
  2    fs.tablespace_name            "Tablespace",
  3    (df.totalspace-fs.freespace)  "Used MB",
  4    fs.freespace                  "Free MB",
  5    df.totalspace                 "Total MB",
  6    round(100*(fs.freespace/df.totalspace)) "Pct. Free"
  7  from
  8    (select tablespace_name,round(sum(bytes)/1024/1024) TotalSpace
  9    from dba_data_files group by  tablespace_name) df,
 10    (select tablespace_name,round(sum(bytes)/1024/1024) FreeSpace
 11    from dba_free_space group by tablespace_name) fs
 12  where df.tablespace_name=fs.tablespace_name;
Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
SYSTEM                               7207       3033      10240         30
TEST                                 6790      34170      40960         83
USERS                                 173      25427      25600         99
UNDOTBS2                              227      24013      24240         99
DATA                               990119     176281    1166400         15
SYSAUX                               3925       1195       5120         23
UNDOTBS1                            12898      28062      40960         69
7 rows selected


查看表lv_data的依赖对象

SQL> select NAME,TYPE from dba_dependencies where REFERENCED_NAME='LV_DATA';
NAME                           TYPE
------------------------------ ------------------
LV_DATA                        VIEW
FC_UPDATE_CORPFUND             PROCEDURE
FC_UPDATE_MY                   PROCEDURE
FC_UPDATE_KY                   PROCEDURE
FC_UPDATE_FACTPAY              PROCEDURE
FC_UPDATE_CALCPAY              PROCEDURE
FC_UPDATE_KY                   PROCEDURE
......
LV_DATA                        SYNONYM
LV_DATA                        VIEW
LV_DATA                        SYNONYM
LV_DATA                        SYNONYM
139 rows selected


查看所有依赖对象的所有者

SQL> select  distinct owner from dba_objects where OBJECT_NAME in(select NAME from dba_dependencies where REFERENCED_NAME='LV_DATA');
OWNER
------------------------------
SY
SY_BK
WEB
CX
DATA
OLD
TEST
XC
CZ
OSY
BACKUP
TJ
12 rows selected


对所有依赖对象所有者授权可以无限制使用表空间

SQL> grant unlimited tablespace to OSY;
Grant succeeded
SQL> grant unlimited tablespace to SBK;
Grant succeeded
SQL> grant unlimited tablespace to WEB;
Grant succeeded
SQL> grant unlimited tablespace to CX;
Grant succeeded
SQL> grant unlimited tablespace to DATA;
Grant succeeded
SQL> grant unlimited tablespace to OLD;
Grant succeeded
SQL> grant unlimited tablespace to TEST;
Grant succeeded
SQL> grant unlimited tablespace to XC;
Grant succeeded
SQL> grant unlimited tablespace to CZ;
Grant succeeded
SQL> grant unlimited tablespace to SY;
Grant succeeded
SQL> grant unlimited tablespace to BACKUP;
Grant succeeded
SQL> grant unlimited tablespace to TJ;
Grant succeeded


再向表lv_data插入数据时恢复正常




                                                            

                    

                                                    来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-2646539/，如需转载，请注明出处，否则将追究法律责任。
                                            

                
转载于:http://blog.itpub.net/26015009/viewspace-2646539/

 

查询空间使用率：

SELECT A.TABLESPACE_NAME "表空间名",

       TOTAL / 1024 / 1024 "表空间大小单位M",

       FREE / 1024 / 1024 "表空间剩余大小单位M",

       (TOTAL - FREE) / 1024 / 1024 "表空间使用大小单位M",

       ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率   [[%]]"FROM

       (SELECT TABLESPACE_NAME, SUM(BYTES) FREE

          FROM DBA_FREE_SPACE

         GROUP BY TABLESPACE_NAME) A,

       (SELECT TABLESPACE_NAME,

               SUM(BYTES) TOTAL

          FROM DBA_DATA_FILES

         GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

 

查看空间路径以及空间名称

select  * from dba_data_files;

 

已创建空间达到最大限度则停止扩容，下面是扩容语句，创建新文件，并且无限扩容：

alter tablespace SHR_DATA add datafile 'G:\ORACLESPACE\SHR\SH_DATA3.DBF' 

size 500M  AUTOEXTEND on next 100m  maxsize unlimited ;

 

1、名词解释

表空间（Tablespace）：ORACLE数据库被划分成称作为表空间 [1]  的逻辑区域——形成ORACLE数据库的逻辑结构。一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等；表空间是一个虚拟的概念可以无限大，但是需要由数据文件作为载体；每个ORACLE数据库均有SYSTEM表空间。

段（Segment）：段是指占用数据文件空间的通称，或数据库对象使用的空间的集合；段可以有表段、索引段、回滚段、临时段和高速缓存段等。

区间（Extent）：分配给对象（如表）的任何连续块叫区间；区间也叫扩展，因为当它用完已经分配的区间后，再有新的记录插入就必须在分配新的区间（即扩展一些块）；一旦区间分配给某个对象（表、索引及簇），则该区间就不能再分配给其它的对象。

Schema：元数据的一个抽象集合。

比较形象的说法：我们可以把Database看作是一个大仓库。仓库分了很多很多的房间，Schema就是其中的房间。一个Schema代表一个房间。Table可以看作是每个Schema中的床，Table（床）被放入每个房间中，不能放置在房间之外，那岂不是晚上睡觉无家可归了。然后床上可以放置很多物品，就好比 Table上可以放置很多列和行一样，数据库中存储数据的基本单元是Table。现实中每个仓库放置物品的基本单位就是床， User就是每个Schema的主人（所以Schema包含的是Object，而不是User），user和schema是一一对应的，每个user在没有特别指定下只能使用自己schema（房间）的东西。如果一个user想使用其他schema（房间）的东西，那就要看那个schema（房间）的user（主人）有没有给你这个权限了，或者看这个仓库的老大（DBA）有没有给你这个权限了。换句话说，如果你是某个仓库的主人，那么这个仓库的使用权和仓库中的所有东西都是你的（包括房间），你有完全的操作权，可以扔掉不用的东西从每个房间，也可以放置一些有用的东西到某一个房间，你还可以给每个User分配具体的权限，也就是他到某一个房间能做些什么，是只能看（Read-Only），还是可以像主人一样有所有的控制权（R/W），这个就要看这个User所对应的角色Role了。



2、表空间释放语句

select sum(a.BYTES)/1024/1024/1024 from dba_data_files a where a.TABLESPACE_NAME='xxx_'tablespace_name'
			
查询表空间大小
		
select sum(a.BYTES)/1024/1024/1024 from dba_free_space a where a.TABLESPACE_NAME='xxx_'tablespace_name'
			
查询空闲表空间大小
		
truncate table xxx_table_name drop storage
			
清除表中数据释放表空间
		
 

Oracle表空间数据文件大小的限制
由于Oracle的Rowid使用22位来代表数据块号，因此Oracle表空间数据文件每个数据文件最多只能包含2^22个数据块。
也因此数据库表空间的数据文件不是无限增长的，例如：
在数据块为8k的情况下，单个数据文件的最大容量为8K*2^22 = 32G
同理，
数据块为2K，数据文件最大约8G
数据块为32，数据文件最大约16*8G
故若达到这个最大容量之后，则即便是设置了自增长，也不是无限自增长，此时则需要为这个表空间添加数据文件
当表空间不足时
（1）设置文件的自动增长
首先选择设置自增长，sql语句：Alter tablespace 表空间名 adddatafile ‘数据文件存放路径‘ autoextend on next 每次增加的大小 maxsize 数据文件大小的最大值
（2）增加数据文件
在自增长失灵了之后，需要增加数据文件，sql语句：Alter tablespace 表空间名 adddatafile ‘数据文件存放的路径’ size 数据文件大小M autoextend on next 每次自增长大小M Maxsize UNLIMITED；（后半部分为设置自增长）
这里放个例子：
alter tablespace SDE add datafile ‘E:\app\EmmaXu\product\11.1.0\db_1\database\SDE_1.dbf’ size 400Mautoextend off

修改某数据文件为不限制大小

ALTER DATABASE DATAFILE ‘D:\oracle_data\xxx.DBF’ AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
增加新的数据文件

alert tablespace 表空间名 add datafile ‘D:\oracle_data\xxx.DBF’ size 1000m autoextend on next 1000m maxsize UNLIMITED;
删除指定的表空间文件
ALTER TABLESPACE 表空间名  DROP DATAFILE  ‘D:\oracle_data\xxx.DBF’;
如何查询oracle用户对应 表空间及对应的DBF文件
1.oracle查询用户以及用户对应的表空间
select username,default_tablespace from dba_users;
2.Oracle,查询 表空间对应的数据文件，用户和表空间对应关系
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;