3

I am working on Oracle 9i

my question is:

can I use a partition over a table through a dblink

for example I am working on DB1 and want to use a table A_table which has a partition with name part1 from DB2

how can I make the following:

SELECT * FROM A_table PARTITION(part1)@DB2

is there any syntax to do that, or it's forbidden in Oracle 9i

Regards

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Hany
  • 1,146
  • 4
  • 18
  • 26
  • 1
    Why do you want to query a specific partition? There are vanishingly few cases where you really want to use the `PARTITION(part1)` syntax. It generally makes much more sense to specify a `WHERE` clause on the partitioning key and let Oracle do the appropriate partition pruning. – Justin Cave Apr 15 '12 at 18:51
  • @JustinCave: Am very late to the party, but found a use case: I want to select from the default partition of a list partitioned table. – wolφi Sep 03 '21 at 10:48

3 Answers3

2

You cannot query partitons over dblinks
see this post:
Oracle forums

Abd
  • 426
  • 1
  • 8
  • 27
  • 1
    To be more accurate, you cannot specify the partition using the PARTITION syntax when querying across a database link. You can, however, query the partition by using an appropriate WHERE clause (as stated in the forum linked to). – Jeffrey Kemp Apr 11 '11 at 01:31
0

Some of the cases, like Hash partition you dont know the keys but you want to manage the load using partition, in that case view can be created at sourceSide and that view can be used at target

Naveen Gupta
  • 1
  • Please [edit] your answer to: include [example] of your solution code , include an explaination of how this works and why it is solution of the problem? See [answer] – Gander Dec 18 '20 at 20:59
0

Create view of partition on remote server side. Execute query with dblink from view.

Michael
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 25 '23 at 07:05