oracle 如何在oracle home中创建目录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28723783/
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
how to create directory in oracle home
提问by Aramillo
I need to create an oracle directory in oracle installation folder. In my case this path is D:\app\Administrator\product\11.2.0\dbhome_1. I tried create directory ora_dir as '\', but this make reference to D:\.
Is there any way to create the directory pointing to oracle_home?
我需要在oracle 安装文件夹中创建一个oracle 目录。在我的情况下，这条路径是D:\app\Administrator\product\11.2.0\dbhome_1. 我试过了create directory ora_dir as '\'，但这要参考D:\. 有没有办法创建指向oracle_home的目录？
Regards.
问候。
回答by Alex Poole
If you're already doing this dynamically from a procedure, you could get the ORACLE_HOME environment variable (as it was set when the instance started anyway) via a Java call, but you need a separate function:
如果您已经从过程中动态执行此操作，则可以通过 Java 调用获取 ORACLE_HOME 环境变量（因为它在实例启动时已设置），但您需要一个单独的函数：
create or replace function getenv(name varchar2) return varchar2
as language java
name 'java.lang.System.getenv(java.lang.String) return java.lang.String';
/
create or replace procedure p42 as
begin
execute immediate q'[CREATE or replace DIRECTORY DIRECTORY_DIR AS ']'
|| getenv('ORACLE_HOME') || q'[']';
end;
/
Assuming both compile OK, when executed the directory is created with the path from the environment variable:
假设两者都编译成功，执行时会使用环境变量中的路径创建目录：
exec p42;
PL/SQL procedure successfully completed.
select directory_path
from all_directories
where directory_name = 'DIRECTORY_DIR';
DIRECTORY_PATH
----------------------------------------
/dboracle/orabase/product/11.2.0
If your procedure is in a package, the function can be in there too, privately if you prefer. And you don't need any additional privileges to use the Java call, just create procedure, which you already have in this case.
如果您的过程在一个包中，则该功能也可以在其中，如果您愿意，可以私下进行。并且您不需要任何额外的权限来使用 Java 调用，只需要create procedure，在这种情况下您已经拥有了。
Unfortunately this doesn't work in Windows as ORACLE_HOME is set in the registry, not the environment; it may be possible to get information from the registrybut you may also have more than one Oracle Home so you'd have to determine which registry key to use.
不幸的是，这在 Windows 中不起作用，因为ORACLE_HOME 设置在注册表中，而不是环境中；可以从注册表中获取信息，但您也可能有多个 Oracle Home，因此您必须确定要使用哪个注册表项。
There is also (as Aramillo found) an [undocumented] built-in DBMS_SYSTEMpackagethat can provide the same information; with that you could do:
还有（正如 Aramillo 发现的）一个 [未记录的] 内置DBMS_SYSTEM包可以提供相同的信息；你可以这样做：
create or replace procedure p42 as
l_oracle_home varchar2(100);
begin
dbms_system.get_env('ORACLE_HOME', l_oracle_home);
execute immediate q'[CREATE or replace DIRECTORY DIRECTORY_DIR AS ']'
|| l_oracle_home || q'[']';
end;
/
But you would need to have execute permission granted on that package, and it's undocumented status might give you pause; some of the functionality seems to be restricted to SYSDBA anyway (note 159968.1).
但是您需要对该包授予执行权限，并且它的未记录状态可能会让您暂停；无论如何，某些功能似乎仅限于 SYSDBA（注释 159968.1）。
While poking around My Oracle Support looking for DBMS_SYSTEMreferences, I also stumbled over this option:
在浏览 My Oracle Support 以寻找DBMS_SYSTEM参考资料时，我也偶然发现了这个选项：
select nvl(substr(file_spec, 1, instr(file_spec, 'lib') -2),
substr(file_spec, 1, instr(lower(file_spec), 'bin') -2)) as oracle_home
from dba_libraries
where library_name='DBMS_SUMADV_LIB';
... which apparently also works on Windows, but you need sufficient privileges to see the data dictionary entries.
...显然也适用于 Windows，但您需要足够的权限才能查看数据字典条目。
回答by tilley31
You need to provide the full path:
您需要提供完整路径：
CREATE DIRECTORY ORA_DIR AS 'D:\app\Administrator\product.2.0\dbhome_1';
