oracle 数据库状态查询

来源：互联网发布：单词默写软件编辑：程序博客网时间：2024/05/01 16:29

oracle 数据库状态查询

#切换用户
[root@localhost ~]# su - oracle

#查看环境变量
[oracle@localhost ~]$ echo $ORACLE_SID
orcl

#启动监听
[oracle@localhost ~]$ lsnrctl start

#登录sqlplus
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 15 19:21:59 2015

Copyright (c) 1982, 2005, Oracle. All rights reserved.

#登录数据库
SQL> connect / as sysdba
Connected to an idle instance.

#启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size 1220820 bytes
Variable Size 171970348 bytes
Database Buffers 427819008 bytes
Redo Buffers 7163904 bytes
Database mounted.
Database opened.

#查看数据库版本
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> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 3

#查看数据库状态
SQL> select * from v$database;

#查看数据库的控制文件，位置
SQL> select * from v$controlfile;

STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

/u01/app/oracle/oradata/orcl/control01.ctl
NO 16384 430

/u01/app/oracle/oradata/orcl/control02.ctl
NO 16384 430

STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

/u01/app/oracle/oradata/orcl/control03.ctl
NO 16384 430

#查看数据库数据文件状态
SQL> select * from v$datafile;

#查看数据库日志文件，位置
SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO

2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---

1 STALE ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO

#查看实例内存情况，sga
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 580M
sga_target big integer 580M

SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO

2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---

1 STALE ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO

#查看实例内存情况，shared
SQL> show parameter shared

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 8178892
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 1

#查看实例内存情况，db_cache
SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 0

#查看实例内存情况，log
SQL> show parameter log

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 7053312
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
logmnr_max_persistent_sessions integer 1
remote_login_passwordfile string EXCLUSIVE

#查询表空间状况，描述了表空间的空闲大小。
SQL>select * from dba_free_space;

#查询表空间文件位置，及状况。描述了数据库中的数据文件。
SQL>select * from dba_data_files;

#查询表空间，描述了数据库中的表空间。
SQL>select * from dba_tablespaces;

#该语句通过查询dba_free_space，dba_data_files，dba_tablespaces这三个数据字典表，得到了表空间名称，表空间类型，区管理类型，以"兆"为单位的表空间大小，已使用的表空间大小及表空间利用率。
select
a.a1 表空间名称,c.c2 类型,c.c3 区管理,b.b2/1024/1024 表空间大小M,(b.b2-a.a2)/1024/1024 已使用M,substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2
from
dba_free_space group by tablespace_name) a,(select tablespace_name b1,sum(bytes) b2
from
dba_data_files group by tablespace_name) b,(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1

#该语句通过查询dba_free_space，dba_data_files，dba_tablespaces这三个数据字典表，得到了表空间名称，表空间类型，区管理类型，以"兆"为单位的表空间大小，已使用的表空间大小及表空间利用率。
select
a.a1 tablespacename,c.c2 tablespacetype,c.c3 segmentname,b.b2/1024/1024 tablespacesizeM,(b.b2-a.a2)/1024/1024 usedM,
substr((b.b2-a.a2)/b.b2*100,1,5) usedpersent
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2
from
dba_free_space group by tablespace_name) a,(select tablespace_name b1,sum(bytes) b2
from
dba_data_files group by tablespace_name) b,(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1

#查看Oracle数据库中数据文件信息的命令方法
select
b.file_name 物理文件名,b.tablespace_name 表空间,b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name

#查看Oracle数据库中数据文件信息的命令方法
select
b.file_name datafileplace,b.tablespace_name tablespacename,b.bytes/1024/1024 tablespacesizeM,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) usedpersent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name

#查看临时表空间和数据库文件的方法
select
a.TABLESPACE_NAME 表空间名称,b.bytes 大小bytes,b.file_name 数据文件名
from dba_tablespaces a, dba_data_files b
Where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.CONTENTS='TEMPORARY'

#查看临时表空间和数据库文件的方法
select a.TABLESPACE_NAME tablespacename,b.bytes tablespacesizebytes,b.file_name datafilename
from DBA_TABLESPACES a,DBA_DATA_FILES b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.CONTENTS='TEMPORARY'

#关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

#退出sqlplus
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

0 0