Using View with Linked Server (Oracle) on Entity Framework (.NET)

One of the issue I came across on Entity Framework was pulling a view that consist of a linked server table (Oracle). When I added the view, I was getting the following error. "The table/view LINKED_SERVER_VIEW does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it." I needed this view as readonly. Only solution that worked was using ISNULL to define a primary key on the view. You will have to create a view like this for Entity Framework to understand the primary key. CREATE VIEW LINKED_SERVER_VIEW AS SELECT ISNULL(PRIMAYKEYCOLUMN, 0) as COL1, OTHERCOLUMN from LINKEDSERVER..SCHEMA.TABLENAME I believe the best approach is to actually fix the table or the model itself and add an actual primary key. However, sometime you will have to work on a database that a client does not want to change and you will have to deal with issues like this. Hope this solution works for you. If you have better solution, feel free to comment.
FacebookTwitterGoogle+Share

Fast tutorial for explain plan on Oracle

user1@testserver : /home/firestorm => sqlplus
 
SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jul 2 15:27:33 2008
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Enter user-name: user1
Enter password: **********
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
 
dbserver:SQL> @?/rdbms/admin/utlxplan
 
dbserver:SQL> truncate table plan_table;
 
Table truncated.
 
dbserver:SQL> commit;
 
Commit complete.
 
dbserver:SQL> explain plan for
2  select count(*) from schemaname.tablename;
 
Explained.
 
dbserver:SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation              |  Name            | Rows  | Bytes | Cost  |  TQ    |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|                  |     1 |       | 26979 |        |      |            |
|   1 |  SORT AGGREGATE        |                  |     1 |       |       |        |      |            |
|   2 |   SORT AGGREGATE       |                  |     1 |       |       | 20,00  | P->S | QC (RAND)  |
|   3 |    TABLE ACCESS FULL   | TABLENAME        |   309M|       | 26979 | 20,00  | PCWP |            |
--------------------------------------------------------------------------------------------------------
 
Note: fully remote operation, cpu costing is off
 
11 rows selected.
 
dbserver:SQL> exit;

Temp space check in oracle

Ever got this issue in oracle ... ORA-01652: unable to extend temp segment by x in tablespace TEMP_TS Here is a way to find out if you are really out of temp space
--Temp space total size, free space and used space
SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024/1024 AS Total_GB,
used_blocks*16/1024/1024 AS Used_GB,
free_blocks*16/1024/1024 AS Free_GB
FROM   v$sort_segment;
 
--Temp space utilization by user
 
SELECT
b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
b.username,
b.blocks*16/1024/1024 AS GB,
a.SID,
a.serial#,
a.status
FROM v$session a, v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.username, b.tablespace, b.blocks;