1.查看oracle数据库字符集

SQL>select (select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME = 'NLS_LANGUAGE')||'_'||

(select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME = 'NLS_TERRITORY')||'.'||

(select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME = 'NLS_CHARACTERSET')

from dual;

2.查看参数文件

从spfile中创建pfile：SQL>create pfile='/tmp/pfile_temp' from spfile;

操作系统中查看：$ cat /tmp/pfile_temp

3.查看数据库文件及存储路径

数据文件：

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/oradata/frank/system01.dbf

/u01/oradata/frank/sysaux01.dbf

/u01/oradata/frank/undotbs01.dbf

/u01/oradata/frank/users01.dbf

控制文件：

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/oradata/frank/control01.ctl

/u01/flash_recovery_area/frank/control02.ctl

日志文件：

SQL> select member from v$logfile order by group#;

MEMBER

--------------------------------------------------------------------------------

/u01/oradata/frank/redo01.log

/u01/oradata/frank/redo02.log

/u01/oradata/frank/redo03.log

4.表空间所占系统空间大小

永久表空间：

SQL> select tablespace_name,sum(bytes)/1024/1024 MB from dba_data_files group by tablespace_name;

TABLESPACE_NAME MB

------------------------------ ----------

UNDOTBS1 95

SYSAUX 520

USERS 5

SYSTEM 670

临时表空间：

SQL> select tablespace_name,sum(bytes)/1024/1024 MB from dba_temp_files group by tablespace_name;

TABLESPACE_NAME MB

------------------------------ ----------

TEMP 29

5.查看alert文件位置
SQL> show parameter dump

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_core_dump string partial

background_dump_dest string /u01/diag/rdbms/frank/frank/trace

core_dump_dest string /u01/diag/rdbms/frank/frank/cd ump

max_dump_file_size string unlimited

shadow_core_dump string partial

user_dump_dest string /u01/diag/rdbms/frank/frank/trace

$ cd /u01/diag/rdbms/frank/frank/trace

该目录下的alert_frank.log文件即为oracle的告警日志文件

6.查看实例名和打开模式

SQL> select NAME,OPEN_MODE from v$database;

NAME OPEN_MODE

--------- --------------------

FRANK READ WRITE

7.查询系统当前的scn号：

SQL> select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)

----------------------------------------

1032767

ITPub博客

oracle常用查询命令

最新文章