oracle常用命令（搜集整理）

set serveroutput on
使用set serveroutput on 命令设置环境变量serveroutput为打开状态，使得pl/sql程序能够在SQL*plus中输出结果

查询命令：
select count(*) from v$session;
select count(*) from sys.v_$process;
select count(*) from sys.v_$transaction;

1、查看表空间的名称及大小

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;

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

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

3、查看回滚段名称及大小

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 ;

4、查看控制文件

select name from v$controlfile;

5、查看日志文件

select member from v$logfile;

6、查看表空间的使用情况

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;

7、查看数据库库对象

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

8、查看数据库的版本　

Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle';

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

Select Created, Log_Mode, Log_Mode From V$Database;

1、查看当前所有对象

SQL> select * from tab;

2、建一个和a表结构一样的空表

SQL> create table b as select * from a where 1=2;

SQL> create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;

3、察看数据库的大小，和空间使用情况

SQL> col tablespace format a20
SQL> select b.file_id　　文件ID,
　　b.tablespace_name　　表空间,
　　b.file_name　　　　　物理文件名,
　　b.bytes　　　　　　　总字节数,
　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
　　sum(nvl(a.bytes,0))　　　　　　　　剩余,
　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比
　　from dba_free_space a,dba_data_files b
　　where a.file_id=b.file_id
　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes
　　order by b.tablespace_name
　　/
　　dba_free_space --表空间剩余空间状况
　　dba_data_files --数据文件空间占用情况


4、查看现有回滚段及其状态

SQL> col segment format a30
SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

5、查看数据文件放置的路径

SQL> col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

6、显示当前连接用户

SQL> show user

7、把SQL*Plus当计算器

SQL> select 100*20 from dual;

8、连接字符串

SQL> select 列1::列2 from 表1;
SQL> select concat(列1,列2) from 表1;

9、查询当前日期

SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;

10、用户间复制数据

SQL> copy from user1 to user2 create table2 using select * from table1;

11、视图中不能使用order by，但可用group by代替来达到排序目的

SQL> create view a as select b1,b2 from b group by b1,b2;

12、通过授权的方式来创建用户

SQL> grant connect,resource to test identified by test;

SQL> conn test/test

How to find the tablespace of a table?

SELECT tablespace_name
FROM all_tables
WHERE table_name = 'YOURTABLENAME';

How to remove duplicate rows from a table

If the unique/primary keys can be identified from the table, it is easier to remove the records from the table using the following query:

DELETE FROM tablename
WHERE rowid not in (SELECT MIN(rowid)
FROM tablename
GROUP BY column1, column2, column3...);

Here column1, column2, column3 constitute the identifying key for each record.
If the keys cannot be identified for the table, you may create a temporary table using the query

CREATE TABLE temptablename
AS SELECT DISTINCT *
FROM tablename;

Then drop the original table and rename the temp table to original tablename.
How to identify and remove bad 'child' records to enable / create a foreign key ("Parent Keys Not Found" error when you try to enable/create relation from child table to parent table!)

The records can be identified and removed using the query

DELETE FROM childtablename ct
WHERE NOT EXISTS (SELECT 'x' FROM parenttablename pt
WHERE ct.keycolumn1 = pt.keycolumn1 AND ct.keycolumn2 = pt.keycolumn2...)

Or if you need to provide the user with bad records you may change the DELETE to SELECT with column list.

Find total number of records in a table

The simple query to find the total number of records is

SELECT COUNT(*) FROM tablename;

If you want to see the record count of more than one table, you may

SELECT TABLE_NAME, NUM_ROWS
FROM ALL_TABLES
WHERE TABLE_NAME LIKE 'searchstring';
if the tables are ANALYZED.

Or you may create a script quickly by

SET PAGES 0 FEEDBACK OFF ECHO OFF VERIFY OFF TERMOUT OFF
SPOOL COUNT.SQL
SELECT 'SELECT COUNT(*) FROM ' :: TABLE_NAME :: ';'
FROM ALL_TABLES
WHERE TABLE_NAME LIKE 'searchstring';
SPOOL OFF
SET TERMOUT ON
@COUNT.SQL
SET FEEDBACK ON VERIFY ON PAGES 24

