Manage Tablespaces

In the old OMWB we had a "Discover Tablespaces" feature, so that you could assign tables to existing tablespaces in your Oracle database, before generating your Database.

In SQL Developer you can do this in 3 ways.

1. Change the generation script so that you specify a different default tablespace for the new schema and all its tables.
2. Change a tables tablespace once in Oracle. Right click the table and choose Storage > Move Tablespace
3. Write a script to automatically assign tables to different tablespaces depending on some criteria.

The first two options are show in this screen shot




But the third way may be the most useful.
If you wanted to have seperate tablespaces depending on the size of the tables you could run the following scripts on your Sybase/SQL Server database. The output of which can be run against your migrated Oracle database to move the tables to their new tablespaces. The best thing about this approach is you can do this before migrating the data, so that moving tablespaces is trivial.

SQL Server

SELECT CASE
       WHEN num_rows >=100 THEN
         'ALTER TABLE ' + TABLE_NAME +' MOVE TABLESPACE '+' BIGTABLESPACE ;'
       WHEN num_rows <100 AND num_rows >=50 THEN
         'ALTER TABLE ' + TABLE_NAME +' MOVE TABLESPACE '+' MEDIUMTABLESPACE ;'
       WHEN num_rows <50  THEN
          'ALTER TABLE ' + TABLE_NAME +' MOVE TABLESPACE '+' SMALLTABLESPACE ;' END  SCRIPT
FROM (SELECT
Table_Name = so.name ,
NUM_ROWS = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name ) ALL_TABLES

Sybase

drop table TableRowCount
go

drop procedure populateTableRowCount
go

CREATE TABLE TableRowCount(table_name varchar(200),num_rows int)
go

create procedure populateTableRowCount as
begin
declare @tableName varchar(200)
declare @rowNum int
declare name_cursor cursor
for select name from sysobjects where type ='U' and name != 'TableRowCount'
for read only
CREATE TABLE #countSize(rowNum int)
open name_cursor
fetch name_cursor into @tableName
while @@sqlstatus = 0 -- ie no errors and we successfully fetched a row
begin
execute( 'insert into #countSize  select count(*) rowNum from ' + @tableName)
select  @rowNum = max(rowNum) from #countSize
delete from #countSize
insert into TableRowCount values(@tableName,@rowNum)
fetch next from name_cursor into @tableName
end
close name_cursor
deallocate cursor name_cursor
end
go

exec  populateTableRowCount
go

SELECT CASE
        WHEN num_rows >=100 THEN
          'ALTER TABLE ' + table_name +' MOVE TABLESPACE '+' BIGTABLESPACE ;'
        WHEN num_rows <100 AND num_rows >=50 THEN
          'ALTER TABLE ' + table_name +' MOVE TABLESPACE '+' MEDIUMTABLESPACE ;'
        WHEN num_rows <50  THEN
           'ALTER TABLE ' + table_name +' MOVE TABLESPACE '+' SMALLTABLESPACE ;' END  SCRIPT
FROM TableRowCount
go

Oracle : If you are already on Oracle and the data is present in your Oracle tables.

SELECT CASE
       WHEN num_rows >=100 THEN
         'ALTER TABLE ' || TABLE_NAME ||' MOVE TABLESPACE '||' BIGTABLESPACE ;'
       WHEN num_rows <100 AND num_rows >=50 THEN
         'ALTER TABLE ' || TABLE_NAME ||' MOVE TABLESPACE '||' MEDIUMTABLESPACE ;'
       WHEN num_rows <50  THEN
          'ALTER TABLE ' || TABLE_NAME ||' MOVE TABLESPACE '||' SMALLTABLESPACE ;' END  SCRIPT
FROM ALL_TABLES
WHERE lower(owner) ='ownername'

The above scripts should generate another script like

SCRIPT
ALTER TABLE au_pix MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE authors MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE blurbs MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE discounts MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE publishers MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE roysched MOVE TABLESPACE  MEDIUMTABLESPACE ;
ALTER TABLE sales MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE salesdetail MOVE TABLESPACE  BIGTABLESPACE ;
ALTER TABLE skill MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE skill_detail MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE stores MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE table_emp MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE titleauthor MOVE TABLESPACE  SMALLTABLESPACE ;
ALTER TABLE titles MOVE TABLESPACE  SMALLTABLESPACE ;

Which when run in Oracle will move the tables to the correct tablespace. The above results are from Sybase pubs2 example database