Tom,

Could you explain how maxsize works? For example, if I have the following create tablespace statement:
CREATE TABLESPACE tbspname_sml
DATAFILE '/usr/local/oracle/data02/${ORACLE_SID}/tbspname_sml_01.dbf' SIZE 524352K REUSE AUTOEXTEND ON NEXT 65536K MAXSIZE 786496K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K NOLOGGING
ONLINE
/

So, my datafile would be 512M, if it runs out of space, it will extend to the maxsize of 768M, would that make the datafile a total size of 1280M (512+768)? Could you elaborate more on it?

Thanks!

I think specifying a reasonably big space growth rate in "next ..." clause would be a good idea, in addition to maxsize, if you want to set autoextend on (such as "alter database datafile '/path/to/db01.dbf' autoextend on next 10MB maxsize 4000MB").

Jenny,

Max size is just that max size.

In your example your file is initially 512K (or was that M). It will grow by 64K (or M) - that is your next size until it reaches a max size of 768K (or M - you get my point).

For your answers.

config1:
One huge file in the tablespace, 15G;

config2:

more than one file in the tablespace, total of 15G;

Comparison of the two, what's the advantage / disadvantage?

TIA

Dear Tom,

We are using Oracle database version 8.1.6 and plan to have a montly database maintenance. Do you recommend to reorganization all the tables by using alter table XYZ move tablespace SAMETABLESPACE STORAGE.... during the montly database maintenance?

By using Alter table xxx MOVE tablespace command, do we still need to recreate the constraints, rebuild indexes, and recompile the views. Can I find any scripts that you have written for this purpose in this website? Thanks.

Rgds
SHGoh

Dear Tom,

Thanks for the information.

Rgds
SHGoh

Is there an easy way to determine when you've overallocated a disk?

Tom
Thanks for the advice - silly question but how do I 'tell' if autoextend is on in the first place? I can use TOAD to see but was interested in the query TOAD uses to provide this. In my standard DB I have quite small indx and users tablespaces and so need to know if they will expand automatically.
Thanks
Phil

ops$tkyte@ORA9IR2> desc dba_data_files;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 FILE_NAME                                    VARCHAR2(513)
 FILE_ID                                      NUMBER
 TABLESPACE_NAME                              VARCHAR2(30)
 BYTES                                        NUMBER
 BLOCKS                                       NUMBER
 STATUS                                       VARCHAR2(9)
 RELATIVE_FNO                                 NUMBER<b>
 AUTOEXTENSIBLE                               VARCHAR2(3)</b>
 MAXBYTES                                     NUMBER
 MAXBLOCKS                                    NUMBER
 INCREMENT_BY                                 NUMBER
 USER_BYTES                                   NUMBER
 USER_BLOCKS                                  NUMBER
 

Earlier in this thread, you mentioned you like to set autoextend for a datafile to the LMT extent size. You also advocate the use of autoallocated extents where the extent sizes change automatically depending on the size of the segment.

Is it a concern if the extent size grows and the autoextend isn't big enough to accommodate? Would it be advisable to set autoextend on next 8M if we use autoallocated extents?

Tablespace A uses 8M extents and its datafile autoextends by 1M intervals.
Tablespace B uses 8M extents and its datafile autoextends by 8M intervals.

When they need a new extent, will A be less efficient the B? Is it more costly to autoextend a datafile multiple times to accommodate a new extent or does it matter at all?

Are Autoextends logged anywhere? I don't think
so but I just want to make sure

Tom, thanks for sharing your knowledge.

I have a question for your regarding autoextend: You say you let datafiles on autoextend on (say of 1M), but you set a maxsize for them, however, with today technology (cluster file systems, logical luns with stripped disk underneath, etc) one tends to put datafiles on the same filesystem, and then when they are many, you just set some "logical" number as their maxsize. However, problems can occur if the filesystem becomes full and you didn't measured well. I was wondering if, within Oracle, even using Java, one can access the underlying filesystem (being Windoze or Unix) to get the underlying metrics of disk storage (like how much space is free on a given filesystem), just like OEM on 10gR2 seems to do it (with the metrics alert, etc). That would be great in generating reports of tablespaces apparently being "full" but the underlying disk having enough space as of this not being an alert anymore.

Thanks for your time.

Thanks again!

.. Java IO classes leaves much to be desired.

</code> http://forums.java.net/jive/thread.jspa?threadID=454&start=0&tstart=0 <code>

I believe I will have to implement a platform especific solution (for example, using df on *nix and some vbscript on windows). That's the way OEM does it I believe. Too bad I can't rather have a database especific solution.

Thanks for your time Tom, and if you know of anything else, let me know.

Thanks again!

A colleague has suggested that using qutoextend is not a good idea and will refuses to use it. Consequently, he gets regular requests to extend the tablespaces in his databases as they run out of space. I accept that some people, as you say at the top, may feel uncomfortable using autoextend and it's use can therefore come down to personal preference. However, he claims that there are good technical reasons why it should not be used. I asked him to explain and got this analogy in response. I would appreciate your comments!

