How To Reference Created Table from other sources? 3

[kjv1611]

My current "project" from which this question comes is mentioned in the following thread:
thread1177-1635292

This may be a dumb question, but at this point, I figure why not ask it anyway.

Currently, where I work, if I want to select data from a table from a system of ours, it shows in Access (imported/linked tables) as SystemName_TableName where SystemName represents the system using the table... so perhaps that's the shema or owner?

Anyway, so that brings me to this question:

If I create a table, such as this:

CREATE TABLE MyTable (FruitID number, Fruit varchar(15));

Then from Access, would I reference it as just MyTable?

Or would I need to reference it somehow by User.TableName or User_TableName?

It seems that the SystemName_TableName is how it shows in the Access ODBC connection, but within the SQL Plus application accessing the Oracle server, it shows as SystemName.TableName.

Thanks for any thoughts, references, suggestions.

 

[SantaMufasa]

KJV,

I do not have access to Access (nor have I any experience with Access), but there are about a half-dozen Tek-Tips forum that deal with MS Access questions including forum701, where I'm sure experts there can advise on the necessities of an Access/Oracle query.

I can show you here Oracle methods I could use to query data from different "distances" away from the data:[ul][li]Querying from the owning schema:

select id, last_name from emp;

ID LAST_NAME
-- ------------
 1 Velasquez
 2 Ngao
 3 Nagayama
 4 Quick-To-See
 5 Ropeburn

[/li][li]Querying from a non-owning schema, same instance:

select id, last_name from [B][I]santamufasa.[/I][/B]emp;

ID LAST_NAME
-- ------------
 1 Velasquez
 2 Ngao
 3 Nagayama
 4 Quick-To-See
 5 Ropeburn

[/li][li]Querying from a different server (using a database link that I created from the remote schema on Machine "B" to the owning schema on Machine "A":

select id, last_name from [B][I][/I][/B]emp@mufasalink;

ID LAST_NAME
-- ------------
 1 Velasquez
 2 Ngao
 3 Nagayama
 4 Quick-To-See
 5 Ropeburn

I can create the dblink, "mufasalink" if I know the target username, password, and network alias:

create database link mufasalink
       connect to santamufasa
       identified by "Rafiki"
       using 'hr015';

[/li]In the above,[li]The database link name is mufasalink,[/li][li]the remote Oracle schema name is santamufasa,[/li][li]The password for the remote schema is Rafiki,[/li][li]The target Oracle instance name is hr015[/li][/ul]Again, I'm not familiar with Access's syntactical intricasies for querying data from an Oracle database, but the above shows how I would query data from different logical "distances" from the source table.


Once you have created a working Access query (after having set up the proper ODBC entries), please post here to close the loop.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kjv1611]

You know, I'm thinking that if I didn't give it a specific schema, then perhaps it's "just there"? I think I remember seeing some tables that were "just there" when I went to link in some tables for testing...

I'll check and post back.

Thanks for the suggestions. I think I can handle the Access side, if not, I'll ask yet another Access question. I haven't had to ask near as many of those now as I used to.

 

[SantaMufasa]

I'm thinking that if I didn't give it a specific schema, then perhaps it's "just there"?

Every table must have one (and only one) owner/schema. If you connect to an Oracle database, and if you don't specify a schema name as owner of a table you are accessing, then here are the possible explanations (for accessing table data without a schema-name qualification):[ul][li]The user to which you are connected owns the table.[/li][li]Another user owns the table, and the owner GRANTed you access to her/his table, and you have created a private (i.e., your user owns it) SYNONYM.[/li][li]Same scenario as above, but someone has created a PUBLIC SYNONYM (one which anyone can use, provided the non-owning user also has been GRANTed access).[/li][/ul]So, KJV, in response to your assertion above (that "it's just there"), it really isn't just "out there" as an orphaned entity...every table has an explicit owner, and if you are not the owner, and you can still access it, that happens by virtue of the "modern convenience" called SYNONYMs.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kjv1611]

Okay, so anything that isn't listed as being associated with an entity is a system table? Such as for example, "Plan_Table"

Well, there is one way to find out for sure how my table would who up.

I'll test and post back.

 

[kjv1611]

Yeah, dummy me, I just hadn't tested it yet...

When I create a table...

CREATE TABLE HowdyDo (TestID Number);

It shows up as:

SysName.HowdyDo

So I know exactly where to look for the tables in that case, at least.

 

[kjv1611]

Okay, I may be getting somewhere on grabbing the PLAN_TABLE or at least finding why I can't seem to pull data from the Plan_Table outside of SQL*PLUS.

I don't understand it, but for instance, I can SELECT, DELETE EXPLAIN records into/out of PLAN_TABLE, however, I cannot TRUNCATE the table, neither can I view the data in Access. In Access, it pulls in the table field headers, but no data. In Excel, if I attempt to pull it in, it acts like it'll pull in, but never shows.

Are there any intricacies to the way the Oracle system uses PLAN_TABLE that would not allow an external source to view the contents of said table?

 

[SantaMufasa]

When you say,

In Access, it pulls in the table field headers, but no data.

...are you telling me that it "hangs", or does it respond with some sort of message that asserts that there are no (or 0) rows selected?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kjv1611]

No errors, no hangs, it looks like it works correctly until I actually open the linked or imported table (tried both ways), and there is no data.

 

[SantaMufasa]

If there appear to be no data, can you confirm that the process that is writing the data does a COMMIT after populating the table? (No COMMIT, no data visible to any other session.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kjv1611]

How do I confirm that it is COMMITTED? I probably missed that earlier..

Or how do I tell Oracle to COMMIT something? In the Explain Command, I didn't add any additional code for committing. Sounds like that's what is missing.

 

[Dagon]

You just type

COMMIT;

immediately after your statement.

For Oracle-related work, contact me through Linked-In.

 

[kjv1611]

Thanks. I'll give it a shot to see if it helps.

 

[kjv1611]

Tried COMMIT;
Oracle said Commit complete.

Still no go on the table import or link in Access.

I wonder if it's perhaps some sort of application-level security locking, just being that it's considered more a system table (I guess) instead of a regular data table.

Regardless, with or without getting a good look at that, I think I got what I want. I was able to update my SQL which I previously posted in another thread to use the MINUS type of clause (like UNION) to remove 85% of the records in a subquery before hitting the other tables...

Time with 3 queries UNIONED (as one subquery) to get that result pushed the query from 5 to 10 minutes. Then when I changed to a MINUS in the subquery, using 2 queries to build that subquery, the query fell back to between 2 and 3 minutes. Definitely an improvement.

Thanks again for all the help.

 

[karluk]

Are there any intricacies to the way the Oracle system uses PLAN_TABLE that would not allow an external source to view the contents of said table?

It appears that you are using Oracle's default plan_table, which is a global temporary table in the sys schema. That would explain why only the session that performs the explain plan can "see" the contents of plan_table. The contents of a temporary table are specific to a session, so no other session will be able to see the results of your explains, even if logged in with the same id.

You could fix this issue by creating a permanent plan_table in your own schema (by running $ORACLE_HOME/rdmbs/admin/utlexplan.sql). That reverts back to the way plan tables were created prior to Oracle 10g. But perhaps it won't be necessary, since you seem to have solved your problem without needing access to the plan_table output.

Quoting from

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48812348054

the ON COMMIT PRESERVE ROWS makes this a session based temporary table. rows will stay
in this table until a logoff. Only I can see them though, no other session will ever see
'my' rows even after I commit

 

[kjv1611]

Thanks... if I get time, I'll look into that, karluk.