Collect query results from several schemas

[cshapira]

Hi Oracle Developers,

Our Database contains several identical schemas with different data in each (this is due to size issues).

I'd like to write a stored procedure that will recieve a query from the user, will run this query in all these schemas and show the results to the user.
I want to put all these results in a temp table (or nested table?), so if the user gives an "order by" clause, I can order the results from all the schema together.

How do I do this in PL/SQL?

Thanks in advance,
Chen Shapira

[davey23uk]

union with the data from other schemas.

But what are these 'size' issues that stop you putting data in the same schema, that sounds like a silly idea

[cshapira]

union with the data from other schemas.

But what are these 'size' issues that stop you putting data in the same schema, that sounds like a silly idea

1. I'm not sure how to do it. I don't have the actual query when writing the procedure, the user enters any query he wants when running the SP. All I have is a variable called p_query. Should I parse it? But it can be very complicated query.

2. Performance drops significantly when you have over 100 partitions with more than 5 million rows in each. I don't have much choice except another schema.

[cshapira]

Found a solution to my woes:

Use

AUTHID CURRENT_USER

on the schema so it won't be bound to specific user on compile.
Then use

alter session set current_schema

to set a different schema as the default.

Thanks everyone!