Passionate about data

Data and its implications on software design and development.

Automated Tablespace Deployment

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

Table_NameTablespace_Name
ORDERSTATUSSmall
CUSTOMERMedium
CUSTOMERORDERLarge

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
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.

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
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.