Oracle常用SQL语句（一）

查看数据库的版本　

Select version FROM Product_component_version

Where SUBSTR(PRODUCT,1,6)='Oracle';

 

如何远程判断Oracle数据库的安装平台

select * from v$version;

 

查看数据库的创建日期和归档方式

Select Created, Log_Mode, Log_Mode From V$Database;

 

查看数据库库对象

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

 

查看日志文件

select member from v$logfile;

 

查看表空间物理文件的名称及大小

select tablespace_name,

       file_id,

       file_name,

       round(bytes / (1024 * 1024), 0) total_space

  from dba_data_files

 order by tablespace_name;

 

查看表空间的使用情况

SELECT A.TABLESPACE_NAME,

       A.BYTES TOTAL,

       B.BYTES USED,

       C.BYTES FREE,

       (B.BYTES * 100) / A.BYTES "% USED",

       (C.BYTES * 100) / A.BYTES "% FREE"

  FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C

 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME

   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

 

耗资源的进程（top session）

select s.schemaname schema_name,

       decode(sign(48 - command),

              1,

              to_char(command),

              'Action Code #' || to_char(command)) action,

       status session_status,

       s.osuser os_user_name,

       s.sid,

       p.spid,

       s.serial# serial_num,

       nvl(s.username, '[Oracle process]') user_name,

       s.terminal terminal,

       s.program program,

       st.value criteria_value

  from v$sesstat st, v$session s, v$process p

 where st.sid = s.sid

   and st.statistic# = to_number('38')

   and ('ALL' = 'ALL' or s.status = 'ALL')

   and p.addr = s.paddr

 order by st.value desc, p.spid asc, s.username asc, s.osuser asc

 

根据sid查是哪台电脑的链接

select osuser,machine,username,sid,serial# from v$session where sid='128';

 

根据sid查对应的sql

select SID,SQL_TEXT from v$open_cursor where SID='128';