oracle常用sql语句以及案例,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';