SQL 如何找到创建 Oracle DBF 文件的默认位置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27017028/
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 find the default location in which Oracle DBF files are created?
提问by Michael
During the creation of a new Tablespace in Oracle Database, the user has to enter the DBF file name that he (or she) want to use. The DBF file is then created in a specific location.
在 Oracle 数据库中创建新表空间期间，用户必须输入他（或她）想要使用的 DBF 文件名。然后在特定位置创建 DBF 文件。
The user may also specify a path in which the DBF file should be created.
用户还可以指定应该在其中创建 DBF 文件的路径。
I need to find a way to get the default location of the DBF file.
我需要找到一种方法来获取 DBF 文件的默认位置。
I know how to do it in MS Sql by using a SQL query:
我知道如何使用 SQL 查询在 MS Sql 中执行此操作：
select substring(physical_name, 1, charindex(N'master.mdf', lower(physical_name)) - 1) from master.sys.master_files where database_id = 1 and file_id = 1;
But I have no idea about how to do it in Oracle.I've tried several things:
但我不知道如何在 Oracle 中做到这一点。我尝试了几件事：
- Ran a query on
all_directories- didn't find any information there - Looked at the
v$datafileview - realized that this view and the others are accesible to database administrators only
- 查询
all_directories- 在那里没有找到任何信息 - 看着
v$datafile观点-意识到这个视图和其他人入店只有数据库管理员
There are also several limitations:
还有几个限制：
- The Oracle Database may be installed on another machine with a different operating system.
- My application may connect to the database with a user who is not an admin.
- It should be done preferably with a SQL query.
- Oracle 数据库可能安装在具有不同操作系统的另一台机器上。
- 我的应用程序可能会使用不是管理员的用户连接到数据库。
- 最好使用 SQL 查询来完成。
Any help is much appreciated.
任何帮助深表感谢。
回答by Cyryl1972
To determine the default datafiles location, I am using the following query:
为了确定默认数据文件位置，我使用以下查询：
SELECT DISTINCT SUBSTR (file_name,
1,
INSTR (file_name,
'/',
-1,
1))
FROM DBA_DATA_FILES
WHERE tablespace_name = 'SYSTEM'
It works for ME because all our datafiles are installed in a the same directory.
它适用于我，因为我们所有的数据文件都安装在同一个目录中。
回答by nekperu15739
Use this sentence with system user:
对系统用户使用这句话：
Select * from dba_data_files ;
回答by Marco Baldelli
DB_CREATE_FILE_DESTspecifies the default location for Oracle-managed datafiles (see its entryin the Database Reference).
DB_CREATE_FILE_DEST指定 Oracle 管理的数据文件的默认位置（请参阅数据库参考中的条目）。
You can retrieve its value with the following SQL query:
您可以使用以下 SQL 查询检索其值：
select value from v$parameter where name = 'db_create_file_dest'
To access the v$parameterview a user needs at least the SELECT_CATALOG_ROLErole.
要访问v$parameter视图，用户至少需要SELECT_CATALOG_ROLE角色。
回答by daniele daniele
As default the .dbffiles are stored under something like:
默认情况下，.dbf文件存储在以下位置：
/u01/app/oracle/product/__VERSION__/__DB_NAME__/dbs
where __VERSION__may be something like "11.0.1.1" and __DB_NAME__is your DB
哪里__VERSION__可能是“11.0.1.1”之类的东西，__DB_NAME__是你的数据库
回答by Mark Wagoner
There is no true default location for a data file in Oracle, the closest thing would be a directory under ORACLE_HOME. If I recall (I don't have access to an Oracle DB at the moment to verify this) the directory structure varies slightly based on the OS and version.
Oracle 中的数据文件没有真正的默认位置，最接近的是 ORACLE_HOME 下的目录。如果我记得（我目前无法访问 Oracle DB 来验证这一点），目录结构会根据操作系统和版本略有不同。
The reason there is no default is because you will typically want to spread your data across physical drives to avoid contention. You will often have some tables that get hit almost all of the time while others are access much less frequently. At the least you want to keep your temporary and redo/undo separate from your data.
没有默认值的原因是因为您通常希望将数据分布在物理驱动器上以避免争用。您经常会遇到一些表几乎一直被命中，而其他表的访问频率要低得多。至少您希望将临时和重做/撤消与数据分开。
回答by Andrei Z
Try this:
尝试这个：
select substr(name, 1, instr(name, 'USER') - 1) prefix from v$datafile where name like '%USER%';
回答by arve0
You can view the default location for tablespaces with
您可以使用以下命令查看表空间的默认位置
show parameter DB_CREATE_FILE_DEST;
You can change the parameter with
您可以更改参数
alter system set DB_CREATE_FILE_DEST='/ORCL/u02/app/oracle/oradata/ORCL';
回答by user13267719
select DISTINCT SUBSTR (file_name,1, instr(file_name, 'SYSTEM') - 1) FROM DBA_DATA_FILES WHERE tablespace_name = 'SYSTEM';
select DISTINCT SUBSTR (file_name,1, instr(file_name, 'SYSTEM') - 1) FROM DBA_DATA_FILES WHERE tablespace_name = 'SYSTEM';
