1.创建测试表空间

SQL> create tablespace tbs01 logging datafile '/oradata/orcl/tbs01.dbf' size 100m autoextend on next 50m maxsize 2048m extent management local;

Tablespace created.

2 创建c##hbhe用户并指定表空间

SQL> create user c##hbhe identified by wwwwww default tablespace tbs01 temporary tablespace TEMP1 profile default;

User created.

3 给c##hbhe用户授予权限

SQL> grant connect,resource to c##hbhe;

Grant succeeded.

SQL> grant dba to c##hbhe;

Grant succeeded.

4.首先通过用户来获取迁移需要的表空间名称，注意，这里的C##HBHE必须大写http://www.cndba.cn/hbhe0316/article/4902

SQL> SELECT distinct ''''|| t.tablespace_name||''',' From dba_segments t WHERE t.owner in ('C##HBHE') and t.tablespace_name not in ('USERS','SYSTEM');

5.将第4步获取的表空间名称带入如下http://www.cndba.cn/hbhe0316/article/4902

SQL> SELECT 'CREATE TABLESPACE ' ||D.TABLESPACE_NAME|| ' datafile ''/oradata/orcl/' ||LOWER(D.TABLESPACE_NAME)||'01.dbf'''|| ' size '|| 
case when (D.total_gb - F.free_gb) > 30 then 30 else round(D.total_gb - F.free_gb + 2) end ||'G autoextend off  EXTENT MANAGEMENT LOCAL;' 
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) free_gb
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 2) total_gb
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
 AND D.TABLESPACE_NAME IN ('TBS01');
'CREATETABLESPACE'||D.TABLESPACE_NAME||'DATAFILE''/ORADATA/ORCL/'||LOWER(D.TABLE
--------------------------------------------------------------------------------
CREATE TABLESPACE TBS01 datafile '/oradata/orcl/tbs0101.dbf' size 2G autoextend
off  EXTENT MANAGEMENT LOCAL;

版权声明：本文为博主原创文章，未经博主允许不得转载。

Linux,oracle