ORACLE用户表空间动态切换

ORACLE数据库默认使用表空间USERS存储数据和索引，但是在企业级的数据库设计方案中，建议使用独立的表空间来存储数据和索引。如果创建的时候没有注意这一点，怎么来补救呢？下面我们以实际例子来说明：

--用户名：ZHQ

--当前表空间:USERS

--新的表空间:ZHQ_DB

--新的索引表空间：ZHQ_DB_IDX

--新的CLOB字段数据表空间：ZHQ_DB_CLOB

整个切换操作都以具有DBA权限的用户来执行。首先我们可以用以下SQL查看下表空间的使用情况：

SELECT *

FROM dba_extents

WHERE OWNER = 'ZHQ'

and tablespace_name = 'USERS';

实际操作步骤如下：

--1.自动生成表移动表空间的SQL，表空间从USERS移动到ZHQ_DB

SELECT DISTINCT 'alter table '||OWNER||'.'|| segment_name ||

' move tablespace ZHQ_DB; '

FROM dba_extents

WHERE OWNER = 'ZHQ'

and tablespace_name = 'USERS'

AND segment_type = 'TABLE';

--复制查询结果作为SQL执行

alter table ZHQ.T_BRM_EXCEPT_RULE move tablespace ZHQ_DB;

alter table ZHQ.WORK_STATE move tablespace ZHQ_DB;

alter table ZHQ.T_BRM_BOM_INFO move tablespace ZHQ_DB;

alter table ZHQ.T_BRM_GROUP_CODE move tablespace ZHQ_DB;

--2.自动生成索引移动表空间的SQL，表空间从USERS移动到ZHQ_DB_IDX

SELECT DISTINCT 'ALTER INDEX '||OWNER||'.'|| segment_name ||' REBUILD TABLESPACE ZHQ_DB_IDX; '

FROM dba_extents

WHERE OWNER = 'ZHQ'

and tablespace_name = 'USERS'

AND segment_type = 'INDEX';

--复制查询结果作为SQL执行

ALTER INDEX ZHQ.TBER_PK REBUILD TABLESPACE ZHQ_DB_IDX;

ALTER INDEX ZHQ.TBGC_PK REBUILD TABLESPACE ZHQ_DB_IDX;

--3.自动生成LOBSEGMENT移动表空间的SQL，表空间从USERS移动到ZHQ_DB_CLOB

select 'alter table ' || l.owner || '.' || l.table_name || ' move lob(' || l.column_name || ') store as (tablespace ZHQ_DB_CLOB);'

from all_lobs l

where segment_name IN (SELECT DISTINCT SEGMENT_NAME

FROM dba_extents

WHERE OWNER = 'ZHQ'

and tablespace_name = 'USERS'

AND segment_type = 'LOBSEGMENT');

--复制查询结果作为SQL执行

alter table ZHQ.T_BRM_UW_PROCESS_LOG_XML move lob(IN_XML) store as (tablespace ZHQ_DB_CLOB);

alter table ZHQ.T_BRM_UW_PROCESS_LOG_XML move lob(OUT_XML) store as (tablespace ZHQ_DB_CLOB);

alter table ZHQ.T_BRM_UW_PROCESS_LOG_XML move lob(EXCEPTION) store as (tablespace ZHQ_DB_CLOB);

alter table ZHQ.T_BRM_UW_PROCESS_LOG_XML move lob(XOM_LOG) store as (tablespace ZHQ_DB_CLOB);

表空间移动常见问题

--1.报空间没有权限,需要执行下面SQL

ALTER USER "ZHQ" QUOTA UNLIMITED ON "ZHQ_DB_IDX";

--2.索引无效导致执行出错

alter index 索引名称 rebuild;