In development mode you don’t want to worry about which table goes into what Tablespace in production as it complicates development environments. The production DBA’s want to have their input and control over deciding what table goes into what Tablespace. To allow for this I used a mapping scheme as shown below.
Lets assume we have 3 tables in our system Customer, CustomerOrder, OrderStatus. Where we are expecting Customer table to have large numbers of rows and CustomerOrder to have significanly large number of rows while OrderStatus would have few rows and not change as much. In development environments all these tables and their indexes will be put under the same tablespace. In production like environments we want to put them into seperate tablespaces.
So we had 3 types of tablespaces Small, Medium and Large to hold tables of Small, Medium and Large Sizes. We also had 3 tablespaces SmallIndex, MediumIndex and LargeIndex to hold the tables Indexes. The production DBA provided a MAP of tables and tablespace names that the table needed to reside in, when we created the tables and their indexes in production like environements we created all the objects in the same tablespace and later on a pl/sql script moved all the tables into their respective tablespaces using the MAP provided by the production DBA’s.
Now lets look at the code. The tablespace MAP looks as below, where we map a table to the tablespace where the table needs to reside. By naming convention we can also move all the indexes of the table. For example the indexes of OrderStatus table will be created in tablespace named as SmallIndex
The pl/sql code to check if all the tables are mapped, if all tables are not mapped then we get a exception.
DECLARE
non_mapped_tables number;
BEGIN
SELECT count(*) INTO non_mapped_tables FROM user_tables
WHERE table_name
NOT IN (SELECT table_name FROM tablespace_map)
AND table_name != 'TABLESPACE_MAP';
IF non_mapped_tables != 0 THEN
THROW_ERROR(non_mapped_tables,' All table/s have not been mapped to TABLESPACES');
END IF;
END;
/
Now we are ready to move the tables and their indexes to their respective tablespaces.
DECLARE
CURSOR tabs IS
SELECT 'ALTER TABLE ' || segment_name || ' MOVE tablespace ' stmt,m.tablespace_name
FROM user_segments u,tablespace_map m
WHERE
u.segment_type = 'TABLE'
AND u.segment_name = m.table_name
ORDER BY u.segment_name;
CURSOR idx IS
SELECT 'ALTER INDEX '|| segment_name || ' REBUILD tablespace ' stmt,m.tablespace_name
FROM user_segments u,user_indexes i,tablespace_map m
WHERE
u.segment_type = 'INDEX'
AND u.segment_name = i.index_name
AND i.table_name=m.table_name
ORDER BY u.segment_name;
BEGIN
--Move all the tables
FOR tabsrec IN tabs LOOP
EXECUTE IMMEDIATE tabsrec.stmt||tabsrec.tablespace_name;
END LOOP;
--Move all the indexes
FOR idxrec IN idx LOOP
EXECUTE IMMEDIATE idxrec.stmt||idxrec.tablespace_name;
END LOOP;
END;
/
Deployments done this way will sheild the development team worrying about production database specifics and allows the production DBA’s to fine tune the production deployment.