Oracle 连接 URL 中的默认架构
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2353594/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Default Schema in Oracle Connection URL
提问by netic
I'd like to set default database schema in Oracle Connection URL
我想在 Oracle Connection URL
jdbc:oracle:thin:@<server>:<port1521>:<sid>
My sample SQL statement:
我的示例 SQL 语句：
select monkey_name from animals.monkey
I need to query database without schema prefix animals.i.e. when I run this statement
我需要查询没有模式前缀的数据库，animals.即当我运行这个语句时
select monkey_name from monkey
it will use animalsschema by default.
它将animals默认使用架构。
What do I need to specify in connection URL above get such effect?
我需要在上面的连接 URL 中指定什么才能达到这种效果？
Thanks.
谢谢。
采纳答案by Gary Myers
You can't put anything in the connection URL.
您不能在连接 URL 中放置任何内容。
In Oracle each user has their own schema (even if doesn't contain any objects) and that is their default schema. Once logged in/connected, they can change their default schema with an
在 Oracle 中，每个用户都有自己的架构（即使不包含任何对象），这是他们的默认架构。登录/连接后，他们可以使用
ALTER SESSION SET CURRENT_SCHEMA=animals
So you'd need to do the extra statement after connecting. It is possible to have a logon trigger on the user and/or database that will run this when they log in. I'd personally prefer an explicit statement when an application connects.
所以你需要在连接后做额外的声明。可以在用户和/或数据库上设置一个登录触发器，在他们登录时运行该触发器。我个人更喜欢在应用程序连接时使用显式语句。
回答by Daniel Worthington-Bodart
If you use C3POyou can make it do it when it checks the connection out.
如果您使用C3PO，您可以在它检查连接时执行此操作。
As properties:
作为属性：
c3p0.preferredTestQuery=alter session set current_schema=animals
c3p0.testConnectionOnCheckout=true
As Java code:
作为Java代码：
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setPreferredTestQuery("alter session set current_schema=animals");
dataSource.setTestConnectionOnCheckout(true);
Downside is this will happen every time the connection is taken out of the pool.
缺点是每次从池中取出连接时都会发生这种情况。
If you are using a JDBC connection yourself you could just do:
如果您自己使用 JDBC 连接，您可以这样做：
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = getConnection("jdbc:oracle:thin:@//server:1521/instance", "username", "password");
connection.createStatement().execute("alter session set current_schema=animals"));
回答by Rene
What about the use of synonyms?
同义词的使用呢？
create synonym monkey for animals.monkey;
select monkey_name from monkey
回答by robothy
You can create a trigger using connection DB user to change the current schema.
您可以使用连接数据库用户创建触发器来更改当前架构。
create or replace trigger SET_SCHEMA_AFTER_LOGON
after logon on database
begin
execute immediate 'alter session set CURRENT_SCHEMA=animals';
end SET_SCHEMA_AFTER_LOGON;
