Oracle tablespaces

Rails
1

Hello there!

I know some Ruby programming, but I’m starting with Rails today, so
excuse me if it’s a basic question, I searched about it but did not find
an answer.

Is there a way to create a controller using something like
tablespace.table?

I have two distinct tablespaces here and need to specify where is the
table I’m using with the controller, like bigtablespace.people for ex. I
could put a default tablespace for the user who connects to the
database, but the same user must have access on both tablespaces.

Thanks!

2

To solve this easily in the meantime, make a public synonym for the
table.

Create or replace public synonym “MYTABLE” for “SCHEMA”.“MYTABLE”

And as long as any user in the system has access to the base table,
they’ll be able to pull data. (any user will be able to do “select *
from mytable” instead of "select * from schema.mytable)

3

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Nov 22, 2005, at 11:42 AM, Eustáquio Rangel wrote:

Is there a way to create a controller using something like
tablespace.table?

I have two distinct tablespaces here and need to specify where is the
table I’m using with the controller, like bigtablespace.people for
ex. I
could put a default tablespace for the user who connects to the
database, but the same user must have access on both tablespaces.

I’m not an Oracle user, but you can try this:

class Foobar < ActiveRecord::Base
self.table_name = ‘sometablespace.foobars’
end

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDg4+6AQHALep9HFYRAnpdAJ4tOZHC74/EqM395jsdJEzyDjCAXQCfckpb
UHnxUC5wSHzI/rAK9b+w2HI=
=aa+b
-----END PGP SIGNATURE-----

4

Jeremy K. wrote:

could put a default tablespace for the user who connects to the
database, but the same user must have access on both tablespaces.

I’m not an Oracle user, but you can try this:

class Foobar < ActiveRecord::Base
self.table_name = ‘sometablespace.foobars’
end

Unfortunately, that won’t work. All output here with ‘rake
freeze_edge’.

For our helpdesk system in Oracle, we have a model named Issue for an
its.issue
table. The application logs in as ‘its’, so the Issue model has no
set_tablename.

As the user who owns the tablespace, this works:

i = Issue.find(123185)
=> #<Issue:0xb6ccd2bc @attributes=…

As another user who doesn’t have ‘its’ that as a default tablespace, you
get this:

i = Issue.find(123185)
ActiveRecord::StatementInvalid: OCIError: ORA-00942: table or view does
not
exist: select * from (select raw_sql_.*, rownum raw_rnum_ from (SELECT *
FROM
issue WHERE (issue.issueid = 123185) ) raw_sql_ where rownum <= 1) where
raw_rnum_ > 0 from
./script/…/config/…/config/…/vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:78:in
`log’

Adding

set_table_name ‘its.issue’

to the model gets this

i = Issue.find(123185)
ActiveRecord::StatementInvalid: OCIError: ORA-00904:
“ITS”.“ISSUE”.“ITS”.“ISSUEID”: invalid identifier: select * from (select
raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE
(its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where
raw_rnum_ > 0

Note the duplicate ‘its’ here.

I’ll be glad to help out and get this resolved, if somebody else wants
to supply
patches :slight_smile:

Regards,
Blair

–
Blair Z., Ph.D.
[email protected]
Subversion and Orca training and consulting
http://www.orcaware.com/svn/

5

Eustáquio Rangel wrote:

table I’m using with the controller, like bigtablespace.people for ex. I
could put a default tablespace for the user who connects to the
database, but the same user must have access on both tablespaces.

I think by tablespace you mean schema. Is that correct? In that case you
can use synonyms on the database, after granting the apropriate rights
to the user you connect with. Or you can use Jeremys solution (but you
have to grant [select|insert|update|whatever] to your connecting user
first).

6

I’ll be glad to help out and get this resolved, if somebody else wants to
supply
patches :slight_smile:

This is only a workaround, not a full solution, but will using Oracle
synonyms work? I have a rails app running against Oracle and the db user
does not own the tables, but there are public synonyms that make the
tables
accessible to the user.

But then you might run into the problem of rails using user_tab_columns
and
not finding the column definitions – I worked around this with the
following override of OCIAdapter#columns:

module ActiveRecord
module ConnectionAdapters
class OCIAdapter < AbstractAdapter

Use all_tab_columns instead of user_tab_columns

def columns(table_name, name = nil)
cols = select_all(%Q{
select column_name, data_type, data_default, data_length, data_scale
from all_tab_columns where table_name = ‘#{table_name.upcase}’}
).map { |row|

strip quotes from default values

default = row[‘data_default’]
default = default.gsub(/’/,’’).strip if default
OCIColumn.new row[‘column_name’].downcase, default,
row[‘data_length’], row[‘data_type’], row[‘data_scale’]
}
cols
end
end
end
end

This is with AR 0.11. I haven’t noticed if this has been addressed in
edge
rails or not, does anyone know? I can file an item in Trac if not.

Cheers,
/Nick

7

Blair Z. wrote:

I have two distinct tablespaces here and need to specify where is the
self.table_name = ‘sometablespace.foobars’

rownum <= 1) where raw_rnum_ > 0 from
ActiveRecord::StatementInvalid: OCIError: ORA-00904:
“ITS”.“ISSUE”.“ITS”.“ISSUEID”: invalid identifier: select * from (select
raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE
(its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where
raw_rnum_ > 0

Note the duplicate ‘its’ here.

I’ll be glad to help out and get this resolved, if somebody else wants
to supply patches :slight_smile:

This is a bug in AR for sure.
Maybe you can use
alter session set current schema=its;
before executing the query?
I think that should work, but it’s a very ugly hack.

8

This is only a workaround, not a full solution, but will using Oracle synonyms
work? I have a rails app running against Oracle and the db user does not own
the tables, but there are public synonyms that make the tables accessible to
the user.

We were using public synonyms sort of by default (i.e., our DBA just
added them as a matter of course), but as we scaled up development we
had each user with his own schema for doing testing and we noticed that
what was going on in one schema could affect what was going on in
another. After a hair-pulling session one afternoon we identified the
problem, nuked the public synonyms and haven’t looked back.

Rick

http://www.rickbradley.com MUPRN: 533
| friends climb in and say,
random email haiku | wow, that fresh clean leather >scent
| is incredible.

9

Rick B. wrote:

We were using public synonyms sort of by default (i.e., our DBA just
added them as a matter of course), but as we scaled up development we
had each user with his own schema for doing testing and we noticed that
what was going on in one schema could affect what was going on in
another. After a hair-pulling session one afternoon we identified the
problem, nuked the public synonyms and haven’t looked back.

Rick

But you can use private synoyms.
for example:
connect as user a
grant select on person to user_b;
connect as user b
create synonym person for user_a.person;
select * from person;

This works, and the synonym person is only visible to user b

10

Simon S. wrote:

tablespace.table?
I’m not an Oracle user, but you can try this:
its.issue table. The application logs in as ‘its’, so the Issue model
i = Issue.find(123185)

I’ll be glad to help out and get this resolved, if somebody else wants
to supply patches :slight_smile:

This is a bug in AR for sure.
Maybe you can use
alter session set current schema=its;
before executing the query?
I think that should work, but it’s a very ugly hack.

I tried adding this to my AR class, but it didn’t work. Here’s from the
command
line:

$ sqlplus username/passwd@dbhost

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 22 15:20:54 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit
Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining
options

SQL> alter session set current schema=its;
alter session set current schema=its
*
ERROR at line 1:
ORA-00922: missing or invalid option

What’s the correct syntax?

Regards,
Blair

–
Blair Z., Ph.D.
[email protected]
Subversion and Orca training and consulting
http://www.orcaware.com/svn/

11

Blair Z. wrote:

$ sqlplus username/passwd@dbhost
options

SQL> alter session set current schema=its;
alter session set current schema=its
*
ERROR at line 1:
ORA-00922: missing or invalid option

What’s the correct syntax?

oh serry. i wrote this without checking. the correct syntax is
alter session set current_schema=its;

12

Hello guys, thanks for all the answers and tips.

I think by tablespace you mean schema. Is that correct? In that case you
can use synonyms on the database, after granting the apropriate rights
to the user you connect with. Or you can use Jeremys solution (but you
have to grant [select|insert|update|whatever] to your connecting user
first).

The tablespaces are really different files, one not so big named like

not_so_big_and_not_so_often_updated

with a table called PEOPLE, for example, and other

big_and_often_updated

with a table called ORDERS, for example, so we keep it on different
files.
To refer to a table with a user, a need something like

not_so_big_and_not_so_often_updated.PEOPLE
big_and_often_updated.ORDERS

Even if I can insert the “alias” after created the controller, I can’t
see a way to create the controller with the “alias” of them and seems
that I’ll need to hack it after created, as you told me. And using the
current_schema will not allow me to make queries like

select a.customer_id, a.name, b.order, b.value
from
not_so_big_and_not_so_often_updated.PEOPLE a,
big_and_often_updated.ORDERS b
where
a.customer_id=b.customer_id

13

Simon S. wrote:

But you can use private synoyms.
for example:
connect as user a
grant select on person to user_b;
connect as user b
create synonym person for user_a.person;
select * from person;

This works, and the synonym person is only visible to user b

To help Rails to make inroads into corporate environments by sneaking
under the
radar, it would be great Rails could use Oracle databases without having
to make
any modifications of the db, such as adding synonyms, otherwise the DBA
gets
involved and may ask more questions than you’d like :slight_smile: So anything to
use a
schema would help a lot.

Regards,
Blair

–
Blair Z., Ph.D.
[email protected]
Subversion and Orca training and consulting
http://www.orcaware.com/svn/

14

To help Rails to make inroads into corporate environments by sneaking
under the radar, it would be great Rails could use Oracle databases
without having to make any modifications of the db, such as adding
synonyms, otherwise the DBA gets involved and may ask more questions
than you’d like :slight_smile: So anything to use a schema would help a lot.

Accessing tables in a different schema is supported in edge rails.

Personally I find that using private synonyms is the cleanest approach,
but in environments where that isn’t allowed, you can specify a table as

set_table_name "owner.table_name"

If that’s not working for you (again, with edge rails), let me know and
I’ll help you work through it.

15

To help Rails to make inroads into corporate environments by sneaking under
the radar, it would be great Rails could use Oracle databases without
having to make any modifications of the db, such as adding synonyms,
otherwise the DBA gets involved and may ask more questions than you’d like
:slight_smile: So anything to use a schema would help a lot.

We’ve been using Rails on an Oracle database (and on Postgres) since
before RubyConf and we’re not currently using any sort of synonyms, etc.
Our DBAs set up a development schema, a production schema, and N testing
schemas (1 for each developer, one for nightly demo builds, one for
continuous integration builds). I’m not a DBA so perhaps I’m missing a
salient detail, but after removing the troublesome synonyms we haven’t
had any problems in this configuration.

Here’s our database.yml file (with some names changed to protect the
guilty):

<%

determine our database user name from an environment variable

user = (ENV[‘PHOENIX_USER’] || ENV[‘LOGNAME’] || ENV[‘USERNAME’])

puts “User [#{user}]”

determine which database we are connecting to via an environment

variable
case ENV[‘PHOENIX_DB’]
when ‘oracle’ # connecting to an Oracle database
puts “Using Oracle”
%>
production:
adapter: oci
host: CNPROD
username: unknown
password: unknown

test:
adapter: oci
host: CNVOL
username: <%= user ? user : ‘cnet’ %>
password: XXXXXXXX

development:
adapter: oci
host: CNDEV
username: cnet
password: cnet

<%
else # connecting to a PostgreSQL database
%>
production:
adapter: postgresql
database: centernet
host: dbhost
username: centernet
password: XXXXXXXXX

test:
adapter: postgresql
database: rewrite_<%= user ? “#{user}_” : ‘’ %>vol
host: dbhost
username: centernet
password: XXXXXXXX

development:
adapter: postgresql
database: rewrite
host: dbhost
username: centernet
password: XXXXXXXXX
<% end %>

(Yes, I’m going to make another pass through there and merge some of the
commonalities between rules).

So, by default we’re using Postgres, but we can use Oracle by setting an
environment variable (PHOENIX_DB=oracle).

In the Oracle environment, each login has its own schema, so everyone’s
got an isolated space for test_unit/test_functional.

Again, maybe there’s some voodoo they pulled on the back-end that I’m
not clear on, but I don’t know of anything at the moment.

Rick

http://www.rickbradley.com MUPRN: 156
| it is based on
random email haiku | the Prism2 chipset, then it
| is compatible.

16

I believe you’ve muddled the concepts of tablespace and schema.

Tablespaces can be implemented behind the scenes as files in the
underlying OS (though often a single tablespace is also made up of
several files), and it’s fairly common to put tables with different
characteristics into different tablespaces.

But within SQL you don’t reference a table with it’s tablespace. If
you’re doing something like…

SELECT * FROM foo.people

…then “foo” is a schema/user name, NOT a tablespace name.

17

Simon S. wrote:

command line:
Production
What’s the correct syntax?

oh serry. i wrote this without checking. the correct syntax is
alter session set current_schema=its;

Thanks, that works like a charm. Changing my model to read like this:

class Issue < ActiveRecord::Base
connection.execute(‘alter session set current_schema=its’)
end

got the user to properly see the table.

Regards,
Blair

18

I believe you’ve muddled the concepts of tablespace and schema.

You’re right! I messed with some concepts because the tablespaces are
the same name as the schemas. I have a “parameters” tablespace and user,
and a “data” tablespace and user. :-p

But within SQL you don’t reference a table with it’s tablespace. If
you’re doing something like…
SELECT * FROM foo.people
…then “foo” is a schema/user name, NOT a tablespace name.

That’s the way the queries are here. Using schema/user.table.
But, being a tablespace or schema, the best way is using the synonyms or
edge rails, right? :slight_smile:
You gave me a very good tip. I think we’ll be able to use synonyms.
Thanks!

19

Michael Schoen wrote:

set_table_name "owner.table_name"

If that’s not working for you (again, with edge rails), let me know and
I’ll help you work through it.

Hi Michael,

Yes, using set_table_name isn’t working for me.

When I do that, I get this error:

i = Issue.find(123185)
ActiveRecord::StatementInvalid: OCIError: ORA-00904:
“ITS”.“ISSUE”.“ITS”.“ISSUEID”: invalid identifier: select * from (select
raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE
(its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where
raw_rnum_ > 0

Note the double appearance of its in the WHERE clause.

Regards,
Blair

–
Blair Z., Ph.D.
[email protected]
Subversion and Orca training and consulting
http://www.orcaware.com/svn/

20

Yes, using set_table_name isn’t working for me.
When I do that, I get this error:
Note the double appearance of its in the WHERE clause.

That may be a bug then, I’ll take a look.

Executing the “current_schema” statement is a nice hack, but as you
pointed out in your other note, it’s not a terribly good one. Let’s
track down the bug and fix it.