Home » RDBMS Server » Server Administration » Datafiles getting Full
Datafiles getting Full [message #206839] Fri, 01 December 2006 13:17 Go to next message
Alps
Messages: 28
Registered: November 2006
Location: Toronto
Junior Member
I am working on Oracle 7.3.4.4 on NT platform and I notice that without much utilization, I mean not much data put into the datafiles but its always going out of space and we have to do reorg. Can somebody help me what needs to be taken care or some tips to improve the space utilization
Thanks
Re: Datafiles getting Full [message #206865 is a reply to message #206839] Fri, 01 December 2006 16:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> I mean not much data put into the datafiles but its always going out of space and we have to do reorg.
Which datafiles are you talking about here?
Is that a regular datafile or a file used for temp tablespace?
Is the "autoextend on" feature enabled for those datafiles?
And
>>I am working on Oracle 7.3.4.4
If this database is somewhat worth, look into upgrading it.

Re: Datafiles getting Full [message #206975 is a reply to message #206865] Sat, 02 December 2006 22:22 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hello

if it is temp file so don't take in tension. just relax.

drop and recreate with disable autoextend option.



hope this helps
Taj




Re: Datafiles getting Full [message #207180 is a reply to message #206975] Mon, 04 December 2006 08:01 Go to previous messageGo to next message
Alps
Messages: 28
Registered: November 2006
Location: Toronto
Junior Member
Its not a temp file and rightnow my company is not looking for any upgradation method. Is there anything which I can take a look and try to look whats going wrong. Not much data is put but still getting full........
Re: Datafiles getting Full [message #207182 is a reply to message #207180] Mon, 04 December 2006 08:31 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
What are your extend settings of the tablespace?
Re: Datafiles getting Full [message #207189 is a reply to message #207182] Mon, 04 December 2006 09:32 Go to previous messageGo to next message
Alps
Messages: 28
Registered: November 2006
Location: Toronto
Junior Member
This is the default storage for tables
DEFAULT STORAGE (
INITIAL 256K
NEXT 256K
MINEXTENTS 2
MAXEXTENTS UNLIMITED
PCTINCREASE 1
)
ONLINE
PERMANENT;

This is the default storage for Indexes
DEFAULT STORAGE (
INITIAL 256K
NEXT 256K
MINEXTENTS 2
MAXEXTENTS 999
PCTINCREASE 1
)
ONLINE
PERMANENT
Re: Datafiles getting Full [message #207194 is a reply to message #206839] Mon, 04 December 2006 09:59 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
Quote:
I mean not much data put into the datafiles but its always going out of space and we have to do reorg.


What is "not much data" and what is "out of space". What are the sizes?

Ps: your initial, next and increase parameters seem normal.

Kr
Karel

[Updated on: Mon, 04 December 2006 09:59]

Report message to a moderator

Re: Datafiles getting Full [message #207195 is a reply to message #207189] Mon, 04 December 2006 10:03 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
I see you've given me the storage parameters for your table, I would like to see them for your tablespace

[Updated on: Mon, 04 December 2006 10:04]

Report message to a moderator

Re: Datafiles getting Full [message #207205 is a reply to message #207195] Mon, 04 December 2006 10:30 Go to previous messageGo to next message
Alps
Messages: 28
Registered: November 2006
Location: Toronto
Junior Member
CREATE TABLESPACE BILLDATA DATAFILE
'E:\xxx\xxxx' SIZE 2000M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M,
'E:\xxx\xxxxT2.DBF' SIZE 2000M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M,
'E:\xxx\xxxx3.DBF' SIZE 2000M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M,
'E:\xxx\xxxxT4.DBF' SIZE 1995M AUTOEXTEND ON NEXT 1M MAXSIZE 2000M,
'E:\xxx\xxxx5.DBF' SIZE 1995M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M,
'E:\xxx\xxxx6.DBF' SIZE 2000M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M,
'E:\xxx\xxxx7.DBF' SIZE 1998M AUTOEXTEND ON NEXT 1M MAXSIZE 2000M,
'E:\xxx\xxxx8.DBF' SIZE 2000M AUTOEXTEND ON NEXT 1M MAXSIZE 2000M,
'E:\xxx\xxxx9.DBF' SIZE 2000M AUTOEXTEND ON NEXT 1M MAXSIZE 1000M,
'E:\xxx\xxxx9A.DBF' SIZE 2000M AUTOEXTEND ON NEXT 1M MAXSIZE 2000M,
'E:\xxx\xxxx9B.DBF' SIZE 1000M AUTOEXTEND ON NEXT 1M MAXSIZE 1000M,
'E:\xxx\xxxx9C.DBF' SIZE 1836M AUTOEXTEND ON NEXT 124M MAXSIZE UNLIMITED
DEFAULT STORAGE (
INITIAL 256K
NEXT 256K
MINEXTENTS 2
MAXEXTENTS UNLIMITED
PCTINCREASE 1
)
ONLINE
PERMANENT;
Re: Datafiles getting Full [message #207215 is a reply to message #207205] Mon, 04 December 2006 12:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Why don't you look in DBA_SEGMENTS and see what's taking up all the space?
Re: Datafiles getting Full [message #207370 is a reply to message #206839] Tue, 05 December 2006 04:50 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I prefer autoextend off, So that I have control.

You need to find which transactions use your space.

As joy_division suggested, Take snapshots of DBS_SEGMENTS at intervals and you can see the growth.

Brian.
Previous Topic: OracleXe and Visualstudio 2005
Next Topic: Configuring OEM
Goto Forum:
  


Current Time: Sun Dec 04 02:37:07 CST 2016

Total time taken to generate the page: 0.09860 seconds