exracting huge data from remote database [message #387722] |
Fri, 20 February 2009 07:08  |
deepbans
Messages: 32 Registered: February 2009
|
Member |
|
|
Hi,
I am accessing a huge amount of data from the the remote database tables.We have only select previliges on those tables.Most of the tables are updated very frequently.
whn i run the query for huge amount of data.its give snapshot too old error...as the tables that i am accessing are updated very frequently..
Is there any way to store the records on my database and access all the information from here only..
|
|
|
|
|
|
Re: exracting huge data from remote database [message #387732 is a reply to message #387722] |
Fri, 20 February 2009 07:26   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If you don't have permission to do anything other than select data I'm really not sure what you expect us to do?
If selecting huge chunks of data is too difficult then you need an alternative approach - which'll required an ability to do more than just select data.
|
|
|
|
|
|
|
|
Re: exracting huge data from remote database [message #388027 is a reply to message #388023] |
Mon, 23 February 2009 00:52   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
deepbans wrote on Mon, 23 February 2009 07:48 | No,we cant wait ..the request we are giving is online and anybody can use it anytime.
|
This does not really make sense. Your query takes a long time (otherwise the chances for a SNAPSHOT_TOO_OLD are low), yet you suggest that it is a synchronous service.
Who would wait for that?
|
|
|
Re: exracting huge data from remote database [message #388029 is a reply to message #388023] |
Mon, 23 February 2009 00:56   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
Ask your DBA to increase undo tablespace. This is a temporary solution and you might face same error even after that. For complete solution wait till batch is complete.
The main error is
ERROR:
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$" too small
Thanks
Trivendra
|
|
|
|
|
Re: exracting huge data from remote database [message #388036 is a reply to message #388030] |
Mon, 23 February 2009 01:04   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Since you did not provide any explanation about your application, I will assume that you have a set of fields that people can enter to execute a search
Minimize query execution time by prohibiting big resultsets.
- For example, require at least <n> fields to be entered
- Require 3 characters before any wildcard
- Rewrite case-insensitive query logic
In short: make sure indexes are used where appropriate and make sure the resultsets are small.
|
|
|
|
Re: exracting huge data from remote database [message #388044 is a reply to message #388036] |
Mon, 23 February 2009 01:11   |
deepbans
Messages: 32 Registered: February 2009
|
Member |
|
|
The query I am generated is dynamic query..and the result set of the query depands on the selection criteria..
when selection is in big range ..then it can select lakh number of records.
Indexes are working fine...one problem is we dont have any previleges on the remote databse except SELECT..
I am trying to split the query and keepin some data in global temporary table.
Will this solution work?
|
|
|
|
|
|
|
|
|
Re: exracting huge data from remote database [message #388134 is a reply to message #388064] |
Mon, 23 February 2009 07:09   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well, if you can't change the query, and you can't create indexes, and you can't change the parameters on the remote database, and you can't create matrialized view logs....
What exactly do you think we can do to help?
You've got some queries that you can't alter, to run in an environment that you can't change - there's not a lot of room for manouver there.
How stale can the data be?
If you can live with data that's (say) and hour old then you could create a local table with the results of the query, run your selects against that, and refresh it every hour.
|
|
|
Re: exracting huge data from remote database [message #388139 is a reply to message #388134] |
Mon, 23 February 2009 07:18   |
deepbans
Messages: 32 Registered: February 2009
|
Member |
|
|
See,
I can change my qurey..thats not a problem..The thing is I cant play with remote database tables..(like creating index etc..we dont own those tables)
I am sending a long query to remote database to fetch the data.
This fetching is taking long time in execution..The remote database tables changes very frequently.
In the meanwhile my records are fetched..the tables got updated
and i am getting snapshot old error...
|
|
|
|