#修改系统参数
alter system set open_cursors=30000

#查看用户连接
select * from v$session

#数据库名
select name from v$database

#CPU数量
select value from v$parameter where name='cpu_count'

#共享池大小
select value from v$parameter where name='shared_pool_size'

#显示数据库的参数
show parameter

#给用户解锁
C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 2月 3 10:26:03 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba
SQL> alter user psmis account unlock
#修改用户密码
SQL> alter user USERNAME identified by PASSWORD;

#启动数据库
6.打开主库数据库，修改为归档方式(oracle9i用户操作)
手工创建归档目录C:/Oracle/Arch
sqlplus /nolog
SQL> conn / as sysdba;
Connected.

SQL> shutdown immediate;
察看归档模式
SQL> startup
SQL> archive log list;

#启动监听器 Oracle用户登录操作系统
$lsnrctl start

--查看锁表的连接
select t2.*
  from v$locked_object t1, v$session t2
 where t1.session_id = t2.sid
 order by t2.logon_time;

--查看被锁的表对象
select t3.name, t2.*
  from v$locked_object t1, v$session t2, sys.obj$ t3
 where t1.session_id = t2.sid
   and t1.OBJECT_ID = t3.obj#
 order by t2.logon_time;

--查看被锁的表对象
select t2.SID, t2.SERIAL#, t2.MACHINE, t3.*
  from v$locked_object t1, v$session t2, sys.obj$ t3
 where t1.session_id = t2.sid
   and t1.OBJECT_ID = t3.obj#
 order by t2.logon_time;

--锁对象
select t1.*
  from v$locked_object t1, v$session t2
 where t1.session_id = t2.sid
 order by t2.logon_time;

--查表锁
 SELECT A.OBJECT_ID,SUBSTR(B.OBJECT_NAME,1,15) TABLE_NAME, A.SESSION_ID SID ,C.SERIAL#,
 SUBSTR(A.ORACLE_USERNAME,1,10) ORA_USER,
 A.PROCESS ,A.LOCKED_MODE ,C.STATUS ,C.MACHINE,
C.TERMINAL,A.OS_USER_NAME OS_USER,C.PROGRAM
FROM V$LOCKED_OBJECT A ,DBA_OBJECTS B ,V$SESSION C
WHERE A.OBJECT_ID=B.OBJECT_ID AND A.SESSION_ID=C.SID
and SUBSTR(B.OBJECT_NAME,1,15)='SYS_CHANGELOG'

--KILL对方连接
alter system kill session '140,4';

--查询历史SQL
select SQL_TEXT,sql_fulltext,rows_processed/executions avgs,fetches,executions,rows_processed from v$sql where sql_id in ('28ghhhyvprmzj','g2tuq5q84n1zh','2wdv536wauxza','1c9p8h0jm1mg3','gxwp1yh37hxqz')

select SQL_TEXT,sql_fulltext,cpu_time,rows_processed/executions avgs,fetches,executions,rows_processed, last_active_time
 from v$sql where rows_processed > 100000 and (SQL_TEXT like 'SELECT%' or SQL_text like 'select%') and sql_text not like '%DUAL'
and SQL_TEXT not like 'SELECT COUNT(*)%' and rows_processed/executions > 100 and executions > 5

--Oracle 查执行成本和执行次数
SELECT SQL_TEXT, SQL_FULLTEXT, SQL_ID, FETCHES, EXECUTIONS, OPTIMIZER_MODE, OPTIMIZER_COST,
       HASH_VALUE, MODULE, CPU_TIME, ELAPSED_TIME
  FROM V$SQL
 WHERE MODULE='JDBC Thin Client'
   AND executions > 1
 ORDER BY OPTIMIZER_COST * EXECUTIONS DESC
 
 
--查执行时间长的SQL
select v$sql.sql_id,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 sid, username, event, blocking_session,
   seconds_in_wait, wait_time
from v$session where state in ('WAITING')
and wait_class != 'Idle';

select sid, sql_text
from v$session s, v$sql q
where sid in (4067,4279)
and (
   q.sql_id = s.sql_id or
   q.sql_id = s.prev_sql_id);
  
  
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 sum(value)/1024/1024 Mb from v$sesstat s, v$statname n
where n.STATISTIC# = s.STATISTIC#
and name = 'session pga memory';   
 

--查询包含某字段的表
select TABLE_NAME,COLUMN_NAME,
CHAR_COL_DECL_LENGTH from user_tab_columns
where  COLUMN_NAME='ELECTRICITY_WORDS_NUMBER';

select * from user_tables where table_name like 'EC%'

select count(*)  from v$session where status='ACTIVE';