2

Using Oracle managed files I can create my tablespace like this:

CREATE TABLESPACE users;

It will autoextend but as I understand it smallfile datafiles have a maximum size. So I then need to do:

ALTER TABLESPACE users ADD DATAFILE;
ALTER TABLESPACE users ADD DATAFILE;
ALTER TABLESPACE users ADD DATAFILE;

Just wondering if there was a way to get oracle to automatically add another datafile when it tries to autoextend past its largest possible size?

Vadzim
  • 24,954
  • 11
  • 143
  • 151
Adam Butler
  • 3,023
  • 5
  • 35
  • 40
  • I thought the limit was 4million x blocksize http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_7003.htm#sthref5404 – Adam Butler Mar 02 '11 at 14:15

3 Answers3

1

There is no built-in method for oracle to auto-add datafiles.

BEWARE: The "unlimited" operator in oracle is not actually unlimited. It only means unlimited up to oracle's hard cap on datafile sizes, which depends on your db_block_size parameter. A default database is going to have an 8k block size and use smallfile tablespaces, which means the datafiles won't grow past 32GB.

Once the datafile reaches the hard limit, even when you have AUTOEXTEND MAXSIZE UNLIMITED set, you will still have to add a new datafile by hand.

For more information see the table in this post:

https://community.oracle.com/message/1900237#1900237

Jonathan Lynch
  • 557
  • 1
  • 5
  • 12
1

There is no real limit in Oracle

ALTER DATABASE DATAFILE <'data_file_name' | data_file_number> AUTOEXTEND K|M|G|T|P|E MAXSIZE <UNLIMITED | K|M|G|T|P|E>;

OS or os operator can stop You and database. Than You can write job that wiil be automaticly add new files to tablespace after % of usage. Something like this (pseudo code).

SELECT tablespace_name,maxbytes,bytes INTO v_tbs_info FROM dba_data_files;

FOR tbs_inf IN v_tbs_info LOOP
  IF tbs_inf.bytes/tbs_inf.maxbytes > 0.99
  excecute immediate 'ALTER TABLESPACE '|| tbs_inf.tablespcae_name ||' ADD DATAFILE SIZE 1M aUTOEXTEND ON NEXT 10M MAXSIZE 10G';
END LOOP;

Of course You shouldn't write this job and use it. You should provide the amount of space occupied by the data and allocate the appropriate number of files to the tablespace.

baklarz2048
  • 10,699
  • 2
  • 31
  • 37
0

sure we can but why should we? Normally there is a reason for a limit. And for the max sizes of the datafiles, we can create a bigfile tablespace that has stretched the filesize limits a bit. This has impact on backup/recovery but since 11gR2 we can have those files backedup in parallel, where the bigfiles are chopped in chunks and distributed over the various channels.

Also, a question what is the largest possible size? Is it the size that is supported by the filesystem, by ASM, by the database or is it the maxsize that is set by the dba?

Ronald.