E-Notebook data growth in the Oracle database

E-Notebook data growth in the Oracle database

Questions:

“Could you tell us which actions (by the user or by the system) make some tablespaces to increase a lot and which actions not? For example: open a new collection, close an experiment, send data to Chemregistration... And which tablespaces are affected by those actions?”

Answers:

Default table space names are used below. These names may be different for customers that have been using an Oracle schema name other than  “CS_NOTEBOOK9”. Further details can be found by looking at the E-Notebook SQL scripts. This information is not specific for E-Notebook 9.x, it will also apply to E-Notebook 11.

The explanations on splitted tables only apply if Oracle Enterprise with the “Partitioning” option is used. Without Partitioning, the table names are similar, but not splitted into different parts. Also, in addition to the standard tables space files (named “T_...”), there is usually an additional audit table space file  (named “TA_...”). Regarding the size of these table space files, similar rules apply to the standard and audit table space file. Audit table space files generally fill up quickly if users do a lot of editing on existing experiments. With each save operation in E-Notebook that is done after data have been saved for the first time, information will be written to the audit tables. So audit tables can become larger than the original Notebook tables after a while.

As a rule of thumb, the bulk of the data in the E-Notebook database will be stored in the following  three table space file types:  

1. Chemical Structures (ELN_CHEMICAL_STRUCTURES table). This is T_CS_NOTEBOOK9_CSx.DBF (x=0-7).
2. “Styled Text” or “Autotext” fields (ELN_STYLED_TEXT table). This is stored in the “Large Binary Objects” (LOB) table space files T_CS_STLOBx.DBF (x = 0-7).
3. MS Office, PDF documents and Images, Ancillary Data (ELN_DOCUMENTS and ELN_ANCILLARY_DATA tables). This is stored in the document table space files T_CS_NOTEBOOK9_DOCx.DBF (x = 0-7).

Data are splitted into segments 1-7 by size in the standard tables, and by date and time of creation in the Audit tables. Splitting data by date and time in the Audit tables should achieve an even distribution of the data among the different table space files.

Simple operations like creating new collection objects in the E-Notebook client will have a very small effect on table space file sizes. Operations like changing the state, or sending data to registration can almost be neglected in that respect.

For standard E-Notebook sections like the “reaction” section, only the structures (T_CS_NOTEBOOK9_CSx.DBF) and “Styled Text” field (T_CS_STLOBx.DBF) table space files will grow.

But “structures” and “Styled Text” fields are usually small compared to the amount of data stored in MS Office, PDF, Image and Ancillary Data sections (T_CS_NOTEBOOK9_DOCx.DBF table space files). This is where most customers will probably store the bulk of their data.

But this obviously depends on the Notebook configuration (available section types) and on the way the Notebook is used. For customers that store large amounts of binary data, we recommend using the SDMS add-in (where data are actually stored in a file system and only references to the data are stored in the ELN).