Oracle学习笔记之DBA常用SQL语句

1、查询表空间大小（数据库的大小）

select tablespace_name, sum(user_bytes) from dba_data_files group by tablespace_name

union

select tablespace_name, sum(user_bytes) from dba_temp_files group by tablespace_name;

2、查询数据对象的大小（数据对象占用的空间）

select segment_name,segment_type,bytes from user_segments where segment_name in ('SP_INFO');

3、查询当前会话的sid

select sid from v$session where audsid=userenv('SESSIONID');

4、查询会话当前的SQL语句

SQL> select sid, status,sql_id from v$session where sid=148;

       SID STATUS   SQL_ID
---------- -------- -------------
       148 ACTIVE   62xr1t3dqg3r9

SQL> select sql_text from v$sql where sql_id='62xr1t3dqg3r9';

SQL_TEXT
--------------------------------------------------------------------------------
select sid, status,sql_id from v$session where sid=148

5、查询会话对应的进程号

select spid from v$process where addr=(select paddr from v$session where sid=148);

6、查看会话的阻塞情况

select sid, lmode, type, request, block from  v$lock where type in ('TX','TM');

7、查看SQL的执行计划

SQL> set autotrace trace exp;
SQL> select * from sp_info;

或

SQL> explain plan for select * from sp_info;

SQL> select * from table(dbms_xplan.display);

8、查看用户下的所有表

select table_name from  user_tables;

9、查看日志告警文件目录

show parameter background_dump_dest;