Forum OpenACS Q&A: Using a different tablespace ACS4.2 & Oracle 8.1.7

Collapse
1: Using a different tablespace ACS4.2 & Oracle 8.1.7
Posted by Jennifer Lawrence on 11/26/01 08:28 PM

I would have posted to the ArsDigita web/DB forum but last time I did was told that almost no Tcl talk happens there anymore and I'm better off posting here.

I have ACS 4.2 Tcl installed on Solaris with Oracle 8.1.7. I have created a seperate tablespace to hold some tables with data I will be using to pull data for many pages. I granted dba privileges to the user jendy who accesses the database through AOLServer to the entire tablespace. I am able to log in via SeverManager as jendy and access these tables by specifying tablespace_owner.table_name, but this is not working through a tcl page. I get the following error message:
ora8.c:3908:ora_tcl_command: error in `OCIStmtExecute ()': ORA-00942: table or view does not exist

SQL: select la_name from testpilot !>>>!lodging_amenity where la_id = :info_id
   while executing
"ns_ora 0or1row nsdb0 {select la_name from testpilot.lodging_amenity where la_id = :info_id}"

The code I am trying to execute is:
set la_name [db_string name "select la_name from testpilot.lodging_amenity where la_id = :info_id" -default "Oopsies"]

Is there some special way of specifying a tablespace name within tcl code that I have overlooked?

Collapse
2: Response to Using a different tablespace ACS4.2 & Oracle 8.1.7 (response to 1)
Posted by Patrick Giagnocavo on 11/26/01 08:45 PM
I am curious whether the jendy user is the user you are connecting to the database as, as specified in your AOLServer nsd.tcl file.
Collapse
3: Response to Using a different tablespace ACS4.2 & Oracle 8.1.7 (response to 1)
Posted by Jennifer Lawrence on 11/26/01 08:55 PM
I've just checked the nsd.tcl file and apparently the jendy user is not even specified there.  I may be confused on this point.  It was early this summer when I installed ACS - and it seems like ages ago.  The service name is jendy and the tablespace where most of the ACS data resides is called jendy, and there is a jendy user in Oracle, so I just assumed that jendy was the user that was connecting through AOLServer.

Would you know which user I should be granting the dba privilege to on this alternate tablespace?  It wouldn't be nsadmin would it?

Collapse
4: Response to Using a different tablespace ACS4.2 & Oracle 8.1.7 (response to 1)
Posted by Daryl Biberdorf on 11/26/01 09:27 PM
<P>Jennifer,

<P>Terminology-wise, what you are calling a "tablespace" is more properly called a "schema user" or "owner." (See the <a href="http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/c01intro.htm#15085">Oracle docs</A> for more details.)

<P>Your best bet is most likely to use something like
<PRE>
        alter session set current_schema = YOUR_APPLICATION_SCHEMA;
</PRE>
to change the resolution mechanisms to assume that all object names should be prefixed with <tt>YOUR_APPLICATION_SCHEMA</tt>. Alternatively (and, from a security standpoint, less desirably), you could create public synonyms for the objects that should be exposed to user <tt>jendy</tt>.