Home » SQL & PL/SQL » SQL & PL/SQL » Is it possible to Access Partition Table Using DBLink (Oracle9i)
Is it possible to Access Partition Table Using DBLink [message #417744] Tue, 11 August 2009 06:16 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

Is it possible to Access Partition Table specific Partition Using DBLink, if yes what is the syntax?

Query:
SELECT COUNT (*) FROM xyz@adam PARTITION (xyz_0809);


Please suggest.
Re: Is it possible to Access Partition Table Using DBLink [message #417747 is a reply to message #417744] Tue, 11 August 2009 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what hapenned when you tried?

Regards
Michel
Re: Is it possible to Access Partition Table Using DBLink [message #417787 is a reply to message #417747] Tue, 11 August 2009 09:35 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Michel,

ERROR:
ORA-14100: partition extended table name cannot refer to a remote object
Re: Is it possible to Access Partition Table Using DBLink [message #417788 is a reply to message #417744] Tue, 11 August 2009 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
14100, 00000, "partition extended table name cannot refer to a remote object"
// *Cause:  User attempted to use partition-extended table name syntax
//          in conjunction with remote object name which is illegal
// *Action: Correct the statement and reenter
Re: Is it possible to Access Partition Table Using DBLink [message #417798 is a reply to message #417787] Tue, 11 August 2009 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is only possible in 10g and up.

Regards
Michel
Re: Is it possible to Access Partition Table Using DBLink [message #417801 is a reply to message #417798] Tue, 11 August 2009 10:09 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Michel,

Thanks.

Is syntax will remain same ?

Re: Is it possible to Access Partition Table Using DBLink [message #417802 is a reply to message #417801] Tue, 11 August 2009 10:12 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Check this for lock table.

EDIT: I read it wrongly.
Quote:
LOCK TABLE
[ schema. ] { table | view }
[ { PARTITION (partition)
| SUBPARTITION (subpartition)
}
| @ dblink -- It says either partition or dblink.
]
[, [ schema. ] { table | view }
[ { PARTITION (partition)
| SUBPARTITION (subpartition)
}
| @ dblink
]
]...
IN lockmode MODE
[ NOWAIT ] ;


By
Vamsi

[Updated on: Tue, 11 August 2009 11:14]

Report message to a moderator

Re: Is it possible to Access Partition Table Using DBLink [message #417803 is a reply to message #417801] Tue, 11 August 2009 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (col integer)
  2  partition by range (col)
  3  (partition p1 values less than (10),
  4  partition pmax values less than (maxvalue));

Table created.

SQL> select count(*) from t@mika partition (p1);
  COUNT(*)
----------
         0

1 row selected.

Regards
Michel
Re: Is it possible to Access Partition Table Using DBLink [message #417811 is a reply to message #417803] Tue, 11 August 2009 10:57 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
In my previous post, I didn't test it.
Now I wonder how it is working for Michel.

I'm also getting the same error for the same example, which Michel has given.

Documentation of 8i, 9i, 10g, 11g are saying the same.
Quote:
Restrictions on Extended Names
Currently, the use of partition-extended and subpartition-extended table names has the following restrictions:

No remote tables: A partition-extended or subpartition-extended table name cannot contain a database link (dblink) or a synonym that translates to a table with a dblink. To use remote partitions and subpartitions, create a view at the remote site that uses the extended table name syntax and then refer to the remote view.

No synonyms: A partition or subpartition extension must be specified with a base table. You cannot use synonyms, views, or any other objects.

The PARTITION FOR and SUBPARTITION FOR clauses are not valid for DDL operations on views.
Even I have checked in 11.1.0.6.0 and 10.2.0.3.0 and getting the error "ORA-14100".

By
Vamsi
Re: Is it possible to Access Partition Table Using DBLink [message #417815 is a reply to message #417811] Tue, 11 August 2009 11:08 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To simplify I used a loopback database link but trying with a real cross database database link I get the same error.
It seems Oracle is smart enough to realize that in my example the db link is on the current database.

Regards
Michel
Previous Topic: Perfomance tunning
Next Topic: No rows selected but data exists
Goto Forum:
  


Current Time: Sun Dec 11 04:17:12 CST 2016

Total time taken to generate the page: 0.23707 seconds