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