Skip to Main Content
  • Questions

  • Why is my freespace not being used in a tablespace?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jason.

Asked: March 19, 2021 - 11:20 am UTC

Last updated: March 23, 2021 - 8:58 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi

I have a tablespace, which until this morning had 2 datafiles. I just got a call to say that that a ORA-01653 had been received against that tablespace.

Both datafiles were set to grow unlimited (32GB) and there was still 12GB free usable between the 2 datafiles. I confirmed this by running free.sql. Even so, the ORA-01653 was still being received and was only alleviated once a 3rd data file was added. So my warning mail didnt trigger - the tablespace was only 80% used and the threshold for a mail is 85%.

Any idea why this would be? Something straightforward I am missing I am sure but cant see what.

Thanks

Jason

CREATE TABLESPACE XXACS_SHIPEZ DATAFILE 
   SIZE 32762M AUTOEXTEND ON NEXT 128K MAXSIZE UNLIMITED,
   SIZE 32764M AUTOEXTEND ON NEXT 128K MAXSIZE UNLIMITED,
   SIZE 1024M AUTOEXTEND ON NEXT 128K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

+DATA/RACP/DATAFILE/xxacs_shipez.453.1037291049 191 75 % 32 GB 23.9 GB 8.1 GB 4193536 True 128 KB UNLIMITED 0.44
+DATA/RACP/DATAFILE/xxacs_shipez.458.1054981407 196 87 % 32 GB 27.9 GB 4.06 GB 4193792 True 128 KB UNLIMITED 0.72
+DATA/RACP/DATAFILE/xxacs_shipez.466.1067565573 204 11 % 1 GB 114 MB 910 MB 131072 True 128 KB UNLIMITED 83.81

and Chris said...

There are various operations that could try to grab lots of space and release it all back if they fail. Here are a couple of examples:

- Create-table-as-select
- Moving a table

create tablespace smallts
  datafile size 10m;
  
select bytes from dba_free_space
where  tablespace_name = 'SMALLTS';

BYTES     
   9437184 
  
create table t (
  c1, c2 
) tablespace smallts as
with rws as (
  select level x from dual
  connect by level <= 2000
)
  select x, lpad ('x', 4000, 'x') from rws;
  
ORA-01652: unable to extend temp segment by 128 in tablespace SMALLTS

select bytes from dba_free_space
where  tablespace_name = 'SMALLTS';

BYTES     
   9437184 

create table t (
  c1, c2 
) tablespace smallts as
with rws as (
  select level x from dual
  connect by level <= 500
)
  select x, lpad ('x', 4000, 'x') from rws;
  
select bytes from dba_free_space
where  tablespace_name = 'SMALLTS';

BYTES     
   4194304 
  
alter table t move;

ORA-01652: unable to extend temp segment by 128 in tablespace SMALLTS

select bytes from dba_free_space
where  tablespace_name = 'SMALLTS';

BYTES     
   4194304


I'm sure there are many more. So you'll need to go back and review what exactly was happening when the alert was triggered.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Resolved - Oracle changed behaviours after 10g

Jason, March 23, 2021 - 11:28 am UTC

Hello

After much searching on MOS, I found an applicable note - 1539699.1.

Additional confusion came from the fact that some instances displayed the behaviour and others didnt.
I have been able to explain that by the fact those instances that were originally created on < 11g and then were upgraded, DONT show the behaviour whilst those created on 11g (or greater) do display the behaviour.

Testing also indicates that the fix / workaround detailed in the MOS note also works.

Not a really well documented change for 11G and beyond IMO.

Thanks for looking at the issue anyway.

Jason
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database