2 Replies Latest reply on Dec 10, 2012 2:56 AM by Salman Qureshi

    Tablespace creation guidelines

      Hi all,

      I'm about to bring objects to our existing Oracle database, and I'm really worried about the functional design of the database overall. With this, I want to know how tablespaces should be created with respect to the functional requirements (i.e. if there are more than 1 application using the database, should there be a single tablespace for an applications?) I would also appreciate best practice tips, etc.

        • 1. Re: Tablespace creation guidelines
          There isn't a lot of relationship between functional requirements and tablespaces...

          You may want to put the objects related to different applications into different tablespaces if each application needs to manage its data usage independently (for example, if you charge back tablespace usage to each application).
          If you know the expected size of different objects, you may be able to slightly reduce the amount of disk space you use by putting objects in different tablespaces that have different extent sizes. On the other hand, though, that tends to increase the amount of effort required to manage the database (and to keep all the objects in the correct tablespace). Generally, the disk space you save isn't worth the effort you expend but that's a question you'd have to answer for your organization.
          If you want the ability to do a tablespace point-in-time recovery so that one application can continue to work if another application's tablespace is offline, you may want separate tablespaces for different applications
          If you want to use transportable tablespaces to move a number of tables from one database to another, you'd want to put those objects in a separate tablespace.

          • 2. Re: Tablespace creation guidelines
            Salman Qureshi
            For application's functional design, there is no complution in physical database desing. You can create a single tablespace and a single schema and put everything there, but, for maintenance and scalability, it is better to have a different schema for each different application as long as applications are not dependent on each other. If application are dependent and need data fron other application's data, you migh need to create synonyms in theh schema which needs data from other schema and grant rights on those.

            You can house all schemas (related to different applications) into a single tablespace without any problem and this should be simpler for you to manage a single tablespace rather than a multiple tablespaces.

            Having different tablespaces for each schema (related to each different application) is also a good option because it will be easier for you to have different kind of maintenance/backup plans with respcet to criticality of the application.