11 Replies Latest reply: Sep 7, 2010 8:04 AM by Mark D Powell RSS

    Tablespace resize

    753818
      Using a Bigfile tablespace (maxsize 250G) in Oracle 10g on Solaris.

      In bigfile tablespace I cannot add a second datafile like normal tablespace.

      Want to know when the tablespace reaches the maxsize 250G, what options do I have?

      Can I increase the maxsize ?

      If I re-size the datafile will it tamper the data in any way?

      Sid

      Edited by: online_dba@rediffmail.com on Sep 4, 2010 7:59 PM
        • 1. Re: Tablespace resize
          CKPT
          Hi,

          what is your block size?

          check for oracle 10g..


          http://www.dba-oracle.com/t_bigfile_tablespace_tips.htm
          • 2. Re: Tablespace resize
            657203
            online_dba@rediffmail.com wrote:
            Using a Bigfile tablespace (maxsize 250G) in Oracle 10g on Solaris.

            In bigfile tablespace I cannot add a second datafile like normal tablespace.

            Want to know when the tablespace reaches the maxsize 250G, what options do I have?

            Can I increase the maxsize ?

            If I re-size the datafile will it tamper the data in any way?

            Sid

            Edited by: online_dba@rediffmail.com on Sep 4, 2010 7:59 PM
            Bigfile tablespace with 8K and 32K blocks can contain 32 terabyte and 128 terabyte datafiles respectively. For resizing, use ALTER TABLESPACE command as with small tablespaces:
            ALTER TABLESPACE YOUR_BIG_TBS RESIZE 500G;
            • 3. Re: Tablespace resize
              753818
              Blocksize is 8k
              • 4. Re: Tablespace resize
                Mark D Powell
                Where did you get the 250GB figure from? Is that a Solaris limit.

                From the 10gR2 Reference Manual

                "A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks."

                HTH -- Mark D Powell --
                • 5. Re: Tablespace resize
                  Chinar
                  online_dba@rediffmail.com wrote:
                  Using a Bigfile tablespace (maxsize 250G) in Oracle 10g on Solaris.

                  In bigfile tablespace I cannot add a second datafile like normal tablespace.
                  This is normal situation.You can not add second datafile to bigfile tablespace.This tablespace have only one datafile.
                  Want to know when the tablespace reaches the maxsize 250G, what options do I have?

                  Can I increase the maxsize ?

                  If I re-size the datafile will it tamper the data in any way?
                  Enable autoextend as
                  alter tablespace tablespace_name autoextend on next 50M;
                  • 6. Re: Tablespace resize
                    753818
                    Hi Teymur,

                    You mean to say that I have only the option to resize everytime the tablespace reaches the upper boundary.

                    Its a index tablespace, does resizing tamper in any way the index structure or data ?

                    Why isn't the maxsize pushed up when I resize the datafile ?
                    • 7. Re: Tablespace resize
                      657203
                      online_dba@rediffmail.com wrote:
                      Hi Teymur,

                      You mean to say that I have only the option to resize everytime the tablespace reaches the upper boundary.

                      Its a index tablespace, does resizing tamper in any way the index structure or data ?

                      Why isn't the maxsize pushed up when I resize the datafile ?
                      Pls, see:
                      SQL> create bigfile tablespace big_ts datafile '/oradata/db1/big_ts_1.dbf' size 10 m autoextend on next 1m maxsize 20m;
                      
                      Tablespace created.
                      
                      SQL> create table big_ts_obj tablespace big_ts as select * from dba_objects;
                      
                      Table created.
                      
                      SQL> insert into big_ts_obj select * from big_ts_obj;
                      
                      69489 rows created.
                      
                      SQL> /
                      insert into big_ts_obj select * from big_ts_obj
                                  *
                      ERROR at line 1:
                      ORA-01653: unable to extend table SYS.BIG_TS_OBJ by 128 in tablespace BIG_TS
                      
                      SQL> alter database datafile '/oradata/db1/big_ts_1.dbf' autoextend on next 1m maxsize unlimited
                        2  /
                      
                      Database altered.
                      
                      SQL> insert into big_ts_obj select * from big_ts_obj;
                      
                      138978 rows created.
                      
                      SQL> /
                      
                      277956 rows created.
                      
                      SQL> 
                      If you do not want to change MAXSIE everytime, then set it to UNLIMITED.

                      There is no any relationship between size of datafiles (or resizing datafiles) and INDEX structure.
                      • 8. Re: Tablespace resize
                        352315
                        Hi Mark.
                        Mark D Powell wrote:
                        Where did you get the 250GB figure from? Is that a Solaris limit.
                        I have set the maxsize of the tablespace to 250G at the time of creation. I know it can grow upto 32 TB.
                        • 9. Re: Tablespace resize
                          352315
                          Hi Teymur,

                          So the options I have is;

                          1> resize the datafile ..

                          2> increase the maxsize ;

                          Simulated -
                          SQL> alter database datafile '/home/oracle/index_ts.dbf' autoextend on next 1M maxsize 300M;
                          
                          Database altered.
                          
                          SQL> select tablespace_name, file_name, (bytes/1024)/1024 "Size (Mb)",(maxbytes/1024)/1024 "Max Size (MB)",autoextensible
                            2  from dba_data_files where tablespace_name=upper('index_ts_8k'); 
                          
                          TABLESPACE_NAME             FILE_NAME                            Size (Mb) Max Size (MB) AUT
                          ------------------------------ -------------------------------------------------- ---------- ------------- ---
                          INDEX_TS_8K                 /home/oracle/index_ts.dbf                     200            300 YES 
                          thanks a lot
                          • 10. Re: Tablespace resize
                            CKPT
                            Hi,

                            we can create upto 32,768 GB of bigfile tablespace.. chcek in the link which i posted earlier...
                            • 11. Re: Tablespace resize
                              Mark D Powell
                              If the only file size limitation you face is the one you set at tablespace creation then all you have to do is alter the maxsize file parameter larger whenever the tablespace gets N percentage full where N is a value that fits in your tablespace free space management scheme.

                              HTH -- Mark D Powel --