Tablespace Query

[najm]

Dear
I have made db1 user's table on system tablespace. Now I want to move db1's tables/objects to db1tbs table. I gave the command on sys after creating db1tbs tablespace, create user db1 identified by *** default tablespace db1tbs;
Then after import dump, the tables move into system and db1tbs remains empty. What do I do to move tables?

[amirtai]

Hi Najm

You have to move table 1 by 1 issuing a command on SQL prompt. (workaround: can write a script to move all tables of that schema).
here is syntax.

ALTER TABLE tablename MOVE newtablespacename

CAUTION: You might need to rebuild indexes related to that schema. you can use script below to automate rebuilding indexes.
============================================
CREATE OR REPLACE PROCEDURE rebuild_indexes (
i_schema IN VARCHAR2
) IS
v_sql VARCHAR2(2000);

CURSOR cur_indexes(
p_schema VARCHAR2) IS

SELECT segment_name,
tablespace_name
FROM sys.dba_extents
WHERE owner=p_schema
AND segment_type = 'INDEX'
GROUP BY segment_name,
tablespace_name
HAVING COUNT(extent_id) > 1
ORDER BY segement_name;
-- Main Logic Loop through the cursor to rebuild all indexs when one fails, the procedure terminates.

BEGIN
FOR ci IN cur_indexes(i_schema) LOOP
v_sql := 'ALTER INDEX'||i_schmea||'.'||ci.segment_name
||'REBUILD TABLESPACE'||ci.tablespace_name;
DBMS_UTILITY.EXEC_DDL_STATMENT(v_sql);
DBMS_OUTPUT.PUT_LINE(ci.segment_name);
END LOOP;
END;