6 Replies Latest reply: Jul 15, 2009 9:29 AM by sb92075 RSS

    How to define tablespaces in Oracle 10g and how put tables on tablespaces

    443102
      Hello,

      I'm having trouble to define the structure of tablespaces and how to distribute the tables/indexes/lobs on these tablespaces.
      Do you know some rules on how to define this?

      What I have until now:
      1) Put table indexes in separate tablespace
      2) Put lobs in separate tablespace and use storage clause when defining the lob column. Not sure how many tablespaces to use, I have tables with millions of lobs and tables with just one lob (with row containing the lob).
      3) Please fill more rules here ...

      Thank you, Alex.
        • 1. Re: How to define tablespaces in Oracle 10g and how put tables on tablespaces
          sb92075
          3) Please fill more rules here ..
          how many physical disk drives do you have to hold Oracle datafiles?
          • 2. Re: How to define tablespaces in Oracle 10g and how put tables on tablespaces
            443102
            Long story: I received database and schema creation scripts defined in Oracle 9i. I want to redesign the tablespaces number/storage and also to change the distribution of tables into tablespaces.
            As an example I will show how tablespaces are created now. There is one tablespace for indexes BLUE_AUTO_INDX and one for each EXTENT MANAGEMENT of:
            - 64K for BLOB , name BLUE_K064_BLOB
            - 64K for tables , name K064_NTAB
            - 128K for tables, name BLUE_K128_NTAB
            - 512K for CLOB , name BLUE_K512_CLOB
            - 4M for BLOB , name BLUE_M004_BLOB
            - 64M for CLOB , name BLUE_M064_CLOB
            - 8M for tables , name BLUE_M008_NTAB
            - 1M for tables , name BLUE_M001_NTAB
            - 256M for tables, name BLUE_M256_NTAB

            Definition of each tablespace is like:
            CREATE TABLESPACE BLUE_AUTO_INDX DATAFILE 'BLUE_AUTO_INDX01.DBF' SIZE 200M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M SEGMENT SPACE MANAGEMENT AUTO;
            CREATE TABLESPACE BLUE_K064_BLOB DATAFILE 'BLUE_K064_BLOB01.DBF' SIZE 20M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;
            CREATE TABLESPACE BLUE_K064_NTAB DATAFILE 'BLUE_K064_NTAB01.DBF' SIZE 20M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;
            CREATE TABLESPACE BLUE_K128_NTAB DATAFILE 'BLUE_K128_NTAB01.DBF' SIZE 20M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;
            CREATE TABLESPACE BLUE_K512_CLOB DATAFILE 'BLUE_K512_CLOB01.DBF' SIZE 20M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT AUTO;
            CREATE TABLESPACE BLUE_M004_BLOB DATAFILE 'BLUE_M004_BLOB01.DBF' SIZE 20M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M SEGMENT SPACE MANAGEMENT AUTO;
            CREATE TABLESPACE BLUE_M064_CLOB DATAFILE 'BLUE_M064_CLOB01.DBF' SIZE 400M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M SEGMENT SPACE MANAGEMENT AUTO;
            CREATE TABLESPACE BLUE_M008_NTAB DATAFILE 'BLUE_M008_NTAB01.DBF' SIZE 200M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M SEGMENT SPACE MANAGEMENT AUTO;
            CREATE TABLESPACE BLUE_M001_NTAB DATAFILE 'BLUE_M001_NTAB01.DBF' SIZE 100M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO;
            CREATE TABLESPACE BLUE_M256_NTAB DATAFILE 'BLUE_M256_NTAB01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256M SEGMENT SPACE MANAGEMENT AUTO;

            Does this structure of tablespaces ok? I mean if the logic of one tablespace for different EXTENT SIZEs looks ok?
            • 3. Re: How to define tablespaces in Oracle 10g and how put tables on tablespaces
              443102
              I use ASM defined on 2 external storages (RAID 10 names Storage01 and Storage02) for RAC with 2 nodes.
              Database size is about 130Gb.
              I want to define next disk groups:
              1) DGDATA - to store database - 300Gb on Storage01
              2) DGREDO - to store redo logs - 4GB on Storage01
              3) DGFRA - to store Flash Recovery Area and Backups - 400Gb on Storage02
              • 4. Re: How to define tablespaces in Oracle 10g and how put tables on tablespaces
                sb92075
                If all tablespaces reside on same collection of disks, it does not matter if you have 1 tablespace or multiple tablespaces.
                The I/O performance will be the same.
                • 5. Re: How to define tablespaces in Oracle 10g and how put tables on tablespaces
                  443102
                  I cannot agree 100% with you.
                  There is a difference between tablespaces I have, the extension sizes are different 1K, 8k, 128k, 512k, 1M, 8M, 64M, 256M.
                  • 6. Re: How to define tablespaces in Oracle 10g and how put tables on tablespaces
                    sb92075
                    There is a difference between tablespaces I have, the extension sizes are different 1K, 8k, 128k, 512k, 1M, 8M, 64M, 256M.
                    Symptomatic of Compulsive Tuning Disorder.