oracle常用语句
1、清空oracle共享池
ALTER SYSTEM FLUSH SHARED_POOL
2、查看当前oracle共享池的占用情况
select * from v$sgastat
3、修改日期输入为yyyy-mm-dd hh24:mi:ss格式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
4、查看表数据量的大小
SELECT SEGMENT_NAME,SUM(BYTES) FROM USER_SEGMENTS
WHERE SEGMENT_NAME IN (SELECT TNAME FROM TAB WHERE TABTYPE='TABLE')
GROUP BY SEGMENT_NAME;
5、清除IBM P630小型机的告警信息
/usr/lpp/diagnostics/bin/usysfault -s normal
6、大表删除记录，每次删除1000条
declare
-- Local variables here
i integer;
begin
while(true) loop
-- Test statements here
DELETE FROM tbilllog12 WHERE callend >= TO_DATE('20031205 00:00:00','YYYYMMDD HH24:MI:SS') and rownum<1001;
if(sql%notfound) then exit;
end if;
commit;
end loop;
end;
7、通过oracle进程查询unix下的进程
select * from v$session找到进程对应的paddr
select * from v$process where addr='paddr'
select distinct object_type from dba_objects
select distinct status from dba_objects
8、以sysdba方式登录sqlplus
sqlplus '/ as sysdba'
sqlplus /
9、数据库的启动
sql>start umount
sql>alter database mount
sql>start mount
sql>alter database open
10、查看进程对应的sql语句
SELECT SQL_Text FROM V$SQLTEXT A, V$SESSION B ,V$PROCESS C
WHERE B.PAddr = C.Addr AND A.Hash_Value = B.SQL_Hash_Value AND C.SPID =''
11、查看表的字段数
select table_name,count(*) from user_tab_columns group by table_name
12、修改oracle9i系统参数
alter system set db_cache_size=900M scope=spfile;
alter system set db_cache_size=900M scope=both;
13、已vi方式显示历史命令
ksh -o vi
14、如何配置listener.ora及tnsnames.ora作到应用分割与应用自动切换而不会提示错误
a.关掉RAC的remote_listener,即屏蔽掉RAC的远程注册功能
alter system set remote_listener=’’ scope=spfile sid=’ora921’
alter system set remote_listener=’’ scope=spfile sid=’ora922’
b.对于2节点的RAC来讲,tnsnames做如下配置
ORAPT =
(DESCRIPTION =
(load_balance= off)------必须关闭掉listener的负载均衡
(failover = on)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 198.115.189.3)(PORT = 1521))---listener1的ip地址
(ADDRESS = (PROTOCOL = TCP)(HOST = 198.115.189.4)(PORT = 1521))-----listener2的ip地址
)
(CONNECT_DATA =
(SERVICE_NAME = ora92)
(failover_mode =
(type = session)
(method = basic)
)
)
)
ORAYW =
(DESCRIPTION =
(load_balance= off)------必须关掉listener的负载均衡
(failover = on)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 198.115.189.4)(PORT = 1521))-----业务所在节点的IP地址
(ADDRESS = (PROTOCOL = TCP)(HOST = 198.115.189.3)(PORT = 1521))-
----平台所在节点的IP地址
)
(CONNECT_DATA =
(SERVICE_NAME = ora92)
(failover_mode =
(type = session)
(method = basic)
)
)
)
15.如何做debug观察RAC使用了指定的IP地址
$sqlplus " / as sysdba"
$sqlplus>oradebug setmypid
$sqlplus>oradebug ipc
在$ORACLE_HOME/admin/ora92/udump查看最新的那个trace文件,看UDP的设置
16.修改job的instance参数
exec dbms_job.instance(222,1);
commit;
17.HP小型机节点切换
cmhaltnode -f -v icdnode1
cmrunnode -v icdnode1
cmhaltnode -f -v icdnode2
cmrunnode -v icdnode2
18.如果包异常退出，需要修改包的属性
cmmodpkg -e pkg1
19.PVG下的创建逻辑卷命令
lvcreate -A n -D y -s g -m 1 -L 2048M -n lv_data051 /dev/vg01
20.-------------------
SELECT S.SID,P.SPID
FROM V$SESSION S, V$PROCESS P, V$LOCKED_OBJECT L
WHERE S.PADDR = P.ADDR
AND S.SID = L.SESSION_ID
AND https://www.360docs.net/doc/ff4445900.html,ERNAME='ICDMAIN'
21.查看数据库进程内容的SQL语句
SELECT /*+ ORDERED */ sql_text FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value),DECODE (sql_hash_value,0,prev_sql_addr, sql_address)
FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&spid')) ORDER BY piece ASC
使用oracle登陆
进入这个目录：
%cd /home/oracle/app/oracle/product/9.2/rdbms/admin
然后使用sysdba权限用户进入oracle数据库
%sqlplus "/as sysdba"
SQL> alter system set timed_statistics = true;
SQL>@spcreate
根据提示依次输入用户密码，用户所在空间，用户所用临时空间
perfstat,ring,temp
期间如果出错执行@spdrop.sql，恢复原状。如果是对的则就不需要作这步了。
SQL>@spdrop.sql
在产生问题的时间段执行：
SQL>execute statspack.snap
PL/SQL procedure successfully completed.
中间间隔半小时（具体时间可以自行掌握）再执行一次：
SQL>execute statspack.snap
PL/SQL procedure successfully completed.
记住ID即可，生成报告：
SQL>@spreport.sql