ALTER TABLE 表名称 MOVE TABLESPACE 新表空间 LOB (表中类型为lob的字段名) STORE AS lobsegment (TABLESPACE 新表空间 );
2、查询当前用户所有非LOB索引,并把表空间为“JCMS24”的索引,更新为:JCMS。
DECLARE i_count INT := 0; CURSOR c_mysql IS SELECT 'alter index JCMS24.' || index_name || ' rebuild tablespace JCMS' mysql FROM dba_INDEXES WHERE owner = 'JCMS24' and tablespace_name = 'JCMS24' and index_type <>'LOB'; BEGIN FOR r_mysql IN c_mysql LOOP DBMS_OUTPUT.put_line(r_mysql.mysql); EXECUTE IMMEDIATE r_mysql.mysql; i_count := i_count + 1; END LOOP; DBMS_OUTPUT.put_line('i_count: ' || i_count); END;
DECLARE i_count INT := 0; CURSOR c_mysql IS SELECT 'alter index JCMS24.' || index_name || ' rebuild tablespace JCMS' mysql FROM dba_INDEXES WHERE owner = 'JCMS24' and tablespace_name = 'JCMS24' and index_type = 'LOB'; BEGIN FOR r_mysql IN c_mysql LOOP DBMS_OUTPUT.put_line(r_mysql.mysql); EXECUTE IMMEDIATE r_mysql.mysql; i_count := i_count + 1; END LOOP; DBMS_OUTPUT.put_line('i_count: ' || i_count); END;