Home » RDBMS Server » Server Administration » Empty Datafile (ORACLE 12.1.0.2.0 Standard Edition)
Empty Datafile [message #681762] Thu, 27 August 2020 08:14 Go to next message
wintert
Messages: 3
Registered: August 2020
Junior Member
Hello!

I'm new here and hope for help Smile

We have a tablespace with two data files.
The first datafile is 12GB and 12GB of free space is reported.
The second datafile is 8GB in size and keeps filling up.
We don't understand why the first one isn't filled.
Could the problem be solved by exporting / importing the tablespace so that the data is again neatly written into the first datafile?
Or could we just delete the first datafile?
We would be very grateful for possible solutions.

Best wishes
Tina
Re: Empty Datafile [message #681765 is a reply to message #681762] Thu, 27 August 2020 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67572
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You cannot remove a data file from the tablespace.
Are you sure it is empty and does not contain recycle bin objects?

There were/are 2 basic Oracle algorithms to fill the files. The (very) old one was to (try to) fill the files equally. The second one is to fill one file and, if not possible (because it is full for instance), allocate space in the second one.

Re: Empty Datafile [message #681767 is a reply to message #681762] Thu, 27 August 2020 10:26 Go to previous messageGo to next message
EdStevens
Messages: 1285
Registered: September 2013
Senior Member
What, exactly, do you mean by "keeps filling up"? Do you have a problem, or merely an observation? Any actual error messages to report?
Re: Empty Datafile [message #681819 is a reply to message #681765] Wed, 02 September 2020 06:15 Go to previous messageGo to next message
wintert
Messages: 3
Registered: August 2020
Junior Member
Thanks for your replies! Smile

Michel Cadot wrote on Thu, 27 August 2020 10:02

Are you sure it is empty and does not contain recycle bin objects?

There were/are 2 basic Oracle algorithms to fill the files. The (very) old one was to (try to) fill the files equally. The second one is to fill one file and, if not possible (because it is full for instance), allocate space in the second one.

In the Oracle EM Express the size is given as 12gb and the free space is also 12gb. So it should be empty right?
In all other tablepaces the datafiles are filled correctly, one after the other.

Michel Cadot wrote on Thu, 27 August 2020 10:02

What, exactly, do you mean by "keeps filling up"? Do you have a problem, or merely an observation? Any actual error messages to report?
No problem, just an observation. It's normal behavior, but I don't understand why the first data file remains empty.
We regularly copy the tablespace for a test environment. The data files are correctly filled there.

Re: Empty Datafile [message #681820 is a reply to message #681819] Wed, 02 September 2020 07:31 Go to previous messageGo to next message
EdStevens
Messages: 1285
Registered: September 2013
Senior Member
wintert wrote on Wed, 02 September 2020 06:15
Thanks for your replies! Smile

Michel Cadot wrote on Thu, 27 August 2020 10:02

What, exactly, do you mean by "keeps filling up"? Do you have a problem, or merely an observation? Any actual error messages to report?
No problem, just an observation. It's normal behavior, but I don't understand why the first data file remains empty.
We regularly copy the tablespace for a test environment. The data files are correctly filled there.

Well, the term 'keeps filling up' implies a repeated action .. that the file fills up, then somehow becomes 'not filled up', then fills up again, then somehow becomes not filled up. Trying to understand what's going on here.
Re: Empty Datafile [message #681833 is a reply to message #681820] Thu, 03 September 2020 01:16 Go to previous messageGo to next message
wintert
Messages: 3
Registered: August 2020
Junior Member
The problem affects the first datafile. That is not filled and seems empty.
The data is only written to the second datafile. That's what I meant by 'keeps filling up'.

I'm sorry if my writing is confusing. My English is not the best.

I have attached a screenshot from EM Express in which you can see the tablespace and the use of space from the data files:

[Updated on: Thu, 03 September 2020 01:17]

Report message to a moderator

Re: Empty Datafile [message #681835 is a reply to message #681833] Thu, 03 September 2020 04:10 Go to previous messageGo to next message
John Watson
Messages: 8431
Registered: January 2010
Location: Global Village
Senior Member
You do seem to be seeing the behaviour that MC describes:
Quote:
There were/are 2 basic Oracle algorithms to fill the files. The (very) old one was to (try to) fill the files equally. The second one is to fill one file and, if not possible (because it is full for instance), allocate space in the second one.
What is not clear is which file is considered to be the "first" and which file is "second". Of course if you allocate extents manually, you can choose which datafile is used.

I would usually not worry about this at all, because nowadays I (nearly) always use bigfile tablespaces.
Re: Empty Datafile [message #681837 is a reply to message #681833] Thu, 03 September 2020 11:13 Go to previous message
EdStevens
Messages: 1285
Registered: September 2013
Senior Member
It appears that you have an observation, not a problem. Has the first datafile ever actually "filled up" -- as in "reached maximum capacity"? If so, what happened next? If not, then like I said, you have an observation, not a problem. When the data file actually does fill up - actually reaches maximum capacity and can allocate no more extents, oracle should start using the other data file. That is by design.
Previous Topic: UPDATE taking a long time, indication disappeared
Next Topic: Temp Tablespace Issue somehow fixed SQL advisor
Goto Forum:
  


Current Time: Sat Dec 05 06:38:07 CST 2020