What is my current session id?

The username, program, machine, terminal, session id, serial # and more can be found from the v$session view. This view has a column audsid. When you join this coulum to your userenv('sessionid') value, you get the session information for your current session. The query could be

SELECT USERNAME, SID, SERIAL#, PROGRAM FROM V$SESSION
WHERE AUDSID = USERENV('SESSOINID');

How to terminate a session?

Using the above method you find the SID and SERIAL# for the session you wish to terminate. Then issue the command

ALTER SYSTEM KILL SESSION 'sid, serial#';

Please note that the sid and serial# should be in quotes separated by a comma.

Which database am I connected to? As which user?

The database name can be found out from different views. The view which everyone has access is GLOBAL_NAME. The query is

SELECT GLOBAL_NAME FROM GLOBAL_NAME;

To find the user, from sqlplus you can do "SHOW USER".

What is the SGA size?

There are two simple ways to find this. The first is to invoke server manager (svrmgrl) and connect ineternal (or as any user). Issue the command "SHOW SGA". Or you can run the query "SELECT * FROM V$SGA;" from svrmgrl or sqlplus.

Where are my alert log and dump files written to?

The alert file is written to your BACKGROUND_DUMP_DEST. This variable is set in the config.ora (init.ora) file. You can find the current values of the dump directories from the database. Invoke svrmgrl and connect. Issue command "SHOW PARAMETER DUMP". The SHOW PARAMETER command can be used to find the value of any database parameter value. For example if you want to find the block size and block buffers, issue command "SHOW PARAMETER BLOCK".

How to create structure (no data) of a table from another table?

If you need to duplicate a table you can do a "create table newtablename as select * from tablename;" This will create the new table will all the data. If you need to create only the structure, add a where condition "1=2" or some condition which is always false.

CREATE TABLE NEWTABLENAME AS
SELECT * FROM OLDTABLENAME WHERE 1=2;
How to increase the size of a tablespace?

The size of the tablespace is increased by changing the size of the size of the underlying physical files. You can either add more space to the existing file by

ALTER DATABASE DATAFILE 'filename' RESIZE nn M;

OR you can add more physical datafiles to the tablespace by

ALTER TABLESPACE tablespacename
ADD DATAFILE 'filename' SIZE nn M;

Make sure you specify the full path name for the filename. Use the script tsinfo.sql to find the size and related physical files of a tablespace.

Is my database running in Archivelog mode? Where are the archived files written?

