3 Replies Latest reply: Feb 5, 2013 4:25 AM by Osama_Mustafa RSS

    Table partition to different tablespaces

    Manjusha Muraleedas
      Hi,

      I am on the way to partition some tables in my database.

      Currently My database contains only one tablespace.
      SQL> select * from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE     11.2.0.1.0     Production
      
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      
      SQL> select t.Tablespace_Name,t.Segment_Space_Management  from User_Tablespaces          t;
      
      TABLESPACE_NAME                SEGMENT_SPACE_MANAGEMENT
      ------------------------------ ------------------------
      SYSTEM                         MANUAL
      SYSAUX                         AUTO
      UNDOTBS1                       MANUAL
      TEMP                           MANUAL
      USERS                          AUTO
      TS_MYSPACE              AUTO
      I am going to do a hash partion on parent table and reference partition on 6 child tables.

      What are the advantages when I create more tablespaces?
      How many additional table spaces I need to create.

      Please advice.
        • 1. Re: Table partition to different tablespaces
          Uwehesse-Oracle
          You create tablespaces to support your organizational requirements - not for database performance.

          You may want to have tablespaces that are read only and/or compressed or you want their datafiles on low cost storage.

          There are more reasons why, but just for starters :-)

          Kind regards
          Uwe Hesse

          "Don't believe it, test it!"
          http://uhesse.com
          • 2. Re: Table partition to different tablespaces
            Osama_Mustafa
            Manjusha Muraleedas wrote:
            Hi,

            I am on the way to partition some tables in my database.

            Currently My database contains only one tablespace.
            SQL> select * from v$version;
            
            BANNER
            --------------------------------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            PL/SQL Release 11.2.0.1.0 - Production
            CORE     11.2.0.1.0     Production
            
            TNS for Linux: Version 11.2.0.1.0 - Production
            NLSRTL Version 11.2.0.1.0 - Production
            
            SQL> select t.Tablespace_Name,t.Segment_Space_Management  from User_Tablespaces          t;
            
            TABLESPACE_NAME                SEGMENT_SPACE_MANAGEMENT
            ------------------------------ ------------------------
            SYSTEM                         MANUAL
            SYSAUX                         AUTO
            UNDOTBS1                       MANUAL
            TEMP                           MANUAL
            USERS                          AUTO
            TS_MYSPACE              AUTO
            I am going to do a hash partion on parent table and reference partition on 6 child tables.

            What are the advantages when I create more tablespaces?
            The advantage of creating tablespace for Make your database management Easier. and you can check oracle documentation
            http://www.csee.umbc.edu/portal/help/oracle8/server.815/a67781/c03space.htm
            How many additional table spaces I need to create.
            Depend on your work.
            • 3. Re: Table partition to different tablespaces
              Manjusha Muraleedas
              Thanks for the informations and links.

              My intention is ONLY performance iimprovement.
              So.. I am not going to create new tablespace.