备份方案 --------------------------------------------------------- exp 与imp数据导出导入命令 exp DEV_PPM_PRODUCE_MES/dev123@10.10.28.15:1521/PPMTEST file=d:\daochu.dmp owner=DEV_PPM_PRODUCE_MES compress=y imp scott/scott@127.0.0.1/ORCL file=d:\daochu.dmp full=y --ignore=y 数据泵导出数据： create directory exp_dir as 'c:\emp'; Grant read,write on directory exp_dir to scott 切换到cmd下 切换到 E:\app\Paul.xu\product\11.1.0\db_1\BIN 目录下 执行以下命令： expdp DEV_PPM_PRODUCE_MES/dev123 DUMPFILE=s.dmp DIRECTORY=exp_dir SCHEMAS=DEV_PPM_PRODUCE_MES reuse_dumpfiles=y version=11.1.0.1.6 数据泵导入数据：（要
删除以前数据） 进入CMD 切换到 E:\app\Paul.xu\product\11.1.0\db_1\BIN 目录下 执行以下命令： -- 将备份的DEV_PPM_PRODUCE_MES 方案装入scott impdp scott/scott directory=exp_dir dumpfile=scott.dmp REMAP_SCHEMA=DEV_PPM_PRODUCE_MES:scott table_exists_action=replace impdp scott/scott@orcl file=c:\scott.dmp buffer=909600 ignore=y full=y commit=y ----------------------------------------------------------
查询sql的历史记录 select * from v$
sqlarea t where t.PARSING_SCHEMA_NAME in ('
用户名') order by t.LAST_ACTIVE_TIME desc select * from v$
sqlarea t order by t.LAST_ACTIVE_TIME desc 查看一段时间内的执行情况 select * from v$
sqlarea t where ( to_char(t.LAST_ACTIVE_TIME,'yyyy-mm-dd hh24:mi:ss') > '2011-12-28 14:11:39' ) and (to_char(t.LAST_ACTIVE_TIME,'yyyy-mm-dd hh24:mi:ss') < '2011-12-28 14:11:43') order by t.LAST_ACTIVE_TIME desc
查询当前时间 select sysdate from dual ------------------------------------------
查询最近
用户执行的
sql SELECT SE.sid,SE.serial#,PR.spid,SE.status,SUBSTR(SE.program,1,20) PROG,SUBSTR(SE.machine,30) MACH,SQ.
sql_text,sq.LAST_ACTIVE_TIME FROM v$session SE,v$
sqlarea SQ,v$process PR WHERE SE.paddr = PR.ADDR(+) AND SE.
sql_address = SQ.address(+) AND schemaname <> 'SYS' ORDER BY SE.sid
查询锁表的情况 select * from v$locked_object; select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id; select b.username,b.sid,b.serial#,
logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.
logon_time; SELECT * FROM V$
sqlAREA T ORDER BY T.LAST_ACTIVE_TIME DESC; 查看Oracle会话 SELECT * FROM V$SESSION T ------------------------------------------------------------------------- 查找某个字符的存储过程 select * from all_source where TYPE = 'PROCEDURE' and TEXT like '%
sql_str1%' -------------------------------------------------------------------------
查询某个字段它所在的表 select A.table_name,B.column_name from user_tables A,user_tab_columns B where A.table_name=B.table_name and B.Column_name='CONTINUOUS_PRD' ----------------------------------------------------------------------------
查询表空间利用率： SELECT a.tablespace_name "表空间名",total 表空间大小,free 表空间剩余大小,(total-free) 表空间使用大小,ROUND((total-free)/total,4)*100 "使用率 %" FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name ) a,(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b WHERE a.tablespace_name=b.tablespace_name select b.file_name 物理
文件名,b.tablespace_name 表空间,b.bytes/1024/1024 大小M,(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,5) 利用率 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name; ---------------------------------------------------------------------------- oracle找速度慢的语句: select b.username username,a.disk_reads reads,a.executions exec,a.disk_reads/decode(a.executions,a.executions) rds_exec_ratio,a.
sql_text Statement from v$
sqlarea a,dba_users b where a.parsing_user_id=b.user_id and a.disk_reads > 100000 order by a.disk_reads desc;
原文链接：https://www.f2er.com/oracle/213870.html
