如何查看oracle数据文件的地址

佚名 2024-05-13 17:00:28 举报

篇首语：本文由小编为大家整理，主要介绍了如何查看oracle数据文件的地址相关的知识，希望对你有一定的参考价值。

参考技术A

1、执行：select name from v$datafile查询表空间中数据文件具体位置

结果集就一列：

NAME

D:\APP\AIRUCHEN\ORADATA\ORCL\USERS01.DBF

2、执行：Select * FROM DBA_DATA_FILES;查询表空间中数据文件具体位置和文件名，表空间名等，比上一个全。

结果集：

FILE_NAME (数据文件名) TABLESPACE_NAME （表空间名）其他表头.............................

D:\APP\AIRUCHEN\ORADATA\ORCL\USERS01.DBF SYSTEM 其他数据...........................

扩展资料：

Linux下oracle数据库常用命令

Oracle用户下执行：

$ sqlplus system/manager @ file.sql 执行sql脚本文件

$ sqlplus system/manager 登录sqlplus，使用system用户

$ sqlplus /nolog 以不连接数据库的方式启动sqlplus，启动数据时会用到

$ lsnrctl status/stop/start oracle的监听器listener状态查看/停止/启动

$ imp system/manager file=/tmp/expfile.dmp log=/tmp/implogfile.log ignore=y fromuser=expuser touser=impuser 用户模式表数据导入，这里只使用了几个参数，还有好多没有用到的参数，如果没有特别指定值，就使用默认的值。

如何查看数据库alert日志文件

1、登录Oracle服务器，切换到oracle用户下

#su - oracle

注：需要进入到命令行模式下

2、进入到sqlplus接口

$sqlplus "/as sysdba"

注意：使用角色sysdba

3、查询alter日志目录

show parameter dump

4、找到alert日志

参考技术A 查看数据库的alert日志文件可以通过sq实现。
1、从X$DBGALERTEXT 视图中读取alert目录内容：
SQL> select message_text from X$DBGALERTEXT where rownum <= 20;

MESSAGE_TEXT
-----------------------------------------------------------------------------------------------------------------
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in client-side pfile /u01/app/oracle/admin/LIN11G/pfile/init.ora on machine linux03
System parameters with non-default values:
processes = 150
memory_target = 404M
control_files = "/u01/oradata/LIN11G/control01.ctl"
control_files = "/u01/oradata/LIN11G/control02.ctl"
control_files = "/u01/oradata/LIN11G/control03.ctl"
db_block_size = 8192
20 rows selected.

2、从X$DBGALERTEXT中查找文件的逻辑名称和文件名：
SQL> select lpad(" ",lvl," ")||logical_file file_name
from X$DBGDIREXT
where rownum <=20;
FILE_NAME
--------------------------------------------------------
asm
lsnrctl
diagtool
rdbms
lin11g
lin11g
ir
recovery_history.ir
incident
incdir_33815
lin11g_ora_10330_i33815.trc
lin11g_ora_10330_i33815.trm
incdir_25347
lin11g_ora_32614_i25347.trc
lin11g_ora_32614_i25347.trm
incdir_25417
lin11g_ora_1225_i25417.trm
lin11g_ora_1225_i25417.trc
incdir_43459
lin11g_ora_9467_i43459.trm

20 rows selected.

If you’re building some custom alert log monitoring, then starting from 11g these x$ tables can help you. 参考技术B

1、查看操作系统版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>

2、查看预警日志文件（alert_sid.log）的位置

SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ -----------
background_core_dump string partial
background_dump_dest string /u01/oracle/admin/bdump
core_dump_dest string /u01/oracle/admin/cdump
max_dump_file_size string 10240
shadow_core_dump string partial
user_dump_dest string /u01/oracle/admin/udump

3、创建目录alert

注意：directory不是实体，只是一个指向，指向os中一个路径

SQL> create or replace directory alert as "/u01/oracle/admin/bdump";
Directory created.
SQL>

4、创建外部表alert

SQL> create table alert
1 (log varchar2(1000))
2 organization external
3 (type oracle_loader
4 default directory alert
5 access parameters
6 (records delimited by newline)
7 location ("alert_PROD.log"))
8 reject limit unlimited;
Table created.

5、查看alert中的内容

SQL> select * from alert where rownum<20;
LOG -----------------------------------------------------------
Wed Feb 27 11:05:11 2013
Starting ORACLE instance (normal)
Cannot determine all dependent dynamic libraries for /proc/self/exe
Unable to find dynamic library libocr10.so in search paths
RPATH =
/ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/ade/aime1_build2101/oracle/has/lib/:
LD_LIBRARY_PATH is not set!
The default library directories are /lib and /usr/lib
Unable to find dynamic library libocrb10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as
/u01/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
19 rows selected.
SQL>

6、看看数据库有哪些可爱的ORA- 错误吧

SQL> select * from alert where log like "%ORA-%";
LOG ------------------------------------------------------------------
ORA-959 signalled during: alter database default tablespace users...
ORA-959 signalled during: drop tablespace uses...
ORA-12919 signalled during: drop tablespace users...
ORA-1549 signalled during: drop tablespace users...
ORA-1505 signalled during: alter database add logfile group 1
ORA-1184 signalled during: alter database add logfile group 1
ORA-1013 signalled during: alter tablespace tts read only...
ORA-1013 signalled during: alter tablespace tts read only...
ORA-1013 signalled during: alter tablespace users read only...
ORA-1539 signalled during: alter tablespace users read only...

以上是关于如何查看oracle数据文件的地址的主要内容，如果未能解决你的问题，请参考以下文章