Why are BC objects created in user's schema
Server = Sun Sparc SunOS 5.8, Oracle 8.1.7.4 SID
Client = Windows 2000, Oracle 9iDS (v9.0.2 w/Jdeveloper v9.0.3)
Network = 100MB TCP/IP
I have a schema with relatively large (in my mind) tables master = 225,000 records; detail = 1,200,000 records.
I've used Jdeveloper 9i to create a business components (Java based) view of these tables.
When I execute the view (query) and request the last record of the master table, this action takes approximately 1 hr and 40 minutes to accomplish.
The database is in archive mode.
The redo logs (3) on the database were 500 KB files (the default created by the DBCA). I was seeing archive files created on the order of one every 10 seconds. I increased the logfiles to be 200 MB and now appear to be getting archives once every 10 minutes.
I've tracked it down to something taking place in the connected user's tablespace during the query.
I created a separate tablespace (with it's own temporary tablespace) for my test user. If I did not grant unlimited tablespace to this user, the query failed because the user did not have privilege on the tablespace.
When I granted unlimited tablespace (is there no middle ground??), the tablespace grew to 290 MB from 128 KB. After disconnecting from the query (quitting the application) the tablespace remained at 290 MB. Upon restarting the query, the used tablespace initially shrunk back to 650KB and then grew again to 290 MB. The temporary tablespace never changed or seemed to be accessed. Both times the query took about1 hr and 40 minutes
9 objects were created in the users schema, 3 tables, 4 indexes and 2 LOBs.
My question is why these thing s need to be created for tables that are already indexed and why they would not stay populated for quick reuse. If every one of my users create and populate these tables and indexes for each query session, Ill soon run out of disk space, to say nothing about the ridiculously poor performance.
Have I made some basic error in the creation of my business components that cause this condition?