tablespaces & datafiles??? Which to increase..

[Ratzie]

I am still trying to get a handle on this oracle database. 8.1.7.4

We have a script that notifies up if our tablespace is getting low. I understand the temp tablespace, and it basically clears its self on restart of database.
But, we have the user tablespace filling up, and what I am confused about is what gets increased.

Can I just increase the tablespace?
Or do I have to add another datafile? Or both?
I have to bring the database offline...

FILE_NAME:/opt/app/oracle/oradata/acdb/db1/users01.dbf
TABLESPACE_NAME:USERS
BYTES: 209715200
BLOCKS: 25600
STATUS: AVAILABLE
AUTOEXTENSIBLE: NO
USER_BYTES: 209674240
USER_BLOCKS: 25595

If the tablespace space (or datafile) is autoextendable, what implications would this have. Would the script always be alarming because the datafile is extending only when needed and would not drop below 80%.

[Jean-Luc Oudart]

Hi

you can allow the datafile to autoextend.
You will have to specify the maximum size and the increment size.

the view is DBA_DATA_FILES.

Obviously, as you set a maximum size you still have to monitor it.
if the file will become large, ensure you allow large files in your file systems

At some stage you may want to add another datafile to your tablespace.
But 200Mb is not a big file.

Regards
Jean-Luc

[Patti Johnson · Accepted solution]

The tablespace is made up of one or more datafiles, you can either extend an existing datafile or add another datafile.
I normally decide on a maximum size for each datafile when it is created and enable autoextend with a maxsize that does not exceed what is available on the filesystem. Unlimited autoextend can get you in trouble if you have a run away process.

My approach is to have at least one datafile per tablespace that has autoextensible = YES, then I monitor the dba_data_files.MAXBYTES vs. data_data_file.bytes and ensure that each tablespace has some amount of growth left.
Using this approach rather than the 80% used will keep you from getting unnecessary alerts if the data files can still grow.

You will then need to monitor filesystem usage at the OS level to ensure that files the Oracle allows to extend have room to extend.

Patti

[Jean-Luc Oudart]

You can check these notes on Metalink for further information :
Metalink Note id 267888.1 "Space Management and Object Storage Knowledge Browser Product Page" (pointer to different documents)

Metalink doc id 332681.1 " How to Restrict Size of the Datafile With Autoextend Enabled "

Regards
Jean-Luc

[TwoProc]

Your user data file is already at about 2G. I would add another file. I don't like files growing over 2G as tar, cpio, and lots of other things don't like them much. Also, just reviewing the number of problems you see with autoextend and file sizes over 2G would convince you not to mix BOTH at the same time.

So, I do use autoextend on the "last" file(I sometimes have more than one "last" file) of a tablespace (like mentioned in a previous posting), just as a safety valve. However, I recommend that you keep the allocation sizes ahead of the growth, so that your user programs don't have a performance penalty of having to deal with autoextend maintenance as things progress. Autoextend is absolutely great, and I recommend using it as a safety valve, but not as the way to consistently run the database. Lots of folks will disagree with me on this for a lot of valid reasons. It's a preferential thing that I use to avoid performance issues, unexpected shutdown issues, and possible data loss issues all at once.

Make sure that you've got room on an Oracle drive somewhere and add it.

The example below will add a second datafile to the users tablespace, size 4M autoextending by 4M each time until just under 2G.

ALTER TABLESPACE "USERS" ADD DATAFILE '/opt/app/oracle/oradata/acdb/db1/users02.dbf ' SIZE 4096K REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 1996M;

It will be available for use as soon as it finishes creating the file.

Of course, like I said, don't let it autoextend all the time, keep ahead of the current need by 32M,64M,96M or whatever you think you need based on watching the growth of the thing over time. Keep in mind that you don't want the thing too cut up all over the file system, so if it is growing quickly,
go ahead and commit to giving it very large chunks to grow in (unless a purge is coming up soon).

[Yogeeraj_1]

hi,

question 1: Are you using locally managed tablespaces (LMTs)?

If yes, then you should be less worried about the size of the tablespace but more about the available space on the file system where the datafile lies.

Here is how it works!
You create a tablespace as follows:
CREATE TABLESPACE MY_TBS
DATAFILE '/oracle/oradata/mydb/lmt_small01.dbf' SIZE 300M AUTOEXTEND ON NEXT 1M MAXSIZE 1024M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

Here you will be creating a tablespace of size 300M allowing it to extend to a maximum of 1GB. Also, when the datafile reaches 300MB, it will extend in chunks of 1MB... till the size of the datafile reaches 1GB. Here again, you have the possibility to add more datafiles to the tablespace accordingly.

Hence, even if the size reaches 100%, you should not be too worried about it...

Also, when creating tables, you would not have to specify storage options... Just the tablespace name and the pctfree/pctused parameters.

This is the main advantage of using LMTs.

hope this helps!

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

below the advantages of using LMTs over DMTs:
*****************************************************************************************
***ADVANTAGES IN USING LOCALLY MANAGED TABLESPACES OVER DICTIONARY MANAGED TABLESPACES***
*****************************************************************************************

- Because locally managed tablespaces do not record free space in data dictionary, it reduces contention on these tables.

- Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.

- Avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.

- Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.

- Changes to the extent bitmaps do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

- Reduced fragmentation

hope this helps too!

kind regards
yogeeraj

[Ratzie]

Thanks for the help