Re: Tablespace AUTOEXTEND ON

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Mon, 15 Sep 2014 14:36:58 -0700
Message-ID: <1410817018.74797.YahooMailNeo_at_web124702.mail.ne1.yahoo.com>

Yes. For an 8k db_block_size the maximum file size is just under 32G; for a 16K db_block_size you should see just under 64GB for the maximum file size and if you set a db_block_size of 32K the maximum size is just under 128GB. This is for smallfile tablespaces. For bigfile tablespaces the maximum size is much larger, but you can't add files to a bigfile tablespace. Bigfile tablepaces can have a file of 32TB with an 8k db_block_size, 64Tb for 16k db_block_size and 128TB for a32k db_block_size.  

David Fitzjarrell

Principal author, "Oracle Exadata Survival Guide"

On Monday, September 15, 2014 1:37 PM, Nik Tek <niktek2005_at_gmail.com> wrote:  

Hi Rodrigo,

So it is the block size that is limiting the tablespace to 32GB? and only option is add more data files?

Thank you
Nik

On Mon, Sep 15, 2014 at 12:19 PM, Rodrigo Mufalani <rodrigo_at_mufalani.com.br> wrote:

Hi,
>
> It was explained here some weeks ago. This 32Gb size is dependent of tablespace block size, in your case 8k.
> Look at old messages in history. Also you can create more datafiles by one tablespace.
>
>[ ]'s
>#mufalani
>
> Desculpe por erros! Este e-mail foi escrito do meu smartphone!
>
> Sorry for typos! This mail was written from my smartphone!!!
>
>
>> On 15/09/2014, at 16:13, Nik Tek <niktek2005_at_gmail.com> wrote:
>>
>> Hi,
>>
>> I have question about tablespace "autoextend on"
>>
>> The below command is used to create the tablespace "USERS" with autoextend ON, but the datafile is stuck at 32GB, is there a way to make it grow automatically until it fills the /data01 lun.
>>
>> v$version: select * from v$version;
>> Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
>> PL/SQL Release 12.1.0.1.0 - Production
>> CORE 12.1.0.1.0 Production
>> TNS for Linux: Version 12.1.0.1.0 - Production
>> NLSRTL Version 12.1.0.1.0 - Production
>>
>>
>> df-h
>> Filesystem Size Used Avail Use% Mounted on
>> /dev/sda2 25G 3.7G 20G 16% /
>> tmpfs 16G 9.3G 6.4G 60% /dev/shm
>> /dev/sda1 26G 5.5G 19G 23% /u01
>> /dev/sdb1 50G 6.9G 40G 15% /u02
>> /dev/sdc1 99G 33G 62G 35% /data01
>> /dev/sdd1 99G 29G 65G 31% /data02
>> /dev/sde1 99G 9.5G 84G 11% /backup01
>> /dev/sdg1 15G 166M 14G 2% /dbbackup
>> /dev/sdf1 20G 17G 2.6G 87% /swapspace
>>
>>
>> Command:
>> CREATE TABLESPACE USERS DATAFILE
>> '/data01/oradata/SCDB/users01.dbf' SIZE 33554416K AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
>> LOGGING
>> ONLINE
>> EXTENT MANAGEMENT LOCAL AUTOALLOCATE
>> BLOCKSIZE 8K
>> SEGMENT SPACE MANAGEMENT AUTO
>> FLASHBACK ON;
>>
>> Trace File:
>> ALTER DATABASE DATAFILE '/data01/oradata/SCDB/users01.dbf' AUTOEXTEND ON maxsize unlimited
>> Completed: ALTER DATABASE DATAFILE '/data01/oradata/SCDB/users01.dbf' AUTOEXTEND ON maxsize unlimited
>>
>>
>> Error:
>> ORA-01653: unable to extend table VXDDM.TESTBED by 128 in tablespace USERS
>>
>>
>> Could someone help me what mistake I'm doing?
>>
>>
>>
>> --
>> Thank you
>> NikTeki
>
>

-- 

Thank you
NikTeki
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 15 2014 - 23:36:58 CEST