关于修改用户，表的 tablespace_name走的一点弯路

 

看tablespace 建在那个文件中：

SQL> select file_name,tablespace_name,bytes,status,online_status from dba_data_files;
FILE_NAME                    TABLESPACE_NA      BYTES STATUS    ONLINE_
---------------------------- ------------- ---------- --------- -------
/u02/ezhou/users01.dbf       USERS           66846720 AVAILABLE ONLINE
/u02/ezhou/sysaux01.dbf      SYSAUX         272629760 AVAILABLE ONLINE
/u02/ezhou/undotbs01.dbf     UNDOTBS1       298844160 AVAILABLE ONLINE
/u02/ezhou/system01.dbf      SYSTEM         513802240 AVAILABLE SYSTEM
/u02/ezhou/example01.dbf     EXAMPLE        104857600 AVAILABLE ONLINE
/u02/ezhou/test01.dbf        TB_TEST         10485760 AVAILABLE ONLINE
/u02/ezhou/example02.dbf     EXAMrPLE         10485760 AVAILABLE ONLINE

7 rows selected.

看table 属于那个tablespace:
SQL> select table_name, tablespace_name from user_tables where table_name = 'TEST';

TABLE_NAME                     TABLESPACE_NA
------------------------------ -------------
TEST                           USERS

Elapsed: 00:00:00.01

下面把 table: test 建在TB_TEST 下：

SQL> show user;     
USER is "SCOTT"
SQL> alter table test move tablespace TB_TEST;
alter table test move tablespace TB_TEST
            *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TB_TEST

怀疑是temp 的问题：

SQL> select name,(bytes)/1024/1024 from v$tempfile;

NAME                                     (BYTES)/1024/1024
---------------------------------------- -----------------
/u02/ezhou/temp01.dbf                                   26

Elapsed: 00:00:00.01
SQL> show user;
USER is "SYS"

看到temp 的大小是26M,下面来extend 一下：
首先，看看那些user 用到这个临时表空间:

SQL>  select username,account_status, TEMPORARY_TABLESPACE from dba_userS where account_status = 'OPEN';

USERNAME       ACCOUNT_STATUS                   TEMPORARY_TABLESPACE
-------------- -------------------------------- ------------------------------
MGMT_VIEW      OPEN                             TEMP
SYS            OPEN                             TEMP
SYSTEM         OPEN                             TEMP
DBSNMP         OPEN                             TEMP
SYSMAN         OPEN                             TEMP
SHENG          OPEN                             TEMP
SCOTT          OPEN                             TEMP

7 rows selected.

Elapsed: 00:00:00.04

SQL> ALTER DATABASE TEMPFILE '/u02/ezhou/temp01.dbf' resize 40M;

Database altered.

Elapsed: 00:00:00.05

一般情况：应该是建立一个新的，然后把新建立的表空间设置为默认的，然后删除旧的。
上面的命令真强大。

SQL> select name,(bytes)/1024/1024 from v$tempfile;

NAME                                     (BYTES)/1024/1024
---------------------------------------- -----------------
/u02/ezhou/temp01.dbf                                   40

Elapsed: 00:00:00.01

下面把用户scott 的tablespace 改成：TB_TEST:
SQL> select table_name, owner from dba_tables where owner like 'SCOTT' and table_name = 'TEST';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
TEST                           SCOTT

Elapsed: 00:00:00.08

SQL>alter user SCOTT TABLESPACE DEFAULT TB_TEST;

SQL> create table t (id number, name varchar2(10))
  2  ;

Table created.

Elapsed: 00:00:00.41

Elapsed: 00:00:01.67
SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NA
------------------------------ -------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS
T                              TB_TEST
TEST                           USERS

 

SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NA
------------------------------ -------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS
T                              TB_TEST
TEST                           USERS

当我发现新建的table tablespace 变成tb_test, 为啥其他的没有变呢？

当我向 t 表中插入 test 的数据也报错：
ERROR at line 1:
ORA-01652: unable to extend temp segment by 8 in tablespace TB_TEST。

我check 一下tb_test 的大小：只有20M，

我又查了 scott.test 的大小：

SQL> select owner, segment_name,tablespace_name,(bytes)/1024/1024 from dba_segments where owner like 'SCOTT';

OWNER      SEGMENT_NA TABLESPACE_NAME                (BYTES)/1024/1024
---------- ---------- ------------------------------ -----------------
SCOTT      PK_DEPT    USERS                                      .0625
SCOTT      DEPT       USERS                                      .0625
SCOTT      EMP        USERS                                      .0625
SCOTT      PK_EMP     USERS                                      .0625
SCOTT      BONUS      USERS                                      .0625
SCOTT      SALGRADE   USERS                                      .0625
SCOTT      T          TB_TEST                                        9
SCOTT      TEST       USERS                                         60

原因找到：tb_test 设置太小了：

下面扩大点：
alter database datafile 'u02/ezhou/test01.dbf' resize 100m;

Database altered.

Elapsed: 00:00:06.32
SQL> alter table scott.test move tablespace TB_test;

Table altered.

Elapsed: 00:00:28.56
SQL>

SQL> l
  1* select table_name, tablespace_name from user_tables
SQL> conn scott/oracle
Connected.
SQL> /

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS
T                              TB_TEST
TEST                           TB_TEST

6 rows selected.

发现已经改变。
success.