MSACCESS hangs
Pete's
Client -- Windows XP, MS Access 2003 using Oracle's ODBC driver
Hey all, have a user having a problem now trying to get a listing of
tables via 'link tables' operation MS Access, this is after the login
dialog. I can see the session connect and stays active until I
eventually have to kill the session. Looking at the sql it's running:
SELECT count(*)
FROM user$ u, xdb.xdb$schema s
WHERE u.user# = :1
AND u.name = s.xmldata.schema_owner
AND xdb.xdb$extname2intname(s.xmldata.schema_url,
s.xmldata.schema_owner) = :2
Anyone have any ideas, besides upgrading? I have updated dictionary
stats, System stats are set and have not changed.
TIA,
Pete's
gazzag
You're logging into a database as SYSDBA using MS Access? Why?
-g
sybr...@hccnet.nl
I would try to isolate whether it is the query or ODBC.
So I would turn it into a passthru query, or run it in sqlplus,
preferably on the server.
BTW: Please do NOT use the sys *tables* directly. Oracle doesn't
guarantee their existence in future releases.
--
Sybrand Bakker
Senior Oracle Dba
Pete's
I'm not logging in as "sysdba".
The point I'm at is where you log in via the odbc connection, you type
your userid/password, press ok. It sits there and you are waiting for
a list of tables to be displayed. The sql I gleaned from the database
is what either Oracle or Microsoft was running while waiting for a
table list. I would agree with using sqlplus, however, the user(s)
are insisting on using msaccess. Sqlplus, Sqlworksheet, Toad, .....
all work. I know Access does not handle this table list too well when
a user has a large number of tables they have rights too. My only
other thoughts are windows patches may have been installed, no db or
server patches were installed.
TIA,
Pete's
Pete's
DA Morgan
A lot of thoughts:
1. Apply patches. You haven't patched your database in years.
2. Check for an upgraded ODBC driver.
3. Stop using MS Access as a front-end. It is insecure junk.
4. Reconsider the advisability of accessing catalog tables the way you are.
5. Run your query as an Explain Plan. Produce output with DBMS_XPLAN.
And post it if there is something you don't understand.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
DA Morgan
There is no way you should be logging in with MS Access and seeing
user$ under normal circumstances. If you can see it then you should
explain what system privs/roles were granted that allow it.
What you are describing is possibly a gross security breach.
Pete's
> There is no way you should be logging in with MS Access and seeing
> user$ under normal circumstances. If you can see it then you should
> explain what system privs/roles were granted that allow it.
>
> What you are describing is possibly a gross security breach.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> Puget Sound Oracle Users Groupwww.psoug.org
Again note, I'm not seeing user$ via Access, this is what I queried
out of the database of what the session was running. It's either a
piece of sql that Oracle runs behind the scenes, or, what Access
runs. In other words, the user is trying to get MS Access to get them
a list of objects and it's not coming up.
I don't disagree with you in regards to patching, it needs to get done
but has not. I also don't disagree with the use of Access as a front
end but I do not have control over that, I'm not the application
developer.
Pete's
DA Morgan
Wrong!
You can't query what you can't see. Your statement clearly indicates
that you can see USER$ and other catalog tables owned by SYS.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
joel garry
While looking for something completely different, I stumbled over
this: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb26imp.htm#sthref2432
I have no clue about xdb, but I'm wondering if there is something
special about it that is influencing your problem.
jg
--
@home.com is bogus.
Revenge is a dish best served hidden away in secret dark places no one
will ever find. http://www.signonsandiego.com/uniontrib/20080725/news_1n25revenge.html
Pete's
> On Jul 23, 7:37 am, "Pete's" <empete2...@yahoo.com> wrote:
>
>
>
> > Server -- AIX 5.3 TL06 SP03, Oracle 10.2.0.1 EE
> > Client -- Windows XP, MS Access 2003 using Oracle's ODBC driver
>
> > Hey all, have a user having a problem now trying to get a listing of
> > tables via 'link tables' operation MS Access, this is after the login
> > dialog. I can see the session connect and stays active until I
> > eventually have to kill the session. Looking at the sql it's running:
>
> > SELECT count(*)
> > FROM user$ u, xdb.xdb$schema s
> > WHERE u.user# = :1
> > AND u.name = s.xmldata.schema_owner
> > AND xdb.xdb$extname2intname(s.xmldata.schema_url,
> > s.xmldata.schema_owner) = :2
>
> > Anyone have any ideas, besides upgrading? I have updated dictionary
> > stats, System stats are set and have not changed.
>
> > TIA,
> > Pete's
>
> While looking for something completely different, I stumbled over
> I have no clue about xdb, but I'm wondering if there is something
> special about it that is influencing your problem.
>
> jg
> --
> @home.com is bogus.
> Revenge is a dish best served hidden away in secret dark places no one
> will ever find. http://www.signonsandiego.com/uniontrib/20080725/news_1n25revenge.html
Thanks for the response Joel, not sure if it's related either, I'll
check into it. For now I solved the problem with patching the client
to the latest, 10.2.0.4, also found that the 11.1.0.6 client works
too. Additional testing found that any connection to a database with
less than 10,000 objects worked fine. After much digging, I also
found an issue another person had where they had pinpointed the number
of objects to be around 32,768 where they started experiencing
problems similar to this.
Thanks,
Pete's