By Franck Pachot
Tim Hall had the idea that as many people as possible would write a small blog post about their favorite Oracle feature and we all post them on the same day. Here is my favorite feature that I described at the “EOUC Database ACES Share Their Favorite Database Things“: Transportable Tablespaces appeared in Oracle 8.1.5
I’ll start with a change that came between Oracle 7 and Oracle 8. The ROWID, which identifies the physical location of a row within an database (with file ID, block offset, and row directory number) changed to be the location within a tablespace only. The format did not change, but the file ID was changed to be a relative file number instead of an absolute file number.
Here is the idea:
Actually, to be able to migrate without visiting each block (the ROWID is present in all blocks, all redo vectors, etc) they used the same number but that number is unique only within a tablespace. The first goal was to hold more datafiles per tablespace (Oracle 8 was the introduction of VLDB – Very Large Databases – concepts). The limit of 255 datafiles per database became a limit of 255 datafiles per tablespace. So the numbers starts the same as before but can go further.
This change was simple because anytime you want to fetch a row by its ROWID you know which table you query, so you know the tablespace. The exception is when the ROWID comes from a global index on a partitioned table, and for this case Oracle 8 introduced an extended ROWID that contains additional bytes to identify the segment by its DATA_OBJECT_ID.
By the way, this makes tablespaces more independent on the database that contains them because all row addressing is relative.
Locally Managed Tablespaces
Another change in 8i was Locally Managed Tablespaces. Before, the space management of the tablespaces was centralized in the database dictionary. Now, it is delocalized in each tablespace. What was stored in UET$ system table is now managed as a bitmap in the first datafile header.
The original name of transportable tablespace was “pluggable tablespaces”. Because they are now more self-contained, you can detach them from a database an attach them to another database, without changing the content. This means that data is moved physically which is faster than the select/inserts that are behind a logical export/import. There are only two things that do not come with the datafiles.
The open transactions store their undo in the database UNDO tablespace. This means that if you detach a user tablespace you don’t have the information to rollback the ongoing transactions when you re-attach it elsewhere. For this reason, this ‘detach’ is possible only when there are no on-going transactions: you have to put the tablespace READ ONLY.
The user object metadata is stored in the database dictionary. Without them, the datafiles is just a bunch of bytes. You need the metadata to know what is a table or index, and which one. So, with transportable tablespaces, a logical export/import remains for the metadata only. This was done with exp/imp when TTS were introduced and is now done with DataPump. Small metadata is moved logically. Large data is moved physically.
TTS is faster than simple DataPump because data is moved physically by moving the datafiles. TTS is more flexible than an RMAN duplicate because you can move a subset of a database easily. Because the metadata is still transported logically, and datafiles are compatible with newer versions, TTS can be done cross-version, which makes it a nice way to migrate and upgrade. It is used also for tablespace point-in-time recovery where you have to recover to an auxiliary instance and then transport the tablespace to the target.
TTS is also used to move data quickly from operational database to a datawarehouse ODS.
It is also a good way to publish and share a database in read-only, on a DVD for example.
Except with the move to DataPump for the metadata transfer, TTS has not change a lot until 12c. In 12.1 you have full transportable tablespace which automates the operations when you want to move a whole database. This can be used to migrate from non-CDB to multitenant architecture.
With multitenant, pluggable databases is an extension of TTS. Because user metadata come with the PDB system tablespaces yon don’t need to export them logically anymore: you transport the whole PDB. That’s the first restriction relieved. The second restriction, the need for read only, will be relieved as well when the UNDO will be local to the PDB and I don’t think I disclose any secret when telling that local UNDO has been announced for 12.2
OTN Appreciation Day
This was my contribution to the “EOUC Database ACES Share Their Favorite Database Things” at Oracle Open World 2016 organized by Debra Lilley. Tim Hall idea of “OTN Appreciation Day” comes from that. You still have time to contribute for this day. No need for long posts – I always write a but more than what I plan to. The “rules” for this day is described in oracle-base.com