Changing Tablespaces in Oracle database
Working on the ERP project for our Client i found that all data was getting stored in the systems tablespace and because the client data was huge it was downgrading the performance of the system. It was something which i and my team at Adaptive IT would never let go. Even though the client was not aware of problem.
We told the problem to client but client was reluctant to do that as they were more worried about their legacy data. We manage to convence the client and changed the tablespace from SYSTEM to user specific. Client was happy to see the performance improvements. I share my experiences in conversion process
About System Tablespace in oracle database:
The SYSTEM tablespace contains the data dictionary tables for the entire database. All data stored on behalf of stored PL/SQL program units procedures, functions, packages, and triggers resides in the SYSTEM tablespace. Earlier to 9i the System table space use to be Dictionary Managed but later it come as Default to Locally managed ie managed by Oracle by itself are best to use. LMTs are faster in some operations, like allocating space, truncating tables, dropping objects.
Guidelines on using tablespace Use Multiple tablespaces
Using multiple tablespaces allows you more flexibility in performing database operations.
- Separate user data from data dictionary data to reduce I/O contention.
- Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
- Store different the datafiles of different tablespaces on different disk drives to reduce I/O contention.
- Take individual tablespaces offline while others remain online, providing better overall availability and allows you to back up individual tablespaces.
You create datafile inside the tablespaces. These file remain open when the database is up, some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently. Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles.
How we changed the tablespaces?
We used datapump utility for Oracle 11g. to change the tablespace. This is two step process which involves Datapump Export and import. While Importing the data it gives an option to REMAP- schema and tablespaces from source to destination.
