[13:34:30] hi everybody! [13:34:42] have a problem here, and would appreciate any help! [13:35:07] i've been using sybase for about 2 years, but now i have a need to migrate to oracle [13:36:10] but i have this problem... in sybase, when you log into a database, you can choose a database to you, and the dafault schema, so that you dont have to write the schema name before the each table you refer to [13:37:33] but in oracle, i couldnt find this option, and each time have to show the alias, i mean, the schema name to use [13:38:37] for instance, if i need to use a table, that belongs to a schema called "billing", and i log in under a different user, each time i need to specify "select * from billing.books where ...." [13:38:42] any suggestions?? [13:38:50] or.. anybody alive in here? :) [13:42:31] hehe. [13:42:34] hi [13:44:53] progreSSive: I dont know of any other way to address a table from a different schema. [13:45:03] but im no guru. [13:45:19] synonym [13:45:51] progreSSive: synonym helps you [13:51:48] Vovka, oh, thanks [13:52:21] Vovka, but do i need to create synonyms for each table?? [13:52:35] Vovka, it's just, there are about 100s of tables [13:52:59] miken_ [i=miken@outbound.silenceisdefeat.org] зашел на #oracle [14:00:06] progreSSive: you can do it dynamicly with SQL [14:01:33] Vovka, hmm.. could you please tell me, how? :) [14:02:01] select 'create synonym '||table_name||' for '||owner||'.'||table_name||' ; ' from dba_tables where blablabla [14:02:27] this SQL makes script that creates synonyms [14:03:02] hm [14:03:04] for all tables wich selects [14:03:04] i see [14:03:16] and how about "alter session"? [14:03:22] heard about it? [14:03:25] current_schema ? [14:03:32] yeah [14:03:39] up to you [14:04:23] synonyms can be public and no need ot use alter sesion for every user [14:04:24] which one is better and easier to realize? [14:04:36] well.. the problem here is.. [14:04:49] i have several modules, that used to work with sybase [14:05:32] and there are hundreds of places and queries i would have to re-write, if i had to change the select, insert, update statements [14:05:57] in sybase, in the beginning of your connect to the database, you say "use dbname" [14:06:11] and under any user, you can you the default schema [14:06:52] but here, the structure is a little different, oracle has users, and each user is assigned a role, and each user can use his own schema [14:07:04] but no single default schema [14:07:10] as far as i got it, right? [14:07:36] user ownes schema [14:07:44] or is it possible to assign a schema as a default schema to a group of users?? [14:07:54] no [14:08:28] so a user cannot own a schema of a different user? [14:08:59] user owns schema, names resolution is schema.table if own schema - just table [14:09:17] in oracle user = schema )) [14:09:40] so what i mean is, in my module, all the tables are referred to as just the table name [14:09:45] no schema used [14:09:52] and all the users could access it [14:10:22] and now, i'll have to add the schema name to the tables [14:10:29] I think public synonyms and grants on tables will help [14:10:33] but i dont really want to do that :) [14:11:59] what if i do this.. [14:12:10] when the user connects to use the module [14:12:35] the first query would be "alter session current_schema=billing" [14:12:45] and use the tables as they are [14:12:56] not changing the prefixes [14:13:03] would that be okay? [14:13:20] yea [14:13:27] it should ) [14:13:43] nice [14:14:06] i'll try, and if it doesnt work, i'll try the synonym stuff ) [14:14:11] thanks a lot, man [14:14:14] appreciate! :) [14:14:25] you are wellcome [14:14:32] кстати, ты откуда? :) [14:14:35] Бля [14:14:39] ))))