oracle+strings+命令,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 sid='sid|*'

查看二进制参数文件

strings spfile$ORACLE_SID.ora

查看trace file 位置

select name,value from v$parameter where name like '%dump_dest%'

创建用户

create user identified by

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$

查系统中的schemaSELECT schema#, cid, cname  FROM sys.registry$ WHERE status IN (1, 3, 5)   AND namespace = 'SERVER'UNION ALLSELECT 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