timallen
Well Known Member
(OneWorld Xe, Oracle 8.1.7, W2K opn DS/ES)
Our client was getting a lot of errors trying to insert new data. As it turns out, the tablespace for PRODDTAT had a single datafile with its MAXBYTES set to 1GB. The file was exactly 1GB, so no extension was possible.
I detected this with this Oracle Query which tells me all tablespaces which have a data file at over 50% its capacity:
select
substr(FILE_NAME, 1, 45),
substr(TABLESPACE_NAME, 1, 20),
BYTES,
MAXBYTES
from dba_data_files
where tablespace_name in
(
select tablespace_name
from dba_data_files
where bytes > (maxbytes * .5)
)
order by tablespace_name
(Note that the sub-query is necessary so we see all of the datafiles for a given tablespace-- just because a tablespace has one full datafile doesn't mean that it can't extend itself).
Then I fixed the PRODDTAT tablespace with this command:
alter tablespace PRODDTAT
add datafile 'e:\orajde\prodx.dbf' size 500M
autoextend on next 200K
maxsize unlimited
My big question here is, why is the production data tablespace set to max out at 1GB with the original installation? Did I miss a step in the installation process that is specified in the installation manual? 1GB seems like very little-- does Oracle perform better if this is set to a fixed limit?
My other question is, does anyone know if this is an issue in SQL Server as well? I've got a SQL Server client who I want to try this out on to make sure they don't have trouble down the road.
Thanks in advance.
Our client was getting a lot of errors trying to insert new data. As it turns out, the tablespace for PRODDTAT had a single datafile with its MAXBYTES set to 1GB. The file was exactly 1GB, so no extension was possible.
I detected this with this Oracle Query which tells me all tablespaces which have a data file at over 50% its capacity:
select
substr(FILE_NAME, 1, 45),
substr(TABLESPACE_NAME, 1, 20),
BYTES,
MAXBYTES
from dba_data_files
where tablespace_name in
(
select tablespace_name
from dba_data_files
where bytes > (maxbytes * .5)
)
order by tablespace_name
(Note that the sub-query is necessary so we see all of the datafiles for a given tablespace-- just because a tablespace has one full datafile doesn't mean that it can't extend itself).
Then I fixed the PRODDTAT tablespace with this command:
alter tablespace PRODDTAT
add datafile 'e:\orajde\prodx.dbf' size 500M
autoextend on next 200K
maxsize unlimited
My big question here is, why is the production data tablespace set to max out at 1GB with the original installation? Did I miss a step in the installation process that is specified in the installation manual? 1GB seems like very little-- does Oracle perform better if this is set to a fixed limit?
My other question is, does anyone know if this is an issue in SQL Server as well? I've got a SQL Server client who I want to try this out on to make sure they don't have trouble down the road.
Thanks in advance.