Thursday, 15 May 2008

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