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用户授予权限https://www.cndba.cn/hbhe0316/article/4902https://www.cndba.cn/hbhe0316/article/4902https://www.cndba.cn/hbhe0316/article/4902
https://www.cndba.cn/hbhe0316/article/4902
https://www.cndba.cn/hbhe0316/article/4902
https://www.cndba.cn/hbhe0316/article/4902
https://www.cndba.cn/hbhe0316/article/4902
https://www.cndba.cn/hbhe0316/article/4902
SQL> grant connect,resource to c##hbhe;
Grant succeeded.
SQL> grant dba to c##hbhe;
Grant succeeded.
4.首先通过用户来获取迁移需要的表空间名称,注意,这里的C##HBHE必须大写https://www.cndba.cn/hbhe0316/article/4902
https://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步获取的表空间名称带入如下
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