PL/SQL :: Unable To Run Spatial Operations Through DBLinks?
Aug 16, 2012
I am trying to run spatial operations through dblinks. See the example query below
select a.OGC_GEOMETRY.sdo_gtype from <table>@dblink a where sdo_nn(a.OGC_GEOMETRY,mdsys.sdo_geometry(2001,null,mdsys.sdo_point_type(0,0,null),null,null),'sdo_num_res=1')='TRUE'.
Query fails with the following error
ORA-13249:
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 17
ORA-06512: at "MDSYS.PRVT_IDX", line 9
*13249. 00000 - "%s"*
**Cause: An internal error was encountered in the extensible spatial index*
component. The text of the message is obtained from some
other server component.
**Action: Contact Oracle Support Services with the exact error text.*
Same Query runs fine in the original database but fails with dblinks. Is it possible to run spatial operations through dblink?
I have a road network which is shape file format and i want to export it to oracle spatial format using any free tool, I am using arcgis 9.3.1 and Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bi,
I have user U1 with dblink DBL1 (private dblink, not public).
CODECREATE DATABASE LINK DBL1 CONNECT TO U1 IDENTIFIED BY XX USING 'TNS1';
I have user U2 with dblink DBL2 (private dblink, not public).
CODECREATE DATABASE LINK DBL2 CONNECT TO U2 IDENTIFIED BY XX USING 'TNS2';
Both dblinks works fine, it means I can do select.
When I logged in with U1 and try to execute the following statement :
CODESELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME) FROM SYS.USER_OBJECTS WHERE OBJECT_TYPE = 'DATABASE LINK' AND OBJECT_NAME = 'DBL1';
I get ORA-31603 error:
CODEORA-31603: object "DBL1" of type DB_LINK not found in schema "U1" ORA-06512: at "SYS.DBMS_METADATA", line 4018 ORA-06512: at "SYS.DBMS_METADATA", line 5843 ORA-06512: at line 1
same happens when I pass user name to get_ddl:
CODESELECT SYS.DBMS_METADATA.GET_DDL('DB_LINK',OBJECT_NAME, [b]USER[/b]) FROM SYS.USER_OBJECTS WHERE OBJECT_TYPE = 'DATABASE LINK' AND OBJECT_NAME = 'DBL1';
When I select from user_objects with the following query , it is there.
CODESELECT * FROM SYS.USER_OBJECTS WHERE OBJECT_TYPE = 'DATABASE LINK' AND OBJECT_NAME = 'DBL1';
DBMS_METADATA.GET_DDL works for every other objects of U1 (tables, views, ... for example) except for dblinks. DBMS_METADATA.GET_DDL works for every objects of U2, includes DBL2.
I tried to add "select catalog role" to U1, even when I know that I need it only for objects from other users.
I tried to create DBL2 in U1 - same results.
I tried to re-create dblink using full connection string - with same result.
I am working on a project that will require both projected coordinates and geographic coordinates for different product files coming in. I have no issues defining, storing, extracting or matching the geographic coordinates. My columns for those coordinates with SRID 8307 are defined in the user_sdo_geom_metadata table as follows:
the index: create index "MYDB"."INVGEO_IDX" on "MYDB"."INVENTORYGEOMETRY"("INVGEO") indextype is MDSYS.SPATIAL_INDEX;
how I am going to have a column that has both Geographic and Projected coordinates. Am I allowed to define more SDO_DIM_ELEMENT(s) fro the SDO_DIM_ARRAY with different min/max values. As an example, would I be able to still use an SRID of 8307 for both WGS 84 and GRS80 data (ellipsoid), something like the following:
Is that possible or do i have to create a new column in the database? Also, I don't know the relationship between the SDO_ELLIPSOIDS table and the SDO_GEOMETRY table.
how I'm going to make this work for both the geographic and projected coordinates or if I'm going to be able to make it work.
I am creating an stored function which has to do some inserts in the meanwhile, and return after all the work has done, an UDT (2 or 3 columns of NUMBER datatype).
With this scenario I have an problem. The DML operations are not supported by and "SELECT * FROM Table(MyProc(args))". I have to use this "SELECT * FROM Table(MyProc(args))" because I need to pass the stored function results directly to an dataset.
Using a Stored Procedure it gives no errors, but the arguments must be passed like OUT params, and it is not what I want.
My question is: Is there any other way to get a result (UDT) of an Stored Function (that makes Inserts) into a DataSet?
In the example below I believe I have created a Nested Table of PL/SQL type and have tried various references to get the SET operation to work, line containing MEMBER OF. Taking the example below from the oracle documentation I have two questions.
1) As I understand it I should be able to use SET operations on Nested tables of PL/SQL types, (I am not using the CREATE OR REPLACE DDL statement prior to the DECLARE block.). Is this correct? 2) I am assuming that I have to reference the record, can I reference by its type / row instance or can I only retrieve the record like a Cursor Fetch solution, (which would defeat the purpose.).
I created a table with a column "id" and values for this column is attached a sequence. And now i need, if any value deleted from the table the column "id" will need to be sequence.
ex:
id name -- ------- 1 xxxx 2 yyyy 3 zzzzz 4 pppp 5 rrrrrr
if i delete
delete from test where id=4;
then automatically.. "id" column values will again in sequence... like this
id name -- -------1 xxxxx 2 yyyyy 3 zzzzzz 4 rrrrrr
note: in the above if i delete the id=4 from the table again it will have be in sequence and if i inserted the again it has to take the next value continue to sequence....
ex : insert to test values(seq_name.nextval,'tttt');
Doing some data conversion at the moment and using V$SESSION_LONGOPS to predict when the current task will be finished so I can run the next one.
V$SESSION_LONGOPS seems to have only the last 16 long operations for the session. Older operations are automatically purged. My bigger tables have 32 partitions, so after the first 16 are processed, I cannot tell which partition I am up to.
Googling "old longops" and "longops history" didn't work, nor did the same searches on this site. The Oracle Reference manual section on V$SESSION_LONGOPS did not mention that older entries are purged.
Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.
I should get values as 0, -34 and 77. But I don't get this as all operations with null gives null. These are fetched data and don't exist in the form shown. I have to use these row values of "Change" further. Is there any way of obtaining these results?
I am executing a sql statement which is doing FTS in parallel mode The server has 8 cpus and threads_per_cpu is 2
The v$sql shows PX_SERVERS_EXECUTIONS as 8
select PX_SERVERS_EXECUTIONS, sql_text from v$sql where sql_id='0q0nk5117yth2' 8, select /*+ full(a) parallel(a)....
however the px_sessions shows 17 sessions (16 parallel session + 1 parent session (where sid = qcsid) Now in px_sessions, these 16 parallel session are divided in 2 server sets 1 and 2 and values for degree and required degree are 8 and 16 respectively
However, all the time, only 8 sessions which belong to server set = 1, were active though its state was waiting with event "PX Deq Credit: send blkd"
The other session which belong to server set = 2 were never active and always had waint event ='PX Deq: Execution Msg'
what could be the reason that 16 parallel session could not be started though I am the only person using the server, there aren't any batch jobs, dbms_jobs,even archivelogs (not a prod system)?
Note that paralel_max_servers setting is 16
Another issue being the duing start of the query approximately 100-115 blocks were read for the query (checked from longops) however after 60-70% blocks are read the number of blocks read / seconds falls down to 10-20 blocks / second across all parallel sessions.
I want to make sure I am describing correctly what happens in a query where there is distributed database access and it is participating in a NESTED LOOPS JOIN. Below is an example query, the query plan output, and the remote SQL information for such a case. Of particular note are line#4 (NESTED LOOPS) and line#11 (REMOTE TABLE_0002).
What I want to know is more detail on how this NESTED LOOPS JOIN handles the remote operation. For example, for each row that comes out of line#5 and is thus going into the NESTED LOOPS JOIN operation @line#4, does the database jump across the network to do the remote loopkup? Thus if there are 1 million rows, does that mean 1 million network hops? Does batchsize play a role? For example, if the database batches in groups of 100 then does that mean 10 thousand network hops?
I think each row that comes out of line#5 means a network hop to the remote database. But I do not know for a fact.I have done some abbreviating in the plan in an attempt to make it fit on the page (line#7 TA = TABLE ACCESS).
Background: We are migrating a lot of databases from one SAN appliance to another. We are doing this by adding new disks from the new SAN appliance to the existing disk groups, re balancing, removing the old disks from the disk groups, and then re balancing again.
Question: If I execute two ALTER commands with the same power on 2 or more separate disk groups, do both operations start executing right away? Or do they queue up and execute one after another?
I ask because we would like to queue up several re-balances so we don't have DBAs watching status bars all day.
When performing job search in i Recruitment found that all jobs are not retried when performing geocode search using Distance from Location functionality.
Narrowed down and found few addresses have Geometry coordinates (0,0) which are posing this problem. Example :
The SDO_LENGTH command appears to be giving us the length of only the first line segment in a mult-segmented polylines. ... For example.. for this
polyline.... MDSYS.SDO_GEOMETRY(3002,82212,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,1,7,2,2),MDSYS.SDO_ORDINATE_ARRAY(489308.53608219,5465823.43147879,0,489254.621813806,5465739.29869485,0,489223.453489433,5465690.81111309,0,489217.608149169,5465676.83952032,0,489213.696031073,5465662.20843221,0,489213.337168734,5465657.12388026,0,489213.259485918,5465652.02727197,0)) RunningUPDATE TEMP_1 SET LEN = SDO_GEOM.SDO_LENGTH (GEOMETRY,0.005,'unit=meter');
We get 99.925 meters,and not the 198.075 that we were expecting.
for functions like SDO_GEOM.RELATE, do the return values like "COVEREDBY", are those values/definitions defined in the database somewhere? For instance a table containing all the values and a description of each?
I want to store duplicate geometry intentionally, will it have any problems ?? Because same data comes from different sources and going to be big database system.
I recently stumbled upon a post for an undocumented golden gate procedure that I find VERY useful when repairing / simplifying spatial data as a DBA without causing user triggers to fire and do unnecessary work, etc. Anything with foo in the name must be good, right? ;-)
HINT: Oracle please make this a documented feature!
This will disable triggers – just for a session – no system wide changes or trigger mods needed! Yes, fire needs to be set to true - to disable the triggers from firing.
I have a two different Databases. I created a db link in DB 1 to connect to DB 2 and it is working fine when I select data from any table. but I have one table in the DB2 which has a column with user defined data type . so when I try to select this column from DB 2 by using the DB link it gives me this error :ORA-22804 remote operations not permitted on object tables or user-defined type columns.
would there be any code change for spatial data after migration from 10g to 11g? In case of upgradation from 10R1 to 10R2, there were lot of issues regarding the spatial data code.
know beforehand so that it should not affect the service.I have already done the migration from 10g to 11g by 2 ways succesfully.
I Have Problem in How to determination the Direction And the neighbor For polygon As in the picture.
picture in [URL]...
I Want determination :
From north : polygon with object Id=..... From south : streets with object Id=..... From east :streets with object Id=..... From west : polygon with object Id=.....
I have Started and I Identified the Directions I get line of land with line 29 meters is the North and line with 25.4 is south ......
Now how I can Determination the border of land ?manes : north ... land with number 2 south ... street with 15 meters width