This can be found by invoking server manager (svrmgrl) and issuing the command "ARCHIVE LOG LIST".[http://www.bijoos.com/page_footer.htm] 　

一、ORACLE的表的分类：
1、REGULAR TABLE：普通表，ORACLE推荐的表，使用很方便，人为控制少。
2、PARTITIONED TABLE：分区表，人为控制记录的分布，将表的存储空间分为若干独立的分区，记录按一定的规则存储在分区里。适用于大型的表。

二、建表
1 CREATE TABLE 表名 （EMPNO NUMBER（2），NAME VARCHAR2（20）） PCTFREE 20 PCTUSED 50
STORAGE （INITIAL 200K NEXT 200K MAXEXTENTS 200 PCTINCREASE 0） TABLESPACE 表空间名称
[LOGGING:NOLOGGING]所有的对表的操作都要记入REDOLOG，ORACLE建议使用NOLOGGING；
[CACHE:NOCACHE]：是否将数据按照一定的算法写入内存。
2、关于PCTFREE 和PCTUSED
A、行迁移和行链接
B、PCTFREE：制止INSERT，为 UPDATE留FREE 空间
C、PCTUSED：为恢复INSERT操作，而设定的。

三、拷贝一个已经存在的表：
CREATE TABLE 新表名 STORAGE（。。） TABLESPACE 表空间
AS SELECT * FROM 老表名 ；
当老表存在约束，触发的时候，不会拷过去。

四、修改表的参数
ALTER TABLE 名称 PCTFREE 20 PCTUSED 50 STOAGE（MAXEXTENTS 1000）；

五、手工分配空间：

ALTER TABLE 名称 ALLOCATE EXTENT（SIZE 500K DATAFILE '。。'）；

1、SIZE选项，按照NEXT分配
2、表所在表空间与所分配的数据文件所在的表空间必须一样。

六、水线
1、水线定义了表的数据在一个BLOCK中所达到的最高的位置。
2、当有新的记录插入，水线增高
3、当删除记录时，水线不回落
4、减少查询量

七、如何回收空间：
ALTER TABLE 名称 DEALLOCATE UNUSED [KEEP 4[M:K]]
1、当空间分配过大时，可以使用本命令
2、如果没有加KEEP，回收到水线
3、如果水线《MINEXTENTS的大小回收到MINEXTENTS所指定的大小

八、TRUNCATE 一个表
TRUNCATE TABLE 表名，表空间截取MINEXTENT，同时水线重置。

九、DROP 一个表
DROP TABLE 表名 [CASCADE CONSTRAINTS]
当一个表含有外键的时候，是不可以直接DROP的，加CASCADE CONSRIANTS将外键等约束一并删掉。

十、信息获取
1、dba_object
2 dba_tables:建表的参数
3 DBA_SEGMENTS:
组合查询的连接字段：DBA_TABLES的table_name+dba_ojbect的object_name+dba_segments的SEGMENT_NAME

第十一章：索引的管理
一、索引的分类：
1、逻辑上：
单列索引 复合索引 唯一索引 非唯一索引
2、物理上：
B-TREE OR BITMAP
B-TREE （NORMAL和反向索引）

二、CREATE INDEX
CREATE INDEX 名称 ON 表名（列名） PCTFREE 30 STORAGE（。。。。。） TABLESPACE 名称
没有定义PCTUSED：索引是按照一定存储的，如果通过PCTUSED允许恢复对BLOCK的INSERT操作，可能影响INDEX的效率。

三、建立索引的注意事项
1、索引对查询性能有提高，但对DML语句有影响。
2、索引也应该放在一个专用的表空间
3、定义索引的EXTENT的大小时，=5*DB BLOCK
4、建立索引时，应采用 NOLOGGING
方式。
四、修改索引
ALTER INDEX 名称 STORAGE（新值）

五、分配空间给索引
1、ALTER INDEX 名称 ALLOCATE EXTENT（SIZE 200K DATAFILE '。。'）

六、重建索引
1、提高查询性能
2、当一个索引重建时，老的索引会在新索引建立完成后，被删除。
3、新索引建立过程中，老的索引仍可用于查询。
4、硬盘的开销大，

七、DROP一个索引
DROP INDEX 名称

八、信息获取
1、DBA_INDEXES:建索引的参数
2、DBA_IND_COLUMNS:

第十二章：用户的管理
一、ORACLE的安全域
1、TABLESPACE QUOTAS：表空间的使用定额
2、DEFAULT TABLESPACE：默认表空间
3、TEMPORARY TABLESPACE：指定临时表空间。
4、ACCOUNT LOCKING：用户锁
5、RESOURCE LIMITE：资源限制
6、DIRECT PRIVILEGES：直接授权
7、ROLE PRIVILEGES：角色授权先将应用中的用户划为不同的角色，
二、创建用户时的清单：
1、选择一个用户名称和检验机制：A，看到用户名，实际操作者是谁，业务中角色。
2、选择合适的表空间：
3、决定定额：
4、口令的选择：
5、临时表空间的选择：先建立一个临时表空间，然后在分配。不分配，使用SYSTEM表空间
6、CREATE USER
7、授权：A，用户的工作职能
B，用户的级别
三、用户的创建：
1、命令：
CREATE USER 名称 IDENTIFIED BY 口令 DEFAULT TABLESPACE 默认表空间名 TEMPOARAY
TABLESPACE 临时表空间名
QUOTA 15M ON 表空间名
[PASSWORD EXPIRE]：当用户第一次登陆到ORACLE，创建时所指定的口令过期失效，强迫用户自己定义一个新口令。
[ACCOUNT LOCK]：加用户锁
QUOTA UNLIMITED ON TABLESPACE：不限制，有多少有多少。
[PROFILE 名称]：受PROFILE文件的限制。

四、如何控制用户口令和用户锁
1、强迫用户修改口令：ALTER USER 名称 IDENTIFIED BY 新口令 PASSWORD EXPIRE；
2、给用户加锁：ALTER USER 名称 ACCOUNT [LOCK:UNLOCK]
3、注意事项：
A、所有操作对当前连接无效
B、1的操作适用于当用户忘记口令时。

五、更改定额
1、命令：ALTER USER 名称 QUOTA 0 ON 表空间名
ALTER USER 名字 QUOTA （数值）K:M:UNLIMITED ON 表空间名；
2、使用方法：
A、控制用户数据增长
B、当用户拥有一定的数据，而管理员不想让他在增加新的数据的时候。
C、当将用户定额设为零的时候，用户不能创建新的数据，但原有数据仍可访问。

六、DROP一个USER
1、DROP USER 名称
适合于删除一个新的用户
2、DROP USER 名称 CASCADE： 删除一个用户，将用户的表，索引等都删除。
3、对连接中的用户不好用。


七、信息获取：
1、DBA_USERS:用户名，状态，加锁日期，默认表空间，临时表空间
2、DBA_TS_QUOTAS:用户名，表空间名，定额。
两个表的连接字段：USERNAME
GRANT CREATE SESSION TO 用户名


第十三章：PROFILE的管理（资源文件）
一、PROFILE的管理内容：
1、CPU的时间
2、I/O的使用
3、IDLE TIME（空闲时间）
4、CONNECT TIME（连接时间）
5、并发会话数量
6、口令机制：

二、DEFAULT PROFILE：
1、所有的用户创建时都会被指定这个PROFILE
2、DEFAULT PROFILE的内容为空，无限制

三、PROFILE的划分：
1、CALL级LIMITE：
对象是语句：
当该语句资源使用溢出时：
A、该语句终止
B、事物回退
C、SESSION连接保持
2、SESSION级LIMITE：
对象是：整个会话过程
溢出时：连接终止

四、如何管理一个PROFILE
1、CREATE PROFILE
2、分配给一个用户
3、象开关一样打开限制。

五、如何创建一个PROFILE：
1、命令：CREATE PROFILE 名称
LIMIT
SESSION_PER_USER 2
CPU_PER_SESSION 1000
IDLE_TIME 60
CONNECT_TIME 480
六、限制参数：
1、SESSION级LIMITE：
CPU_PER_SESSION:定义了每个SESSION占用的CPU的时间： （1/100 秒）
2、SESSION_PER_USER:每个用户的并发连接数
3、CONNECT_TIME:一个连接的最长连接时间（分钟）
4、LOGICAL_READS_PER_SESSION: 一次读写的逻辑块的数量
5、CALL级LIMITE
CPU_PER_CALL:每个语句占用的CPU时间

LOGICAL_READS_PER_CALL:

七、分配给一个用户：
CREATE USER 名称。。。。。。
PROFILE 名称
ALTER USER 名称 PROFILE 名称

八、打开资源限制：
1、RESOURCE_LIMT:资源文件中含有
2、ALTER SYSTEM SET RESOURCE_LIMIT=TRUE；
3、默认不打开

九、修改PROFIE的内容：
1、ALTER PROFILE 名称参数 新值
2、对于当前连接修改不生效。


十、DROP一个PROFILE
1、DROP PROFILE 名称
删除一个新的尚未分配给用户的PROFILE，
2、DROP PROFILE 名称 CASCADE
3、注意事项
A、一旦PROFILE被删除，用户被自动加载DEFAULT PROFILE
B、对于当前连接无影响
C、DEFAULT PROFILE不可以被删除
十一、信息获取：
1、DBA_USERS:
用户名，PROFILE
2、DBA_PROFILES:
PROFILE及各种限制参数的值
每个用户的限制：PROFILE（关键字段）
十二、PROFILE的口令机制限制
1、限制内容
A、限制连续多少次登录失败，用户被加锁
B、限制口令的生命周期
C、限制口令的使用间隔
2、限制生效的前提：
A、RESOURCE_LIMIT:=TRUE
B ORACLE\RDBMS\ADMIN\UTLPWDMG.SQL
3、如何创建口令机制：
CREATE PROFILE 名称
SESSIONS_PER_USER

.....
password_life_time 30
failed_log_attempts 3
password_reuse_time 3
4、参数的含义：
A FAILED_LOGIN_ATTEMPTS:
当连续登陆失败次数达到该参数指定值时，用户加锁
B PASSWORD_LOCK_TIME:加锁天数
C PASSWORD_LIFE_TIME:口令的有效期（天）
D PASSWORD_GRACE_TIME:口令修改的间隔期（天）
E PASSWORD_REUSE_TIME:口令被修改后原有口令隔多少天被重新使用。
F PASSWORD_REUSE_MAX:口令被修改后原有口令被修改多少次被重新使用。


第十四章:ORACLE的权限管理
一 授权的两种分类:
1 SYSTEM 授权:允许特定的用户对特定的一类对象做特定的操作.可操作的对象:TABLES,INDEXES,PROCEDURES,SEGMENTS;系统授权是对某一类对象.
GRANT SELECT ANY TABLE TO ZT

2 OBJECT(对象授权):
A 允许一个特定的用户对一个特定的对象做特定的操作.对象:TABLE,INDEX,SEGMENT,..
GRANT UPDATE ON EMP TO ZT

二 系统授权的特点
1 ORACLE中有超过80种以上的 SYSTEM授权
2 几乎所有的SYSTEM授权包含 ANY关键字
3 SYSTEM授权:GRANT授权
4 SYSTEM授权:REVOKE回收
5 允许用户做系统一级的操作,建表空间,建SESSION.
6 最常用的系统授权:
INDEX:CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX

TABLE:CREATE ANY TABLE
ALTER ............
DROP .,.......
SELECT .......
UPDATE.........
SESSION:CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
TABLESPACE: CREATE TABLESPACE
ALTER TABLESPACE
DROP TABLESPACE

三,如何授权:

1 命令格式
GRANT CREATE SESSION,SELECT ANY TABLE TO [USERNAME:PUBLIC:ROLE]

2带有转授权限:
A 转授:当A用户得到系统授权后, 如果可以将这个权限授给别人,称转授.

GRANT CREATE SESSION,SELECT ANY TABLE
TO [USERNAME:PUBLIC:ROLE] WITH ADMIN OPTION

举例:
1 SYSTEM->ZT
GRANT CREATE SESSION TO ZT WITH ADMIN OPTION
ZT用户可以拥有CREATE SESSION权限
2 ZT->OLM
GRANT CREATE SESSION TO OLM,
ZT,OLM也有了.

四 SYSDBA和SYSOPER系统权限
1 SYSOPER:SYSTEM OPERATOR:系统操作员
STARTUP,SHUTDOWN;
ALTER DATABASE[MOUNT:OPEN];
RECOVER TABLESPACE

BACKUP DATABASE
ARCHIVELOG OR NOARCHIVELOG;
2 SYSDBA
SYSTEM DABASE ADMIN:数据库管理员,
SYSOPER WITH ADMIN OPTION
RECOVER DATABASE
CREATE DATABASE

五 信息获取
DBA_SYS_PRIVS
GRANTEE:得到权限的人
PRIVILEGE:得到何种权限
ADMIN OPTION:是否可以转授
想知道SCOTT有多少权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='SCOTT'

六 回收权限:
REVOKE 权限 FROM [USERNAME:PUBLIC:ROLE]
举例:
SYSTEM->ZT(CREATE SESSION WITH ADMIN OPTION)
ZT->OLM(CREATE SESSION)

SYS ZT OLM

PRIV Y Y Y
REVOKE Y N Y


七 对象授权

TABLE: ALTER DELETE SELECT UPDATE INSERT
INDEXELETE INSERT SELECT UPDATE

少CREATE,对象是已经存在的事物,授权者是所有者.

一,授权:
GRANT UPDATE(ENAME) ON EMP TO ZT WITH GRANT OPTION
二信息获取
1 DBA_TAB_PRIVS:
GRANTEE:得到者
GRANTOR:授权者
PRIVILEGE:权限
GRANTABLE:是否可以转授
OWNER:所有者
三 回收对象授权
1 REVOKE 权限 FROM 用户



SYSTEM->ZT(UPDATE ON EMP WITH GRANT OPTION)
ZT->OLM(UPDATE ON EMP)

SYS ZT OLM

PRIV Y Y Y
REVOKE Y N N
第十五章:角色管理
一,角色及其特点:
1,角色实际上是若干权限的集合体

2,DBA通过为应用中的不同用户,不同职责,定义不同的角色,可以达到减少工作量的目的.
3,角色的使用同授权一样,可以用 GRANT授权,REVOKE回收.
4,角色可以象开关一样打开关闭
5,角色的使用可以提高性能.
6,角色的使用可以大大减少工作量


二 建立角色
1 CREATE ROLE 名
2 CREATE ROLE 名
IDENTIFIED BY 口令

三 修改角色

1 ALTER ROLE 名称 IDENTIFIED BY 口令
将没有口令的加一个口令

2ALTER ROLE 名称 NOT IDENTIFIED
将有口令的变为没有口令

三 分配一个角色
GRANT ROLE 名 TO 用户名

四 回收一个角色
REVOKE ROLE 名 FROM 用户名


五 如何创建一个默认角色
只有设为默认角色,才可以在用户登录时,使角色所含有的权限生效
1 ALTER USER SCOTT
DEFAULT ROLE 角色1,角色2

2 ATLER USR SCOTT
DEFAULT ROLE ALL

3 ALTER USER SCOTT DEFAULT ROLE ALL EXCEPT 角色名

4 ALTER USER SCOTT DEFAULT ROLE NONE

六 打开和关闭角色:
1 SET ROLE 名称
2 SET ROLE IDENTIFIED BY 口令,(建立角色时,带口令)
3 SET ROLE NONE

七 删除角色
DROP ROLE 名称

八 信息获取
DBA_ROLE:
DBA_ROLE_PRIVS:

九 如何向角色里加权限:
GRANT 权限 TO 角色名
REVOKE 权限 FROM 角色名
角色:1不能跟用户名重复
2 ROLE的ALTER和其他对象的ALTER 不一样,ROLE的ALTER不能修改ROLE的内容,只可以修改角色的认证方式




1. 监控事例的等待

select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;

2. 回滚段的争用情况

select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;

3. 监控表空间的 I/O 比例

select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;

4. 监控文件系统的 I/O 比例

select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;

5.在某个用户下找所有的索引

select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;

6. 监控 SGA 的命中率

select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;

7. 监控 SGA 中字典缓冲区的命中率

select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;

8. 监控 SGA 中共享缓存区的命中率，应该小于1%

select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;

select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;

9. 显示所有数据库对象的类别和大小

select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;

10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%

SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size

SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');


12. 监控当前数据库谁在运行什么SQL语句

SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;

13. 监控字典缓冲区

SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;

后者除以前者,此比率小于1%,接近0%为好。

SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE

14. 找ORACLE字符集

select * from sys.props$ where name='NLS_CHARACTERSET';

15. 监控 MTS

select busy/(busy+idle) "shared servers busy" from v$dispatcher;

此值大于0.5时，参数需加大

select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;

servers_highwater接近mts_max_servers时，参数需加大

16. 碎片程度

select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;

alter tablespace name coalesce;
alter table name deallocate unused;

create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;

select * from ts_blocks_v;

select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;

查看碎片程度高的表

SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

17. 表、索引的存储情况检查

select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;

select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;

18、找使用CPU多的用户session

12是cpu used by this session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

 

 

参考：http://blog.chinaunix.net/uid-20082146-id-83488.html