1. Find out distinct schema name of Tablespace using the following Query.
SELECT DISTINCT OWNER
FROM ALL_TABLES
WHERE TABLESPACE_NAME = 'TBS_01';
2. Now Create the following Procedure. [ if your table size is big you can add the parallel and nologging clause (||' PARALLEL 8 NOLOGGING' ).
CREATE OR REPLACE PROCEDURE SP_TABLE_MOVE (
P_USER_NAME IN VARCHAR2,
P_CURRENT_TABLESPACE IN VARCHAR2,
P_NEW_TABLESPACE IN VARCHAR2)
IS
V_ALTER_SCRIPTS_TABLE VARCHAR2 (3000);
V_INDEX_NAME VARCHAR2 (100);
V_IDX_SCRIPTS VARCHAR2 (3000);
BEGIN
FOR IDX_TABLE
IN (SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM ALL_TABLES
WHERE OWNER = P_USER_NAME
AND TABLESPACE_NAME = P_CURRENT_TABLESPACE
AND TABLE_NAME = P_TABLE_NAME)
LOOP
V_ALTER_SCRIPTS_TABLE :=
'ALTER TABLE '
|| IDX_TABLE.OWNER
|| '.'
|| IDX_TABLE.TABLE_NAME
|| ' MOVE TABLESPACE '
|| P_NEW_TABLESPACE;
BEGIN
EXECUTE IMMEDIATE V_ALTER_SCRIPTS_TABLE;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (
'EXCEPTION IN MOVING TABLESPACE'
|| IDX_TABLE.TABLE_NAME
|| ' : '
|| SQLERRM);
END;
BEGIN
FOR IDX_INDEX
IN (SELECT OWNER, INDEX_NAME, TABLESPACE_NAME
FROM ALL_INDEXES
WHERE OWNER = P_USER_NAME
AND TABLE_NAME = IDX_TABLE.TABLE_NAME)
LOOP
V_IDX_SCRIPTS :=
'ALTER INDEX '
|| IDX_INDEX.OWNER
|| '.'
|| IDX_INDEX.INDEX_NAME
|| ' REBUILD TABLESPACE '
|| P_NEW_TABLESPACE;
BEGIN
EXECUTE IMMEDIATE V_IDX_SCRIPTS;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (
'EXCEPTION IN REBUILD INDEX'
|| IDX_INDEX.INDEX_NAME
|| ' : '
|| SQLERRM);
END;
END LOOP;
END;
END LOOP;
END;
/
3. Now execute the procedure to move table into different Tablespace.
EXEC SP_TABLE_MOVE('CBSUSR', 'TBS_01','TBS_02');
Sample Log:
SQL> conn /as sysdba
Connected.
SQL> CREATE OR REPLACE PROCEDURE SP_TABLE_MOVE (
P_USER_NAME IN VARCHAR2,
2 3 P_CURRENT_TABLESPACE IN VARCHAR2,
4 P_NEW_TABLESPACE IN VARCHAR2,
5 P_TABLE_NAME IN VARCHAR2)
6 IS
7 V_ALTER_SCRIPTS_TABLE VARCHAR2 (3000);
8 V_INDEX_NAME VARCHAR2 (100);
9 V_IDX_SCRIPTS VARCHAR2 (3000);
10 BEGIN
11 FOR IDX_TABLE
12 IN (SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
13 FROM ALL_TABLES
14 WHERE OWNER = P_USER_NAME
15 AND TABLESPACE_NAME = P_CURRENT_TABLESPACE
16 AND TABLE_NAME = P_TABLE_NAME)
17 LOOP
18 V_ALTER_SCRIPTS_TABLE :=
19 'ALTER TABLE '
20 || IDX_TABLE.OWNER
|| '.'
21 22 || IDX_TABLE.TABLE_NAME
|| ' MOVE TABLESPACE '
23 24 || P_NEW_TABLESPACE;
25
26 BEGIN
27 EXECUTE IMMEDIATE V_ALTER_SCRIPTS_TABLE;
28 EXCEPTION
29 WHEN OTHERS
30 THEN
31 DBMS_OUTPUT.PUT_LINE (
32 'EXCEPTION IN MOVING TABLESPACE'
33 || IDX_TABLE.TABLE_NAME
34 || ' : '
35 || SQLERRM);
36 END;
37
38 BEGIN
39 FOR IDX_INDEX
40 IN (SELECT OWNER, INDEX_NAME, TABLESPACE_NAME
41 FROM ALL_INDEXES
42 WHERE OWNER = P_USER_NAME
43 AND TABLE_NAME = IDX_TABLE.TABLE_NAME)
44 LOOP
45 V_IDX_SCRIPTS :=
46 'ALTER INDEX '
47 || IDX_INDEX.OWNER
48 || '.'
49 || IDX_INDEX.INDEX_NAME
50 || ' REBUILD TABLESPACE '
51 || P_NEW_TABLESPACE;
52
53 BEGIN
54 EXECUTE IMMEDIATE V_IDX_SCRIPTS;
55 EXCEPTION
56 WHEN OTHERS
57 THEN
58 DBMS_OUTPUT.PUT_LINE (
59 'EXCEPTION IN REBUILD INDEX'
60 || IDX_INDEX.INDEX_NAME
61 || ' : '
62 || SQLERRM);
63 END;
64 END LOOP;
65 END;
66 END LOOP;
67 END;
68 /
Procedure created.
SQL>
SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM ALL_TABLES
WHERE OWNER = 'CBSUSR' AND TABLE_NAME = 'ACCOUNTS' 2 3 ;
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CBSUSR
ACCOUNTS TBS_01
SQL> EXEC SP_TABLE_MOVE('CBSUSR', 'TBS_01','TBS_02');
PL/SQL procedure successfully completed.
SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM ALL_TABLES
WHERE OWNER = 'CBSUSR' AND TABLE_NAME = 'ACCOUNTS' 2 3 ;
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CBSUSR ACCOUNTS TBS_02
SQL>
