ORA-00740: datafile size of (53687041280) blocks exceeds maximum file size

Toad for Oracle
1

Hi,

I am having problems trying to re-size tablespaces. I run the calculator tool, but then whenever I try to re-size I get the following error;

ORA-00740: datafile size of (53687041280) blocks exceeds maximum file size

Regardless of the size of the tablespace.

I have recently upgraded to the latest version of Toad - has anyone else run into this problem?

Thanks!

2

Hi Steven,

Happens to me also. It works OK in 11.6.1. Must be a bug.

Alan.

3

Can I see a screen shot of the resize datafile window before the error occurs, along with a spool SQL of what SQL Toad is attempting to execute?

4

Spooled SQL

Timestamp: 09/07/2013 15:14:05

declare cursor NlsParamsCursor is

MaxCharLength=[1]

MinCharLength=[1]

NlsDateLanguage=[

NlsDateFormat=[

NlsNumericCharacters=[

NlsTimeStampFormat=[

NlsTimeStampTZFormat=[

Elapsed time: 0.013

Timestamp: 09/07/2013 15:14:05

select

Describe Only

Timestamp: 09/07/2013 15:14:06

select

Describe Only

Timestamp: 09/07/2013 15:14:06

select

Elapsed time: 0.002

Timestamp: 09/07/2013 15:14:06

select

Describe Only

Timestamp: 09/07/2013 15:14:06

select

Describe Only

Timestamp: 09/07/2013 15:14:06

select

Elapsed time: 0.003

SELECT * FROM nls_session_parameters;begin SELECT Nvl(Lengthb(Chr(16777216)), Nvl(Lengthb(Chr(65536)), Nvl(Lengthb(Chr(256)), 1))), Nvl(Lengthb(Chr(1)), 1) INTO :MaxCharLength, :MinCharLength FROM dual; for NlsRecord in NlsParamsCursor loop if NlsRecord.parameter = ‘NLS_DATE_LANGUAGE’ then :NlsDateLanguage := NlsRecord.value; elsif NlsRecord.parameter = ‘NLS_DATE_FORMAT’ then :NlsDateFormat := NlsRecord.value; elsif NlsRecord.parameter = ‘NLS_NUMERIC_CHARACTERS’ then :NlsNumericCharacters := NlsRecord.value; elsif NlsRecord.parameter = ‘NLS_TIMESTAMP_FORMAT’ then :NlsTimeStampFormat := NlsRecord.value; elsif NlsRecord.parameter = ‘NLS_TIMESTAMP_TZ_FORMAT’ then :NlsTimeStampTZFormat := NlsRecord.value; end if; end loop;end;‘AMERICAN’]‘DD-MON-RR’]’.,’]‘DD-MON-RR HH.MI.SSXFF AM’]‘DD-MON-RR HH.MI.SSXFF AM TZR’]**** PARAMETER,VALUE from nls_session_parameters where PARAMETER in(‘NLS_NUMERIC_CHARACTERS’,‘NLS_DATE_FORMAT’,‘NLS_CURRENCY’)******** PARAMETER,VALUE from nls_session_parameters where PARAMETER in(‘NLS_NUMERIC_CHARACTERS’,‘NLS_DATE_FORMAT’,‘NLS_CURRENCY’)******** PARAMETER,VALUE from nls_session_parameters where PARAMETER in(‘NLS_NUMERIC_CHARACTERS’,‘NLS_DATE_FORMAT’,‘NLS_CURRENCY’)******** to_char(9,‘9C’) from dual******** to_char(9,‘9C’) from dual******** to_char(9,‘9C’) from dual

5

Oh, I see, you are using the ‘calculate’ button followed by ‘shrink to minimum MB’.

I didn’t realize that before. I can reproduce the problem and will fix it. In the meantime, they can double-click on a row and shrink it in the dialog that
appears.

-John

From: alancounihan [mailto:bounce-alancounihan@toadworld.com]

Sent: Tuesday, July 09, 2013 9:20 AM

To: toadoracle@toadworld.com

Subject: Re: [Toad for Oracle - Discussion Forum] ORA-00740: datafile size of (53687041280) blocks exceeds maximum file size

Re: ORA-00740: datafile size of (53687041280)
blocks exceeds maximum file size

Reply by alancounihan

Spooled SQL

--------------------------------------------------------------------------------

Timestamp: 09/07/2013 15:14:05

declare cursor NlsParamsCursor is

SELECT *
FROM nls_session_parameters;begin
SELECT Nvl(Lengthb(Chr(16777216)), Nvl(Lengthb(Chr(65536)), Nvl(Lengthb(Chr(256)), 1))), Nvl(Lengthb(Chr(1)), 1)
INTO :MaxCharLength, :MinCharLength FROM dual;
for NlsRecord in NlsParamsCursor loop
if NlsRecord.parameter =
‘NLS_DATE_LANGUAGE’ then :NlsDateLanguage := NlsRecord.value; elsif NlsRecord.parameter =
‘NLS_DATE_FORMAT’ then :NlsDateFormat := NlsRecord.value; elsif NlsRecord.parameter =
‘NLS_NUMERIC_CHARACTERS’ then :NlsNumericCharacters := NlsRecord.value; elsif NlsRecord.parameter =
‘NLS_TIMESTAMP_FORMAT’ then :NlsTimeStampFormat := NlsRecord.value; elsif NlsRecord.parameter =
‘NLS_TIMESTAMP_TZ_FORMAT’ then :NlsTimeStampTZFormat := NlsRecord.value;
end if;
end loop;end;

MaxCharLength=[1]

MinCharLength=[1]

NlsDateLanguage=[

‘AMERICAN’]

NlsDateFormat=[

‘DD-MON-RR’]

NlsNumericCharacters=[

‘.,’]

NlsTimeStampFormat=[

‘DD-MON-RR HH.MI.SSXFF AM’]

NlsTimeStampTZFormat=[

‘DD-MON-RR HH.MI.SSXFF AM TZR’]

Elapsed time: 0.013

--------------------------------------------------------------------------------

Timestamp: 09/07/2013 15:14:05

select

PARAMETER,VALUE
from nls_session_parameters where PARAMETER
in(‘NLS_NUMERIC_CHARACTERS’,‘NLS_DATE_FORMAT’,‘NLS_CURRENCY’)

Describe Only

--------------------------------------------------------------------------------

Timestamp: 09/07/2013 15:14:06

select

PARAMETER,VALUE
from nls_session_parameters where PARAMETER
in(‘NLS_NUMERIC_CHARACTERS’,‘NLS_DATE_FORMAT’,‘NLS_CURRENCY’)

Describe Only

--------------------------------------------------------------------------------

Timestamp: 09/07/2013 15:14:06

select

PARAMETER,VALUE
from nls_session_parameters where PARAMETER
in(‘NLS_NUMERIC_CHARACTERS’,‘NLS_DATE_FORMAT’,‘NLS_CURRENCY’)

Elapsed time: 0.002

--------------------------------------------------------------------------------

Timestamp: 09/07/2013 15:14:06

select

to_char(9,‘9C’)
from dual

Describe Only

--------------------------------------------------------------------------------

Timestamp: 09/07/2013 15:14:06

select

to_char(9,‘9C’)
from dual

Describe Only