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;