"Oracle is a great steward, and will keep placing bottles of champagne in the fridge. The steward wants to keep the fridge looking good, so it rearranges the bottles, relizes there is more space, and adds more bottles. He keeps doing this, because, the fridge has plenty of space.

Soon he finds half full bottles of champagne, so he starts adding these to the fridge. He also notices that there are some only a quarter full of champagne, and some with no champagne at all, these get added to the refrigerator.

The steward keeps working until refrigerator gets full (ie: out of space).

Now, do we buy a new refrigerator (ie: add disk)?

Now Oracle did his job splendidly, the bottles are neatly in fridge, but now how do we tell the empty bottles from the full ? The only way is to empty the refrigerator, and look at each bottle and remove the empty ones and combine the partials bottles to make one full bottle (ie: database reorganization).

So, now the question is, what is the best way to proceed? Oracle will use all the space for data whether it needs it or not, without any human intervention, when autoextend is turned on. Oracle will consume allocated storage in the way it feels, this includes setting aside large amounts of storage for limited data. The only way this storage can be reclaimed, is through a database reorganization. Its a long and time consuming task that will require some outage time. "

I think he may be confusing either tablespace fragmentation (no longer an issue with LMT), or maybe lots of truncates and/or insert /*+append */ - or maybe not!

Steve -

Perhaps your friend is not used to using LMT's? When an LMT gets a request for space it does not require the space to be contiguous, unlike DMT's. So in this unusual metaphor, when Oracle picks up a bottle to put in the fridge:

- an unopened bottle goes in the fridge
- an opened bottle gets poured into other opened bottles and then thrown away, storing the champagne but not the bottle.
- an empty bottle gets thrown away immediately.

Oracle doesn't ask for another refrigerator until all the bottles in the fridge (sealed _or_ previously opened) are full.

If this isn't it, I guess we'll need more details about his refrigerator.

Hope that helps...?

I have some information about a tablespace in dba_tablespaces for , say tablespace1

1.Can I generate a "create tablespace" script from it [oracle 10g]?
I need to create same to another database for some tables that are in this tablespace.

2.Can I create a tablespace without mentioning Datafile in it?


Thanks for your help.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLESPACE', 'USERS' ) from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
-------------------------------------------------------------------------------

  CREATE TABLESPACE "USERS" DATAFILE
  '/home/ora10gr2/oradata/ora10gr2/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  '/home/ora10gr2/oradata/ora10gr2/users01.dbf' RESIZE 462684160

ops$tkyte%ORA10GR2> show parameter create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /home/ora10gr2/oradata/ora10gr
                                                 2
ops$tkyte%ORA10GR2> create tablespace test;

Tablespace created.

Hi Tom,

I have been searching for pros and cons to having a large
number of datafiles. We are currently at 3200 datafiles,
each with a MAXSIZE of 8GB (most have already attained the
8GB). In order to increase our tablespaces, we have 2 options:

o Add datafiles
o increase the maxsize of existing datafiles

We are concerned that 3200 files is getting too large and
are leaning towards the second option.

Do you have an opinion or a link that can list the pros
and cons? I'm guessing things like the checkpoint process
will be affected.

Thanks TOm

Hello,

I have one tablespace with 200 datafiles, in which one datafile is in autoextend on mode. My tablespace size is 600gb which is actual size as per dba_data_files, i daily monitor free_space in tablespace using dba_free_space, i am surprised i already have 40 gb free space in tablespace which is decreasing daily by 200 mb but also my actual tablespace size which is 600gb is increasing after 2 or 3 days around 500mb. As i think once actual size will get filled then only whole tablespace size should increase, but here after 2 or 3 days whole tablespace size is increasing as well as daily free space is decreasing.

Please guide, i am new in this.

Thanks,
Ashish

I am on Exadata and Database 18.5

I have tablespaces created as bigfile initial 20G next 256G maxsize 20T

Since 2019 we have had 5 occurrences of the tablespace extending to the 20T limit. The average usage is around 400G

Any idea on how to find out what the circumstances are when the tablespace extension goes wild?

Regards

Hi Tom, Great site, and great info.

If the current table space for Users01 is 31G
and the current tablespace for Users02 is 31G
and I run a script to extend the table space as below, will this change the tablespace from 31G to 250M?

ALTER TABLESPACE users
ADD DATAFILE 'E:\ORACLE\ORADATA\BEEBOP\USERS01.DBF' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;


ALTER TABLESPACE users
ADD DATAFILE 'E:\ORACLE\ORADATA\BEEBOP\USERS02.DBF' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;

 ALTER TABLESPACE users
ADD DATAFILE 'E:\ORACLE\ORADATA\BEEBOP\USERS03.DBF' SIZE 10M
AUTOEXTEND ON
NEXT 100M
MAXSIZE 32G;