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.
1 2 3 4 5 6 7 8 9 10 11 12
Now we are ready to move the tables and their indexes to their respective tablespaces.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
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.