Oracle 命令(一)

nomount模式打开数据库
startup nomount;

 

打开数据库
alert database open;

 

关闭数据库
alert database close;

 

查看数据文件
select name from v$datafile;

 

查看联机重做日志
select member from v$logfile;

 

查看控制文件
select name from v$controlfile;

 

查看spfile文件
show parameter spfile

 

pfile与spfile互换
create spfile from pfile
create pfiel from spfile

 

修改参数后会延迟生效的参数
select name from v$parameter where ISSYS_MODIFIABLE='DEFERRED';

 

更改排序区大小
alter system set sort_area_size = 65536 deferred;

 

恢复参数默认值
alter system reset parameter <scope=memory|spfile|both> sid='sid|*'

 

查看二进制参数文件
strings spfile$ORACLE_SID.ora

查看trace file 位置
select name,value from v$parameter where name like '%dump_dest%'

创建用户
create user <username> identified by <password>
grant create session to username;

查询当前会话trace文件
select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
 from v$process a, v$session b, v$parameter c, v$instance d
 where a.addr = b.paddr
 and b.audsid = userenv('sessionid')
 and c.name = 'user_dump_dest'
 
给当前会话trace文件加标识
alter session set tracefile_identifier = 'Look_For_Me';

 

查询系统表空间
select name from v$tablespace

 

启用/停止增加备份文件

alter database enable block change tracking using file '/u01/app/oradata/orcl/tracking_bak.bct';

alter database disable block change tracking;

 

查询ORACLE各个部件大小
select pool, name, bytes from v$sgastat  order by pool, name;
show parameter shared_pool_size
select sum(bytes) from v$sgastat where pool = 'shared pool';

查询各Oracle各个部件内存粒度
select component,granule_size from v$sga_dynamic_components;

 

查询LOG_BUFFER的大小
show parameter log_buffer

 

查询对象
select * from dba_objects where OBJECt_name='T'

 

查询对象的数据分布文件
select * from v$bh where OBJD=52973

 

查询对象数据内存分布
select * from x$bh where OBJ=52973

 

禁止表使用TM锁
ALTER TABLE T_TS_MAX_TRAN DISABLE TABLE LOCK

 

允许表使用TM锁
ALTER TABLE T_TS_MAX_TRAN ENABLE TABLE LOCK

SQLPLUS设置SESSION的CLIENT_INFO,MODULE NAME,ACTION NAME
BEGIN
dbms_session.set_identifier(client_id=>'GUHUI');
dbms_application_info.set_client_info(client_info=>'XSHELL FIRST SESSION');
dbms_application_info.set_module(module_name=>'mytestmodulename', action_name=>'mytestactioname');
end;

select sys_context('userenv','client_identifier') as client_identifier,
sys_context('userenv','client_info') as client_info,
sys_context('userenv','module') as module_name,
sys_context('userenv','action') as action_name
from dual;

关闭与开启TRACE(Enable and Disable SQL trace at any level)

查SESSION的ID,SERIAL#
select sid,serial# from v$session where sid = sys_context('userenv','sid');
select client_identifier,client_info,module as module_name,action as action_name from v$session where sid=sys_context('userenv','sid');

 

开启会话级TRACE
ALTER SESSION SET events '10046 trace name context forever, level 12'
exec dbms_monitor.session_trace_enable(session_id => 158,serial_num => 98,waits => true, binds => true);

 

关闭会话级TRACE
ALTER SESSION SET events '10046 trace name context off'
exec dbms_monitor.session_trace_disable(session_id => 159,serial_num => 89)

 

开启模块级TRACE
exec dbms_monitor.serv_mod_act_trace_enable(service_name => 'DBM10203.antognini.ch',
 module_name => 'mymodule',
 action_name => 'myaction',
 waits => TRUE,
 binds => FALSE,
 instance_name => NULL)

关闭模块级TRACE
exec dbms_monitor.serv_mod_act_trace_disable(service_name => 'DBM10203.antognini.ch',
 module_name => 'mymodule',
 action_name => 'myaction',
 instance_name => NULL)

 

开启数据库级TRACE
dbms_monitor.database_trace_enable(waits => TRUE,binds => TRUE,instance_name => NULL)

 

关闭数据库级TRACE
dbms_monitor.database_trace_disable(instance_name => NULL)

 

更改TRACE日志大小
ALTER SESSION SET max_dump_file_size = unlimited

 

开启日志中的时间记录
ALTER SESSION SET timed_statistics = TRUE

 

根据会话SID查询日志所在目录
SELECT s.sid,
       s.server,
       lower(CASE
               WHEN s.server IN ('DEDICATED', 'SHARED') THEN
                i.instance_name || '_' || nvl(pp.server_name, nvl(ss.name, 'ora')) || '_' ||
                p.spid || '.trc'
               ELSE
                NULL
             END) AS trace_file_name
  FROM v$instance      i,
       v$session       s,
       v$process       p,
       v$px_process    pp,
       v$shared_server ss
 WHERE s.paddr = p.addr
   AND s.sid = pp.sid(+)
   AND s.paddr = ss.paddr(+)
   AND s.type = 'USER'
   and s.SID = (select sid from v$session where sid = sys_context('userenv','sid'))
 ORDER BY s.sid;

 

TVDXTAT抽取日志
tvdxtat -i orcl_ora_3021.trc -o 3029.html -s no -w yes

 

查系统中所有用户
select * from sys.user$

 

查系统中的schema
SELECT schema#, cid, cname
  FROM sys.registry$
 WHERE status IN (1, 3, 5)
   AND namespace = 'SERVER'
UNION ALL
SELECT s.schema#, s.cid, cname
  FROM sys.registry$ r, sys.registry$schemas s
 WHERE r.status IN (1, 3, 5)
   AND r.namespace = 'SERVER'
   AND r.cid = s.cid