Home » SQL & PL/SQL » SQL & PL/SQL » exracting huge data from remote database (10g)
exracting huge data from remote database [message #387722] Fri, 20 February 2009 07:08 Go to next message
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 #387724 is a reply to message #387722] Fri, 20 February 2009 07:10 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
materialised view might be what you need.
Re: exracting huge data from remote database [message #387729 is a reply to message #387724] Fri, 20 February 2009 07:16 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
We need to create materiazed for creating materialized view..
But I am not able to create materialized view on remote database tables...
Re: exracting huge data from remote database [message #387731 is a reply to message #387729] Fri, 20 February 2009 07:25 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
We need to create materiazed for creating materialized view
Excuse me?

Quote:
But I am not able to create materialized view on remote database tables
Why not?
Re: exracting huge data from remote database [message #387732 is a reply to message #387722] Fri, 20 February 2009 07:26 Go to previous messageGo to next message
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 #387734 is a reply to message #387732] Fri, 20 February 2009 07:35 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
sorry..

I wanted to say I need to create a materialized view log for the remote database tables.

but materialized view log i cant create.

Its giving me illegal reference to remote database error...
Re: exracting huge data from remote database [message #387740 is a reply to message #387734] Fri, 20 February 2009 07:50 Go to previous messageGo to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
Is some Batch Running parallel on your remote database?

[Updated on: Fri, 20 February 2009 07:51]

Report message to a moderator

Re: exracting huge data from remote database [message #387742 is a reply to message #387740] Fri, 20 February 2009 07:52 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
Ya..Batch running on our remote databse..

But my query is totally indepandent of remote database jobs...
Re: exracting huge data from remote database [message #387743 is a reply to message #387742] Fri, 20 February 2009 07:54 Go to previous messageGo to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
This is the main cause of error. Wait for Batch to complete and then try.

[Updated on: Fri, 20 February 2009 07:55]

Report message to a moderator

Re: exracting huge data from remote database [message #388023 is a reply to message #387743] Mon, 23 February 2009 00:48 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
No,we cant wait ..the request we are giving is online and anybody can use it anytime.
Re: exracting huge data from remote database [message #388027 is a reply to message #388023] Mon, 23 February 2009 00:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #388030 is a reply to message #388027] Mon, 23 February 2009 00:58 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
Thats true query is taking long time in execution thats why giving snapshot too old error...
My question is this only..

How can i minimize the query execution time?

And all the tables are on remote database...
Re: exracting huge data from remote database [message #388033 is a reply to message #388030] Mon, 23 February 2009 00:59 Go to previous messageGo to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
What kind of queries are they.

1. Simple SELECT.
2. Complex queries

Thanks
Trivendra
Re: exracting huge data from remote database [message #388036 is a reply to message #388030] Mon, 23 February 2009 01:04 Go to previous messageGo to next message
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 #388039 is a reply to message #388033] Mon, 23 February 2009 01:05 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
No its not simple select..
Its contain 17 remote databse table with joins(outer join also)
Re: exracting huge data from remote database [message #388044 is a reply to message #388036] Mon, 23 February 2009 01:11 Go to previous messageGo to next message
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 #388046 is a reply to message #387722] Mon, 23 February 2009 01:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sure, yes, no, maybe
What are you ranting about anyways?
Re: exracting huge data from remote database [message #388049 is a reply to message #388044] Mon, 23 February 2009 01:15 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Storing intermediate results will (in general) only take longer.
Why do you need dynamic sql? It makes it difficult to tune.
Re: exracting huge data from remote database [message #388050 is a reply to message #388046] Mon, 23 February 2009 01:15 Go to previous messageGo to next message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
I hope this can help,

http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

Thanks
Trivnedra
Re: exracting huge data from remote database [message #388054 is a reply to message #388049] Mon, 23 February 2009 01:24 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
To make dynamic query is the requirement of application..We cant avoid thid..
I am trying to store the intermsedite result at my local databse..
Re: exracting huge data from remote database [message #388057 is a reply to message #387722] Mon, 23 February 2009 01:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Nothing is impossible for the person who does not have to do it.
Ask those who set your rules to provide a solution.
Re: exracting huge data from remote database [message #388064 is a reply to message #388050] Mon, 23 February 2009 01:41 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
Thanks for help..

But this doc contain all the solution related to DBA side..
As i told you I dont have any previleges..

I can only tune the query in my possible ways...

[Updated on: Mon, 23 February 2009 01:52]

Report message to a moderator

Re: exracting huge data from remote database [message #388134 is a reply to message #388064] Mon, 23 February 2009 07:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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...

Re: exracting huge data from remote database [message #388143 is a reply to message #388139] Mon, 23 February 2009 07:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are your queries taking advantage of the Driving_Site hint?
It might be worth looking at.

Previous Topic: how to avoid outer join?
Next Topic: Global temp table throwing error
Goto Forum:
  


Current Time: Thu Feb 06 16:53:05 CST 2025