同事在使用数据泵导入dmp文件时,抛出了一个异常
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment CC_CK.SYS_LOB0000089385C00045$$ by 128 in tablespace USERS
USERS表空间无法扩展了.
事实上,我们的数据不应该存放在默认的表空间内。
概述
USERS表空间也就是默认用户表空间。
在创建一个用户并没有指定此用户使用表空间时,该用户所有信息都会放入到users表空间中。
查看默认用户表空间
SQL>select * from database_properties where property_name like '%DEF%';
创建表空间
SQL> create tablespace TAB_XGJ datafile '/oradata/datafile/tab_xgj01.dbf' size 1024m
autoextend on next 2000M
maxsize unlimited
extent management local autoallocate
segment space management auto ;
各种属性根据实际情况取舍。
指定单个用户默认表空间
建立用户时直接指定
SQL> create user xgj identified by xgj default tablespace tab_xgj;
或者
SQL> create user xgj identified by xgj ;
SQL> alter user xgj default tablespace tab_xgj;
如果需要指定临时表空间
SQL>create user xgj identified by xgj
default tablespace tab_xgj
temporary tablespace temp;
查看所有用户的默认表空间
SQL> select username,default_tablespace from dba_users;
创建用户实例
oracle@entel2:[/oracle]$sqlplus sys/system as sysdba;
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 7 12:46:58 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user cc_ck identified by xgj;
User created.
SQL> grant dba to cc_ck;
Grant succeeded.
SQL> GRANT CONNECT ,RESOURCE TO cc_ck;
Grant succeeded.
SQL> GRANT ALL PRIVILEGES TO cc_ck;
Grant succeeded.
SQL> alter user cc_ck default tablespace TAB_CC;
User altered.
或者在创建时指定TABLESPACE CREATE USER cc_CK IDENTIFIED BY smart
DEFAULT TABLESPACE TAB_CC;
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@entel2:[/oracle]$
附
查询表空间大小的SQL
--Oracle 表空间使用率
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;