1. 查看数据库启动状态
SQL> select status from v$instance;
2. 查看数据库名or实例名
SQL> select name from v$database; --- 查看数据库名 SQL> select instance_name from v$instance; --- 查看实例名
3. 查看数据库连接数&会话相关
SQL> select count(*) from v$session; --- oracle的连接数 SQL> select count(*) from v$session where status='ACTIVE'; ---oracle的并发连接数 SQL> select username,count(username) from v$session where username is not null group by username; ---查看不同用户的连接数
SQL> select sid,serial#,username,program,machine,status from v$session; ---列出当前数据库建立的会话情况 ---SID 会话(session)的ID号; ---SERIAL# 会话的序列号,和SID一起用来唯一标识一个会话; ---USERNAME 建立该会话的用户名; ---PROGRAM 这个会话是用什么工具连接到数据库的; ---STATUS 当前这个会话的状态,ACTIVE表示会话正在执行某些任务,INACTIVE表示当前会话没有执行任何操作; ---如果DBA要手工断开某个会话,则执行: ---alter system kill session \'SID,SERIAL#\'; SQL> SELECT username, machine, program, status, COUNT (machine) AS 连接数量 FROM v$session GROUP BY username, machine, program, status ORDER BY machine; ---分组统计
4. 查看数据库用户信息
SQL> select * from all_users; ---查看所有用户 SQL> select * from V$PWFILE_USERS; ---查看哪些用户有sysdba或sysoper系统权限
5. 查看表空间使用情况
set pages 12222 lines 132; set serveroutput on size 1000000; col tablespace_name format a30; col autoextensible format a7; SELECT a.tablespace_name, round(a.s, 2) AS "CURRENT_TOTAL(MB)" , round(a.s - f.s, 2) AS "USED(MB)" , f.s AS "FREE(MB)" , round(f.s / a.s * 100, 2) AS "FREE%" , g.autoextensible, round(a.ms, 2) AS "MAX_TOTAL(MB)" FROM ( SELECT d.tablespace_name, SUM(bytes / 1024 / 1024) AS s , SUM(decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024) AS ms FROM dba_data_files d GROUP BY d.tablespace_name ) a, ( SELECT f.tablespace_name, SUM(f.bytes / 1024 / 1024) AS s FROM dba_free_space f GROUP BY f.tablespace_name ) f, ( SELECT DISTINCT tablespace_name, autoextensible FROM DBA_DATA_FILES WHERE autoextensible = 'YES' UNION SELECT DISTINCT tablespace_name, autoextensible FROM DBA_DATA_FILES WHERE autoextensible = 'NO' AND tablespace_name NOT IN ( SELECT DISTINCT tablespace_name FROM DBA_DATA_FILES WHERE autoextensible = 'YES' ) ) g WHERE a.tablespace_name = f.tablespace_name AND g.tablespace_name = f.tablespace_name ORDER BY "FREE%";
6. 查看数据文件存放路径
select name from v$datafile;
7. 查看是否启动闪回
select * from v$flash_recovery_area_usage;
8. 查看用户下的表
--查看指定用户下的表:
select * from all_tables where owner='USER01';
---USER01为用户名,必须大写。
--查看当前登陆用户下的表:
select table_name from user_tables;
9. 启动/关闭数据库
---关闭数据库 SQL> shutdown immediate; ---启动数据库 方式1:直接启动 SQL> startup; ---启动数据库 方式2: 按步骤启动 SQL> startup nomount; SQL> alter database mount; SQL> alter database open;