OraFAQ Forum: Server Administration » Datafiles getting Full

Home » RDBMS Server » Server Administration » Datafiles getting Full

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Datafiles getting Full [message #206839]

Fri, 01 December 2006 13:17

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

Report message to a moderator

Re: Datafiles getting Full [message #206865 is a reply to message #206839]

Fri, 01 December 2006 16:36

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: Datafiles getting Full [message #206975 is a reply to message #206865]

Sat, 02 December 2006 22:22

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

Report message to a moderator

Re: Datafiles getting Full [message #207180 is a reply to message #206975]

Mon, 04 December 2006 08:01

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........

Report message to a moderator

Re: Datafiles getting Full [message #207182 is a reply to message #207180]

Mon, 04 December 2006 08:31

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

What are your extend settings of the tablespace?

Report message to a moderator

Re: Datafiles getting Full [message #207189 is a reply to message #207182]

Mon, 04 December 2006 09:32

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

Report message to a moderator

Re: Datafiles getting Full [message #207194 is a reply to message #206839]

Mon, 04 December 2006 09:59

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

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

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;

Report message to a moderator

Re: Datafiles getting Full [message #207215 is a reply to message #207205]

Mon, 04 December 2006 12:15

joy_division
Messages: 4963
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?

Report message to a moderator

Re: Datafiles getting Full [message #207370 is a reply to message #206839]

Tue, 05 December 2006 04:50

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.

Report message to a moderator

Previous Topic:	OracleXe and Visualstudio 2005
Next Topic:	Configuring OEM

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 06:59:58 CDT 2024