single big tablespace versus multiple tablespaces
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: single big tablespace versus multiple tablespaces

  1. #1
    Join Date
    Jan 2011
    Posts
    4

    single big tablespace versus multiple tablespaces

    My database version is

    Code:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE    11.1.0.7.0      Production
    TNS for Linux: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production
    My os version is

    Code:
    Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
    My database is OLP system.

    My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?


    Easy to maintain when you have single tablespace. but hard to track the IO issues.

    Any other input from any one? Please let me know.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    BFT has some advantageous like it simplifies table space management, simplifies data file management (with OMF) and it also has the disadvantageous like it takes more time in case of restore / recover. Google it for more details.

    by the way, what is your DB size?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,443

    Cool

    Adding to vnktummala's post:

    1) It will also depend on the number of files. If you have one tablespace with many files, the backups/restore can be parallelized (vg in RMAN) and you can track I/O issues per file.

    2) If you have one or several tablespaces for each schema, you can use "transportable tablespaces" feature for moving data or backing up (archiving old) partitions.

    3) Otherwise if you have "bigfiles" tablespace, backups/restore can get un-wieldly.

    4) And so on...

    My 2c
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Jan 2011
    Posts
    4
    My DB size couple of tera byte. I am looking difference between one tablepsace with multiple data files versus multiple tablespace with multiple data files. Thanks for your input though.

    Quote Originally Posted by vnktummala View Post
    BFT has some advantageous like it simplifies table space management, simplifies data file management (with OMF) and it also has the disadvantageous like it takes more time in case of restore / recover. Google it for more details.

    by the way, what is your DB size?

    Thanks,

  5. #5
    Join Date
    Jan 2011
    Posts
    4
    Thank you!! Appreciate your input.

    Quote Originally Posted by LKBrwn_DBA View Post
    Adding to vnktummala's post:

    1) It will also depend on the number of files. If you have one tablespace with many files, the backups/restore can be parallelized (vg in RMAN) and you can track I/O issues per file.

    2) If you have one or several tablespaces for each schema, you can use "transportable tablespaces" feature for moving data or backing up (archiving old) partitions.

    3) Otherwise if you have "bigfiles" tablespace, backups/restore can get un-wieldly.

    4) And so on...

    My 2c

  6. #6
    Join Date
    Jan 2011
    Posts
    4
    Thank you!! My db size is 2TB. I am looking difference between single tablespace with multiple datafiles versus multiple tablespace with multiple data files.

    Quote Originally Posted by vnktummala View Post
    BFT has some advantageous like it simplifies table space management, simplifies data file management (with OMF) and it also has the disadvantageous like it takes more time in case of restore / recover. Google it for more details.

    by the way, what is your DB size?

    Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width