Moving Schema's Across Tablespace's

				







<--[if gte mso 9]>
 
 Administrator
 Normal
 Administrator
 2
 2
 2002-08-17T07:30:00Z
 2002-08-17T07:30:00Z
 1
 PCONE
 1
 1
 9.3821
 





CREATE OR REPLACE PROCEDURE  TOBJECTFROMTS (
  V_Owner  
IN    VARCHAR2,
  V_NEWTS  
IN    VARCHAR2
 
)
IS
 v_table_name 
varchar2(100);
 str_sql   
varchar2(200);
 
---Opening an explicit
cursor to get all the users belonging to the Schema
cursor schematables(Owner varchar2) is
select TABLE_NAME  from DBA_TABLES x
Where LOWER(x.OWNER) = LOWER(V_OWNER);
BEGIN
open schematables(V_owner);
loop
Fetch schematables into v_table_name ;
       exit when schematables%notfound;
      ---DBMS_OUTPUT.put_line(' alter table '|| v_table_name
|| ' move tablespace psle1;');
      str_sql := 'alter table '|| v_table_name || ' move tablespace' ||' ' ||'' || V_NEWTS ||'';
      DBMS_OUTPUT.put_line(str_sql);
      --- str_sql is the sql statement that we want
to execute to move the
      -- tablespaces to the New table space
      execute immediate str_sql;
 end loop;
 close schematables;
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
   DBMS_OUTPUT.put_line ('THERE IS NO SUCH RECORD');
--RAISE_APPLICATION_ERROR(-20045,'NO
DATA FOUND');
END;
/