目录
1.查看表空间
col tablespace_name format a10;
col file_name format a30;
col total_space format a10;
SELECT tablespace_name,
file_name,
round(bytes/(1024*1024*1024), 2)||' GB' total_space
FROM dba_data_files
ORDER BY tablespace_name;
2.查看用户
col USERNAME format a15;
col ACCOUNT_STATUS format a20;
col DEFAULT_TABLESPACE format a20;
select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE from dba_users;
USERNAME ACCOUNT_STATUS DEFAULT_TA
------------------------------ -------------------------------- ----------
NEWIRC OPEN NEWIRC
BAE OPEN BAE
FINEDB51 OPEN FINEDB51
EOS_WORKFLOW OPEN ARP
UFIDA_NC OPEN NNC_DATA01
.....
3.查看数据库内存
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 233984M
sga_target big integer 233984M
4.查看数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
5.oracle归档情况
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA/archivelog
Oldest online log sequence 4695
Next log sequence to archive 4696
Current log sequence 4696
6.查看redo log日志位置
SQL> col member format a30;
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------
1 +DATA/newarpdb/redo01.log
2 +DATA/newarpdb/redo02.log
3 +DATA/newarpdb/redo03.log
4 +DATA/newarpdb/redo04.log
7.查看数据库的控制文件
SQL> col name format a60;
SQL> select status,name from v$controlfile;
STATUS NAME
------- ------------------------------------------------------------
+DATA/newarpdb/control01.ctl
+DATA/newarpdb/control02.ctl
8.查看RMAN的备份情况
COL STATUS FORMAT a9
COL hrs FORMAT 999.99
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,
ELAPSED_SECONDS/3600 hrs
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
9.FRA的相关信息
V$RECOVERY_FILE_DEST：提供在数据库中定义的FRA的概述。提供配置的FRA的大小、所使用的空间数量、可以回收多少空间，以及FRA中的文件数量。
V$FLASH_RECOVERY_AREA_USAGE：FRA空间各个文件类型的详细信息。
可以查看FRA占用空间的大小:
SQL> set linesize 100
SQL> col parameter for a40
SQL> col value for a40
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 7.03 //当时环境是99%已经满了 0 7
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
已选择7行
10.查看Oracle数据库的进程数配置
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 16
gcs_server_processes integer 6
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 2000
11.Oracle数据库的可用连接数
如果是RAC数据库，会有两个值如下:
SQL> select trim(limit_value-current_utilization) from gv$resource_limit
2 where resource_name='processes';
TRIM(LIMIT_VALUE-CURRENT_UTILIZATION)
----------------------------------------
1906
1918