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 firstname.lastname@example.org 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