运维笔记--oracle数据库常用查询语句

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;