Re: Select across Tablespaces

From: <oratune_at_aol.com>
Date: 2000/07/28
Message-ID: <8lsrlg$3pd$1@nnrp1.deja.com>

In article <8lsdv0$o7p$1_at_nnrp1.deja.com>,   dschorsc_at_rtscinc.com wrote:
>
>
> I have a table that is in several tablespaces and I would like to
> perform a 'SELECT' across them. Can I do this easily or do I have to
> write a stored procedure that creates a database link REMOTE_CONNECT
> for each tablespace, select the data, put in a temp table. Repeat for
> each tablespace, adding to the result from each select to the temp
> table and then return that result set??
>
> This is very confusing so any help would be greatly appreciated!!
>
> Thanks in Advance.
>
> Dave
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

I cannot see how a table of the same name, owned by the same user, can be in several tablespaces of a single instance outside of the table being partitioned (and then this situation does not occur since Oracle will select from any and all partitions necessary to retrieve the required data). That being said, there could be a number of ways this could occur:

1. The same table (name, definition) is in several tablespaces of a single instance due to multiple users creating it -- it appears across schemas.
2. The tablespaces are actually on different servers and in different instances.
3. The tablespaces are in different instances on the same server.

Dealing with these three possibilities there are two ways to accomplish your goal.

Situation 1: Select across the schemas and, presuming the tables are exactly the same, or the information from each table contains the same fields, perform a union of the individual queries. For example:

Users bob, ed, al and festus all have a copy of the SALES table, and there is a master SALES table in a different schema. Presuming you are connected to the master schema, and you have select privileges on the SALES tables from bob, ed, al and festus you could do this:

select region, account, acct_rep, monthly_volume from sales
where txn_date between to_date('Jul 1, 2000', 'Mon DD, CCYY')

                   and to_date('Jul 31, 2000', 'Mon DD, CCYY')

union
select region, account, acct_rep, monthly_volume from ed.sales
where txn_date between to_date('Jul 1, 2000', 'Mon DD, CCYY')

                   and to_date('Jul 31, 2000', 'Mon DD, CCYY')

union
select region, account, acct_rep, monthly_volume from bob.sales
where txn_date between to_date('Jul 1, 2000', 'Mon DD, CCYY')

                   and to_date('Jul 31, 2000', 'Mon DD, CCYY')

union
select region, account, acct_rep, monthly_volume from festus.sales
where txn_date between to_date('Jul 1, 2000', 'Mon DD, CCYY')

                   and to_date('Jul 31, 2000', 'Mon DD, CCYY')

union
select region, account, acct_rep, monthly_volume from al.sales
where txn_date between to_date('Jul 1, 2000', 'Mon DD, CCYY')

                   and to_date('Jul 31, 2000', 'Mon DD, CCYY')

order by region, account, acct_rep, monthly_volume;

No database links, just select privilege for the table in question across the schemas.

Situations 2 and 3 would be resolved in a similar manner using db_links to retrieve the data rather than simple select grants from the individual users. Let us say there are 4 divisions to your company plus a home office -- Northeast, Northwest, Southeast, Southwest and Central (the home office). Each division has it's own instance exactly like the home office. Database links to each division would allow the same query listed above to be used collect data across the company:

select region, account, acct_rep, monthly_volume from sales
where txn_date between to_date('Jul 1, 2000', 'Mon DD, CCYY')

                   and to_date('Jul 31, 2000', 'Mon DD, CCYY')

union
select region, account, acct_rep, monthly_volume from sales_at_NE
where txn_date between to_date('Jul 1, 2000', 'Mon DD, CCYY')

                   and to_date('Jul 31, 2000', 'Mon DD, CCYY')

union
select region, account, acct_rep, monthly_volume from sales_at_NW
where txn_date between to_date('Jul 1, 2000', 'Mon DD, CCYY')

                   and to_date('Jul 31, 2000', 'Mon DD, CCYY')

union
select region, account, acct_rep, monthly_volume from sales_at_SE
where txn_date between to_date('Jul 1, 2000', 'Mon DD, CCYY')

                   and to_date('Jul 31, 2000', 'Mon DD, CCYY')

union
select region, account, acct_rep, monthly_volume from sales_at_SW
where txn_date between to_date('Jul 1, 2000', 'Mon DD, CCYY')

                   and to_date('Jul 31, 2000', 'Mon DD, CCYY')

order by region, account, acct_rep, monthly_volume;

The only difference between the two queries is that in the first the table is prepended with the owner whereas the second is using db_links with the '@' syntax. Both queries should return the full set of data from all tables. No temporary tables need to be explicitly created and no PL/SQL procedures are necessary.

I hope this covers your situation. If not, please provide more information so you can be properly assisted.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Jul 28 2000 - 00:00:00 CDT