Move multiple tables belonging to a particular schema

Moves all the tables belonging to a particular schema to a new tablespace.

If you are running a number of schemas under one service -- which is usually the case -- and you need to move all...

the tables belonging to a particular schema to a new tablespace, then this script is for you. Instead of manually typing the command...

 Alter table tablename move tablespace tsname;  

...for each individual table, use this simple procedure that uses the DBA_TABLES information to move all the tables belonging to a particular schema from one tablespace to another tablespace. It needs the SCHEMA NAME and the TABLESPACE NAME to be moved as input parameters.

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; 
/ 

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in March 2002

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close