/*查看表空间的名称及大小*/ SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name; /*查看表空间物理文件的名称及大小*/ SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; /*查看表空间的使用情况*/ SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name FROM dba_free_space GROUP 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; /*查看数据库库对象*/ SELECT owner, object_type, status, COUNT(*) count# FROM all_objects GROUP BY owner, object_type, status; /*查找object为哪些进程所用*/ SELECT p.spid, s.sid, s.type, s.serial# serial_num, s.username user_name, a.type object_type, s.osuser os_user_name, a.owner, a.object object_name, decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command)) action, p.program oracle_process, s.terminal terminal, s.program program, s.status session_status FROM v$session s, v$access a, v$process p WHERE s.paddr = p.addr AND s.type = 'USER' AND a.sid = s.sid AND a.object = 'SUBSCRIBER_ATTR' ORDER BY s.username, s.osuser; /*查看回滚段名称及大小*/ SELECT segment_name, tablespace_name, r.status, (initial_extent / 1024) initialextent, (next_extent / 1024) nextextent, max_extents, v.curext curextent FROM dba_rollback_segs r, v$rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name; /*查看控制文件*/ SELECT NAME FROM v$controlfile; /*查看日志文件*/ SELECT MEMBER FROM v$logfile; /*查看数据库的版本*/ SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle'; /*查看数据库的创建日期和归档方式*/ SELECT created, log_mode, log_mode FROM v$database; /*捕捉运行很久的SQL*/ column username format a12 column opname format a16 column progress format a8 SELECT username, sid, opname, round(sofar * 100 / totalwork, 0) || '%' AS progress, time_remaining, sql_text FROM v$session_longops, v$sql WHERE time_remaining <> 0 AND sql_address = address AND sql_hash_value = hash_value; /*查看数据表的参数信息*/ SELECT partition_name, high_value, high_value_length, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extent, max_extent, pct_increase, freelists, freelist_groups, logging, buffer_pool, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size, last_analyzed FROM dba_tab_partitions --WHERE table_name = :tname AND table_owner = :towner ORDER BY partition_position; /*查看还没提交的事务*/ SELECT * FROM v$locked_object; SELECT * FROM v$transaction; /*回滚段查看*/ SELECT rownum, sys.dba_rollback_segs.segment_namename, v$rollstat.extents extents, v$rollstat.rssize size_in_bytes, v$rollstat.xacts xacts, v$rollstat.gets gets, v$rollstat.waits waits, v$rollstat.writes writes, sys.dba_rollback_segs.status status FROM v$rollstat, sys.dba_rollback_segs, v$rollname WHERE v$rollname.name(+) = sys.dba_rollback_segs.segment_name AND v$rollstat.usn(+) = v$rollname.usn ORDER BY rownum; /*耗资源的进程(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; /*查看锁(lock)情况*/ SELECT /*+ RULE */ ls.osuser os_user_name, ls.username user_name, decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type, o.object_name OBJECT, decode(ls.lmode, 1, NULL, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', NULL) lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2 FROM sys.dba_objects o, (SELECT s.osuser, s.username, l.type, l.lmode, s.sid, s.serial#, l.id1, l.id2 FROM v$session s, v$lock l WHERE s.sid = l.sid) ls WHERE o.object_id = ls.id1 AND o.owner <> 'SYS' ORDER BY o.owner, o.object_name; /*查看等待(wait)情况*/ SELECT v$waitstat.class, v$waitstat.count COUNT, SUM(v$sysstat.value) sum_value FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets', 'consistent gets') GROUP BY v$waitstat.class, v$waitstat.count; /*查看SGA情况*/ SELECT NAME, bytes FROM sys.v_$sgastat ORDER BY NAME ASC /*查看数据表的全部索引列*/ SELECT c.index_name, i.uniqueness, c.column_name FROM user_indexes i, user_ind_columns c WHERE i.index_name = c.index_name AND i.table_name = upper('hr_locations_all') ORDER BY c.index_name, c.column_position /*查看低效运行的SQL语句*/ SELECT executions, disk_reads, buffer_gets, round((buffer_gets - disk_reads) / buffer_gets, 2) hit_radio, round(disk_reads / executions, 2) reads_per_run, sql_text FROM v$sqlarea WHERE executions > 0 AND buffer_gets > 0 AND (buffer_gets - disk_reads) / buffer_gets < 0.8 ORDER BY 4 DESC; /*查询有哪些用户在使用数据库*/ SELECT username FROM v$session; /*查看数据库的SID:*/ SELECT NAME FROM v$database; --也可以直接查看 init.ora文件 /*如何在Oracle服务器上通过SQLPLUS查看本机IP地址 :*/ SELECT sys_context('userenv', 'ip_address') FROM dual;