Red Gate forums :: View topic - Unable to extend temp?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Data Compare for Oracle
Data Compare for Oracle forum

Unable to extend temp?

Search in Data Compare for Oracle forum
Post new topic   Reply to topic
Jump to:  
Author Message
cmille19



Joined: 28 Jan 2014
Posts: 3

PostPosted: Tue Jan 28, 2014 3:45 pm    Post subject: Unable to extend temp? Reply with quote

Getting errors comparing a table with 250 million rows. The two tables are static (not being updating during time of compare). I'm a little confused why its using temp space. I've used the SQL Server product for 10 years and compare equivalent sized tables on SQL Server without issue.

Devart.Data.Oracle.OracleException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
at Devart.Data.Oracle.a7.b(Int32 A_0)
at Devart.Data.Oracle.a9.b(Int32 A_0)
at Devart.Data.Oracle.OracleDataReader.Read()
at RedGate.Oracle.DataCompare.DbDataReaderWrapperBase.Read()
at RedGate.Oracle.DataCompare.DataComparer.Compare()


System.InvalidOperationException: No data exists for the row/column.
at Devart.Common.DbDataReaderBase.AssertReaderHasData()
at Devart.Data.Oracle.OracleDataReader.GetValues(Object[] values)
at RedGate.Oracle.DataCompare.DbDataReaderWrapperBase.GetValues(Object[] values)

CURRENT_ROW_COMPARISON_RESULT
System.NullReferenceException: Object reference not set to an instance of an object.
at RedGate.Oracle.DataCompare.ComparisonException.CreateReport()

at RedGate.Oracle.DataCompare.ComparisonException.CreateReport()
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 925
Location: Red Gate Software

PostPosted: Wed Jan 29, 2014 5:00 pm    Post subject: Reply with quote

Thank you for your forum post.

To answer your question:
Quote:
Getting errors comparing a table with 250 million rows. The two tables are static (not being updating during time of compare). I'm a little confused why its using temp space.


Oracle creates temporary tablespaces which are used for sorting and hash join operations. A SQL query with millions of rows returned, the sort operation is too large to occur in memory (RAM) and must occur on disk. The temporary tablespace is where this takes place.

When the temporary tablespace has insufficient disk space allocated, Oracle error ORA-01652 is generated.

I recommend that you take a look at following internet articles which can explain temporary tablespaces in much more detail than I can:



I hope the above answers your question.

Many Thanks
Eddie
_________________
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
cmille19



Joined: 28 Jan 2014
Posts: 3

PostPosted: Wed Jan 29, 2014 5:16 pm    Post subject: Query? Reply with quote

We see that Red Gate is selecting all the columns of the table and doing an ORDER BY. The order by is what is causing the temp space issue. Two questions:

#1 Does your product have to do an ORDER BY? It seems like other data comparison products like Oracle Veridata does the sort through app. Not sure if the your SQL Server product does sorting on database also.

#2 We are comparing two 250 million rows tables in the same database, but different schemas. Does your product execute both source and target queries at same time or is it retrieving source rows first and then target? I ask because we increased temp size to large than the table, but still ran out of space. Wondering if we need space for both.
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 925
Location: Red Gate Software

PostPosted: Wed Feb 12, 2014 5:04 pm    Post subject: Reply with quote

Thank you for your patience whilst we continued to investigate your questions and sorry for deley in replying back to you.

To answer your questions:
Quote:

#1 Does your product have to do an ORDER BY? It seems like other data comparison products like Oracle Veridata does the sort through app. Not sure if the your SQL Server product does sorting on database also.

Yes it uses ORDER BY, data comparison is possible without it but I believe you would have to pull down all the data, then sort, then compare. The database is better at sorting than Data Compare for Oracle pretty much all of the time (it should have indexes it can use, maybe it has done the sort before and cached the result etc.).


Quote:

#2 We are comparing two 250 million rows tables in the same database, but different schemas. Does your product execute both source and target queries at same time or is it retrieving source rows first and then target? I ask because we increased temp size to large than the table, but still ran out of space. Wondering if we need space for both.

Yes it executes both queries at the same time. We can then compare them one row at a time because the ORDER BY has already been applied.

Many Thanks
Eddie
_________________
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group