This discussion is archived
11 Replies Latest reply: Sep 7, 2010 6:04 AM by Mark D Powell RSS

Tablespace resize

753818 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Blocksize is 8k
  • 4. Re: Tablespace resize
    Mark D Powell Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 --

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points